DebuggingGENERALSOURCE-SPECIFICSIMULATED

Missing Rows

The report is low, no task failed, and the source database still has every record. Working from a shortfall back to the arrow that dropped it.

What actually happens How to build it Can I trust it?

Who needs this, what one row is, and why the obvious build breaks

Every lesson starts from the consumer, because designing from the source outward is this domain's characteristic mistake.

The question

Yesterday shows materially fewer orders than the equivalent day last week, every DAG task is green, and the source system has all of them. Where did the rows go?

Who needs this

Anyone whose decision depends on a count or a sum being complete: a finance close, a supplier reconciliation, a fraud rate, a conversion funnel where the denominator moved. A shortfall is the one data failure consumers do sometimes notice — and only when it is large enough to look implausible.

What one row is

The unit of investigation is one business record for one period — one order, one payment, one event — and the question at every hop is whether that record is present *there*. Counting at the wrong grain is how an investigation concludes that nothing is missing when a third of the changes are (Grain: What Does One Row Represent?).

The obvious build

Query the serving table for the affected day, see fewer rows than expected, and re-run the pipeline. Re-running is right often enough that it becomes the default response, and when the cause was a transient failure it genuinely resolves the incident in a minute.

Why it breaks

The re-run reproduces the same shortfall exactly, because the pipeline is deterministic and the rows were never delivered to it. The re-run proved the pipeline works and told you nothing about the data (Idempotent Data Pipelines).

How it breaks with real data

The re-run reproduces the same shortfall exactly, because the pipeline is deterministic and the rows were never delivered to it. The re-run proved the pipeline works and told you nothing about the data (Idempotent Data Pipelines).
The high-water mark advanced past the missing rows. An incremental extract using WHERE updated_at > :last_run skips every row whose timestamp was assigned before commit but whose commit landed after the extract read — and the watermark now excludes them permanently (Incremental Extraction, The High-Water Mark).
The CDC connector was down and its position fell outside the source's log retention. The changes are not late; they were never emitted and cannot be replayed (CDC Failure Modes and the Retention Deadline, Retention and Replay).
The rows arrived, but after the window they belong to had already closed, so the batch assigned them nowhere. The day looks quiet rather than incomplete (Late-Arriving Data).
An inner join to a dimension silently dropped every fact whose key was not yet present — new products created after the dimension's last refresh vanish from the fact table entirely (Dimension Tables).
A filter that reads WHERE region IS NOT NULL began excluding a whole country after an upstream change started sending an empty string instead of the code (Nullability & Defaults).
One partition was written to a path the reader does not scan — a date format change, a time zone shift, a typo in a partition value — so the data exists in the lake and is invisible to every query (Partitioning, Partition Pruning).

Source→Ingestion→Raw→Transformation→Validation→Storage Model→Serving→Consumer→Observability

What is actually happening

Rows go missing in exactly two ways: they were never captured, or they were captured and then dropped. Those two have completely different recovery paths, and separating them is the first branch of the investigation (Data Ingestion).
Never-captured failures live at the boundary you do not control. A watermark that advanced too far, a connector outage past retention, an API that paginated differently, a source-side hard delete. What they share is that no amount of re-running your pipeline recovers them (Ingestion Failure & Recovery).
Dropped-after-capture failures live in your own code and are all recoverable, because raw still has the record. Inner joins, filters on columns whose semantics changed, casts that produce null and are then filtered out, windows that closed, deduplication keyed on the wrong column (Keeping Raw History: The Recovery Position and the Liability).
A shortfall is detectable only against an expectation. Without a reconciliation against the source, or at minimum a volume comparison against the dataset's own history, a pipeline that delivers 80% of the rows looks exactly like a quiet day (Volume Anomalies).
The row-count-per-hop chart is the highest-leverage artefact in the whole investigation, because a loss localises to one arrow rather than being searched for across six systems. Building it before an incident costs an afternoon; building it during one costs the incident.
In the in-repo pipeline model, two different causes of missing rows produce two different signatures: a connector outage removes a contiguous block large enough to move the distribution check as well as completeness, while a tail of late arrivals moves completeness and reconciliation only. The magnitude of the loss is itself a diagnostic (The Pipeline Succeeded. The Data Is Wrong.).

