A SQL aggregation that produces a delay rate by airport is four lines. The interesting engineering isn’t in those four lines — it’s in what happens around them. How do you know the input data isn’t missing airports? How do you know the output delay rate is between 0 and 1? How do you keep seven related models from silently breaking when the staging schema changes? How do you run this in CI without a real data lake?
dbt (data build tool) addresses all of those questions. You write SELECT statements, dbt handles the materialization, test assertions, dependency graph, and documentation. The models in the flight disruption platform are a good illustration because they’re genuinely simple SQL on top of genuinely complex infrastructure, and the dbt layer is what makes the whole thing trustworthy.
What You Need Coming In
- Comfortable writing SQL:
GROUP BY,AVG,CAST,WHERE - Basic Python familiarity (dbt is configured in Python environments)
- No prior dbt experience needed
The Three-Layer Pattern
The project has three model directories that reflect a standard dbt pattern:
dbt/flight/models/
├── staging/ # views — lightweight renaming + typing over sources
│ ├── stg_flights.sql
│ └── stg_airports.sql
└── marts/ # tables — aggregated, tested, ready for the API
├── agg_airport_reliability.sql
├── agg_route_reliability.sql
├── agg_carrier_reliability.sql
├── agg_airport_hourly.sql
└── ...Staging models are views — they don’t materialize to disk. Their job is to cast columns to correct types, rename anything inconsistently named in the source, and filter out rows that would corrupt aggregates (cancelled flights shouldn’t count toward delay rates). Mart models are tables — they materialize to DuckDB and stay there for the API to query.
The Staging Layer
stg_flights.sql is where the source data gets normalized:
{% if var('use_seeds', false) %}
{% set flights_relation = ref('seed_flights') %}
{% else %}
{% set flights_relation = source('silver', 'flights') %}
{% endif %}
with src as (
select * from {{ flights_relation }}
)
select
cast(flight_date as date) as flight_date,
cast(year as integer) as year,
cast(dep_hour as integer) as dep_hour,
cast(carrier as varchar) as carrier,
cast(origin as varchar) as origin,
cast(dest as varchar) as dest,
cast(dep_del15 as integer) as dep_del15,
cast(dep_delay_minutes as double) as dep_delay_minutes,
cast(cancelled as integer) as cancelled,
-- ... other columns
from src
where coalesce(cast(cancelled as integer), 0) = 0The use_seeds toggle is a practical CI decision. In production, source('silver', 'flights') points at the real silver Parquet layer on disk. In CI — where the lake doesn’t exist — var('use_seeds') switches the same model to read from a small dbt seed (a CSV file bundled with the project). The mart models downstream don’t care which one they got. The entire dbt DAG runs in CI against the seed data without needing a full data pipeline to have run first.
The WHERE cancelled = 0 filter matters for the marts. A cancelled flight is not “not delayed” — it’s a different outcome. If cancellations flow into the delay rate denominator, the rate is artificially low for airports and routes with high cancellation rates. Filtering them at staging means every downstream model sees only operated flights.
Building the Mart Models
Each mart is a self-contained SQL file. The route reliability mart:
-- models/marts/agg_route_reliability.sql
select
origin,
dest,
count(*) as flights,
avg(cast(dep_del15 as double)) as delay_rate,
avg(dep_delay_minutes) as avg_delay_min
from {{ ref('stg_flights') }}
group by origin, dest{{ ref('stg_flights') }} is the core dbt concept. It doesn’t hardcode a table name — it references the staging model by name. dbt resolves this to the correct schema and table name at compile time, and uses it to build the dependency graph. If you rename stg_flights to stg_operated_flights, every model that references it updates automatically. dbt also guarantees that stg_flights runs before agg_route_reliability — you can’t accidentally run a mart before its upstream model.
The airport and carrier marts follow the same pattern:
-- models/marts/agg_airport_reliability.sql
select
origin,
count(*) as flights,
avg(cast(dep_del15 as double)) as overall_delay_rate
from {{ ref('stg_flights') }}
group by origin
-- models/marts/agg_airport_hourly.sql
select
origin,
dep_hour as hour,
count(*) as flights,
avg(cast(dep_del15 as double)) as delay_rate
from {{ ref('stg_flights') }}
group by origin, dep_hour
-- models/marts/agg_carrier_reliability.sql
select
carrier,
count(*) as flights,
avg(cast(dep_del15 as double)) as delay_rate
from {{ ref('stg_flights') }}
group by carrieravg(cast(dep_del15 as double)) converts the integer label (0/1) to a floating-point average, which is the fraction of flights delayed. dep_del15 = 0.23 means 23% of flights on this route departed more than 15 minutes late.
Data Tests in schema.yml
The SQL produces numbers. The YAML tests prove those numbers are valid. dbt’s schema.yml files declare tests per model and column:
# models/marts/_marts.yml
- name: agg_route_reliability
columns:
- name: origin
data_tests:
- not_null
- relationships:
to: ref('dim_airports')
field: iata
- name: dest
data_tests:
- not_null
- relationships:
to: ref('dim_airports')
field: iata
- name: delay_rate
data_tests:
- not_null
- accepted_range:
min_value: 0
max_value: 1
data_tests:
- unique_combination:
columns: [origin, dest]not_null asserts no nulls in the column. relationships asserts every origin value exists in dim_airports.iata — if the flights data contains an airport code that isn’t in the reference dimension, this test catches it. accepted_range asserts delay_rate is between 0 and 1. unique_combination asserts there’s exactly one row per (origin, dest) pair — a mart with duplicate route rows would silently double-count in the API.
accepted_range and unique_combination are custom generic tests bundled with the project rather than from a dependency. dbt’s generic test format is a Jinja macro that returns a SELECT of failing rows:
-- tests/generic/test_accepted_range.sql
{% test accepted_range(model, column_name, min_value, max_value) %}
select {{ column_name }}
from {{ model }}
where {{ column_name }} is not null
and (
{{ column_name }} < {{ min_value }}
or {{ column_name }} > {{ max_value }}
)
{% endtest %}A dbt test passes when the query returns zero rows. If any row in agg_route_reliability has a delay_rate outside [0, 1], the test returns that row and the run fails. This is intentional — a delay_rate of 1.2 would be a bug in the aggregation logic (possibly a casting issue or a source data problem), and it’s better to fail loudly than to serve nonsense to the API.
Running the tests is one command:
dbt test
# or run + test together:
dbt builddbt build runs models in dependency order and runs tests after each model materializes. If agg_route_reliability passes its tests, dbt proceeds to agg_airport_worst_routes (which depends on route data) and tests that. If anything fails, the run stops and you get a clear error pointing at the failing model and test.
How the API Reads the Marts
The FastAPI serving layer opens the DuckDB file that dbt materialized into, and queries the mart tables by name:
# serving/flight_serving/queries.py
con = duckdb.connect(duckdb_path, read_only=True)
row = con.execute(
"SELECT origin, dest, delay_rate, flights, avg_delay_min "
"FROM agg_route_reliability "
"WHERE origin = ? AND dest = ?",
[origin, dest],
).fetchone()The table names in those queries — agg_route_reliability, agg_airport_reliability, agg_carrier_reliability — are also in shared/flight_contracts/contract.py as named constants. The contract module is the single source of truth for what the marts are called; both the dbt project (which creates them) and the serving layer (which reads them) import from it. A rename in the contract immediately surfaces as a mismatch in both places.
The materialization setting in dbt_project.yml controls what dbt does with each model:
models:
flight:
staging:
+materialized: view # stg_* are views — no data on disk
marts:
+materialized: table # agg_* are tables — data on diskStaging models as views means they’re fast to “build” (just create the view definition) and always reflect the current source. Mart models as tables means the API queries pre-aggregated results rather than running aggregations on-demand. An API request for ATL’s delay rate hits a table with one row per airport, not a full-scan GROUP BY over millions of flight records.
