From One Check to a Severity-Ranked Check Ledger

Level 10: Distributed Compute & Data Operationsmedium28 mincheck suitescheck results ledgerwarn vs error severityrun verdictappend-only historymulti-statement scriptsscoped idempotency

Run a whole check suite in one script, record every result in an append-only check_results ledger with its severity, and derive the run verdict from severity rather than from a raw failure count.

A check whose result is not stored can only answer one question

You already know how to write a check. Level 6 wrote them one query at a time, and Level 8 gave you the vocabulary they are written in: a test is a query that returns the violating rows, and it passes when that query returns zero rows. Both stopped at the moment the query returns.

That is the moment production begins. A check you run and throw away can answer exactly one question, "is this broken right now". It cannot answer any of the questions people actually ask during an incident: has this check ever passed, when did it start failing, is this the third night in a row, did the number of bad rows get worse after the fix. Those need a record.

So a production quality stack has two tables, and only two.

The ledger and the current-status projection

check_results(run_id, check_name, table_name, failed_rows, severity, status, checked_at)
dq_check_status(check_name, table_name, severity, failed_rows, status, last_run_id, last_checked_at)

check_results is append-only. Every run of every check adds one row and nothing is ever updated in place. That is what gives you history: every night adds one row per check that actually ran, and every question about the past is a filter over them. Checks get added and retired, so a night's block of rows is not always the same height, and the ledger is the only place that records when a check joined the suite.

dq_check_status is the current-status projection, exactly one row per check, and it is what a dashboard reads so the dashboard never has to compute "latest per check" on every page load. Because it holds one row per check, it is written with an upsert, not an append. Two tables, two write shapes, and the difference between them is the whole lesson:

-- append: one more row per check, every run, forever
INSERT INTO check_results (run_id, check_name, table_name, failed_rows, severity, status, checked_at)
SELECT 'run_2026_03_02', d.check_name, d.table_name, v.failed_rows, d.severity, ... ;

-- upsert: the same check overwrites its own single row
INSERT INTO dq_check_status (check_name, failed_rows, status, last_run_id)
SELECT check_name, failed_rows, status, run_id
FROM check_results
WHERE run_id = 'run_2026_03_02'
ON CONFLICT(check_name) DO UPDATE SET
  failed_rows = excluded.failed_rows,
  status      = excluded.status,
  last_run_id = excluded.last_run_id;

excluded is the pseudo-table holding the row the INSERT tried to add and could not, because a row with that check_name was already there. So failed_rows = excluded.failed_rows reads "keep the new value". SQL Level 4 introduced this in sql-l4-idempotent-merge and sql-l4-scd-type1; here it is pointed at a status table, and it is what makes rerunning tonight's suite harmless.

The demo below runs the append half for real against an empty ledger: two of the five checks, two rows written, and what it returns is exactly what landed in check_results.

Severity is a property of the check, not of the query

Here is the half that interviews probe and that no earlier level grades. A failing check is not automatically an emergency. Every check definition carries a severity:

Table

Severity is attached to the check definition, so the same violating-rows query can block one environment and merely annotate another.
severity	what it does to the run	who hears about it	typical check
error	blocks the publish, the load stops	pages the on-call engineer	primary key is not unique
warn	load continues, the row still lands in the ledger	shows up on the dashboard, reviewed next morning	a few negative amounts
error in prod, warn in dev	same query, different consequence	environment decides, not the SQL	referential integrity

Severity is attached to the check definition, so the same violating-rows query can block one environment and merely annotate another.

The last row is the point. The query that finds orphaned foreign keys is byte for byte the same in dev and in prod. What differs is what the platform does when it returns rows. That is why severity lives in check_definitions next to the check name, and why your script must read severity from that table rather than typing 'error' into the INSERT. A hardcoded severity is a check you cannot re-tune without a code deploy.

