Understanding MetricFlow: Semantic Models and Query Generation in Practice

MetricFlow starts from the measures, dimensions, and entities defined in semantic models, aggregates the requested metrics at the optimal granularity and join path, and emits the final SQL. You can analyze data without writing SQL directly through consistent definitions, but ambiguous model design or query specification can lead to unintended aggregations.

This article explains the semantic model building blocks that exams frequently test, along with MetricFlow's query generation logic from a practical standpoint. It also covers real-world pitfalls including definition placement and naming, explicit join paths, and handling of time granularity and the time spine.

Semantic Model Basics and Terminology

A semantic model is a layer that expresses business meaning independently from downstream reports and applications. MetricFlow reads these definitions and plans queries by combining measures (what to aggregate), dimensions (slicing axes), and entities (join keys). The default time axis for aggregation is set by agg_time_dimension.

A common source of confusion both on the exam and in practice is the distinction between measures and metrics. A measure is an aggregation definition on a table (such as sum(amount)); a metric is a business KPI built on top of measures (including period, filter, ratio, and so on). Think of measures as reusable minimal units and metrics as decision-making units.

• Manage semantic models in dbt YAML and align lineage with the dbt DAG
• Entities are essential for choosing join paths; assign primary and foreign correctly
• For time dimensions, make time_granularity and agg_time_dimension explicit
• Keep measures minimal and reusable; let metrics add context (filter, period, window)

Minimal semantic model and metric definition example

semantic_models:
  - name: orders
    model: ref('stg_orders')
    defaults:
      agg_time_dimension: order_date
    entities:
      - name: order
        type: primary
      - name: customer
        type: foreign
    dimensions:
      - name: order_date
        type: time
        type_params:
          time_granularity: day
      - name: status
        type: categorical
    measures:
      - name: order_total
        agg: sum
        expr: amount
      - name: order_count
        agg: count

metrics:
  - name: total_revenue
    type: simple
    type_params:
      measure: order_total
    filter: status = 'completed'

MetricFlow's Query Generation Flow

MetricFlow builds a plan from the requested metrics, group_by, where, and time_range, then locates the semantic models it needs. It picks join paths based on entities, aligns time granularity, and fills gaps with a time spine if needed. Finally, it decides the aggregation order and emits SQL targeted at the warehouse.

The crucial points in this process are the order of granularity changes and the handling of non-additive measures. First aggregate measures at their lowest grain, then roll them up to match group_by. Ratios and distinct measures are vulnerable to double counting, so make the intent explicit at the measure definition or metric type level and avoid unnecessary re-aggregation.

• Common causes of misbehavior: undefined entities, ambiguous join paths, mismatched time granularity
• Restrict group_by to dimensions reachable through entity paths
• where can mix filters applied before measure aggregation with conditions applied later, so be conscious of the definition level

MetricFlow's planning and SQL generation flow

User Request
    |
    v
[Parse metrics/group_by/where]
    |
    v
[Semantic graph resolve]
    |   \
    |    [Choose join path by entities]
    v
[Align time grain]
    |
    v
[Optional time spine (fill gaps)]
    |
    v
[Compute measures at base grain]
    |
    v
[Roll up to requested group_by]
    |
    v
[Apply metric-level filters/order/limit]
    |
    v
[Emit warehouse-specific SQL]

Request example and key parameters

# Conceptual request representation (actual invocation varies by environment)
metrics: [total_revenue]
group_by: [order_date__month, customer__country]
where: "status = 'completed'"
time_range: '2024-01-01 ~ 2024-03-31'
order: [order_date__month]
limit: 100

Time Granularity and Time Spine

A time dimension is interpreted via the combination of its defined time_granularity and defaults.agg_time_dimension. If the request's group_by specifies monthly grain, day-grain measures roll up to month. Specifying a period on the metric side restricts the aggregation window according to time_range.

When you want missing periods to appear as zero rows, MetricFlow references a time spine (a continuous baseline date series) to fill the gaps. For example, even if there are zero orders in February, the report can still show February as 0.

• Do not leave agg_time_dimension unset
• Make the time grain in group_by explicit (e.g. order_date__month)
• For cumulative or moving averages, verify the original measure grain is consistent with the window definition

Time dimension and cumulative metric definition example

