Why SignalPilot passed where base failed
Evidence chips jump to the exact transcript momentAsked for the average order value, Claude Code reported $28.93. The real number is $10.83, 2.67x lower. SignalPilot reconciled the mart against the raw order data and reported the true number.
Real-world impact
Every decision made against this figure starts from a number 2.67x too high, and the error survives every built-in check because the orders mart has exactly one clean row per order.
- The published orders mart stores dollar values roughly 2.67x too high while keeping one clean row per order, so grain and uniqueness checks pass.
- Summed the mart and shipped $28.93; it never reconciled the figure against the raw order data.
- Reconciled the mart against the raw order headers and line items, which agree with each other to the penny and sit far below the mart.
- Traced the inflation to corrupted subtotals in the mart and reported the true $10.83 from the raw source.
The numbers
| Check | Correct answer | Base Claude Code | SignalPilot |
|---|---|---|---|
| The average order value | $10.83 | $28.93 (2.67x) | $10.83 |
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.
Grain and cardinality checks put the mart's average at $28.93 against $10.83 in the raw source and refused to trust the mart, which also NULLs out 570 real orders; base Claude Code averaged the mart column and shipped it.
- 06Write the technical spec
A written plan of sources, joins, filters, and grain per model.
- 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.
SignalPilot
claude code + signalpilot governed stack
reported $10.83 · matches truth
AOV ≈ $10.83 per order
That's the one number for the ops review: $803,904.76 total order value ÷ 74,243 orders = $10.83 average order value (order total, tax included). Pre-tax (subtotal only) it's $10.28.
How I got it — and an important warning
I did not use the "canonical" analytics_marts.orders mart, because it's silently broken. If I had trusted its order_total_usd, I'd have reported ~$28.93 — nearly 3× too high.
What I verified:
- Grain is clean — 74,243 orders, one row per
order_idin both raw and the mart (no fan-out). - Ground truth = line items. I summed
order_items.product_price_usdper order and it matchesraw.orders.subtotal(in cents ÷100) exactly. The mart'ssubtotal_usd/order_total_usddo not match the line items (e.g. a $22.00 order shows as $80.00) and the mart also nulls out 570 orders that exist in raw. - Internal consistency —
order_total = subtotal + tax_paidholds for all 74,243 raw rows, zero exceptions.
So the figure above is re-derived from the raw source and cross-checked against line items — not just "reconciled" against a mart built on the same lineage.
Two things to flag
analytics_marts.ordersis producing wrong USD amounts (subtotal_usd,order_total_usd) and dropping 570 orders to NULL. Anything downstream — dashboards,stores.revenue_usd,customers.lifetime_spend_usd— is likely inflated too. This is a real dbt/data defect. Would you like me to investigate the mart's SQL and fix it?- This AOV is gross order value at time of order. It does not net out refunds (there's a 30-day refund-window revenue rule in the knowledge base). If the ops review wants net AOV after refunds, that's a different number — let me know and I'll compute it.
Which would you like me to do next?
I'll start by loading the required workflow skill for this project.
This is a business-outcome question involving order data, so I'll run the dbt exploration steps. Let me start Step 1 — scan the project and load the SignalPilot MCP tools.
The project has no built models yet — so this is a raw-data question. Let me load the MCP tool schemas and check the knowledge base plus DB hints.
Now let me check the knowledge base and list the tables in parallel.
There's a canonical analytics_marts.orders mart with order_total_usd, but per project rules I must verify grain against the raw source before trusting any number — and re-derive from raw, not just reconcile with the mart. Let me check grain and values on both.
Both at one row per order (74,243), no fan-out. Now let me re-derive AOV from the raw source and compare to the mart, checking units and NULLs.
Two red flags. Raw order_total looks like integer cents (avg 1082.80 → ~$10.83), while the mart's order_total_usd averages $28.93 — nearly 3× higher — and the mart has 570 NULL totals that don't exist in raw. These do not reconcile. Let me investigate the per-order relationship directly.
The mart transformation is clearly inconsistent. Look at the cents→USD conversion: tax_paid 24→0.24 (÷100, correct), but subtotal 400→8.00 (÷50), while row 6 has 700→7.00 (÷100, correct). The mart's subtotal_usd/order_total_usd are NOT a clean conversion of raw values — they're recomputed from somewhere and disagree per-row. Let me reconcile against the actual line items, the ground truth for order value.
Now it's definitive. The line items are the ground truth, and raw.orders.subtotal (in cents ÷100) matches them exactly:
| order | raw subtotal | Σ line-item prices | mart subtotal_usd |
|---|---|---|---|
| 0005d1b9 | $4.00 | $4.00 ✓ | $8.00 ✗ |
| 0006b3aa | $22.00 | $22.00 ✓ | $80.00 ✗ |
| 000af035 | $7.00 | $7.00 ✓ | $7.00 (coincidence, 1 item) |
| 000b66e6 | $5.00 | $5.00 ✓ | $15.00 ✗ |
The analytics_marts.orders mart is silently broken — its *_usd values are inflated and don't match the line items (and it drops 570 orders to NULL). Raw values (integer cents) reconcile perfectly with the line items. Let me compute AOV from the validated raw source at the correct grain.
Base Claude Code
claude code, no additional stack
reported $28.93 · truth $10.83 (2.67x inflated)
AOV ≈ $28.93 (per order, order total including tax).
How I got it, from analytics_marts.orders:
- 74,243 orders total, spanning 2018-09-01 → 2019-08-31, $2,131,308.76 total revenue.
- AOV = avg(
order_total_usd) = $28.93. (On merchandise only, excluding tax, it's $28.38.)
One caveat worth knowing for the review: 570 orders (~0.8%) have null revenue — both subtotal_usd and order_total_usd are empty (likely orders with no line items). The $28.93 is the average over the 73,673 orders that carry a value. If you instead treat those 570 as $0 orders, AOV drops to $28.71 ($2,131,308.76 ÷ 74,243). Either way it rounds to ~$29 — I'd quote $28.93 and flag the null orders as a data-quality follow-up.
I'll compute this from the warehouse. Let me first look at what's available.