23.3 Merge Row Explosion (Many-to-Many)

By Ravindra Bagale Last updated: 23 October 2026

Why. You merge orders to stores on city alone. Pune has 3 stores and 100 orders → 300 rows. GMV triples. Ravindra’s dashboard “goes viral” for the wrong reason.

What.

One-to-one / many-to-one on a true key keeps grain stable.
Many-to-many duplicates rows when both sides repeat keys.
validate= and indicator=True catch this early.

How.

Merge safely

Write the grain: “one row = one order”.
Choose a key that matches that grain (store_id, not only city).
df.merge(..., how="left", validate="m:1") when the right table should be unique on the key.
Compare len(left) vs len(result) immediately.
Use indicator=True to see left_only / both / right_only.

Worked example A — Explosion demo.

orders = pd.DataFrame({
    "order_id": ["BLK-20", "BLK-21", "BLK-22"],
    "city": ["Pune", "Pune", "Nashik"],
    "amount": [100, 120, 90],
})
stores = pd.DataFrame({
    "city": ["Pune", "Pune", "Nashik"],
    "store_id": ["PUN-01", "PUN-02", "NAS-01"],
})
boom = orders.merge(stores, on="city", how="left")
print("orders", len(orders), "after merge", len(boom))
print(boom)

What you should see. 3 → 5 rows. Pune orders duplicated per store.

Worked example B — Fix with store_id.

orders2 = pd.DataFrame({
    "order_id": ["BLK-20", "BLK-21", "BLK-22"],
    "store_id": ["PUN-01", "PUN-02", "NAS-01"],
    "amount": [100, 120, 90],
})
stores2 = pd.DataFrame({
    "store_id": ["PUN-01", "PUN-02", "NAS-01"],
    "city": ["Pune", "Pune", "Nashik"],
    "manager": ["Ruhi Bagale", "Amir", "Zoya"],
})
ok = orders2.merge(stores2, on="store_id", how="left", validate="m:1")
print(len(orders2), len(ok))
print(ok)

Worked example C — indicator report.

orphan = pd.DataFrame({
    "order_id": ["BLK-99"],
    "store_id": ["UNK-09"],
    "amount": [50],
})
chk = orphan.merge(stores2, on="store_id", how="left", indicator=True)
print(chk[["order_id", "store_id", "_merge"]])

left_only means the store master is incomplete — fix the dimension table, do not “fill city with Pune” silently.

Ravindra Bagale's Tip

Merge nanantar len print. 10% row jump = red flag. validate='m:1' class madhe habit banva.