Why SignalPilot passed where base failed
Evidence chips jump to the exact transcript momentAsked for the billed charges on Bayview Medical's denied claims, Claude Code reported $3.57 billion. The real number is $795.2 million, 4.49x lower. SignalPilot reconciled the published mart against its source data and reported the correct figure.
Real-world impact
A 4.49x 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 $3.57 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 $795.2 million.
The numbers
| Check | Correct answer | Base Claude Code | SignalPilot |
|---|---|---|---|
| The billed charges on Bayview Medical's denied claims | $795.2 million | $3.57 billion (4.49x) | $795.2 million |
Verification workflow
Where the eight-step workflow caught it.
- 01Map the project
Scan every model: dependencies, required columns, macros, hazards.
The scan mapped the lineage under the denials mart, so the first queries went straight to the poisoned claim financials intermediate; base Claude Code poked at tables ad hoc with no model map.
- 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 recomputed denied charges from staging at claim grain, saw the mart come back 4.49x higher on identical row counts, and confirmed each claim's inflation ratio equals its diagnosis count exactly; base Claude Code shipped $3.57 billion as verified.
- 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 $795.2M · matches truth
Confirmed. The inflation ratio equals n_diagnoses exactly for every claim (7, 1, 3, 1, 4). The diagnosis is airtight.
Answer
Total billed charges on Bayview Medical's denied claims: $795,161,367.37 (across 22,811 denied claims).
This is the figure re-derived at the true claim grain from the raw source — the only number safe for the finance write-up.
⚠️ Do not use the published mart figure
fct_bayview__denials (and its upstream int_bayview__claim_financials) reports $3,570,302,977.99 for the same 22,811 claims — 4.5× too high. That number is wrong, and here's exactly why:
- Both the mart and the intermediate model are at clean claim grain (206,905 rows = distinct
claim_id), so a naive row-count reconciliation passes and hides the bug. - But
int_bayview__claim_financialsfans the claim out against the diagnosis table before summingtotal_charge_usd, then regroups to one row per claim. The charge is left multiplied by each claim's diagnosis count. - Proof: for every sampled claim the mart/raw charge ratio is an exact integer equal to
n_diagnoses(e.g. CN-0000011: $33,837.91 × 7 diagnoses = $236,865.37).
How the correct number was derived
Joined raw claim headers to raw remittance at claim_id (both verified 1:1, one row per claim, no NULL charges), filtered to is_denied = TRUE:
SELECT SUM(c.total_charge_usd)
FROM analytics_staging.stg_bayview__claims c
JOIN analytics_staging.stg_bayview__remittance r ON c.claim_id = r.claim_id
WHERE r.is_denied = TRUE; -- 795,161,367.37
This is a real dbt breakage
int_bayview__claim_financials is silently inflating total_charge_usd for every downstream consumer (fct_bayview__denials, fct_bayview__claims, and likely the org-level rollups that share this pattern across all clients). Any dashboard sourced from these marts is overstating charges.
Would you like me to fix int_bayview__claim_financials (remove the diagnosis fan-out so total_charge_usd stays at claim grain) and rebuild the affected models? I'd want your go-ahead before touching the project, since it will change published dashboard numbers.
I'll start by loading the required workflow skill, as the project instructions mandate.
This is a business-outcome question. Per the project instructions, I'll still run the exploration steps. Let me load the SignalPilot MCP tools and start the project scan in parallel.
Now let me load the MCP tool schemas and query the knowledge base.
Now let me run the knowledge base check and DB analysis in parallel.
Knowledge base is empty. Let me run targeted search probes and explore the relevant tables.
I have the full schema. Now let me verify grain and re-derive the number from the raw staging source rather than trusting the mart. Let me run grain checks and the derivation together.
Grain is clean (one row per claim_id everywhere), and the denied count (22,811) reconciles between the raw remittance flag and the denials mart. Now let me re-derive the billed-charges total from the raw staging source and compare it to the mart.
The row counts match (22,811 denied claims in both), but the mart's charge total ($3.57B) is 4.5× larger than the raw-derived total ($795M) — a classic silently-broken mart. Same grain, same count, inflated dollar values. Let me trace where the inflation enters.
The inflation is now diagnosed. int_bayview__claim_financials outputs one row per claim (clean 206,905 grain) but its total_charge_usd is inflated by integer multipliers (7×, 1×, 3×, 1×, 4×) — the signature of a fan-out join (claim → service lines / diagnoses) where the claim charge was SUM'd across the fanned rows before regrouping. The fct_bayview__denials mart inherits this broken value. Let me confirm the multiplier is a join fan-out count so the diagnosis is airtight.
Base Claude Code
claude code, no additional stack
reported $3.57B · truth $795.2M (4.49x inflated)
Both tables agree exactly. The denials mart and the is_denied = true claims match on both count (22,811) and charges.
Answer
Total billed charges on Bayview Medical's denied claims: $3,570,302,977.99
Details for the finance review:
- Denied claims: 22,811
- Billed charges (
total_charge_usd): $3,570,302,977.99
This is corroborated by two independent sources in the warehouse that agree exactly:
analytics_marts.fct_bayview__denials(one row per denied claim) — sum = $3,570,302,977.99analytics_marts.fct_bayview__claimsfiltered tois_denied = true— 22,811 claims, same sum
Note this is billed (gross) charges. If the write-up also needs net exposure, the same denied claims carry $paid and unpaid balances (paid_amount_usd / unpaid_usd) I can total up as well.
I'll explore the warehouse to find the relevant data.
There's a Bayview-specific denials table. Let me examine it.
That number seems large. Let me cross-check against the claims table to make sure the denials table is what it appears to be.