# Semantic Model Specification: [Model name]

> Specification of one governed semantic model (metrics layer): the
> datasets and entities it covers, relationships and join keys,
> dimensions and time grains, measures and aggregations, metrics by
> type, default filters, access rules, owners, certification, tests,
> change control and export to an open interchange format. Concepts are
> aligned with dbt MetricFlow (Apache License 2.0, release v0.213.0 of
> 2026-09-10) and kept tool-neutral; Apache Ossie (incubating), formerly
> Open Semantic Interchange, is named as an emerging draft interchange
> format. Replace every `[bracketed]` value and every row marked
> *Example*.

<!--
Guidance: How to use this template
- One specification per semantic model (usually one subject area, such
  as Orders or Checkout). A model is the shared set of joins, dimensions
  and metrics that every BI tool, notebook and AI assistant should query
  instead of writing its own SQL.
- Boundaries with neighbouring documents:
  - Metric Definition (`data/metric-definition`) defines one metric in
    business terms, with its formula, caveats and targets. This
    specification implements many metrics and points to their
    definitions; it never restates the business argument.
  - Analytics and BI Solution Design
    (`architecture/analytics-bi-solution-design`) designs the whole BI
    solution: sources, dimensional model, platform, distribution.
  - Dashboard Specification (`data/dashboard-specification`) specifies
    one dashboard that consumes this model.
  - dbt Project Standard (`data/dbt-project-standard`) sets the build
    conventions of the marts this model reads.
- Fill sections 3 (datasets and entities) and 4 (relationships) first.
  Most wrong numbers come from joins, not from formulas.
- The worked example is "Corvid Commerce", a fictional SaaS retailer,
  with an "Orders" semantic model on lakehouse marts.
-->

## Document control

<!-- Guidance: Identify the model, the engine that serves it and the business owner who certifies its metrics. Changing a certified metric is a version change of this document. -->

| Field | Value |
|---|---|
| Document ID | [SEM-ORD-001] *(Example)* |
| Semantic model name | [orders] |
| Subject area | [Orders and revenue] |
| Semantic engine and version | [MetricFlow vX.Y / BI tool semantic layer / other] |
| Source project | [corvid_analytics, marts/orders] *(Example)* |
| Version | [1.0] |
| Status | [Draft / In review / Certified / Deprecated] |
| Business owner | [@head-of-commerce-analytics] |
| Technical owner | [@analytics-engineer] |
| Owner | [@business-owner] |
| Approvers/reviewers | [@data-governance-lead], [@finance-controller], [@security-reviewer] |
| Classification | [Internal / Confidential] |
| Contains personal data | [Yes, pseudonymous customer key / No] |
| Last updated | YYYY-MM-DD |
| Next certification review | YYYY-MM-DD |

### Approvals

| Role | Name | Decision | Date |
|---|---|---|---|
| Business owner | [@name] | [Certified / Changes requested] | YYYY-MM-DD |
| Data governance lead | [@name] | [Approved / Changes requested] | YYYY-MM-DD |
| Finance controller (revenue metrics) | [@name] | [Approved / Changes requested] | YYYY-MM-DD |

### Revision history

| Version | Date | Author | Change |
|---|---|---|---|
| 0.1 | YYYY-MM-DD | [@author] | First draft |
| 1.0 | YYYY-MM-DD | [@author] | Certified for production use |

## Contents

