Resources · 68
Data joins: avoid multiplying business metrics
Verify grain, keys and cardinality before totaling sales, costs or conversions.
· 2 min
What this guide helps achieve
- Describe the grain
- Check cardinality
- Reconcile totals
- Review exclusions
Quick check
- What does one row represent in each table?
- Do keys have the expected cardinality?
- Does the independent total remain unchanged?
Step-by-step method
- 01
Describe the grain
State what one row represents: order, order line, payment or event. Identify expected keys and time versions. Repeated customer keys are not necessarily faulty duplicates.
Deliverable: grain dictionary.
- 02
Check cardinality
Count key occurrences on both sides and find null values. Compare row counts before and after joining. Define whether the expected relationship is one-to-one, one-to-many or many-to-many.
Deliverable: key profile and exceptions.
- 03
Reconcile totals
Keep an independent sum at the business grain. Joining an order to multiple lines can repeat its amount. Aggregate at the appropriate grain before joining or define an explicit allocation, then reconcile the result.
Deliverable: before-and-after balance.
- 04
Review exclusions
Filtering the right table after an outer join can remove unmatched rows. Measure unmatched records, time periods and time zones. Retain test cases when adding new sources.
Deliverable: coverage report and regression cases.
Reusable worksheet
Complete with your authorised observations. These fields are a working template, not observed results.
| Field | Information to record |
|---|---|
| Grain | Row meaning and expected key |
| Cardinality | Occurrences, null values and expected relationship |
| Reconciliation | Counts, reference sum and differences |
Worked example
Illustrative situation
Fictional example: an order of 100 is linked to two product lines.
Decision and expected evidence
Summing the order amount after joining yields 200. The team aggregates lines before reconciliation and checks the independent total of 100.
Distinguish the mechanisms
| Mechanism | Purpose | Check or limitation |
|---|---|---|
| INNER JOIN | Retain matched rows | Unmatched rows disappear |
| LEFT JOIN | Retain left-side rows | Later filters can change that effect |
Management indicators
| Indicator | What it measures | First action |
|---|---|---|
| Total difference | Difference from the independent sum | Block publication of unexplained totals |
| Unmatched share | Keys without a match in scope | Document exclusions and period |
Common pitfalls
- Sum a value repeated at another grain
- Conceal cardinality errors with DISTINCT
Frequently asked questions
Does DISTINCT fix an incorrect join?
It may conceal repeats without fixing the grain or allocation. Verify the relationship before deduplicating.
Are repeated keys always wrong?
No: order lines normally repeat an order identifier. Check against the expected grain.
What small test helps?
A fictional order of 100 with two lines should retain an order total of 100, not 200 after joining.
Official references
References consulted: . The method and worksheet propose checks to adapt to your context; they do not constitute certification.
Continue exploring
Related analysis
