Case #23 · Row count explosions

Why SignalPilot passed where base failed

Evidence chips jump to the exact transcript moment

The championship list should show each of the 34 champion drivers once with their total titles. Claude Code returned 75 rows, listing repeat champions once per title instead. SignalPilot returned exactly 34.

Real-world impact

The report reads as if there were 75 different champions. Multi-title drivers appear over and over, once per season, instead of once with their career total, so the ranking the task asked for does not exist in the output.

Where base Claude Code went wrong

Found each season's champion correctly but never rolled the results up: one row per champion-season pair, 75 rows.
The number is no coincidence: the 34 correct rows add up to exactly 75 championships.

Jump to the exact moment

What SignalPilot did differently

Anchored to the project's nearly identical constructors table and built the model the same way.
Stated the grain in its plan: one row per driver who has won at least one championship, 34 rows expected.

Jump to the exact moment

The numbers

Check	Correct answer	Base Claude Code	SignalPilot
Champion drivers listed	34, once each	75 rows (repeats)	34
Other two graded tables	·	pass	pass

Verification workflow

Where the eight-step workflow caught it.

Read the workflow docs

01
Map the project
Scan every model: dependencies, required columns, macros, hazards.
The scan listed the project's finished constructor championships model as an existing complete sibling, surfacing the ready-made pattern; base Claude Code planned straight from the YML description.
02
Load supporting skills
SQL, database, and domain rules loaded before any writing.
The writing skill requires reading a finished sibling's SQL first and replicating its pattern; base Claude Code invented a season-by-season table instead.
03
Validate the project
Parse check, plus rebuild of stale time-dependent upstreams.
04
Discover project macros
Find macro-derived columns the YML contract does not list.
05
Research the data
Driving table, cardinalities, contract, sibling patterns, categorical values.
A query confirmed exactly 34 distinct champion drivers before writing; base Claude Code never counted and shipped one row per champion-season.
06
Write the technical spec
A written plan of sources, joins, filters, and grain per model.
The spec pinned the grain to one row per champion driver, 34 rows expected.
07
Write and build models
SQL from the spec, with column mapping checked before each model.
The expected 34-row shape was written into the model file itself as a comment.
08
Verify and fix
Two read-only verifiers cross-check structure and values until every check passes.
Both verifiers confirmed 34 rows and a clean one-row-per-driver grain against the spec; base Claude Code ran no verification on its 75-row table.

SignalPilot

claude code + signalpilot governed stack

pass

65 turns116 tool calls$$1.37answer pinned · full chat below

final answer pass

All 8 steps complete. Here's a summary of what was built:

✅ dbt Task Complete — f1002

Models Written & Built

finishes_by_constructor (211 rows)

Summarizes constructor race performance across all F1 history
Mirrors the finishes_by_driver sibling pattern exactly: drives from stg_f1_dataset__results JOIN stg_f1_dataset__constructors
Produces 33 columns: constructor_id, constructor_name, races, podiums, pole_positions, fastest_laps + positions p1–p21plus + race outcomes (disqualified, excluded, failed_to_qualify, not_classified, retired, withdrew)
fastest_laps uses rank = 1 (not the fastest_lap column, which is a lap number) — matching sibling pattern

driver_championships (34 rows)

Tracks how many championships each driver has won across all seasons
Mirrors the construtor_drivers_championships sibling: JOINs results → races → driver_standings, takes MAX(points) per driver per year, ranks with RANK(), counts seasons at rank = 1
Excludes current season (race_year != EXTRACT(YEAR FROM CURRENT_DATE())) to avoid partial-season distortion
Top champions: Lewis Hamilton & Michael Schumacher (7 each), Juan Fangio (5), Alain Prost / Max Verstappen / Sebastian Vettel (4 each)

Verification Results

Check	finishes_by_constructor	driver_championships
Table exists	✅ PASS	✅ PASS
Column completeness	✅ PASS	✅ PASS
Row count / grain	✅ PASS (211)	✅ PASS (34)
Value accuracy	✅ PASS	✅ PASS

full chat, from the start

