В Looker (LookML) объясните, когда нужно создавать derived table, а не рассчитывать метрику напрямую в explore, на примере когортного показателя удержания?

Короткий ответ

Derived table нужна, когда метрика требует многошаговой агрегации/предварительного джойна (например, когортное удержание фиксирует дату когорты и отдельно считает активность в последующих периодах), что невозможно выразить одной мерой над базовым explore.

Как это работает подробнее

Меры и измерения LookML работают в рамках одного дерева джойнов на запрос; когортное удержание требует сначала зафиксировать когорту пользователя (например, месяц регистрации), а затем отдельно посчитать активность в каждом последующем периоде относительно неё — такая двухшаговая логика естественно выражается через derived table, которую Looker затем открывает как новый explore.

Пример кода

LookML derived tableКопировать

derived_table: {
  sql:
    SELECT
      date_trunc('month', signup_date) AS cohort_month,
      DATE_DIFF(activity_month, date_trunc('month', signup_date), MONTH) AS period_number,
      COUNT(DISTINCT user_id) AS active_users
    FROM user_activity
    GROUP BY 1, 2 ;;
  persist_for: "24 hours"
}

Что использовать на практике

• Применение. Стройте derived table с явным шагом агрегации (cohort_month, period_number, active_users), кэшируйте результат через persist_for, если вычисление дорогое, джойньте обратно для дашборда; явно документируйте гранулярность derived table для тех, кто будет строить на ней джойны.
• Проверка. сумма active_users при period_number = 0 должна совпадать с количеством новых пользователей, зарегистрированных в этом cohort_month.

Подводные камни

• Частые ошибки. Попытка втиснуть многошаговую когортную логику в одну меру с вложенными CASE и джойнами, приводящая к некорректному размножению строк; отсутствие persist_for для дорогой derived table, из-за чего каждая загрузка дашборда пересчитывает её заново; отсутствие документации гранулярности derived table, ведущее к её неправильному использованию в последующих джойнах.