Validating Data Integrity Across Regions | Dataford Interview Questions

How do you validate data integrity when dealing with large, messy datasets from multiple regional databases for Uber?

For this SQL exercise, use the supplied canonical trip data, regional records, and region reference data to identify integrity issues. Return one validation result per trip identifier, including records that appear only in a regional database.

Output

One row per distinct trip identifier
Include canonical and regional values, regional record count, and an issue_flags classification
Order by trip_id ascending

Schema

canonical_trips

Column	Type	Description
trip_idPK	VARCHAR(20)	Canonical Uber trip identifier
region_code	VARCHAR(5)	Canonical operating region
fare_amount	DECIMAL(10,2)	Canonical trip fare
status	VARCHAR(20)	Canonical trip status

regional_trip_records

Column	Type	Description
regional_record_idPK	INT	Regional source record identifier
trip_id	VARCHAR(20)	Trip identifier received from a regional database
region_code	VARCHAR(5)	Region reported by the regional source
fare_amount	DECIMAL(10,2)	Fare reported by the regional source
status	VARCHAR(20)	Trip status reported by the regional source

region_codes

Column	Type	Description
region_codePK	VARCHAR(5)	Approved region code
region_name	VARCHAR(100)	Region display name

Your question is Validating Data Integrity Across Regions. Start with the requirements and the three 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