Why SignalPilot passed where base failed
Evidence chips jump to the exact transcript momentAsked for Bayview Medical's total billed charges, Claude Code reported $32.27 billion. The real number is $7.18 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 $32.27 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 $7.18 billion.
The numbers
| Check | Correct answer | Base Claude Code | SignalPilot |
|---|---|---|---|
| Bayview Medical's total billed charges | $7.18 billion | $32.27 billion (4.5x) | $7.18 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 re-derived the total from staging claim headers and service lines, both agreeing to the penny at $7.18 billion against the mart's $32.27 billion, and named the culprit model fanning each service line out per diagnosis; base Claude Code only summed the published mart and its rollup.
- 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 $7.18B · matches truth
I found the root cause. The answer is $7,175,420,144.87, and the marts are wrong.
Bayview Medical — Total Billed Charges: $7,175,420,144.87 (≈ $7.18 billion)
Across all 206,905 claims (client_id bayview_medical).
How I verified it (and why the marts are wrong)
I derived the figure from two independent raw sources that reconcile exactly:
| Source | Grain | Total billed charge |
|---|---|---|
stg_bayview__claims (claim header, from raw $.billing.claim.charges) | 206,905 rows = 206,905 distinct claims ✓ | $7,175,420,144.87 |
stg_bayview__service_lines (sum of line charge_usd) | 1.34M lines → 206,905 claims | $7,175,420,144.87 |
The claim-header total and the service-line rollup match to the penny — that's the true billed charge.
⚠️ The published marts report an inflated $32.27B — do not trust them
fct_bayview__claims, fct_org_charges_by_client, and fct_org_ar_summary all report $32,267,692,366.77 for Bayview. They agree with each other only because they share a broken upstream: int_bayview__claim_financials.
The bug is in that model's charges CTE (int_bayview__claim_financials.sql:7-12):
charges as (
select sl.claim_id, sum(sl.charge_usd) as total_charge_usd
from {{ ref('stg_bayview__service_lines') }} sl
join {{ ref('stg_bayview__diagnoses') }} dx on sl.encounter_id = dx.encounter_id -- ← spurious fan-out
group by sl.claim_id
)
The join to stg_bayview__diagnoses serves no purpose for summing charges, but it fans out every service line by the number of diagnoses on the encounter (~4.5 diagnoses/encounter: 931K diagnoses ÷ 207K encounters). That inflates the charge sum by ~4.5×: $7.175B × 4.5 ≈ $32.27B. The final grain stays at one row per claim, so a grain/row-count check alone wouldn't catch it — only re-deriving from source does.
This bug flows into every downstream org-level charge mart for Bayview, and the same pattern is worth checking on the other 8 clients' int_*__claim_financials models.
Would you like me to fix int_bayview__claim_financials (remove the diagnoses join) and rebuild the affected models? Since these feed AR and charge dashboards, the inflation is a significant reporting error. I'd also recommend I check whether the sibling client models share the same defect.
I'll start by loading the required skill for this dbt project, as mandated by the project instructions.
This is a healthcare analytics question. Per the project instructions, I'll run the dbt exploration steps. Let me start by loading the SignalPilot MCP tools and checking the knowledge base, while scanning the project.
The MCP tools are now available. Let me load the key tool schemas and check the knowledge base.
No knowledge base entries. Let me explore the relevant tables.
Now let me verify the grain and identify how "Bayview Medical" maps to a client_id. I'll query the staging source (the raw claims), which is the true source of billed charges.
Client is bayview_medical, and stg_bayview__claims is at true claim grain (206,905 rows = 206,905 distinct claims). Now let me independently re-derive the sum from the staging source rather than trusting the mart, and cross-check the fct model.
The two marts agree at $32.27B but the raw staging claims header sums to only $7.18B — same claim count, same grain. This is exactly the shared-lineage trap the project instructions warn about. I must find which figure is the true billed charge. Let me investigate where the fct model's number comes from.
Critical finding: the service-line charges sum to $7,175,420,144.87 — exactly matching the staging claim header total. But int_bayview__claim_financials reports $32.27B at the same one-row-per-claim grain (no fan-out). The intermediate model is inflating the charge. Let me read the model SQL to find why.
Base Claude Code
claude code, no additional stack
reported $32.27B · truth $7.18B (4.5x inflated)
Both the org-level rollup and the Bayview claims fact table agree.
Bayview Medical — total billed charges: $32,267,692,366.77 (≈ $32.27 billion) across 206,905 claims.
This is the sum of total_charge_usd over every row in fct_bayview__claims, and it matches the pre-aggregated fct_org_charges_by_client row for bayview_medical exactly.
I'll query the warehouse to find this. Let me first explore the schema to understand where claims and customer data live.
There's a Bayview-specific claims table. Let me inspect its schema and the org-level charges table.