MetricFlow Advanced Patterns

The five metric types cover most use cases, but real-world metrics require patterns that go beyond basic type selection. Period-over-period comparisons, filtered segment metrics, and time series gap handling are the three patterns that come up repeatedly once you move past simple aggregations.

Period-Over-Period Comparisons

The offset_window parameter on derived metrics shifts a metric back in time. This is the foundation for growth rates, variance analysis, and any “compared to last period” calculation.

The basic pattern compares a metric to its value at a previous point:

metrics:
  - name: bookings_vs_last_week
    label: "Bookings Change vs Last Week"
    type: derived
    type_params:
      expr: bookings - bookings_7_days_ago
      metrics:
        - name: bookings
        - name: bookings
          offset_window: 7 days
          alias: bookings_7_days_ago

The same metric (bookings) appears twice in the metrics list — once at the current time, once shifted back 7 days. The alias is required because MetricFlow needs a way to distinguish them in the expression.

For percentage change, wrap the denominator in NULLIF to handle the cold-start case where last period had no data:

metrics:
  - name: bookings_growth_wow
    label: "Bookings Growth % W/W"
    type: derived
    type_params:
      expr: (bookings - bookings_7_days_ago) / NULLIF(bookings_7_days_ago, 0) * 100
      metrics:
        - name: bookings
        - name: bookings
          offset_window: 7 days
          alias: bookings_7_days_ago

Without NULLIF, a zero-bookings prior period produces a division-by-zero error. NULLIF converts zero to null, and null division returns null — which is the correct answer. “Growth rate is undefined when the prior period had no activity” is more truthful than “growth rate is infinity.”

Common Offset Windows

You can compose multiple offsets in a single derived metric for trend analysis:

metrics:
  - name: revenue_trend
    label: "Revenue Current vs 1W vs 4W"
    type: derived
    type_params:
      expr: >
        revenue - revenue_1w AS delta_1w,
        revenue - revenue_4w AS delta_4w
      metrics:
        - name: revenue
        - name: revenue
          offset_window: 7 days
          alias: revenue_1w
        - name: revenue
          offset_window: 28 days
          alias: revenue_4w

Filtered Metrics

Filters use Jinja templating to reference dimensions through their entity path. This is MetricFlow’s syntax for saying “apply this WHERE clause using the semantic graph.”

Dimension Filters

metrics:
  - name: enterprise_revenue
    type: simple
    type_params:
      measure: revenue
    filter:
      - "{{ Dimension('customer__segment') }} = 'enterprise'"

The Dimension() function resolves to the correct column and table based on the semantic model’s entity relationships. You do not write raw column names — MetricFlow handles the join path from the metric’s semantic model to the dimension’s source.

This is the preferred approach over hardcoding filters in measures. A general revenue measure combined with metric-level filters produces enterprise_revenue, smb_revenue, startup_revenue, and any other segment without defining new measures.

Time Dimension Filters

For time-based filtering with a specific granularity:

filter:
  - "{{ TimeDimension('order__ordered_at', 'month') }} >= '2024-01-01'"

The second argument to TimeDimension() specifies the granularity. This is important because date truncation affects which rows match. Filtering on the day grain versus month grain produces different results when your filter value is a month boundary.

Ratio Metric Filters

Filters on ratio metrics can target the numerator, denominator, or both independently:

metrics:
  - name: mobile_conversion_rate
    label: "Mobile Conversion Rate"
    type: ratio
    type_params:
      numerator:
        name: conversions
        filter:
          - "{{ Dimension('session__device_type') }} = 'mobile'"
      denominator: sessions

This calculates “mobile conversions divided by all sessions.” If you want “mobile conversions divided by mobile sessions,” apply the filter to both:

type_params:
  numerator:
    name: conversions
    filter:
      - "{{ Dimension('session__device_type') }} = 'mobile'"
  denominator:
    name: sessions
    filter:
      - "{{ Dimension('session__device_type') }} = 'mobile'"

The distinction matters. “Mobile conversion rate out of all traffic” and “mobile conversion rate out of mobile traffic” are different metrics with different business meanings. The filter placement makes this explicit.

Handling Nulls in Time Series

Metrics with no data for a time period return null by default. In a daily revenue report, a day with zero sales returns null, not zero. This creates gaps in charts and confuses downstream consumers who interpret null as “data missing” rather than “nothing happened.”

Two parameters fix this:

type_params:
  measure:
    name: revenue
    fill_nulls_with: 0
    join_to_timespine: true

join_to_timespine: true ensures every date in the time range appears in results, even dates with no underlying data. MetricFlow maintains a timespine table (a continuous date sequence) and left-joins your metric results to it.

fill_nulls_with: 0 replaces null values with zeros after the timespine join. Together, they produce complete time series without gaps.

When to use each:

• Both together for operational metrics where zero is meaningful (daily revenue, order count, page views). A day with zero orders is a real data point, not missing data.
• join_to_timespine only (without fill) for metrics where null and zero are different. A sensor that reports no reading is different from a sensor that reports zero.
• Neither for event-based metrics where you only care about periods with activity (campaign performance, feature usage in a product that is not always active).

Timespine Configuration

The timespine table must exist in your dbt project. MetricFlow uses it as the backbone for time series completeness:

# In your dbt project
models:
  - name: metricflow_time_spine
    description: "Continuous date sequence for time series gap filling"

The table is simply a list of dates covering your data range. MetricFlow expects it to exist and uses it automatically when join_to_timespine is enabled.

Combining Patterns

These patterns compose naturally. A period-over-period comparison of a filtered metric with timespine gap filling:

metrics:
  - name: enterprise_revenue_growth_mom
    label: "Enterprise Revenue Growth % M/M"
    type: derived
    type_params:
      expr: >
        (enterprise_rev - enterprise_rev_last_month)
        / NULLIF(enterprise_rev_last_month, 0) * 100
      metrics:
        - name: enterprise_revenue
          alias: enterprise_rev
        - name: enterprise_revenue
          offset_window: 1 month
          alias: enterprise_rev_last_month

The enterprise_revenue metric (itself a filtered simple metric) is used twice at different time offsets. MetricFlow resolves the filter, the offset, and the derivation in the correct order. You express business intent; MetricFlow handles the SQL complexity.

This composability is the core value of defining metrics as code. Each layer builds on the one below it, and changes propagate automatically. Update the enterprise filter on the base metric, and the growth calculation picks it up without modification.