Audit flight data quality from metadata

You’re given an airline on‑time dataset and a one‑page “Metadata” slide that claims: flight_date (string, local time), dep_time/arr_time (HHMM local), dep_delay/arr_delay (minutes, negative allowed), cancelled (0/1), tail_num (aircraft id), origin, dest, aircraft_type (optional), weather_code (optional). The modeling target will be arrival delay in minutes. Audit data quality before modeling: 1) List at least 8 concrete, high‑risk issues you would proactively test for (e.g., timezone mixups, impossible HHMM values like 2460, clock changes around DST, negative delays beyond physical plausibility, cancelled flights with non‑null delays, duplicated tail_num semantics, leakage via “arr_delay” filled post‑facto, inconsistent units). 2) For each, specify an exact detection check (e.g., dep_time not matching ^\d{3,4}$ OR casted minutes >= 24*60; arr_delay < -60 OR > 600; origin/dest not in allowed IATA set) and how you’d quantify impact (prevalence, row counts, feature coverage). 3) Propose mitigation that avoids target leakage (e.g., remove post‑arrival features, impute within training folds, align to UTC with airport TZ map, drop DST transition minutes) and describe how you’d verify the fixes with before/after distributions and holdout data. 4) Define a lightweight data contract for upstream producers (types, ranges, allowed nulls, semantic rules) and how you’d enforce it in CI with schema tests and sampling.

Detect invalid flight times and implausible delays

You are given a simplified airline on-time performance table. The metadata says: flight_date (local date), dep_time/arr_time (HHMM local as strings), dep_delay/arr_delay (minutes, can be negative), cancelled (0/1), plus some identifiers.

Write a SQL query that audits three high‑risk data quality issues and returns one row per issue with a count of affected flights:

1. INVALID_DEP_TIME: departure time is missing or not a valid HHMM between 00:00 and 23:59.
2. INVALID_ARR_TIME: arrival time is missing or not a valid HHMM between 00:00 and 23:59.
3. IMPLAUSIBLE_NEG_DELAY: dep_delay or arr_delay is less than −60 minutes.
4. IMPLAUSIBLE_POS_DELAY: dep_delay or arr_delay is greater than 600 minutes.
5. CANCELLED_WITH_DELAYS: cancelled = 1 but dep_delay or arr_delay is not NULL.

Treat dep_time/arr_time as strings in HHMM format (3 or 4 digits allowed, e.g. '930' or '0930'). A time is invalid if it is NULL, not 3–4 characters long, the numeric value is outside 0000–2359, or the minutes portion is 60 or greater.

Your query should output three columns:

• issue_type (one of the 5 labels above)
• issue_description (a short human-readable description)
• issue_count (number of flights that violate the rule)

Table flights

column	type	constraints
flight_id	INT	PRIMARY KEY, NOT NULL
flight_date	DATE	NOT NULL
dep_time	VARCHAR(4)
arr_time	VARCHAR(4)
dep_delay	INT
arr_delay	INT
cancelled	INT	NOT NULL
tail_num	VARCHAR(10)
origin	CHAR(3)	NOT NULL
dest	CHAR(3)	NOT NULL
aircraft_type	VARCHAR(20)
weather_code	VARCHAR(10)
data_extract_date	DATE	NOT NULL

Sample data:

aircraft_type	arr_delay	arr_time	cancelled	data_extract_date	dep_delay	dep_time	dest	flight_date	flight_id	origin	tail_num	weather_code
A320	5	1130	0	2023-01-11	10	0930	LAX	2023-01-10	1	JFK	N123AA	CLR
A320	0	1400	0	2023-01-09	0	2460	LAX	2023-01-10	2	JFK	N124AA	CLR
B737	-10	1010	0	2023-01-12	-5	0815	JFK	2023-01-11	3	SFO	N125AA	RAIN
B737	-80	0900	0	2023-01-12	-120	0700	JFK	2023-01-11	4	SFO	N126AA	CLR
A320	650	1500	0	2023-01-13	700	1300	LAX	2023-01-12	5	ABC	N127AA	CLR
A320	NULL	1300	1	2023-01-13	NULL	1100	JFK	2023-01-12	6	JFK	N128AA	SNOW
A320	0	1100	1	2023-01-14	0	0900	JFK	2023-01-13	7	LAX	N129AA	CLR
A320	20	0120	0	2023-02-20	0	2359	XXX	2023-03-01	8	JFK	N130AA	NULL
NULL	0	0899	0	2023-03-03	0	1000	SFO	2023-03-02	9	LAX	NULL	CLR

Expected output

