Data Engineering / Basic: contracts and repeatable batches / Lesson 4

SQL grain, joins and reconciliation

Keep relational transformations correct when one-to-many relationships change row counts. Reconcile measures at a declared grain before trusting a published total.

About 4 min readContent reviewed 2026-10-26

On this page

Lesson goals

Predict join multiplicity and optional-parent behavior.
Use independent aggregation and invariant checks.

State what one row represents at every stage

A customer row, shipment row and line-item row represent different facts. Joining one parent to several children repeats parent fields; joining two independent child sets can multiply both. SQL executing successfully does not make the resulting grain correct. Write the expected cardinality beside each transformation and identify whether a measure belongs to parent or child. DISTINCT can remove repeated projected rows but cannot generally repair an inflated financial sum, because equal amounts may be legitimate separate events. Reconciliation must follow meaning, not merely reduce a row count.

Preserve a parent and count the child identity

This complete program creates only an in-memory SQLite database and closes it afterward. Customer two has no purchase, so LEFT JOIN preserves that parent while COUNT(p.id) returns zero. COUNT(*) would count the preserved outer row as one. Explicit grouping and ordering make the result contract visible. The example uses synthetic integers and labels, not an application database. It demonstrates row semantics rather than a performance benchmark, and its result does not establish that every optional relationship in a larger pipeline follows the same policy.

python Synthetic outer-join count in SQLite memory

import sqlite3

db = sqlite3.connect(':memory:')
db.executescript('''
CREATE TABLE customer(id INTEGER PRIMARY KEY);
CREATE TABLE purchase(id INTEGER PRIMARY KEY, customer_id INTEGER);
INSERT INTO customer VALUES(1),(2);
INSERT INTO purchase VALUES(10,1),(11,1);
''')
rows = db.execute('''SELECT c.id,COUNT(p.id) FROM customer c
LEFT JOIN purchase p ON p.customer_id=c.id GROUP BY c.id ORDER BY c.id''').fetchall()
assert rows == [(1, 2), (2, 0)]
db.close()

The exact result is [(1,2),(2,0)]. No disk database or configured connection is opened.

Place optional-child filters at the intended boundary

A report of all customers and their paid purchases usually puts the paid predicate in the join condition. Moving it to WHERE can discard unmatched parents because their child fields are null. Mandatory tenant or population scope still needs its own explicit predicates. Likewise, filtering source rows before grouping differs from filtering completed groups with HAVING. Test no children, multiple children, null attributes and repeated amounts; a convenient all-matching fixture can make an incorrect query appear correct. Production flow correctness requires these counterexamples before performance tuning.

Expose a two-by-three fan-out explicitly

The local product model has two line amounts and three payment amounts for one shipment. Directly combining them creates six rows, inflating the line total to seventy-five and the payment total to fifty. The correct independent totals are both twenty-five. Preaggregate each side to the shared shipment grain before joining their summaries. The model illustrates multiplicity only and does not execute an external warehouse. A real flow must also define refunds, currency, tax and missing-child behavior before treating either total as an accounting invariant.

python Offline multiplicity counterexample

from itertools import product

lines = [10, 15]
payments = [5, 8, 12]
joined = list(product(lines, payments))
assert len(joined) == 6
assert sum(line for line, payment in joined) == 75
assert sum(payment for line, payment in joined) == 50
assert sum(lines) == sum(payments) == 25

Six combinations repeat each line three times and each payment twice. Independent aggregation preserves the intended facts.

Verify keys, populations and measures together

A useful transformation check compares expected key coverage, uniqueness, null treatment and aggregate relationships. Equal output counts alone can hide one missing and one duplicated key, while equal totals can hide offsetting errors. Preserve source interval and version with the reconciliation evidence. SQLite here is an educational relational engine; warehouse-specific SQL, null ordering, merge syntax and optimizer behavior require their own manuals. Do not copy one engine’s successful example as proof that an untested production query or data volume is correct and performant.

Common errors and fixes

Totals grow after adding another child table

Why it happens: Independent one-to-many relationships multiplied each other.
How to fix it: Preaggregate each child to the shared intended grain.
Prevent it next time: Use two-by-three counterexamples in reconciliation.

Parents without purchases disappear

Why it happens: A child WHERE condition rejected null-extended outer rows.
How to fix it: Place optional matching conditions in the correct join boundary.
Prevent it next time: Test unmatched parents explicitly.

Equal counts are treated as proof of equal data

Why it happens: Key identity and value relationships were not compared.
How to fix it: Reconcile keys and meaningful measures as well as counts.
Prevent it next time: Include offsetting missing/duplicate fixtures.

Check your understanding

1. Two outputs both contain one hundred rows. What remains unproven?

Show worked solution

They may contain different keys, duplicates, nulls or values. Compare the expected population and key-level relationships, then meaningful totals at the declared grain. Row-count equality is a useful signal but cannot establish complete transformation equivalence or source coverage by itself.

2. A shipment has two lines and three payments. Why is SUM(DISTINCT amount) an unsafe fan-out repair?