Why SignalPilot passed where base failed
Evidence chips jump to the exact transcript momentClaude Code's idea of data cleaning destroyed real information: it overwrote 2,060 genuinely empty post bodies and deleted 475 real comments. SignalPilot preserved every row and every empty value.
Real-world impact
475 real comments vanished from the dataset and 2,060 posts had their content silently rewritten. Any analysis of posting behavior, engagement, or content would run on corrupted, incomplete data with no warning.
- Replaced genuinely empty post bodies with blank text, wiping out 2,060 real empty values the answer preserves.
- Dropped any comment missing a link field, deleting 475 real comments.
- Treated the project's data quality checklist as instructions to enforce by removing data.
Jump to the exact moment
- Its rules say quality checks describe the output; they do not license dropping or rewriting rows.
- Shipped straight pass-throughs: all 30,971 posts with empty bodies intact, and all 15,359 comments.
Jump to the exact moment
The numbers
| Check | Correct answer | Base Claude Code | SignalPilot |
|---|---|---|---|
| Posts preserved faithfully | 30,971, empties intact | 2,060 rewritten | all preserved |
| Comments | 15,359 | 14,884 (475 deleted) | 15,359 |
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.
The writing rules state quality checks describe the output, not filter the input, so every row was kept; base Claude Code read the same not_null tests and responded with filters, dedup, and blank-string rewrites.
- 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 fixed the contract before any SQL, 30,971 posts and 15,359 comments expected to survive untouched; base Claude Code printed the same source counts, then never held its output to them.
- 06Write the technical spec
A written plan of sources, joins, filters, and grain per model.
The spec recorded "Filters: none, not_null tests are output assertions" with expected rows matching the source; base Claude Code's plan said the opposite.
- 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.
The verifier compared the finished tables against the source, 30,971 = 30,971 and 15,359 = 15,359; base Claude Code never compared, so 475 deleted comments went out unnoticed.
SignalPilot
claude code + signalpilot governed stack
All 8 steps complete. Here's a summary of what was built:
✅ reddit001 — Complete
Models Built
prod_posts_ghosts — Cleansed posts from paranormal subreddits
- Source:
raw_posts_ghosts(30,971 rows → 30,971 output rows, 1:1) - Renames 10 columns to their prefixed YML aliases (
author → author_post,created_at → post_created_at, etc.) - Pass-through 3 extra source columns:
author_flair_text,clicked,saved - Adds 2 macro-derived columns:
hour_created_at(viaextract_hour) andnormalized_created_at(vianormalize_timestamp)
prod_comments_ghosts — Cleansed comments from paranormal subreddits
- Source:
raw_comments_ghosts(15,359 rows → 15,359 output rows, 1:1) - Renames 7 columns to their YML aliases (
post_url → comments_post_url,body → comment_body,post_id → comments_post_id, etc.) - Adds 2 macro-derived columns:
hour_created_atandnormalized_created_at
Verification Results
| Check | prod_posts_ghosts | prod_comments_ghosts |
|---|---|---|
| Table exists | ✅ PASS | ✅ PASS |
| All YML columns present (17 / 11) | ✅ PASS | ✅ PASS |
| Row count matches source | ✅ PASS (30,971) | ✅ PASS (15,359) |
| No fan-out / grain correct | ✅ PASS | ✅ PASS |
| Aggregate values match source | ✅ PASS | ✅ PASS |
I'll follow the full 8-step dbt workflow. Let me start by checking the knowledge base and scanning the project simultaneously.
No KB entries. Now let me create tasks and proceed with Step 2.
Now loading Step 2 — all three supporting skills in parallel.
All three skills loaded. Now Step 3 — validate the project.
Project validates with no errors. No current_date hazards flagged. Step 3 complete. Proceeding to Step 4 — macro discovery.
Let me look at the existing stub files and explore the source tables to understand the macro targets.
Now let me explore both source tables and run blueprints in parallel.
Step 4 macro notes recorded. Now Step 5 — run blueprints for both models in parallel.
Good. I can see both tables. Let me get full column details for both source tables.
Now let me run map-columns for both models to confirm column mappings.
Let me check the YML schema details and sample data.
Now let me read the schema.yaml for column definitions.
Now I have everything I need. Step 5 complete. Moving to Step 6 — write the technical spec.
Writing the technical spec now.
Technical spec written. Now Step 7 — write and build both models.
Both SQL files written. Now building the models.
Both models built successfully. Now Step 8 — dispatching both verifiers in parallel.
Database is accessible. Now dispatching both verifiers in parallel.
Both verifiers return all PASS. No fixes needed.
Base Claude Code
claude code, no additional stack
prod_posts_ghosts: FAIL — no pred column matched gold column 'post_text' prod_comments_ghosts: FAIL — row count mismatch gold=15359 pred=14884
37/37 tests passed. Here's a summary of what was built:
3 models created in models/prod/:
| Model | Description |
|---|---|
prod_posts_ghosts | Cleaned posts: null-filtered, deduplicated on post_id, columns renamed with post_ prefix, boolean nulls coalesced to false, post_text coalesced to '' for link posts |
prod_comments_ghosts | Cleaned comments: null-filtered, deduplicated on comment_id, columns renamed with comment_ prefix, boolean nulls coalesced to false |
prod_posts_comments_joined | INNER JOIN of the two cleaned tables on post_id = comments_post_id, combining all post metadata (score, upvote ratio, comment count, timestamps) with individual comment data |
Key cleaning decisions:
- Null filtering: Rows missing key identifiers (
post_id,comment_id,post_url, etc.) are dropped - Deduplication:
ROW_NUMBER() OVER (PARTITION BY post_id/comment_id ORDER BY created_at DESC)keeps the most recent version of any duplicate - Boolean coalescing:
distinguished,edited,locked, etc. default tofalserather than being dropped post_text: Coalesced to''(link-only posts have no self-text but are valid records)
All 37 not_null and unique data tests passed cleanly.
Let me read the existing YAML files to understand the exact source definitions before writing the models.
Now I have everything I need. Let me create the three model SQL files in models/prod/ and update schema.yaml to include the joined model.
Now add the joined model to schema.yaml:
Now run dbt to materialize all models:
All 3 models materialized. Let me run the tests to verify data quality: