§1 The Four Discoveries That Reshaped The Plan

v1 of this plan was a brute-force port of v4's measure list into per-measure dbt models. Stakeholder review (and a cept consultation) surfaced four issues that compounded into "the wrong shape" — not a wrong implementation, but a wrong architecture. v2 restarts from the right shape.

§2 The Right Architecture (4 Layers)

The architecture below is what every major analytics platform converged on independently. We're catching up to industry, not inventing.

Concept-by-concept: how 22 measures collapse to ~7 metrics × dimensions

The data flow, end to end

§3 Industry Justification — Why This Is The Right Pattern

This isn't a team-invented pattern or a company-specific compromise. Every serious analytics platform converged on this independently because the alternatives don't scale.

Why every one of them did it (the forcing functions)

§4 dbt MetricFlow — The Implementation Standard We'll Use

dbt MetricFlow is the open-source implementation of this pattern, designed to live inside an existing dbt project. We're already using dbt; adding MetricFlow is a pip install dbt-metricflow + YAML files. No new infrastructure.

dbt build --select +metric:revenue +metric:disposal_cost ...
# materializes gold.metric_value with all combinations

How it works (data + control flow)

What our YAML will look like

One semantic_model file (the GL fact):

semantic_models:
  - name: gl_entry
    description: "GL line items with all dimensions resolved."
    model: ref('fct_gl_entry')

    entities:
      - name: entity        # region_a | region_b
        type: foreign
        expr: entity_id
      - name: lob           # roll_off | fec | residential | portable
        type: foreign
        expr: lob_id

    dimensions:
      - name: period
        type: time
        type_params:
          time_granularity: month
        expr: period
      - name: account_category
        type: categorical
      - name: is_capitalized
        type: categorical
      - name: is_revenue
        type: categorical

    measures:
      - name: signed_amount
        agg: sum
        expr: signed_amount
        agg_time_dimension: period

One metrics file (the 7 business metrics):

metrics:
  - name: revenue
    description: "Total revenue (4xxxx accounts), Acme-side. Negate for display."
    type: simple
    type_params:
      measure: signed_amount
    filter: "{{ Dimension('gl_entry__is_revenue') }} = true"
    # Multiply by -1 in the published view (revenue is stored as credits).

  - name: disposal_cost
    description: "Disposal cost (53xxx accounts)."
    type: simple
    type_params:
      measure: signed_amount
    filter: "{{ Dimension('gl_entry__account_category') }} = 'disposal'"

  - name: rm_cost
    description: "R&M (55xxx + 56xxx + 57xxx, EXCLUDING capitalized)."
    type: simple
    type_params:
      measure: signed_amount
    filter: |
      {{ Dimension('gl_entry__account_category') }} IN ('truck_rm', 'container_rm', 'portable_rm')
      AND {{ Dimension('gl_entry__is_capitalized') }} = false

  - name: total_costs_cogs_sga_minus_da
    description: "COGS + SG&A − D&A (5xxxx + 6xxxx, excluding D&A and Capitalized)."
    type: simple
    type_params:
      measure: signed_amount
    filter: |
      {{ Dimension('gl_entry__is_in_total_costs') }} = true

Then dbt build --select +metric:revenue compiles these into SQL, materializes the rows, and puts them in gold.metric_value.

The stakeholder bridge — how the controller's PDF rows map to metric × dim tuples

-- registry.metric_published (seed, then evolved as we ratify more)
-- One row per controller-published metric. The bridge from stakeholder language
-- to dimensional language.

published_id, published_label, source_doc, metric_id, entity_id, lob_id, ratified
"M001", "Consolidated Revenue Region A", "controller_2026_02_11.pdf", "revenue", "region_a", NULL, true
"M002", "Consolidated Revenue Region B", "controller_2026_02_11.pdf", "revenue", "region_b", NULL, true
"M003", "Consolidated Revenue Total", "controller_2026_02_11.pdf", "revenue", NULL, NULL, true
"M004", "Disposal Cost Roll Off Region A", "controller_2026_02_11.pdf", "disposal_cost", "region_a", "roll_off", true
"M005", "R&M Cost Region A", "controller_2026_02_11.pdf", "rm_cost", "region_a", NULL, true
... 17 more ratified, 73 unratified (ratified=false)

