Answers · Where the data lives

How can I prove nothing was lost without comparing every single row?

In shortSample a few thousand rows drawn from the source, look each one up in the destination, and add one total row count. That proves loss and duplication without reading every row.

You do not compare every row, and you do not need to. Draw a random sample of a few thousand identifiers from the source, look each one up at the destination and compare the fields, and run one count over the whole window as well. The count catches the large losses and duplications the sample could step past, and the sample catches the corruption a count cannot see, because a row with one field wrong still counts as one row.

The size of the sample

Let $p$ be the defect rate you would want to hear about, meaning the share of rows that are lost, duplicated or corrupted. Sample $n$ rows at random. The sample comes back clean only if every one of those $n$ rows is good, so the chance of catching at least one defect is one minus the chance of catching none:

P = 1 - (1-p)^n \approx 1 - e^{-np}

Set the confidence at 0.95, which most people find adequate for a daily check. Then $np$ has to be about 3, because $e^{-3} = 0.0498$ and $1 - 0.0498 = 0.95$ . That leaves a rule you can do in your head:

n = \frac{3}{p}

FIG 1

defect rate p	rows to sample n	share of a ten million row table
1 in 100	30	0.0003%
1 in 1,000	3,000	0.03%
1 in 10,000	30,000	0.3%
1 in 100,000	300,000	3%

The striking part is what is not in the formula: the size of the table. If you care about a one in a thousand problem, three thousand rows is the sample whether the table holds a million rows or a billion. Against a million rows that is three thousand over a million, which is $0.3$ per cent. Against a billion it is three thousand over a billion, which is $0.0003$ per cent. Same three thousand lookups, same few minutes, and the cost of the check stops moving while the table keeps growing.

What the sample cannot see

The obvious way to get this wrong is to sample the destination and check that each sampled row exists at the source. Loss is then invisible by construction: the missing row is not in the destination, so it can never be drawn into the sample, and you will get a clean result from a table that is short by thousands of rows. Always draw the identifiers from the source and look them up at the other end. Sampling with a limit is the second version of the same error, because a limit returns whatever the storage engine found convenient, usually insertion order, which is the opposite of random and puts every defect from a bad backfill outside the sample.

The other natural guess is that reconciliation means a full row by row diff, and that anything less is a spot check that might miss something. That belief is what stops the check being built: a full comparison is a full read at both ends, so it gets scheduled weekly, then monthly, then quietly switched off. The row by row diff also answers a different question. It tells you which rows and which fields are wrong, which is what you want once a count has gone red, not what you want every morning at four. Run it on the failing window, not on the table.

Counting alone has its own gap, and it is wider than it looks. One row lost and another duplicated in the same window leave the count identical at both ends, and the two failures are not independent: both cluster around the same restart or failover. Add a corrupted field, where the row count is untouched, and a reordering, where every row and every value is right and only the positions are wrong, and a green count covers four failures at once. Compare a sum of a numeric column or a hash of the concatenated fields as well; that is the check that turns Wednesday red when Monday and Tuesday were green.

Where it stops

A clean sample is a statement about a rate, not a proof of zero. Three thousand clean rows out of a table is consistent with a defect rate below roughly one in a thousand and says nothing about one in ten thousand. If the guarantee you need is stronger than that, the full comparison still has a place, run in chunks across days rather than all at once. And the count should stay in place regardless of the sample size, because the two have different blind spots: the count sees a whole window vanish, and the sample sees the single corrupted field the count cannot.

Finally, the failure that survives longest is the check itself. A job that stopped being scheduled, a credential that expired, a query that now times out: all of these produce silence, and silence is indistinguishable from a pass. Write a result row on every run, pass or fail, with the window and the numbers found, and alarm on the absence of that row rather than only on failure. Keep the history rather than overwriting it, because a year of daily counts turns the next investigation into a comparison instead of a guess.

Worth keeping

A sample of about three divided by the defect rate gives roughly a 95 per cent chance of catching at least one defect, and that number does not depend on the size of the table.
Draw the sample from the source, never from the destination, because a lost row cannot be sampled where it does not exist.
A check that stops running looks exactly like a check that passes, so alarm on the absence of a result and not only on failure.

Where this goes next

This question is one step of Moving Data Without Losing Any, a course that works through its subject from the beginning. The page that teaches this part of it is Proving Nothing Was Lost, and it reads the whole way through whether or not you have an account.

What an account changes is the practice: problems standing under every idea, marked with a reason rather than a tick, in a copy of the course that is yours.

Read that page The whole course