Airline On-Time Performance Pipeline
72,961,836 flight records — every month of US BTS data since January 2015, ~30 GB of raw CSV — cleaned by Spark into partitioned Parquet, modeled in Snowflake with dbt, gated by 36 dbt tests and a 16-expectation Great Expectations checkpoint, orchestrated by a monthly Airflow DAG that ingested a new month autonomously, with the estate in Terraform and delivery to Power BI over the REST API.
01 Overview
A data-engineering pipeline built at a size where the engineering decisions actually matter: 72,961,836 US flight records — every month from January 2015 to the present, ~30 GB of raw CSV from the Bureau of Transportation Statistics — cleaned, warehoused, tested, orchestrated, and delivered to a dashboard.
The distinguishing property is that it stays current on its own: a monthly Airflow DAG whose first scheduled run ingested a new month of data end to end with nobody watching. Everything below was measured over the full history, not a sample.
The layering is deliberate: Spark does structural cleaning only — types, dedup, null-key filtering. All business logic (KPI definitions, lookups) lives in dbt where it is SQL-reviewable, tested, and documented.
02 Results
Loaded into Snowflake through an external stage with exact row-count reconciliation — the load either matches the source count or it fails. The Great Expectations checkpoint (row-count bands, distribution checks, freshness across four marts) has its exit-code contract verified in both directions: it passes clean data and fails dirty data, which is the only way to know a quality gate is wired up at all.
03 Architecture
Orchestrated by a monthly Airflow DAG; the AWS and Snowflake estate is imported into Terraform and plan-clean.
Ingestion that a scheduler can trust
An idempotent downloader with atomic writes (.tmp + rename) and retry with backoff. A re-running scheduler must never let Spark read a partially written CSV — so a partial file is never visible under its final name.
Spark cleaning, explicitly typed
CSVs are read all-string and cast explicitly. Schema inference double-scans 26 GB and can guess differently across 120 monthly files; explicit casts fail loudly and survive year-over-year schema drift. Natural-key dedup, flag normalization, null-key filtering — structural work only.
Warehouse modeling in dbt
raw → staging → marts in Snowflake, loaded from an external stage with exact row-count reconciliation. KPI definitions live here as reviewable, tested SQL rather than buried in a Spark job: on-time performance, delay-cause breakdown, route cancellation rate, airport congestion.
A release gate, verified both ways
A 16-expectation Great Expectations checkpoint across four marts — row-count bands, distribution checks, freshness — with the exit-code contract tested in both directions so a failing gate actually fails the run.
Orchestration and delivery
A monthly Airflow DAG keeps the warehouse current — its first scheduled run ingested a new month fully autonomously. CI runs dbt and Great Expectations on every PR, and 761,909 KPI rows are pushed to Power BI through the REST API with device-code auth.
04 Decisions worth defending
Read all-string, cast explicitly
Inference costs a second full scan of 26 GB and can disagree between monthly files. Explicit casts fail loudly at the boundary and survive schema drift across a decade of BTS releases.
Spark cleans, dbt decides
Types, dedup, and null keys in Spark. Every business definition in dbt, where it is SQL, reviewable, tested, and documented — instead of a transformation nobody can audit.
Partitioned by year/month
Matches BTS's own monthly grain, which is what makes incremental warehouse loads and predicate pushdown possible rather than reprocessing the decade.
HHMM times left as strings
Turning wall-clock times into timestamps needs timezone context. Deferred to dbt rather than done half-correctly in Spark and silently wrong at DST boundaries.
local[*] on purpose
The dataset exceeds laptop RAM but not laptop disk. The identical job runs on Databricks or EMR by swapping the master — so the cluster is a deployment choice, not a prerequisite.
Booleans typed at the edge
BTS ships "1.00"/"0.00" flags; empty flags become NULL (unknown), not false — because an unknown cancellation silently read as "not cancelled" corrupts every downstream rate.
05 Stack
The full source is on GitHub
Every number on this page is reproducible from the repository.