dbt Semantic Layer for Analytics Engineers: MetricFlow & dbt Labs

The dbt semantic layer centralizes metric logic in your dbt project and exposes consistent answers through a governed API and integrations. You define metrics once in YAML, a planning engine compiles warehouse-native SQL on demand, and downstream tools consume trusted results without each team re-implementing business logic. For analytics engineers, this eliminates metric drift, enables governed reuse across BI and AI, and keeps performance where it belongs: inside the warehouse.

What is the semantic layer of dbt?

The dbt semantic layer is a definition-first abstraction over your warehouse models that standardizes every metric and dimension once, then serves them to applications. You put the definition of a metric (e.g., revenue, active_users, conversion) next to your models. When an application issues a query, the layer generates optimized SQL, enforces joins and filters, and returns consistent results. In short, the semantic layer eliminates the usual chaos of copy-pasted calculations and differing time filters across teams.

Think of it as a governed interface that sits on top of your transformed data, providing a single source of truth that your analytics stack—both BI and ai—can trust.

Components of the dbt semantic layer

These are the core components of the dbt semantic layer you operate as an analytics engineer:

• Semantic models and metrics in declarative files: a precise definition of measures, dimensions, and relationships bound to warehouse tables/views.
• MetricFlow: the compiler and planner that generates pushdown SQL for a requested metric set, handling joins, time windows, and grains.
• dbt Cloud services by dbt Labs: a hosted registry, API endpoints, and integrations that route query requests to your warehouse.
• Connectors/integrations: partner apps and downstream consumers (BI, notebooks, ai agents) that call the API or supported interfaces.
• Governance: permissions in dbt Cloud and the warehouse enforcing who can discover or run a given metric.

An example of a semantic layer

Below is a compact example with a semantic model for orders and a few metrics. This config lives with your transformations in the dbt project.

# models/marts/orders.yml
version: 2
semantic_models:
  - name: orders
    description: Base orders grain is 1 row per order_id
    model: ref('fct_orders')
    entities:
      - name: order_id
        type: primary
      - name: user_id
        type: foreign
    dimensions:
      - name: order_date
        type: time
        type_params:
          time_granularity: day
      - name: country
        type: categorical
    measures:
      - name: order_count
        agg: count_distinct
        expr: order_id
      - name: revenue
        agg: sum
        expr: total_amount

metrics:
  - name: total_orders
    type: simple
    label: Total Orders
    type_params:
      measure: order_count

  - name: gross_revenue
    type: simple
    label: Gross Revenue
    type_params:
      measure: revenue

  - name: 7d_visit_to_purchase_conversion
    type: ratio
    label: 7D Visit to Purchase Conversion
    type_params:
      numerator:
        name: users_purchased_within_7d
      denominator:
        name: users_visited

  - name: users_visited
    type: cumulative
    type_params:
      measure: visited_users
      window: all_time

  - name: users_purchased_within_7d
    type: derived
    filter: >-
      -- This references a visit session model joined via user_id and date logic
      visit_to_purchase_within_days = 7

The example shows how you can define metrics once and let the layer orchestrate joins and time windows. You can add more metrics and dimensions safely without duplicating sql across tools.

As an example: a conversion metric within 7 days

Searchers often ask: “How many users visited my website and made a purchase within 7 days of their visit?” In the dbt semantic layer, you create a metric for visits, a metric for purchases-within-7-days, and a ratio. The definition lives in your repo; the engine plans the rest.

# models/marts/sessions.yml
version: 2
semantic_models:
  - name: sessions
    model: ref('fct_sessions')
    entities:
      - name: user_id
        type: primary
    dimensions:
      - name: session_date
        type: time
        type_params: { time_granularity: day }
    measures:
      - name: visited_users
        agg: count_distinct
        expr: user_id

metrics:
  - name: users_purchased_within_7d
    type: derived
    filter: >-
      -- implemented via a time-aware join from sessions to orders
      purchase_within_days = 7

