My Journal: Dagster + dlt + DuckDB + Claude — Part 4

In Part 3, I promised to start a new series on making the F1 data warehouse conversational. I said I was planning to use a custom LangChain + dbt + Streamlit stack. Well, that plan changed. I ended up using Claude’s Cowork mode instead (I discovered live artifacts and i’m like a child in playing with their new toy), which let me talk directly to the data platform I had built and produce a fully interactive monitoring dashboard without writing a frontend framework from scratch. This post is about what I found when I finally looked at my own data.

Context

Just to recap where we are: over the first three parts of this series, I built a data pipeline for F1 data using Dagster, dlt, PostgreSQL, dbt, and DuckDB. The architecture ingests data from the Ergast/Jolpi F1 API, stages it in PostgreSQL and models it into a silver layer in DuckDB using the Kimball approach.

The silver layer has these tables:

• fact_race_results — one row per driver per race
• dim_driver, dim_constructor, dim_circuit, dim_races, dim_status

Up to Part 3, I was focused on the pipeline. I had never really sat down and asked: is my data any good? This part is that reckoning.

The AI Layer I Actually Used

My original plan was LangChain + Streamlit. A lot has changed since I wrote Part 3.

When I wrote the first three parts of this series, AI tools were largely conversational; you described a problem, got a suggestion, copied it into your editor, and took it from there. That is still the mode I was in when I wrote Part 1. Since then, the shift has not just been about better code generation. The bigger change is that AI moved from chat to agents. Models can now take actions: read files, run queries, call APIs, produce artifacts. People are no longer just talking about their data, they are having agents do actual work on it, like building dashboards or generating reports, without a separate frontend project in the way.

That context problem is exactly what the MCP (Model Context Protocol) server pattern solves. Instead of describing my schema to an AI and hoping it generates correct SQL, I connected Claude directly to my DuckDB silver layer via an F1 MCP server. Claude could inspect the actual tables, run queries, see the results, and iterate all within a single conversation.

The reason I chose this approach over Streamlit was the live artifacts feature in Claude’s Cowork mode. Rather than building a dashboard in a separate app and deploying it, I could produce interactive HTML artifacts that query the MCP server live each time they are opened. The loop was: ask a question, get SQL results, turn those results into a dashboard, reopen it next week and get fresh data. No infrastructure to manage on the frontend side.

I was skeptical about how well this would work in practice. Code assistants have let me down before on data engineering tooling (see Lesson 1 in Part 1). But this felt different, the model was not guessing at my schema, it was reading it directly. What I did not expect was how much the process would reveal about my own data.

The MCP Server and the Semantic Layer

Before getting to what I found in the data, it is worth explaining what the MCP server actually does, because it is not just a query endpoint.

The server (server.py) connects to Claude via stdin/stdout using the Model Context Protocol. When Claude opens the connection it sees a list of tools, reads their descriptions and decides which ones to call based on what you ask. The key design decision was splitting the tools into three groups.

Semantic layer tools — these exist so that Claude understands the data before writing any SQL:

• get_schema — returns the full column definitions and join keys for any table. Every column has a business description, not just a data type.
• get_metric_definition — returns the exact SQL formula for metrics like win_rate, dnf_rate, podium_rate. This avoids Claude inventing its own definition of "win rate" each time.
• get_query_pattern — returns worked SQL templates for common patterns: window functions, rolling averages, head-to-head comparisons, streaks. Claude adapts these rather than writing from scratch.
• get_glossary — F1 domain terms with their meaning in the data. For example: what DNF means, that the points system changed in 2010, that comparing raw points across eras is misleading.

All of this lives in YAML files in the semantic/ folder: catalog.yaml for table and metric definitions, glossary.yaml for domain terms, query_patterns.yaml for SQL templates. The semantic layer is not generated — it is hand-written and it is the most important part of the whole setup.

The quality of the AI’s SQL is directly proportional to the quality of the semantic layer. If the catalog is vague, the queries will be wrong. If the metric definitions are precise, the answers will be precise.

Raw SQL tool — execute_sql runs any read-only SELECT against the DuckDB silver layer. Claude uses this when the pre-built tools do not cover the question being asked.

Data product tools — pre-built analytical tools for the most common questions: get_driver_career, get_season_standings, compare_drivers, get_greatest_races, get_constructor_history, get_circuit_stats. These are Python functions that run curated SQL, format the output, and return structured results. They are faster and more consistent than letting Claude write the same query repeatedly.

