Semantic Layer Patterns — A Field Guide

Every analytics team eventually hits the same wall: the dashboard says 8,400 active users, the weekly report says 7,100, and finance's spreadsheet says something else entirely. Nobody lied. Three people wrote three different SQL queries for "active user" — one counted logins, one counted any API call, one excluded a customer segment nobody remembered to tell the others about. A semantic layer is the unglamorous fix for this: define the metric once, in one place, and make every tool downstream read from that definition instead of reinventing it.

The problem: metric drift across dashboards

Metric drift happens gradually, not all at once. Someone builds a Looker dashboard with a quick COUNT(DISTINCT user_id). Someone else builds a Metabase dashboard six months later and joins in a table the first person didn't know existed, changing the denominator. A data analyst writes a one-off query for a board deck that excludes trial accounts because that's what the CFO asked for that quarter, and the filter never gets removed. None of this is anyone doing a bad job — it's what happens when the definition of a metric lives inside SQL scattered across BI tools, notebooks, and dashboard configs instead of in one governed place. By the time someone notices two numbers disagree, there's no way to tell which query is "right," because there was never a canonical one to check against.

What a semantic layer actually is, technically

Strip away the vendor language and a semantic layer is a mapping layer that sits between your raw warehouse tables and the tools people query them with. It defines dimensions (attributes you group or filter by — plan_type, country, signup_month) and metrics (aggregations with a specific formula — active_users, net_revenue, churned_accounts) as named, versioned objects instead of ad hoc SQL. Underneath, it still generates SQL against your warehouse — it isn't a new database — but the query a BI tool sends is "give me active_users by country for last quarter," not a hand-rolled join and filter. The mapping layer resolves that request into the actual SQL using one shared definition, so a dashboard in Looker and a query from a Python notebook produce the same number for the same metric, because they're both compiling down to the same underlying logic.

dbt's semantic layer vs Cube vs LookML

The three tools people actually reach for solve this the same way conceptually but differ in where they live and how they execute. dbt's semantic layer (built on MetricFlow) defines metrics as YAML alongside your existing dbt models — a metric references a semantic model, which in turn references a dbt model already in your project. It's the natural choice if you're already using dbt for transformation, since metrics live in the same repo, get version-controlled the same way, and pass through the same CI. Queries go through the dbt Semantic Layer API (via Arrow Flight or JDBC), which most BI tools connect to directly. Cube is a standalone semantic layer server — you define schemas in JavaScript/TypeScript or YAML, and Cube runs as its own service in front of your warehouse, exposing a SQL API, GraphQL API, and REST API simultaneously. It's less coupled to dbt and better suited if multiple teams query through different protocols, or if you want the semantic layer itself to own caching and pre-aggregation as an active service rather than a passive definition. LookML predates both and is Looker-specific — it's a real semantic layer (explores, dimensions, measures, joins defined once) but it only serves Looker; you can't point a different BI tool or a notebook at a LookML model without going through Looker's own query API. The trade-off is real: dbt's approach is transformation-first and free-ish if you already pay for dbt, Cube is BI-tool-agnostic but another service to run, LookML is the most mature but locks your metric definitions to one visualization tool.

semantic_models:
  - name: users
    model: ref('fct_user_activity')
    defaults:
      agg_time_dimension: activity_date
    entities:
      - name: user
        type: primary
        expr: user_id
    dimensions:
      - name: activity_date
        type: time
        type_params:
          time_granularity: day
      - name: plan_type
        type: categorical
    measures:
      - name: active_user_count
        agg: count_distinct
        expr: user_id

metrics:
  - name: active_users
    label: Active Users
    type: simple
    type_params:
      measure: active_user_count
    filter: |
      {{ Dimension('user__last_event_type') }} != 'internal_test'

Where it sits relative to the warehouse and BI tools

The semantic layer sits in the query path, not the ETL path. Your ELT pipeline (Fivetran, Airbyte, dbt models) still builds the warehouse tables the same way it always did — the semantic layer doesn't replace transformation, it sits downstream of it. Think of the stack as four layers: raw sources, transformed warehouse tables (dbt marts, typically), the semantic layer translating "give me this metric" into SQL against those marts, and finally the BI tools, notebooks, and reverse-ETL jobs that consume it. This means a semantic layer only pays off once your warehouse modeling is reasonably settled — if your dbt marts are still being restructured weekly, you're defining metrics on a moving target. It also means the semantic layer becomes the new integration point: instead of every BI tool having its own database credentials and writing its own joins, they all speak to one API that enforces the same metric definitions regardless of which tool is asking.

Caching and performance considerations

Because every query now funnels through one layer, that layer becomes both your best caching opportunity and your biggest single point of latency if you get it wrong. Cube leans into this hardest — it has a built-in pre-aggregation system that materializes rollups (say, daily active users by plan type) into its own storage or back into the warehouse, so a dashboard hitting the same metric repeatedly doesn't recompute it from raw rows each time. dbt's semantic layer is thinner by comparison: it compiles to SQL and runs it against your warehouse each time unless you pair it with a BI tool's own caching or materialize commonly-used metrics as dbt models directly. LookML sits in between, relying on Looker's persistent derived tables and query cache. The practical rule: if a metric is expensive to compute (large joins, window functions over years of data) and queried often, materialize it — don't rely on the semantic layer to make an expensive query cheap just by naming it. It will still run the expensive query, just with a friendlier name on it.

Wrapping up

None of these tools solve the human problem — someone still has to agree on what "active" means and write it down once. What they solve is the drift: once active_users is defined in one place and every dashboard, notebook, and export pulls from that definition, disagreements move from "whose query is right" to "should we change the definition," which is a much shorter argument. Start with the one or two metrics that cause the most arguments in your org, define those first, and expand from there — trying to migrate every existing dashboard metric on day one is how these projects stall.