Why SignalPilot passed where base failed
Evidence chips jump to the exact transcript momentThe task required two report tables. Claude Code built one, saw a reassuring "34 models passed" message, and finished. The second table never existed, and the one it built filtered on a status value that matches zero rows. SignalPilot verified the real values against the live data and its build passed.
Real-world impact
Support metrics for all nine companies simply did not exist, and the one table that did exist attributed conversations to the wrong teams and silently dropped closed conversations from its counts.
- Misread the scope as a one-table job, saw "34 models passed" (a count dominated by pre-built models), and stopped; the company metrics table was never built.
- The one table it did build filtered on the value "close" when the data says "closed", and filled the team column with admin ids.
Jump to the exact moment
- Queried the live data first: SELECT DISTINCT showed the status value is "closed", not the "close" the YML note suggested.
- Kept team ids as numbers from the team mapping table, so conversations attribute to teams, not admins. The graded checks passed.
Jump to the exact moment
The numbers
| Check | Correct answer | Base Claude Code | SignalPilot |
|---|---|---|---|
| Status filter | matches "closed" | "close" (matches 0 rows) | correct |
| Team attribution | team ids | admin ids as strings | correct team ids |
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.
SELECT DISTINCT on the live status column showed the value is "closed"; base Claude Code trusted a YML note saying "open or close" and shipped a filter that matches zero rows.
- 06Write the technical spec
A written plan of sources, joins, filters, and grain per model.
The spec pinned the verified "closed" filter and kept team ids as numbers from the team mapping table; base Claude Code cast team ids to strings and filled the column with admin ids.
- 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
All 8 steps complete. Here is a detailed explanation of the process:
How Admin-Specific (and Optionally Team-Specific) Metrics Are Calculated for Closed Conversations
1. Data Sources
The model draws from four upstream tables:
| Source (via dbt variable) | Purpose |
|---|---|
{{ var('admin') }} → stg_intercom__admin | Admin ID, name, job title |
{{ var('team_admin') }} → stg_intercom__team_admin | Maps each admin to their team(s) |
{{ var('team') }} → stg_intercom__team | Team names (only when intercom__using_team = True) |
{{ ref('intercom__conversation_metrics') }} | Pre-computed conversation-level metrics |
2. Grain: Team-Admin Pair
Because a single admin can belong to multiple teams, the model is built at the team-admin grain — one row per (admin_id, team_id) combination. The primary key is ['admin_id', 'team_id'] when teams are enabled, or just ['admin_id'] when they're not.
3. Conversation Filter: Closed Only
Only conversations with conversation_state = 'closed' are included in the metrics CTE. This follows the domain rule that open conversations (with no close timestamp) must never be included in resolution time calculations.
4. Attributing Conversations to Admins
Conversations are attributed to admins via last_close_by_admin_id — the ID of the admin who last closed each conversation (derived by the upstream intermediate model from conversation part events where part_type = 'close' and author_type = 'admin'). This is a LEFT JOIN, so admins who closed zero conversations still appear with total_conversations_closed = 0.
5. Metric Calculations
| YML Column | Aggregation Expression |
|---|---|
total_conversations_closed | COUNT(conversation_metrics.conversation_id) — counts attributable closed conversations (not COUNT(*), so unmatched admins get 0) |
average_conversation_parts | AVG(count_total_parts) — mean number of message parts per conversation |
average_conversation_rating | AVG(conversation_rating) — mean satisfaction rating (1–5 scale) |
median_conversations_reopened | MEDIAN(count_reopens) — median number of times conversations were reopened after close |
median_conversation_assignments | MEDIAN(count_assignments) — median assignment count per conversation |
median_time_to_first_response_time_minutes | MEDIAN(time_to_first_response_minutes) — pre-computed in intercom__conversation_metrics as seconds-difference / 60 |
median_time_to_last_close_minutes | MEDIAN(time_to_last_close_minutes) — same pre-computed field |
All time metrics are not recomputed from raw timestamps — they reference the already-calculated values in intercom__conversation_metrics, which computes them via datediff('second', conversation_created_at, first_admin_response_at) / 60 (and similar) to avoid rounding drift.
6. Optional Team Enrichment
The entire team join path is wrapped in a Jinja {% if var('intercom__using_team', True) %} conditional:
- When enabled (default):
stg_intercom__team_adminis joined onadmin_id, andstg_intercom__teamis then joined onteam_idto bring inteam_name. The model outputs one row per admin-team pair. - When disabled:
team_idandteam_nameoutput asNULL, and the model collapses to one row per admin.
7. Build Result
- 4 rows produced (one per team-admin pair in the test data)
- All structure checks passed: table exists, all 12 YML columns present, correct row count
- All value checks passed: aggregation functions verified (
COUNT(col),AVG,MEDIAN),conversation_state = 'closed'filter confirmed, join key (last_close_by_admin_id) confirmed correct
Now I have the scan. Let me create tasks and load supporting skills while validating the project.
Skills loaded. Now validating the project and running the blueprint research in parallel.
Validation passed. Now I need to materialize the upstream dependencies and run blueprint research.
No macros. Let me read the stubs and complete models simultaneously.
Now let me read the YML files and run the blueprint in parallel.
The upstream staging tables don't exist. I need to build them with + prefix.
All upstream models materialized. Now running the blueprint and exploring the database tables.
Now let me explore the key tables to understand the data structure.
Let me explore more about how conversations relate to admins in the data.
Let me check the staging model column names.
Now let me run map-columns and build the remaining staging models.
I have enough information to write the technical spec. Let me load the knowledge-base skill and proceed.
Now writing the SQL for intercom__admin_metrics:
Model built successfully. Now Step 8 — verify the output.
Both verifiers passed. Let me mark Step 8 complete.
Base Claude Code
claude code, no additional stack
intercom__company_metrics: FAIL — table not in result DB (have: ['admin_data', 'company_history_data', 'company_tag_history_data', 'contact_company_history_data', 'contact_history_data', 'contact_tag_history_data', 'conversation_contact_history_data', 'conversation_history_data', 'conversation_part_history_data', 'conversation_tag_history_data', 'int_intercom__conversation_part_aggregates', 'int_intercom__conversation_part_events', 'int_intercom__conversation_string_aggregates', 'intercom__admin_metrics', 'intercom__company_enhanced', 'intercom__contact_enhanced', 'intercom__conversation_enhanced', 'intercom__conversation_metrics', 'tag_data', 'team_admin_data', 'team_data']) intercom__admin_metrics: FAIL — no pred column matched gold column 'team_id'
All 34 models built successfully. Here's an explanation of the complete process implemented in intercom__admin_metrics.sql:
How intercom__admin_metrics calculates admin (and team) metrics
Step 1 — Filter to closed conversations
The model starts from intercom__conversation_metrics (the richest conversation-level table) and filters to only rows where conversation_state = 'close'. Open, snoozed, or in-progress conversations are excluded.
Step 2 — Identify the responsible admin
The column last_close_by_admin_id (populated by a window function in int_intercom__conversation_part_events) identifies the admin who performed the final close action on each conversation. Rows where this is null (e.g., bot-closed conversations) are excluded from aggregation.
Step 3 — Aggregate per admin
A CTE called admin_aggregates groups by last_close_by_admin_id and computes:
| Metric | Calculation |
|---|---|
total_conversations_closed | count(*) |
average_conversation_parts | avg(count_total_parts) — average number of message parts per conversation |
average_conversation_rating | avg(conversation_rating) — 1–5 scale, nulls excluded automatically |
median_conversations_reopened | percentile_cont(0.5) over count_reopens |
median_conversation_assignments | percentile_cont(0.5) over count_assignments |
median_time_to_first_response_time_minutes | percentile_cont(0.5) over time_to_first_response_minutes |
median_time_to_last_close_minutes | percentile_cont(0.5) over time_to_last_close_minutes |
Medians use the standard SQL percentile_cont(0.5) within group (order by ...) aggregate function, which is null-safe and supported by DuckDB.
Step 4 — Enrich with admin and team details
The final CTE:
- Starts from the
adminstaging table (all admins, even those with zero closed conversations, viaLEFT JOIN) - Joins
admin_aggregatesonadmin_idto attach the computed metrics - If
intercom__using_team = true(as configured): also joinsteam_admin(which maps one admin to potentially multiple teams) and thenteamfor team names — this expands the grain to one row per admin-team combination, so an admin on 2 teams produces 2 rows with identical metrics but differentteam_id/team_name.
Let me read the key files directly before writing any code.
Now let me read the remaining model files to understand the full data structures.
Let me look at the staging models in the dbt_packages directory to understand the available columns.
Now I have everything I need. The intercom__admin_metrics model is documented in intercom.yml but its SQL file is missing. Let me create it.
All 34 models passed successfully. Let me verify the output table.