The combination means Claude almost never has to guess. It reads the schema, checks the metric definition if needed, picks up a query pattern if one fits, and falls back to raw SQL only for novel questions.

Discovery 1: My Data Coverage Was… Interesting

The first thing I asked was a simple overview: how many seasons, races, drivers, constructors are in the database?

The answer: 14 seasons, spanning 1950 to 2026. F1 has run 77 seasons since 1950. That means my pipeline had loaded 18% of all seasons.

I was not surprised by this, the backfill is deliberate and ongoing. What I was curious about was which seasons. Turns out:

• 1950–1956: present (7 seasons, early era)
• 1957–2019: completely missing (63 seasons)
• 2020–2026: present (modern era, 2026 in progress)

The gap from 1957 to 2019 is my main backfill target. This is expected since I prioritised the modern era for the initial load. But seeing it laid out clearly made it feel more real and more urgent.

What I did not expect was what came next when I drilled into the round-level coverage.

My first query on fact_race_results showed only 4–5 rounds loaded per modern season specifically rounds 1, 6, 11, 16, and 21. I spent a while looking at this, assuming the pipeline had sampled every fifth race during the backfill. I wrote this in my notes: "fact table appears to be sampled at ~20% per season, rounds 1, 6, 11, 16, 21."

My first instinct was to blame the query. The MCP execute_sql tool has a default row limit of 100, and a full season with 22 rounds × 20 drivers = 440 rows. It was plausible that the result set was being silently truncated. So I re-ran the query correctly — grouping by season and round directly against fact_race_results with no JOIN and no row cap:

SELECT season, round, COUNT(*) as entries
FROM silver.fact_race_results
GROUP BY season, round
ORDER BY season, round

The same 4–5 rounds came back. This time I could not blame the query. The data really was sparse.

The actual cause was in the pipeline. The dlt resource was only processing the first page of the API response. The F1 API paginates race results, and the pagination processing was not implemented correctly — it fetched page one and stopped. Rounds 1, 6, 11, 16, 21 happened to fall on the first page boundary for the offset and limit values I had configured.

This is exactly the kind of bug that a monitoring dashboard surfaces and a unit test does not. The pipeline ran successfully every time — no errors, no failures, clean Dagster logs. It was just quietly loading partial data on each run.

A pipeline that runs without errors is not the same as a pipeline that loads complete data. Silent pagination bugs are particularly hard to catch because the success signal is genuine — just incomplete.

Discovery 2: SQL Can Lie to You Politely

This is the lesson I am most embarrassed to write about, because it is something I should have caught.

The first pass at coverage analysis used INNER JOINs throughout:

SELECT f.season, COUNT(DISTINCT f.driver_key) as drivers
FROM silver.fact_race_results f
JOIN silver.dim_driver d ON f.driver_key = d.dim_driver_key
GROUP BY f.season
ORDER BY f.season

This returned results only for seasons where both the fact rows and the dimension records existed and matched. The early era seasons (1950–1956) vanished completely, not because the data was missing, but because the INNER JOIN silently dropped any rows it could not match across both tables.

The fix was simple, use LEFT JOINs from dim_races outward, so unmatched rows show up as nulls rather than disappearing:

SELECT r.season,
  COUNT(DISTINCT r.round) AS dim_rounds,
  COUNT(DISTINCT CASE WHEN f.race_key IS NOT NULL THEN r.round END) AS fact_rounds
FROM silver.dim_races r
LEFT JOIN silver.fact_race_results f ON f.race_key = r.dim_race_key
GROUP BY r.season
ORDER BY r.season

With this query, the early era seasons came back — with fact data intact. The dimension key joins were clean for all 14 seasons. The data was never broken. I just was not looking for it properly.

A query that returns no rows is not the same as data that does not exist. Always LEFT JOIN when you are auditing coverage, not when you are querying for analysis.

Discovery 3: How I Was Measuring DNFs Was Wrong

Once the coverage checks were sorted, I moved on to building a results dashboard. One of the summary cards showed DNF counts per season. For all seasons, it showed zero. I knew that was wrong — there are always retirements in F1.

The assumption in my code was:

dnfs = rows.filter(r => r.position === null).length

