Handling Schema Evolution | Dataford Interview Questions

How do you handle schema evolution within your data pipelines?

Write a SQL query that compares the latest two registered schema versions for each pipeline and identifies columns whose definition changed. Include newly added or removed columns, data type changes, and nullability changes.

Output

One row per detected column change, with pipeline_name, column_name, change_type, previous_data_type, current_data_type, previous_is_nullable, and current_is_nullable.
Include only the latest-versus-previous comparison for each pipeline.
Order by pipeline_name, then column_name.

Schema

schema_versions

Column	Type	Description
version_idPK	INT	Unique schema version identifier
pipeline_name	VARCHAR(100)	Name of the data pipeline
version_number	INT	Increasing version number within a pipeline
registered_at	DATE	Date the schema version was registered

schema_columns

Column	Type	Description
column_idPK	INT	Unique schema column record identifier
version_id	INT	Schema version containing the column
column_name	VARCHAR(100)	Column name
data_type	VARCHAR(50)	Declared SQL data type
is_nullable	BOOLEAN	Whether the column accepts NULL values

Your question is Handling Schema Evolution. Start with the requirements and the two tables in the Question tab.

Run and submit as often as you like. When you're ready, talk me through your approach or go straight to the code.

Problem