What "blocks the load" actually looks like in SQL is the write-audit-publish gate: stage into a temporary table, run the audit, publish only on a clean pass. SQL Level 5 (sql-l5-data-quality-gates) grades exactly that, so this lesson points at it and stays on the ledger and the verdict.

The verdict is derived from severity, not from a count

Once results carry severity, the run verdict falls out of them:

any error-severity check failed, the verdict is blocked and nothing publishes
no errors failed but at least one warn did, the verdict is warn and the load goes through with a note
nothing failed, the verdict is passed

Notice what this makes irrelevant: the raw number of failures. A run with six warn failures publishes. A run with one error failure does not. A pipeline that alerts on "failure count above zero" wakes people up for negative refund amounts and is muted within a month, which is how a team ends up with no alerting at all.

Common mistake: treating the ledger as the current state and updating rows in place. The instant you UPDATE check_results SET status = ..., you have thrown away the history that made the table worth having. Append to the ledger, upsert the projection.

Interview nuance: "how do you know your data is correct" has two halves, and most candidates answer only the first. The first half is the check families (uniqueness, not-null, referential integrity, accepted values, freshness). The second half is where the results land, what severity each check carries, and who gets woken up. Candidates who stop at "I write tests" get the follow-up "and then what happens", so answer both halves unprompted.

On a real platform this differs. dbt writes this ledger to run_results.json after every invocation and teams load that artifact into a warehouse table with this exact shape; dbt also expresses severity directly on the test (severity: warn or error, with warn_if and error_if thresholds). Great Expectations stores validation results in a results store, Soda keeps a check-history table, and Monte Carlo keeps the same shape behind a UI. Four products, one pair of tables: an append-only result history and a one-row-per-check current status.

Sample data for this example

CREATE TABLE staged_orders (
  order_id    INTEGER,
  customer_id INTEGER,
  amount      REAL,
  status      TEXT,
  order_date  TEXT
);
INSERT INTO staged_orders (order_id, customer_id, amount, status, order_date) VALUES
  (9001, 501,  120.50, 'paid',    '2026-03-01'),
  (9002, 502,   89.99, 'paid',    '2026-03-01'),
  (9003, 503,   45.00, 'paid',    '2026-03-01'),
  (9003, 503,   45.00, 'paid',    '2026-03-01'),
  (9004, NULL, 210.00, 'paid',    '2026-03-01'),
  (9005, 504,  -35.25, 'refund',  '2026-03-01'),
  (9006, 777,   76.40, 'paid',    '2026-03-02'),
  (9007, 501,   15.00, 'paid',    '2026-03-02'),
  (9008, NULL,  60.00, 'pending', '2026-03-02'),
  (9009, 505,  340.00, 'paid',    '2026-03-02'),
  (9010, 502,  -12.00, 'refund',  '2026-03-02'),
  (9011, 506,   99.00, 'paid',    '2026-03-02'),
  (9012, NULL,  55.75, 'pending', '2026-03-02'),
  (9013, 507,   -8.50, 'refund',  '2026-03-02'),
  (9014, 503,  180.00, 'paid',    '2026-03-02'),
  (9015, 504,  -21.00, 'refund',  '2026-03-02');

CREATE TABLE customers (
  customer_id INTEGER,
  region      TEXT
);
INSERT INTO customers (customer_id, region) VALUES
  (501, 'us-east'),
  (502, 'us-west'),
  (503, 'eu-central'),
  (504, 'us-east'),
  (505, 'apac'),
  (506, 'eu-central'),
  (507, 'us-west');

CREATE TABLE check_definitions (
  check_name TEXT,
  table_name TEXT,
  severity   TEXT      -- error blocks the load, warn records and lets it through
);
INSERT INTO check_definitions (check_name, table_name, severity) VALUES
  ('orders_unique_order_id',      'staged_orders', 'error'),
  ('orders_customer_id_not_null', 'staged_orders', 'error'),
  ('orders_status_allowed',       'staged_orders', 'error'),
  ('orders_amount_non_negative',  'staged_orders', 'warn'),
  ('orders_customer_fk',          'staged_orders', 'warn');

