Why SignalPilot passed where base failed
Evidence chips jump to the exact transcript momentAsked for ember_commerce's total enrollment count, Claude Code reported 167,779. The raw landing table holds 217,101: 23% of the rows had silently vanished below the mart. SignalPilot reconciled the count down to the raw data and recovered the full population.
Real-world impact
Capacity planning, billing, and experiment power calculations built on this count all start from a number 23% too low, and nothing in the pipeline flags it: the shrunken table has clean grain, clean uniqueness, and an authoritative name.
- Below the published mart, a ROW_NUMBER deduplication collapses genuinely distinct enrollments into one.
- Row counts and uniqueness checks still pass; only the total is wrong.
- Counted the authoritative-looking table and reported 167,779 with confidence; the raw landing table was never consulted.
- Started from the client's own staging and raw landing data instead of the published rollup, counting the population at every layer.
- Reported 217,101 from the source, matching the independently derived truth exactly.
The numbers
| Check | Correct answer | Base Claude Code | SignalPilot |
|---|---|---|---|
| Ember_commerce's total enrollment count | 217,101 | 167,779 (23% missing) | 217,101 |
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 counted distinct enrollment ids at every layer, caught a ROW_NUMBER dedup collapsing 217,101 staging rows to 167,779, and reconciled 217,101 against the raw landing blobs; base Claude Code cross-checked two marts that inherited the same collapse.
- 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 217,101 · matches truth
ember_commerce total enrollment count: 217,101
That's 217,101 subject-experiment assignments, all time — re-derived directly from the raw source (raw.client_blob where source_client = 'ember_commerce') at the true enrollment grain (count(distinct enrollment_id), latest revision per enrollment). It reconciles exactly with staging.stg_ember__enrollments (217,101 rows, one per enrollment_id).
⚠️ Do not trust the ember marts for this number — they're silently broken
Every ember mart reports 167,779, which is wrong. Here's why:
| Source | Value | What it actually is |
|---|---|---|
Raw source / stg_ember__enrollments | 217,101 | ✅ true enrollments (distinct enrollment_id) |
int_ember__enrollments | 167,779 | ❌ collapsed to one row per subject |
dim_ember__experiments.total_enrollments (sum) | 167,779 | ❌ inherits the bug |
fct_ember__experiment_results.enrollments (sum) | 167,779 | ❌ inherits the bug |
Root cause — models/intermediate/ember_commerce/int_ember__enrollments.sql lines 3-8:
select * from (
select *, row_number() over (partition by subject_key order by bucketed_at) as rn
from {{ ref('stg_ember__enrollments') }}
) t
where rn = 1
Partitioning by subject_key alone and keeping rn = 1 keeps only each subject's first enrollment and drops every other experiment that subject was assigned to. That's exactly why the marts land on 167,779 = the distinct subject count, not the enrollment count. Sibling models (int_drift__enrollments, int_northwind__enrollments, etc.) do not do this — they keep one row per enrollment_id, which is the correct grain. This dedup is unique to ember and is a bug.
The staging layer already handles the legitimate dedup (collapsing revisions to the latest per enrollment_id), so this intermediate dedup is both wrong and redundant.
For reference, the grain breakdown:
- 217,101 distinct enrollment records ← the answer
- 212,317 distinct
(subject, experiment)pairs (4,784 pairs were re-bucketed under a secondenrollment_id— genuinely separate assignment events, not revisions) - 167,779 distinct subjects (what the broken marts return)
Next step
This is a factual error in the dbt project that corrupts the ember enrollment/experiment marts feeding downstream dashboards. Would you like me to fix int_ember__enrollments.sql (drop the partition by subject_key dedup so it matches the sibling pattern) and rebuild the affected ember models? I'd want your go-ahead before modifying and re-materializing project models.
I'll start by loading the required workflow skill, as mandated by the project instructions.
The signalpilot MCP tools are needed. Let me load them and run the Step 1 scans in parallel with the knowledge base check.
Now let me load the MCP tool schemas and run the knowledge base check plus DB analysis.
Knowledge base is sparse. Let me explore the actual data — list tables and find the ember source for enrollments/assignments.
This is an experimentation platform. "ember_commerce" is the client for the ember source. Enrollment = one subject-experiment assignment = one enrollment_id. The stg_ember__enrollments table (217K rows) has a revision column and there's a stg_ember__revisions table plus fct_ember__resubmissions — a strong signal that enrollment_id fans out. Let me verify the grain before trusting any count.
Staging is one row per enrollment_id (217,101), all for client ember_commerce. But there's a subtlety: distinct (subject, experiment) pairs = 212,317, which is fewer than enrollment_ids (217,101). So ~4,784 subject-experiment pairs have multiple enrollment records. I need to understand how the project itself defines an "enrollment." Let me read the model SQL rather than guess.
Critical finding in int_ember__enrollments: it dedups with row_number() over (partition by subject_key order by bucketed_at) and keeps rn = 1 — one row per subject, which collapses subjects enrolled in multiple experiments down to a single enrollment. Let me compare against sibling models to confirm whether this is a bug and check what the marts inherit.
Confirmed the anomaly: sibling models (int_drift__enrollments, int_northwind__enrollments) keep one row per enrollment_id — they do not dedup by subject. Only int_ember__enrollments adds partition by subject_key ... where rn=1, which collapses every subject to a single enrollment and drops all enrollments for subjects who are in more than one experiment. That means the ember marts (dim_ember__experiments.total_enrollments, fct_ember__experiment_results, dim_ember__subjects) undercount.
Let me quantify the damage and re-derive the true count from the raw source. First let me check the j macro and the raw blob type.
Now let me re-derive the true enrollment count directly from the raw source (raw.client_blob), independent of the staging table, and simultaneously measure how badly the broken int_ember__enrollments undercounts.
Base Claude Code
claude code, no additional stack
reported 167,779 · truth 217,101 (23% of rows silently dropped)
Confirmed. Every row is a distinct subject-experiment assignment — enrollment_id, (subject_key, experiment_key), and (subject_key, canonical_experiment_key) all yield exactly 167,779, so there are no duplicate or mergeable assignments inflating the count.
Answer
ember_commerce has 167,779 total enrollments (subject-experiment assignments), all time.
Source: org.int_org__enrollments_canonical (the canonical cross-client enrollment model), filtered to client_id = 'ember_commerce'. The client's code name in the warehouse is ember.
One note in case it matters for your use: this is the total, including internal/test subjects (there's an is_internal_subject flag). If you want production-only enrollments excluding internal subjects, I can rerun with that filter — just say the word.
I'll explore the warehouse schema to find the right tables for enrollments and ember_commerce.
I'll look at the marts and other schemas to find enrollment-related tables.
The client uses code name "ember". Let me examine the enrollment tables and how clients are identified.
Both tables agree on 167,779 for ember_commerce, but the existence of a "canonical" table suggests deduplication logic. Let me verify whether that raw count actually represents unique subject-experiment assignments.