Why SignalPilot passed where base failed
Evidence chips jump to the exact transcript momentAsked for the revenue gap between the Philadelphia and Brooklyn stores, Claude Code reported $685,090. The real number is $247,998, 2.76x 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.76x 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.76x too high while keeping one clean row per order, so grain and uniqueness checks pass.
- Summed the mart and shipped $685,090; 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 $247,998 from the raw source.
The numbers
| Check | Correct answer | Base Claude Code | SignalPilot |
|---|---|---|---|
| The revenue gap between the Philadelphia and Brooklyn stores | $247,998 | $685,090 (2.76x) | $247,998 |
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 caught tax reconciling exactly between raw and mart while subtotals did not, pinning the inflation to the revenue column, then derived both stores from line items and headers agreeing exactly; base Claude Code confirmed the mart against a sibling built from the same column.
- 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 $247,998 · matches truth
Both independent derivations now reconcile exactly at the true grain:
- Line-item revenue (sum of product prices): Philadelphia $505,644 · Brooklyn $257,646
orders.subtotal(independent source column): Philadelphia $505,644 · Brooklyn $257,646
They match, so this is the trustworthy figure.
$247,998
Philadelphia ($505,644) generated $247,998 more merchandise revenue than Brooklyn ($257,646).
Two important caveats:
- ⚠️ The
analytics_marts.storesmart is broken. Itsrevenue_usd(Philadelphia 1,387,892 / Brooklyn 702,802, implying a difference of $685,090) is inflated ~2.7×. It was built by summingsubtotal_usdacross a fanned-out order↔items join, so the per-order subtotal got multiplied by each order's line count. Tax reconciles with raw, but subtotal does not — a classic silent upstream-join bug. I did not use the mart number. You may want me to flag/fix this mart. - Revenue here = pre-tax merchandise subtotal (tax isn't store revenue). There's no refunds table in this database, so the KB refund-window rule has no data to apply.
I'll start by loading the required skill, per the project instructions.
This is a business outcome question. Per the workflow, I'll run the exploration steps. Let me start Step 1 — map the project and load the knowledge tools.
The project has no models — this is a pure business-outcome question I'll answer from the source data. Let me load the MCP tools and gather knowledge.
Now let me gather knowledge and list tables in parallel.
There's a stores mart with revenue_usd, but per project rules I must re-derive at the correct grain from source rather than trust it. Note there's a refund rule in KB, but there's no refunds table present, so it doesn't apply here. Let me look at the stores and orders grain.
The mart shows Philadelphia 1,387,892 and Brooklyn 702,802 (diff 685,090). Per project rules I must re-derive from the raw source at the correct grain rather than trust the mart. Let me verify the orders grain and re-derive revenue directly.
The raw orders table is clean (74,243 rows, all distinct ids — no fan-out). But the raw sums (Brooklyn order_total 26,795,117) are far larger than the mart's revenue_usd (702,802). That mismatch needs explanation. Let me check the marts orders table and how units are handled.
Key finding: tax reconciles between raw and mart ($10,305.17 for Brooklyn), but subtotal does not — raw orders.subtotal = $257,646 for Brooklyn, yet the mart's subtotal_usd = $702,802. The mart appears to recompute revenue from a different source (likely order_items). This is exactly the kind of mart/source divergence I can't ignore. Let me reconcile at the line-item level.
For that order, raw subtotal (1800) equals the sum of its item prices (1800) — so raw orders.subtotal reconciles with the line items, while the mart's subtotal_usd is ~2.7× inflated. The mart is the broken one. Let me re-derive true revenue from the raw line items for both stores as the source of truth.
Base Claude Code
claude code, no additional stack
reported $685,090 · truth $247,998 (2.76x inflated)
The mart's revenue_usd matches the sum of subtotal_usd (pre-tax) from orders, and order counts line up — so the numbers are trustworthy.
Philadelphia generated $685,090.00 more revenue than Brooklyn.
- Philadelphia: $1,387,892.00
- Brooklyn: $702,802.00
(This uses the project's revenue definition — pre-tax subtotal. If you meant order totals including tax, the gap is $705,094.42.)
I'll explore the dbt project and query the warehouse to answer this.
The stores mart has precomputed revenue, but several stores show 0 — let me verify it against the raw orders before trusting it.