To request the answer, an application issues a query for 7d_visit_to_purchase_conversion by day or by country. The layer returns a time series with a consistent numerator and denominator across all tools.

But first, what is MetricFlow?

MetricFlow is the planning engine behind the dbt semantic layer. It reads semantic definitions of your metrics, builds a graph of possible joins and filters, chooses a path to your requested grain, and emits efficient warehouse-native sql. It resolves time grains, handles windowed logic (like 7-day conversions), and ensures the same metric returns identical results regardless of where the query originated.

But how does the engine retrieve the answer when a user queries a metric from their application?

At runtime, the engine:

• Parses the requested metric(s) and optional group-bys/filters.
• Finds the semantic path through entities and dimensions across data sources.
• Constructs a plan, picking the minimal necessary joins.
• Generates pushdown sql with windows, aggregations, and predicates.
• Executes against the warehouse and returns results to the caller.

Here is an illustrative snippet of compiled warehouse SQL for a 7-day conversion time series (simplified for clarity):

-- Generated by the semantic planning engine (illustrative)
WITH sessions AS (
  SELECT user_id, session_date
  FROM analytics.fct_sessions
  WHERE session_date BETWEEN DATE_SUB(CURRENT_DATE(), INTERVAL 60 DAY) AND CURRENT_DATE()
),
orders AS (
  SELECT DISTINCT user_id, order_date
  FROM analytics.fct_orders
  WHERE order_date BETWEEN DATE_SUB(CURRENT_DATE(), INTERVAL 60 DAY) AND CURRENT_DATE()
),
visits AS (
  SELECT session_date AS ds, COUNT(DISTINCT user_id) AS users_visited
  FROM sessions
  GROUP BY ds
),
conversions AS (
  SELECT s.session_date AS ds,
         COUNT(DISTINCT s.user_id) AS users_purchased_within_7d
  FROM sessions s
  JOIN orders o
    ON o.user_id = s.user_id
   AND o.order_date BETWEEN s.session_date AND DATE_ADD(s.session_date, INTERVAL 7 DAY)
  GROUP BY ds
)
SELECT v.ds,
       SAFE_DIVIDE(c.users_purchased_within_7d, v.users_visited) AS 7d_visit_to_purchase_conversion
FROM visits v
LEFT JOIN conversions c USING (ds)
ORDER BY ds;

As an analytics engineer, you focus on the semantic definition and clean models. The engine handles the generated sql for you.

How to query dbt semantic layer

There are three common patterns to consume metrics from the dbt semantic layer hosted in dbt Cloud:

• Semantic Layer API: Issue a query for one or more metrics, optionally grouped by dimensions (e.g., day, country). The API responds with rows and metadata.
• Partner integrations: Supported BI or notebooks connect to the layer so authors select governed metrics instead of writing bespoke calculations.
• Generated SQL preview: In some workflows you can inspect or log the compiled query for observability and debugging.

Example API call (illustrative):

curl -X POST \
  -H "Authorization: Bearer <DBT_CLOUD_TOKEN>" \
  -H "Content-Type: application/json" \
  https://cloud.getdbt.com/api/vX/semantic-layer/query \
  -d '{
    "metrics": ["gross_revenue", "total_orders"],
    "group_by": ["orders__order_date:day", "orders__country"],
    "filters": ["orders__order_date >= 2024-01-01"]
  }'

You can also call from Python to power ai copilots or notebooks:

import requests

payload = {
  "metrics": ["7d_visit_to_purchase_conversion"],
  "group_by": ["sessions__session_date:day"]
}

r = requests.post(
  "https://cloud.getdbt.com/api/vX/semantic-layer/query",
  headers={"Authorization": f"Bearer {TOKEN}"},
  json=payload,
  timeout=60,
)
print(r.json())

BI tools like tableau can consume results via supported connectors or partner integrations; when not directly supported, they continue to point at the warehouse while you migrate critical metric logic into the layer. For JDBC/ODBC scenarios, confirm whether a jdbc route is available in your region and account. Always verify current support in the official dbt documentation.