From the symptom to the mechanism

Every row in the table below produces the same first observation: a count that is lower than expected with no failed task anywhere. What separates them is a second observation, and knowing which second observation to look for is the entire skill.

Work the table as a decision procedure rather than a reference. Is the loss contiguous in time or spread across the period? Is it concentrated in one dimension value or smeared evenly? Does the gap close as the period matures? Does re-running change anything? Four questions, and the answers pick out a row.

The response column deliberately separates the fix from the recovery. Fixing the mechanism stops tomorrow from being wrong; recovering the data fixes yesterday. They are different pieces of work with different risks, and shipping the fix without the backfill leaves a permanent notch in the history that somebody will find in a year (Planning a Backfill).

Seven ways a row goes missing, and how to tell them apart

Trigger	Symptom	Cause	Response
An incremental extract keyed on `updated_at`, with concurrent long transactions at the source.	A small, steady shortfall spread evenly across every period. Never large enough to alert; never zero.	The timestamp is assigned when the statement runs, the row becomes visible when the transaction commits. Rows whose commit landed after the extract read are permanently below the advanced watermark (Incremental Extraction).	Switch the watermark to a source-side monotonic position, or overlap the window and deduplicate. Recover by re-extracting the affected range on a key rather than a timestamp.
The change-capture connector is down for hours and its position falls outside log retention.	A contiguous block of the day is missing. The loss is large enough to move volume and distribution checks, not only completeness.	Changes committed during the outage were emitted to nobody, and the log that held them has since expired. The pipeline processed everything it received, perfectly (CDC Failure Modes and the Retention Deadline).	Replay from the log if still inside retention. If not, re-snapshot the source and accept that intermediate states within the gap are gone. Alert on connector lag against retention, not against zero.
Events arrive after the batch window they belong to has closed.	The recent period looks quiet, then quietly fills in if — and only if — someone reprocesses it. Distribution checks stay green because the loss is a modest tail.	The window was closed on arrival time rather than event time, so records that happened inside the period and landed outside it were assigned to no window at all (Late-Arriving Data, Event Time).	Widen allowed lateness, or make the last N periods reprocessable on a rolling schedule. Recover by reprocessing the affected range from raw, which still holds the late records.
A fact table joins a dimension with an inner join.	A shortfall concentrated in whatever is new — new products, new regions, new payment methods — and invisible in the total.	The dimension refresh runs after the fact build, or a key was never added. An inner join is a silent filter and SQL will never mention that it removed rows (Dimension Tables).	Left join with an explicit unknown member, and test that the unknown bucket stays below a threshold. Recover by rebuilding the affected partitions after the dimension is correct.
An upstream field starts arriving as an empty string instead of null, or vice versa.	An entire category disappears from one day forward, with a clean step in the chart at the deployment boundary.	A `WHERE region IS NOT NULL` filter, or a join predicate, silently changes meaning. No schema check fires because the type did not change (Nullability & Defaults, Semantic Changes).	Enforce a contract on values, not only on types. Recover by reprocessing from raw with corrected handling — raw has the records, which is why raw exists.
A partition is written under a path or value the reader does not scan.	A whole day or region reads as zero. Row-level tests pass because there are no rows to fail them.	A date format change, a time zone shift at the writer, or a partition value with different casing. The data is in the lake and no query looks there (Partitioning).	Assert the expected partition set before publishing. Recover by rewriting the partition to the correct path — the bytes were never lost.
The source hard-deletes rows, or a retention job at the source removes history.	Historical counts change retroactively. A report rerun for last quarter returns a different number than it did last quarter.	Your extract reflects current source state rather than the state at the time. Nothing in your pipeline is wrong (Keeping Raw History: The Recovery Position and the Liability).	Keep raw history so historical periods stop depending on the source still holding them. This is prevention only; once the source has deleted and you did not retain, the record is gone.

