Skip to main content

Semantic Layer & MetricFlow

For the last decade, the standard data architecture looked like this:

  1. Data Engineers build tables in Snowflake using dbt.
  2. The Marketing team connects Tableau to Snowflake and defines "Active Customers" using Tableau formulas.
  3. The Finance team connects Looker to Snowflake and defines "Active Customers" using LookML.

The Problem: At the end of the quarter, the CMO and the CFO present their reports, and the numbers don't match. Why? Because the business logic (the "metric") was defined in two different downstream BI tools.

To solve this, dbt introduced the Semantic Layer (powered by MetricFlow).

Topics Covered​

  • The Headless Semantic Layer Concept
  • MetricFlow in dbt
  • Defining Metrics in YAML
  • Querying Metrics

1. The Headless Semantic Layer Concept​

A Semantic Layer acts as a universal translator between your raw tables and your BI tools.

Instead of defining "Active Customers" in Tableau and Looker, you define it once, centrally, in dbt code. dbt then becomes the single source of truth for your business metrics.

When Tableau or Looker want to know the number of Active Customers, they do not query Snowflake tables directly. Instead, they query the dbt Semantic Layer via API, and dbt dynamically generates the correct SQL to fetch the answer.

2. Defining Metrics in YAML (MetricFlow)​

In dbt, metrics are defined alongside your models using YAML. dbt uses an engine called MetricFlow to compile these YAML definitions into SQL at runtime.

Let's assume our Indian fintech company has a model called fct_transactions. We want to define a metric called total_revenue.

Step 1: Define the Semantic Model​

Before you can define a metric, you must tell MetricFlow about the underlying table. You do this by defining a semantic_model.

# semantic_models.yml
semantic_models:
- name: transactions
description: "Semantic model for fintech transactions"
model: ref('fct_transactions') # Points to the physical dbt model

entities:
- name: transaction_id
type: primary
- name: customer_id
type: foreign

dimensions:
- name: transaction_date
type: time
type_params:
time_granularity: day
- name: is_successful
type: categorical

measures:
- name: revenue
description: "The monetary value of the transaction"
agg: sum
expr: amount_inr

Step 2: Define the Metric​

Now that MetricFlow understands the table (the entities, dimensions, and measures), you define the actual business Metric.

# metrics.yml
metrics:
- name: total_successful_revenue
description: "Total revenue from successful transactions only."
type: simple
type_params:
measure: revenue
filter: |
{{ Dimension('transaction__is_successful') }} = true

3. Types of Metrics​

MetricFlow supports several advanced metric types:

  1. Simple: Direct aggregation of a measure (e.g., sum(revenue)).
  2. Ratio: One metric divided by another (e.g., total_revenue / total_transactions to get Average Order Value).
  3. Cumulative: A rolling sum over time (e.g., "Year-to-Date Revenue").
  4. Derived: Complex expressions built from other metrics (e.g., (revenue - cost) / revenue to get Profit Margin).

By defining these purely in YAML, you guarantee that Profit Margin is calculated exactly the same way regardless of who is asking for it.

4. Querying the Semantic Layer​

Once your metrics are defined and deployed to dbt Cloud, how do business users access them?

They do not write SQL. Instead, dbt Cloud provides several connection interfaces:

  1. JDBC/ODBC: Traditional BI tools (like Tableau or Excel) connect to dbt exactly as if dbt were a database.
  2. GraphQL API: Custom web applications (like an internal React dashboard) can query the metrics dynamically over HTTP.

Example: A GraphQL Query​

An internal dashboard built by a developer in Chennai wants to show total_successful_revenue grouped by month. They send this GraphQL query to the dbt API:

query {
metric(
name: "total_successful_revenue",
groupBy: ["transaction_date__month"]
) {
data {
transaction_date__month
total_successful_revenue
}
}
}

MetricFlow receives this, dynamically writes the complex Snowflake SQL required to join the tables and aggregate the data, runs the query against Snowflake, and returns the JSON result to the dashboard.

Summary​

  • A Semantic Layer centralizes business logic (metrics) so that all downstream BI tools report the exact same numbers.
  • MetricFlow is the engine inside dbt that powers the Semantic Layer.
  • You define Semantic Models (entities, dimensions, measures) and Metrics (simple, ratio, cumulative) purely in YAML.
  • External tools query the dbt Semantic Layer via JDBC or GraphQL APIs, preventing analysts from ever having to write raw SQL joins.