Why SignalPilot passed where base failed
Evidence chips jump to the exact transcript momentAsked for the Brooklyn store's total revenue, Claude Code reported $702,802. The real number is $257,646, 2.73x 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.73x 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.73x too high while keeping one clean row per order, so grain and uniqueness checks pass.
- Summed the mart and shipped $702,802; 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 $257,646 from the raw source.
The numbers
| Check | Correct answer | Base Claude Code | SignalPilot |
|---|---|---|---|
| The Brooklyn store's total revenue | $257,646 | $702,802 (2.73x) | $257,646 |
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 compared the mart against raw order data and caught a $22.00 order stored as $80.00, with headers and line items agreeing at $257,646; base Claude Code cross-checked one mart against another mart that inherits the same bug.
- 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 $257,646 · matches truth
Two independent source paths agree exactly: the raw order-header subtotal (25,764,600¢) equals the itemized line-total rebuilt from raw.items × raw.products (25,764,600¢). That's the trustworthy number.
Answer
Brooklyn store revenue: $257,646.00
(net sales across all 25,535 Brooklyn orders — sum of order subtotals, excluding sales tax, matching the standard revenue definition the project's own mart uses)
Why not the published number
analytics_marts.stores reports Brooklyn revenue_usd = $702,802.00 — do not trust it. I re-derived from raw source and found the mart is corrupted:
- The mart's
orders.subtotal_usdis inflated. Example order0006b3aa: its 3 actual line items total $22.00 (raw.orders agrees: subtotal 2200¢ = $22.00), but the mart records $80.00. The mart converts tax correctly (88¢→$0.88) while overstating subtotal, so its revenue rolls up wrong. - Raw
raw.ordersis clean grain (74,243 rows = 74,243 distinct ids) and internally consistent for Brooklyn: subtotal+tax=total for 100% of rows; tax = 4% (Brooklyn's rate) for 99.7% (rest are rounding). - Note: only two stores (Brooklyn and Philadelphia) have any orders in the raw data — the four "$0" stores in the mart are genuinely order-less, not a broken join.
For transparency
- $257,646.00 — revenue net of tax (subtotal). ← the figure to report
- $267,951.17 — if you want the tax-inclusive gross (sum of
order_total, i.e. including the $10,305.17 sales tax collected).
The analytics_marts orders/stores models have a real subtotal bug feeding your dashboards. Want me to trace and fix the mart's subtotal transformation?
I'll start by loading the required skills for this analytics task.
This is a business analytics question. Per the project instructions, I'll load the dbt-workflow skill first to run the exploration steps.
I'll research this properly. Let me start by scanning the project and checking the knowledge base in parallel.
Let me scan the project and check the knowledge base in parallel.
The scan shows no models to build — this is a pure analytics question. I'll explore the schema and probe the knowledge base for the specific entities involved.
The KB only has an unrelated nala project entry. Let me list the tables to find where "store" and revenue live.
There's a published analytics_marts.stores mart with revenue_usd, but per project rules I must re-derive from the raw source at the correct grain rather than trust it. Let me find the Brooklyn store and inspect the grain.
The mart is a red flag: 4 of 6 stores show 0 orders / 0 revenue, which suggests it's silently broken by an upstream join. I won't trust it. Let me verify the grain of the raw orders table and re-derive from source.
Key finding: raw.orders is clean grain (74,243 rows = 74,243 distinct ids), and it genuinely only contains orders for two stores — Brooklyn and Philadelphia. So the mart's zeros for the other four stores reflect the raw data, not a broken join.
But the numbers don't line up on units: raw sum_order_total = 26,795,117 for Brooklyn, while the mart says 702,802. Let me inspect sample rows to understand the units and what "revenue" means in the mart.
Now I've found a serious data-quality problem. Look at order 0006b3aa:
- raw.orders: subtotal=2200, tax_paid=88, order_total=2288 (internally consistent — tax is exactly 4% of subtotal, Brooklyn's rate; values in cents → $22.00 + $0.88 = $22.88)
- mart: subtotal_usd=80.00, tax_paid_usd=0.88, order_total_usd=80.88
The mart converted tax correctly (88¢→$0.88) but its subtotal_usd (80.00) does not equal raw subtotal/100 ($22.00). The mart's subtotal — and therefore its revenue_usd — is corrupted. Let me confirm where 80.00 comes from and check the raw internal consistency at scale.
Confirmed. For order 0006b3aa, the actual 3 items sum to $22.00 — which matches raw.orders subtotal ($22.00), not the mart's inflated $80.00. And raw.orders is internally consistent across all 25,535 Brooklyn orders (subtotal+tax=total for 100%; tax=4% for 99.7%, rest rounding). The raw source is trustworthy; the mart's subtotal/revenue is corrupted.
Let me do one final independent cross-check: sum the actual itemized line prices for all Brooklyn orders and confirm it equals the raw order-header subtotal.
Base Claude Code
claude code, no additional stack
reported $702,802 · truth $257,646 (2.73x inflated)
$702,802.00
That's the revenue across all 25,535 Brooklyn orders. A couple of notes on how I got there:
- The
analytics_marts.storesmart reports Brooklynrevenue_usd = 702,802.00, and I confirmed it by aggregating the underlyingorderstable directly — same order count (25,535) and same total. - "Revenue" here is the pre-tax subtotal. Tax collected adds another $10,305.17, so if you instead want the gross amount customers paid (subtotal + tax), that's $713,107.17. The standard revenue figure is the $702,802.00.
I'll explore the warehouse to find the Brooklyn store's revenue. Let me start by inspecting the schema.
I have four marts tables. Let me inspect the relevant schemas.
There's a convenient stores.revenue_usd, but let me find Brooklyn and cross-check it against the actual orders rather than trusting the pre-aggregate blindly.
Now let me verify this against the raw orders directly, and check which column the mart's "revenue" corresponds to.