Why SignalPilot passed where base failed
Evidence chips jump to the exact transcript momentAsked for revenue in May 2019, Claude Code reported $276,011. The real number is $99,025, 2.79x 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.79x 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.79x too high while keeping one clean row per order, so grain and uniqueness checks pass.
- Summed the mart and shipped $276,011; 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 $99,025 from the raw source.
The numbers
| Check | Correct answer | Base Claude Code | SignalPilot |
|---|---|---|---|
| Revenue in May 2019 | $99,025 | $276,011 (2.79x) | $99,025 |
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 reconciled the mart against raw orders per order, catching a 500-cent order stored as $15.00, and rebuilt May 2019 two independent ways until they agreed at $99,025; base Claude Code ran one sum over the mart and never reconciled it against raw.
- 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 $99,025 · matches truth
May 2019 revenue: $99,025.00 (net of sales tax, 9,482 orders).
Re-derived from the raw.orders source and independently confirmed via order-line item prices — because the analytics_marts.orders dollar columns are corrupted and would have given a badly inflated figure (~$276K). I've flagged that mart data-quality issue above and can help chase down its build logic if you want it fixed.
I'll start by loading the required workflow skill, as the project instructions mandate this before any data work.
This is a business-outcome revenue question. Per the instructions, I'll run the dbt exploration steps. Let me load the SignalPilot MCP tools and start Step 1 (project scan + DB hints + knowledge base).
The tools are now available. Let me load the exact schemas I need and start Step 1.
Now let me run Step 1 in parallel: the project scan, DB hints, and knowledge base lookup.
The scan shows no dbt models exist yet — this is a pure business-outcome question, so I'll focus on data exploration. The knowledge base flagged an important revenue rule about refunds. Let me probe the knowledge base further and explore the schema. Since this is a revenue/financial question, let me also load the financial domain skill.
Let me load the financial domain skill and explore the actual schema in parallel.
Now let me explore the actual database schema to understand the revenue structure.
I notice the KB refund rule references stg_refunds, but there's no refunds table in this database — so no refund reversal can apply here. I'll confirm that reasoning holds.
Now, revenue. There's analytics_marts.orders with subtotal_usd, tax_paid_usd, order_total_usd. Per CLAUDE.md I must verify grain and reconcile the mart against the raw source before trusting any number. Let me run grain and reconciliation checks.
Grain is clean (74,243 = distinct = raw count, no fan-out) and order_total = subtotal + tax exactly. Now I need the project's definition of "revenue." The stores mart has a revenue_usd column — let me see whether it aligns with subtotal (net) or order_total (gross incl. tax), and re-derive May 2019 directly from the raw source.
Major red flag. Same order count (9,482) for May 2019, but the raw source and the mart disagree wildly on the money:
- Raw: subtotal 9,902,500, tax 507,882, total 10,410,382
- Mart: subtotal 276,011, tax 5,078.82, total 281,089.82
The mart's tax is exactly raw/100, but subtotal is not — so this isn't a clean unit conversion. Also note stores.revenue_usd = subtotal (net of tax), telling me the project defines revenue as subtotal. I must not trust either number until I understand the raw units. Let me inspect matching individual rows and product prices to determine whether raw is in cents or dollars.
I now have a clear and corroborated picture. Here's what the data shows:
Raw values are in cents. Product prices are 400–1400 (i.e. $4–$14 for a jaffle shop), and the mart's tax_paid_usd = raw tax_paid ÷ 100 exactly. So raw subtotal is in cents.
The analytics_marts.orders money columns are broken. For a single order, raw subtotal 500¢ (=$5.00) shows as subtotal_usd = 15.00 in the mart — inflated and non-uniform (May aggregate is ~2.8× too high). Tax converts correctly but subtotal/total do not. Do not trust the mart's dollar figures.
Re-derived from the raw source, two independent ways, both agree exactly:
raw.orders.subtotalfor May 2019 = 9,902,500¢- Rebuilt from
raw.items×raw.products.price= 9,902,500¢ (independent corroboration)
Grain is clean (74,243 orders = distinct = raw count, no fan-out). The KB refund rule references stg_refunds, but no refunds table exists in this database, so no revenue reversal applies.
The project defines revenue as subtotal (net of sales tax) — that's what stores.revenue_usd maps to (subtotal, not order_total).
Answer
$99,025.00
May 2019 revenue (net of sales tax), from 9,482 orders. Re-derived at order grain from raw.orders and independently confirmed against order-line item prices.
For reference: gross including tax would be $104,103.82, but standard revenue (and this project's revenue_usd convention) excludes collected sales tax.
⚠️ Important data-quality flag: The analytics_marts.orders table has corrupted subtotal_usd and order_total_usd columns (querying it directly would have given you ~$276K — badly wrong). Any dashboard reading those columns is overstating revenue. There are no dbt model .sql files in this project to fix (the marts are materialized in the DB with no source in /work). Would you like me to investigate the mart's build logic further or help track down where it's defined so the bug can be fixed?
Let me save this non-obvious finding to memory.
Base Claude Code
claude code, no additional stack
reported $276,011 · truth $99,025 (2.79x inflated)
May 2019 revenue: $276,011.00
That's net product revenue (subtotal) across 9,482 orders, from analytics_marts.orders for ordered_at in May 2019.
I excluded sales tax, since tax collected ($5,078.82) is a liability owed to the government, not revenue. For reference:
| Measure | Amount |
|---|---|
| Revenue (subtotal) | $276,011.00 |
| Sales tax collected | $5,078.82 |
| Gross total (incl. tax) | $281,089.82 |
If your monthly report defines revenue as gross receipts including tax, use $281,089.82 instead — but the standard revenue figure is $276,011.00.
I'll explore the warehouse to find the revenue data. Let me start by looking at the schema and the dbt models.