Schema Drift, Lineage, and the Incident Walk
Detect breaking schema drift by diffing two catalog snapshots, then triage a silent-success incident by walking lineage upstream with a recursive CTE until you find the run that succeeded and wrote nothing.
Schema drift is a diff between two catalog snapshots
The schema pillar has no clever machinery behind it. Capture the column catalog every day, and drift is the difference between yesterday's snapshot and today's. Every warehouse can give you this catalog, and once it is a table, the monitor is a self-join.
What matters is that the three kinds of change are not equally dangerous:
- Added columns are usually safe. Nothing downstream selects a column that did not exist, so an addition is additive and consumers keep working. This is the same logic as the backward-compatible schema rules from the streaming level: add fields with defaults and old readers are fine.
- Dropped columns break consumers. Every query naming that column now errors, and you will not find out from the pipeline that dropped it. You find out from the dashboard.
- Type changes break consumers quietly. A
REALthat becomesTEXTdoes not error immediately. It errors at the firstSUM, or worse, it sorts'9'above'10'and returns a wrong number with no complaint at all.
So the drift monitor reports drops and type changes and stays quiet about additions. That single decision is the difference between a monitor people read and a monitor people mute.
Lineage is an edges table, and a recursive CTE walks it
The lineage pillar is one narrow table:
lineage_edges(upstream_table, downstream_table)
One row per "this is built from that". Every question you want to ask is a walk over it. Upstream of a broken table, downstream of a broken table, everything within two hops. In SQL that walk is a recursive CTE: an anchor query that finds the first ring of neighbours, then a recursive part that keeps joining the edges table to what you have found so far.
WITH RECURSIVE upstream(table_name) AS (
SELECT upstream_table FROM lineage_edges WHERE downstream_table = 'revenue_dashboard'
UNION
SELECT e.upstream_table
FROM lineage_edges e
JOIN upstream u ON e.downstream_table = u.table_name
)
SELECT * FROM upstream;
Use UNION, not UNION ALL. UNION deduplicates, which is what stops the walk when a table is reachable by two paths, and in a graph with a cycle it is the difference between a result and an infinite loop.
The failure mode nobody tests for: green but empty
Here is the incident. At 09:00 someone says the revenue dashboard shows a number that is far too low. You open the orchestrator. Every task is green. Every run says success. There is no alert, no retry, no failed task, nothing to look at.
Stage 1 of 4: The raw landing tables. All four ran, all four are green, and one of them pulled nothing at all.
A run status answers "did the code finish without throwing". It does not answer "did the code do anything". A job that reads an empty source, transforms nothing, and writes zero rows completes perfectly. This is the most-missed production failure, and it is missed because every monitoring dashboard is built on the status column.
This is not the same zero-row success Level 9 counted. de-l9-freshness-slas already names silent success and grades a silent_success_runs column, but it counts them one pipeline at a time, in a report you open when you already know which pipeline you are worried about. Here you do not know. The complaint is a number on a dashboard, every table in the log is green, and three separate tables wrote zero rows on their latest run. Lineage is the half that is new: it is what narrows a list of anomalies to the one node that can explain this number, and it is what tells you the other two sit on a pipeline the revenue dashboard never reads.
The triage method
The method is short enough to say in an interview:
- Start from the complaint. The broken asset is
revenue_dashboard, so that is the root of the walk. - Walk lineage upstream. Collect every table the broken asset depends on, at any depth. Do not guess which one it is, and do not guess how far up it is either: a two-hop or three-hop join is an assumption about the shape of a graph you have not read, and real chains are longer than the sketch you drew from memory.
- For each node check three things, not one. The latest run's
status, itsrows_written, and its freshness. Status alone is what let this incident happen. - The break is the deepest node that is green and empty. Anything below it is fine, anything above it is a victim.
- Then check the schema pillar, because a dropped or retyped upstream column is a common cause of a filter that suddenly matches nothing.
Common mistake: scanning the whole run log for zero-row successes instead of walking lineage first. In this seed that returns three tables, two of which belong to a completely different pipeline that nothing on the revenue dashboard reads. Lineage is what turns a list of anomalies into one root cause, and it is exactly the step Level 9's per-pipeline report never had to take.
Interview nuance: "the pipeline is green but the dashboard is empty, walk me through it" is a standard scenario, and there are two graded moments in it. The first is saying out loud that a success status does not mean rows were written, which is the insight most candidates miss. The second is triaging by lineage rather than by guessing, because that is what makes the method work on a pipeline you have never seen. Naming rows_written, freshness, and schema drift as the three things you check at each node is the answer.
On a real platform this differs. dbt, Airflow's asset graph, OpenLineage, and the warehouse catalogs all maintain the edges table for you, so you query lineage rather than record it: dbt's
ref()calls build it at compile time, anddbt run --select fct_revenue+selects the same downstream set the recursive CTE computes here. Column-level lineage tools take this one step further and tell you which downstream COLUMNS a dropped column breaks, which is the version that makes the blast-radius drill exact rather than conservative. The failure itself is unchanged: every one of those platforms will show you a green run that wrote nothing.
CREATE TABLE column_catalog (
snapshot_date TEXT, -- the day the catalog was captured
table_name TEXT,
column_name TEXT,
data_type TEXT
);
INSERT INTO column_catalog (snapshot_date, table_name, column_name, data_type) VALUES
('2026-03-01', 'raw_payments', 'payment_id', 'INTEGER'),
('2026-03-01', 'raw_payments', 'order_id', 'INTEGER'),
('2026-03-01', 'raw_payments', 'amount_usd', 'REAL'),
('2026-03-01', 'raw_payments', 'payment_method', 'TEXT'),
('2026-03-01', 'raw_payments', 'paid_at', 'TEXT'),
('2026-03-01', 'stg_payments', 'payment_id', 'INTEGER'),
('2026-03-01', 'stg_payments', 'order_id', 'INTEGER'),
('2026-03-01', 'stg_payments', 'amount_usd', 'REAL'),
('2026-03-01', 'stg_payments', 'paid_at', 'TEXT'),
('2026-03-01', 'stg_orders', 'order_id', 'INTEGER'),
('2026-03-01', 'stg_orders', 'customer_id', 'INTEGER'),
('2026-03-01', 'stg_orders', 'promo_code', 'TEXT'),
('2026-03-01', 'stg_orders', 'order_date', 'TEXT'),
('2026-03-01', 'fct_revenue', 'revenue_date', 'TEXT'),
('2026-03-01', 'fct_revenue', 'amount_usd', 'REAL'),
('2026-03-01', 'fct_revenue', 'order_count', 'INTEGER'),
('2026-03-01', 'revenue_dashboard', 'revenue_date', 'TEXT'),
('2026-03-01', 'revenue_dashboard', 'amount_usd', 'REAL'),
('2026-03-02', 'raw_payments', 'payment_id', 'INTEGER'),
('2026-03-02', 'raw_payments', 'order_id', 'INTEGER'),
('2026-03-02', 'raw_payments', 'amount_usd', 'REAL'),
('2026-03-02', 'raw_payments', 'paid_at', 'TEXT'),
('2026-03-02', 'raw_payments', 'settlement_id', 'TEXT'),
('2026-03-02', 'stg_payments', 'payment_id', 'INTEGER'),
('2026-03-02', 'stg_payments', 'order_id', 'INTEGER'),
('2026-03-02', 'stg_payments', 'amount_usd', 'REAL'),
('2026-03-02', 'stg_payments', 'paid_at', 'INTEGER'),
('2026-03-02', 'stg_orders', 'order_id', 'INTEGER'),
('2026-03-02', 'stg_orders', 'customer_id', 'INTEGER'),
('2026-03-02', 'stg_orders', 'order_date', 'TEXT'),
('2026-03-02', 'fct_revenue', 'revenue_date', 'TEXT'),
('2026-03-02', 'fct_revenue', 'amount_usd', 'TEXT'),
('2026-03-02', 'fct_revenue', 'order_count', 'INTEGER'),
('2026-03-02', 'fct_revenue', 'refund_usd', 'REAL'),
('2026-03-02', 'revenue_dashboard', 'revenue_date', 'TEXT'),
('2026-03-02', 'revenue_dashboard', 'amount_usd', 'REAL');
CREATE TABLE lineage_edges (
upstream_table TEXT,
downstream_table TEXT -- one row per "downstream_table is built from upstream_table"
);
INSERT INTO lineage_edges (upstream_table, downstream_table) VALUES
('raw_payments', 'stg_payments'),
('raw_refunds', 'stg_refunds'),
('raw_chargebacks', 'stg_refunds'),
('raw_orders', 'stg_orders'),
('stg_payments', 'fct_revenue'),
('stg_refunds', 'agg_refunds_daily'),
('agg_refunds_daily', 'fct_revenue'),
('stg_orders', 'fct_revenue'),
('fct_revenue', 'revenue_dashboard'),
('fct_revenue', 'finance_extract'),
('raw_clicks', 'stg_clicks'),
('stg_clicks', 'marketing_dashboard');
CREATE TABLE pipeline_runs (
run_id TEXT,
table_name TEXT,
run_date TEXT,
status TEXT, -- every row here is 'success', which is the whole problem
rows_written INTEGER,
finished_at TEXT
);
INSERT INTO pipeline_runs (run_id, table_name, run_date, status, rows_written, finished_at) VALUES
('r1001', 'raw_payments', '2026-03-01', 'success', 128400, '2026-03-01 02:12:00'),
('r1002', 'stg_payments', '2026-03-01', 'success', 128400, '2026-03-01 02:40:00'),
('r1003', 'raw_refunds', '2026-03-01', 'success', 4120, '2026-03-01 02:15:00'),
('r1004', 'raw_chargebacks', '2026-03-01', 'success', 260, '2026-03-01 02:16:00'),
('r1005', 'stg_refunds', '2026-03-01', 'success', 4380, '2026-03-01 02:44:00'),
('r1006', 'agg_refunds_daily', '2026-03-01', 'success', 90, '2026-03-01 02:58:00'),
('r1007', 'raw_orders', '2026-03-01', 'success', 0, '2026-03-01 02:18:00'),
('r1008', 'stg_orders', '2026-03-01', 'success', 96500, '2026-03-01 02:47:00'),
('r1009', 'fct_revenue', '2026-03-01', 'success', 3100, '2026-03-01 03:05:00'),
('r1010', 'revenue_dashboard', '2026-03-01', 'success', 3100, '2026-03-01 03:20:00'),
('r1011', 'raw_clicks', '2026-03-01', 'success', 501000, '2026-03-01 02:22:00'),
('r1012', 'stg_clicks', '2026-03-01', 'success', 498300, '2026-03-01 02:51:00'),
('r1013', 'finance_extract', '2026-03-01', 'success', 3100, '2026-03-01 03:35:00'),
('r1014', 'marketing_dashboard','2026-03-01', 'success', 498300, '2026-03-01 03:40:00'),
('r1101', 'raw_payments', '2026-03-02', 'success', 131900, '2026-03-02 02:11:00'),
('r1102', 'stg_payments', '2026-03-02', 'success', 131900, '2026-03-02 02:39:00'),
('r1103', 'raw_refunds', '2026-03-02', 'success', 0, '2026-03-02 02:14:00'),
('r1104', 'raw_chargebacks', '2026-03-02', 'success', 210, '2026-03-02 02:16:00'),
('r1105', 'stg_refunds', '2026-03-02', 'success', 210, '2026-03-02 02:43:00'),
('r1106', 'agg_refunds_daily', '2026-03-02', 'success', 30, '2026-03-02 02:57:00'),
('r1107', 'raw_orders', '2026-03-02', 'success', 97200, '2026-03-02 02:17:00'),
('r1108', 'stg_orders', '2026-03-02', 'success', 97800, '2026-03-02 02:46:00'),
('r1109', 'fct_revenue', '2026-03-02', 'success', 2870, '2026-03-02 03:04:00'),
('r1110', 'revenue_dashboard', '2026-03-02', 'success', 2870, '2026-03-02 03:19:00'),
('r1111', 'raw_clicks', '2026-03-02', 'success', 512400, '2026-03-02 02:21:00'),
('r1112', 'stg_clicks', '2026-03-02', 'success', 0, '2026-03-02 02:50:00'),
('r1113', 'finance_extract', '2026-03-02', 'success', 2870, '2026-03-02 03:34:00'),
('r1114', 'marketing_dashboard','2026-03-02', 'success', 0, '2026-03-02 03:39:00');-- The triage walk, live. Every status says success. One node is lying by omission.
WITH RECURSIVE upstream(table_name, depth) AS (
SELECT upstream_table, 1 FROM lineage_edges WHERE downstream_table = 'revenue_dashboard'
UNION
SELECT e.upstream_table, u.depth + 1
FROM lineage_edges e
JOIN upstream u ON e.downstream_table = u.table_name
)
SELECT u.table_name,
u.depth AS hops_upstream,
r.run_date,
r.status,
r.rows_written,
CASE WHEN r.status = 'success' AND r.rows_written = 0
THEN 'GREEN BUT EMPTY' ELSE 'ok' END AS triage_flag
FROM upstream u
JOIN pipeline_runs r ON r.table_name = u.table_name
WHERE r.run_date = (SELECT MAX(r2.run_date) FROM pipeline_runs r2 WHERE r2.table_name = u.table_name)
ORDER BY u.depth, u.table_name;Apply
Your turn
The task this lesson builds to.
Write a query that returns every breaking schema change between the '2026-03-01' and '2026-03-02' snapshots, as (table_name, column_name, change), ordered by table name then column name, over column_catalog(snapshot_date, table_name, column_name, data_type).
change is 'dropped' when a column present on 2026-03-01 is gone on 2026-03-02, and 'type_changed' when it is still there under a different data_type. Columns that only appear in the newer snapshot are additive and must not be reported. Alias the computed column exactly change.
4 hints and 1 automated check are waiting in the workspace.
Practice
Make it stick
A second problem on the same idea, plus 2 bonus drills.
Write a query that returns the upstream table that broke revenue_dashboard, as (table_name, run_date, rows_written), over lineage_edges(upstream_table, downstream_table) and pipeline_runs(run_id, table_name, run_date, status, rows_written, finished_at).
The dashboard is empty this morning and every run in the log reports 'success'. The table you are looking for is the one in revenue_dashboard's upstream chain, at any depth, whose most recent run succeeded and wrote zero rows. Alias every column exactly.
1 hint and 1 automated check are waiting in the workspace.