Built an incremental data warehouse that transforms monthly Bureau of Transportation Statistics flight files into a dimensional model and analytics for U.S. domestic airline performance.
Architecture
A streaming Python downloader retrieves monthly BTS archives, validates the extracted CSV, and uploads it to a Snowflake internal stage. SQL loads the raw data, normalizes and deduplicates staging records, and merges airline, airport, and date dimensions into a flight fact table. Apache Airflow orchestrates the pipeline through ten tasks, with retries, failure tracking, and recorded stage metrics. Docker Compose runs the local Airflow services.
Incremental Loading
- Successful monthly checkpoints skip files that have already been loaded
- Forced reruns replace only the selected month's raw and staging records
- Deterministic SHA-256 flight keys and SQL MERGE prevent duplicate fact rows
- Source checksums, source months, run IDs, and timestamps preserve data lineage
Data Quality
Streaming validation checks required columns, source-month agreement, dates, route identifiers, distance bounds, and duplicate natural keys before upload. Snowflake records source-to-raw, raw-to-staging, and staging-to-fact reconciliation results in a metadata schema. Reconciliation failures stop the run, while invalid source rows are recorded and excluded from the fact table.
Analytics
- Airline performance metrics for flight volume, cancellations, arrival delays, and on-time percentage
- Directional route analysis for volume, delay, cancellation rate, and elapsed time
- Monthly airline delay breakdowns for carrier, weather, airspace, security, and late-aircraft causes
Measured Results
The documented October 1, 2026 checkpoint contains 7,079,060 flight records from all twelve 2024 source months, with 15 airlines, 348 airports, and 366 calendar dates. Average monthly pipeline runtime was 96.859 seconds, and the three analytics mart queries took 0.218 to 0.287 seconds. One invalid source row was rejected. The January idempotency check retained 547,271 fact rows after both forced runs, with zero new duplicates.