Maintaining Integrity During Migration | Dataford Interview Questions

How do you handle data integrity when migrating records between environments?

Using the supplied source_records and target_records tables, write a SQL query that identifies records requiring investigation after a migration. Compare the identifying and data attributes, and distinguish missing, extra, and changed records.

Output

One row per migration exception.
Columns: source_record_id, target_record_id, integrity_status, source_name, target_name, source_status, target_status.
Exclude records that match completely. Order by the available record identifier ascending.

Schema

source_records

Column	Type	Description
record_idPK	INTEGER	Identifier of the source record
nhs_number	VARCHAR(10)	Illustrative NHS number value
full_name	VARCHAR(100)	Name stored in the source environment
postcode	VARCHAR(10)	Postcode stored in the source environment
active	BOOLEAN	Whether the source record is active

target_records

Column	Type	Description
target_record_idPK	INTEGER	Identifier assigned in the target environment
source_record_id	INTEGER	Source identifier associated with the migrated record
nhs_number	VARCHAR(10)	Illustrative NHS number value in the target
full_name	VARCHAR(100)	Name stored in the target environment
postcode	VARCHAR(10)	Postcode stored in the target environment
active	BOOLEAN	Whether the target record is active

Your question is Maintaining Integrity During Migration. 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