This lesson's interactive features are locked, please to keep using them

Merge Validation

The scary thing about merges is that mistakes can happen silently. The table that comes out of the merge could be missing data or contain duplicates, and your code probably won't raise an exception. The data will just be wrong. Bad merges cause problems like:

Lost revenue data (incomplete joins)
Duplicate records (many-to-many joins)
Wrong conclusions (joined on the wrong keys)

So be careful! There are steps you can take to validate merges and catch problems early.

Row Count Validation

The simplest check: count rows before and after merging.

original_count = len(sales)
merged = pd.merge(sales, products, on="product_id", how="left")
merged_count = len(merged)

if merged_count != original_count:
    print(f"WARNING: Expected {original_count} rows, got {merged_count}")

For inner joins: the row count should be less than or equal to the left table, when you expect at most one match per left row.
For left joins: the row count should usually stay the same. If it grows, the right table probably has duplicate keys.
For outer joins: the row count should be at least as large as the larger input table.

If you see record counts that surprise you, inspect the keys before trusting the result.

It can also be helpful to check the right table directly for duplicates before merging, since that's where the problem usually is. For example, given a products table with a product_id key:

# Check if product_id has duplicates
has_dupes = products["product_id"].duplicated().any()

if has_dupes:
    print("WARNING: Duplicate keys in products table")
    dupes = products[products["product_id"].duplicated(keep=False)]
    print(dupes)

Validating Aggregates

Check that a trusted left-side metric didn't change unexpectedly:

total_before = sales["quantity"].sum()

merged = pd.merge(sales, products, on="product_id", how="left")
total_after = merged["quantity"].sum()

if total_before != total_after:
    print("ERROR: Totals changed!")
    print(f"Before: {total_before}, After: {total_after}")

If totals change, you may have duplicate keys creating extra rows.

Automatic Validation

The pd.merge() method has a validate parameter that can catch cardinality mistakes for you:

merged = pd.merge(sales, products, on="product_id", how="left", validate="many_to_one")

You can specify "one_to_one", "one_to_many", or "many_to_one"; Pandas raises an error when the keys violate that expectation. "many_to_many" is also accepted, but performs no checks.

Assignment

SnackStack's data team wants a merge validation step before updating daily dashboards.

Complete the validate_merge function. It accepts two DataFrames: sales and products. It should perform a left join on product_id and return a dictionary with validation results.

Merge the DataFrames on product_id, keeping all sales records but only the matching products.
Check whether the sales keys are unique by checking for duplicates in the sales product_id column.
Check whether the products keys are unique the same way.
Check whether the row count is preserved by comparing the merged row count to the original sales row count.
Check whether every product_id value in sales exists in the products table. The .all() method returns True if all values in a Series are True, and False otherwise.
Return a dictionary with these keys mapping to Python bool values. Pandas methods may return NumPy booleans, so convert them with bool():
- left_keys_unique
- right_keys_unique
- row_count_preserved
- no_unmatched_rows