An INNER JOIN can lose orders and multiply others in the same query. Here is a reproducible test for the failure that aggregate dashboards often miss.
AI-generated illustration of duplicate matches and an unmatched order. Synthetic amounts in integer cents.
A join can return the same number of rows as its input and still inflate revenue. In this synthetic example, three orders become three output rows, while the total rises from 17,500 to 25,000 cents: an increase of about 42.9%.
The row-count check approves. Accounting would like a word.
The aggregate count is unchanged because one duplicated order compensates for one missing order.
AI-generated comic about a row-count check that misses a revenue mismatch.
The join that cancels its own mistakes
We have three synthetic orders, in integer cents:
order customer amount_cents
o1 c1 10000
o2 c2 5000
o3 c3 2500
The source total is 17,500 cents. Our customer profile table has two historical versions for c1, one for c2, and no profile for c3.
SELECT o.order_id, o.amount_cents, p.tier
FROM orders AS o
INNER JOIN customer_profiles AS p
ON p.customer_id = o.customer_id;
What happens?
- o1 matches two profile rows, so its 10,000 cents are counted twice.
- o2 matches once.
- o3 disappears because its customer has no profile.
The output is still three rows, but the sum is now 25,000 cents. We lost one fact row and duplicated another. A row-count assertion looks reassuring precisely because the two errors compensate numerically. The query assumes one matching profile per customer, but the data contains two matches for one customer and none for another.
Define the grain before writing the join
The grain of the source is one row per order_id. The intended relationship is many orders to one current customer profile. But the customer_profiles table actually stores multiple versions for at least one customer.
An INNER JOIN only enforces its equality predicate. It does not invent a uniqueness constraint or choose “current” by intuition. A LEFT JOIN would preserve unmatched orders, but it would not prevent fanout when two profiles match.
Before joining, make the contract explicit:
- Every order is associated with exactly one valid profile for the report’s business time.
- The right side contains no more than one profile row per join key after applying that time or current-record rule.
- An unmatched order is an exception to investigate, not something silently removed.
- A revenue report reconciles both fact count and monetary totals in integer cents.
For a Type 2 dimension, the correct fix is normally a temporal rule, such as selecting the version effective when the order happened. Choosing MAX(profile_version) without understanding the business definition of “current” is not a neutral fix; it can rewrite history. If the contract truly needs the latest known state rather than an as-of state, document that choice.
Four checks that expose the issue
Check 1: duplicate dimension keys at the join grain.
SELECT customer_id, COUNT(*) AS matches
FROM customer_profiles
GROUP BY customer_id
HAVING COUNT(*) > 1;
This is intentionally conservative. In a history table, duplicates may be legitimate overall, but they are not legitimate in the dimension slice selected for a one-profile-per-customer join. Filter or window the history according to the correct as-of rule first, then check that derived slice.
Check 2: unmatched orders.
SELECT o.order_id
FROM orders AS o
WHERE NOT EXISTS (
SELECT 1 FROM customer_profiles AS p
WHERE p.customer_id = o.customer_id
);
Use this even if the row-count totals happen to match. If null business keys are allowed, specify their matching policy explicitly instead of relying on implicit SQL NULL behavior.
Check 3: reconcile totals before and after.
SELECT COUNT(*) AS row_count, SUM(amount_cents) AS cents FROM orders;
SELECT COUNT(*) AS row_count, SUM(o.amount_cents) AS cents
FROM orders AS o
INNER JOIN customer_profiles AS p
ON p.customer_id = o.customer_id;
For this example the counts both equal three, but the amounts disagree: 17,500 versus 25,000 cents. Finance-grade pipelines should avoid floating-point money and define how refunds, cancellations, currency, and tax are represented.
Check 4: fail the contract, not only the SQL execution.
A query that returns rows has not proved its relationship assumptions. Treat fanout, unmatched facts, and unreconciled totals as observable contract breaches. Fail the publication gate before an incorrect result enters an executive dashboard.
A small executable proof
Spark join hints suggest execution strategies, such as broadcasting one side; they do not enforce one profile per customer. Keep the cardinality and reconciliation checks when porting this pattern, then evaluate the physical plan separately. The executable proof here remains SQLite-only.
Spark JOIN reference: https://spark.apache.org/docs/latest/sql-ref-syntax-qry-select-join.html
Join hints: https://spark.apache.org/docs/latest/sql-ref-syntax-qry-select-hints.html
The public lab includes a dependency-free SQLite example and eight tests:
The example deliberately produces the deceptive equality of row counts, catches duplicate profile keys, catches orphan orders, compares totals, and refuses the publication contract. Tests also cover a healthy dimension, an empty input, and a refund represented as negative integer cents.
Run it with Python 3.12+:
python -m unittest discover -s sql/day_002_join_cardinality -p 'test_*.py' -v
python sql/day_002_join_cardinality/join_guard.py
Scope: the tests run against SQLite with synthetic data. They do not benchmark Spark, execute a Databricks job, or validate your production dimensional model. The behavioral principle is portable; query plans and enforcement mechanisms differ by engine.
What I would require in a production review
A code review focused only on syntax and task status will not catch this class of incident. I would ask for a written grain, a documented as-of join rule, checks for right-side cardinality and unmatched keys, monetary reconciliation, and a failure path that blocks consumption when those checks fail.
For an analytics system with late-arriving dimensions, the contract also needs a defined quarantine or remediation strategy. Do you defer the record, show it as “unknown,” or resolve it from an earlier version? A blanket INNER JOIN is not a policy.
For this example, I would block the report until c1 has one profile selected by the agreed time rule and o3 has an explicit resolution. Then I would rerun the key, row-count, and money checks before making the result available.
References
- https://docs.databricks.com/aws/en/sql/language-manual/sql-ref-syntax-qry-select-join (INNER vs LEFT and join criteria).
- https://www.postgresql.org/docs/current/queries-table-expressions.html (relational join behavior).
- https://github.com/jenniferevangelista-de/data-engineering-lab/tree/main/sql/day_002_join_cardinality (synthetic example and tests).
Top comments (0)