Reconcile source and target totals after a load
Compare scoped row counts, reported-value counts, quantities and identities to expose balanced errors, null changes and shared omissions.
A load copied four records, and the target still contains four records. Its quantity total also agrees with the source. That sounds reassuring until one customer’s quantity has increased by one and another’s has decreased by one. The errors cancel in the total while remaining wrong for both customers.
Reconciliation asks which properties of a defined source population survived a load. Counts, totals, missing-value counts, key checks and row comparisons answer different questions. This lesson builds those checks around one small batch and shows what even an exact match cannot establish about the population you selected.
The intended reader understands basic SQL grouping and joins. Python only creates the SQLite fixture and collects query results. Use Python 3.12 or later; the recorded run uses Python 3.12.3 and SQLite 3.45.1. All quantities are invented bounded integers or NULL, not production customer data or floating-point measurements.
Name the population before comparing it
Our source and target both contain batch load-17, plus an unrelated row from load-18. The reconciliation concerns only load-17. Comparing the entire source with one target batch would report an error caused by different scopes, while comparing two accidentally incomplete filters could hide a real omission.
The copy contract preserves a non-null integer identifier and a nullable integer quantity for every selected row. There should be one row per identifier within the batch. If the real pipeline legitimately aggregates, filters or changes units, first express the expected target relation from the chosen source snapshot. Comparing raw rows across such a transformation would enforce the wrong contract.
Place the four Python blocks in sequence in a single script. Run without other writers; the local transaction provides a consistent database read boundary, but no remote source or production ingestion process is involved.
import hashlib, json, sqlite3
db = sqlite3.connect(':memory:', autocommit=sqlite3.LEGACY_TRANSACTION_CONTROL)
db.executescript("""CREATE TABLE source(batch TEXT, id INTEGER NOT NULL, qty INTEGER);
CREATE TABLE target(batch TEXT, id INTEGER NOT NULL, qty INTEGER);""")
BATCH = 'load-17'
original = [(101, 10), (102, 20), (103, None), (104, 0)]
with db:
db.executemany('INSERT INTO source VALUES (?, ?, ?)',
[(BATCH, key, qty) for key, qty in original])
db.execute("INSERT INTO source VALUES ('load-18', 999, 500)")
db.execute("INSERT INTO target VALUES ('load-18', 999, 500)")
def replace_target(rows):
with db:
db.execute('DELETE FROM target WHERE batch = ?', (BATCH,))
db.executemany('INSERT INTO target VALUES (?, ?, ?)',
[(BATCH, key, qty) for key, qty in rows])
def fingerprint(rows):
raw = json.dumps(rows, separators=(',', ':'), ensure_ascii=True).encode()
return hashlib.sha256(raw).hexdigest()The four selected source rows contain quantities 10, 20, NULL and 0. A NULL quantity means no reported value. Zero is a reported quantity. The schema intentionally permits duplicate identifiers so a later negative control can demonstrate why the comparison must check them first. The test data is trusted; the table is not a general ingestion validator for arbitrary SQLite types.
Use summaries to locate a problem
SQLite’s aggregate documentation defines COUNT(*) as the number of rows and COUNT(qty) as the number of non-null quantities. SUM(qty) adds the non-null quantities. The source summary is therefore (4, 3, 30), not a claim that all four quantities are known.
A count mismatch suggests missing or extra rows. A non-null count mismatch can expose a lost value or an invented replacement. A total mismatch indicates a numerical difference at this scope. Matching all three summaries is useful, but it does not prove that the correct value belongs to each identifier.
The following helper performs every query inside one explicitly opened read transaction. The table names come from a fixed internal tuple; the batch value is bound as a SQL parameter. The helper requires an idle connection and ends only the transaction it opened.
def reconcile():
if db.in_transaction:
raise ValueError('reconciliation owns an idle connection')
db.execute('BEGIN')
try:
stats, duplicate_ids, rows = {}, {}, {}
for table in ('source', 'target'): # Fixed internal table names.
stats[table] = db.execute(
f'SELECT COUNT(*), COUNT(qty), SUM(qty) FROM {table} WHERE batch = ?',
(BATCH,)).fetchone()
duplicate_ids[table] = db.execute(
f'SELECT id, COUNT(*) FROM {table} WHERE batch = ? GROUP BY id HAVING COUNT(*) > 1',
(BATCH,)).fetchall()
rows[table] = db.execute(
f'SELECT id, qty FROM {table} WHERE batch = ? ORDER BY id, qty',
(BATCH,)).fetchall()
if any(duplicate_ids.values()):
return {'status': 'invalid keys', 'stats': stats, 'duplicates': duplicate_ids}
differences = db.execute("""
WITH s AS (SELECT id, qty FROM source WHERE batch = ?),
t AS (SELECT id, qty FROM target WHERE batch = ?)
SELECT s.id, CASE WHEN t.id IS NULL THEN 'missing' ELSE 'changed' END,
s.qty, t.qty
FROM s LEFT JOIN t ON t.id = s.id
WHERE t.id IS NULL OR s.qty IS NOT t.qty
UNION ALL
SELECT t.id, 'unexpected', NULL, t.qty
FROM t LEFT JOIN s ON s.id = t.id WHERE s.id IS NULL
ORDER BY 1, 2
""", (BATCH, BATCH)).fetchall()
return {'status': 'match' if not differences else 'mismatch',
'stats': stats, 'differences': differences,
'fingerprints': {table: fingerprint(values) for table, values in rows.items()}}
finally:
db.rollback() # End this read-only transaction; no business writes occur.Before aligning rows, the helper checks uniqueness separately on each side. A duplicate key makes the intended one-to-one comparison undefined, even when count and total still agree. dbt’s data-test documentation similarly distinguishes uniqueness, non-nullness, accepted values and relationships as separate assertions. Passing one does not substitute for the others, and no dbt process runs in this example.
The first join finds source identities whose target row is missing or whose quantity changed. The second finds target identities absent from the source. The missing and unexpected labels matter because a displayed NULL quantity alone cannot tell you whether a row exists. We use the non-null identifier to detect presence.
For values, SQLite’s IS NOT operator supplies the required null-aware comparison: NULL versus NULL is unchanged, while NULL versus zero is changed. A plain s.qty != t.qty evaluates to unknown when either operand is NULL and can silently exclude that difference from a WHERE clause. Other engines commonly spell this condition IS DISTINCT FROM; verify your engine’s semantics before translating the query.
This readable panel was rendered from the exact local query text and captured results. Both queries use the same four-row batch; it is not a screenshot of a native database client.
Read the counterexamples by identifier
cases = {
'reordered': list(reversed(original)),
'balanced error': [(101, 11), (102, 19), (103, None), (104, 0)],
'null replaced by zero': [(101, 10), (102, 20), (103, 0), (104, 0)],
'wrong zero-quantity key': [(101, 10), (102, 20), (103, None), (105, 0)],
'duplicate key': [(101, 10), (101, 20), (103, None), (104, 0)],
}
for label, candidate in cases.items():
replace_target(candidate)
report = reconcile()
print(label, report['status'], report['stats'],
report.get('differences', report.get('duplicates')))The reordered copy matches because row order is not part of this contract. The balanced error preserves (4, 3, 30) but reports identifier 101 changing from 10 to 11 and 102 changing from 20 to 19. A total cannot reveal which pair of opposite errors produced its unchanged value.
Replacing NULL with zero leaves the sum at 30 but changes the non-null count from three to four. The row difference identifies 103. Replacing identifier 104 with 105 also preserves the full summary; the output reports missing 104 and unexpected 105. Even a zero-quantity record carries identity and can matter to completeness.
The duplicate-key case still has the same summary. Its target contains identifier 101 twice, so the helper returns invalid keys instead of publishing a potentially misleading row-difference report. Resolve that grain violation before treating a join result as one comparison per business entity.
The source summary is always four rows, three reported quantities and a total of 30. All examples are constructed to expose the scope of each check.
The sorted row fingerprint is an additional compact comparison. It includes identifiers and explicit JSON nulls, so a permutation keeps the same digest while the balanced error changes it. A checksum that concatenates only quantities would omit identity; a sum of row hashes would introduce its own representation and combination rules.
Here the byte representation is deliberately narrow: ordered pairs of integer identifiers and integer-or-null quantities, serialized with fixed JSON separators. Extending it to decimals, timestamps, floating-point NaN or text normalization requires a defined canonical representation. A hash is not source authentication, and comparing hashes does not replace the detailed differences already available in this small fixture. Cryptographic collisions are possible; the helper’s match decision uses the actual relational comparison, not digest equality.
Distinguish agreement from source completeness
replace_target(original)
assert reconcile()['status'] == 'match'
with db:
db.execute('DELETE FROM source WHERE batch = ? AND id = 104', (BATCH,))
db.execute('DELETE FROM target WHERE batch = ? AND id = 104', (BATCH,))
report = reconcile()
assert report['status'] == 'match'
assert report['stats']['source'][0] == 3
print('same omission on both sides:', report['status'])
# The external expectation of four identities was not part of this comparison.The final control removes identity 104 from both sides. Reconciliation reports a match because the supplied relations agree exactly. The external expectation that this batch contains four identities was never provided to the helper. Computing an expected count from the same incomplete extraction would not create independent evidence.
For an operational load, retain the source cutoff or snapshot identity, selected business scope and any independently established expected population. A source export manifest might declare a count; a separate ledger might enumerate expected keys. Each has its own trust and freshness assumptions. A count alone still cannot identify the right population, as the wrong-key example already showed.
The omission changes the selected population from four identities to three while leaving the quantity total at 30. The diagram does not claim the local helper can discover a missing upstream expectation.
The read transaction also has a limited purpose. It keeps these local queries on a consistent view under SQLite’s transaction-isolation rules. It does not align an independently changing remote source with a target sampled at another time, prove that a multi-page export finished, or prevent a later target modification. Store the checked source and target versions with the result so a later reader knows what the report covered.
Handle empty and large inputs deliberately
For zero rows, the summary is (0, 0, None) because SQLite’s SUM returns NULL when there are no non-null inputs. Four rows whose quantities are all NULL give (4, 0, None). A row with quantity zero gives (1, 1, 0). Keeping the counts alongside the sum preserves those distinctions instead of replacing every missing total with zero.
The fixture’s small integer quantities and totals fit SQLite’s signed integer range. A production contract must bound the aggregate as well as individual rows; integer SUM can overflow even when every row fits. Switching to floating-point TOTAL changes the numeric contract and is not an exact repair for that problem.
This implementation materializes both sorted row sets to create fingerprints and returns every difference. It is intended for understanding, not a scalability claim. A larger reconciliation needs a bounded reporting strategy and a partitioned or streaming comparison that preserves the same key, null, representation and snapshot rules. Retain representative mismatches without exposing unnecessary sensitive fields.
For an exercise, reset both sides to the original four rows before each case. Add an unexpected target identity 106 with quantity zero; expect five target rows, unchanged total 30, and one unexpected result. Then make source 103 equal 5 while leaving its target NULL; expect a changed result despite the null. Finally compare two empty scoped batches and two all-null batches. Record both what matches and which external completeness claim remains untested.