Why SignalPilot passed where base failed
Evidence chips jump to the exact transcript momentAsked for northwind_marketplace's all-time enrollments, Claude Code reported 101,411. The raw landing table holds 179,496: 44% 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 44% 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 hard-coded date filter (assigned_ts >= 2025-12-01) quietly truncates history.
- Row counts and uniqueness checks still pass; only the total is wrong.
- Counted the authoritative-looking table and reported 101,411 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 179,496 from the source, matching the independently derived truth exactly.
The numbers
| Check | Correct answer | Base Claude Code | SignalPilot |
|---|---|---|---|
| Northwind_marketplace's all-time enrollments | 179,496 | 101,411 (44% missing) | 179,496 |
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 read the SQL beneath the mart, found a hard-coded assigned_ts >= 2025-12-01 filter truncating history, and reconciled raw against staging to recover all 179,496; base Claude Code queried only the mart and reported 101,411.
- 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 179,496 · matches truth
Answer: 179,496 enrollments (all time)
northwind_marketplace has 179,496 subject-experiment assignments in the warehouse over its full history (assigned Sept 4, 2025 → June 29, 2026).
How I verified it (grain re-derived from source)
- Source of truth: each row in
raw.client_blobwheresource_client = 'northwind_marketplace'is one enrollment envelope. I counted directly from raw, not from a mart. - Grain check passed: 179,496 blob rows = 179,496 distinct
enrollment_id— perfect 1:1, no fan-out. This is also the platform's declared grain (tests/assert_northwind_enrollments_grain.sql). - Reconciliation:
staging.stg_northwind__enrollmentsmatches raw exactly (179,496 / 179,496).
⚠️ Important trap — don't use the marts for this
The "canonical" org/mart layer reports only 101,411 for northwind, because int_northwind__enrollments hard-codes where assigned_ts >= '2025-12-01'. That filter propagates into int_all__enrollments, fct_org_revenue_by_client, rpt_*, etc. Those are date-scoped, not all-time — so anyone pulling northwind enrollments from the marts would undercount by ~78K.
| Layer | Enrollments | Scope |
|---|---|---|
| Raw / staging | 179,496 | all time ✅ |
| int_all / org marts | 101,411 | assigned_ts ≥ 2025-12-01 only |
One nuance on "subject-experiment assignments"
If you mean distinct (subject, experiment) pairs rather than distinct enrollment records, the figure is 176,161 — there are 3,335 cases where a subject was assigned to the same experiment more than once (each got its own enrollment_id). The platform's canonical grain is the enrollment record (enrollment_id), so 179,496 is the enrollment count; 176,161 is only relevant if you want to collapse repeat assignments. Let me know which you need.
I'll start by loading the dbt-workflow skill, which owns the workflow for this project.
This is a business-outcome question. Per the workflow, I'll run the exploration steps. Let me load the SignalPilot tools and start Step 1 (project scan + DB hints + knowledge base check) in parallel.
The scan shows this is an experimentation platform with per-org models (drift, ember, ..., northwind, quanta) plus an int_all/int_org canonical layer. "northwind_marketplace" is the client; internal codename is northwind. This is a business-outcome question — no models to build. Let me run the knowledge probes and DB hints in parallel.
Knowledge base is empty. Going to data exploration. Let me read the northwind enrollment model lineage and list the raw tables.
Now let me read the northwind staging/int model SQL and the grain-assertion test, and check the client_id values plus grain. The raw source is raw.client_blob (a JSON blob per source_client).
Critical finding: int_northwind__enrollments filters where assigned_ts >= '2025-12-01', so the intermediate/mart/org layers are date-scoped, not all-time. The staging model stg_northwind__enrollments (179K rows) parses all enrollments from the raw blob with no date filter. Let me understand how staging distinguishes enrollment blobs from other record types, and look at the raw source.
One client_blob row for northwind = one enrollment envelope ($.assignment + $.subject, with nested $.exposures/$.events arrays). So staging enrollments is one row per blob. Let me read the j macro so I can query the raw source directly, then verify grain.
Now I'll verify grain directly from the raw source (raw.client_blob) and reconcile with the materialized staging table. This checks whether one blob = one unique enrollment (subject-experiment assignment) or if there's fan-out.
Clean reconciliation — staging exactly matches raw. Key facts:
- 179,496 blob rows = 179,496 distinct
enrollment_id→ perfect 1:1, no fan-out. Each enrollment envelope is one unique enrollment. - 176,161 distinct (subject, experiment) pairs → 3,335 enrollments are repeat assignments of a subject to an experiment they were already in.
- assigned_ts spans 2025-09-04 → 2026-06-29; only 101,411 fall on/after the
2025-12-01cutoff baked intoint_northwind__enrollments.
Let me confirm the downstream org/mart layer reports the date-scoped number (so I can flag the trap), and get the actual client_id value.
Confirmed. The client_id value is literally northwind_marketplace, and the org/mart layer reports the date-scoped 101,411 — not all-time.
Let me record this non-obvious project gotcha to memory, then answer.
Base Claude Code
claude code, no additional stack
reported 101,411 · truth 179,496 (44% of rows silently dropped)
Answer: 101,411 enrollments (all time)
That's the row count of the enrollments model (org.int_all__enrollments) for client_id = 'northwind_marketplace', and it's clean at the enrollment grain — 101,411 rows, 101,411 distinct enrollment_ids, no fan-out.
One nuance worth flagging
If by "subject-experiment assignment" you mean a unique (subject, experiment) pair, the number is slightly lower: 99,798. The gap is because some subjects were enrolled into the same experiment more than once:
- 1,584 subject-experiment pairs appear more than once (1,613 "extra" rows).
- Of those, 867 pairs got assigned different variants across enrollments (e.g.
nwu001396oncart_upsell_widgetwasvariant_2in January, thencontrolin May) — a possible assignment-stability/data-quality concern, since a subject flipping variants can bias experiment results. - The rest are same-subject/same-experiment/same-variant re-enrollments at a later timestamp.
So:
| Definition | Count |
|---|---|
Enrollment records (distinct enrollment_id) | 101,411 |
| Distinct subject-experiment pairs | 99,798 |
The headline number is 101,411. Let me know if you'd rather count distinct pairs, or if you want me to dig into those 867 variant-flip cases.
I'll explore the warehouse schema to find enrollment data for northwind_marketplace.
Interesting — there's a codename structure. Let me look at the enrollments tables and how clients map to codenames.
A direct match. Let me sanity-check for duplicate enrollment IDs and compare against the canonical table, since a naive count(*) can mislead if the model has fan-out.
The enrollment_id is unique (101,411), but there are 1,613 rows where the same subject+experiment pair appears more than once. Let me investigate whether those are true re-assignments or data-quality duplicates.