The audit calls every row excluded

The audit says three rows were excluded. A teammate checks it the quick way: six source rows minus three loaded is three. Same answer. Now suppose the load had silently lost TG-106. Loaded drops to two, source minus loaded says four were excluded, and the arithmetic still balances, because it was built to. A count worked out from the other two can never disagree with them.

So count each population from its own table. Source rows come from stg_items, loaded rows from items, excluded rows from the decisions in load_audit. When source equals loaded plus excluded with three independent counts, the equation means something. When it fails, the gap is a row you can go and find.

SELECT (SELECT COUNT(*) FROM stg_items) AS source_rows,
       (SELECT COUNT(*) FROM items) AS loaded_rows,
       (SELECT COUNT(*) FROM load_audit WHERE decision = 'Excluded') AS excluded_rows;

Watch the excluded count itself. COUNT(CASE WHEN decision = 'Excluded' THEN 1 ELSE 0 END) says six, because COUNT counts every value that is not NULL, and 0 is a value. Count the rows with a WHERE, or let the CASE fall through to NULL.

The equation fits this kind of load: a first load into an empty table, one source row to one target row. An append to a table that already has rows, a file with duplicates you merge, a file that becomes two tables: each needs its own mapping, written down before the counts mean anything.

Northshore Coffee copies its 204 orders into a new table, and the copy reports 204 rows. One order came in twice and another never arrived. The count is perfect, the sales total is twelve dollars short, and the count will never tell you.

A reconciliation needs evidence about the rows as well as the totals. COUNT(DISTINCT id) beside COUNT(*) shows a duplicate. A SUM of the amount shows money that moved. And then the identities themselves: the three serials load_audit marks Loaded should be exactly the three serials in items, carrying the cleaned values you meant.

SELECT a.source_row, a.serial, i.id AS loaded_id
FROM load_audit a
LEFT JOIN items i ON i.serial = a.serial
WHERE a.decision = 'Loaded'
ORDER BY a.source_row;

Write a baseline down before the load and compare after it: the row count, the count of distinct keys, a total, the first and last key. Two numbers that match prove that two numbers match. Rows that match prove the load.