Lesson 4 of 4 · 25 min

Explain a data incident with evidence and a repair plan

Produce a bounded incident diagnosis and validation plan.

Mechanism and reasoning

A data incident is often discovered through a surprising business number. The first task is to separate a real business change from a pipeline defect. Compare source evidence, ingestion completeness, transformation versions and downstream query changes. A chart drop alone cannot identify the cause.

Freeze the affected result's definition and input range. If the dashboard query changes while the source is being repaired, later comparisons become ambiguous. Preserve a small set of affected keys and the run metadata needed to reproduce the discrepancy. Avoid spreading personal or sensitive records into broad incident messages.

Localize the first stage where the counts or values diverge. Compare source, raw, staged and published data at the same grain and time boundary. A source count of transactions cannot be compared directly with a destination count of customers. Unit and grain mismatches can create false alarms.

Choose a repair that follows the proven scope. If one date partition has a parser defect, a bounded replay is usually easier to validate than rebuilding all history. If the source contract changed, first fix the transform and add a check that detects the new shape. Replaying with unchanged faulty logic repeats the defect.

State what is known, inferred and still untested. A strong interview answer can say that evidence supports a hypothesis without pretending the investigation is complete. Define recovery as correct data available to readers with the right timestamp, not simply a successful job. Close with one prevention tied to the actual failure mechanism and one check that demonstrates it works.

Localize the first divergence with comparable evidence

Build a stage ledger for one fixed business interval. Every count must name its grain and inclusion rule. If raw storage counts events while staging counts current orders, a lower staging count can be correct. Compare distinct source event IDs when tracing ingestion, then compare current business keys when tracing a state transformation. Changing the unit midway through the ledger creates a false localization.

Stage	Grain	Count	Key difference
Source extract	Distinct payment event	10,000	Baseline
Raw	Distinct payment event	10,000	None against source
Accepted staging	Distinct payment event	9,500	500 reversed events absent
Published event facts	Distinct payment event	9,500	None against staging
Dashboard	Same event facts, same period	9,500	No extra filter difference

Under these stated conditions, the first observed divergence is staging. This does not prove that every missing event should be accepted unchanged. The new status needs business semantics: it may reverse an earlier payment, represent a separate adjustment or mark an invalid source record. Fixing the parser to accept the string without understanding the operation can restore row count while making revenue wrong.

sql

1-- Teaching key comparison on the fixed incident range.2SELECT r.event_id, r.status3FROM raw_payments AS r4LEFT JOIN staged_payments AS s5  ON s.event_id = r.event_id6WHERE r.business_date = DATE '2026-09-20'7  AND s.event_id IS NULL;

This query assumes event IDs are unique in each relevant relation and the staging scope matches the raw date contract. If the sink reassigns business dates, compare the complete scoped key set rather than filtering both sides in a way that hides moved records. Also inspect extra staged keys, not only missing ones. A correct reconciliation checks both directions.

A revenue discrepancy needs value checks as well as key checks. All keys can be present while amounts use the wrong currency scale or reversal sign. Group comparisons by currency, operation type and business period before combining totals. A net total can hide an overstatement in one category and an equal understatement in another. Preserve the small set of affected keys that explains the difference.

The mitigation should protect decisions while repair proceeds. Mark the affected report stale or under review according to the product contract. Do not silently overwrite it with a partial number. If the previous validated result is served, show its age and scope. Communication should distinguish confirmed missing data from a possible business decline and avoid claiming a full revenue impact until the monetary semantics are reconciled.

Repair the smallest range justified by evidence, but test whether the defect began earlier than the alert. A parser revision or first appearance of the new source status can help locate the start. Sample adjacent periods and compare accepted/rejected counts. A one-day symptom does not automatically prove a one-day defect. State the supported range and the checks used to bound it.

Build the corrected output in an isolated generation. Validate missing and extra keys, operation semantics, totals by currency and the specific affected entities. Then promote through the agreed reader boundary and verify the dashboard uses the new generation. A successful transform job can leave readers on an old pointer or cached result; recovery includes the consumer-visible state.

The prevention should match the mechanism. In this case, an explicit rejected-status count and a publication gate for unexpected statuses would have exposed the silent loss. A broad request to “add monitoring” is less useful. Add a fixture with a new status and verify that the pipeline either handles it under a defined version or fails visibly before publication. That test demonstrates the intended behavior without claiming that no future data incident can occur.