CREATE TABLE check_results (
  run_id      TEXT,
  check_name  TEXT,
  table_name  TEXT,
  failed_rows INTEGER,  -- how many rows the check's violation query returned
  severity    TEXT,     -- copied from check_definitions at run time
  status      TEXT,     -- pass when failed_rows = 0, otherwise fail
  checked_at  TEXT
);

CREATE TABLE dq_check_status (
  check_name      TEXT PRIMARY KEY,   -- exactly one current row per check, enforced by the schema
  table_name      TEXT,
  severity        TEXT,
  failed_rows     INTEGER,
  status          TEXT,
  last_run_id     TEXT,
  last_checked_at TEXT
);

Worked example (SQL)

-- Two of the five checks, actually writing their ledger rows. This is a WRITE: check_results
-- starts empty above and holds these two rows afterwards. RETURNING shows what landed, and what
-- each severity means for tonight's load.
WITH violations(check_name, failed_rows) AS (
  SELECT 'orders_unique_order_id',
         (SELECT COUNT(*) FROM staged_orders s
          WHERE (SELECT COUNT(*) FROM staged_orders t WHERE t.order_id = s.order_id) > 1)
  UNION ALL
  SELECT 'orders_amount_non_negative',
         (SELECT COUNT(*) FROM staged_orders WHERE amount < 0)
)
INSERT INTO check_results (run_id, check_name, table_name, failed_rows, severity, status, checked_at)
SELECT 'run_2026_03_02', d.check_name, d.table_name, v.failed_rows, d.severity,
       CASE WHEN v.failed_rows = 0 THEN 'pass' ELSE 'fail' END,
       '2026-03-02 06:15:00'
FROM check_definitions d
JOIN violations v ON v.check_name = d.check_name
RETURNING check_name,
          severity,
          failed_rows,
          status,
          CASE
            WHEN status = 'pass' THEN 'nothing to escalate, but the pass is recorded anyway'
            WHEN severity = 'error' THEN 'blocks the publish, pages the on-call'
            ELSE 'load continues, the warning lands in the ledger'
          END AS consequence;

Apply

Your turn

The task this lesson builds to.

Write a script that runs all five checks in check_definitions against staged_orders, appends one check_results row per check for run 'run_2026_03_02', and then refreshes dq_check_status so it holds exactly one current row per check.

Each ledger row carries the check's failed_rows (how many rows its violation query returns), the severity and table_name read from check_definitions, a status of 'pass' when failed_rows is 0 and 'fail' otherwise, and checked_at set to '2026-03-02 06:15:00'.

The five checks are:

orders_unique_order_id: rows whose order_id appears more than once
orders_customer_id_not_null: rows with a NULL customer_id
orders_status_allowed: rows whose status is not one of 'paid', 'pending', 'refund'
orders_amount_non_negative: rows with a negative amount
orders_customer_fk: rows whose customer_id is not NULL and has no matching row in customers

check_results is append-only and grows on every run, which is expected. dq_check_status is keyed on check_name, so it must still hold one row per check after the script runs twice.

5 hints and 8 automated checks are waiting in the workspace.

Practice

Make it stick

A second problem on the same idea, plus 3 bonus drills.

Write a query that returns the verdict for each run in the ledger, as (run_id, error_failures, warn_failures, verdict), newest run first, over check_results(run_id, check_name, table_name, failed_rows, severity, status, checked_at).

error_failures counts the failing checks whose severity is 'error', warn_failures counts the failing checks whose severity is 'warn', and verdict is 'blocked' when any error-severity check failed, 'warn' when only warn-severity checks failed, and 'passed' otherwise. Alias every column exactly, and sort by run_id descending.

3 hints and 1 automated check are waiting in the workspace.