How Snowflake Teams Identify Missing, Duplicated or Late-Arriving Records

A Snowflake estate rarely fails loudly on data quality. It fails quietly, in three recurring ways. Records go missing when a batch partially fails or a source feed drops and nobody notices. Records duplicate when a load is re-run and inserts the same rows again. And records arrive late, after the period they belong to has already been reported, so the number was "final" before the data was complete.

What these share is invisibility. The load succeeds, the pipeline goes green, the dashboard renders. The gap, the double-count or the late batch is discovered weeks later when someone reconciles against the source and the numbers do not tie — usually at month-end, in front of finance, which is the worst possible moment.

Catching them is not about being more careful manually. It is about scheduled checks that run every time data lands and tell you the morning something drifts. Each of the three failures has a specific, cheap check that catches it.

Missing data is caught by reconciliation. For each important table, compare the row count and key control totals — summed amounts, document counts, quantities — in Snowflake against the source system, on a schedule, and alert when they diverge beyond a tolerance. If the source has 10,000 orders for the day and Snowflake has 9,840, the gap is flagged the morning it happens, not at close.

Control totals matter as much as row counts. A load can have the right number of rows but the wrong total if some rows landed truncated or with nulls, so reconciling a summed amount against the source catches a class of problem a row count misses. Together, count plus total is a strong signal that the data is complete.

The reconciliation is cheap to build and is the single highest-trust check on the estate. "The Snowflake order count and total match the source to the row and the cent as of this morning" is a statement finance can rely on; discovering a gap weeks later is what erodes trust in the whole platform.

Duplicates come from loads that re-run or re-ingest, and they are caught two ways: prevent them at load time, and detect them with checks. Prevention is a MERGE keyed on the business key rather than an append, so re-loading the same record updates the existing row instead of inserting a second — an idempotent load, where running twice leaves the table the same as running once. This removes the most common source of duplicates by construction.

Detection is a uniqueness check: scheduled queries that count rows per business key and flag any key that appears more than once. This catches duplicates that slipped past — a key that should be unique but is not, a grain that is finer than expected. Running the uniqueness check regularly turns a silent double-count into a flagged exception.

Snowflake streams help on the change-tracking side: a stream records what actually changed on a table, so a load can process genuine changes rather than blindly re-ingesting, which reduces the re-insert risk in the first place. Between an idempotent MERGE, a uniqueness check and streams, duplicates stop being the quiet inflator of every downstream total.

Late and stalled data is caught by freshness and arrival-lag checks. A freshness check asks "when did this table last receive data, and is that recent enough?" — so a feed that silently stopped is flagged rather than quietly serving stale numbers that look current. A table that should update hourly and last updated six hours ago is a problem worth knowing about before someone reports from it.

Arrival-lag checks handle the subtler case of data that does arrive, but late — after the period it belongs to was reported. Measuring the gap between when a record is dated and when it landed tells you how late your data genuinely runs, which both flags abnormal lateness and informs how long a period should stay open before it is treated as final.

These checks turn "the number was final but the data was not complete" into a known, bounded condition. You know a feed is current because freshness says so, and you know how late data realistically arrives because lag says so — so a period is reported final when the data actually is, not when the calendar says the month ended.

Data quality on Snowflake is a monitoring discipline, not an act of care. Reconcile counts and control totals against the source to catch missing records; prevent duplicates with an idempotent MERGE and catch any that slip through with uniqueness checks; run freshness and arrival-lag checks to catch stalled and late feeds; and alert on every breach so a drift surfaces the morning it starts. Snowflake streams support the change-tracking that underpins several of these.

The test is whether a gap, a duplicate or a stalled feed would reach a human the morning it happened or at month-end in front of finance. If the checks run on a schedule and alert on tolerance breaches, it is the former — a flagged exception resolved quietly. If data quality depends on someone noticing, it is the latter, and the platform is one reconciliation away from a trust problem. We build Microsoft-first by default and apply the same monitoring discipline on Snowflake where a client’s estate runs there — the checks differ in syntax, not in principle.