Worked example

Teaching incident: the source has 10,000 payment events for a day. Raw storage has 10,000 distinct event IDs. Staging has 9,500, and publication has 9,500. All missing staging rows use a new status value 'reversed'. The parser's accepted-values rule rejected them without an alert. The evidence localizes the defect to parsing and contract handling, not ingestion. Add explicit reversal semantics, replay the affected date into a separate result and reconcile event keys and monetary totals before promotion.

Exercise

Source and raw both contain 2,000 unique orders. Staging contains 2,000, but a dashboard join produces 2,300 rows. Name the likely class of defect, the check and the repair boundary.

Model solution and rubric

The join likely multiplies rows because the joined relation is not unique at the assumed key or the predicate is incomplete. Check join cardinality per order and dimension-key uniqueness. Fix the join or dimension grain, then rebuild the affected output. Reingesting the source is not supported by this evidence. Verify both unique order count and aggregate values because a distinct-count patch can conceal duplicated monetary sums.

Score out of four: one point for the correct result, one for showing the intermediate reasoning, one for identifying the stated failure case, and one for a verification that could disprove the answer. Do not award the reasoning point for a tool name alone.

Failure modes and misconceptions

“Matching stage counts prove matching data.” Different keys or wrong amounts can preserve counts. Compare identity and value at the required grain.

“Restoring rows proves revenue is correct.” New operations such as reversals need business semantics. Validate signs, references and currency rules before promotion.

Interview probe

Evidence class: recommended. Original practice.

Revenue fell by 20%. How do you decide whether to wake the business owner or repair the pipeline?

Strong answer: I would compare the same period and grain across source and pipeline stages, check recent definitions and completeness, and identify the first divergence. I would communicate the uncertainty and affected decisions while preventing unverified figures from appearing current.

Follow-up: What evidence would show that the decline is real rather than a processing defect?

Weak answer indicators: Declaring a root cause from one chart; comparing different grains; replaying everything before fixing the transformation.

Sources

Technical references: dbt data tests; Airflow best practices; Debezium PostgreSQL connector. Sources support the documented mechanisms. The numbers, decisions, rubrics and interview prompts in this lesson are original teaching examples, not measurements or employer question claims.

docsdbt data testsdocs.getdbt.com docsAirflow best practicesairflow.apache.org docsDebezium PostgreSQL connectordebezium.io

Checkpoint

Source/raw have 10,000 distinct event IDs, staging 9,500 and output 9,500. First observed divergence?

ARaw-to-staging transformation boundaryBSource extractionCDashboard join onlyDBroker retention necessarily

Checkpoint

All missing staged events use a new reversed status. What must be established before accepting them?

AWhether reversal negates a prior payment, creates an adjustment, or has another defined business effectBOnly whether the string fits the existing column lengthCWhether coercing it to paid restores the row countDWhether dropping the accepted-values test makes the task pass

Checkpoint

Source 900, staging 900, joined output 1,200 at an intended one-order grain. First check?

ASnapshot end timeBNetwork retry countCJoin cardinality and dimension-key uniquenessDOnly compare overall revenue, accepting the row increase if the sum matches

Checkpoint

A repair job passes but readers remain on the old output pointer. Recovery state?

AComplete because compute succeededBComplete if logs are greenCThe source must now be wrongDIncomplete until validated data is visible under the reader contract

Checkpoint

Grand revenue matches despite equal opposite errors in two currencies. What reconciliation helps?

AOnly repeat the same total with a wider time rangeBCompare key/value totals by currency, operation and period under fixed definitionsCOnly verify every task reported successDOnly check that the output has the expected number of partitions

Explain how you would produce a bounded incident diagnosis and validation plan without reading the solution. State one assumption that could change your answer, and one observation that would make you revise it.

Not yetGetting thereConfident

Wrap-up

Find the first divergence at a fixed grain. Repair the smallest supported scope and verify the business result.

Sources

Free to read · better with Enzo

Learn it with Enzo

Save your progress, answer the checkpoints, and let Enzo quiz you on what you just read.

Continue with Enzo Sign up free