Why SignalPilot passed where base failed
Evidence chips jump to the exact transcript momentClaude Code crushed 3,430 stock price records into 15, and the error snowballed: the firm's book value table came out with a single row where the answer values 3,185 positions. SignalPilot matched both counts exactly.
Real-world impact
A portfolio valuation that should price 3,185 positions reported the value of exactly one. Every other position's book value simply did not exist.
- Took the phrase "on a daily basis" literally and squashed all intraday price records into one row per ticker per day: 3,430 records became 15.
- The book value table built on top collapsed to exactly 1 row; it never checked either table's size.
- The project's own YML contract defines the unique key as ticker plus timestamp, directly contradicting a per-day grain.
Jump to the exact moment
- Read the YML contract and counted 3,430 distinct ticker-timestamp pairs against only 15 ticker-day pairs, so the per-timestamp grain went into the plan.
- The verification loop then caught a join defect in its own draft and rebuilt to exactly 3,430 price rows and 3,185 valued positions.
Jump to the exact moment
The numbers
| Check | Correct answer | Base Claude Code | SignalPilot |
|---|---|---|---|
| Price averages | 3,430 rows | 15 rows | 3,430 rows |
| Book value | 3,185 positions valued | 1 position valued | 3,185 positions valued |
Verification workflow
Where the eight-step workflow caught it.
- 01Map the project
Scan every model: dependencies, required columns, macros, hazards.
- 02Load supporting skills
SQL, database, and domain rules loaded before any writing.
- 03Validate the project
Parse check, plus rebuild of stale time-dependent upstreams.
- 04Discover project macros
Find macro-derived columns the YML contract does not list.
- 05Research the data
Driving table, cardinalities, contract, sibling patterns, categorical values.
Cardinality queries counted 3,430 distinct ticker-timestamp pairs against only 15 ticker-day pairs, and the YML unique key is defined as ticker plus timestamp; base Claude Code read "daily basis" literally and crushed 3,430 records into 15.
- 06Write the technical spec
A written plan of sources, joins, filters, and grain per model.
The technical spec recorded the expected 3,430 rows at one row per ticker per timestamp before any SQL; base Claude Code never stated an expected size.
- 07Write and build models
SQL from the spec, with column mapping checked before each model.
- 08Verify and fix
Two read-only verifiers cross-check structure and values until every check passes.
The verifier passed the price table at exactly 3,430 rows, then failed the book value draft for 300 missing values and forced a join fix that landed on 3,185 valued positions; base Claude Code ran dbt once and never checked either table.
SignalPilot
claude code + signalpilot governed stack
All 8 steps complete. Here's a summary of what was built:
✅ Task Complete — asset001
Models Built
bar_quotes — models/bar_quotes.sql
- Groups the
quotessource by(date, ts, ticker)to produce 3,430 rows (one per distinct ticker+timestamp) - Computes
avg_bid_pr = AVG(bid_pr),avg_ask_pr = AVG(ask_pr), andavg_mid_pr = (avg_bid_pr + avg_ask_pr) / 2.0 tt_key = concat(ticker, ts::TIMESTAMP)— follows thebar_executionssibling pattern
book_value — models/book_value.sql
- INNER JOINs
positions_shares(cumulative shares from trades) withbar_quotes(average prices from quotes) on(ticker, ts) - Produces 3,185 rows — one per position that has a matching market price
value = shares × avg_mid_pr— total position value at mid price; confirmed zero NULLs across all rows- INNER JOIN chosen over LEFT JOIN because 300 positions had no price data, which would have produced NULL
value(violating the YMLnot_nullconstraint)
Dependency chain built
stg_executions → bar_executions → positions_shares → book_value ← bar_quotes
I'll follow the full 8-step dbt workflow. Let me start by checking the knowledge base and scanning the project.
Now loading all three required skills for Step 2 in parallel:
All three skills loaded. Now Step 3 — validate the project:
Project validates cleanly. Step 4 — scan showed no macros. Let me check the project structure and proceed to Step 5:
Now running blueprints for both models in parallel:
Let me explore the key source tables to understand their structure:
Let me check if bar_executions and positions_shares are materialized as tables:
I have enough research. Let me mark Step 5 complete and move to Step 6:
Now running map-columns for both models before writing SQL:
Models not materialized yet — columns known from SQL inspection and YML. Now writing the SQL:
Now build the full dependency chain (no +, explicit model list):
All 5 models built successfully. Now Step 8 — dispatch both verifiers in parallel:
Structure verifier reported CHECK 3 FAIL on book_value — 300 NULL value rows due to LEFT JOIN misses (positions_shares has 517 distinct ts values; bar_quotes covers 448). The YML declares value as not_null. I need to fix this.
Per the dbt-write skill: when LEFT JOINing stats onto a grain table, wrap metric columns in COALESCE. However, for a financial book value, returning 0 for a position with no price is misleading — an INNER JOIN is semantically correct (only compute book value when price data is available).
Let me update the spec first, then fix the SQL:
Rebuilt successfully. Re-dispatching both verifiers to confirm all checks pass:
Both verifiers report all checks PASS. Marking complete:
Base Claude Code
claude code, no additional stack
bar_quotes: FAIL — row count mismatch gold=3430 pred=15 book_value: FAIL — row count mismatch gold=3185 pred=1
All 10 models built successfully. Here's what was created:
bar_quotes — groups the quotes source by (date, ticker) and computes:
avg_bid_pr— average bid price for the dayavg_ask_pr— average ask price for the dayavg_mid_pr— average midpoint(bid + ask) / 2for the daytt_key=concat(ticker, date)(unique per ticker/day)
book_value — joins bar_quotes with positions_shares on (ticker, date):
- Uses
arg_max(shares, ts)to take the end-of-day share count per ticker value=shares × avg_mid_pr
Both tables are now materialized in asset.duckdb under the main schema.
Let me read the key existing files before writing the new models.
Now I have everything I need. I'll create the two missing models: bar_quotes.sql (daily average bid/ask/mid per ticker) and book_value.sql (shares × mid price).