Case #16 · Silent value corruption

Why SignalPilot passed where base failed

Evidence chips jump to the exact transcript moment

Claude Code's customer report included 1,770 people who never bought anything. Their only activity was returning items, and their "purchase totals" were computed from the goods they gave back. SignalPilot excluded them and matched the correct count exactly.

Real-world impact

1,770 fake paying customers entered the lifetime value analysis, each carrying a purchase total fabricated from returned merchandise. Any marketing or retention decision keyed on that list targets people who never made a purchase.

Where base Claude Code went wrong

Never separated purchases from returns when summing each customer's history; every transaction counted.
1,770 return-only customers got a row with a purchase total fabricated from goods they gave back: 76,777 rows instead of 75,007.

Jump to the exact moment

What SignalPilot did differently

Its first build made the same mistake; the value verifier failed it at 76,777 rows, citing the domain rule that returns are excluded from purchase totals.
The prescribed WHERE filter removed the return-only rows, proved on real data: 76,777 minus 1,770 equals 75,007 retained.

Jump to the exact moment

The numbers

Check	Correct answer	Base Claude Code	SignalPilot
Real purchasing customers	75,007	76,777 (1,770 fake)	75,007

Verification workflow

Where the eight-step workflow caught it.

Read the workflow docs

01
Map the project
Scan every model: dependencies, required columns, macros, hazards.
02
Load supporting skills
SQL, database, and domain rules loaded before any writing.
The e-commerce domain skill states that returns sitting as rows in a purchase table must be filtered out of purchase totals, the exact rule the fix cited; base Claude Code summed every transaction.
03
Validate the project
Parse check, plus rebuild of stale time-dependent upstreams.
04
Discover project macros
Find macro-derived columns the YML contract does not list.
05
Research the data
Driving table, cardinalities, contract, sibling patterns, categorical values.
06
Write the technical spec
A written plan of sources, joins, filters, and grain per model.
07
Write and build models
SQL from the spec, with column mapping checked before each model.
08
Verify and fix
Two read-only verifiers cross-check structure and values until every check passes.
SignalPilot's own first build made the same mistake as base; the value verifier failed it at 76,777 rows, prescribed the WHERE filter citing the domain rule, and the rebuild removed the 1,770 return-only customers; base Claude Code never queried its own output.

SignalPilot

claude code + signalpilot governed stack

pass

66 turns143 tool calls$$1.83answer pinned · full chat below

final answer pass

All 8 steps complete. Here's a summary of what was built:

✅ Task Complete — tpch001

Models Built

`lost_revenue` — 28,424 rows

Filters lineitem for l_returnflag = 'R' (returned items, matching sibling SUPPLIER_RETURNS.sql pattern)
Aggregates revenue lost per customer: ROUND(SUM(l_extendedprice * (1 - l_discount)), 2)
LEFT JOINs customer and nation for enrichment details
Columns: c_custkey, c_name, revenue_lost, c_acctbal, n_name, c_address, c_phone, c_comment

`client_purchase_status` — 75,007 rows

purchase_total: SUM of customer_cost from order_line_items excluding item_status = 'R' (domain-ecommerce rule: returns are in the same fact table → exclude with WHERE)
return_total: from ref('lost_revenue'), COALESCE'd to 0 for customers with no returns
lifetime_value: purchase_total − return_total
return_pct: on 0–100 scale (dbt-write pct rule)
customer_status: equal-width bands (green < 25%, yellow 25–50%, orange 50–75%, red 75–100%, NULL for anomalies > 100%)

Key Decisions

1,770 return-only customers excluded from client_purchase_status (correct — no net purchases)
9,909 anomaly customers have customer_status = NULL (return_pct > 100%, where returns exceeded purchases — intentional per domain-ecommerce + accepted_values test)
Fact-table-drives pattern applied to both models per domain-ecommerce skill

full chat, from the start

Calculate the lifetime value of a customer by analyzing their total purchases and returns, categorize their status based on the percentage of returns, and combine this data with lost revenue information.

I'll follow the 8-step dbt workflow. Let me start with the Knowledge Base check and Step 1 simultaneously.

No KB entries. Now let me create tasks for Steps 2–8 and load the YML files to understand the project structure.

Now let me load the supporting skills and also read the existing stub SQL files.

Now let me load the remaining skills and read the stub SQL files simultaneously.

No macros found. Now let me mark Step 3 and 4 complete and proceed to Step 5 — blueprints for both models.

Now let me query the source data to understand the data structure before writing SQL.

Let me query the data to understand the return patterns and grain before writing the spec.

Let me also check alternative purchase_total calculations to understand which makes more sense.

The data confirms: purchase_total = ALL items (gross) → return_pct stays 0-100% with no anomalies. Now let me load the knowledge-base skill and write the technical spec.

No existing spec. Let me write it now based on all research findings.

Technical spec written. Now Step 7 — let me run map-columns for both models before writing SQL.

All column mappings confirmed. Now let me write the SQL for both models.

Both SQL files written. Now let me build them — lost_revenue first, then client_purchase_status.

order_line_items not yet materialized — adding + prefix for this specific model only.

Both models built successfully. Now Step 8 — dispatching both verifiers in parallel.

The value verifier flagged client_purchase_status CHECK 3 FAIL: 1,770 customers with only 'R' items inflating purchase_total. The domain-ecommerce rule requires WHERE item_status != 'R' in the purchases CTE. Updating spec then SQL.

Now update the SQL to add the filter and rebuild.

Rebuilt successfully. Now re-run both verifiers to confirm all checks pass after the fix.

Both verifiers return all checks PASS. Let me mark Step 8 complete.

Base Claude Code

claude code, no additional stack

fail

client_purchase_status: FAIL — row count mismatch gold=75007 pred=76777

13 turns28 tool calls$$0.22answer pinned · full chat below

final answer fail

All 8 models materialized successfully. Here's what was created:

models/FINANCE/lost_revenue.sql — Joins customer, orders, lineitem, and nation, filters for returned items (l_returnflag = 'R'), and sums l_extendedprice * (1 - l_discount) per customer to compute revenue_lost.

models/FINANCE/client_purchase_status.sql — Combines two data sources:

order_line_items → sums all customer_cost per customer for purchase_total
lost_revenue → provides return_total

Then computes:

lifetime_value = purchase_total - return_total
return_pct = return_total / purchase_total * 100
customer_status via thresholds: green (<5%), yellow (5–10%), orange (10–20%), red (≥20%)

full chat, from the start

Now I have a complete picture. I need to create two missing model files:

models/FINANCE/lost_revenue.sql
models/FINANCE/client_purchase_status.sql

Now run dbt to materialize all models: