Conflicting Records Resolution — Solution & ExplanationProblems / Conflicting Records Resolution / EditorialApproach

FULL OUTER JOIN billing and warehouse on order_id, because each system has orders the other has never seen. A left or inner join would silently lose warehouse-only (or billing-only) orders.

Coalesce the key. After a full outer join, b.order_id is NULL for warehouse-only rows and w.order_id is NULL for billing-only rows, so the output key is COALESCE(b.order_id, w.order_id).

Resolve the amount with COALESCE(b.amount, w.amount) — billing wins whenever it has a real value, warehouse fills every other case.

Attribute the winner honestly. source must test the same condition COALESCE used: CASE WHEN b.amount IS NOT NULL THEN 'billing' ELSE 'warehouse' END. Testing row existence instead (b.order_id IS NOT NULL) mislabels the trap row — an order present in billing with a NULL amount is resolved from warehouse, and the label has to say so.

Order by order_id.

Key InsightRecord resolution is a precedence rule, and the resolved value and its provenance label must be computed from the same predicate. COALESCE(b.amount, w.amount) encodes "billing wins when non-NULL"; the moment the source column encodes anything else — like "billing wins when the row exists" — the two columns can contradict each other. The dataset forces the distinction: orders in both systems with conflicting amounts (billing wins), orders in exactly one system, and an order present in billing whose amount is NULL (warehouse wins despite billing's presence).Hints

Some orders live only in billing, some only in warehouse — a LEFT JOIN from either side silently drops the other side's exclusives. FULL OUTER JOIN keeps every order from both systems.

After a full outer join either side's order_id can be NULL, so the output key must be COALESCE(b.order_id, w.order_id). The resolved amount is the same trick: COALESCE(b.amount, w.amount).

The subtle case: order 105 exists in BOTH systems but billing's amount is NULL. COALESCE correctly falls through to warehouse — so source must be derived from 'is billing.amount non-NULL?' (CASE WHEN b.amount IS NOT NULL ...), NOT from 'does a billing row exist?' (b.order_id IS NOT NULL gets it wrong).

In PySpark, rename the right side's columns (w_order_id, warehouse_amount) before joining; then F.coalesce + F.when/otherwise mirror the SQL exactly.

Solve Conflicting Records Resolution yourself →