DEV Community

KAREN
KAREN

Posted on

Same Row Count, Missing Records: 4 Ways a Count Check Can Fool You

A common check after a batch job is to compare two numbers: how many records went in, and how many came out. If they match, the job is marked healthy.

That check is useful as an alarm. But it is not proof. Two lists can have the same length and still hold different records.

A tiny made-up example

The run was supposed to deliver four orders:

  • Expected: A, B, C, D
  • Found in the destination: A, A, C, E

Count check: 4 expected, 4 found. Looks fine.

Compare by ID instead:

  • missing: B, D
  • duplicate: A (written twice)
  • unexpected: E (not part of this run)

Three problems, and the count hid all of them.

4 ways the count can match while records are missing

1. A retry wrote duplicates that cancel out a drop

One batch failed halfway and was retried. The retry wrote some rows a second time, while a different set of rows never made it. The extra copies fill the gap in the total.

2. Rows from outside the run filled in

Late rows from the previous window, or rows near a time zone boundary, landed in this run's scope. They add to the count, so real gaps in this run stay invisible.

3. The key changed shape on the way

The record arrived, but its ID did not survive intact: 00123 became 123, a trailing space was added, or letters changed case. The count is right, but a lookup for 00123 finds nothing. To anyone searching by ID, the record is missing.

4. A filter removed rows while another step added some

One step skipped records it should have kept. Another step added test rows, or brought back rows that had been deleted. Minus a few, plus a few, same total.

How to check by ID

First, build the list of IDs that this run was expected to deliver, from that run's input or a saved copy. Do not use today's version of the source.

Then check three lists separately. A plain SQL sketch (function names vary slightly between databases):

-- missing: expected, but not in the destination
SELECT e.id
FROM expected_ids e
LEFT JOIN destination d ON d.id = e.id
WHERE d.id IS NULL;

-- unexpected: in the destination, but not expected
SELECT d.id
FROM destination d
LEFT JOIN expected_ids e ON e.id = d.id
WHERE e.id IS NULL;

-- duplicates: the same ID written more than once
SELECT id, COUNT(*) AS copies
FROM destination
GROUP BY id
HAVING COUNT(*) > 1;
Enter fullscreen mode Exit fullscreen mode

Before trusting the result, look at the shape of the keys on both sides:

-- IDs with extra spaces
SELECT id FROM destination WHERE id <> TRIM(id);

-- how many IDs of each length (run on both sides and compare)
SELECT LENGTH(id) AS id_length, COUNT(*) AS ids
FROM destination
GROUP BY LENGTH(id);
Enter fullscreen mode Exit fullscreen mode

If one side has IDs of length 5 and the other has length 3, leading zeros were probably dropped somewhere. Decide on one rule and write it down. Do not quietly clean the keys on one side only, or you can hide the very problem you are looking for.

If the destination has a known delay before new rows are visible, wait for it before you call anything missing.

Then follow one missing ID

Pick one missing ID and look for it at every stage. Another made-up example, for order B:

Stage Result Evidence
Input found in the saved input for this run
Filter found not excluded
Batch 2, first try absent batch failed with a timeout
Batch 2, retry absent retry log lists batch 3's rows, not batch 2's
Destination absent not in the export

First known gap: the retry picked up the wrong batch. That also explains the duplicates. If you have no evidence for a stage, write "unknown" instead of guessing.

So, are counts useless?

No. A count is a cheap first alarm, and a mismatch is always worth a look. Just do not stop there: a matching count means "nothing obviously broke," not "everything arrived."


Have you seen a count check pass while records were missing? I would like to hear what caused it.

This post was drafted with AI and checked by a human before publishing.

Top comments (0)