Data Quality and Incident Questions
The round about the day something went wrong — structuring an incident answer, the six checks worth naming, and finding a discrepancy by bisecting the pipeline.
Every senior data engineering loop has this round. It is scored on structure and on specifics — vague answers about “monitoring and alerting” score badly regardless of how much monitoring you have actually built.
”Tell me about a data incident you handled”
Use six headings, in this order. Most candidates start at diagnosis and skip the rest.
| Heading | What to say | Common mistake |
|---|---|---|
| Detection | how you found out, and how long after it started | ”someone told us” with no follow-up |
| Impact | who was affected, which numbers, how wrong | skipped entirely |
| Diagnosis | how you narrowed it down | jumping to the answer |
| Mitigation | what you did to stop the bleeding | conflated with the fix |
| Root cause | the actual cause, not the symptom | ”a bad deploy” |
| Prevention | what you changed so it cannot recur | ”we were more careful” |
A worked answer, with numbers:
Detection. “Finance flagged that Tuesday’s revenue looked low. Our freshness check had passed, so nothing alerted — that was the first finding.”
Impact. “Three dashboards and the weekly board pack. Revenue understated by £41,000 across four days, about 8%.”
Diagnosis. “I compared row counts layer by layer. Bronze and silver matched the source; gold was short. The join to the customer dimension was dropping rows.”
Mitigation. “Reverted the gold model to the previous version, republished the four days, and told finance which figures had changed before they asked.”
Root cause. “A change swapped a left join for an inner join to ‘clean up’ nulls. Customers created after the dimension’s daily snapshot had no matching row, so their orders vanished.”
Prevention. “Three things: a reconciliation test comparing gold revenue to silver on every run, an anti-join test on the dimension, and a volume check against the trailing seven- day average. The reconciliation test would have caught it on the first run.”
The prevention section is where seniority shows, and the strongest version names which check would have caught it rather than listing checks in general.
Show the bisection
The diagnosis step is worth demonstrating, because it is a technique rather than a story.
import duckdb
con = duckdb.connect()
con.execute("""create table source_orders as select * from (values
(1001, 1, date '2026-01-04', 'completed', 25.50),
(1002, 2, date '2026-01-04', 'completed', 12.00),
(1003, 1, date '2026-01-04', 'returned', 40.00),
(1004, 3, date '2026-01-04', 'completed', 8.75),
(1005, 9, date '2026-01-04', 'completed', 19.99),
(1006, 4, date '2026-01-04', 'completed', 31.20)
) t(order_id, customer_id, ordered_at, status, amount)""")
con.execute("""create table dim_customer as select * from (values
(1, 'GB'), (2, 'US'), (3, 'GB')
) t(customer_id, country)""")
con.execute("create table bronze as select * from source_orders")
con.execute("create table silver as select * from bronze where status <> 'pending'")
con.execute("""create table gold as
select s.ordered_at, c.country, sum(s.amount) as revenue
from silver s join dim_customer c using (customer_id)
where s.status = 'completed' group by 1, 2""")
print(con.execute("""
select 'source' as layer, count(*) as rows,
round(sum(amount) filter (status='completed'), 2) as completed_revenue
from source_orders
union all select 'bronze', count(*), round(sum(amount) filter (status='completed'),2) from bronze
union all select 'silver', count(*), round(sum(amount) filter (status='completed'),2) from silver
union all select 'gold', (select sum(1) from gold), (select round(sum(revenue),2) from gold)
""").df().to_string(index=False)) layer rows completed_revenue
source 6 97.44
bronze 6 97.44
silver 6 97.44
gold 3 77.45Source, bronze and silver agree at £97.44. Gold is £19.99 short — the divergence is between silver and gold, so the transformation to look at is the one in between, and nothing above it matters.
print(con.execute("""
select s.order_id, s.customer_id, s.amount
from silver s left join dim_customer c using (customer_id)
where c.customer_id is null and s.status = 'completed'
""").df().to_string(index=False)) order_id customer_id amount
1005 9 19.99
1006 4 31.20Two orders reference customers absent from the dimension. £19.99 of the gap is order 1005; order 1006’s £31.20 is missing too — and the arithmetic does not close, which is itself informative:
print(con.execute("""
select 97.44 as silver_completed,
(select round(sum(revenue),2) from gold) as gold,
round(97.44 - (select sum(revenue) from gold), 2) as gap,
(select round(sum(amount),2) from silver s
left join dim_customer c using (customer_id)
where c.customer_id is null and s.status='completed') as orphan_total
""").df().to_string(index=False)) silver_completed gold gap orphan_total
97.44 77.45 19.99 51.19The gap is £19.99 but the orphans total £51.19 — so the numbers do not reconcile and there is a second problem. Following that discrepancy rather than stopping at the first plausible cause is the behaviour being assessed. Being able to say “the arithmetic does not close, so there is more than one issue” is a strong signal.
Bisection generalises: compare a metric at each layer, find the first divergence, look only there. Two minutes, and it beats reading transformation code from the top.
”What checks would you add?”
Name six, with what each catches. Listing check names is mid-level; naming the failure each one catches is senior.
checks = {
"freshness": "select max(ordered_at) >= current_date - 1 from silver",
"volume": "select count(*) between 0.5*avg_7d and 1.5*avg_7d from ...",
"unique_key": "select count(*) = count(distinct order_id) from silver",
"referential": "select count(*) = 0 from silver s left join dim_customer c using (customer_id) where c.customer_id is null",
"accepted_values":"select count(*) = 0 from silver where status not in ('completed','returned','refunded')",
"reconciliation": "select abs(sum(gold.revenue) - sum(silver completed)) < 0.01",
}
for name, sql in checks.items():
print(f"{name:<16} {sql[:78]}")freshness select max(ordered_at) >= current_date - 1 from silver
volume select count(*) between 0.5*avg_7d and 1.5*avg_7d from ...
unique_key select count(*) = count(distinct order_id) from silver
referential select count(*) = 0 from silver s left join dim_customer c using (custome
accepted_values select count(*) = 0 from silver where status not in ('completed','returne
reconciliation select abs(sum(gold.revenue) - sum(silver completed)) < 0.01| Check | Catches |
|---|---|
| Freshness | the source stopped arriving |
| Volume vs trailing average | a truncated file, a partial export, a tightened filter |
| Unique key | duplicates inflating every downstream sum |
| Referential integrity | the incident above |
| Accepted values | an upstream team adding a status without telling anyone |
| Reconciliation | rows lost to a join — what no per-column check can see |
Volume is the one candidates omit, and it catches the failure where every row that arrived is individually valid:
con.execute("""create table load_history as select * from (values
(date '2026-01-01', 4820), (date '2026-01-02', 4712), (date '2026-01-03', 4902),
(date '2026-01-04', 4688), (date '2026-01-05', 4771), (date '2026-01-06', 4810),
(date '2026-01-07', 1204)
) t(load_date, rows_loaded)""")
print(con.execute("""
select load_date, rows_loaded,
round(avg(rows_loaded) over (order by load_date rows between 6 preceding and 1 preceding)) as trailing_avg,
round(100.0 * rows_loaded / avg(rows_loaded) over (order by load_date rows between 6 preceding and 1 preceding), 1) as pct
from load_history order by load_date desc limit 3
""").df().to_string(index=False)) load_date rows_loaded trailing_avg pct
2026-01-07 1204 4784 25.2
2026-01-06 4810 4779 100.6
2026-01-05 4771 4781 99.825% of normal. Every row-level check passes on those 1,204 rows because they are all valid — only the volume check sees a partial load. A threshold expressed as a fraction of the trailing average needs no maintenance as volume grows, which is why it beats an absolute number.
”Block or warn?”
The question behind the question is whether you understand the cost of each.
“Block when publishing would be worse than publishing nothing — a duplicated primary key, a volume collapse, a failed reconciliation. Those corrupt numbers people act on, and stale data is recoverable while wrong data that has been acted on is not. Warn for a known-imperfect source where downstream can cope, and record it either way so the rate is trackable. What I would avoid is a check that only logs — nobody reads it, so it is the appearance of quality rather than quality.”
Then the operational half, which most candidates skip:
con.execute("""create table quality_runs as select * from (values
(date '2026-01-05', 'referential', 0, 'error'),
(date '2026-01-06', 'referential', 0, 'error'),
(date '2026-01-07', 'referential', 2, 'error'),
(date '2026-01-05', 'accepted_values', 1, 'warn'),
(date '2026-01-06', 'accepted_values', 1, 'warn'),
(date '2026-01-07', 'accepted_values', 9, 'warn')
) t(run_date, check_name, failing_rows, severity)""")
print(con.execute("""
select check_name, run_date, failing_rows,
round(avg(failing_rows) over (partition by check_name order by run_date
rows between 6 preceding and 1 preceding), 1) as trailing
from quality_runs where run_date = date '2026-01-07'
""").df().to_string(index=False)) check_name run_date failing_rows trailing
referential 2026-01-07 2 0.0
accepted_values 2026-01-07 9 1.0Alert on the change, not the absolute count. referential going from a trailing zero to two
is a new problem; accepted_values at 9 against a trailing 1 is a spike worth investigating.
A quality dashboard that fires on absolute thresholds becomes noise everyone mutes within a
month.
”How do you prevent a schema change breaking you?”
Two halves, and the second is the one that scores:
Technically — land raw so additive changes flow through, declare types at ingestion so a rename fails loudly rather than producing nulls, and keep the raw payload so a new field is available without a backfill.
try:
con.execute("select order_id, amount_gbp from source_orders limit 1")
except Exception as e:
print(type(e).__name__ + ":", str(e).split("\n")[0])BinderException: Binder Error: Referenced column "amount_gbp" not found in FROM clause!“That failure is what I want. A schema-inference reader would have given me NULL for every
row and the pipeline would have published zeros.”
Organisationally — a data contract checked in the producer’s CI, so a breaking change fails their build rather than your 03:00 run. “Detecting an incompatible schema at read time tells the consumer they are broken, which they already knew. The check has to run earlier than that."
"How do you know a table is trustworthy?”
A short answer that covers a lot of ground:
- Freshness and volume, visible to consumers, not just to the team.
- Ownership — a named team, not a person.
- Lineage — where it came from, so an incident can be traced upstream.
- A documented grain and SLA — “one row per order line, available by 07:00 UTC”.
- A quality history — check results over time, so consumers can see the trend rather than trusting a green badge.
The last point is the differentiator: “a table with a visible quality history is more trustworthy than one that has never been checked, even if the second has fewer known problems.”
The scoring
| Behaviour | Signal |
|---|---|
| Structured the incident detection → impact → … → prevention | senior |
| Named which check would have caught it | senior |
| Bisected layer by layer rather than reading code | senior |
| Noticed the arithmetic did not close and kept going | senior |
| Included volume-against-trend, not just row-level checks | senior |
| Listed good checks without saying what each catches | mid |
| ”We added more monitoring” with no specifics | junior |
Practice
1. Compare a metric across every layer and find the divergence.
layer rows completed_revenue
source 6 97.44
silver 6 97.44
gold 3 77.45The break is between silver and gold, so only that transformation matters. Two minutes of bisection beats an hour of reading code.
2. Check whether the discrepancy fully reconciles.
gap 19.99 orphan_total 51.19The numbers do not close, so there is a second problem. Stopping at the first plausible cause is the most common diagnostic error.
3. Compute volume against a trailing average.
2026-01-07 1204 rows trailing_avg 4784 pct 25.2Every row-level check passes — the rows that arrived are valid. Only volume sees a truncated load, which is why it is the check worth adding first.
4. Compare a check's failing rows with its own trailing average.
check_name failing_rows trailing
referential 2 0.0
accepted_values 9 1.0Zero-to-two is new; one-to-nine is a spike. Alerting on the change rather than the absolute count is what stops a quality dashboard becoming noise.
Next: system design for data — the whiteboard round, scoped and sequenced.