Diff two snapshots of a table

Pandas data engineering interview problem. Difficulty: intermediate. Pattern: Reconciliation. About 16 minutes. Part of the Pro drill bank.

Compare yesterday's and today's table snapshots to detect added, removed, and modified records by primary key. Treat this as a production helper: match the contracted return shape, including empty and duplicate inputs.

Given two snapshot DataFrames snap1 (yesterday) and snap2 (today) with columns id, name, and status: Identify all record-level changes across the two snapshots: added: key exists in snap2 but not in snap1 removed: key exists in snap1 but not in snap2 modified: key exists in both, but name or status has changed value Records that are identical across both snapshots must be excluded. Return columns: id, diff_type. Sort by id ascending and reset the index. Assign the final DataFrame to result.

Requirements

• Filter out unchanged rows completely.
• Sort by id ascending.

Constraints

• id is a unique primary key within each snapshot.
• Column names and types match across snapshots.

Examples

Input: snap1 id | name | status 1 | Alice | active 2 | Bob | pending 3 | Charlie | active snap2 id | name | status 2 | Bob | active 3 | Charles | active 4 | David | pending Output: id | diff_type 1 | removed 2 | modified 3 | modified 4 | added ID 1 is gone, 2 and 3 changed status/name, and 4 is brand new.

Topics: lakebench, pandas, compare, outer-merge.

More interview problems · All interview problems · Learn data engineering