The Rows That Never Reached the Reject Table
4 min read

The Rows That Never Reached the Reject Table


I was following a data-quality check backwards through a pipeline when an awkward question came up: what happens to a row that the source query never returns?

The obvious answer is that it doesn’t get loaded. The less obvious answer is that it doesn’t get rejected either. It isn’t in the reject table, the accepted table, or the validator’s denominator. As far as that particular quality report is concerned, the row never existed.

This is easy to miss because the extraction query looks like ordinary housekeeping. You add a date boundary to avoid reading years of history. You add a TRY_CONVERT to keep malformed dates away from the expensive transformation. You put the predicate close to the source so the database does less work. All reasonable decisions, separately.

Then someone asks whether every bad input was captured, and the dashboard says zero rejects.

Three populations, one misleading count

Imagine a feed of 10,000 records. An extraction query selects 9,700 because 300 have an invalid or out-of-scope business date. The validator receives 9,700, accepts 9,680, and rejects 20.

The validator’s arithmetic is correct:

9,700 received = 9,680 accepted + 20 rejected

But it cannot establish that 10,000 source records were accounted for. Its world begins downstream of the filter.

A source set branches into selected and excluded records before validation; only selected records contribute to accepted and rejected counts.

The reject table describes the selected population, not necessarily the source population.

There are legitimate reasons to exclude rows. If a job rebuilds only the current quarter, older records belong outside its work. That’s not a quality failure. A missing or malformed date is different: the row cannot even be assigned to a quarter. If both cases disappear behind the same WHERE, the operational evidence becomes ambiguous.

The filter is a policy, even when it looks like plumbing

Consider a simplified source query:

SELECT *
FROM source_orders
WHERE TRY_CONVERT(date, invoice_date) >= @window_start;

This expression makes three decisions at once. A valid date inside the window is eligible. A valid date before it is outside the run’s scope. An invalid date evaluates to NULL, fails the predicate, and vanishes from the result.

Sometimes that’s exactly what you want for a bounded rebuild. What bothered me was allowing the same predicate to be both the extraction boundary and the only apparent record of eligibility. You can’t tell from a clean reject table whether the source was clean or whether the source query was selective.

The cheapest improvement isn’t necessarily to copy every excluded row into a new error table. For a large source, that can turn a useful pushdown filter into an expensive full scan. It is to make the contract explicit:

  • Out of scope: valid records intentionally outside this run’s boundary.
  • In scope: records delivered to the validator, where accepted and rejected counts must reconcile.
  • Unclassifiable: records that cannot be assigned to either population because the fields used for eligibility are invalid.

Those categories can be measured at the source, through a separate profiling query, or through a periodic control check. The right mechanism depends on cost and what the source can expose. The important thing is that the extraction count and the validation count must not pretend to answer the same question.

Where I would put the check

For a batch with reliable source snapshots, I like two explicit invariants:

source eligible = delivered to staging + extraction failures
staged rows = accepted + rejected

The first may be an eligibility audit rather than a row-by-row reject stream. The second belongs in the validator. When the source is changing during extraction, even those equalities need a stable cutoff or snapshot; otherwise counts from two different moments can disagree without a bug.

This is also why I no longer treat 0 rejected as a synonym for 0 bad source rows. It means no records seen by that validator were rejected. A useful result, but a smaller claim.

The distinction is easy to explain and surprisingly easy to lose in a pipeline diagram. Draw the filter before the validator. Label the row counts on both sides. If the excluded branch has no observable outcome, you’ve found the part of the system the green quality dashboard cannot see.

The related failure is a missing column that didn’t crash. There, the reader saw the row and misunderstood it. Here, the reader never sees the row at all.

Next in “Microsoft Fabric migration and data correctness” Two Customer IDs, Both Correct →