Why SignalPilot passed where base failed
Evidence chips jump to the exact transcript momentBoth reports looked identical: same 40 rows, same 25 columns. Claude Code still failed, because it built part of the report from the wrong upstream dataset, which quietly made every date in the report wrong. SignalPilot traced the data to the correct source.
Real-world impact
A daily app performance dashboard keyed to the wrong dates: install, crash, and rating trends would all be misaligned with what actually happened on those days, while looking completely plausible.
- Trusted a configuration hint pointing at the wrong of two similar-looking datasets that cover different apps.
- The shape stayed perfect and every value was keyed to the wrong dates, so the grader found no matching date column at all.
Jump to the exact moment
- Checked which dataset actually feeds the report and documented the trap in its plan.
- Built an intermediate step from the correct source; the final build references the wrong dataset zero times, and the dates lined up.
Jump to the exact moment
The numbers
| Check | Correct answer | Base Claude Code | SignalPilot |
|---|---|---|---|
| Report shape | 40 rows, 25 columns | same shape | same shape |
| Date values | from the correct dataset | every date wrong | correct |
Verification workflow
Where the eight-step workflow caught it.
- 01Map the project
Scan every model: dependencies, required columns, macros, hazards.
The project scan flagged the four local staging stubs to rewrite, wiring the report to them by ref(); base Claude Code took the config file's var() mapping as the wiring truth.
- 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.
Research noticed the config's var() mapping points at a different same-named dataset, queried both, and traced the report to the correct raw source; base Claude Code read the same config and simply followed it.
- 06Write the technical spec
A written plan of sources, joins, filters, and grain per model.
The spec wrote the source decision down before coding, ref() to the local raw-table stubs and never var(); the finished model references the wrong dataset zero times.
- 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.
Verification cross-checked every model's aggregates against the raw source tables, a check that fails instantly on wrong-source data, and caught a LEFT JOIN silently dropping disjoint store rows along the way; base Claude Code shipped without comparing output to any source.
SignalPilot
claude code + signalpilot governed stack
All 8 steps complete. Here's a summary of what was built:
✅ Google Play Overview Report — Complete
What was built (6 models)
| Model | Type | Rows | Description |
|---|---|---|---|
stats_installs_overview | Staging | 10 | Reads raw install data; renames columns (active_device_installs → active_devices_last_30_days, etc.) |
stats_crashes_overview | Staging | 10 | Reads raw crash data; renames (daily_crashes → crashes, daily_anrs → anrs) |
stats_ratings_overview | Staging | 10 | Reads raw ratings; converts 'NA' → NULL for daily_average_rating |
stats_store_performance_country | Staging | 10 | Aggregates raw store data by (source, date, country, package) |
int_google_play__store_performance | Intermediate | 10 | Aggregates country-level store data to package/date; adds rolling total_store_acquisitions + total_store_visitors |
google_play__overview_report | Mart | 40 rows | FULL OUTER JOIN across all 4 sources; 26 columns; unique on (source_relation, package_name, date_day) |
Key verified aggregates in google_play__overview_report
- SUM(device_installs) = 893 ✓ matches raw source
- SUM(crashes) = 51 ✓ matches raw source
- SUM(store_listing_acquisitions) = 987 ✓ matches raw source
- SUM(store_listing_visitors) = 2072 ✓ matches raw source
android_os_version= all NULL ✓ (no OS dimension at overview grain)- Grain uniqueness = 100% ✓ no duplicate (source_relation, package_name, date_day)
Critical fix made
The store performance source (member.android.candyshop) is completely disjoint from installs/crashes/ratings sources. Changed the store performance join from LEFT JOIN → FULL OUTER JOIN in the main CTE, matching the google_play__country_report sibling pattern. This brought all 987 store acquisitions and 2072 store visitors into the output.
Now let me read the stub SQL files and existing complete SQL files in parallel:
Now let me set up tasks and load supporting skills. Let me also read the dbt_project.yml and explore the database schema:
Skills loaded. Now proceeding to Step 3: Validate project.
There's a YAML parse error in google_play.yml — the refs: block at line 381 is mis-indented (2 spaces instead of 4, putting it outside the model block). I need to fix this to proceed.
Same issue in intermediate.yml:
Project parses cleanly. Now Step 4: Discover macros and Step 5: Research (blueprints) — let me explore the database schema and run blueprints for all stubs in parallel:
No project macros. Now running all 6 blueprints in parallel:
I need to understand the source package staging models and raw table schemas. Let me explore the source tables:
Now let me look at the source package staging models to understand what columns they expose:
Now let me read the source package staging models to understand their column transforms:
Let me look at the source package tmp models and understand the full picture:
Let me check the source package for source definitions and the map-columns tool for the stub models:
I have all the research needed. Proceeding to Step 6: Write technical spec.
Now Step 7: Write and Build models. Writing SQL for all 6 stubs in dependency order:
Writing all 6 SQL models simultaneously:
Now writing the intermediate and final models:
Now let me verify the source definitions exist before building. Let me check the full source YAML:
All source tables are defined in src_google_play.yml. Now let's build the models:
All 6 models built successfully. Now Step 8: Verify and Fix — dispatching both verifiers in parallel:
The value verifier found a FAIL: store_listing_acquisitions = 0 across all rows because member.android.candyshop (the only package in store performance) doesn't appear in installs/crashes/ratings sources, so the LEFT JOIN produces no matches. The fix: change to FULL OUTER JOIN for store_performance (matching the country_report sibling pattern). Updating spec first, then SQL:
Now rewriting google_play__overview_report.sql with FULL OUTER JOIN for store performance (following the google_play__country_report.sql sibling pattern):
Now verify the fix:
All store metrics now match: 987 acquisitions, 2072 visitors. Running final verification round:
All checks pass. Marking Step 8 complete:
Base Claude Code
claude code, no additional stack
google_play__overview_report: FAIL — no pred column matched gold column 'date_day'
All 35 models materialized successfully. Here's what was done:
Created: models/google_play__overview_report.sql
The model joins four overview-level staging sources to produce a single daily app-level performance table:
| Source | Metrics |
|---|---|
stg_google_play__stats_installs_overview | device/user installs, uninstalls, upgrades, install events |
stg_google_play__stats_crashes_overview | crashes, ANRs |
stg_google_play__stats_ratings_overview | average rating, rolling total average rating |
stg_google_play__store_performance_source | store acquisitions, visitors, conversion rate (aggregated from traffic-source grain to app level) |
The model produces 25 columns including:
- Daily metrics:
device_installs,device_uninstalls,crashes,anrs,average_rating,store_listing_acquisitions, etc. - Rolling/cumulative metrics:
total_device_installs,total_device_uninstalls,total_store_acquisitions,total_store_visitors - Derived metrics:
net_device_installs,rolling_store_conversion_rate,rolling_total_average_rating
Also fixed: Two pre-existing YAML syntax errors in google_play.yml and intermediate/intermediate.yml where orphaned refs: blocks were indented at the wrong level.
The google_play__overview_report table is now materialized in the DuckDB database with 40 rows across multiple apps and dates.
Let me read the existing model files to understand what's already in place.
Now I can see the google_play__overview_report model is defined in the YAML but the SQL file doesn't exist yet. Let me check what's in the models directory and look at the staging models for the overview data.
Now I have everything I need. The google_play__overview_report is defined in google_play.yml but its SQL file is missing. I'll create it following the same pattern as the other report models, joining the overview staging sources (installs, crashes, ratings) and aggregating store performance to app level.
There's a YAML syntax error — the trailing refs: block at line 381 is at wrong indentation (top-level under models: list without a - indicator). Let me fix it:
Same YAML issue in the intermediate model definitions file. Let me read and fix it:
All 35 models passed. Let me verify the overview report table has data: