Derived metrics и saved queries

В прошлом уроке мы кратко познакомились с derived metrics — формулами поверх других metrics. В этом уроке углубимся в их возможности (filters, dimension-aware expressions) и узнаем про saved queries — pre-computed queries для production performance и переиспользования.

Derived metric — детально

Базовый derived:

metrics:
  - name: revenue
    type: simple
    type_params:
      measure: order_amount

  - name: refunds
    type: simple
    type_params:
      measure: refund_amount

  - name: net_revenue
    description: "Revenue minus refunds"
    type: derived
    type_params:
      expr: "revenue - refunds"
      metrics:
        - name: revenue
        - name: refunds

expr — формула. metrics — список metric’ов, на которые ссылается expr. Имена в expr должны match’иться с metric names.

Compiled SQL:

WITH
  revenue_subquery AS (
    SELECT
      DATE_TRUNC('day', order_date) AS metric_time__day,
      SUM(amount) AS revenue
    FROM analytics.fct_orders
    GROUP BY 1
  ),
  refunds_subquery AS (
    SELECT
      DATE_TRUNC('day', order_date) AS metric_time__day,
      SUM(refund_amount) AS refunds
    FROM analytics.fct_orders
    GROUP BY 1
  )
SELECT
  r.metric_time__day,
  r.revenue - rf.refunds AS net_revenue
FROM revenue_subquery r
LEFT JOIN refunds_subquery rf USING (metric_time__day)

Аггрегирует numerator и denominator отдельно, потом combines.

Derived с filter — фильтрация внутри metric

Часто нужно фильтровать внутри metric:

metrics:
  - name: paid_customer_revenue
    description: "Revenue from customers in paid tier"
    type: derived
    type_params:
      expr: "revenue"
      metrics:
        - name: revenue
          filter: "{'{{ Dimension(\'customers__tier\') }}'} = 'paid'"

Compiled SQL добавит WHERE:

SELECT
  DATE_TRUNC('day', order_date) AS day,
  SUM(o.amount) AS paid_customer_revenue
FROM analytics.fct_orders o
JOIN analytics.dim_customers c ON o.customer_id = c.customer_id
WHERE c.tier = 'paid'
GROUP BY 1

{'{{ Dimension(...) }}'} — Jinja-syntax для ссылки на dimension. Это используется в filter context.

Множественные фильтры

metrics:
  - name: paid_us_revenue
    type: derived
    type_params:
      expr: "revenue"
      metrics:
        - name: revenue
          filter: |
            {'{{ Dimension(\'customers__tier\') }}'} = 'paid'
            AND {'{{ Dimension(\'customers__country\') }}'} = 'US'

Derived с time offset

Сравнить с прошлым периодом — типичная задача:

metrics:
  - name: revenue_yoy_growth
    description: "Year-over-year growth: (this year - last year) / last year"
    type: derived
    type_params:
      expr: "(revenue - revenue_prior_year) / revenue_prior_year * 100"
      metrics:
        - name: revenue
        - name: revenue
          alias: revenue_prior_year
          offset_window: 1 year

Один metric revenue ссылается дважды:

1. Текущий период (без offset).
2. С offset_window: 1 year — данные сдвинутые на год назад.

MetricFlow генерирует две aggregations с time shift и subtracts:

WITH
  current_period AS (
    SELECT DATE_TRUNC('month', order_date) AS month, SUM(amount) AS revenue
    FROM fct_orders
    GROUP BY 1
  ),
  prior_period AS (
    SELECT DATE_TRUNC('month', order_date + INTERVAL '1 year') AS month, SUM(amount) AS revenue
    FROM fct_orders
    GROUP BY 1
  )
SELECT
  c.month,
  (c.revenue - p.revenue) / p.revenue * 100 AS revenue_yoy_growth
FROM current_period c
LEFT JOIN prior_period p USING (month)

Время на JOIN — p.month — это сдвинутый назад на год order_date. То есть row для January 2026 в prior_period содержит данные от January 2025.

Saved queries — pre-computed queries

Часто одна и та же query запрашивается много раз: revenue by month, revenue by region. Каждый раз — fresh compile + warehouse run. На больших объёмах — медленно и дорого.

Saved query — это переиспользуемая декларация query: «вот стандартный набор параметров (metrics + group_by + filter), используйте этот endpoint».

# models/semantic_models/sq_executive.yml
saved_queries:
  - name: executive_revenue
    description: "Monthly revenue by region for executive dashboard"
    query_params:
      metrics:
        - net_revenue
        - total_orders
        - aov
      group_by:
        - metric_time__month
        - customers__region
      where:
        - "{'{{ Dimension(\'orders__status\') }}'} = 'completed'"

Этот query — saved. Consumers могут запросить через имя:

df = client.query_saved_query('executive_revenue')

Это alias для (metrics + group_by + where). Удобно для:

• Стандартизация: BI tool не знает internal structure, ссылается на saved query.
• Refactoring: меняем что в saved query, все consumers обновлены автоматически.
• Документация: saved query — это «именованный официальный отчёт».

Exports — материализация saved query

Saved query сама по себе не материализована. При каждом query — fresh compile. Чтобы материализовать — нужны exports:

saved_queries:
  - name: monthly_revenue_dashboard
    query_params:
      metrics:
        - net_revenue
        - total_orders
        - aov
      group_by:
        - metric_time__month
        - customers__region

    exports:
      - name: mart_monthly_revenue
        config:
          export_as: table       # или view, или incremental_table
          schema: marts
          alias: monthly_revenue_dashboard

При dbt build --select mart_monthly_revenue:

1. dbt компилирует saved query -> SQL.
2. Создаёт таблицу marts.monthly_revenue_dashboard с результатами.
3. Эта таблица обновляется при каждом dbt build.

BI tool ссылается на эту таблицу напрямую:

-- В Tableau
SELECT * FROM marts.monthly_revenue_dashboard

Это по сути pre-aggregation. Query даже не идёт через Semantic Layer — читается готовая таблица. Миллисекунды вместо секунд.

export_as типы

Полный production example

# models/semantic_models/sq_dashboards.yml

saved_queries:
  # Executive dashboard — monthly view
  - name: executive_monthly
    description: "Monthly revenue + key metrics by region для C-level dashboard"
    query_params:
      metrics:
        - net_revenue
        - total_orders
        - aov
        - active_customer_count
      group_by:
        - metric_time__month
        - customers__region
      where:
        - "{'{{ Dimension(\'orders__status\') }}'} = 'completed'"
    exports:
      - name: mart_executive_monthly
        config:
          export_as: table
          schema: marts

  # Sales daily — daily granularity by sales rep
  - name: sales_daily
    description: "Daily sales by rep для sales ops dashboard"
    query_params:
      metrics:
        - net_revenue
        - total_orders
      group_by:
        - metric_time__day
        - orders__sales_rep_id
        - customers__country
    exports:
      - name: mart_sales_daily
        config:
          export_as: incremental_table
          schema: marts

  # Finance — quarterly with cumulative
  - name: finance_quarterly_cumulative
    description: "Quarterly net revenue with YTD"
    query_params:
      metrics:
        - net_revenue
        - revenue_ytd
      group_by:
        - metric_time__quarter
    exports:
      - name: mart_finance_quarterly
        config:
          export_as: table
          schema: marts_finance

В этом setup’е:

• mart_executive_monthly обновляется каждый раз когда dbt build. Tableau подсасывает таблицу — fast.
• mart_sales_daily incremental — только новые дни добавляются. Подходит для daily refresh.
• mart_finance_quarterly — quarterly, в отдельной схеме marts_finance для finance team access.

Когда saved query, а когда нет

Workflow с saved queries

1. Аналитик пишет ad-hoc query через SL:
   dbt sl query --metrics net_revenue --group_by metric_time__month customers__region
   -> видит результат, итерирует

2. Финализирует query — это standard для dashboard:
   Добавляет в models/semantic_models/sq_*.yml как saved_query

3. Добавляет export -> table:
   exports:
     - name: mart_revenue_dashboard
       config:
         export_as: table

4. Запускает dbt build:
   dbt build --select mart_revenue_dashboard
   -> создаёт таблицу marts.mart_revenue_dashboard

5. BI tool ссылается на таблицу:
   В Tableau: data source = marts.mart_revenue_dashboard

6. Daily refresh:
   Cron: dbt build --select +mart_revenue_dashboard
   (с + — обновляет также upstream models если изменились)

7. Iteration:
   Если нужно изменить metric formula или add dimension —
   change в YAML, PR, merge, automatic rebuild на следующем cron.

Это GitOps для metrics.

Caching и refresh

Saved query с export = materialized таблица. Cache — это сама таблица.

Свежесть данных:

• View export: всегда свежее (compute on read).
• Table export: stale между builds. Если build раз в день — данные на сутки stale.
• Incremental: новые периоды сразу, исторические могут быть stale (зависит от unique_key и стратегии).

Update strategy для table exports:

• Full rebuild на каждом build — materialized: table. Дорого если данные большие.
• Incremental — materialized: incremental с unique_key: month. Добавляет только новые периоды.
• Snapshot для historical retention.

API для saved queries

from dbt_sl_client import SemanticLayerClient
client = SemanticLayerClient(...)

# Список доступных saved queries
queries = client.list_saved_queries()
# [
#   {"name": "executive_monthly", "description": "..."},
#   {"name": "sales_daily", "description": "..."},
# ]

# Query saved query
df = client.query_saved_query('executive_monthly')

# Override filters (если query поддерживает)
df = client.query_saved_query(
    'executive_monthly',
    where=["{'{{ Dimension(\'customers__country\') }}'} = 'US'"]
)

Best practices

1. Saved query per dashboard, не per metric. Один dashboard = один saved query (или несколько). Не дробить.
2. Имена с префиксами: dashboard_*, report_*, kpi_* — помогает organize.
3. Documentation в description — без неё через год никто не помнит зачем saved query создан.
4. Cron schedule per export:
   • Executive (slow-changing): daily build OK.
   • Sales operations (fast-moving): hourly build.
5. Версионирование metric definitions: если изменилась бизнес-логика — версия metric (как dbt model versions в module 10), saved query на новую версию.
6. Testing saved queries: dbt sl validate проверяет structure. Reality check — manually query и verify.

Попробуй сам

В вашем dbt-проекте:

1. Создайте saved query в models/semantic_models/sq_dashboards.yml:

saved_queries:
  - name: monthly_revenue_dashboard
    description: "Monthly revenue by month for executive dashboard"
    query_params:
      metrics:
        - total_orders
        - revenue_mtd
      group_by:
        - metric_time__month

    exports:
      - name: mart_monthly_revenue
        config:
          export_as: table

2. Запустите build:

dbt parse
dbt build --select mart_monthly_revenue

3. Запросите таблицу:

dbt show --inline "SELECT * FROM marts.mart_monthly_revenue ORDER BY metric_time__month"

4. Запросите через SL API:

dbt sl query-saved monthly_revenue_dashboard

Оба должны дать одинаковый результат, но первый — мгновенно (читает таблицу), второй — генерирует SQL заново.

5. Измените metric (например, fixed wording). Запустите dbt build --select mart_monthly_revenue — таблица обновится с новыми данными.

Бонус: создайте derived metric revenue_yoy_growth с offset_window: 1 year. Используйте в saved query. Посмотрите compiled SQL — увидите two aggregations с time shift.

Ключевые выводы

1. Derived metric — формула из других metrics через expr. Поддерживает filters, offset_window (для year-over-year), alias.
2. Saved query — переиспользуемая декларация (metrics + group_by + where). Один источник truth для dashboard configuration.
3. Exports материализуют saved query в table/view/incremental_table. Это pre-aggregation для производительности.
4. Workflow: ad-hoc query -> finalize -> saved_query YAML -> export -> dbt build -> BI tool ссылается на таблицу.
5. Caching = таблица: view export refreshes on read, table export stale между builds. Incremental для time series.
6. API: client.list_saved_queries(), client.query_saved_query('name'). Поддерживается override filters.
7. Best practices: per dashboard not per metric, prefixed names, documentation, version metrics, не дублировать логику между metric и SQL.
8. Versioning: derived metrics могут быть версионированы как dbt models (через v1/v2 suffix), saved queries ссылаются на конкретные версии.