Source
Periodic public flight records
BTS publishes monthly On-Time Performance files. Separate archives need repeatable acquisition and consistent interpretation before they can support analysis.
RAW TO RELIABLE / ENGINEERING CASE STUDY / US DOT FLIGHTS
US DOT Flights Data Engineering Pipeline
Parameterized US DOT flight-data ingestion with Python, AWS S3, Snowflake, dbt dimensional modeling, and Airflow orchestration for carrier performance and delay attribution.
Monthly batch processing · flight-level models and analytical marts · a recorded manual Airflow run, with source-linked implementation details.
Project stateCompleted
Focus areas
- Monthly batch ingestion
- Airflow orchestration
- Partitioned S3 / Snowflake loading
- Dimensional modeling
- Carrier performance & delay causes
- Rerun safeguards
Technology stack
01 / Executive Overview
Executive Overview
Turn periodic public flight files into repeatable analytical models.
Pipeline
One month at a time
Python selects a year and month, extracts the CSV, compresses it to GZIP, and places it in a partitioned S3 location.
Warehouse
Preserve the raw record
Snowflake COPY maps staged CSV fields into RAW_FLIGHTS and adds file provenance and load timestamps.
Modeling
Staging / Core / Mart
Seven SQL models normalize records, define flight and dimension tables, and aggregate carrier performance and delay causes. Staging selects AA, UA, and DL.
Orchestration
Five dependent Airflow tasks
A manual DAG coordinates download, compression and upload, warehouse loading, dbt run, and dbt test. Year/month parameters support historical-month selection.
Analytical output
Questions encoded in SQL
Two monthly marts expose flight volume, on-time performance, cancellations, and delay-cause shares. These are analytical model definitions; no BI dashboard is claimed.
- Airflow tasks
- 05
- dbt SQL models
- 07
- Staging test declarations
- 17
The implementation supports flight, carrier, cancellation, and delay-cause analysis. Counts above describe checked-in definitions, not processed flights or passed tests.
02 / System Architecture
System Architecture
Airflow coordinates the workflow above the data path; it is not a downstream destination.
DOT FLIGHTS / DATA & CONTROL PATHS / FIG. 01
- Source-defined movement / dependency
- Documented / manual relationship
- Orchestration / control
Apache Airflow / manual orchestration and control
01 / Airflow task
Download
BTS ZIP → CSV
- Then
02 / Airflow task
Compress & upload
GZIP → S3
- Then
03 / Airflow task
Load Snowflake
COPY INTO
- Then
04 / Airflow task
Build models
Staging / Core / Mart
- Then
05 / Airflow task
Run tests
dbt test
Acquisition / monthly BTS file to partitioned S3 object
01 / Source
BTS monthly ZIP
Year / month selection
- Read
02 / Acquisition
Python → CSV
Stream / extract
- gzip
03 / Compression
GZIP
One CSV stream
- Upload
04 / Landing
AWS S3
year=YYYY / month=MM
Warehouse loading / continues from S3
01 / Input
AWS S3
Partitioned GZIP files
- Setup
02 / Access
External stage
Storage Integration
- Read
03 / Loading
COPY INTO
Matching .csv.gz files
- Load
04 / Raw layer
RAW_FLIGHTS
Source fields + metadata
dbt modeling / continues from RAW_FLIGHTS
01 / Input
RAW_FLIGHTS
Loaded raw history
- source()
02 / Normalize
Staging
stg_flights / view
- ref()
03 / Model
Core
One fact / three dimensions
- ref()
04 / Aggregate
Marts
Two carrier-month models
Control follows five task boundaries
The first two tasks acquire and upload a monthly file. The third loads Snowflake; the final two invoke dbt run and dbt test. Compression and S3 upload share one task.
Month selection has a loading boundary
Download and upload use year/month parameters. COPY scans matching GZIP files across the configured stage, and dbt rebuilds models from loaded raw history. Neither operation is restricted to the selected month’s XCom key.
03 / Apache Airflow Orchestration
Apache Airflow Orchestration
One dependency chain, explicit handoffs, and a recorded manual run.
DOT FLIGHTS / AIRFLOW DAG / FIG. 02
- Source-defined movement / dependency
- Documented / manual relationship
- Orchestration / control
dot_flights_pipeline / task dependency chain
01 / Task 1
download_dot_data
Stream the BTS ZIP and extract its CSV.
- Then
02 / Task 2
compress_and_upload_s3
GZIP the CSV, hash the file, and upload it to S3.
- Then
03 / Task 3
copy_into_snowflake
Execute COPY INTO and print loading results and a total row count.
- Then
04 / Task 4
dbt_run
Invoke dbt run in the configured container project directory.
- Then
05 / Task 5
dbt_test
Invoke dbt test after the model build succeeds.
01 / Download
download_dot_dataStream the BTS ZIP and extract its CSV.
- Input
- year / month parameters
- Output
- Local CSV path via XCom
- Rerun / failure behavior
- Existing ZIP skips download; extraction still runs. Missing CSV raises an error.
02 / Compress & upload
compress_and_upload_s3GZIP the CSV, hash the file, and upload it to S3.
- Input
- CSV path via XCom
- Output
- Partitioned GZIP object, metadata log, S3 key via XCom
- Rerun / failure behavior
- Existing GZIP and S3 object skip those writes. A timestamped ingestion log is still written.
03 / Load Snowflake
copy_into_snowflakeExecute COPY INTO and print loading results and a total row count.
- Input
- Configured external stage
- Output
- RAW_FLIGHTS rows with provenance
- Rerun / failure behavior
- FORCE = FALSE uses file-load history. COPY scans all matching stage files, not the month-specific XCom key.
04 / Build models
dbt_runInvoke dbt run in the configured container project directory.
- Input
- Loaded raw records and dbt project
- Output
- Staging view, core tables, and mart tables
- Rerun / failure behavior
- No month selector is passed to dbt. The table models rebuild from the loaded raw history.
05 / Run tests
dbt_testInvoke dbt test after the model build succeeds.
- Input
- Built models and YAML declarations
- Output
- dbt command exit status
- Rerun / failure behavior
- A failing command fails the task. The recorded screenshot shows task success, without individual test results.
schedule=None,
start_date=datetime(2024, 1, 1),
catchup=False,task_download >> task_upload_s3 >> task_copy_snowflake >> task_dbt_run >> task_dbt_testHistorical-month execution
Runtime parameters default to year 2024 and month 1. Selecting another month supports a manual historical run. There is no automatic month loop or scheduled backfill; data-model coverage beyond 2024 needs changes described below.
Containerized orchestration
Compose defines LocalExecutor, a PostgreSQL metadata database, initialization, webserver, and scheduler services. The image uses Airflow 2.10.4 with Python 3.11 and installs dbt-snowflake, boto3, requests, and the Snowflake connector.
Original project screenshot of one successful manual Airflow run. The five task states include dbt_test success; individual dbt test results, loaded row counts, processed months, and the executed source revision are not shown.
Source: image linked in the audited DOT repository README. Full image preserved; the supplied image already obscures date fields.This recorded output supports one manual run’s successful task states. It does not identify the executed source revision or prove that each of the 17 current test declarations ran and passed. Reproduction gaps in the public checkout are listed in Evidence.
04 / Ingestion & Snowflake Loading
Ingestion & Snowflake Loading
Separate source acquisition, object identity, and warehouse loading contracts.
- 01 / AcquireZIP → CSV
Stream the parameterized BTS URL, extract the archive, and select its CSV. The standalone download script prints a lightweight row/header inspection.
- 02 / PrepareCSV → GZIP
Stream compression instead of loading the whole file in memory. Compute file size and SHA256 for the generated metadata log.
- 03 / LandPartitioned S3 object
Use a deterministic monthly key. The standalone uploader also performs a post-upload existence check.
- 04 / LoadCOPY → RAW_FLIGHTS
Map positional CSV fields and inject METADATA$FILENAME and CURRENT_TIMESTAMP for provenance.
s3_key = (
f"{S3_PROJECT_PREFIX}/raw/flights/"
f"year={year}/month={month:02d}/{gz_filename}"
)dot_flights/raw/flights/year=2024/month=01/on_time_2024_01.csv.gz is an illustrative expansion of the verified key template, not a recorded object listing.
Storage access is a setup boundary
The integration template associates external-stage access with an IAM role and allowed S3 locations. The stage references that integration. Owner configuration and the trust relationship must be established before loading; no credentials or private storage identifiers are reproduced here.
Loading needs reconciliation
COPY overrides the stage’s uncompressed default with GZIP, skips the CSV header, and uses ON_ERROR = CONTINUE with FORCE = FALSE. The DAG prints COPY results and a total raw row count, but does not fail on rejected-record counts or validate a monthly expected total.
05 / dbt Data Modeling
dbt Data Modeling
Normalize first, retain a flight-level fact, then aggregate at carrier-month grain.
stg_flights is a view over RAW_FLIGHTS, filtered to AA, UA, and DL. It casts dates, numeric measures, and status flags; handles the 2400 time sentinel; and carries file/load metadata forward. Four core tables separate flight measures from carrier, calendar, and airport attributes.
DOT FLIGHTS / DIMENSIONAL MODEL / FIG. 03
Fact / Declared scheduled flight leg
fact_flights
flight_key / MD5 identifiercarrier_key · date_keyorigin_airport_key · dest_airport_keyflight_date · flight_number · tail_numberdelay minutes · distance_milesis_cancelled · is_divertedsource_file · loaded_at
carrier_key
dim_carrier
Carrier code and metadata; AA, UA, DL names are explicitly mapped.
carrier_code · carrier_nameiata_code_carrier · dot_id_carrier
date_key
dim_date
YYYYMMDD key; a bounded 2024 date spine with a source-derived upper filter.
full_date · year · month_numquarter · day_of_week · is_weekend
origin_airport_key → airport_key
dim_airport
Origin role; airport attributes are derived from both origin and destination records.
airport_code · airport_idcity · state · region
dest_airport_key → airport_key
dim_airport
Destination role; the same airport dimension serves this second relationship.
airport_code · airport_idcity · state · region
MD5(
COALESCE(carrier_code, '') || '-' ||
COALESCE(CAST(flight_number AS VARCHAR), '') || '-' ||
COALESCE(CAST(flight_date AS VARCHAR), '') || '-' ||
COALESCE(origin, '') || '-' ||
COALESCE(dest, '') || '-' ||
COALESCE(tail_number, '')
) AS flight_key,Declared grain and identifier assumptions
The fact declares one scheduled leg per carrier, flight number, date, origin, and destination. Its hash also includes tail_number. It neither deduplicates rows nor filters out cancellations. MD5 does not establish uniqueness; repeated inputs and NULL-to-empty substitution retain row-identity assumptions.
Dimension attributes need key validation
Carrier rows use DISTINCT across carrier attributes; airports UNION distinct origin/destination attribute rows. Neither operation proves one row per key. Airport states map to Northeast, Midwest, South, West, or Territory/Other using the source’s Census-style grouping.
DATEADD(DAY, seq4(), '2024-01-01'::DATE) AS full_date
FROM TABLE(GENERATOR(ROWCOUNT => 366))dim_date filters this fixed range using MAX(flight_date) from staging. It does not derive a dynamic lower bound or expand beyond 366 dates. All three dbt layers are explicit: Staging / Core / Mart.
06 / Data Quality & Validation
Data Quality & Validation
Show what is declared, what the recorded run shows, and which checks remain absent.
not_null
12 declarations
Staging identifiers, flags, distance, year/month, and provenance. The distance test is unconditional, despite its description referring to operated flights.
accepted_values
5 declarations
Carrier AA/UA/DL; cancellation and diversion booleans; flight_year = 2024; flight_month from 1 to 12.
Core-key coverage
No declarations
No unique or relationships tests are defined for the flight identifier, dimensions, or foreign keys. Their presence in SQL does not prove stable join cardinality.
Defined: 17 staging checks
The YAML defines 12 not_null and five accepted_values checks, all on stg_flights. These protect essential fields and selected categorical domains. It does not declare tests on cancellation codes or delay flags, despite the broader README description.
Recorded: dbt_test task success
The Airflow image shows a successful dbt_test task. No manifest, run_results, or test log is published to establish how many checks were executed or passed. A successful command in that recorded environment does not verify current core joins.
Historical inputs beyond 2024 conflict with the accepted-year test and date coverage. The actual definitions determine the presentation; README claims about unique and relationship tests are excluded.
07 / Analytical Data Marts
Analytical Data Marts
Two implemented SQL marts answer performance and delay-cause questions without invented KPI results.
Analytical table / carrier-month
mart_
Compare carrier reliability month by month.
- Aggregation grain
carrier_key / carrier_name / year / month_num / month_name- Implemented measures
total_flightscancelled_count / cancelled_pctdiverted_count / diverted_pcton_time_count / on_time_pctavg_dep_delay / avg_arr_delayp75_arr_delay / p95_arr_delay
- Interpretation
- On-time counts require arr_delayed_15 = 0 and exclude cancelled or diverted flights; the percentage denominator is total joined flight rows. Delay means exclude cancellations. The percentile expressions have no explicit cancellation filter.
Analytical table / carrier-month
mart_
Separate reported delay minutes by cause.
- Aggregation grain
carrier_key / carrier_name / year / month_num / month_name- Implemented measures
delayed_flights_counttotal_carrier_delay_min / pct_carriertotal_weather_delay_min / pct_weathertotal_nas_delay_min / pct_nastotal_security_delay_min / pct_securitytotal_late_aircraft_delay_min / pct_late_aircrafttotal_delay_min
- Interpretation
- Includes arr_delayed_15 = 1 and non-cancelled flights; no separate diversion exclusion is applied. NULLIF guards a zero denominator. Missing cause sums can propagate NULL into the combined total.
SUM(CASE WHEN arr_delayed_15 = 0 AND NOT is_cancelled AND NOT is_diverted THEN 1 ELSE 0 END) AS on_time_count,
ROUND(on_time_count * 100.0 / total_flights, 2) AS on_time_pct,The delay model’s actual filename is mart_delay_cause_breakdown_monthly, rather than the README’s attribution name. These measures describe model logic, not recorded results, causal findings, or a dashboard. Unverified dimension cardinality can affect joined aggregates.
08 / Engineering Decisions & Reliability
Engineering Decisions & Reliability
Explain the processing contracts and tradeoffs behind a repeatable monthly workflow.
Source + documentation
Make the processing month an explicit input
- Observed problem
- Historical files need repeatable selection without hardcoding a new download URL each time.
- Root cause
- BTS distributes this source as monthly archives.
- Engineering solution
- Use year/month DAG parameters and CLI arguments; zero-pad the storage month while leaving the BTS URL month unpadded.
- Validation / result
- The code derives the URL and partition key from those inputs. Each manual trigger selects one month; automatic catchup is disabled.
Source evidence
Source + documentation
Separate archive acquisition from warehouse format
- Observed problem
- The source arrives as a ZIP archive, while COPY is configured for GZIP CSV input.
- Root cause
- The download format and warehouse loading contract differ.
- Engineering solution
- Extract the CSV, stream it into GZIP, and record SHA256 and file size in an ingestion log.
- Validation / result
- gzip.open and shutil.copyfileobj implement compression; the COPY statement explicitly declares COMPRESSION = GZIP. No measured compression savings are claimed.
Source evidence
Source + documentation
Treat rerun protection as stage-specific
- Observed problem
- Monthly processing can be retried or manually rerun.
- Root cause
- Local files, S3 objects, warehouse files, and modeled rows have different identities.
- Engineering solution
- Check existing local paths and S3 objects; use FORCE = FALSE for COPY and table materializations for dbt models.
- Validation / result
- These guards are visible in code. They do not compare source freshness or deduplicate business-key rows, so universal end-to-end idempotency is not established.
Source evidence
Source + documentation
Build deterministic flight identifiers, then test the grain
- Observed problem
- Flight analysis needs a consistent identifier across selected attributes.
- Root cause
- The fact model declares a scheduled-leg grain and also includes tail_number in its hash input.
- Engineering solution
- MD5 six fields using hyphen separators and COALESCE to an empty string.
- Validation / result
- The expression is deterministic for the same inputs. Duplicate combinations remain duplicates; NULL and empty strings coincide. Delimiter ambiguity and hash collisions remain assumptions, and no unique test proves row identity.
Source evidence
Source + documentation
Keep date coverage explicit during backfill
- Observed problem
- Historical-month inputs must be covered by the date dimension and validation rules.
- Root cause
- The checked-in spine starts in 2024 and generates only 366 candidate dates; the staging year test accepts only 2024.
- Engineering solution
- Filter the generated dates by MAX(flight_date) from staging, within that fixed range.
- Validation / result
- The upper filter adapts within 2024. Extending backfill beyond that year requires changing both date coverage and the accepted-year test; the README's fully dynamic claim is not implemented.
Source evidence
Rerun strategy by stage
- Local download / compression
Skip ZIP or GZIP creation if the path exists.
Existence does not validate completeness or freshness; interrupted downloads are not written atomically.
- S3 upload
head_object checks the expected monthly object key.
Any ClientError is treated as absence. The recorded SHA256 is not compared with an existing object; new metadata logs are written on reruns.
- Snowflake COPY
FORCE = FALSE applies file-load-history protection.
No row-level MERGE or business-key deduplication is implemented. ON_ERROR = CONTINUE requires reconciliation of skipped records.
- dbt models
Staging is a view; core and marts materialize as tables.
This rebuilds selected raw history rather than incrementally merging one month. Repeatability still depends on raw records and join grain.
Bound warehouse idle time
The setup SQL configures an XSMALL warehouse, AUTO_SUSPEND = 60, AUTO_RESUME = TRUE, and an initially suspended state. These are cost-aware settings; billing outcomes or measured savings are not established.
Retry the failed task, inspect the handoff
Tasks inherit one retry with a two-minute delay. Reruns rely on local artifacts, object existence, and COPY history. Inspect rejected COPY rows and validate model grain before interpreting a successful task chain as complete data processing.
09 / Evidence, Limitations & Source Code
Evidence, Limitations & Source Code
Source definitions and one recorded manual run support the walkthrough, with their boundaries visible.
What the source establishes
Parameterized acquisition, GZIP compression, partitioned S3 keys, loading templates and COPY logic, five dependent tasks, seven dbt SQL models, and 17 staging test declarations. No source services were executed during this presentation audit.
What the screenshot establishes
One recorded manual run of dot_flights_pipeline with five successful task states and no configured schedule. It does not establish continuously scheduled production operation, a completed backfill inventory, individual passed-test counts, or analytical KPI values.
Public checkout needs path alignment
The actual DAG is Airflow/dot_flights_pipeline.py, outside the absent dags/ mount. Compose references an absent dbt_dot_flights directory; the checked-in dbt project is under dbt/ and expects models/, while SQL is under Models/. There is no requirements.txt; dependencies are installed in the Dockerfile. The recorded environment cannot be reproduced directly from this layout.
Setup and multi-year coverage need validation
Storage SQL contains placeholder role/location values and uses ENABLE where the integration property is ENABLED. The stage location needs owner configuration. Date coverage is bounded to 2024, and staging accepts only that year. No published run logs resolve these snapshot discrepancies.
Shared limits: no flight-key uniqueness or relationship tests; no reconciliation results for skipped COPY rows; no verified row counts, historical-month completion, compression savings, or cloud billing. Staging time expressions also mix departure-time conversion with arrival-time strings; typed runtime output is not published.
Reviewed source revision e3628dc. Evidence links target actual files at that revision. Source credentials and private identifiers were excluded from copied content and excerpts.
- View Compose Configuration Opens in a new tab
- View dbt Configuration Opens in a new tab
- View dbt Source Definitions Opens in a new tab
- View Test Declarations Opens in a new tab
- View Storage Integration Template Opens in a new tab
- Snowflake Integration Syntax Opens in a new tab
- View Project Notes & Recorded Run Opens in a new tab