Dashboard query becomes trivial:

SELECT p.published_label, g.value
FROM registry.metric_published p
JOIN gold.metric_value g
  ON g.metric_id = p.metric_id
  AND COALESCE(g.entity_id, '') = COALESCE(p.entity_id, '')
  AND COALESCE(g.lob_id, '') = COALESCE(p.lob_id, '')
WHERE g.period = '2025-12'
  AND p.ratified = true
ORDER BY p.published_id;

§5 What Gets Reverted (Slices 6.2 + 6.3)

The SQL logic in slices 6.2 + 6.3 (filter expressions, exclusion handling, reassignment overrides) ports directly into MetricFlow filter expressions and silver dim flags. The structure they encode (per-measure tables, column-based exclusions, leading-digit join) is wrong and gets rebuilt.

§6 The Revised Slice Plan (v2)

§7 Definition of Done (v2)

The validation queries (post-deploy smoke test)

-- 1. Metric definitions are intact: 7 metrics
SELECT metric_id, COUNT(*) AS rows_materialized
FROM gold.metric_value
GROUP BY metric_id
ORDER BY metric_id;
-- expect 7 rows

-- 2. Published metrics are intact: 22 ratified rows
SELECT COUNT(*) FROM registry.metric_published WHERE ratified = true;
-- expect 22

-- 3. Every published metric has corresponding gold value for Dec 2025
SELECT p.published_label, g.value, p.ratified
FROM registry.metric_published p
LEFT JOIN gold.metric_value g
  ON g.metric_id = p.metric_id
  AND COALESCE(g.entity_id, '') = COALESCE(p.entity_id, '')
  AND COALESCE(g.lob_id, '') = COALESCE(p.lob_id, '')
WHERE g.period = '2025-12' AND p.ratified = true
ORDER BY p.published_id;
-- expect 22 rows, no NULL values

-- 4. Sentinel cents-precision check
SELECT g.value
FROM registry.metric_published p
JOIN gold.metric_value g USING (metric_id)
WHERE p.published_label = 'Consolidated Revenue Region B'
  AND g.entity_id = 'region_b' AND g.period = '2025-12';
-- expect 872850.23 (matches the controller's December PDF to the cent)

§8 What This Plan Explicitly Does NOT Do

§9 Risks Specific To MetricFlow Adoption

§10 v1 vs v2 — The Diff Summarized

§11 Governance Principle: Supabase Is Never The Semantic Source Of Truth

Locking this in now, before Phase 3 dashboard wire-up creates the temptation to define metrics in Supabase migrations.

The rule: if a future dashboard need wants a number that doesn't exist in gold.metric_value, the answer is "add a metric to MetricFlow YAML + republish," NOT "write a SQL view in Supabase" and NOT "compute it in the app's React code." Single source of truth for every business number; one path for adding new ones.

Why this matters: the moment Supabase has its own "revenue" calculation that drifts from gold.metric_value, stakeholders see two different numbers in two different views and trust collapses. Same Minerva lesson Airbnb learned the hard way.

§12 What Ships To Supabase, What Stays In The Warehouse

This is the operational picture. Supabase is the dashboard's read surface; the warehouse is the metric factory. Knowing exactly what crosses the boundary (and what does NOT) is the difference between clean architecture and accidental sprawl.

The ship/no-ship matrix

The two rules that keep this clean

Why this matters operationally

• Cost containment. Supabase priced by data volume. Mirror is small (≤2K rows for v2 scope). Bronze is large (~1.4M rows). The matrix prevents accidental million-row Supabase loads.
• Governance enforcement. §11 said "Supabase is never the semantic source of truth." This matrix is HOW that's enforced — by listing exactly what Supabase contains, anything else is by definition not supposed to be there.
• Sync surface stays narrow. Slice 6.11v2's sync script is responsible for 2 tables. Adding a third = explicit code change + governance review. No accidental sprawl.
• Dashboard predictability. Dashboard developers know exactly what tables exist in Supabase under mirror_gold. Schema is stable: gold.metric_value's columns don't drift because they're materialized by MetricFlow against versioned YAML.

§13 Decisions to confirm