Why SignalPilot passed where base failed
Evidence chips jump to the exact transcript momentAsked for the Philadelphia store's total revenue, Claude Code reported $1,387,892. The real number is $505,644, 2.74x 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.74x 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.74x too high while keeping one clean row per order, so grain and uniqueness checks pass.
- Summed the mart and shipped $1,387,892; 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 $505,644 from the raw source.
The numbers
| Check | Correct answer | Base Claude Code | SignalPilot |
|---|---|---|---|
| The Philadelphia store's total revenue | $505,644 | $1,387,892 (2.74x) | $505,644 |
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 checks plus a single-order trace caught the mart inflating a true $38.00 subtotal to $140.00, with raw headers and line items agreeing at $505,644; base Claude Code summed the mart without a single distinct-count check.
- 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 $505,644 · matches truth
Confirmed the ground truth. Order a111941b genuinely has 4 items summing to $38.00 = raw subtotal (3800¢); the mart's subtotal_usd of 140.00 was inflated. At the aggregate level, Philadelphia's subtotal from order_items ($505,644.00) exactly matches raw.orders.subtotal (50,564,400¢). The order_items table has no fan-out (108,876 rows = distinct items).
Answer
Philadelphia store revenue: $505,644.00 (net sales, pre-tax) — re-derived from source at order grain.
⚠️ Do not use the published marts for this. They report inflated figures:
analytics_marts.stores.revenue_usd= 1,387,892.00 ❌analytics_marts.orders SUM(order_total_usd)= 1,418,201.59 ❌
Both are wrong. There's a fan-out bug in the mart's subtotal calculation — it multiplies each order's subtotal (verified: one real order with a true $38.00 subtotal shows as $140.00 in the mart). The two marts agree with each other only because they share the same broken upstream, not because they're correct.
The numbers, re-derived from raw.orders for store_id = 71a8e342... (Philadelphia), all reconciling against the raw order_items line prices:
| Figure | Amount (USD) |
|---|---|
| Revenue (net sales, pre-tax) | 505,644.00 |
| Tax collected | 30,309.59 |
Gross incl. tax (order_total) | 535,953.59 |
| Orders | 48,708 |
I'm reporting $505,644.00 because that matches the mart's own definition of "revenue" (it excludes tax), just computed correctly. If you meant total dollars collected including tax, it's $535,953.59. The KB refund rule doesn't apply here — there's no refunds table in this database.
Given the mart is materially broken, want me to raise this as a dbt fix (the stores/orders mart subtotal fan-out)?
I'll start by loading the required workflow skill, as mandated by the project instructions.
This is a business outcome question. Per the project instructions, I'll run the dbt exploration workflow. Let me start with Step 1 — mapping the project — and load the SignalPilot MCP tools.
The project has no dbt models — this is a business-outcome query against the raw database. Let me load the needed tools and explore.
There's a precomputed stores.revenue_usd mart, but per the project rules I must not trust it blindly — I need to verify grain and re-derive from source. Let me run knowledge probes and identify the Philadelphia store.
The mart reports Philadelphia = 1,387,892.00 across 48,708 orders. But per project rules I must re-derive this from the raw source at the correct grain, not trust the mart. Let me verify grain and re-derive from raw.orders.
Grain confirmed: one row per order, no fan-out. Now let me re-derive Philadelphia's revenue directly from raw.orders and cross-check the components.
Now I have a real discrepancy — three different numbers. Let me investigate carefully rather than trust any of them:
stores.revenue_usd= 1,387,892.00ordersmartSUM(order_total_usd)= 1,418,201.59 (difference = tax of 30,309.59)raw.orders SUM(order_total)= 53,595,359 (doesn't reconcile with either)
Let me trace a single order end-to-end to understand the units and lineage.
Key finding: the mart is not a simple unit conversion of raw. For order a111941b: raw subtotal=3800¢ ($38.00), raw tax=227¢ ($2.27), raw total=4027¢ ($40.27) — but the mart shows subtotal_usd=140.00, order_total_usd=142.27. The tax matches (227¢→$2.27) but the mart's subtotal is inflated. And separately, raw.subtotal (2600¢) exactly equals the sum of its order_items prices ($26.00). This points to a fan-out bug in the mart's subtotal. Let me confirm the ground truth at both the single-order and aggregate level.
Base Claude Code
claude code, no additional stack
reported $1,387,892 · truth $505,644 (2.74x inflated)
$1,387,892.00
That's the Philadelphia store's revenue across all 48,708 of its orders. The stores mart's revenue_usd matches summing subtotal_usd on the underlying orders exactly — i.e., revenue is defined as the pre-tax subtotal.
For completeness: the orders also carry $30,309.59 in tax, so the total collected including tax is $1,418,201.59 — but tax collected isn't revenue, so the figure you want is $1,387,892.00.
I'll investigate the warehouse schema to find the right tables, then compute the revenue.
The marts I care about are in analytics_marts. Let me inspect the relevant tables.
The stores mart shows Philadelphia at $1,387,892.00. Let me verify this against the raw orders, since "revenue" could be defined a few ways (subtotal vs. total incl. tax).