- [1. Purpose and scope](#1-purpose-and-scope)
- [2. Model overview](#2-model-overview)
- [3. Datasets and entities](#3-datasets-and-entities)
- [4. Relationships and join keys](#4-relationships-and-join-keys)
- [5. Dimensions and time grains](#5-dimensions-and-time-grains)
- [6. Measures and aggregations](#6-measures-and-aggregations)
- [7. Metrics](#7-metrics)
- [8. Filters and default constraints](#8-filters-and-default-constraints)
- [9. Access rules](#9-access-rules)
- [10. Ownership and certification](#10-ownership-and-certification)
- [11. Tests and validation](#11-tests-and-validation)
- [12. Change control and versioning](#12-change-control-and-versioning)
- [13. Consumers and interchange](#13-consumers-and-interchange)
- [14. Traceability matrix](#14-traceability-matrix)
- [15. Glossary and references](#15-glossary-and-references)

## 1. Purpose and scope

<!-- Guidance: Say which business questions this model answers and which it deliberately does not. Questions: who queries it (people, tools, AI assistants), and what must they stop doing once it exists? Pitfall: a model that tries to cover every subject area and becomes impossible to certify. -->

The [Orders] semantic model gives every consumer one governed way to
ask questions about orders, revenue, customers and products at Corvid
Commerce *(Example)*. Once certified, dashboards and notebooks must
query revenue and order metrics through this model rather than writing
their own SQL against the marts.

| In scope *(Example)* | Out of scope *(Example)* |
|---|---|
| Orders, order lines, refunds, gross and net revenue | Checkout funnel events (Checkout model, SEM-CHK-001) |
| Customers (pseudonymous key, segment, region) | Marketing attribution and spend |
| Products and categories | Inventory and supply |
| Daily, weekly, monthly and fiscal time | Intraday operational monitoring |

Questions the model must answer *(Example)*:

1. What were net revenue and order count by region and channel last
   fiscal month, compared with the same month last year?
2. What is average order value by customer segment over a rolling
   90 days?
3. What share of orders were refunded within 30 days of purchase?

## 2. Model overview

<!-- Guidance: Show the datasets, their primary entities and the join paths in one diagram. A reader should see at once which joins are allowed. Pitfall: a diagram that shows physical tables but not the entity each one represents. -->

```mermaid
flowchart LR
  ORD[fct_orders<br/>primary entity: order] -- customer --> CUS[dim_customers<br/>primary entity: customer]
  LIN[fct_order_lines<br/>primary entity: order_line] -- order --> ORD
  LIN -- product --> PRD[dim_products<br/>primary entity: product]
  REF[fct_refunds<br/>primary entity: refund] -- order --> ORD
  ORD -- metric_time --> CAL[dim_calendar<br/>primary entity: date_day]
```

| Element | Count *(Example)* |
|---|---|
| Datasets | 6 (section 3) |
| Entities | 6 (order, order_line, customer, product, refund, date_day) |
| Dimensions | 7 (section 5) |
| Measures or simple aggregations | 6 (section 6) |
| Metrics (all types) | 10 (section 7) |
| Certified metrics | 7 |

## 3. Datasets and entities

<!-- Guidance: List each dataset (model, table or view) the semantic model reads, its grain and its entities. Questions: is each dataset a public, contracted mart, and is its grain stable? Pitfall: pointing the model at staging or intermediate tables whose shape can change without notice. -->

Every dataset must be a public mart with an enforced contract (see rule
DBT-051 in dbt Project Standard, `data/dbt-project-standard`), or a
table covered by a Data Contract (`data/data-contract`).

| Dataset ID | Physical relation | Grain | Contract | Default time dimension |
|---|---|---|---|---|
| DS-01 *(Example)* | `marts.orders.fct_orders` (v2) | One row per confirmed order | Enforced dbt model contract; built from `lakehouse.sales.orders`, which is covered by Data Contract DC-ORD-001 | `ordered_at` |
| DS-02 *(Example)* | `marts.orders.fct_order_lines` | One row per order line | Enforced dbt model contract | `ordered_at` |
| DS-03 *(Example)* | `marts.orders.fct_refunds` | One row per refund | Enforced dbt model contract | `refunded_at` |
| DS-04 *(Example)* | `marts.orders.dim_customers` | One row per customer (current state) | Enforced dbt model contract | None |
| DS-05 *(Example)* | `marts.orders.dim_products` | One row per product (current state) | Enforced dbt model contract | None |
| DS-06 *(Example)* | `marts.core.dim_calendar` | One row per day, with fiscal periods | Enforced dbt model contract | `date_day` |

Column lineage to the Orders data contract *(Example)*: `fct_orders`
keeps `order_id`, `customer_id`, `channel`, `net_amount`, `currency` and
`ship_country` with the contract names. The contract's `order_ts` is
renamed `ordered_at` in staging under rule DBT-013 of dbt Project
Standard; the meaning (time the order was confirmed, UTC) is unchanged.
The deprecated `region_code` is not used; the region is derived from
`ship_country` (DIM-04).

Entities are the keys that identify business objects and allow joins.
Use the four entity roles below; most semantic engines distinguish them
in the same way.

| Role | Meaning | Join behaviour |
|---|---|---|
| Primary | Exactly one row per value, and every value of the entity is present | Safe target of a many-to-one join |
| Unique | At most one row per value, but the dataset may hold a subset | Safe target, but some keys may not match |
| Foreign | Zero, one or many rows per value | Source side of a join; never a target |
| Natural | A business key, or combination, that identifies a real-world record and may change over time | Used for slowly changing dimensions with validity periods |

| Entity ID | Entity | Dataset | Column or expression | Role |
|---|---|---|---|---|
| ENT-01 *(Example)* | order | DS-01 | `order_id` | Primary |
| ENT-02 *(Example)* | customer | DS-01 | `customer_id` | Foreign |
| ENT-03 *(Example)* | customer | DS-04 | `customer_id` | Primary |
| ENT-04 *(Example)* | order | DS-02 | `order_id` | Foreign |
| ENT-05 *(Example)* | product | DS-02 | `product_id` | Foreign |
| ENT-06 *(Example)* | product | DS-05 | `product_id` | Primary |
| ENT-07 *(Example)* | order_line | DS-02 | `order_line_id` | Primary |
| ENT-08 *(Example)* | refund | DS-03 | `refund_id` | Primary |
| ENT-09 *(Example)* | order | DS-03 | `order_id` | Foreign |
| ENT-10 *(Example)* | date_day | DS-06 | `date_day` | Primary |
| [ENT-NN] | [entity] | [DS-NN] | [column] | [Role] |

`customer_id` is a pseudonymous key (classified Confidential with the
`PD` and `PSEUDO` flags in the Orders data contract); the semantic model
never exposes names, e-mail addresses or other direct identifiers, and
does not offer `customer_id` as a dimension.

## 4. Relationships and join keys

<!-- Guidance: State every allowed join with its cardinality and the key. Questions: can any join multiply rows (fan-out), and what happens to rows without a match? Pitfall: a many-to-many join that silently double counts revenue. -->

| Rel ID | From (dataset.entity) | To (dataset.entity) | Cardinality | Join type | Unmatched rows *(Example)* |
|---|---|---|---|---|---|
| REL-01 *(Example)* | DS-01.customer | DS-04.customer | Many to one | Left | Shown as "Unknown customer"; test alerts above 0.5 percent |
| REL-02 *(Example)* | DS-02.order | DS-01.order | Many to one | Left | Must be zero; test fails on any orphan line |
| REL-03 *(Example)* | DS-02.product | DS-05.product | Many to one | Left | Shown as "Unknown product" |
| REL-04 *(Example)* | DS-03.order | DS-01.order | Many to one | Left | Must be zero |
| [REL-NN] | [from] | [to] | [Cardinality] | [Join type] | [Handling] |

Rules:

- **SEM-001** Allow only many-to-one and one-to-one joins toward a
  primary or unique entity. A many-to-many path must be resolved by a
  bridge dataset with its own grain, documented here.
- **SEM-002** Limit join depth to [2] hops *(Example)*; beyond that,
  build a mart.
- **SEM-003** State the join type explicitly; never rely on an engine
  default.
- **SEM-004** For dimensions that keep history, state whether metrics
  use the value at event time or the current value. Default
  *(Example)*: the value at event time for region, the current value for
  customer segment.

## 5. Dimensions and time grains

<!-- Guidance: List every dimension consumers may group or filter by, with its type, allowed values and owner. Questions: which fiscal calendar applies, which time zone defines a "day"? Pitfall: one dashboard using UTC days and another using local days for the same metric. -->

Dimensions are either categorical (labels to group and filter by) or
time (dates and timestamps that roll up to coarser grains).

| Dim ID | Dimension | Dataset | Type | Allowed values or grain | Description *(Example)* |
|---|---|---|---|---|---|
| DIM-01 *(Example)* | `order__ordered_at` | DS-01 | Time | Day, week, month, quarter, year | Time the order was confirmed, UTC (contract `order_ts`) |
| DIM-02 *(Example)* | `order__channel` | DS-01 | Categorical | web, app, marketplace (contract enumeration); null before 2022 shown as "Unknown" | Channel that captured the order |
| DIM-03 *(Example)* | `order__ship_country` | DS-01 | Categorical | ISO 3166-1 alpha-2 codes | Country of delivery at order time |
| DIM-04 *(Example)* | `order__ship_region` | DS-01 | Categorical | EMEA, AMER, APAC | Derived from `ship_country` through the seed `seed_country_region`; replaces the deprecated `region_code` |
| DIM-05 *(Example)* | `customer__segment` | DS-04 | Categorical | new, returning, vip | Current segment, recalculated daily |
| DIM-06 *(Example)* | `product__category` | DS-05 | Categorical | Category tree level 1 | Current category |
| DIM-07 *(Example)* | `refund__refunded_at` | DS-03 | Time | Day and coarser | Time the refund was issued, UTC |
| [DIM-NN] | [name] | [DS-NN] | [Type] | [Values] | [Description] |

Time rules:

- **SEM-010** Define one metric time per measure (the aggregation time
  dimension). Consumers asking for "revenue by month" get months of that
  time dimension unless they choose another.
- **SEM-011** Store timestamps in UTC; derive local or fiscal periods
  through a calendar dataset, not through per-query conversions.
- **SEM-012** Declare the smallest grain each time dimension supports.
  Consumers may roll up to coarser grains only.
- **SEM-013** Define the fiscal calendar once *(Example: fiscal year
  starts 1 February, weeks start Monday, ISO week numbering)* and expose
  fiscal grains as separate dimensions.

## 6. Measures and aggregations

<!-- Guidance: List the base aggregations that metrics are built from. Questions: is each measure additive across every dimension, and which aggregation is used? Pitfall: summing a value that is only additive across some dimensions, such as a balance or a distinct count. -->

Measures are the base aggregations of a column or expression. Some
engines declare them separately; from dbt v1.12 the dbt semantic layer
expresses them as simple metrics (`type: simple` with `agg` and `expr`)
instead of a separate measures list. Either way, list them here, because
every other metric type is built from them. On an engine without
separate measures, each row below becomes a simple metric, usually
hidden from consumers when it is only an input.

| Measure ID | Name | Dataset | Expression | Aggregation | Additivity | Agg time dimension |
|---|---|---|---|---|---|---|
| MEA-01 *(Example)* | `order_count` | DS-01 | `order_id` | Count distinct | Additive, because each order has one order date and one value per dimension | `ordered_at` |
| MEA-02 *(Example)* | `gross_revenue_eur` | DS-02 | `quantity * unit_price_eur` | Sum | Fully additive | `ordered_at` |
| MEA-03 *(Example)* | `discount_eur` | DS-02 | `discount_eur` | Sum | Fully additive | `ordered_at` |
| MEA-04 *(Example)* | `refund_amount_eur` | DS-03 | `refund_amount_eur` | Sum | Fully additive | `refunded_at` |
| MEA-05 *(Example)* | `customer_count` | DS-01 | `customer_id` | Count distinct | Non-additive | `ordered_at` |
| MEA-06 *(Example)* | `net_amount_eur` | DS-01 | `net_amount` converted to EUR | Sum | Fully additive | `ordered_at` |
| [MEA-NN] | [name] | [DS-NN] | [expression] | [Sum / Count / Count distinct / Min / Max / Average / Median / Percentile] | [Additivity] | [time dimension] |

- **SEM-020** Every monetary measure states its currency in the name and
  the conversion rule in the description *(Example: converted to EUR at
  the daily reference rate of the order date)*.
- **SEM-021** Mark non-additive and semi-additive measures; dashboards
  must not total them across the non-additive dimension.
- **SEM-022** Percentile and median measures state whether they are
  exact or approximate on the engine in use.

## 7. Metrics

<!-- Guidance: List every metric the model serves, its type, its inputs and the Metric Definition that owns its meaning. Questions: is the metric certified, and who approves changes? Pitfall: two metrics with the same label and different logic. -->

Metric types follow the five types used by MetricFlow; most semantic
layers support equivalents.

| Type | Built from | Typical use |
|---|---|---|
| Simple | One measure, optionally filtered | Counts and sums |
| Ratio | A numerator metric divided by a denominator metric | Rates and averages that must be computed after aggregation |
| Cumulative | One measure over a running or trailing window, or grain to date | Rolling 90-day customers, month to date revenue |
| Derived | An expression over other metrics, optionally with time offsets | Growth versus last year, revenue after refunds |
| Conversion | Entities that perform a base event and then a conversion event within a window | Refund within 30 days, repeat purchase |

| Metric ID | Name and label | Type | Inputs | Metric definition | Certified | Owner *(Example)* |
|---|---|---|---|---|---|---|
| MET-ORD-001 *(Example)* | `orders`, "Orders" | Simple | MEA-01 | MET-ORD-001 doc | Yes | [@commerce-analytics] |
| MET-REV-001 *(Example)* | `net_revenue`, "Net revenue" | Simple | MEA-06 (contract measure `total_net_amount`) | MET-REV-001 doc | Yes | [@finance-analytics] |
| MET-REV-002 *(Example)* | `gross_revenue`, "Gross revenue" | Simple | MEA-02 | MET-REV-002 doc | Yes | [@finance-analytics] |
| MET-REV-003 *(Example)* | `average_order_value`, "Average order value" | Ratio | `net_revenue / orders` | MET-REV-003 doc | Yes | [@commerce-analytics] |
| MET-REV-004 *(Example)* | `net_revenue_yoy_growth`, "Net revenue growth vs last year" | Derived | `net_revenue` and `net_revenue` offset by 1 year | MET-REV-004 doc | Yes | [@finance-analytics] |
| MET-REV-005 *(Example)* | `discounts`, "Discounts" | Simple | MEA-03 | Used by TST-07 | No (input only, hidden) | [@finance-analytics] |
| MET-REV-006 *(Example)* | `refunds`, "Refunds" | Simple | MEA-04 | MET-REV-006 doc | Yes | [@finance-analytics] |
| MET-CUS-002 *(Example)* | `customers`, "Customers" | Simple | MEA-05 | Input to MET-CUS-001 | No (input only, hidden) | [@crm-analytics] |
| MET-CUS-001 *(Example)* | `active_customers_90d`, "Active customers (90 days)" | Cumulative | Simple metric MET-CUS-002 (`customers`, over MEA-05), trailing 90 days | MET-CUS-001 doc | Yes | [@crm-analytics] |
| MET-ORD-002 *(Example)* | `refund_rate_30d`, "Refund within 30 days" | Conversion | Base: order; conversion: refund; window 30 days; entity: order | MET-ORD-002 doc | No (in review) | [@commerce-analytics] |
| [MET-XXX-NNN] | [name, label] | [Type] | [Inputs] | [Link] | [Yes / No] | [@owner] |

Net revenue (MET-REV-001) is the governed metric that the Orders data
contract names for its measure `total_net_amount`: the sum of
`net_amount` (order value after discounts, before tax and shipping) for
confirmed orders that are not cancelled. This model implements it as a
simple metric over MEA-06, converted to EUR with the SEM-020 rule, so a
question about "net revenue" gets the same answer from the contract's
verified statement, from SQL on the mart and from the semantic layer.
Refunds are reported separately (MET-REV-006) and are not deducted from
net revenue. Gross revenue less discounts at line grain must equal net
revenue at order grain (TST-07). Derived and ratio metrics take metrics
as inputs, never measures; that is why discounts and customers appear as
hidden simple metrics.

Rules:

- **SEM-030** Every certified metric has exactly one Metric Definition
  (`data/metric-definition`) with the same ID. The definition owns the
  business meaning; this specification owns the implementation.
- **SEM-031** Compute ratios from aggregated numerator and denominator,
  never by averaging row-level ratios.
- **SEM-032** Labels are unique across all certified models.
- **SEM-033** Derived metrics with time offsets state how missing prior
  periods are shown *(Example: null, never zero)*.

Engine-neutral sketch of one ratio metric *(Example)*:

```yaml
metric:
  id: MET-REV-003
  name: average_order_value
  label: Average order value
  type: ratio
  numerator: net_revenue
  denominator: orders
  unit: EUR
  certified: true
  owner: "@commerce-analytics"
  definition: data/metric-definition MET-REV-003
```

Translate the sketch into the syntax of your engine; property names
differ between engines and releases.

## 8. Filters and default constraints

<!-- Guidance: Make every filter that changes a number visible and named. Questions: which records are excluded from all metrics, and which filters are metric specific? Pitfall: a filter hidden in one dashboard that makes it disagree with every other report. -->

| Filter ID | Applies to | Condition (plain words) | Reason *(Example)* |
|---|---|---|---|
| FLT-01 *(Example)* | All order metrics | Exclude test orders created by internal accounts | Test traffic in production |
| FLT-02 *(Example)* | All order metrics | Exclude orders with status `cancelled` (cancelled before shipment) | Not a sale; same rule as the Orders data contract |
| FLT-03 *(Example)* | MET-REV-001, MET-REV-002, MET-REV-005 | Include only confirmed orders (the grain of the Orders data contract) | Revenue counted at order confirmation, as in the contract's verified statement |
| FLT-04 *(Example)* | MET-CUS-001 | Exclude customers who requested erasure | Privacy rights; aggregate history stays, the key is removed |
| [FLT-NN] | [metrics] | [condition] | [reason] |

- **SEM-040** Model-wide filters are applied in the marts, not in the
  semantic layer, so that SQL users get the same rows. Metric-specific
  filters live on the metric.
- **SEM-041** Every filter appears in the Metric Definition of each
  affected metric (section "Filters and exclusions" of that template).

## 9. Access rules

<!-- Guidance: State who may query which metrics, dimensions and rows, and where the rule is enforced. Questions: does the rule follow the user into every tool and export? Pitfall: row-level rules defined in one BI tool only, bypassed by notebooks and APIs. -->

| Rule ID | Subject (role or group) | Scope | Rule | Enforced in *(Example)* |
|---|---|---|---|---|
| ACC-01 *(Example)* | All employees | Metrics MET-ORD-001, MET-REV-003 | Read, all regions | Semantic layer role |
| ACC-02 *(Example)* | Regional managers | All metrics | Rows limited to their own `order__ship_region` | Row access policy on marts |
| ACC-03 *(Example)* | Finance and the executive team | MET-REV-001 to MET-REV-006 | Read, all regions, all grains | Semantic layer role |
| ACC-04 *(Example)* | External partners | None | No access to this model | Not granted |
| ACC-05 *(Example)* | AI assistants and service accounts | Certified metrics only | Read with the calling user's identity, never a shared superuser | Semantic layer API token per user |
| [ACC-NN] | [role] | [scope] | [rule] | [layer] |

- **SEM-050** Enforce row-level rules in the data platform (row access
  policies) or in a semantic layer that every consumer must use; record
  where in this table.
- **SEM-051** Apply a minimum group size of [10] customers *(Example)* to
  any metric broken down by customer attributes, to limit
  re-identification from small cells.
- **SEM-052** Identity and role mapping follow IAM Solution Design
  (`security/iam-solution-design`).

## 10. Ownership and certification

<!-- Guidance: Say who owns what and what "certified" promises. Questions: what is checked before certification, and how long does it last? Pitfall: certification badges that are never reviewed and outlive the logic they certified. -->

| Role | Responsibility | Person |
|---|---|---|
| Business owner | Approves meaning and certifies metrics | [@business-owner] |
| Technical owner | Implements, tests and deploys the model | [@analytics-engineer] |
| Metric owners | Own individual Metric Definitions | See section 7 |
| Data steward | Maintains descriptions, glossary links and classification | [@data-steward] |

Certification criteria (all required):

1. The metric has an approved Metric Definition with the same ID.
2. Every input dataset is a contracted public mart.
3. Tests in section 11 pass for [14] consecutive days *(Example)*.
4. The value reconciles with the agreed reference (section 11, TST-04).
5. Access rules in section 9 are implemented and tested.
6. Owners are named and reachable.

Certification lasts [12 months] *(Example)* or until the logic changes,
whichever comes first. Decertify a metric when its owner leaves without
a successor or when reconciliation fails twice in a row.

## 11. Tests and validation

<!-- Guidance: List the tests that prove the model returns correct numbers, not only that it compiles. Pitfall: testing the marts thoroughly but never testing the joins and metrics the semantic layer generates. -->

| Test ID | What it checks | Method | Frequency | Failure action *(Example)* |
|---|---|---|---|---|
| TST-01 *(Example)* | Model definitions parse and validate against the warehouse | Semantic engine validation in CI | Every pull request | Block merge |
| TST-02 *(Example)* | Entity keys are unique where declared primary | Data tests on marts | Every build | Block deploy |
| TST-03 *(Example)* | No orphan rows on REL-02 and REL-04 | Relationship tests | Every build | Block deploy |
| TST-04 *(Example)* | Net revenue by month equals finance ledger total within 0.5 percent | Reconciliation query | Daily | Alert finance analytics; decertify after two failures |
| TST-05 *(Example)* | Saved queries for top 10 dashboard questions return expected values on a fixed fixture | Regression snapshot | Every pull request | Block merge |
| TST-06 *(Example)* | Row access policy returns only own region for a test regional manager | Access test with test identity | Weekly and on change | Block deploy |
| TST-07 *(Example)* | `gross_revenue - discounts` per day equals `net_revenue` (the contract measure `total_net_amount`, converted to EUR with the SEM-020 rule) within 0.1 percent (same tolerance as DQ-ORD-004) | Reconciliation query | Daily | Alert finance analytics; block certification |
| [TST-NN] | [check] | [method] | [frequency] | [action] |

Monitor data quality thresholds from Data Quality Standard
(`data/data-quality-standard`); this section adds the tests that are
specific to the semantic layer.

## 12. Change control and versioning

<!-- Guidance: Classify changes and say who approves each class. Questions: how much notice do consumers get, and how is a renamed metric handled? Pitfall: renaming a metric and breaking every saved dashboard overnight. -->

| Change class | Examples | Approval | Notice to consumers *(Example)* |
|---|---|---|---|
| Additive | New dimension, new uncertified metric | Technical owner | Release note |
| Corrective | Bug fix that changes a certified number | Business owner and metric owner | Announcement before deploy, with old and new values |
| Breaking | Rename or remove a metric or dimension, change a grain or a join | Business owner and data governance lead | [30 days] with a deprecation period |
| Definition change | New meaning of a certified metric | Metric owner, business owner, finance for revenue | New Metric Definition version, [30 days] notice |

- **SEM-060** Keep a deprecated metric available, labelled as
  deprecated, for the notice period, and point it to its replacement.
- **SEM-061** Version this document with every certified change and
  list affected metrics in the revision history.
- **SEM-062** Record lineage from metrics to marts to sources in the
  data catalogue so impact analysis is possible before a change.

## 13. Consumers and interchange

<!-- Guidance: List who and what queries the model and how the model can be exported. Questions: can another tool read this definition without retyping it? Pitfall: copying metric logic into each BI tool by hand, which recreates the drift the model was meant to remove. -->

| Consumer ID | Consumer | Access path | Notes *(Example)* |
|---|---|---|---|
| CON-01 *(Example)* | Executive Sales Dashboard (DSH-001) | BI tool connector to the semantic layer | Specified in Dashboard Specification (`data/dashboard-specification`) |
| CON-02 *(Example)* | Finance notebooks | Semantic layer API or SQL interface | Certified metrics only |
| CON-03 *(Example)* | Internal AI analytics assistant | Semantic layer API with user identity | ACC-05 applies; answers cite metric IDs |
| CON-04 *(Example)* | Data product "Orders" | Exports of saved queries to tables | Described in Data Product Specification (`data/data-product-specification`) |
| [CON-NN] | [consumer] | [path] | [notes] |

Interchange:

- Keep the model as code in version control, in the native format of
  the semantic engine, as the source of truth.
- Where consumers use another tool, export rather than retype. Apache
  Ossie (incubating), formerly Open Semantic Interchange, is an emerging
  vendor-neutral specification for exchanging semantic models (datasets,
  relationships, fields and metrics) between analytics, BI and AI tools.
  As of October 2026 its core specification is a draft (0.2.0.dev0)
  with no formal release, and dbt can already read Ossie documents as
  an alternative to its native semantic model YAML. Check its maturity
  at https://github.com/apache/ossie before relying on it.
- Record the export format, version and the tools tested:

| Format | Version | Exported by | Tested with | Status *(Example)* |
|---|---|---|---|---|
| Native engine definitions | [engine version] | CI | Semantic engine | Source of truth |
| [Open interchange format] | [version] | [export job] | [tools] | Pilot |

## 14. Traceability matrix

<!-- Guidance: Link each business question to metrics, definitions, datasets, tests and consumers, so an auditor or a new owner can follow any number back to its source. -->

| Question | Metric | Metric definition | Datasets | Tests | Consumers *(Example)* |
|---|---|---|---|---|---|
| Q1 Net revenue by region vs last year | MET-REV-001, MET-REV-004 | MET-REV-001, MET-REV-004 | DS-01, DS-02, DS-06 | TST-02, TST-04, TST-05, TST-07 | CON-01, CON-02 |
| Q2 Average order value by segment, rolling 90 days | MET-REV-003 | MET-REV-003 | DS-01, DS-04 | TST-02, TST-05 | CON-01 |
| Q3 Share of orders refunded within 30 days | MET-ORD-002 | MET-ORD-002 | DS-01, DS-03 | TST-03 | CON-01 (after certification) |
| [Question] | [MET-ID] | [Link] | [DS-NN] | [TST-NN] | [CON-NN] |

## 15. Glossary and references

<!-- Guidance: Define terms used in this model once, and link the related documents and sources. -->

| Term | Meaning |
|---|---|
| Semantic model | A governed definition of datasets, entities, dimensions and metrics that tools query instead of raw tables |
| Entity | A key that identifies a business object and defines how datasets join |
| Dimension | An attribute to group or filter by; categorical or time |
| Measure | A base aggregation of a column or expression |
| Metric | A named, owned calculation built from measures or other metrics |
| Metric time | The time dimension used to place a metric value on the calendar |
| Fan-out | Row multiplication caused by joining to a dataset at a finer grain |
| Certification | A dated statement by the business owner that a metric is correct and supported |

References:

- dbt MetricFlow, v0.213.0 (2026-09-10), Apache License 2.0 from
  v0.209.0: https://github.com/dbt-labs/metricflow
- dbt documentation on semantic models and metrics (entity roles,
  dimension types, metric types; the spec that replaces measures with
  simple metrics applies to dbt v1.12 and later): https://docs.getdbt.com
- Apache Ossie (incubating), core metadata specification 0.2.0.dev0,
  draft: https://github.com/apache/ossie
- Related templates: Metric Definition (`data/metric-definition`),
  Analytics and BI Solution Design
  (`architecture/analytics-bi-solution-design`), Dashboard
  Specification (`data/dashboard-specification`), Data Dictionary
  (`architecture/data-dictionary`), Data Platform Architecture
  (`architecture/data-platform-architecture`), dbt Project Standard
  (`data/dbt-project-standard`), Data Contract (`data/data-contract`).

## Standard and licence note

Structure aligned with the semantic modelling concepts of dbt MetricFlow
(Apache License 2.0, https://github.com/dbt-labs/metricflow) and with
the concept names of the Apache Ossie (incubating) draft core
specification (Apache License 2.0, https://github.com/apache/ossie).
Only concept and type names are used; no specification text, schema or
documentation prose is reproduced, and the Corvid Commerce example is
fictional. Ossie is a draft and may change. dbt and MetricFlow are
trademarks of dbt Labs, Inc., and Apache and Apache Ossie are trademarks
of The Apache Software Foundation; this template is not affiliated with
or endorsed by them. This template is original text written for this
collection. Check the current official versions before use. Template
licence: MIT.