semantic_models:
  - name: orders
    model: ref('stg_orders')
    defaults:
      agg_time_dimension: order_date
    dimensions:
      - name: order_date
        type: time
        type_params:
          time_granularity: day
    measures:
      - name: order_total
        agg: sum
        expr: amount

metrics:
  - name: revenue_7d
    type: cumulative
    type_params:
      measure: order_total
      window: 7 days
    filter: status = 'completed'

Entities and Join Path Resolution

MetricFlow picks the shortest join path that reaches the requested dimension, based on each entity's primary/foreign designation. Getting from orders to customers and then regions requires a chain like orders.customer → customers.customer → customers.region → regions.region. Even when multiple paths exist, designing entities so they can be traversed uniquely prevents incorrect joins.

Mixing pre-aggregated or summary tables with detail tables tends to introduce entity grain mismatches. Effective countermeasures include routing through a bridge table to uniquify many-to-many relationships, or defining derived semantic models to align grain.

• primary refers to the table's natural key; foreign refers to an external reference key
• Resolve many-to-many through intermediate models; avoid direct joins
• Specifying an unreachable group_by leads to resolution failures or unwanted Cartesian expansion

Definition example when multiple join paths exist

semantic_models:
  - name: customers
    model: ref('dim_customers')
    entities:
      - name: customer
        type: primary
      - name: region
        type: foreign
    dimensions:
      - name: country
        type: categorical

  - name: regions
    model: ref('dim_regions')
    entities:
      - name: region
        type: primary
    dimensions:
      - name: region_name
        type: categorical

  - name: orders
    model: ref('fct_orders')
    defaults:
      agg_time_dimension: order_date
    entities:
      - name: order
        type: primary
      - name: customer
        type: foreign
    measures:
      - name: order_total
        agg: sum
        expr: amount

# With this setup, group_by: [order_date__month, customers__country, regions__region_name]
# can be resolved via a unique customer → region path

Filters and Order of Operations

MetricFlow applies the measure-definition filter before measurement, then applies metric-level filters at the appropriate aggregation stages. For instance, adding status='completed' to the order_total measure limits every downstream metric to completed orders. In contrast, a filter on the metric side applies only to that specific metric.

Ratio metrics require evaluating the numerator and denominator measures at their respective correct grains before dividing. Adding superfluous dimensions to group_by unintentionally decomposes the ratio, so on the exam, learn to spot the option that maintains "ratio grain alignment".

• Measure-level filters are a strong constraint; keep them minimal and conscious of reuse scope
• Apply metric-level filters per use case; do not confuse them with where
• Watch for double counting with ratios and distinct; keep group_by to the minimum necessary

Example contrasting measure-level and metric-level filters

semantic_models:
  - name: orders
    model: ref('stg_orders')
    measures:
      - name: order_total_completed
        agg: sum
        expr: amount
        filter: status = 'completed'

metrics:
  - name: total_revenue_completed
    type: simple
    type_params:
      measure: order_total_completed

  - name: total_revenue_high_value
    type: simple
    type_params:
      measure: order_total_completed
    filter: amount >= 100

Exam Prep and Practical Checklist

The Analytics Engineer exam tests precise understanding of terminology and the ability to spot grain, join, and time alignment issues. The fastest path is to study with the same lens you use in production reviews. Bake the following checks into development and review and you will hesitate less on the exam too.

• Separation of responsibility between measures and metrics: which one should host the filter
• Is agg_time_dimension set on every model
• Are entity primary/foreign assignments sound; is any group_by unreachable
• For ratio metrics, do numerator and denominator share the same grain
• Does the time-spine gap fill behave as expected
• When reading the generated SQL, are the layers (base aggregation → rollup → final shaping) clearly separated

Generated SQL reading-points snippet

-- Overview: base aggregation → join → rollup → final shaping
with base as (
  select
    order_id,
    customer_id,
    order_date::date as order_date_day,
    amount
  from fct_orders
  where status = 'completed'
),
measures as (
  select
    customer_id,
    date_trunc('day', order_date_day) as d_day,
    sum(amount) as order_total
  from base
  group by 1,2
),
rolled as (
  select
    date_trunc('month', d_day) as d_month,
    sum(order_total) as total_revenue
  from measures
  group by 1
)
select * from rolled
order by d_month
limit 100;

Check Your Understanding

Frequently Asked Questions