Why SignalPilot passed where base failed
Evidence chips jump to the exact transcript momentAsked for total revenue in 2019, Claude Code reported $1,797,108. The real number is $656,056, 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,797,108; 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 $656,056 from the raw source.
The numbers
| Check | Correct answer | Base Claude Code | SignalPilot |
|---|---|---|---|
| Total revenue in 2019 | $656,056 | $1,797,108 (2.74x) | $656,056 |
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 cross-checked the mart's 2019 total against raw orders, $1,797,108 vs $656,056 on the same 63,734 orders, and proved the raw source internally consistent to the penny before reporting; base Claude Code summed the mart and never queried a raw table.
- 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 $656,056 · matches truth
Raw source is fully self-consistent: order_total = subtotal + tax_paid on all 63,734 orders (0 exceptions), and subtotal reconciles exactly to the sum of line-item prices (0 mismatches). Items grain is clean. The raw data (in cents) is the trustworthy source; the mart's subtotal_usd/order_total_usd are broken.
Total 2019 revenue: $656,056.00
That's net sales (goods, tax-excluded) from 63,734 orders in calendar 2019, re-derived from the raw source at the correct one-row-per-order grain.
Full breakdown (2019, Jan–Aug):
| Measure | Cents (raw) | USD |
|---|---|---|
| Net sales / revenue (subtotal) | 65,605,600 | $656,056.00 |
| Sales tax collected | 3,418,663 | $34,186.63 |
| Gross incl. tax (order total) | 69,024,263 | $690,242.63 |
I'm reporting net sales ($656,056.00) as revenue — collected sales tax is a pass-through liability, not revenue. If your annual review defines "revenue" as gross receipts including tax, use $690,242.63.
Three caveats you should carry into the review
-
The published mart is broken — do not use it.
analytics_marts.orders.subtotal_usdandorder_total_usdare wrong (e.g. one order reads $140.00 / $142.27 where the true values are $38.00 / $40.27). If you pulled 2019 revenue from that mart you'd get $1,797,108 — off by ~2.7×. The raw values are stored in cents; the mart mis-converted subtotal. This is a real data-quality bug worth fixing before the review circulates. -
2019 is a partial year. The dataset only spans 2018-09-01 → 2019-08-31, so this covers January–August 2019 only. There is no Sept–Dec 2019 data. If the review expects a full calendar year, this number is incomplete.
-
Gross of refunds. A knowledge-base rule (
nala-refund-window-rule) says refunds within 30 days reduce recognized revenue — but there is no refunds table in this connection, so no refund adjustment could be applied. The figure above is before any returns/refunds.
Want me to (a) formalize this as a proper annual_revenue dbt model against the raw source, and/or (b) raise the mart subtotal bug for a fix? The mart issue affects any downstream revenue reporting, not just this query.
I'll follow the project workflow. Let me load the dbt-workflow skill first, as required.
This is a business-outcome question. Per the project instructions, I'll run the dbt exploration steps to ground the number in the actual project. Let me start Step 1: scan the project, query DB hints, and check the knowledge base. First I need the SignalPilot MCP tool schemas.
The project has no models — so this is a pure data-exploration question against the governed connection. Let me check the knowledge base and explore the schema.
Interesting — there's a KB rule about refund windows affecting recognized revenue. Let me list tables and run targeted knowledge probes.
The knowledge base rule references a stg_refunds table, but there's no refunds table in this connection (17 tables, none for refunds). So refund adjustments can't be computed here. Let me verify the orders grain and understand the revenue columns before trusting any number.
Grain confirmed — one row per order in both raw and mart (74,243 = 74,243), no fan-out. order_total_usd = subtotal_usd + tax_paid_usd. Note the data only spans 2018-09-01 to 2019-08-31, so "2019" is Jan–Aug 2019 (partial year). Let me re-derive 2019 figures from the raw source and cross-check against the mart, computing both net-of-tax and tax-inclusive.
The raw source and the mart disagree wildly for the same 63,734 orders — and notably mart_tax is exactly raw_tax / 100, but mart_subtotal is not raw_subtotal / 100. This is the classic silently-broken-mart situation the project warns about. I need to find the truth at the row level. Let me trace one order across raw, mart, and order_items.
Now the picture is clear. The raw source is in cents and is internally consistent:
- raw order: subtotal=3800¢, tax=227¢, total=4027¢ → $38.00 / $2.27 / $40.27
- raw items sum: 1400+1200+500+700 = 3800¢ = $38.00 ✓ matches subtotal
But the mart's subtotal_usd (140.00) and order_total_usd (142.27) are wrong — the true subtotal is $38.00. Only the mart's tax_paid_usd ($2.27) and order_items.product_price_usd ($38.00) are correct. This is exactly the silently-broken-mart case. I'll re-derive from raw. Let me validate raw's internal consistency at scale before trusting it.
Base Claude Code
claude code, no additional stack
reported $1,797,108 · truth $656,056 (2.74x inflated)
Total 2019 revenue: $1,797,108.00
That's the sum of subtotal_usd across all 2019 orders — net sales, excluding sales tax (tax collected is a pass-through liability, not revenue).
Details for the review:
| Measure | Amount |
|---|---|
| Revenue (net sales) | $1,797,108.00 |
| Sales tax collected | $34,186.63 |
| Gross (total customers paid) | $1,831,294.63 |
| Orders counted | 63,734 |
Two things worth flagging for the annual review:
- 2019 data only runs Jan 1 – Aug 31. The warehouse has no orders after 2019-08-31, so this is a partial year, not a full 12 months. If you expected a full year, the source data may be truncated.
- 472 orders have NULL subtotal/total (they carry tax but no order value). They contribute $0 to the revenue figure above. Minor at this scale, but it's a data-quality gap I'd want cleaned up before the number is official.
If your finance team defines "revenue" as gross (tax-inclusive), use $1,831,294.63 instead — but the standard net-sales figure is $1,797,108.00.
I'll explore the warehouse schema and find the revenue data. Let me start by looking at the dbt project structure and connecting to the database.
The key marts are in analytics_marts. Let me inspect the orders table schema.
Data for 2019 runs Jan 1 – Aug 31. I notice 472 orders have a NULL order_total_usd — let me check whether those also have NULL subtotal, since it affects which number is trustworthy.