The Check Takes Four Minutes a Day and Almost Nobody Runs It

Last timeThings That Arrive Late

Every failure in this course is detectable by comparing the two ends. Counts catch two of the four, values catch the rest, and a sample is enough.

Which check finds which failure

The first lesson named four ways data moves wrongly: loss, duplication,

corruption and reordering. Every lesson since has been about preventing one or

more of them. This one is about detecting them anyway, on the reasonable

assumption that something in the design is wrong and nobody knows which part.

The checks available are not interchangeable, and the mapping is worth having

precisely because the cheapest check is also the most popular and covers half the

cases.

FIG 1Each failure and the cheapest check that finds it

	a count at each end	a sum or hash of values	a row by row comparison	a check on sequence or o
loss	1	1	1	0
duplication	1	1	1	0
corruption	0	1	1	0
reordering	0	0	1	1

The two marked cells are the gaps that matter. A count cannot see corruption, because a row with one wrong field still counts as one row, and a row by row comparison cannot see reordering unless it compares positions, because every row is present and correct. Those two failures are therefore invisible to the checks most pipelines actually run.

Counts. Compare the number of records at each end for a bounded range, by

event time rather than arrival. Cheap, fast, and it catches both loss and

duplication. It is the right first check and it should be running everywhere.

A sum or a hash. Total a numeric column at each end, or hash the

concatenated values of the fields that matter and compare the hashes. This

catches corruption, including the cases counts cannot: a truncated string, a

rounded amount, a timezone applied twice, a field that silently became null. It

costs a full read at each end for the range being checked, which is why it gets

run on a sample or on a chunked schedule rather than continuously.

Row by row. Compare identified rows field by field, which tells you not only

that something is wrong but which rows and which fields. This is the diagnostic

tool rather than the monitor: too expensive to run across everything, exactly

what you want once a cheaper check has gone red.

Sequence or order. Compare positions rather than contents: are the sequence

numbers contiguous, did anything arrive out of order, is any position missing

from the middle. Almost nobody runs this, and it is the only thing that detects

reordering, which is why reordering is the failure that survives longest in

production systems.

FIG 2The four checks, in the shape they are usually written

plaintext

COUNTS, by event time, over a closed window

  select count(*) from source
   where happened_at >= :from and happened_at < :to

  -- and the same against the destination, then compare

VALUES, a sum for numbers and a hash for everything else

  select sum(amount) from source where ...
  select md5(string_agg(id || field_a || field_b, order by id))

ROW BY ROW, on a sample of identifiers

  select id, field_a, field_b from source where id in :sample

  -- same from the destination, compare field by field

SEQUENCE, for a stream carrying positions

  select max(position) - min(position) + 1 - count(*) as missing
    from destination where position between :from and :to

Four queries, none of them complicated, and the last one is the one almost nobody writes. Note that the first takes its range from event time rather than arrival, for the reason the previous lesson gave: a range by arrival puts the late records in the wrong window and the two ends then disagree for a reason that is not a failure. The sequence check reads as arithmetic on positions: if the range is contiguous the expression is zero, and any other answer is the number of positions missing from the middle.

Why counts are not enough

Equal counts feel like proof and are not. Three cases make the point.

A row arrives with one field wrong. The counts match exactly. Nothing about the

number of rows is affected by a truncated name, an amount rounded to the wrong

precision, or a timestamp that passed through a timezone conversion twice.

A duplicate and a loss in the same window. One record was lost and another

duplicated, so the counts match and two separate things are wrong. This is less

of a coincidence than it sounds, because both failures tend to cluster around the

same events: a restart, a redeploy, a failover.

And a reordering. Every row present, every value correct, the counts identical,

and a sequence of state changes applied in the wrong order so the final state is

wrong. For anything that processes transitions rather than values, this is the

failure that produces the least explicable data, and no count or sum detects it.

The lesson stops here

4 more paragraphs to go

You have read the opening. The rest of the argument, the problems that check whether it landed, and the lines worth keeping at the end all come with a plan.

The first lesson of every course in the library reads the whole way through, free, so you can see exactly what the rest of them are.

See the plan The contents

This is the reading half

Starting the course gives you your own copy of it. Every idea on every page has problems standing under it, marked with a reason rather than a tick, and any sentence you do not believe can be opened and argued with. None of that can happen on a page nobody owns.

The contents

The Check Takes Four Minutes a Day and Almost Nobody Runs It

Which check finds which failure

Why counts are not enough

4 more paragraphs to go

This is the reading half

Read alongside