Checking joins before you trust them

Before the join

Check the lookup key is unique, trimmed and not missing. State the expected cardinality (many-to-one is the usual lookup).

During the join

Use a left join from the table whose rows you must keep. In pandas use validate= and indicator=True; in SQL count matched and unmatched rows.

After the join

Compare row counts and totals before and after. Report unmatched rows as orphans with a reason.

• Rows in = rows out for a many-to-one lookup
• Totals unchanged
• Orphans reported, not dropped