Detect schema drift between two loads

Pandas data engineering interview problem. Difficulty: intermediate. Pattern: Schema Drift. About 14 minutes. Part of the Pro drill bank.

Compare yesterday's and today's extract and list columns that were added, removed or changed type. Treat this as a production helper: match the contracted return shape, including empty and duplicate inputs.

old is yesterday's extract and new is today's. They should have the same columns with the same types, but upstream teams change files without telling anyone. Return 1 row for every column that differs, with the columns column and change, where change is one of: added: the column is only in new removed: the column is only in old type_changed: the column is in both but its pandas data type (dtype) is different, for example whole numbers versus decimals versus text Sort by column and use a clean index. If nothing changed, return an empty frame with those 2 columns. Assign the DataFrame to result.

Requirements

  • Return the columns column, change.
  • Sort by column and use a clean index.

Constraints

  • Column names are unique within a frame.
  • Names are compared exactly, including case.

Examples

Input: old user_id | email | age | plan 1 | [email protected] | 31 | free 2 | [email protected] | 45 | pro new user_id | email | age | country 3 | [email protected] | 29 | IN 4 | [email protected] | 41 | US Output: column | change age | type_changed country | added plan | removed age was whole numbers and is now text, plan disappeared and country is new. user_id and email kept their types.

Topics: lakebench, pandas, schema drift, dtype, columns.

More interview problems · All interview problems · Learn data engineering

intermediate

Detect schema drift between two loads

Interview-style drill: Compare yesterday's and today's extract and list columns that were added, removed or changed type.

`old` is yesterday's extract and `new` is today's. They should have the same columns with the same types, but upstream teams change files without telling anyone. Return 1 row for every column that differs, with the columns `column` and `change`, where `change` is one of: - `added`: the column is only in `new` - `removed`: the column is only in `old` - `type_changed`: the column is in both but its pandas data type (`dtype`) is different, for example whole numbers versus decimals versus text Sort by `column` and use a clean index. If nothing changed, return an empty frame with those 2 columns. Assign the DataFrame to `result`.