Localising the loss to one arrow

GENERALThe three-query shape works against any SQL-addressable stack. Where the source is a SaaS API rather than a database, step one is replaced by the provider's own reported totals, which are usually available and usually the only ground truth you have.

The investigation is a binary search over hops. You know the source has the rows and the serving table does not; between them are four to six arrows, and each one either preserved the record or did not. Two or three queries settle it, provided each hop can be counted for the same period at the same grain.

The grain caveat is not pedantry. A CDC stream counted as though its rows were orders will appear to have *more* records than the source, which reads as a surplus rather than a loss and sends the investigation in the wrong direction. Count distinct business keys at every hop, not rows (Grain: What Does One Row Represent?).

Then sample. Twenty actual missing keys tell you more than any aggregate: they share a country, or a status, or an hour, or a product created last Tuesday. That shared attribute is usually the mechanism, and it is far faster to see than to deduce.

Localise, then sample

1-- 1. Which arrow lost them? Count distinct business keys per hop,
2--    for one closed day, at the same grain everywhere.
3SELECT 'source'    AS hop, COUNT(DISTINCT order_id) AS keys FROM src.orders           WHERE order_day = DATE '2026-03-17'
4UNION ALL SELECT 'raw',    COUNT(DISTINCT order_id)        FROM raw.order_changes     WHERE order_day = DATE '2026-03-17'
5UNION ALL SELECT 'staging',COUNT(DISTINCT order_id)        FROM stg.orders            WHERE order_day = DATE '2026-03-17'
6UNION ALL SELECT 'fact',   COUNT(DISTINCT order_id)        FROM marts.fct_orders      WHERE order_day = DATE '2026-03-17'
7ORDER BY 1;
8 
9-- 2. Which rows, exactly? An anti-join against the source, not a count.
10SELECT s.order_id, s.status, s.country, s.created_at, s.updated_at
11FROM   src.orders s
12LEFT   JOIN marts.fct_orders f ON f.order_id = s.order_id
13WHERE  s.order_day = DATE '2026-03-17'
14  AND  f.order_id IS NULL
15ORDER  BY s.created_at
16LIMIT  50;
17 
18-- 3. What do they share? The answer is usually visible in one group-by.
19SELECT s.status, s.country, s.payment_method,
20       DATE_TRUNC('hour', s.created_at) AS hour,
21       COUNT(*) AS missing
22FROM   src.orders s
23LEFT   JOIN marts.fct_orders f ON f.order_id = s.order_id
24WHERE  s.order_day = DATE '2026-03-17' AND f.order_id IS NULL
25GROUP  BY 1, 2, 3, 4
26ORDER  BY missing DESC;

Query 3 is the one that ends most investigations. Missing rows clustered in a contiguous set of hours is an ingestion outage; clustered in one payment_method is a join or a filter; clustered in the last hours of the day is lateness; spread evenly across everything is a watermark.

The checks that would have caught it — and what they would still miss

Missing rows are the failure class with the best available detection, and also the one most often left undetected, because the check that finds them is the only one that requires touching the source system. Every other data test can run entirely inside the warehouse; reconciliation cannot, and that is precisely why it is worth building.

The four checks below form a ladder of cost and coverage. Volume monitoring is free and catches only large losses. Per-hop counts are nearly free and localise but do not detect. Reconciliation detects everything at the ends and costs a source scan. The anti-join detects and identifies and costs the most.

Choose deliberately, and write down what you are choosing not to see. A platform with volume monitoring alone has decided that any loss under its threshold will be discovered by a consumer, which is a defensible decision and a terrible surprise if nobody ever made it explicitly (Quality Alerting).

