Formula 1 dbt
Analytics Engineering Project

Building to learn

The dbt Analytics Engineer was on my list of goals for 2026. Along with extensive study of the materials, I wanted to get hands on practice and build a real-world project that maps back to the exam domains.

I picked Formula 1 racing data as the subject, mostly because it's a dataset I find genuinely interesting to work with. Races, drivers, constructors, results, and lap times going back through F1 history, all supplied as CSV files on Kaggle. The project is not trying to model every corner of that dataset. It's about touching as many dbt features as possible to get hands on experinece with the tool.

Infrastructure: Neon, seeds, and sources

The database sits on Neon, a hosted PostgreSQL service, managed day to day through pgAdmin. Connecting dbt to Neon needs sslmode: require set in the profile, and the project runs in its own Python virtual environment (Python 3.12.10, dbt-core 1.9.0, dbt-postgres 1.9.0) to keep it isolated from other projects.

Loading data used a hybrid approach on purpose, since the exam expects an analytics engineer to know when to use each. Large tables, like lap times at over 500,000 rows, were loaded into a raw schema in Neon and declared as dbt sources. Small, static, lookup style tables, like circuits and constructors, went in as seeds instead. Seeds are meant for small reference data, not big transactional tables, and the project follows that distinction deliberately rather than loading everything the same way.

From staging to intermediate: joins and grain

All 13 staging models follow the same two CTE pattern, a source CTE and a renamed CTE, and all run as views. Casting and renaming happen here. Filtering and business logic are pushed downstream, with one exception: genuinely broken data can be filtered out at the staging layer, since that's a data quality problem rather than business logic.

The intermediate layer is where the real modeling work sits. Four models make up this layer.

• int_pitstops — a summary model over pit stop data.
• int_results_enriched — grain of one row per driver per race. Anchored on stg_results with left joins out to races, drivers, and constructors. Includes a grid position versus race position comparison, which folded a separate planned model into this one instead of needing its own.
• int_constructor_season_performance — constructor performance aggregated to season level.
• int_driver_season_performance — grain of one row per driver per season, built on top of int_results_enriched.

Incremental models and raw loading

int_pitstops and int_results_enriched are both materialized as incremental models, using is_incremental() with incremental_strategy='append'. The staging models feeding them, stg_pitstops and stg_results, stay as views, since they only cast and rename. The compute savings from an incremental build matter at the intermediate layer, where the heavier joins and aggregation actually live.

Growing the raw data itself is a separate job from dbt's incremental logic. A single Python script handles that side, checking the current maximum ID already sitting in Neon for four source tables (pit stops, constructor results, races, and results) and inserting only the rows past that point. Only once new rows exist in the raw table does dbt's own incremental materialization come into play, to avoid reprocessing rows that were already transformed on a prior run.

-- int_results_enriched.sql, incremental logic

select *
from renamed

{% if is_incremental() %}
where result_id > (select max(result_id) from {{ this }})
{% endif %}

The mart, and a contract that actually failed once

mart_driver_career_stats is the project's one mart, one row per driver, career to date. It pulls career wins and podiums from int_driver_season_performance, exact race level dates (first race, first and last win, first and last podium) from int_results_enriched, date of birth from stg_drivers, and championship logic joined directly from stg_driver_standings, since that was judged a one-off aggregation not worth its own intermediate model.

Model governance is the exam domain this mart demonstrates most directly. All 18 columns are declared in schema.yml with a data_type, the contract is enforced with contract.enforced: true, and there's a not_null constraint on driver_id.

Tests, tags, groups, and documentation

Generic tests cover the core grain and join integrity checks. unique and not_null sit on driver_id, race_id, constructor_id, and result_id across the staging models, and relationships tests confirm stg_results joins cleanly to both stg_drivers and stg_constructors.

Singular tests, a custom generic test, and the dbt_utils package were all deliberately scoped out. A duplicate driver-per-race singular test and a non-negative points custom test were both considered and judged unnecessary against the generic coverage already in place, given the wide-not-deep goal of the project. Adding a package purely to enable one test is a legitimate thing to skip, not every gap needs a new dependency closed around it.

All 18 models carry tags for fact versus dimension and staging versus intermediate versus mart, and are assigned to one of three groups (drivers, constructors, races) through _groups.yml. Model-level descriptions are set in schema.yml, and dbt docs generate plus dbt docs serve both run cleanly, with tags and groups showing up correctly in the docs site. Column-level documentation was deliberately left out, kept at model level only, in keeping with the wide-not-deep approach.

A real limitation in the source data

Results in this dataset are recorded as at the checkered flag, the moment the race ends. When the FIA changes a result afterward, for example applying a time penalty that promotes another driver, that change is never reflected retroactively in results.csv.

A concrete example turned up during the build. Lewis Hamilton finished a race in 7th, and was later promoted to 6th after Charles Leclerc received a post-race penalty. The source data still shows the original, pre-penalty positions. That means position, and anything derived from it, like win counts in int_driver_season_performance or podium counts in mart_driver_career_stats, reflect race-day classification rather than final official classification.

There's no field in the dataset showing the corrected result, so no test can catch this. It's documented here as a limitation of the source data, not a bug in the pipeline.

Leveraging dbt state

This domain got a full hands-on pass rather than a quick mention. The workflow: build a baseline manifest, store it in a state/ directory, then run dbt build --select state:modified --state state to only rebuild what actually changed.

dbt run --select state:modified --state ./state
dbt build --select state:modified --state ./state

Testing this further confirmed a distinction that's easy to miss: description-only changes in schema.yml don't trigger state:modified by default, since that selector tracks changes to compiled output and config, not documentation metadata. Picking up description changes needs state:modified.persisted_descriptions instead.

What was deliberately left out

Snapshots were considered for driver_standings or constructor_standings, along with a computed current-driver flag. Both were scrapped. Snapshots exist to track slowly changing dimensions on a live, mutable source, and this project's standings tables are historical and load-once, with no natural row-level change for a snapshot to capture. A current-driver flag is answerable just by filtering the most recent season, with no change tracking needed. A synthetic workaround, manually editing rows to fake a change, was considered and rejected as dishonest to the exam prep goal. Better to document the gap plainly than manufacture a fake use case.

This kind of decision matters as much as the parts that got built. Choosing not to build something, once reasoned through, is different from a gap left by accident, and the project documentation treats both with the same level of detail.

Challenges overcome

Personal reflection

This project set out to be a study tool first and a portfolio piece second, and that ordering shaped every decision along the way. Checking the build plan against the official dbt Labs study guide partway through was a useful gut check. It caught that the guide's domain structure had moved from 8 domains to 7, and it surfaced a few real gaps, like macros and Jinja, source freshness, and model versioning, that hadn't been on the radar until then.

The most useful parts of the project ended up being the errors, not the clean runs. The seed schema bug, the silently ignored config key, and the contract catching a real bigint versus integer mismatch all taught more about how dbt actually behaves than the models that worked on the first try.

Next up: building out macros and Jinja with two small hands-on examples, adding an exposure and source freshness checks, and a model version to round out the governance domain alongside the contract already in place.

Questions about this project or the dbt build? Get in touch.