Why SignalPilot passed where base failed
Evidence chips jump to the exact transcript momentClaude Code's sales table counted many sales 2 or 3 times, inflating 9,994 transactions into 25,667 rows. SignalPilot's verification caught the same trap in its own first draft and rebuilt to the correct 9,994.
Real-world impact
Every metric added up from that table, revenue, quantity, profit, would be inflated by up to 2.5x, because each sale could be counted up to three times.
- Joined sales to products on product id alone; the lookup holds up to 3 rows per product id (one per segment), so 9,994 transactions became 25,667.
- Invented its own manager id numbering (101 to 104) instead of deriving ids the way the answer does (1001 to 1004).
Jump to the exact moment
- Its own first draft hit the same fan-out; the verification loop measured 25,667 rows against the 9,994-row source and failed the model.
- The rebuild joined on the full combination of product id, segment, and name, so each sale matched exactly one product row, and research-derived manager ids matched the answer's 1001 to 1004.
Jump to the exact moment
The numbers
| Check | Correct answer | Base Claude Code | SignalPilot |
|---|---|---|---|
| Sales transactions | 9,994 | 25,667 (each sale counted up to 3x) | 9,994 |
| Manager ids | 1001 to 1004 | 101 to 104 | 1001 to 1004 |
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.
Research on the pre-built manager table surfaced ids 1001 to 1004 and their region-plus-900 derivation; base Claude Code never looked and invented its own 101 to 104 numbering.
- 06Write the technical spec
A written plan of sources, joins, filters, and grain per model.
The spec pinned the sales table at 9,994 rows, giving the verifier a hard number to fail the inflated build against; base Claude Code had no expected row count anywhere.
- 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 fan-out check measured 25,667 rows against the 9,994-row source, failed the model, and forced a rebuild joining on the full product id, segment, and name key; base Claude Code shipped its own 25,667-row table with no check at all.
SignalPilot
claude code + signalpilot governed stack
All 8 steps complete. Here's a summary of what was built:
✅ Task Complete
Two dbt models were written, built, and verified for the superstore001 project:
dim_regional_managers (4 rows)
Links each sales manager to their region via an INNER JOIN of stg_sales_managers and dim_regions. Surrogate key formula: region_id + 900 (produces 1001–1004).
fct_sales (9,994 rows)
The central fact table associating every sales transaction line item with all its dimensional context:
| Dimension | Join Key |
|---|---|
| Regional Geography | dim_geo on city + state + postal_code |
| Products | dim_products on product_id + segment + product_name |
| Customers | dim_customers on customer_id |
| Shipping Mode | dim_shipping on ship_mode |
| Order Date | dim_calendar on parsed order_date |
| Ship Date | dim_calendar on parsed ship_date |
Combining fct_sales with dim_regional_managers via dim_geo.region_id = dim_regional_managers.region_id gives the complete dataset: every sales transaction enriched with its regional manager, product details, customer info, shipping mode, and geography.
Key fix discovered during verification: dim_products stores one row per (product_id, segment, product_name) combination. Joining only on product_id caused a 2.57× fan-out (25,667 rows). The fix was to join on all three columns, restoring the 1:1 match at 9,994 rows.
Now loading all three required supporting skills in parallel:
No macros found. Validation passes cleanly. Now running blueprints for both stub models in parallel:
Good blueprints received. Now I need to explore the source data to understand the join keys and surrogate key format before writing the spec. Let me run targeted queries:
Now I understand the ID patterns. Let me query more to understand the full join structure:
Let me check the dim_calendar columns, stg_orders SQL, and orders table row IDs:
Let me verify the fct_sales.id generation pattern:
The id pattern is ROW_NUMBER() OVER (ORDER BY row_id) + 100. Let me now read the remaining staging models:
Now let me read the YML files and look at the model directory structure:
All joins are verified. I have everything needed. Now moving to Step 6 to write the technical spec:
Technical spec written. Now moving to Step 7 — writing the SQL models:
First, let me run map-columns for both models to confirm column mappings:
Now I have the full column map. Let me find the stub files and write the SQL:
Now writing both SQL files:
Now building both models:
Both models built successfully. Now Step 8 — dispatching both verifiers in parallel:
The value verifier found a FAIL on fct_sales — 25,667 rows instead of 9,994 due to fan-out from dim_products having multiple rows per product_id (one per segment). Before modifying anything, let me investigate the correct fix:
Joining on product_id + segment gives 10,277 rows — also wrong. Let me check fct_returns.sql to see how it handles the dim_products join:
product_id + segment + product_name is unique in dim_products — joining on all three gives exactly 9,994 rows. The fix is to update fct_sales.sql to join on these three columns, not modify the pre-existing dim_products.sql.
Updating the technical spec first:
Now fixing fct_sales.sql with the correct dim_products join:
Re-dispatching both verifiers to confirm the fix:
Both verifiers return all checks PASS. Marking Step 8 complete:
Base Claude Code
claude code, no additional stack
dim_regional_managers: FAIL — no pred column matched gold column 'id' fct_sales: FAIL — row count mismatch gold=9994 pred=25667
Two new models were created and all 12 models materialized successfully:
dim_regional_managers (models/marts/core/dim_regional_managers.sql)
- Joins
stg_sales_managerswithdim_regionsto produce a clean dimension:id,manager_name,region_id.
fct_sales (models/marts/core/fct_sales.sql)
- The central fact table joining every dimension to each sales transaction row from
stg_orders:
| Column | Source |
|---|---|
id | surrogate key |
order_id | stg_orders |
order_date_id / ship_date_id | dim_calendar |
sales, profit, quantity, discount | stg_orders (measures) |
dim_products_id | dim_products (via product_id) |
dim_customers_id | dim_customers (via customer_id) |
dim_shipping_id | dim_shipping (via ship_mode) |
dim_geo_id | dim_geo (via city/state/postal_code) |
dim_regional_managers_id | dim_regional_managers (via geo → region_id) |
The regional manager is linked to each transaction through the geography: stg_orders.region → dim_regions → dim_regional_managers.
Let me read the existing model files to understand the exact columns before writing the new fact table.
Now I have a complete picture. Two models are defined in schema.yml but their SQL files don't exist yet: dim_regional_managers and fct_sales. I'll create both.
All 12 models passed.