Why SignalPilot passed where base failed
Evidence chips jump to the exact transcript momentA month-over-month review summary should have exactly 3 rows. Claude Code returned 11,135 rows, computing the summary for every date in five years of history. SignalPilot returned exactly 3.
Real-world impact
Anyone opening the report would find thousands of stale historical rows instead of the current month's three sentiment numbers, and the month-over-month change figures inside it were computed against history the table was never built to hold.
- Computed the summary for every date on record instead of anchoring to the latest one: three sentiments times thousands of dates made 11,135 rows.
- It never counted its output; the 11,135-row mismatch only surfaced in the post-run grading.
Jump to the exact moment
- Wrote the expected output into its plan before coding: one row per sentiment at the latest date only, 3 rows total.
- Verified the built table held exactly 3 rows before calling the work done.
Jump to the exact moment
The numbers
| Check | Correct answer | Base Claude Code | SignalPilot |
|---|---|---|---|
| Month-over-month reviews | 3 rows | 11,135 rows | 3 rows |
| Listings and hosts | 17,499 rows | pass | pass |
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.
The loaded skills carry a temporal scope rule, a rolling month-over-month summary keeps only the latest date; base Claude Code had no such rule and grouped by every review date on record.
- 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.
Research on the sibling wow_agg_reviews table showed its 10,851 rows came from incremental accumulation, confirming a fresh build must hold the latest date only.
- 06Write the technical spec
A written plan of sources, joins, filters, and grain per model.
The technical spec stated the expected output in advance, one row per sentiment at the latest date only, 3 rows total; base Claude Code computed the summary for every date in five years.
- 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.
Both verifier subagents returned PASS with the table at exactly 3 rows; base Claude Code never counted its output, and the 11,135-row mismatch only surfaced in the post-run grading.
SignalPilot
claude code + signalpilot governed stack
All 8 steps complete. Here's a summary of what was built:
✅ Task Complete — airbnb001
Models Written (3 stubs → working tables)
| Model | Rows | Verified |
|---|---|---|
daily_agg_reviews | 10,005 | ✅ PASS |
dim_listings_hosts | 17,499 | ✅ PASS |
mom_agg_reviews | 3 | ✅ PASS |
What each model does
daily_agg_reviews — Aggregates fct_reviews by (REVIEW_DATE::DATE, REVIEW_SENTIMENT) with COUNT(*) as REVIEW_TOTALS. Produces 10,005 rows covering all unique date-sentiment combinations from 2009–2021. DATE_SENTIMENT_ID uses dbt_utils.surrogate_key. SUM of REVIEW_TOTALS = 409,697 — exactly matches source row count.
dim_listings_hosts — Joins dim_listings (17,499 rows) LEFT JOIN dim_hosts on HOST_ID. UPDATED_AT takes GREATEST(listings.UPDATED_AT, hosts.UPDATED_AT) per YML spec. minimum_nights is aliased lowercase per the YML contract. LISTING_HOST_ID surrogate key from (LISTING_ID, HOST_ID).
mom_agg_reviews — Rolling 30-day window aggregation anchored to the latest review date (2021-10-22). Outputs 3 rows (one per sentiment). MOM = CAST(NULL AS DOUBLE) per the first-build incremental rule — no prior state exists for period-over-period comparison. Per-sentiment totals (negative=834, neutral=2,745, positive=4,370) reconcile exactly to the 30-day source window.
Infrastructure fixes
- Removed duplicate YML entries for
dim_listings_hosts,daily_agg_reviews,mom_agg_reviewsthat were preventingdbt parsefrom succeeding - Materialized all upstream models (
src_*,dim_*,fct_reviews,monthly_agg_reviews,wow_agg_reviews) needed by the new models
Now I have the project scan. Let me create tasks for all remaining steps and proceed with the workflow.
Now loading all three supporting skills in parallel:
Skills loaded. Now Step 3 — validate the project and rebuild stale/unmaterialized models.
Duplicate YML entry for dim_listings_hosts — need to remove one. Let me check both files:
The dim_listings_hosts entry exists in both models/dim/dim.yml (full column definitions) and models/schema.yml (tests only). I need to remove the duplicate from schema.yml to fix the parse error:
More duplicates in agg directory. Let me check all YML files:
Let me check the current state of schema.yml and remove all duplicates at once:
Remove the duplicate daily_agg_reviews and mom_agg_reviews entries from schema.yml (they exist in agg/agg.yml):
Project validates. Now rebuild the unmaterialized models including monthly_agg_reviews (has current_date hazard):
The src_* tables are missing. Let me check the model structure:
The src_* staging models also need to be built. Run the full dependency chain:
dim_dates also needs to be built first:
All upstream models are built. Now Step 4 — macros — and then Step 5 — blueprints. Let me update tasks and proceed:
Step 4 — macros analysis: The project's macros (get_date_range, get_missing_dates, is_empty) are operational utilities, not column-producing macros. No macro-derived columns needed.
Now Step 5 — run blueprints for all 3 stubs in parallel:
Now I need to do deeper research. Let me read existing sibling SQL files and explore key source tables in parallel:
Let me query source data to verify surrogate key formula and column names:
Now let me verify the surrogate key formula and check source column types:
Surrogate key confirmed: md5(CAST(col1 AS VARCHAR) || '-' || col2) = dbt_utils.surrogate_key. REVIEW_DATE is TIMESTAMP, 10,005 unique date-sentiment combos in fct_reviews. Now Step 6 — write the technical spec:
Tech spec written. Now Step 7 — run map-columns for all 3 stubs before writing SQL:
The stubs have no parseable refs — pass upstreams explicitly:
Map-columns confirms no missing columns. Now writing SQL for all 3 models:
Model 1: daily_agg_reviews
Now build all 3 models:
All 3 models built successfully. Now Step 8 — verify:
Database is accessible. Dispatching both verifiers in parallel:
Both verifiers return all checks PASS across all 3 models.
Base Claude Code
claude code, no additional stack
dim_listings_hosts: PASS mom_agg_reviews: FAIL — row count mismatch gold=3 pred=11135
All 12 models materialized successfully (PASS=12). Here's what was created:
New models:
| Model | File | Description |
|---|---|---|
dim_listings_hosts | models/dim/dim_listings_hosts.sql | Joins dim_listings + dim_hosts on HOST_ID, uses GREATEST(listings.UPDATED_AT, hosts.UPDATED_AT) to track the latest update, and generates a LISTING_HOST_ID surrogate key |
daily_agg_reviews | models/agg/daily_agg_reviews.sql | Aggregates review counts by REVIEW_DATE and REVIEW_SENTIMENT daily; incremental runs process only the current date |
mom_agg_reviews | models/agg/mom_agg_reviews.sql | Rolling 30-day review totals per sentiment joined against dim_dates, with a MOM column (LAG of 30 days) showing the month-over-month percentage change |
Fixes made: Removed duplicate model definitions from models/dim/dim.yml and models/agg/agg.yml that conflicted with entries already in models/schema.yml.
Let me read the existing model files to understand the patterns before creating the missing ones.
Now I have all the context needed. Let me create the three missing models.
dim_listings_hosts is defined in both schema.yml and dim.yml. I need to remove it from dim.yml.
The dim.yml only contains dim_listings_hosts (the full documentation entry). Since schema.yml already has the tests for it, I'll remove the duplicate from dim.yml.
Same issue with agg.yml. Let me clear the duplicates there too.
That would break the YAML. Let me just replace the whole file cleanly.
All 12 models passed.