Моделирование данных для BI на собеседовании

Зачем моделирование на собесе BI

Хороший дашборд начинается с правильно смоделированной data layer. Без star schema / dimensions / агрегатов дашборды тормозят и сложны для maintenance. На собесе BI-разработчика моделирование спрашивают через кейсы: «спроектируй data model для X».

Слабый ответ — wide denormalized table. Сильный — star schema с правильными gradations, агрегатами, SCD-обработкой.

Star schema (звезда)

Стандарт в BI / DWH.

Структура:

Fact table в центре — измеряемые события (orders, transactions, sessions)
Dimension tables по краям — атрибуты (date, product, customer, region)
Связи только fact ↔ dimension (никаких dim ↔ dim)

Пример для маркетплейса:

            dim_date
                |
dim_customer -- fact_orders -- dim_product
                |
            dim_region

fact_orders колонки:

order_id (PK)
customer_id (FK)
product_id (FK)
region_id (FK)
date_id (FK)
amount (measure)
quantity (measure)

Преимущества:

Простой mental model
Быстрые joins в BI (1-2 join)
Хорошо работает в OLAP БД

Подробнее — Star vs Snowflake schema.

Snowflake schema

Snowflake = star + normalized dimensions (dim ↔ subdim).

dim_date -> dim_quarter -> dim_year

Когда: dimension очень большая или часто обновляется.

Когда не надо: в большинстве BI star schema достаточно. Snowflake усложняет.

Slowly Changing Dimensions (SCD)

Атрибуты dimensions меняются (например, customer-у поменяли tier). Как trackать?

SCD Type 1 (overwrite):

Просто обновить значение
История теряется
Used для not-important changes

SCD Type 2 (history):

Новая запись с новым valid_from / valid_to
Все historical state preserved
Standard for BI

customer_id | tier   | valid_from | valid_to   | is_current
1           | Bronze | 2024-01-01 | 2024-06-30 | 0
1           | Gold   | 2024-07-01 | NULL       | 1

SCD Type 3 (current + previous):

Two columns: current и previous value
Limited history
Rarely used

Подробнее — SCD типы для DE.

Grain (зернистость)

Grain = что один row в fact table represents.

Stable grain:

Один row = один заказ
Один row = один session event
Один row = один customer-day snapshot

Mixing grains в одной fact — антипаттерн. Разные grains → разные fact-таблицы.

Агрегаты и materialized views

Для performance дашбордов.

Aggregate table:

daily_sales_by_region — pre-aggregated daily totals
Updated через ETL (dbt incremental model)
Dashboard читает из aggregate → быстро

Materialized view:

БД-native (PostgreSQL, ClickHouse)
Refresh by schedule
В ClickHouse — AggregatingMergeTree

Подробнее — materialized views в SQL.

Conformed dimensions

Когда несколько fact-таблиц используют одну dim (например, dim_date для и fact_orders, и fact_sessions).

Best practice: одна dim_date на всё DWH. Не дублировать.

Bridge tables (для many-to-many)

Когда нужно many-to-many между fact и dim (например, order может иметь несколько promotions):

fact_orders -> bridge_order_promotion -> dim_promotion

Не идеально для BI (joins сложнее). Часто работают workarounds (denormalize в массив).

Типичные кейсы

«Спроектируй data model для маркетплейса»

fact_orders: order_id, customer_id, product_id, date_id, region_id, amount.
dim_customer: customer_id, tier (SCD2), segment.
dim_product: product_id, category, brand.
dim_date: date_id, day, week, month, quarter, year, weekday.
dim_region: region_id, region_name, country.
Aggregate table: daily_sales_by_region (refresh nightly).

«Где fact_sessions, где fact_orders?»

Two fact tables с conformed dimensions (date, region, customer). Dashboard может combine их через дашборд-level joins.

«Customer status changed — как handle?»

SCD2: new row с new valid_from, old row updated valid_to. Dashboard filtered by current state или historical.

«BI медленный на 1B+ строк»

Star schema (если ещё не).
Aggregate tables.
Partition fact by date.
Index on FK.
Migrate to OLAP (ClickHouse).

Частые ошибки

Wide denormalized table вместо star. Не масштабируется.
SCD type 1 для важных атрибутов. История теряется.
Mixing grains в одной fact. Аналитика ломается.
Без conformed dimensions. Каждый dashboard свой dim_date.
Без aggregates. Каждый refresh — full fact scan.

FAQ

Star или snowflake?

Star для 95% случаев. Snowflake только когда dim очень большая.

dbt для моделирования?

Стандарт в 2026. Staging + intermediate + mart layers. Tests + docs.

Один fact или несколько?

Несколько fact с conformed dimensions. Один wide fact — антипаттерн.

Big dim — какой подход?

Для 100M+ rows dim — snowflake или split (current state в одной, history в другой).

Real-time data в BI?

Через streaming + CDC в OLAP DB. Дашборд читает оттуда. Real-time complete refresh model редок.

Смотрите также

Тренироваться в Telegram