One row per issue type with the number of flights violating each rule, based on the sample flights data.

issue_count	issue_description	issue_type
1	Departure time missing or not a valid HHMM between 0000 and 2359	INVALID_DEP_TIME
1	Arrival time missing or not a valid HHMM between 0000 and 2359	INVALID_ARR_TIME
1	Departure or arrival delay less than -60 minutes	IMPLAUSIBLE_NEG_DELAY
1	Departure or arrival delay greater than 600 minutes	IMPLAUSIBLE_POS_DELAY
1	Cancelled flights with non-null delay values	CANCELLED_WITH_DELAYS

Hints

1. Treat dep_time and arr_time as strings; use LPAD and SUBSTRING to extract hours and minutes.
2. Use UNION ALL to stack separate issue-specific COUNT(*) queries into a single result set.

Check airport codes and detect potential target leakage

You are continuing the data quality audit on the same flights dataset, now focusing on referential integrity and potential target leakage for the modeling target arr_delay.

You are given an airports dimension table listing valid IATA airport codes. Using the flights and airports tables, write a SQL query that returns one row per data quality issue with a count of affected flights for these rules:

1. INVALID_ORIGIN: flights whose origin airport code is not present in airports.iata_code.
2. INVALID_DEST: flights whose dest airport code is not present in airports.iata_code.
3. ORIGIN_EQUALS_DEST: flights where origin = dest.
4. LEAKED_ARR_DELAY: flights where data_extract_date < flight_date AND arr_delay IS NOT NULL (arrival delay appears to be known before the flight date, indicating potential target leakage).

Output columns:

• issue_type (one of the 4 labels above)
• issue_description (short description of the rule)
• issue_count (number of flights that violate the rule)

Table flights

column	type	constraints
flight_id	INT	PRIMARY KEY, NOT NULL
flight_date	DATE	NOT NULL
dep_time	VARCHAR(4)
arr_time	VARCHAR(4)
dep_delay	INT
arr_delay	INT
cancelled	INT	NOT NULL
tail_num	VARCHAR(10)
origin	CHAR(3)	NOT NULL
dest	CHAR(3)	NOT NULL
aircraft_type	VARCHAR(20)
weather_code	VARCHAR(10)
data_extract_date	DATE	NOT NULL

Sample data:

aircraft_type	arr_delay	arr_time	cancelled	data_extract_date	dep_delay	dep_time	dest	flight_date	flight_id	origin	tail_num	weather_code
A320	5	1130	0	2023-01-11	10	0930	LAX	2023-01-10	1	JFK	N123AA	CLR
A320	0	1400	0	2023-01-09	0	2460	LAX	2023-01-10	2	JFK	N124AA	CLR
B737	-10	1010	0	2023-01-12	-5	0815	JFK	2023-01-11	3	SFO	N125AA	RAIN
B737	-80	0900	0	2023-01-12	-120	0700	JFK	2023-01-11	4	SFO	N126AA	CLR
A320	650	1500	0	2023-01-13	700	1300	LAX	2023-01-12	5	ABC	N127AA	CLR
A320	NULL	1300	1	2023-01-13	NULL	1100	JFK	2023-01-12	6	JFK	N128AA	SNOW
A320	0	1100	1	2023-01-14	0	0900	JFK	2023-01-13	7	LAX	N129AA	CLR
A320	20	0120	0	2023-02-20	0	2359	XXX	2023-03-01	8	JFK	N130AA	NULL
NULL	0	0899	0	2023-03-03	0	1000	SFO	2023-03-02	9	LAX	NULL	CLR

Table airports

column	type	constraints
iata_code	CHAR(3)	PRIMARY KEY, NOT NULL
name	VARCHAR(100)	NOT NULL
city	VARCHAR(100)

Sample data:

city	iata_code	name
New York	JFK	John F. Kennedy International Airport
Los Angeles	LAX	Los Angeles International Airport
San Francisco	SFO	San Francisco International Airport

Expected output

One row per issue type with the number of flights violating each referential or leakage rule, based on the sample flights and airports data.

issue_count	issue_description	issue_type
1	Flights whose origin airport code is not in the airports table	INVALID_ORIGIN
1	Flights whose dest airport code is not in the airports table	INVALID_DEST
1	Flights with the same origin and destination airport code	ORIGIN_EQUALS_DEST
2	Flights where data_extract_date < flight_date and arr_delay is non-null	LEAKED_ARR_DELAY

Hints

1. Use LEFT JOIN from flights to airports to find airport codes that do not match any row in the airports table.
2. Detect potential leakage by comparing data_extract_date to flight_date and checking for non-null arr_delay.