Why SignalPilot passed where base failed
Evidence chips jump to the exact transcript momentAsked for Metro Children's total billed charges, Claude Code reported $29.54 billion. The real number is $6.57 billion, 4.5x lower. SignalPilot reconciled the published mart against its source data and reported the correct figure.
Real-world impact
A 4.5x overstatement of a headline financial figure, delivered with confidence. The agent cross-checked two tables that are built from the same poisoned layer and took their agreement as proof.
- A hidden diagnoses join inflates every charge sum roughly 4.5x before aggregation; row counts and uniqueness checks stay clean.
- Summed the published mart and shipped $29.54 billion as verified; its checks never left the poisoned layer.
- Did not take the published mart at face value: it recomputed the figure from the staging claim headers and service lines, which agree with each other to the penny.
- Traced the gap to the diagnoses join inside the claim financials model and reported the corrected $6.57 billion.
The numbers
| Check | Correct answer | Base Claude Code | SignalPilot |
|---|---|---|---|
| Metro Children's total billed charges | $6.57 billion | $29.54 billion (4.5x) | $6.57 billion |
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 reproduced the mart's buggy join and measured exactly 4.5 diagnoses per encounter, the precise ratio between $29.54 billion and $6.57 billion, then cross-validated staging parsed from the raw blob; base Claude Code called two tables from the same layer agreeing "verified two ways".
- 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 $6.57B · matches truth
Metro Children's — total billed charges: $6,567,401,322.57 (≈ $6.57 billion)
across all 189,272 of their claims.
How I verified it (and why the obvious number is wrong)
I re-derived this at claim grain directly from the raw source, and two independent source paths agree to the penny:
| Source path | Total charges |
|---|---|
Claim-level billed charge, parsed straight from raw blob ($.billing.claim.charges) | 6,567,401,322.57 |
| Clean sum of service-line charges (no fan-out) | 6,567,401,322.57 |
Grain is clean: stg_metro__claims is 189,272 rows = 189,272 distinct claims (one row per claim, no fan-out).
⚠️ The published marts are wrong — do not use them for the deck
fct_metro__claims, fct_org_charges_by_client, fct_org_monthly_charges, and fct_org_claims all report $29,537,823,474.82 for Metro — 4.5× too high. They agree with each other only because they share the same broken upstream (int_metro__claim_financials), so a reconciliation between them proves nothing.
Root cause — models/intermediate/metro_childrens/int_metro__claim_financials.sql, line 10:
charges as (
select sl.claim_id, sum(sl.charge_usd) as total_charge_usd
from stg_metro__service_lines sl
join stg_metro__diagnoses dx on sl.encounter_id = dx.encounter_id -- ← fan-out
group by sl.claim_id
)
The join to diagnoses multiplies every service line's charge by the number of diagnoses on the encounter. Metro averages exactly 4.500 diagnoses per encounter, which is precisely the 29.5B / 6.57B inflation. The diagnoses join contributes nothing to the SELECT — it only fans out the sum.
This is not isolated to Metro. The same buggy pattern exists in the intermediate models for bayview, harbor, northstar, plains, and riverside (summit uses a claim_seq=1 variant; only cedar and valley are correct, as they carry the claim-level charge through directly). So every org-wide charge mart is overstated.
Do you want me to fix it?
Per the project's policy on DBT breakages, I've stopped short of editing. I can fix int_metro__claim_financials.sql (drop the diagnoses join, or use the claim-level total_charge_usd), and apply the same correction to the other five affected clients, then rebuild the affected marts. Want me to proceed — Metro only, or all affected clients?
I'll start by loading the dbt-workflow skill, as required before any dbt/data work in this project.
This is a business-outcome question. Per project instructions, I'll still run the dbt exploration steps. Let me begin Step 1: scan the project and load the SignalPilot MCP tools in parallel.
The MCP tools are now available. Let me load the schemas for the ones I need and read the rest of the scan output.
Now let me run the DB analysis and knowledge base probes in parallel.
Knowledge base is empty. This is an analytics question, so let me explore the data. Let me list the tables to understand the schema.
Let me check the DBT project for how clients are named (to confirm "Metro Children's") and inspect the metro claim grain.
"Metro Children's" is client_id metro_childrens. The staging claims table is clean at claim grain (189,272 rows = 189,272 distinct claims, no fan-out). Let me verify the staging model derivation against the raw source and cross-check the marts.
Critical finding: the marts (29.5B) disagree with staging parsed directly from the raw blob (6.57B) — and all four marts agree with each other because they share the same upstream lineage. This is exactly the trap CLAUDE.md warns about. Same row count (189,272), so it's not fan-out at claim grain — the per-claim value is inflated somewhere in the intermediate layer. Let me investigate.
Found the bug. In int_metro__claim_financials.sql, the charges CTE sums service-line charges but joins service_lines to diagnoses on encounter_id (line 10) — a fan-out join that multiplies every service line's charge by the number of diagnoses on its encounter. Metro has ~4.5 diagnoses per encounter, which is exactly the 29.5B / 6.57B ≈ 4.5× inflation. All the org marts inherit this from fct_metro__claims, so they agree with each other while all being wrong.
Let me confirm the mechanism and get the clean service-line total.
Definitive. Two independent correct derivations from source agree to the penny:
- Claim-level billed charge (parsed straight from the raw blob
$.billing.claim.charges): 6,567,401,322.57 - Clean sum of service-line charges (no fan-out): 6,567,401,322.57 — exact match.
The buggy charges CTE reproduces the marts' 29,537,823,474.82 exactly, and the inflation factor is precisely the 4.500 average diagnoses per encounter. The marts are wrong; the source figure is solid.
Let me quickly check whether this fan-out bug is isolated to Metro or repeats across the other clients (it affects the org-wide marts too).
Base Claude Code
claude code, no additional stack
reported $29.54B · truth $6.57B (4.5x inflated)
$29,537,823,474.82
Metro Children's (metro_childrens) has billed $29.54 billion in total charges across all 189,272 of their claims.
For the board deck: $29.5B (or precisely $29,537,823,474.82).
Verified two ways that agree exactly: summing the source fct_metro__claims table and the pre-aggregated fct_org_charges_by_client rollup. Every claim row is a distinct claim (189,272 rows = 189,272 unique claim IDs), so there's no double-counting inflating the figure.
I'll explore the warehouse to find the relevant tables and compute this.
Metro maps to a fct_metro__claims table. Let me verify the client naming and understand the schema before trusting it.
The rollup shows metro_childrens at $29,537,823,474.82 across 189,272 claims. Let me cross-check against the underlying fct_metro__claims table to make sure the rollup isn't stale or defined differently.
Both the source fact table and the org rollup agree exactly. Quick check for duplicate claim rows that could inflate the sum: