Why SignalPilot passed where base failed
Evidence chips jump to the exact transcript momentAsked for total revenue on the canonical dbt demo project, Claude Code reported $2,090,694. The real number is $763,290, nearly 3x lower. It even saw the true total in the order line items and dismissed it as incomplete data. SignalPilot reconciled the mart against the raw orders and reported the true figure.
Real-world impact
A topline nearly three times reality. Margins look artificially thin against it, growth rates and store contribution are all distorted, and the error survives every built-in check because the table has exactly one row per order.
- The published orders mart stores subtotals roughly 2.74x too high while keeping one clean row per order, so grain and uniqueness checks pass.
- It even computed the true $763,290 from the order line items during its own checks, then rejected that number as incomplete test data.
- Shipped the mart's $2,090,694 with confidence.
- Cross-checked the mart against raw order headers and line items; the two raw sources agree to the penny and the mart matches neither.
- Traced the gap to corrupted mart subtotals, a $4.00 order stored as $8.00.
- Reported the true $763,290 from the raw source.
The numbers
| Check | Correct answer | Base Claude Code | SignalPilot |
|---|---|---|---|
| Total revenue | $763,290 | $2,090,694 (2.74x) | $763,290 |
Verification workflow
Where the eight-step workflow caught it.
- 01Map the project
Scan every model: dependencies, required columns, macros, hazards.
The scan surfaced the raw order tables, giving the run something independent to reconcile against; base Claude Code never touched a raw table.
- 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 cross-checked the mart against raw order headers and line items, which agree to the penny at $763,290 while the mart matches neither; base Claude Code saw the same $763K line-item total and explained it away as incomplete data.
- 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 $763,290 · matches truth
Total revenue: $763,290.00 (net) — do not pull this from the dashboard
For the board deck, use $763,290.00 — net sales across all 74,243 orders (excluding sales tax). If your definition of "total" includes tax collected, gross is $803,904.76.
Why I'm not giving you the mart number
The published analytics_marts.orders table (and the stores revenue dashboard built from it) is broken — it would tell you $2,131,308.76, which is inflated ~2.7×. I caught it because it reconciles with nothing:
| Figure | True (re-derived) | Broken mart |
|---|---|---|
| Net subtotal | $763,290.00 | $2,090,694.00 |
| Tax collected | $40,614.76 | $40,614.76 ✓ |
| Gross total | $803,904.76 | $2,131,308.76 |
The true net figure is confirmed two independent ways that agree exactly:
raw.orders.subtotal(stored in cents) ÷ 100 = $763,290.00- Sum of individual line items in
order_items= $763,290.00
The mart's subtotal_usd matches neither — it's wrong at the per-order level (e.g. a $4.00 order shows $8.00; a $26.00 order shows $92.00). Grain is clean: 74,243 orders, one row each, no fan-out.
Two caveats before this goes on a slide
- Refunds not reflected. The knowledge base has a rule that refunds within 30 days reduce recognized revenue — but there is no refunds table in this database, so I can't net them out. $763,290 is gross-of-refunds. If refunds matter for the board number, we need that data sourced first.
- The broken marts should be fixed. There's no dbt
.sqlfile for these models in the project (the marts exist only as materialized tables), so I couldn't fix them in-place. Would you like me to (a) leave it as a one-off number, or (b) create/fix a dbt model that computes revenue correctly fromraw.ordersso the dashboard stops lying? I'd recommend (b) given the stores dashboard is currently reporting the inflated figure.
Bottom line for the deck: $763,290 net revenue (or $803,904.76 including tax).
I'll follow the project workflow for this. Let me load the dbt-workflow skill first, as the project instructions require.
This is a business-outcome question. Per project instructions, I'll run the dbt exploration steps rather than blindly querying. Let me start Step 1: map the project and load the needed MCP tools.
The MCP tools are now available. The scan shows no dbt models exist — so this is a business-outcome question against the raw database. Let me load the tool schemas and explore.
Now let me gather context — knowledge base, DB hints, and table listing in parallel.
Note: the knowledge base flags a refund window rule referencing stg_refunds — but no refunds table appears in the listing. And there are several candidate revenue figures (orders.order_total_usd, orders.subtotal_usd, stores.revenue_usd). I must verify grain and reconcile before trusting any of them. Let me probe the knowledge base per-entity and check grain against raw source in parallel.
Now let me verify grain and reconcile the candidate figures against the raw source.
Major discrepancy found. Both tables have the same 74,243 orders (clean grain, no fan-out), but the sums diverge massively:
- raw.orders:
order_totalsums to 80,390,476 - analytics_marts.orders:
order_total_usdsums to 2,131,308.76 (andstores.revenue_usd= 2,090,694 = the mart's subtotal, not total)
Same order population, ~37× different totals. Per CLAUDE.md this is exactly the "shared-upstream-bug" trap — I can't trust either until I find where they diverge. Let me compare per-order and check units.
The reconciliation is now revealing the bug. Key finding:
- raw.orders values are in cents.
SUM(subtotal)= 76,329,000 cents → $763,290.00 - analytics_marts.order_items
SUM(product_price_usd)= $763,290.00 — an independent line-item source that matches raw/100 exactly. - But analytics_marts.orders.subtotal_usd sums to $2,090,694 — matching neither source, inflated ~2.74×. (Its tax column is correct: raw tax 4,061,476¢ = $40,614.76.)
So the published orders mart (and the stores mart that derives from it) has a broken subtotal. Two independent sources agree the true net figure is $763,290. Let me confirm at the row level that the item sum matches raw/100 for a sample order, and verify item grain.
Confirmed at the row level. The item-level source and raw order-level source agree exactly; the published mart is inflated on a per-order basis (order 0005d1b9: true $4.00, mart says $8.00; order 00099478: true $26.00, mart says $92.00). One order happened to match by coincidence.
Let me record this broken-mart finding to memory since it's a durable, non-obvious project hazard.
Base Claude Code
claude code, no additional stack
reported $2,090,694 · truth $763,290 (2.74x inflated)
Total revenue: $2,090,694 (USD)
That's net sales across all 74,243 orders, excluding tax — the standard "revenue" figure for a board deck.
For reference, from analytics_marts.orders (the authoritative mart):
| Measure | Amount |
|---|---|
| Revenue (subtotal, ex-tax) | $2,090,694.00 |
| Sales tax collected | $40,614.76 |
| Gross incl. tax (order total) | $2,131,308.76 |
Use $2.09M as revenue — tax collected is a pass-through liability, not revenue. If the deck wants "gross bookings including tax," that's $2.13M.
Notes so you can defend the number:
- Period covered: Sep 1 2018 – Aug 31 2019 (one fiscal year).
order_total_usdfully reconciles: subtotal + tax = total on every row, zero mismatches.- 570 orders have a null total — verified they have no line items and no subtotal (empty/void orders), so they correctly contribute $0.
- I did not use the
order_itemstable for this. It sums to only $763K and is missing line items on 63K orders (subtotal ≥ line-item sum on every order, never less) — it looks incomplete, so trust theordersmart, not a roll-up from items.
I'll investigate the data model before pulling a number. Let me look at the dbt project and the warehouse.
The marts are built. Let me inspect the orders and order_items schemas to find the revenue column.
Let me pull the aggregates and sanity-check the columns.
570 orders have a null order_total_usd — let me check whether those rows still carry a subtotal (i.e. whether they're being silently dropped from the total).
These null-subtotal orders may still have line items. Let me reconcile against order_items.
Let me check the direction of the orders-vs-lineitems gap before I report — this reconciliation gap matters for how much to trust the number.