Semantic layer: what it is, how it works and how to choose in 2026

Why do three dashboards show three different revenue numbers?

Ask three teams for last quarter's revenue and you can get three correct answers. The lab project in this post has nine orders. Finance counts money kept after refunds and skips cancelled orders. Sales counts order value before refunds and also skips cancelled orders. A dashboard built straight on the orders table counts every row.

The figures are invented: one 200-unit order is cancelled, and three orders carry refunds totalling 90. Currency, time zone and calendar widen the gap. An order placed at 21:00 in Toronto on September 30, 2026 is 01:00 UTC on October 1, so a UTC dashboard books it in October and a Toronto finance report books it in September. A fiscal year that starts on July 1 puts July to September in its first quarter.

Each team can defend its number, so the argument is about definitions, not arithmetic. A semantic layer turns each definition into a named object with one owner and one place to change it. Every dashboard, notebook and assistant then asks for net_revenue by name instead of recomputing it.

How does a semantic layer work?

A semantic layer does five jobs between the warehouse and whoever asks a question.

1. Model. It records which tables hold which business entities, how they join, which columns are dimensions and how each metric aggregates. In dbt's spec, entities are what you group or join by, and dimensions are what you filter or slice by.
2. Compile. A request names metrics and dimensions, never tables. The layer finds where the data lives, performs the joins and writes the SQL, as dbt's architecture page describes it.
3. Cache. Cube builds rollup tables called pre-aggregations in its own store and routes a matching query to one. dbt can pre-build cache tables from saved queries, but only on Enterprise-tier plans.
4. Control access. Cube applies access policies, written as code and ranging from row-level rules up, before a query reaches the warehouse.
5. Serve. One interface for BI tools, notebooks, apps and AI assistants: SQL, REST, GraphQL, JDBC or MCP, depending on the product.

The layer does not clean data or repair a weak model. It assumes the tables underneath have a clear grain, which is the job of a star schema on the gold layer of a medallion architecture.

How is a semantic layer different from a warehouse, a catalog or a mart?

The four overlap in conversation but differ in function.

The boundaries are blurring. Databricks defines metric views inside Unity Catalog, and Snowflake stores semantic views as schema-level objects, so the layer can now sit beside the data it describes. The warehouse and its tools are covered in big data analytics explained.

How do you define a metric in MetricFlow?

The lab ran dbt-core 1.12.5, dbt-metricflow 0.15.0 (MetricFlow 0.213.0) and dbt-duckdb 1.11.0 on DuckDB 1.5.6 on October 8, 2026. The project README lists MetricFlow as Apache 2.0 licensed from version 0.209.0, after the AGPL and then the BSL. DuckDB is not among the platforms dbt lists for MetricFlow (Snowflake, BigQuery, Databricks, Postgres on dbt v1 only, and Redshift), but it lets you reproduce everything on a laptop.

The data is nine orders in fct_orders and five customers in dim_customers. The YAML follows the latest spec: the semantic model sits under the model, entities and dimensions sit under columns, and simple metrics replace the older measures.

# models/orders.yml
models:
  - name: fct_orders
    description: One row per order.
    semantic_model:
      enabled: true
    agg_time_dimension: ordered_at

    columns:
      - name: order_id
        entity:
          type: primary
          name: order
      - name: customer_id
        entity:
          type: foreign
          name: customer
      - name: ordered_at
        granularity: day
        dimension:
          type: time
      - name: status
        dimension:
          type: categorical

    metrics:
      - name: gross_revenue
        description: Order totals before refunds. Cancelled orders are excluded.
        type: simple
        agg: sum
        expr: order_total
        filter: "{{ Dimension('order__status') }} != 'cancelled'"
        config:
          meta:
            owner: sales

      - name: refunds
        description: Refunds issued against completed orders.
        type: simple
        agg: sum
        expr: refunded_total
        filter: "{{ Dimension('order__status') }} != 'cancelled'"
        config:
          meta:
            owner: finance

Each simple metric carries its own filter, so no dashboard has to remember to drop cancelled orders. The owner sits in config.meta, which lands in target/semantic_manifest.json, so a script can read it. Metrics that build on other metrics can sit in a top-level metrics block, which the spec requires when they depend on a different semantic model:

# models/customers.yml
models:
  - name: dim_customers
    semantic_model:
      enabled: true
    columns:
      - name: customer_id
        entity:
          type: primary
          name: customer
      - name: region
        dimension:
          type: categorical

metrics:
  - name: net_revenue
    description: Gross revenue minus refunds. The number finance reports.
    type: derived
    expr: gross_revenue - refunds
    input_metrics:
      - name: gross_revenue
      - name: refunds
    config:
      meta:
        owner: finance

  - name: refund_rate
    description: Refunds as a share of gross revenue.
    type: ratio
    numerator: refunds
    denominator: gross_revenue
    config:
      meta:
        owner: finance

