Why SignalPilot passed where base failed
Evidence chips jump to the exact transcript momentAsked for the average customer lifetime value, Claude Code reported $2,250.48. The real number is $821.63, 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 $2,250.48; 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 $821.63 from the raw source.
The numbers
| Check | Correct answer | Base Claude Code | SignalPilot |
|---|---|---|---|
| The average customer lifetime value | $821.63 | $2,250.48 (2.74x) | $821.63 |
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 summed mart and raw side by side and traced a single order to its line items, exposing the 2.74x inflation; base Claude Code reconciled the customers mart against the orders mart, two tables sharing the same defect.
- 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 $821.63 · matches truth
Average customer lifetime value: $821.63 per customer
That's net product revenue (total revenue $763,290 ÷ 929 customers, all of whom have orders). Including sales tax collected, it's $865.34 — but tax is a pass-through, not revenue, so $821.63 is the figure for a retention review.
⚠️ Do not use the customers mart for this
The analytics_marts.customers.lifetime_spend_usd column reports an average of $2,250 — that is wrong, and so is analytics_marts.orders.subtotal_usd. Here's what I found tracing the numbers back to source:
raw.orders.subtotalandraw.products.priceare stored in cents (a $4.00 drink =400, a $14 jaffle =1400).- For every one of the 73,673 orders with line items,
raw.orders.subtotalexactly equals the sum of its line-item prices, and the raw total (76,329,000¢ = $763,290) exactly matches the independentorder_itemsline-item total. That's the ground truth. - The marts'
*_usdcolumns show2,090,694— which matches neither the correct $763,290 nor a clean cents/dollars conversion. It's a corrupted transformation. - The
customersmart and theordersmart agree with each other (both say 2,090,694) only because the customer mart sums the already-broken order mart — a shared upstream bug, not a correct number. Averaginglifetime_spend_usdwould have given you $2,250 and looked internally consistent.
Grain was clean throughout (one row per customer, one per order, one customer per order — no fan-out), so the only issue is the unit/transformation bug in the marts.
Note
There are no dbt model files in this project directory (/work) — the marts exist only as materialized tables in the database, so I can't fix the broken transformation via dbt from here. If you can point me to where these marts are defined (or want to add them as dbt models), I can fix the cents→USD conversion so the published lifetime_spend_usd reads correctly. Want me to do that?
I'll start by loading the required workflow skill, as mandated by the project instructions.
Now let me load the SignalPilot MCP tools I'll need and run the initial exploration. Let me search for the tool schemas first.
The project scan shows no dbt models exist. This is a pure business-outcome question, so per the instructions I'll run the exploration steps against the database directly. Let me load the MCP tool schemas.
Now let me gather knowledge and explore the database in parallel.
There's a customers mart with lifetime_spend_usd — but per project rules I must not trust a mart number without re-deriving it at the correct grain from source. Let me run the required knowledge probes and verify grain.
The only KB entry (nala-refund-window-rule) is scoped to a different project (project:nala, references stg_refunds) and there's no refunds table on this connection, so it doesn't apply here.
Now let me verify grain at the source before trusting anything. Let me check the grain of both the customers mart and the orders, and reconcile the mart against raw.
Grains confirmed clean: one row per customer (929) and one row per order (74,243) in both raw and marts; order_total = subtotal + tax_paid. Now let me re-derive per-customer revenue directly from raw and reconcile against the mart, and check whether every customer has orders.
All 929 customers have orders and the order counts reconcile (74,243). But the revenue does not reconcile at all: re-derived from raw, average per-customer revenue is ~$82K, yet the customers mart's lifetime_spend_usd averages only $2,250 — a ~36× gap. Per the project rules, I can't trust either number until I find where they diverge. Let me trace one order across raw → staging → mart and compare the order-grain revenue against the line items.
The picture is now clear, and it's exactly the trap the project rules warn about. For order 0006b3aa:
raw.orders.subtotal= 2200, but the 3 line items sum to $22.00 → raw subtotal is in cents.mart.orders.subtotal_usd= 80.00 → matches neither ($22 from line items, nor $22 from raw/100). The mart is silently broken.- The
customersmart total (2,090,694) exactly equals theordersmart subtotal total — both agree because they share the same broken upstream, not because they're right.
Meanwhile raw subtotal total (76,329,000 cents = $763,290) exactly matches the line-items total ($763,290). Let me verify the cents hypothesis rigorously at the row level against raw line items and product prices, not the marts.
That resolves it. raw.products.price and raw.orders.subtotal are both in cents (a beverage at 400 = $4.00, a jaffle at 1400 = $14.00 — realistic prices), and for all 73,673 orders with line items, subtotal exactly equals the sum of line-item prices. Converting to USD (÷100) gives a total of $763,290, which independently matches the order_items line-item total. The orders/customers marts' *_usd columns (2,090,694) match neither and are simply broken.
Let me run one final clean query to lock in the authoritative figures from the verified source.
Base Claude Code
claude code, no additional stack
reported $2,250.48 · truth $821.63 (2.74x inflated)
Average customer lifetime value: $2,250 USD per customer
Computed from the analytics_marts.customers mart:
| Metric | Value |
|---|---|
| Customers | 929 |
| Total lifetime revenue | $2,090,694 |
| Avg LTV (revenue ÷ customers) | $2,250.48 |
One number for the review: ~$2,250 per customer.
Notes for accuracy:
- This uses
lifetime_spend_usd, which is net revenue (subtotal, excludes sales tax) — I confirmed it reconciles exactly toSUM(subtotal_usd)in the orders table. This is the correct basis for "revenue," since collected tax isn't company revenue. - All 929 customers have at least one order, so there are no zero-spend customers deflating the average.
- If you instead want gross receipts including tax (
order_total_usd), the figure is $2,294.20. For a revenue/retention review, the $2,250 net figure is the one to use.
I'll explore the dbt project structure and the warehouse to find the right revenue and customer data.