Diagnose and Prevent Metric Mismatches Between Upstream and Downstream Tables

A data engineering interview asks how you deal with metrics that do not line up between upstream and downstream data. The same metric gives different numbers depending on where it is read: for example, the daily order count in a summary table differs from the count computed from the detail table the summary is built from, or a report disagrees with the table it reads. Explain how you find the cause, how you fix the data, and how you keep upstream and downstream metrics consistent from then on. ```hint Find where it starts Think about how you would locate the exact step in the data lineage at which the two numbers stop agreeing, and then narrow down within that step. ``` ```hint Same name, same meaning? Before looking for a bug, ask whether both sides really compute the same thing: which rows count, how duplicates are removed, and which time boundary a record falls into. ``` ### Clarifying Questions - Which metric and which layers disagree, and by how much: a fixed offset, a steady percentage, or only on some days? - Did the numbers ever agree, and did anything change around the time they diverged, such as a schema change, a backfill or a new pipeline? - Are both sides supposed to use the same definition, or do different teams own different definitions of the metric? - Is the downstream job started at a fixed clock time, or triggered when the upstream data is complete? - What tolerance is acceptable: must financial amounts match exactly, while approximate distinct counts may differ slightly? ### What a Strong Answer Covers - A systematic diagnosis that narrows the gap by layer, then by partition and dimension, down to row-level differences - The common technical causes: definition drift, time zones and partition boundaries, late or incomplete upstream data, rows lost or multiplied by joins, duplicate loads, NULL handling, and corrections that never reached downstream - Fixing the data: deciding which number is right, backfilling in lineage order, and telling consumers what changed - Prevention: shared metric definitions, completion-triggered scheduling, automated reconciliation checks that block publishing, and lineage - Tolerances and alerting that separate real breaks from expected differences ### Follow-up Questions - The gap appears only for the most recent day and disappears a day later. What does that tell you, and what would you change? - After a dimension table was updated, last month's numbers in a report changed. How do you keep history from shifting? - How would you run reconciliation checks on every pipeline run without doubling compute cost? - Two teams insist on different definitions of the same metric. How do you resolve it?