Why a Warehouse Schema Alone Does Not Guarantee Consistent Business Metrics

Learning outcomes

1. AtlasMart’s problem: one warehouse, three incompatible dashboards

AtlasMart’s star schema is structurally correct. The fact has a declared order-line grain, customer/product/date dimensions are conformed, history has explicit rules, and Chapter 19 even adds a reconciled acceleration path. Yet Finance reports 820 USD revenue while a legacy dashboard reports 690; Growth says four active customers while another tile says six; and two teams publish 50% versus 25% retention. No table is corrupt. The disagreement lives in semantic choices that the schema alone cannot force.

A semantic layer is the governed analytical contract that turns physical facts and dimensions into named measures, metrics, relationships, filters, time semantics, units/currencies, security behavior, and consumer-facing definitions. A measure is aggregatable numeric or set-valued behavior such as SUM(amount_cents). A metric binds a measure to business semantics: filters, denominator, window, units, version, owner, and edge behavior. A BI contract says what consumers may request and what the returned number means.

2. Controlled failure: three legal SQL statements, three wrong business meanings

The local fixture deliberately runs plausible dashboard logic that is syntactically valid but semantically wrong. The revenue tile hides an end date; the “active customers” tile counts order lines instead of distinct customers; the retention tile divides by current-period customers instead of prior-period customers.

governed gross revenue:                         820.00 USDwrong dashboard with hidden end_date=2026-09-21:    690.00 USDgoverned active customers (Sep20..Sep22):              4wrong dashboard COUNT(*) mislabeled as customers:       6governed retention = retained / prior active:          50%wrong dashboard = retained / current active:            25%

The mechanism matters: SQL correctness is not metric correctness. A database cannot infer whether a hidden filter is authorized, which denominator the business means by retention, or whether a column should be counted distinctly.

3. The governed replacement: explicit contracts

AtlasMart names three versioned metrics. Revenue is explicitly gross, so returns are not silently subtracted. Active customer is a set cardinality over a closed UTC date window. Period retention is an intersection divided by the prior active population; when that denominator is empty, the metric returns NULL rather than inventing zero or 100%.

{  "gross_revenue_usd.v1": {    "measure": "sum(fact_sales.amount_cents)",    "filters": ["status = paid", "currency_code = USD"],    "time_dimension": "order_date",    "timezone": "UTC",    "format": "USD",    "owner": "finance-analytics"  },  "active_customers.v1": {    "measure": "count_distinct(fact_sales.customer_id)",    "filters": ["status = paid"],    "window": "inclusive [start_date, end_date]",    "owner": "growth-analytics"  },  "period_retention.v1": {    "numerator": "customers active in both prior and current periods",    "denominator": "customers active in prior period",    "zero_denominator": "NULL",    "owner": "growth-analytics"  }}

These definitions are not tied to a chart. A dashboard, notebook, scheduled report, API, or alert can all request the same named metric and receive the same compiled logic.

4. Physical schema versus semantic model

5. Observable compilation and golden result

The lab’s didactic compiler turns gross_revenue_usd.v1 into SQL. The source contract requires paid USD lines and an inclusive UTC date window. Running the full range yields exactly 820 USD, and the Chapter 19 aggregate yields the same result. That equality proves semantic compatibility for this fixture; it does not prove every arbitrary slice can use the aggregate.

SELECT SUM(f.amount_cents)/100.0 AS gross_revenue_usd FROM fact_sales f  WHERE f.status='paid' AND f.currency_code='USD' AND f.order_date BETWEEN :start_date AND :end_date

gross_revenue_usd.v1, all dates:                   820.00 USDactive_customers.v1, 2026-09-20..2026-09-22:        4period_retention.v1, Sep18-19 -> Sep20-22:           0.50 (50%)  prior active customers:                             2  current active customers:                           4  retained customers:                                 1gross_revenue_usd.v1, analyst_east:                 395.00 USDperiod_retention.v1, analyst_east:                    1.00 (100%)zero-prior-population retention:                      NULLatomic revenue vs Chapter 19 accelerator:             820.00 == 820.00

6. Boundary cases the semantic contract must decide

• Returns: this metric is gross revenue. A net-revenue definition must be a separately governed metric or a new version, not a dashboard subtraction hidden in a visual.
• Time: order_date is UTC calendar date. User-local date bucketing would be a different contract and can move boundary events.
• Currency: the lab blocks mixed USD/EUR input instead of adding incomparable cents. FX source, rate type, effective time, rounding, and reporting currency must be explicit before conversion is enabled.
• History: this chapter computes current paid-sales semantics over current facts; historical SCD attribution rules from earlier chapters still apply when a metric slices historical dimension versions.
• Freshness: a correct semantic definition can still return stale data if the selected physical route is stale. Semantic correctness and data freshness are separate acceptance axes.

7. Production judgment

Centralize only logic that is truly shared and governed. A semantic layer should reduce ambiguity, not become an opaque monolith. Expose metric definition, version, owner, source lineage, last certified data watermark, allowed dimensions, security context, and tests. Cache/acceleration can sit beneath it, but routing must reject stale or semantically incompatible objects.

Retries of metric compilation/evaluation should be read-only/idempotent; source/warehouse replay remains governed by Chapters 14–16. Observability should include metric name/version/hash, query route, principal/security scope, time window, result checksum where practical, and failed contract validations. Rollback means routing consumers back to a prior active metric version—not editing past semantics invisibly.

8. Bridge to central metric definitions

Lesson 1 establishes why a schema cannot encode every business meaning. Lesson 2 turns that insight into complete specifications for measures versus metrics, filters, windows, currency/units, ownership, and explicit edge behavior.

Knowledge check

Authoritative references

• Kimball Group — Conformed DimensionsBackground for shared dimensional meaning across analytical consumers and processes.
• Kimball Group — Fact TablesGrain-first fact-table guidance underlying safe metric aggregation.
• dbt — MetricFlow overviewCurrent official example of a semantic metric engine. It is a non-prerequisite product reference, not the course's semantic source of truth.
• dbt — Semantic LayerCurrent official example of centrally defined metrics consumed across tools; exact capabilities and syntax are product-specific.
• PostgreSQL — Row Security PoliciesOfficial engine-specific reference for row-level security. The mandatory local lab simulates entitlement filtering with joins instead of claiming SQLite has equivalent native RLS.
• SQLite — SELECTOfficial semantics for the deterministic local SQL examples.
• Python — sqlite3Standard-library interface used by the no-cost local lab.

9. Lab cleanup/reset

The mandatory examples use only the deterministic local Chapter 20 fixture. Delete atlasmart_ch20_lab (or your configured local output directory) and rerun python ch20_lab.py to recreate a clean state. The script recreates the SQLite database and metric/report files; it does not create cloud resources or modify the academy repository.