Why SignalPilot passed where base failed
Evidence chips jump to the exact transcript momentAsked for total revenue in 2018, Claude Code reported $293,586. The real number is $107,234, 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 $293,586; 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 $107,234 from the raw source.
The numbers
| Check | Correct answer | Base Claude Code | SignalPilot |
|---|---|---|---|
| Total revenue in 2018 | $107,234 | $293,586 (2.74x) | $107,234 |
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 lined the mart up against the raw order table row by row and saw every subtotal inflated about 3x, then confirmed the true $107,234 from headers and line items; base Claude Code summed the mart in a single query.
- 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 $107,234 · matches truth
Total 2018 revenue: $107,234.00 (net of sales tax)
That's the figure I'd put in the annual review, but it comes with three caveats that matter more than the number itself:
1. "2018" is only 4 months of data. Orders in this database span 2018‑09‑01 → 2019‑08‑31. There is no Jan–Aug 2018. So this is Sept–Dec 2018 (10,509 orders), not a full calendar year. Don't compare it like-for-like against a 12‑month 2019 figure.
2. The published analytics_marts.orders mart is broken — I did not use it. Its subtotal_usd / order_total_usd columns are inflated ~3× by an upstream fan‑out join (the per‑order multiplier even varies: 3.0×, 3.2×, 3.3×…). If you'd pulled 2018 revenue straight from that mart you'd have reported roughly $310K instead of ~$107K. I re‑derived from raw.orders (stored in cents) and independently cross‑checked against the order_items line items — two independent sources agree to the penny.
3. No refund adjustment applied. The knowledge base has a rule that refunds within 30 days reduce recognized revenue — but there is no refunds table in this database (stg_refunds doesn't exist). So this figure is gross of refunds.
The full 2018 breakdown (re-derived, grain‑verified):
| Measure | 2018 (Sep–Dec) |
|---|---|
| Net revenue (excl. tax) | $107,234.00 |
| Sales tax collected | $6,428.13 |
| Gross incl. tax | $113,662.13 |
| Orders | 10,509 |
I led with net revenue ($107,234.00) since sales tax is a pass‑through liability, not revenue — but if your annual review convention is gross-of-tax, use $113,662.13.
One thing worth your call: the broken orders mart is a live dashboard risk — anything reading order_total_usd is currently overstating revenue ~3×. There are no model .sql files in this project directory (only config), so the mart is materialized from SQL I can't see here. Want me to track down and fix the fan-out, or flag it to whoever owns that model?
I'll start by loading the required skill, as mandated by the project instructions for any dbt/SQL/database question.
This is a business-outcome analytics question. Per the project instructions, I'll load the dbt-workflow skill to run the exploration steps before trusting any number.
I'll approach this properly: explore the dbt project and verify revenue at the correct grain against source data before reporting a number. Let me start by loading the knowledge base and scanning the project.
I'll check the knowledge base and scan the project in parallel.
The knowledge base flags a key nuance: refunds within 30 days reduce recognized revenue. This is a revenue-definition question, so I need to explore carefully. Let me probe the knowledge base and examine the relevant tables.
Let me describe the tables without the schema prefix and look at the local dbt project files for context.
Two important findings already: (1) the only refund reference is a knowledge rule pointing at stg_refunds, which does not exist in this database; (2) order data only spans 2018-09-01 → 2019-08-31, so "2018" contains just Sept–Dec. Let me verify the grain of the orders table against the raw source and compute the figure. Per project rules, I'll re-derive from raw.orders, not just trust the mart.
Let me inspect the raw schema columns directly.
Now let me verify grain on the raw source and check whether raw amounts match the mart (unit conversion check), then compute 2018 figures.
Grain is clean (74,243 rows = 74,243 distinct ids). But there's a real discrepancy to resolve before I trust any number:
- Raw
tax_paid(4,061,476 → $40,614.76) matches the mart's tax exactly (raw is in cents). - But raw
subtotal(76,329,000 → $763,290) does not match the mart subtotal ($2,090,694). The mart's subtotal appears to be recomputed from line items, not carried fromraw.orders.
Per project rules I must re-derive at the true grain rather than trust either figure blindly. Let me reconcile the mart subtotal against the order_items line-item source.
Now the picture is clear, and it's exactly the kind of silent mart breakage the project rules warn about. Look at order f7c56f40:
- Raw
subtotal500¢ = $5.00, and the line items (order_items) also sum to $5.00 — two independent sources agree. - But the mart
subtotal_usd= $15.00 — inflated 3×. - Raw
tax30¢ = $0.30 matches the mart tax; so the mart'sorder_total_usd= $15.30 is wrong (true total = $5.30 = raworder_total530¢).
The mart's subtotal_usd/order_total_usd are inflated by a fan-out (inconsistent multiplier across orders: 3.0×, 3.2×, 3.3×…), while raw orders.subtotal and the order_items line-item sum independently agree at $763,290. The mart is the outlier. Let me confirm independently against raw.items and compute the 2018 calendar-year figures from the trustworthy source.
raw.items carries only sku — price comes via products, so the $763,290 line-item figure is a genuinely independent derivation from raw.orders.subtotal, and they agree. Now the calendar-2018 figures from the trustworthy source:
Let me close the loop with an independent line-item reconciliation of the 2018 subtotal (via order_items, which sources price from products, not from raw.orders):
Both independent sources agree exactly: $107,234.00 net subtotal for 2018. No refunds table exists, so the knowledge-base refund rule can't be applied (figures are gross of refunds). Let me record the mart bug for future work.
Base Claude Code
claude code, no additional stack
reported $293,586 · truth $107,234 (2.74x inflated)
For the annual review, here's the 2018 figure from analytics_marts.orders:
Total 2018 revenue: $293,586.00
This is net sales — the sum of subtotal_usd across all 10,509 orders placed in 2018, excluding sales tax (tax collected is a liability, not revenue).
For reference, the full breakdown:
| Metric | Amount (USD) |
|---|---|
| Revenue (subtotal, ex-tax) | $293,586.00 |
| Sales tax collected | $6,428.13 |
| Gross total incl. tax | $300,014.13 |
| Orders | 10,509 |
If your annual review reports revenue inclusive of tax (gross), use $300,014.13 instead — but standard practice is the ex-tax figure of $293,586.00.
I'll explore the warehouse to find the orders/revenue data. Let me start by understanding the schema.