A query names metrics and dimensions, never tables. The lab returned:

mf query --explain prints the SQL instead of running it. This is net_revenue by month and customer region, with table aliases shortened:

SELECT
  metric_time__month
  , customer__region
  , gross_revenue - refunds AS net_revenue
FROM (
  SELECT
    metric_time__month
    , customer__region
    , SUM(gross_revenue) AS gross_revenue
    , SUM(refunds) AS refunds
  FROM (
    SELECT
      DATE_TRUNC('month', fct_orders.ordered_at) AS metric_time__month
      , fct_orders.status AS order__status
      , dim_customers.region AS customer__region
      , fct_orders.order_total AS gross_revenue
      , fct_orders.refunded_total AS refunds
    FROM "shop"."main"."fct_orders" fct_orders
    LEFT OUTER JOIN
      "shop"."main"."dim_customers" dim_customers
    ON
      fct_orders.customer_id = dim_customers.customer_id
  ) subq_5
  WHERE order__status != 'cancelled'
  GROUP BY
    metric_time__month
    , customer__region
) subq_9

Nobody wrote that filter or join: the filter comes from the metric definitions, the join from the shared customer entity, and the outer query applies the derived expression.

The fiscal calendar is defined once too. A daily date model carries a fiscal_quarter label (the fiscal year starts on July 1), and the time spine config registers it as a custom granularity, so any metric can be grouped by metric_time__fiscal_quarter:

# models/time_spine.yml
models:
  - name: time_spine_daily
    time_spine:
      standard_granularity_column: date_day
      custom_granularities:
        - name: fiscal_quarter
          column_name: fiscal_quarter
    columns:
      - name: date_day
        granularity: day
      - name: fiscal_quarter

Which metric types can you define, and where do they break?

MetricFlow has five metric types. The lab covered four.

Cumulative metrics held the surprises:

• Month start or month end. Grouped by month with no period_agg, the running total of gross_revenue read 120, 200 and 500 for July to September: its value when each month opens, with July at 120 because the first order arrived on July 3. Setting period_agg: last gave month-end totals of 200, 500 and 950.
• No time range. Without a start and end time the same query returned 18 monthly rows, to December 2027 at 950, because the lab's time spine runs that far. Bound every cumulative query.
• Derived inputs. A cumulative metric over the derived metric net_revenue failed mf validate-configs and then failed at query time with the one-word error 'net_revenue'. The same definition over the simple metric gross_revenue worked.

Behaviour like this is why metric values need tests in CI.

Which semantic layer should you choose in 2026?

Seven options cover most choices as of October 2026. Cells come from each vendor's own documentation; the AtScale cells come from its marketing page, so treat them as claims.

Two details change the choice: Looker's SQL interface needs a LookML project on a BigQuery connection and wraps every measure in AGGREGATE(), and Power BI's rename was a rename only, with REST API operations that still say dataset. How to choose:

• dbt already runs your transformations. Start with MetricFlow. The open-source engine covers definitions, validation and a CI check; the APIs and BI integrations need a dbt platform plan.
• Applications and embedded analytics come first. Cube pairs a data model with REST, GraphQL and SQL interfaces for them.
• Several BI tools must share one model. AtScale sells exactly that, and its own page names Excel, Tableau and Power BI. Prove its claims in a pilot.
• One BI tool dominates. Its native model is the cheapest start, but other tools get less: Looker's SQL interface has no joins.
• Your platform is Databricks or Snowflake. Definitions sit beside the data, and each vendor's own AI agents read them.

Whatever you pick, ask three questions: can you review definitions in Git, who can query them and through which interface, and what do you lose if you leave?

What are Open Semantic Interchange and Apache Ossie?

Every product above stores definitions in its own format, so moving between them means rewriting. Open Semantic Interchange (OSI) set out to fix that. Snowflake was one of its founding organizations, and the repository opened in November 2025. On July 10, 2026 the project announced a new name, Apache Ossie (Incubating), after joining the Apache Incubator, to avoid confusion with other projects that share the OSI acronym. The announcement says the specification did not change and that the coalition grew from 17 launch partners to more than 50 organizations. Incubation is an early stage: the project notice says it "has yet to be fully endorsed" by the Apache Software Foundation.

dbt v1.12 reads Ossie documents in .json, versioned 0.1.0 or 0.1.1, from an osi/ folder. Support for dbt v2 is listed as coming soon, and constructs it cannot convert are dropped with warning I078. In the lab, dbt 1.12.5 also wrote target/osi_document.json for the native project above, with each metric flattened to an ANSI SQL expression (excerpt):

{
  "name": "net_revenue",
  "expression": {
    "dialects": [
      {
        "dialect": "ANSI_SQL",
        "expression": "SUM(CASE WHEN order__status != 'cancelled' THEN fct_orders.order_total END) - SUM(CASE WHEN order__status != 'cancelled' THEN fct_orders.refunded_total END)"
      }
    ]
  }
}