Detecting a shortfall, in ascending order of cost

Check	Expresses	Catches	Still misses
Daily volume against the dataset's own history	Today looks like a normal day for this dataset.	A large contiguous loss — an ingestion outage, a dropped partition, a source that stopped.	Any loss smaller than the natural day-to-day variation, which includes almost every join and filter bug. Also fires falsely on genuine quiet days, and gets widened until it stops firing (Volume Anomalies).
Row count per hop, per run	No arrow in the pipeline lost records this run.	Losses inside your own pipeline, localised to the exact hop, with no source access required.	Everything that never entered the pipeline. A CDC gap looks perfectly consistent hop to hop, because every hop faithfully processed a set that was already short (Pipeline Metrics).
Reconciliation on count and a summed measure, for a closed period	The end of the chain agrees with the source about how much happened.	Every kind of loss and every kind of duplication, including the ones that started before your pipeline (Reconciliation).	Open periods, where lateness is indistinguishable from loss. Losses that a duplicate coincidentally offsets. Any measure it does not sum. Anything the source itself lost before you read it.
Anti-join against source keys for a closed period	These specific records are absent, and here they are.	Everything reconciliation catches, plus the pattern in what is missing — which is the diagnosis, not just the detection.	The same blind spots as reconciliation, and it additionally requires a genuinely unique key on both sides. A non-unique join key makes it return nothing and look reassuring.
Expected-partition-set assertion before publish	Every partition this period should contain exists and is non-empty.	A partition written to the wrong path, a partial load, a period where one region's job silently produced nothing (Atomic Publish).	A partition that exists and is wrong. It asserts presence, never content, and a partition with one row passes it exactly as well as one with a million.

Only the third and fourth rows can detect a loss that happened before your pipeline, and both of them require reading the source. That single property is why so many platforms can localise a shortfall perfectly and never notice one.

How to build it

Most important first.

Reconcile against the source on a schedule, for closed periods, on both row count and a summed measure. This is the only check that observes both ends of the chain at once and it is the cheapest one you can build (Reconciliation).
Emit a row count at every hop of every run into one table, so the per-hop chart exists by default rather than being assembled during an incident (Pipeline Metrics).
Advance the high-water mark from a source-side monotonic sequence — a log position, a commit LSN — rather than from a wall-clock column, or overlap the window and deduplicate (The High-Water Mark, CDC vs Polling).
Prefer left joins to dimensions with an explicit unknown-member row, so a missing key produces a visible unknown bucket instead of silently deleting the fact (Dimension Tables, Surrogate Keys).
Monitor connector lag against the source's log retention, not against zero. The alert that matters is "this connector is close to falling outside the window from which it can still recover" (CDC Failure Modes and the Retention Deadline).
Publish partitions atomically and assert the expected partition set exists before a consumer reads. A missing partition is a shortfall that no row-level test will find, because the rows are not there to be tested (Atomic Publish, Partial Failure).

What this actually promises

Naming the guarantee you do not have is worth more than naming the one you do — everything downstream inherits the weakest promise in the chain.

Ingestion typically guarantees at-least-once delivery of what it captured. It guarantees nothing about what it failed to capture, and the distinction is the whole lesson (At-Least-Once Delivery).
A log-based connector guarantees no gaps while its position stays inside the retained log. Past retention that guarantee is void, and the failure is silent because the connector simply resumes from wherever it can.
A batch extract guarantees whatever its predicate captures. If the predicate is time-based, the guarantee is only as strong as the source's clock and commit ordering, which is weaker than almost everyone assumes (Incremental Extraction).
A transformation guarantees only what its tests assert. An inner join is a silent filter and nothing in SQL will warn you that it removed a third of the input (Data Tests).
Completeness is never inherited. It is measured, by comparing against the source, or it is unknown (The Dimensions of Data Quality).

Can I trust it?

A green pipeline is evidence that code ran. These four fields are the evidence that the data is right.

