Detect schema drift between two batches

Yesterday's batch and today's do not have the same columns. Before the load runs, the pipeline wants to know exactly what changed — and wants to stay quiet when nothing did.

Input

before — a list of (column_name, type_name)
after — the same, for the newer batch

Column names are unique within a batch. The two lists are not necessarily in the same order.

Output

A list of (column_name, change), where change is one of:

'ADDED' — in after only
'REMOVED' — in before only
'TYPE_CHANGED' — in both, with a different type

Order is not graded.

Example

before [('id', 'int'), ('name', 'string')] after [('id', 'int'), ('email', 'string')] returns [('name', 'REMOVED'), ('email', 'ADDED')]

Constraints

Compare by name, never by position. A SELECT that lists its columns in a different order has not drifted, and reporting that it has is the false alarm that gets this check switched off.
A schema that did not change produces an empty list.

Community-reported interview topic. Not an official company question and no affiliation is implied.