Join Tables Safely with Cardinality and Reconciliation Checks

Module 4: Combining and Summarizing Data · Lecture 14 of 32

A join can multiply revenue without changing a single source amount. Enrich orders with customer regions using an explicit many-to-one contract, identify unmatched keys, and prove that the join preserves the order grain.

What you will learn

Choose join type according to the reporting population.
Validate many-to-one cardinality before using enriched totals.
Investigate unmatched keys and null-key behavior.

Understand the problem

Name both grains

The left table contains one row per order. The right table contains one row per customer. A customer can place several orders, so the intended relationship is many orders to one customer. If the right table repeats a customer key, each matching order can expand into several rows and inflate revenue. This is a relational error, not an aggregation problem.

Preserve the intended population

A left join retains every accepted order, including anonymous and unmatched customers. An inner join would exclude them, changing the revenue population. If the report is explicitly about identified customers, an inner join may be appropriate, but its exclusions still need disclosure. Never switch join types merely because unmatched rows make a table untidy.

Validate and inspect

Use validate='many_to_one' to enforce the dimension-key contract and indicator=True to inspect matched versus left-only rows. Also compare row count and total revenue before and after enrichment. Those checks protect distinct properties: uniqueness on the dimension, population preservation, and monetary reconciliation.

Null keys need care

pandas can match null join keys to null join keys, unlike common SQL expectations. The customer dimension in this kit contains no missing IDs. Reject missing dimension keys before joining, so an anonymous order does not accidentally attach to a fabricated shared customer row. Fill only the presentation field region with Unknown; preserve customer_id as missing.

Run the worked example

Download and extract the course kit linked below. Open a terminal in the extracted folder, complete the setup in Lecture 2, and run python run_course.py --lesson 14 --examples-only. Each script starts from the shared dataset; it does not depend on a notebook cell from another lecture. All retail records are fictional and amounts are integer paise unless a chart explicitly says INR.

from pathlib import Path
import numpy as np
import pandas as pd
from course_data import load_orders, load_raw, load_customers, clean_orders, ROOT, OUT

orders = load_orders()
customers = load_customers()
assert customers['customer_id'].notna().all()
assert customers['customer_id'].is_unique
enriched = orders.merge(customers, on='customer_id', how='left',
                        validate='many_to_one', indicator=True)
enriched['region'] = enriched['region'].fillna('Unknown')
print(enriched['_merge'].value_counts().to_string())
print(enriched.groupby('region')['net_paise'].sum().to_string())
assert len(enriched) == len(orders)
assert int(enriched['net_paise'].sum()) == int(orders['net_paise'].sum())
assert int(enriched['_merge'].eq('left_only').sum()) == 1

Verified output

_merge
both          238
left_only       1
right_only      0
region
East        7155660
North       1637130
South       4370050
Unknown       29900
West       10919680

Walk through the result

The customer dimension is validated before use, and merge independently enforces the many-to-one relationship. The single left-only order is the deliberately anonymous checkout. It remains in the revenue population and appears under Unknown region. A report that omitted that region would fail the total-reconciliation check. The indicator column is useful during analysis but can be removed from a polished delivery table after its counts are recorded.

Common mistakes and diagnosis

Do not call drop_duplicates on a dimension simply to make validation pass: conflicting customer attributes require investigation. Joining on region instead of customer_id creates many-to-many combinations. Matching keys with different normalization can create false nonmatches. A perfect-looking inner join can conceal missing customers because it discards the evidence you needed to inspect.

Practice assignment

Create a dimension with one duplicated customer row. Confirm that many_to_one validation raises a MergeError. Then calculate region totals from the valid join and verify that they reconcile to the accepted-order total.

Worked solution and explanation

Add the following code below the worked example, or run the matching file in the solutions folder. Compare the result with the business definition before trusting a passing assertion.

bad_dimension = pd.concat([customers, customers.iloc[[0]]], ignore_index=True)
try:
    orders.merge(bad_dimension, on='customer_id', how='left', validate='many_to_one')
except pd.errors.MergeError:
    pass
else:
    raise AssertionError('Duplicated dimension key was accepted')
region_totals = enriched.groupby('region')['net_paise'].sum()
assert int(region_totals.sum()) == int(orders['net_paise'].sum())

Check your understanding

Why are a uniqueness check and a revenue-total check both useful?

Answer guide

They guard different failure modes. Dimension uniqueness prevents row multiplication, while total reconciliation detects population loss or accidental filtering after the join. A join can preserve a grand total by coincidence, especially with zero-value rows, so the count, key, match, and amount checks together provide stronger evidence.

Reference and next step

pandas merging guide

Download the complete course kit: datasets, all examples, solutions, and capstone.

Return to the full Data Analysis with Python and pandas curriculum.

Course index · Previous · Next