I assumed DNF drivers would have a null position. They do not. Every driver gets a position value in the database, including those who retired. The actual retirement information is in status_category:

SELECT DISTINCT status_category, status_description
FROM silver.dim_status
ORDER BY status_category

The categories are: Completed, Lapped, Mechanical Failure, Accident, Did Not Start, Disqualified, Other. A "finished" result is only Completed or Lapped. Everything else is a non-finish.

So the correct logic was:

const FINISHED = new Set(['Completed', 'Lapped']);
const dnfs = rows.filter(r => !FINISHED.has(r.status_category)).length;

For 2024 this gave 54 non-finishes (49 retirements, 2 disqualifications, 3 DNS). That is much more believable.

If a field is described as “finishing position,” test the assumption that non-finishers have a null value. They may not.

Discovery 4: Sprint Races Do Not Exist in My Database

The final thing I noticed was that championship points in my dashboard were consistently lower than the official F1 website. For 2026 after 5 rounds, my dashboard showed Kimi Antonelli on 118 points. The official site showed 131. A 13-point gap.

The reason: sprint races. Since 2021, some rounds include a sprint race with its own points (8–7–6–5–4–3–2–1 for P1–P8). My pipeline only ingests main race results. Sprint results were never loaded for any season.

This affects 6 seasons in my database:

| Season | Sprints |
|--------|---------|
| 2021   | 3       |
| 2022   | 3       |
| 2023   | 6       |
| 2024   | 6       |
| 2025   | 6       |
| 2026   | ongoing |

There are no sprint entries in dim_races at all. This is a schema-level gap, not just missing rows — the data model needs to be extended to support sprint results as a separate entity.

An incomplete pipeline is not just about missing rows. It can mean entire event types were never modelled.

What I Built

Using Claude’s Cowork mode throughout this investigation, I ended up with two live dashboards:

Coverage Dashboard — an overview of what is actually in the database: season timeline, round-level heatmap per season, table coverage by layer, and the sprint gap prominently flagged.

Coverage Dashboard on Live Artifacts

Results Dashboard — a season explorer with four tabs:

• Races: full race-by-race results with round/driver filters and correct DNF/DNS/DSQ badges
• Drivers: an animated bar chart race showing the championship standings evolving round by round (the one I am most proud of)
• Constructors: cumulative points chart per team with standings table
• Schedule: full race calendar with completion status

Results Dashboard on Live artifacts

The animated championship viz was built entirely in vanilla JavaScript — no charting library — using CSS transitions on absolutely-positioned div elements. Bars slide up and down as the standings change, with play/pause/speed controls and a round scrubber.

Conclusion

I came into this expecting to spend most of the time on the AI interface. I ended up spending most of the time rediscovering gaps in my own data. That is probably the right order of events for any data project since you cannot trust a dashboard you have not stress-tested and stress-testing means going back to the queries.

The three key things I will carry forward:

1. LEFT JOIN for auditing, INNER JOIN for analysis. These are different queries with different purposes. Mixing them up hides data you actually have.
2. Always check tool and query limits before concluding data is missing. A 100-row default cut off a 440-row result set and I misread it as a data gap for most of a session.
3. Sprint races need their own modelling. The Ergast API has sprint results at /ergast/f1/{year}/sprint.json. I need to add this as a new resource in the dlt source and extend the dbt models to include it.

The backfill for 1957–2019 and the sprint race modelling are the two main things left before the data layer is reliable enough for deeper analysis.

What If You Want to Self-Host Everything?

Everything I have described so far uses Claude as the AI layer. Claude is not open source, and Cowork mode is a hosted product. For teams or individuals who want to run the full stack themselves — model, interface, and data — there is an open source path worth thinking about.

The good news is that the most important parts are already portable. The MCP protocol is open — the F1 MCP server will work with any MCP-compatible client, not just Claude. The semantic layer (the YAML catalog, glossary, and query patterns) is plain files. The DuckDB silver layer is already self-hosted. The only things that need replacing are the model and the agent interface.

For the model, Ollama is the obvious starting point — it handles local model serving with a single command and supports models that perform well on SQL generation.

For the interface, this is where I am still doing research. All this will form part of what I will be implementing next. Hoping to discover interesting open source tools.

The dashboards were built using Claude’s Cowork mode with an F1 MCP server connected to the DuckDB silver layer. Code for the data pipeline and MCP server can be found here.