Source versus target reconciliation

SQL data engineering interview problem. Difficulty: advanced. Pattern: Joins. About 18 minutes. Part of the Pro drill bank.

After you copy data from one system to another, you must check that the copy is right. This is called reconciliation. src_balances is the source of truth. tgt_balances is the copy that was loaded somewhere else. Both have 1 row per account_id. Return every account that does not match, together with a status that says why. Use missing_in_target when the account is in the source but not in the target, extra_in_target when it is in the target but not in the source, and amount_mismatch when it is in both but the amount is different. Accounts that match are not returned. For this check, two NULL amounts count as equal, and NULL against a real number counts as different. Return account_id, status, src_amount and tgt_amount, ordered by account_id.

Requirements

• Find accounts that exist on only one side, and accounts on both sides with different amounts.
• Label each one with the right status text.
• Do not return accounts that match, including accounts where both amounts are NULL.
• Return account_id, status, src_amount, tgt_amount, ordered by account_id.

Constraints

• account_id is unique inside each table.
• An account can be in one table, the other, or both, so you need a join that keeps rows from both sides (a full outer join).
• Use IS DISTINCT FROM to compare amounts so that NULL is handled the way the task asks.

Examples

Input: src_balances account_id | amount 1 | 100 2 | 200 3 | 300 4 | NULL 5 | 500 tgt_balances account_id | amount 1 | 100 2 | 250 4 | NULL 5 | NULL 6 | 600 Output: account_id | status | src_amount | tgt_amount 2 | amount_mismatch | 200 | 250 3 | missing_in_target | 300 | NULL 5 | amount_mismatch | 500 | NULL 6 | extra_in_target | NULL | 600 Why this passes: Account 2 differs (200 vs 250). Account 3 is missing in the target. Account 5 differs because the target amount is NULL. Account 6 exists only in the target. Accounts 1 and 4 reconcile.

Input: src_balances account_id | amount 1 | 10 2 | 20 tgt_balances account_id | amount 1 | 11 Output: account_id | status | src_amount | tgt_amount 1 | amount_mismatch | 10 | 11 2 | missing_in_target | 20 | NULL Why this passes: One mismatch and one missing account.

Topics: lakebench, sql, reconciliation, full outer join, null-safe.

More SQL interview questions · All interview problems · Learn data engineering