Two limits showed up. The filter uses MetricFlow's name order__status, while the dataset field in the same file is called status. And when the project held cumulative metrics, dbt warned that their "cumulative window and grain semantics cannot be represented" and exported them as ordinary sums. The Ossie announcement lists "advanced metric logic, windowing functions" among additions the community may propose, and adds that none of it is predetermined. Use the format to move simple, ratio and derived metrics, and diff exported expressions against your definitions before you trust a round trip.

How does a semantic layer help AI assistants, and where does it stop?

An assistant writing SQL from a question works from table and column names and writes a new query every time. dbt Labs says the model "has to infer the semantics of your data from structural clues". With a semantic layer the task shrinks: the assistant picks metrics and dimensions by name, and the layer writes the SQL. The dbt MCP server offers list_metrics, get_dimensions and query_metrics for that, and get_metrics_compiled_sql returns the SQL without running it. Cube describes the same pattern: every query is "validated against the data model and has access policies applied deterministically before reaching the warehouse".

Not every product compiles the query. Snowflake's documentation says Cortex Agents currently "reads the information captured in the semantic view definition and generates the SQL against the physical tables directly": the definitions guide the model, but the model still writes the SQL.

In April 2026 dbt Labs reran its text-to-SQL comparison, vendor research built on the ACME Insurance benchmark created by Juan Sequeda and colleagues at data.world: 11 questions, each run 20 times, across 15 tables. On the modelled project:

Three details matter more than the headline. The text-to-SQL runs loaded "the entire schema as context, which isn't practical for larger datasets". Before extra modelling, some questions could not be answered through the layer at all, because the normalized schema needed too many entity hops. And three new dbt models, drafted by an LLM, let the layer answer every question. The authors sum up the failure modes: "With text-to-SQL, failure looks like a plausible but incorrect answer. With the Semantic Layer, failure looks like an error message."

The limits are plain. A layer cannot answer a question nobody modelled, cannot correct a wrong definition, and does nothing for an assistant that holds warehouse credentials and writes its own SQL. Treat the assistant like any other consumer: read-only tools with the least access they need, answers grounded in approved sources, and an evaluation set built from real questions that runs before every change, which is how Computese runs AI and automation work. Text-to-SQL and its risks are covered in generative AI for databases.

How do you introduce a semantic layer without a big-bang project?

Prove one number end to end before modelling anything else:

1. Pick 10 to 20 metrics that appear in board and finance reports. Write each as a sentence first: what it counts, what it excludes, its time grain and who signs it off.
2. Name one owner per metric. Computese's data process uses the same gate: every key metric has one definition and one owner.
3. Model the tables underneath. One fact table per business event, conformed dimensions, a stated grain.
4. Define the metrics in code and review them in pull requests like any other change.
5. Reconcile before you switch. Run old and new side by side, compare totals within a tolerance the business agrees, and have the owner sign off before the old report is retired.
6. Migrate the top dashboards first, then delete the duplicate calculations from the BI tools.
7. Close the side doors. Give BI and AI service accounts access to the layer, not to the raw tables.

Tests make the plan stick. The lab's check_metrics.sh builds the project, validates every definition, fails on any metric without an owner, and compares one value that finance has signed off:

#!/usr/bin/env bash
# Fails the build when a definition breaks, a metric has no owner or a value drifts.
set -euo pipefail

dbt build --quiet   # also writes target/semantic_manifest.json, which mf reads
mf validate-configs

python3 - <<'PY'
import json, sys
metrics = json.load(open("target/semantic_manifest.json"))["metrics"]
missing = [m["name"] for m in metrics if not m["config"]["meta"].get("owner")]
if missing:
    sys.exit("metrics without an owner: " + ", ".join(missing))
PY

mf query --metrics net_revenue --group-by metric_time__month \
  --start-time 2026-07-01 --end-time 2026-07-31 --csv net_revenue_2026_07.csv

got="$(tail -n 1 net_revenue_2026_07.csv | tr -d '\r' | cut -d, -f2)"
want="180"
if [ "$got" != "$want" ]; then
  echo "net_revenue for 2026-07: got $got, expected $want" >&2
  exit 1
fi

On the project as it stands the script exits 0. With want="181" it prints net_revenue for 2026-07: got 180, expected 181 and exits 1. With the owner removed from refunds it stops with metrics without an owner: refunds.

Validation earns its place on upstream changes. When the lab renamed refunded_total to refunded_amount in the fct_orders model, the view still built, but mf validate-configs exited 1 with five errors: the fct_orders semantic model, the refunds measure and the metrics refunds, net_revenue and refund_rate, each reporting that column refunded_total was not found. Restoring the column made the script pass again.

Computese builds this as part of its data platform service: models pass their tests in CI before production, and new numbers are reconciled with the old ones and signed off by the business.