Library · Data Practice OS

The semantic layer

Reference guide · Published · Source reviewed 2026-11-06

The answer: before any natural-language "ask the data" experience goes in front of users, the metrics it will answer about must be defined once, in version control, and reviewed by the people who own them. The Semantic layer is the most important AI investment in the practice, and it pays off for BI before any model touches it.

Why it matters for AI

An LLM asked "how many heavy vehicle registrations lapsed last quarter?" has to decide what heavy vehicle, lapsed and quarter mean, which tables hold them and how they join. Without a semantic layer it guesses from column names, and it guesses differently each time. Text-to-SQL accuracy on real schemas jumps when the model is given declared metrics, synonyms and verified example queries instead of raw DDL. That is what Cortex Analyst semantic views and AI/BI Genie instructions and metric views do.

Where to define it

Option	Use when	Watch out for
dbt Semantic Layer (MetricFlow) (MetricFlow)	Both platforms are in play, or you want metrics portable across BI tools	A separate service to run; LLM tools must call its API rather than read native objects
Snowflake semantic views	Snowflake is the serving platform and Cortex Analyst is the chat front end	Snowflake-only; keep the YAML in git and generate it, do not hand-edit in the UI
Databricks metric views	Databricks SQL and AI/BI Genie are the serving platform	Databricks-only; same git discipline
Power BI semantic model	Small, BI-only marts	Logic trapped in one tool; LLMs outside Power BI cannot reuse it

Practice default: author metric definitions once in dbt (see ADR-006: Define metrics once in a semantic layer) and generate the platform-native semantic views or metric views from them for each platform's AI front end. One source, many projections. Track Apache Ossie (formerly Open Semantic Interchange), which aims to make that generation step a standard. Do not depend on it yet.

What a good definition contains

Metric: name, plain-English definition, owner, aggregation, filters, grain.
Dimensions with synonyms that users actually say ("rego", "plate", "VIN").
Join paths declared, not inferred.
Verified queries: 10 to 30 real questions with the approved SQL. These double as the evaluation set.
Guard notes: "Never count cancelled registrations as active", "Licence counts exclude learner permits unless asked".

# models/marts/registrations/_semantic.yml  (dbt Semantic Layer)
semantic_models:
  - name: registrations
    model: ref('fct_registration_events')
    entities:
      - { name: vehicle, type: foreign, expr: vehicle_id }
    dimensions:
      - { name: event_date, type: time, type_params: { time_granularity: day } }
      - { name: vehicle_class, type: categorical }   # synonyms: class, category
    measures:
      - { name: lapsed_count, agg: count, expr: "case when event_type = 'LAPSE' then 1 end" }
metrics:
  - name: registrations_lapsed
    label: Registrations lapsed
    description: Registrations that passed expiry without renewal. Excludes cancellations and transfers.
    type: simple
    type_params: { measure: lapsed_count }

Worked example: registrations

The registrations domain team owns eight metrics (active, new, renewed, lapsed, cancelled, transferred, concessions applied, revenue). They define them in dbt alongside the gold Data product, add 25 verified questions from the call centre's most common requests, and publish. The platform pipeline generates a Snowflake semantic view for Cortex Analyst and a Databricks metric view for AI/BI Genie. Power BI connects to the same gold tables. When the definition of "lapsed" changes after a policy review, it changes in one pull request, and the evaluation suite shows exactly which verified answers moved.

When not to bother (yet)

Exploratory data science on bronze or silver data. Analysts write SQL directly.
A one-off report with no reuse.
A domain where the definitions are still contested. Settle the definitions first. Encoding an argument into YAML does not end it.

Pitfalls

Too wide. A semantic model covering 400 columns gives the LLM more ways to be wrong. Start with one domain and fewer than 30 metrics.
No owner. Every metric needs a named business owner who signs off its definition.
Drift. Platform-native views edited by hand diverge from dbt. Make CI regenerate them and fail on a diff.
Skipping Lineage. Users trust an AI answer more when it can show which gold table and metric it came from. Keep lineage from metric to source intact.

Verify current semantic-view and metric-view capabilities against the Snowflake and Databricks docs. Both moved quickly through 2025-26.