What is the semantic layer of dbt security?

Security spans dbt Cloud and your warehouse. At a high level:

• Identity and permissions: Access to semantic objects and the API is controlled by dbt Cloud roles and project access.
• Warehouse authorization: Queries execute under a service identity or delegated user identity, constrained by warehouse roles and policies you define.
• Data residency and transport: Results travel over TLS; stored artifacts follow your dbt Cloud org settings. Sensitive filters/columns should still be enforced in the warehouse.
• Auditability: Request logs help you trace who ran which metric and when—useful for regulated reporting.

Because exact features vary by adapter and account configuration, confirm details in the docs and your security review. The guiding principle is simple: the warehouse remains the policy enforcement point; the dbt semantic layer orchestrates consistent metric logic on top.

Integration patterns and real-world limits

Integrations fall into two buckets:

• Direct semantic consumption: Tools that natively browse and run a metric (e.g., “gross_revenue by month”) through the API.
• Hybrid: Tools that still render warehouse-native sql dashboards but rely on the layer to keep business logic centralized.

Common caveats:

• Grain and join semantics: Ensure your models encode the correct keys; the planner can’t fix bad joins.
• Expensive windows: 7-day or 30-day windows over large tables can be costly without partitions or pre-aggregations.
• Adapter support: Some features are adapter-specific; test early.

Performance on “40M-row orders” and similar

If your orders table has 40M rows and growing, treat the dbt semantic layer as a planner, not a silver bullet. Performance remains a warehouse concern. Practical tips:

• Model hygiene: Pre-join and de-duplicate in a dbt model so semantic joins are simple and selective.
• Partition and cluster: Design your warehouse tables for time-based pruning. The layer will push date predicates if your query includes time filters.
• Rollups: Materialize daily aggregates in dbt for heavy-read metrics (e.g., revenue by day,country). The planner can then hit smaller tables.
• Window hints: Where supported, leverage built-in warehouse features for efficient windowing.

Remember: the layer generates great sql. It still executes on your warehouse. Monitor and optimize like any other critical workload.

Can anyone give me any info whether dbt semantic layer works with dbt Core and ClickHouse DB?

Short answer: the hosted dbt semantic layer is a dbt Cloud capability. dbt Core does not include the hosted registry or API. You can still define metrics locally, but you won’t have the managed service to answer metric query requests.

For ClickHouse: support depends on the adapter and Semantic Layer compatibility at the time you read this. Many teams use the ClickHouse adapter with dbt for transformations, but the managed semantic service may not be available for that adapter. Check the official docs for the current matrix. If unsupported, you can still centralize business logic in models and expose curated tables to your bi tool while you evaluate roadmap timelines.

Feeling behind AI?

The dbt semantic layer is a strong bridge between governed data and ai applications. Instead of letting an LLM improvise financial logic, have it call the semantic API for a named metric. This keeps the model’s role to language and UX while the warehouse computes truth. You can even build an agent purpose-built for analytics engineering that maps user questions to approved metrics and dimensions, returns answers with lineage, and logs access for governance.

Practical pattern: prompt the model with available metrics across entities and the allowed group-bys; when a user asks for “7-day conversion by country last quarter,” the agent calls the metric endpoint and formats the result. No freehand arithmetic, no drift. You can ship the result straight to a dashboard or a chat reply.

Example: adopting the layer in weeks, not months

1. Inventory metrics: List 10–15 core business metrics that drive your dashboards and SLAs.
2. Model readiness: Ensure keys and grains are sound. See our dbt tests guide to harden assumptions.
3. YAML: Add semantic definitions for measures and metrics. Start with simple sums and counts.
4. Wire an integration: Point a notebook, bi tool, or ai service at the API.
5. Iterate: Add windows, ratios, and new dimensions after you verify base correctness.

If you’re newer to dbt, skim the dbt tutorial and our dbt topic hub for context, then come back and layer in semantics.