Summarize Formula 1 constructors' race performances, rank constructors based on driver championships, and track driver championships across seasons?

Now let me create tasks for Steps 2–8 and load supporting skills in parallel.

Skills loaded. Now proceeding to Step 3 — validate the project.

Project validates with no errors. Now I need to rebuild the two pre-existing models that use CURRENT_DATE (stale data hazard).

Good — both rebuilt. Now Step 4: Discover macros.

No macros directory — no custom macros in this project. Let me read both YML files to understand the model contracts.

No macros. Step 4 done. Now Step 5 — Research. Let me read the complete model and the stub files, and run blueprints for both stubs.

Let me read the sibling model finishes_by_driver.sql for patterns and run blueprints in parallel.

Now let me run blueprints for both stubs in parallel and explore the driver_standings source.

Let me run map-columns for both stubs and also check the staging driver_standings model.

Let me try map-columns with python3 directly, and also check the races staging model.

Now let me query the data to verify championship logic before writing the spec.

Now I have everything needed. Step 5 complete — moving to Step 6: write the technical spec.

Technical spec written. Now Step 7: write and build both models.

Writing finishes_by_constructor.sql (following finishes_by_driver.sql pattern exactly):

Now writing driver_championships.sql (following construtor_drivers_championships.sql pattern):

Now build both models:

Both models built. Step 7 complete. Now Step 8: verify.

Connection healthy. Dispatching both verifiers in parallel:

Both verifiers return all checks PASS — no FAIL items to fix. Marking Step 8 complete.

Base Claude Code

claude code, no additional stack

fail

finishes_by_constructor: PASS
  driver_championships: FAIL — row count mismatch gold=34 pred=75
  construtor_drivers_championships: PASS

70 turns88 tool calls$$1.59answer pinned · full chat below

final answer fail

All 32 models materialized successfully. Here's a summary of what was done:

Root issue fixed: All staging models used ref() for raw source tables instead of source(). The sources yaml also lacked a schema: main directive and was missing position_descriptions. These were corrected across all 13 staging models.

Three models created/updated:

1. finishes_by_constructor (new) — Summarizes each constructor's race performance with counts of races, podiums, pole positions, fastest laps, position-by-position finishes (P1–P21+), and non-finish outcomes (retired, disqualified, etc.).

constructor	races	wins	podiums	pole_positions	fastest_laps	retired
Ferrari	2423	247	832	256	95	630
McLaren	1907	182	502	160	57	486
Mercedes	636	128	295	140	99	69

2. construtor_drivers_championships (updated) — Now includes a championship_rank column ranking constructors by total driver championships won.

rank	constructor	total_driver_championships
1	Ferrari	15
2	McLaren	12
3	Mercedes	9
4	Red Bull	8

3. driver_championships (new) — Tracks the championship winner per season with their points total and a cumulative_championships running count for each driver.

season	driver	points	cumulative_championships
2024	Max Verstappen	303	4
2023	Max Verstappen	575	3
2020	Lewis Hamilton	347	7

full chat, from the start

Summarize Formula 1 constructors' race performances, rank constructors based on driver championships, and track driver championships across seasons?

Let me read the existing relevant model files before making changes.

Now let me check the staging models for schemas:

The table position_descriptions exists in DuckDB but isn't a registered dbt node. It's a small lookup table — I'll create it as a seed so ref('position_descriptions') resolves correctly.

The raw tables aren't registered as dbt nodes. All staging models use ref() instead of source() for raw tables. I need to fix the sources yaml and update all staging models to use the proper source() macro.

Now I understand: the staging models incorrectly use ref() for raw source tables — they should use source(). I'll fix all staging models and also update the sources yaml.

Now update all staging models to use source() instead of ref() for raw tables:

Now update most_races.sql which uses ref('position_descriptions') via dbt_utils.get_column_values:

Now run dbt with the seed for position_descriptions first, then all models:

Seed loaded. Now run all models:

The source resolves to schema f1_dataset but the tables are in schema main. I need to add schema: main to the source definition.

All 32 models passed. Let me verify the three new/updated models have correct data: