Practise · The audit calls every row excluded

Opening your saved work…

The reading

Count every population on its own

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;

6, 3 and 3: three counts from three tables, and 3 + 3 = 6. Open in the playground

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.

Rule of thumb. Count source, loaded and excluded separately. A count you worked out from the other two cannot catch a lost row.

Going deeper (optional, not on the exam)

Near its end the kit's load.sql prints a reconciliation block: one row per target table, stacked with UNION ALL, gear_types first with its two hand-typed rows, then items, followed by the excluded rows and their reasons. Your client's load.sql closes the same way, one row per table it fills.

Prove more than a count

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;

Every accepted serial finds its loaded row. A NULL in loaded_id would be a row the load lost. Open in the playground

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.

Rule of thumb. A count can stay right while the rows go wrong. Compare keys and totals as well.

The audit calls every row excluded · source = loaded + excluded, counted independently · level 4 of 6

Prove more than a count

Level 4: Write it yourself · Exercise 2 of 2

orders_import is a copy of orders with the expected 204 rows, but one order arrived twice and another never arrived. Set the baseline beside the copy: two rows, orders first and then orders_import, with the columns table_name, order_rows, distinct_ids and total_amount, the sum of total_amount rounded to cents.

Finish line: 2 rows · columns table_name, order_rows, distinct_ids, total_amount · in the order the question gives

Data: Northshore Coffee.

I would like help with this exercise

Table & column names only · ↑ ↓ choose · Tab insert · Esc dismiss

Open in the playground

Query results

Run your query to see the result here.

Practice here is evidence for you and your instructor, never a grade. Grades live in Canvas.