Comparison: where to encode metrics

How to configure and operationalize

At a high level, you’ll configure three areas in the dbt platform:

• Project: Add semantic definitions near models. Keep names stable; treat them like APIs.
• Access: Configure permissions for authors vs. consumers.
• Integrations: Register apps that will consume metrics (BI, notebooks, ai services). Validate row limits, timeouts, and caching where applicable.

Then, use the dbt semantic layer deliberately: add metrics incrementally, tag production-ready ones, and monitor usage. Many teams use the dbt Cloud job outputs to validate changes before promoting to production.

FAQ: quick answers

What is an example of a semantic layer?

The config above that defines gross_revenue, total_orders, and the 7-day conversion is a concrete example. The layer maps those to warehouse tables and generates the query logic.

How to query dbt semantic layer?

Use the API in dbt Cloud or a supported integration. Request one or more metrics, optional dimensions and filters, and receive results. You can also inspect generated sql when needed.

As an example, I can use a conversion metric to answer the following question: how many users visited my website and made a purchase within 7 days of their visit?

Yes. Model visits and orders with shared keys and dates, then define a derived metric for purchases within 7 days and a ratio metric. Request it by time and any supported dimension.

But first, what is MetricFlow?

It’s the planner/compiler that reads semantic definitions and writes efficient warehouse SQL for a requested metric set.

But how does MetricFlow retrieve the answer when a user queries a metric from their application?

It determines the path through entities and joins, creates a plan, and emits pushdown SQL that the warehouse executes, returning consistent results to the caller.

What is the semantic layer of dbt security?

Permissions live in dbt Cloud and the warehouse. The warehouse enforces data access; the layer standardizes logic and routes execution.

Can anyone give me any info whether dbt semantic layer works with dbt Core and ClickHouse DB?

The hosted service and API require Cloud. For ClickHouse, check adapter and feature support in current docs. You can still transform with the adapter even if the managed service isn’t supported.

Does anyone here have experience with it?

Practitioner tip: start with 3–5 metrics that cause the most debate. Prove consistency across two tools. Then expand.

Practitioner patterns you can copy

• Centralize a glossary in your repo and map each term to exactly one metric definition.
• Expose a “metrics catalog” page to analysts. Link to lineage and tests. See our dbt macros and Jinja tips to keep metadata tidy.
• Automate contract checks in CI. For testing primers, defer to the dbt tests guide.
• When migrating a contentious metric, freeze BI calculations and make the new layer the only path to a production dashboard.

When to prefer curated tables over the semantic layer

Use curated rollups if:

• You serve thousands of daily dashboard refreshes with the same slice and dice.
• You need strict SLAs and want to precompute at ingestion time.

Use the dbt semantic layer if:

• You need consistent metrics across many tools and teams.
• You want ai assistants to call named metrics safely instead of writing ad-hoc SQL.
• Your data teams want one contract to evolve, with fewer table permutations.

Governance and change management

Treat models and metrics as public interfaces. Version names, deprecate with notices, and publish release notes in your repo. Align with your dbt community of contributors so breaking changes are rare and well signposted.

Putting it together

The dbt semantic layer is a pragmatic way to codify business logic and serve it consistently across tools—without leaving the warehouse. With MetricFlow generating tight SQL, dbt Labs providing hosted services, and integrations enabling BI and ai consumption, analytics engineers finally get a durable interface for metrics. Use it to align teams, reduce debate, and unlock governed self-serve.

If you need to refresh fundamentals first, skim our analytics engineering portfolio projects, the dbt tutorial, and the broader dbt topic hub. If you script your own utilities, those dbt macros & Jinja tips are a force multiplier.

Finally, you can use the dbt Cloud tooling today, wire one consumer, and iterate. If your data platform or adapter isn’t supported yet, stage your migration by codifying logic and exposing curated tables while you pilot integrations with select downstream tools.

Want to practice the skills from this guide? Try the free graded exercises at /practice.