Case #20 · Silent value corruption

Why SignalPilot passed where base failed

Evidence chips jump to the exact transcript moment

The entire difference between pass and fail was a minus sign. Claude Code reported account spend as -0.158, a negative cost, where the answer is +0.16. SignalPilot reasoned about the sign convention and passed.

Real-world impact

Finance would see negative spend on the account overview: the report says the account earned money by sending messages. Plugged into any budget or margin calculation, the sign error flips the result.

Where base Claude Code went wrong

Used the same raw sum of prices for both report tables.
That is correct at the phone number level but wrong at the account level, where the answer reports spend as positive: -0.158 shipped where +0.16 was expected.

What SignalPilot did differently

Treated the two spend columns as two different questions: sign preserved at the phone-number level, normalized to positive at the account level.
The project docs never spell this out; the marketing domain skill's sign rule prescribed it, and the spec recorded the decision.

Jump to the exact moment

The numbers

Check	Correct answer	Base Claude Code	SignalPilot
Account spend	+0.16	-0.158 (negative cost)	+0.16
Phone number spend	-0.158	-0.158	-0.158

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 marketing domain skill mandates checking the sign of every monetary source before summing it; base Claude Code never mentioned signs at all.
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.
Min and max price checks found messages priced negative while usage records run positive; base Claude Code summed raw prices without ever checking a sign.
06
Write the technical spec
A written plan of sources, joins, filters, and grain per model.
The spec recorded the sign decision before any SQL, preserve the negative in the single-source phone model and normalize to positive where the two sources mix; base Claude Code went straight to sum(price) for both.
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

claude code + signalpilot governed stack

pass

60 turns91 tool calls$$1.46answer pinned · full chat below

final answer pass

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

✅ twilio001 — Complete

Models built

twilio__number_overview — 1 row per phone number
Aggregates all 10 messages into a single row for phone 15555555555. Counts by direction (total_outbound_messages = 10, total_inbound_messages = 0), counts by status (total_delivered_messages = 10, all other buckets = 0), and total_spend = SUM(price) = -0.158 — sign preserved per domain-marketing single-source rule.

twilio__account_overview — 1 row per (account_id × date_day)
Aggregates messages plus joins two dimension tables:

stg_twilio__account_history → account_name, account_status, account_type (filtered to is_most_recent_record = TRUE)
stg_twilio__usage_record → total_account_spend = 0.260 (total across all usage categories for the account)

Both spend columns use ABS() because messages have negative prices while usage records have positive prices — multi-source sign normalization per domain-marketing rule.

Key decisions

Decision	Rationale
Drive from `int_twilio__messages`	Domain-marketing: drive from event table, not dimension
`SUM(price)` in number_overview (no ABS)	Single-source model → preserve sign
`ABS(SUM(...))` in account_overview	Multi-source: messages negative, usage positive → normalize all
`stg_twilio__usage_record` over raw table	Staging model cleans dirty price strings (`$0.05`, `0.01x`)
`is_most_recent_record = TRUE` filter	Boolean flag for most recent account state

full chat, from the start

Aggregate messaging data for Twilio, one at the phone number level and another at the account level, including details like inbound/outbound message counts, message statuses, and total spend.

The project scan is complete. I have 2 stubs to rewrite and 1 existing complete model. Let me create the task list and proceed with Steps 2 and 3 in parallel.

Good — all three skills are loaded. Now let me mark Step 2 complete and move to Steps 3 and 4.

Clean parse — no errors. Let me now move to Step 4 (discover macros) and read the existing model simultaneously.

Now let me read all existing SQL files to understand the stubs and the existing complete model.

Now let me check macros and variables in the dbt_project.yml, then run the blueprint tool.

No project-level macros found. Proceeding to Step 5 (blueprint research) for both models.

Now let me explore the key source tables in parallel to understand data structure and sign conventions.

Good data picture. Let me now run map-columns for both models and also check the usage record price sign.

I have all research data. Key findings:

Messages price: negative (-0.0158), Usage record price: positive (0.01-0.07) → multi-source in account_overview → apply ABS() to both
int_twilio__messages is the driving table for both models
Account metadata comes from stg_twilio__account_history (via var('account_history'))
Total account spend comes from stg_twilio__usage_record (via var('usage_record'))

Now loading the knowledge-base skill for Step 6.

Technical spec written. Now proceeding to Step 7 — write and build both models.

Now build both models:

Both models built successfully. Now moving to Step 8 — verify.

All checks pass. Both verifiers confirm the models are correct.

Base Claude Code

claude code, no additional stack

fail

twilio__account_overview: FAIL — no pred column matched gold column 'total_messages_spend'
  twilio__number_overview: PASS

13 turns29 tool calls$$0.34answer pinned · full chat below

final answer fail

Both models built successfully. The warning about unique_combination_of_columns is a pre-existing YAML formatting issue in twilio.yml (not something I introduced) and doesn't affect correctness.

Here's what was created:

twilio__number_overview — aggregates at the phone number level from int_twilio__messages:

Inbound/outbound message counts
Per-status counts (accepted, scheduled, canceled, queued, sending, sent, failed, delivered, undelivered, receiving, received, read)
total_messages and total_spend

twilio__account_overview — aggregates at the (account_id, date_day) level, joining three sources:

Message status/direction counts and total_messages_spend from int_twilio__messages
Account metadata (account_name, account_status, account_type) from stg_twilio__account_history (most recent record per account)
total_account_spend from stg_twilio__usage_record, joined where the usage record's start_date matches the date_day

full chat, from the start

Aggregate messaging data for Twilio, one at the phone number level and another at the account level, including details like inbound/outbound message counts, message statuses, and total spend.

Let me read the existing model files to understand the exact column names before creating the new aggregation models.

Now let me check the DuckDB table schemas for the account history and usage record tables, and also look at what staging models are in dbt_packages.

Now I have everything I need. Let me create the two aggregation models.

Now run dbt to materialize both new models: