Agree what “the same” means before writing a join
Most reconciliations that go round in circles are comparing two different things correctly. Before any SQL, write down four things and get the owners of both sides to agree with them. This is not the metric definition, which should already be settled; it is what has to be true for two tables to be compared at all.
Then normalise. Timestamps go to one timezone before a date is derived from them, amounts to one currency at an agreed rate, and both sides are rounded the same way. And decide the NULL policy out loud: whether a NULL on both sides counts as equal, and whether a NULL on one side counts as a difference. The script below says yes to both.
- Grain: what one row is on each side. One row per order on one side and one per order line on the other cannot be compared until one is rolled up to the other.
- Key: the column or columns that identify a row at that grain, and that exist on both sides with the same meaning.
- Population: who is in and who is out. Test accounts, cancellations and internal users filtered on one side only appear as missing rows.
- Time window: which date decides membership, in which timezone, as a half-open range so no day is counted twice.
- Still arguing about what the number should count? Settle the definition first with the KPI definition template
The worked example, step by step
Illustrative data, not a real dataset. The orders below are invented to show the method, and planted with one of each problem the guide describes. Run the script as it is first and check each result against the expected output under it; then you know what a correct run looks like before you point it at your own tables.
One BigQuery Standard SQL script: the seven blocks below, in order, with the expected outputs as comments. It reads none of your tables and creates only temporary tables. No sign-up needed.
1. State the scope before anything else
The script starts with what was agreed: one row per order, matched on order_id, orders placed in September 2026, amounts in EUR. The window is half-open, so an order on the last day is counted once and the next month starts cleanly.
The tolerance is zero. Change it only when the business has decided how far apart two amounts may be and still count as equal; that is a decision, not a setting to make a check pass.
-- SQL data reconciliation: find missing, duplicate and changed rows.
-- BigQuery Standard SQL. https://lp-data.cloud/insights/sql-data-reconciliation
--
-- ILLUSTRATIVE DATA: the orders below are invented to show the method. They
-- are not from any client or real system. Run the whole script as it is first
-- and check every result against the expected output written under it.
--
-- It reads none of your tables and changes nothing: everything it creates is
-- a temporary table that disappears when the script ends. In the console, use
-- Run (not Run selection) and open each result from the list of statements.
-- Agreed scope. DECLARE has to come first in a BigQuery script.
DECLARE scope_start DATE DEFAULT DATE '2026-09-01'; -- inclusive
DECLARE scope_end DATE DEFAULT DATE '2026-10-01'; -- exclusive
-- How far apart two amounts may be and still count as equal. A nonzero
-- value is a business decision; zero means exactly equal.
DECLARE amount_tolerance NUMERIC DEFAULT NUMERIC '0';2. Put both sides in the same shape
Two temporary tables stand in for the two systems: source_orders for what the source recorded, model_orders for what the warehouse model produced. Amounts are NUMERIC literals, which are exact decimals; summing FLOAT64 can leave a difference of a fraction of a cent between two sides that agree.
With real data, this is where you normalise. Convert timestamps to the reporting timezone before deriving the date, convert to one currency at the agreed rate, and round both sides the same way. A difference that comes from comparing UTC with local dates, or gross with net, is not a data error, and it will bury the ones that are.
-- The two sides. To use your own data, replace each UNNEST with a SELECT from
-- your table that returns these four columns, already normalised to the same
-- timezone, currency and rounding on both sides.
CREATE TEMP TABLE source_orders AS
SELECT * FROM UNNEST([
STRUCT('A-0999' AS order_id, DATE '2026-08-31' AS order_date, NUMERIC '40.00' AS amount, 'paid' AS status), -- outside the scope
('A-1001', DATE '2026-09-01', NUMERIC '120.00', 'paid'),
('A-1002', DATE '2026-09-02', NUMERIC '75.50', 'paid'),
('A-1003', DATE '2026-09-03', NUMERIC '200.00', 'paid'),
('A-1004', DATE '2026-09-05', NUMERIC '49.99', 'paid'),
('A-1005', DATE '2026-09-08', NUMERIC '15.00', 'paid'),
('A-1006', DATE '2026-09-12', NUMERIC '30.00', NULL),
('A-1007', DATE '2026-09-15', NUMERIC '82.25', NULL),
('A-1008', DATE '2026-09-20', NUMERIC '60.00', 'paid')
]);
CREATE TEMP TABLE model_orders AS
SELECT * FROM UNNEST([
STRUCT('A-1001' AS order_id, DATE '2026-09-01' AS order_date, NUMERIC '120.00' AS amount, 'paid' AS status),
('A-1002', DATE '2026-09-02', NUMERIC '75.50', 'paid'),
('A-1003', DATE '2026-09-03', NUMERIC '210.00', 'paid'), -- planted: 10 more than the source
('A-1004', DATE '2026-09-05', NUMERIC '39.99', 'paid'), -- planted: 10 less, so the total cancels
-- planted: A-1005 is missing
('A-1006', DATE '2026-09-12', NUMERIC '30.00', 'paid'), -- planted: status NULL in the source
('A-1007', DATE '2026-09-15', NUMERIC '82.25', NULL), -- NULL on both sides counts as equal
('A-1008', DATE '2026-09-20', NUMERIC '60.00', 'paid'), -- planted: duplicate key
('A-1008', DATE '2026-09-20', NUMERIC '60.00', 'paid'),
('A-1009', DATE '2026-09-28', NUMERIC '15.00', 'paid') -- planted: extra order
]);
-- The same scope on both sides, applied before any comparison.
CREATE TEMP TABLE source_in_scope AS
SELECT * FROM source_orders WHERE order_date >= scope_start AND order_date < scope_end;
CREATE TEMP TABLE model_in_scope AS
SELECT * FROM model_orders WHERE order_date >= scope_start AND order_date < scope_end;3. Check uniqueness before any join
A join on a key that is not unique multiplies rows: a key twice on one side and twice on the other becomes four rows, and every total built on the join grows with it. So the script checks each side on its own first, and lists every key that appears more than once or is NULL.
Those keys are then excluded from both sides of every comparison that follows, and counted in the summary as excluded. They are not deduplicated. Keeping one of two rows is a guess about which one is right, and if the two rows differ, the guess decides the answer. Resolve them at the source, or, for a check that must pass before anything else runs, stop the script instead: there is an ASSERT for that at the end.
-- Result 1: uniqueness failures. Keys that appear more than once on a side,
-- or are NULL, cannot be matched one to one.
CREATE TEMP TABLE ambiguous_keys AS
SELECT side, order_id, COUNT(*) AS row_count, SUM(amount) AS amount_across_rows
FROM (
SELECT 'source' AS side, * FROM source_in_scope
UNION ALL
SELECT 'model' AS side, * FROM model_in_scope
)
GROUP BY side, order_id
HAVING COUNT(*) > 1 OR order_id IS NULL;
SELECT * FROM ambiguous_keys ORDER BY order_id, side;
-- Every comparison below uses only keys that are unique on both sides. An
-- ambiguous key is excluded from both sides and counted in Result 5, never
-- deduplicated.
CREATE TEMP TABLE source_unique AS
SELECT * FROM source_in_scope
WHERE order_id IS NOT NULL
AND order_id NOT IN (SELECT order_id FROM ambiguous_keys WHERE order_id IS NOT NULL);
CREATE TEMP TABLE model_unique AS
SELECT * FROM model_in_scope
WHERE order_id IS NOT NULL
AND order_id NOT IN (SELECT order_id FROM ambiguous_keys WHERE order_id IS NOT NULL);
-- Expected with the illustrative data:
-- side | order_id | row_count | amount_across_rows
-- model | A-1008 | 2 | 120| side | order_id | row_count | amount_across_rows |
|---|---|---|---|
| model | A-1008 | 2 | 120 |
4. Compare totals, and see what they hide
Totals are still worth comparing, as a quick signal that something is wrong. They are not evidence that nothing is. The first line shows the duplicate inflating the model by 60. Once the ambiguous key is set aside, both sides have seven rows and 572.74, and on the six keys found on both sides the totals agree to the cent. All of that is true, and the data still holds six planted differences: a missing order, an extra one, a duplicate key, two amounts that are wrong in opposite directions, and a status that is NULL on one side.
-- Result 2: row counts and totals at three levels. Equal numbers here do not
-- mean equal data; Results 3 and 4 are the evidence.
WITH totals AS (
SELECT 1 AS sort, 'all rows in scope' AS population,
(SELECT COUNT(*) FROM source_in_scope) AS source_rows,
(SELECT COUNT(*) FROM model_in_scope) AS model_rows,
(SELECT SUM(amount) FROM source_in_scope) AS source_amount,
(SELECT SUM(amount) FROM model_in_scope) AS model_amount
UNION ALL
SELECT 2, 'keys unique on both sides',
(SELECT COUNT(*) FROM source_unique),
(SELECT COUNT(*) FROM model_unique),
(SELECT SUM(amount) FROM source_unique),
(SELECT SUM(amount) FROM model_unique)
UNION ALL
SELECT 3, 'keys found on both sides',
COUNT(*), COUNT(*), SUM(s.amount), SUM(m.amount)
FROM source_unique AS s
JOIN model_unique AS m ON s.order_id = m.order_id
)
SELECT population, source_rows, model_rows, source_amount, model_amount,
model_amount - source_amount AS amount_difference
FROM totals
ORDER BY sort;
-- Expected with the illustrative data:
-- population | source_rows | model_rows | source_amount | model_amount | amount_difference
-- all rows in scope | 8 | 9 | 632.74 | 692.74 | 60
-- keys unique on both sides | 7 | 7 | 572.74 | 572.74 | 0
-- keys found on both sides | 6 | 6 | 557.74 | 557.74 | 0| population | source_rows | model_rows | source_amount | model_amount | amount_difference |
|---|---|---|---|---|---|
| all rows in scope | 8 | 9 | 632.74 | 692.74 | 60 |
| keys unique on both sides | 7 | 7 | 572.74 | 572.74 | 0 |
| keys found on both sides | 6 | 6 | 557.74 | 557.74 | 0 |
5. Isolate missing and extra keys
A full outer join on the unique keys finds the orders that exist on only one side. Before calling one missing, look for its key on the other side without the date filter: an order the source dates 30 September and the model dates 1 October is a timezone or timing difference, not a lost row.
-- Result 3: keys on one side only.
SELECT
IF(m.order_id IS NULL, 'missing_in_model', 'extra_in_model') AS finding,
COALESCE(s.order_id, m.order_id) AS order_id,
COALESCE(s.order_date, m.order_date) AS order_date,
COALESCE(s.amount, m.amount) AS amount
FROM source_unique AS s
FULL OUTER JOIN model_unique AS m ON s.order_id = m.order_id
WHERE s.order_id IS NULL OR m.order_id IS NULL
ORDER BY order_id;
-- Expected with the illustrative data:
-- finding | order_id | order_date | amount
-- missing_in_model | A-1005 | 2026-09-08 | 15
-- extra_in_model | A-1009 | 2026-09-28 | 15| finding | order_id | order_date | amount |
|---|---|---|---|
| missing_in_model | A-1005 | 2026-09-08 | 15 |
| extra_in_model | A-1009 | 2026-09-28 | 15 |
6. Compare values on the keys that match
Each matched key is compared column by column, and listed if any column differs. The NULL policy is stated in the code rather than left to the defaults: NULL on both sides is equal, NULL on one side only is a difference. A plain = would do neither, because NULL = anything is NULL, and a WHERE clause drops it, so a value that went missing would never show up as changed.
-- Result 4: matched keys whose values differ.
-- NULL policy: NULL on both sides is equal; NULL on one side only is a
-- difference. IS DISTINCT FROM does exactly that, where = would drop the row.
CREATE TEMP TABLE compared AS
SELECT
s.order_id,
s.amount AS source_amount,
m.amount AS model_amount,
m.amount - s.amount AS amount_difference,
s.status AS source_status,
m.status AS model_status,
s.order_date IS DISTINCT FROM m.order_date AS order_date_differs,
CASE
WHEN s.amount IS NULL AND m.amount IS NULL THEN FALSE
WHEN s.amount IS NULL OR m.amount IS NULL THEN TRUE
ELSE ABS(m.amount - s.amount) > amount_tolerance
END AS amount_differs,
s.status IS DISTINCT FROM m.status AS status_differs
FROM source_unique AS s
JOIN model_unique AS m ON s.order_id = m.order_id;
SELECT
order_id,
ARRAY_TO_STRING([
IF(order_date_differs, 'order_date', NULL),
IF(amount_differs, 'amount', NULL),
IF(status_differs, 'status', NULL)
], ', ') AS differing_columns,
source_amount, model_amount, amount_difference, source_status, model_status
FROM compared
WHERE order_date_differs OR amount_differs OR status_differs
ORDER BY order_id;
-- Expected with the illustrative data:
-- order_id | differing_columns | source_amount | model_amount | amount_difference | source_status | model_status
-- A-1003 | amount | 200 | 210 | 10 | paid | paid
-- A-1004 | amount | 49.99 | 39.99 | -10 | paid | paid
-- A-1006 | status | 30 | 30 | 0 | null | paid| order_id | differing_columns | source_amount | model_amount | amount_difference | source_status | model_status |
|---|---|---|---|---|---|---|
| A-1003 | amount | 200 | 210 | 10 | paid | paid |
| A-1004 | amount | 49.99 | 39.99 | -10 | paid | paid |
| A-1006 | status | 30 | 30 | 0 | null | paid |
7. Summarise, and decide what a pass means
The summary counts each kind of finding, so a rerun after a fix shows what changed. A clean run reads 0 on the ambiguous, missing, extra and differing lines. To turn the script into a check that fails a scheduled job instead of reporting, uncomment the ASSERT at the end.
-- Result 5: summary counts.
WITH summary AS (
SELECT 1 AS sort, 'source rows in scope' AS measure, (SELECT COUNT(*) FROM source_in_scope) AS value
UNION ALL SELECT 2, 'model rows in scope', (SELECT COUNT(*) FROM model_in_scope)
UNION ALL SELECT 3, 'ambiguous keys excluded (duplicate or NULL)', (SELECT COUNT(DISTINCT IFNULL(order_id, '(null)')) FROM ambiguous_keys)
UNION ALL SELECT 4, 'keys missing in model', (SELECT COUNT(*) FROM source_unique WHERE order_id NOT IN (SELECT order_id FROM model_unique))
UNION ALL SELECT 5, 'keys extra in model', (SELECT COUNT(*) FROM model_unique WHERE order_id NOT IN (SELECT order_id FROM source_unique))
UNION ALL SELECT 6, 'matched keys', (SELECT COUNT(*) FROM compared)
UNION ALL SELECT 7, 'matched keys with a difference', (SELECT COUNTIF(order_date_differs OR amount_differs OR status_differs) FROM compared)
UNION ALL SELECT 8, 'matched keys equal', (SELECT COUNTIF(NOT (order_date_differs OR amount_differs OR status_differs)) FROM compared)
)
SELECT measure, value FROM summary ORDER BY sort;
-- To fail instead of report, uncomment:
-- ASSERT (
-- SELECT COUNT(*) FROM ambiguous_keys
-- ) + (
-- SELECT COUNT(*) FROM source_unique s FULL OUTER JOIN model_unique m ON s.order_id = m.order_id
-- WHERE s.order_id IS NULL OR m.order_id IS NULL
-- ) + (
-- SELECT COUNTIF(order_date_differs OR amount_differs OR status_differs) FROM compared
-- ) = 0 AS 'Reconciliation failed: see Results 1, 3 and 4';
-- Expected with the illustrative data:
-- measure | value
-- source rows in scope | 8
-- model rows in scope | 9
-- ambiguous keys excluded (duplicate or NULL) | 1
-- keys missing in model | 1
-- keys extra in model | 1
-- matched keys | 6
-- matched keys with a difference | 3
-- matched keys equal | 3| measure | value |
|---|---|
| source rows in scope | 8 |
| model rows in scope | 9 |
| ambiguous keys excluded (duplicate or NULL) | 1 |
| keys missing in model | 1 |
| keys extra in model | 1 |
| matched keys | 6 |
| matched keys with a difference | 3 |
| matched keys equal | 3 |
The BigQuery console shows NUMERIC values without trailing zeros, so 200.00 appears as 200, and a NULL as null. Other clients may print 200.00; the values are the same.
Adaptation worksheet
Fill this in before you change a line of the script, and agree it with whoever owns each side. The script only proves what this worksheet says: no mismatches means the two sides agree for these tables, this key, this window and these measures, and nothing beyond them.
| Decide | What to write down |
|---|---|
| Table names | The two sides, named exactly: the source table or extract, and the model or report query it should match. Write down who owns each one. |
| Key | The column or columns that identify one row at the agreed grain. If it takes two columns, compare on both everywhere the script uses order_id. If no key exists on one side, that is the first finding. |
| Date scope | The date column on each side, the timezone it is in, and the half-open window: start inclusive, end exclusive. Both sides use the same window, applied before any comparison. |
| Population | Who is in and who is out on each side: test accounts, cancelled orders, internal users, refunds. A filter that exists on only one side shows up as missing or extra keys. |
| Compared measures | The columns to compare on matched keys, and how each is normalised first: currency and rate, rounding, gross or net, units. |
| Accepted tolerances | Zero, unless the business owner has agreed a number, written down with their name and the reason, for example a rounding difference per order. A tolerance chosen so the check passes hides the error it was meant to find. |
Why equal row counts and equal totals prove nothing
A row count and a sum are each one number standing for many rows, and very different sets of rows produce the same number. A missing row and an extra row cancel in the count. Two amounts wrong in opposite directions cancel in the sum. A duplicate can hide a missing row of similar value. The worked example has all of these: set the duplicate aside and both sides have seven orders worth 572.74, and the data still contains five other differences.
So an equal total is not where a reconciliation ends. A total that differs tells you something is wrong; a total that matches tells you only that the errors, if any, add up to zero. Equality is shown row by row, on a unique key, value by value, which is what Results 1, 3 and 4 do.
Trace source → model → report, one boundary at a time
When a report disagrees with the source, compare one boundary at a time rather than the two ends. Run the comparison on the source and the warehouse model first, then on the model and whatever the report actually queries. The first boundary where a difference appears is the layer that introduced it, and every layer before it is cleared with evidence rather than opinion.
Classify each difference before fixing anything: a definition gap, a population or scope mismatch, timing or late-arriving data, a transformation bug, or rows that never arrived. The fix depends on the class. Changing a filter until the totals match fixes none of them, and can create a new error larger than the one it hides.
What a clean result does and does not prove
A run with no ambiguous keys, no missing or extra keys and no differing values proves the two sides agree for the tables, key, window, population and columns you chose, at the tolerance you set. It says nothing about columns you did not compare, days outside the window, or a third system neither side reads. Write the scope next to the result when you report it.
A nonzero tolerance is a business decision, not a technical one. Someone who owns the number has to agree that a difference of that size does not matter, and say why. Chosen by the engineer so that a check passes, a tolerance becomes the place where the next real error hides.
Once a comparison is worth keeping, schedule it. The ASSERT at the end of the script turns the summary into a check that fails the job, so the next divergence is found by the pipeline rather than by someone reading a board pack.