Why SignalPilot passed where base failed
Evidence chips jump to the exact transcript momentAsked for Plains Regional's total billed charges, Claude Code reported $22.69 billion. The real number is $5.04 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 $22.69 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 $5.04 billion.
The numbers
| Check | Correct answer | Base Claude Code | SignalPilot |
|---|---|---|---|
| Plains Regional's total billed charges | $5.04 billion | $22.69 billion (4.5x) | $5.04 billion |
Verification workflow
Where the eight-step workflow caught it.
- 01Map the project
Scan every model: dependencies, required columns, macros, hazards.
The scan traced lineage down to the plains claim financials model before trusting any number; base Claude Code queried the published mart and never opened a model file.
- 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 measured 4.5 diagnoses per encounter, located the join in the model's SQL, and reconciled two independent source derivations to the penny at $5.04 billion; base Claude Code summed the mart.
- 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 $5.04B · matches truth
Confirmed. The numbers reconcile exactly:
- Claim-level billed charges (from source claim blob): $5,039,433,956.75
- Service-line charges summed independently (no bad join): $5,039,433,956.75 — identical, so two independent source derivations agree.
- Avg diagnoses/encounter = 4.499, and $5.04B × 4.499 ≈ $22.69B — precisely the inflated figure sitting in the mart.
Answer
Plains Regional's total billed charges across all 144,989 claims = $5,039,433,956.75 (≈ $5.04 billion USD).
⚠️ Important for the QBR — do not use the published mart figure. The models fct_plains__claims, fct_org_claims, and the client-rollup fct_org_charges_by_client all report $22.69B for Plains, which is 4.5× too high. That number is wrong.
Root cause: int_plains__claim_financials recomputes total_charge_usd by summing service-line charges joined to the diagnoses table on encounter_id. With ~4.5 diagnoses per encounter, every service line's charge gets counted ~4.5×. The bug is invisible to a row-count check because the result is grouped back to one row per claim — the grain looks clean while the value is inflated. This is exactly the "mart reconciles with the fact table but both share the broken upstream" trap: every downstream table built off this intermediate carries the same 4.5× inflation.
The correct value ($5.04B) comes straight from each claim's total_charge field in stg_plains__claims, and is independently corroborated by the raw service-line sum.
This is a real defect in the dbt project that will mis-state charges for every client, not just Plains (the same int_*__claim_financials pattern feeds fct_org_claims and fct_org_charges_by_client). Would you like me to:
- Fix
int_plains__claim_financials(drop the erroneous diagnoses join in thechargesCTE — or just use the claim's owntotal_charge_usdinstead of re-summing) and rebuild the affected models, and/or - Check the other 8 clients for the same fan-out bug before the QBR?
Just say the word and I'll proceed via the dbt workflow.
I'll start by loading the dbt-workflow skill, which owns the workflow for this project.
This is a business-outcome question on healthcare claims data. Per the project instructions I'll run the dbt exploration workflow first (grain verification is critical here). Let me load the SignalPilot MCP tools and start Step 1.
MCP tools are available now. Let me load the schemas I need and run knowledge probes.
Knowledge base has nothing relevant. Let me explore the claims tables. "Plains Regional" is one of the facilities/clients. Let me examine the relevant tables.
I now have the landscape. "Plains" is a client with fct_plains__claims (145K rows), plus staging stg_plains__claims and an aggregate mart fct_org_charges_by_client. Billed charges = total_charge_usd. Per the grain rule, I'll verify grain at each level rather than trust any single table. Let me start with the source and the fact table.
A textbook grain/value trap. All three tables are clean at one-row-per-claim (144,989 rows = 144,989 distinct claims — no fan-out). But the value of total_charge_usd diverges massively: staging source = $5.04B, while the intermediate and mart both show $22.69B (4.5×). Since the grain is identical, the intermediate model is transforming the charge value. I need to find out which is the true billed charge. Let me read the model SQL.
The bug is now visible. In int_plains__claim_financials, the charges CTE recomputes total_charge_usd by summing service-line charges — but it joins service lines to the diagnoses table on encounter_id:
charges as (
select sl.claim_id, sum(sl.charge_usd) as total_charge_usd
from stg_plains__service_lines sl
join stg_plains__diagnoses dx on sl.encounter_id = dx.encounter_id -- fan-out!
group by sl.claim_id
)
Each service line is multiplied by the number of diagnoses on its encounter, inflating the summed charge. Because the result is grouped back to claim_id, the row grain stays clean (1 per claim) — which is exactly why the fan-out hides from a row-count check but corrupts the value. The true billed charge lives in stg_plains__claims.total_charge_usd, read straight from the source claim blob. Let me confirm the mechanism.
Base Claude Code
claude code, no additional stack
reported $22.69B · truth $5.04B (4.5x inflated)
Both the dedicated Plains mart and the org-wide fact table agree exactly, which is a good consistency check.
Plains Regional — total billed charges
$22,686,900,303.89 (~$22.69 billion) across 144,989 claims.
Details:
- Source:
analytics_marts.fct_plains__claims, summingtotal_charge_usd(billed charges) across all claims forclient_id = 'plains_regional'. - Cross-check: Filtering the org-wide
fct_org_claimsto Plains Regional returns the identical claim count and total, so the figure is consistent across both marts. - Spread across 6 facilities (FAC-01 through FAC-06), each contributing ~24k claims.
Note: this is billed charges (gross), not collected/paid. If the QBR also wants paid or outstanding, those live in the same table as paid_amount_usd and unpaid_usd — happy to pull those too.
I'll query the warehouse to find Plains Regional's total billed charges. Let me first explore the schema.
Confirmed — client_id = 'plains_regional' is Plains Regional. Let me sum total billed charges, cross-checking the dedicated mart against the org-wide fact table.