D8LooPFocus modeCODE WORKSPACE
Data-quality audit summary
Return check_name, failing_rows, and status for four checks: duplicate_snapshot_keys, orders_without_items, missing_country_customers, and orphan_line_items. Order by check_name.
Build each check as a counted subquery, stack them with UNION ALL, then label pass/fail with CASE.
Sample input
order_snapshots1001 and 1004 repeat → 2 duplicate snapshot keys.
| order_id | updated_at |
|---|---|
| 1001 | 2026-01-12 09:00:00 |
| 1001 | 2026-01-13 10:00:00 |
| 1004 | 2026-01-15 14:30:00 |
| 1004 | 2026-01-16 12:20:00 |
customersOne NULL country → 1 missing-country customer. orders/order_items drive the other two checks.
| customer_id | country |
|---|---|
| 1 | US |
| 2 | null |
| 3 | GB |
Expected output
Expected outputOne row per check, ordered by name; the orphan check passes, the rest fail.
| check_name | failing_rows | status |
|---|---|---|
| duplicate_snapshot_keys | 2 | fail |
| missing_country_customers | 1 | fail |
| orders_without_items | 5 | fail |
| orphan_line_items | 0 | pass |
Constraints
Compute each check as a scalar COUNT subquery, UNION ALL them into one set of (check_name, failing_rows), then add status via CASE WHEN failing_rows = 0 THEN 'pass' ELSE 'fail' END.
Expected skills
Composing named checks into a reproducible audit evidence table.
SQL
Loading...
AI evaluation
Run the SQL query to inspect preview rows.