The check that would catch this

The definitive check is an anti-join reconciliation: for a closed period, list the source keys with no counterpart in the serving table. It gives you a count, a sample of the actual missing records, and usually an obvious pattern in what they have in common.
It misses everything in an open period, anything where the source itself lost the record before your extract, and any case where the source and the serving table are wrong in the same way.
Add a per-hop row-count assertion so a loss localises without a second investigation, and an expected-partition-set check so a missing partition is not mistaken for a quiet day (Data Tests).

Freshness

A shortfall in an open period is usually lateness rather than loss, and the two look identical at the moment you notice. Waiting for the period to close is a legitimate diagnostic step and a genuinely hard one to take while an executive is asking (Late-Arriving Data).
If the numbers converge as the period matures, the mechanism is lateness and the fix is a wider window or a revision policy. If they never converge, the rows were lost and the fix is upstream.
Alerting on completeness for open periods produces constant false positives. Reconcile closed periods and monitor volume anomalies for open ones — two checks with two different tolerances (Freshness Checks).

When the schema or meaning changes

A schema change is a common cause of a shortfall without ever failing a schema check: an added enum value falls into an ELSE branch that filters it out, or a column that used to be null becomes an empty string and stops matching IS NOT NULL (Nullability & Defaults, Enum Evolution: The New Value That Broke Old Clients).
Changing the partition key or its format orphans historical partitions. The old data is still there and no reader looks at it, which is a shortfall that reconciliation catches and volume monitoring does not, because the drop is instantaneous rather than gradual (The Partitioning Decision).
A source system introducing soft deletes, or switching from hard to soft deletes, changes what "missing" means. Rows that used to disappear now persist with a flag, and every downstream count changes in the opposite direction (What a CDC Event Contains).

How to re-run this safely

If raw still holds the records, recovery is a bounded reprocess of the affected range, written to a scratch location, validated against the source and published atomically (Planning a Backfill, Reprocessing vs Retrying).
If the records were never captured, recovery means going back to the source: a re-extract of the affected range, a replay from the log if it is still inside retention, or a fresh snapshot if it is not (Replay from the Log, Snapshot and Stream: the Bootstrap Problem).
Reset the high-water mark deliberately and with a range, not by deleting it. A watermark reset to zero triggers a full re-extract that will take longer than the incident and may not be idempotent (The High-Water Mark).
If the source itself has since mutated — rows updated or deleted after your gap — a re-extract reconstructs current state rather than what was true then, and the period is recoverable only in the sense that it is now internally consistent (Keeping Raw History: The Recovery Position and the Liability).

What can go wrong

Failure modes

The re-run "fixes" the count by re-processing the rows that were already there, so the total looks better and the missing records are still missing.
The reconciliation query joins on a key that is not unique on one side, so the anti-join returns nothing and the incident is declared resolved.
The backfill that recovers the gap also overwrites a period that was correct, turning one incident into two (What Backfills Break).
The high-water mark is reset, the extract runs for eleven hours, and the pipeline is now behind on the current day as well.
The gap is recovered but no test is added, so the same watermark bug loses another window six weeks later and is diagnosed from scratch.
Volume monitoring is tuned wide enough not to alert on the loss, because it was previously tuned to stop paging on ordinary weekend variation (Alert Fatigue: The Page Nobody Reads).

Misreads

"The task succeeded, so nothing was lost." Task success reports that the code ran. Every mechanism in this lesson produces a successful run by construction (The Pipeline Succeeded. The Data Is Wrong.).
"The source has the rows, so we can always get them back." Only if you can still identify which rows and the source has not mutated since. A re-extract after the fact gives you current state, not the state you missed.
"Volume monitoring would have caught it." It catches losses large enough to exceed its threshold. A steady 3% loss from a badly-keyed join is invisible to volume monitoring forever, and reconciliation finds it on the first run.
"It is a small shortfall, so it is a small problem." A shortfall concentrated in one segment can be the entire signal for that segment while being a rounding error in the total.
"Re-running fixed it." Verify what changed. A re-run that reprocesses the same input and produces a different count means the pipeline is non-deterministic, which is a worse finding than the shortfall.

