Why SignalPilot passed where base failed
Evidence chips jump to the exact transcript momentA daily shop performance table should cover 2,077 days. Claude Code shipped 2. Its own reasoning named the correct calendar for the job, then its model never used it. SignalPilot delivered all 2,077 days.
Real-world impact
A store owner opening their daily performance dashboard would see 2 days of history. The other 2,075 days, 99.9 percent of the record, were silently missing.
- Drove the daily report off order records: a day with no orders did not exist in the output, and only 2 days had orders.
- Its own reasoning said to use the project's 2,077-day calendar as a date spine, then the model it wrote never referenced it.
Jump to the exact moment
- Recognized a calendar-driven report: every day appears, with zeros on quiet days, and the plan names the calendar as the backbone.
- Shipped one row per day for all 2,077 days, with order activity filled in where it exists.
Jump to the exact moment
The numbers
| Check | Correct answer | Base Claude Code | SignalPilot |
|---|---|---|---|
| Daily shop report | 2,077 days | 2 days | 2,077 days |
| Product summary | · | 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 e-commerce domain skill's calendar spine rule says a daily report must show every day; base Claude Code had no such rule and aggregated only the days that had orders.
- 03Validate the project
Parse check, plus rebuild of stale time-dependent upstreams.
The scan flagged a current_date hazard in the project's calendar, which was rebuilt to end at the data's real last day, landing the report on exactly 2,077 days; base Claude Code never touched the calendar.
- 04Discover project macros
Find macro-derived columns the YML contract does not list.
- 05Research the data
Driving table, cardinalities, contract, sibling patterns, categorical values.
A min, max, and count query confirmed the calendar spans 2,077 days before any SQL was written; base Claude Code ran zero database queries.
- 06Write the technical spec
A written plan of sources, joins, filters, and grain per model.
The spec names the 2,077-day calendar as the driving table with zeros on quiet days; base Claude Code's own reasoning said to use the calendar as a date spine, then its model never referenced it.
- 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 checked the finished table's row count against the calendar's date range and sampled zero-activity days; base Claude Code printed row counts with no expected number and shipped 2 rows.
SignalPilot
claude code + signalpilot governed stack
Both models are built and verified. Here's a summary of what was delivered:
✅ shopify__products (3 rows)
One row per product. Sources: int_shopify__products_with_aggregates (product metadata + collections/tags/variants/images) LEFT JOINed with int_shopify__product__order_line_aggregates (sales metrics).
Key columns delivered:
- Product metadata:
product_id,title,vendor,status,handle,created_timestamp, etc. - Sales metrics:
total_quantity_sold,subtotal_sold,quantity_sold_net_refunds,subtotal_sold_net_refunds - Tax & discount:
product_total_tax,product_avg_tax_per_order_line,product_total_discount,product_avg_discount_per_order_line - Timestamps:
first_order_timestamp,most_recent_order_timestamp - Inventory:
count_variants,has_product_image,collections,tags
✅ shopify__daily_shop (2,077 rows)
One row per calendar day × shop. Calendar spine (shopify__calendar) CROSS JOINed with the shop record, LEFT JOINed with three intermediate models:
Key columns delivered:
- Shop metadata:
shop_id,name,domain,currency,iana_timezone,created_at - Order metrics:
count_orders,count_line_items,count_customers,order_adjusted_total,refund_subtotal,total_discounts,shipping_cost - Abandoned checkouts:
count_abandoned_checkouts,count_customers_abandoned_checkout,count_customer_emails_abandoned_checkout - Fulfillment statuses (11 counts):
count_fulfillment_delivered,count_fulfillment_in_transit,count_fulfillment_attempted_delivery, etc. - Days with no activity show
0for all count/sum metrics andNULLfor averages.
Now loading the DuckDB SQL skill and e-commerce domain skill:
Skills loaded. Now proceeding to Step 3: Validate project and fix hazards.
Validation passes. Now fix the current_date hazard in the calendar model:
Calendar fixed. Moving to Step 4 (macros) and reading the stubs.
Now let me read the YML contract and key intermediate models to understand the data flow:
Let me now read all the intermediate models needed for both target models:
Now let me run blueprints for both target models and check project config in parallel:
Let me check the project configuration and source vars, plus query the shop source table:
Let me check the calendar model and also query key data to understand grain:
Let me check the original package code for guidance on the daily shop pattern:
I have all the data I need. Let me load the knowledge-base skill and write the technical spec:
Now writing the technical spec:
Spec written. Now proceeding to Step 7 — write and build both models:
Now building both models. First need to materialize intermediate dependencies, then the mart models:
Both models built successfully. Moving to Step 8 — Verify:
Both verifiers returned 0 FAILs. The value verifier is the authority — both models are correct. Let me do a quick sanity check query to confirm output:
Both models verify cleanly. Let me mark Step 8 complete:
Base Claude Code
claude code, no additional stack
shopify__products: PASS shopify__daily_shop: FAIL — row count mismatch gold=2077 pred=2
Both tables materialized successfully (PASS=2):
shopify__products (3 rows) — product-level summary joining int_shopify__products_with_aggregates with int_shopify__product__order_line_aggregates:
- Product metadata:
product_id,title,vendor,product_type,status,collections,tags,count_variants,has_product_image - Sales:
total_quantity_sold,subtotal_sold,first_order_timestamp,most_recent_order_timestamp - Refunds:
quantity_sold_net_refunds,subtotal_sold_net_refunds - Discounts:
product_total_discount,product_avg_discount_per_order_line - Taxes:
product_total_tax,product_avg_tax_per_order_line
shopify__daily_shop (2 rows) — daily shop performance joining int_shopify__daily_orders, int_shopify__daily_abandoned_checkouts, int_shopify__daily_fulfillment, and shop metadata:
- Shop info:
shop_id,name,domain,currency,iana_timezone - Orders:
count_orders,order_adjusted_total,avg_order_value,refund_subtotal,total_discounts - Abandoned checkouts:
count_abandoned_checkouts,count_customers_abandoned_checkout - Fulfillment statuses:
count_fulfillment_delivered,count_fulfillment_in_transit,count_fulfillment_failure, etc. (11 statuses total)
Let me read the relevant intermediate models before building the two new tables.
Now let me read the existing mart models and the shopify.yml for schema details.
Let me check what model files currently exist in the models directory.
Both models built successfully. Let me quickly verify the tables have data.