Operating it

How you see it in production

Row count per hop per run, plotted together. A step down between two adjacent hops is the whole diagnosis, and its absence rules out everything inside your pipeline (Pipeline Metrics).
Connector lag measured against log retention, so the alert fires while recovery is still possible rather than after (CDC Failure Modes and the Retention Deadline).
Daily volume against the same weekday historically, per dataset and per major dimension — a loss confined to one country is invisible in the total and obvious per country (Volume Anomalies).
The expected-partition-set check: the list of partitions a period should have, compared with the list it does have (Partition Pruning).
Anti-join counts against the source for the last closed period, recorded as a metric rather than run ad hoc (Reconciliation).

What changes at 10x and 100x

At 10x volume the anti-join stops being casual. Reconcile on aggregates by day and drill into keys only for the days that disagree, rather than joining the full key sets (Reconciliation).
At 100x, per-hop counting must be an aggregate emitted by the job rather than a separate counting query, or the observability costs more than the pipeline.
More sources multiply the surface. Each one has its own extraction semantics, its own retention and its own way of losing rows, and a single platform-wide completeness dashboard is what keeps that from becoming unmanageable (The Data Quality Dashboard).

What drives cost here

Reconciliation costs a scan of the source and a scan of the serving table for the period. Bound it to closed periods and to keys plus one measure rather than full rows, and it stays small against the datasets it protects (Scan Cost).
Per-hop row counts are nearly free — a count on a set the job already holds in memory — and are the highest ratio of diagnostic value to cost anywhere in this domain.
Recovery is the expensive part: a re-extract competes with the source system, and a backfill competes with the current day's pipeline for the same compute. Both are cheaper if the range is bounded, which requires knowing exactly which period is affected (What Backfills Break).

What this approach costs

Reconciliation requires read access to the source system and periodic scans of it, which is exactly the coupling that moving analytics off production was meant to remove. Scoped, scheduled aggregate queries against a replica are the usual compromise (Workload Isolation).
Overlapping extraction windows make loss much less likely and duplicates much more likely. You are choosing which failure you would rather have, and duplicates are the better choice because deduplication is possible and un-losing a row is not (Deduplication).
Wide late-arrival windows reduce false shortfalls and delay every final number. Every consumer waits longer so that fewer of them are wrong (Watermarks).

Where this applies

Almost nothing here is universal. These labels say what each claim is specific to, and where a different engine, format, warehouse or scale would differ.

GENERALThe never-captured versus dropped-after-capture split holds in every stack and decides the recovery path. What differs is which side is more likely: batch-extract platforms lose rows at the watermark, log-based ones lose them at retention.
SOURCE-SPECIFICPostgres logical replication holds the source's WAL while a slot lags, so the gap risk becomes disk pressure on the source instead; MySQL binlog and Mongo oplog expire on their own schedule regardless of consumer position, so a lagging connector silently loses changes there.
SIMULATEDThe claim that a connector outage and a late-arrival tail produce different check signatures comes from the model in src/de/sim/pipeline.ts, asserted in scripts/de-sim.test.ts. It is a property of that teaching model, not a measurement of any production system.

Where the depth lives

This domain teaches how data moves and how you know it arrived intact. It hands the rest off by name.

Databasewrite-ahead-logging mvcc transactions-and-acid replication

Backendat-least-once-delivery dual-write-problem transactional-outbox

Observabilityreplication-lag symptom-to-signal alert-fatigue

Concurrencyordering-guarantees

Domains that do not exist yet

— Distributed Systems owns what a delivery guarantee actually promises across a network partition, and why a consumer that has fallen outside a retained log cannot recover by trying harder.