11 вопросов по теме «Data Engineering: Моделирование данных» на собеседовании

Короткий ответ: OLTP обслуживает короткие транзакции — вставку и обновление отдельных строк с низкой задержкой, а OLAP отвечает на аналитические запросы, сканирующие миллионы строк. OLTP хранит данные построчно, OLAP — колоночно. Тяжёлую аналитику не гоняют на боевой OLTP-базе, потому что она конкурирует за ресурсы с транзакциями и роняет latency продакшена.

Подробно:

1. Профиль нагрузки — OLTP: много мелких операций (INSERT/UPDATE по первичному ключу); OLAP: мало тяжёлых запросов с GROUP BY и агрегациями.
2. Модель хранения — построчное хранение читает всю строку целиком (удобно для транзакций), колоночное читает только нужные столбцы и лучше сжимается (удобно для аналитики).
3. Нормализация — OLTP нормализован (3NF) ради целостности при записи, OLAP денормализован ради скорости чтения.
4. Изоляция ресурсов — аналитический скан забивает буферный кэш и диск, из-за чего транзакции начинают ждать.

⚠️ Частая ошибка: гонять отчётность прямо по продакшен-базе. Данные выгружают в отдельное хранилище (ETL/ELT), чтобы аналитика не мешала транзакциям.

Короткий ответ: Размерное моделирование раскладывает данные на факт-таблицы (измеримые события бизнеса) и таблицы-измерения (контекст: кто, что, когда, где). Звезда — это одна факт-таблица в центре, окружённая денормализованными измерениями. Она бьёт полностью нормализованную модель на аналитике, потому что требует меньше джойнов и понятна аналитику.

Подробно:

1. Факт-таблица — хранит числовые метрики (сумма, количество) и внешние ключи на измерения; строк много, столбцов мало.
2. Измерения (дименшны) — описательные атрибуты для фильтрации и группировки (дата, товар, клиент); строк мало, столбцов много.
3. Грейн (гранулярность) — уровень детализации одной строки факта; объявляется в первую очередь.
4. Почему звезда — предсказуемые джойны «факт → измерение», простые запросы, оптимизатор хорошо с ней работает.

               ┌────────────┐
               │  dim_date  │
               └──────┬─────┘
                      │
┌────────────┐   ┌────┴───────┐   ┌──────────────┐
│ dim_product│───│ fact_sales │───│ dim_customer │
└────────────┘   │  метрики + │   └──────────────┘
                 │   FK dim   │
               ┌─┴────────────┘
               │
         ┌─────┴──────┐
         │ dim_store  │
         └────────────┘

⚠️ Частая ошибка: не объявить грейн. Без чёткой гранулярности в факт-таблицу попадают строки разного уровня, и метрики задваиваются при агрегации.

Короткий ответ: В звезде измерения денормализованы — каждое измерение это одна плоская таблица. В снежинке измерения нормализованы и разбиты на иерархию связанных таблиц (товар → категория → отдел). Снежинка экономит место и убирает дублирование, но добавляет джойны и усложняет запросы; на современных колоночных хранилищах чаще выбирают звезду.

Подробно:

• Звезда — измерение хранит всю иерархию в одной таблице (денормализация); меньше джойнов, проще SQL, быстрее на чтение.
• Снежинка — иерархия разложена по нескольким таблицам (нормализация); меньше дублирования, легче поддерживать справочники, но больше джойнов.
• Компромисс денормализации — платим местом и риском рассинхрона ради скорости и простоты. На колоночных витринах место дёшево, а лишние джойны дороги — поэтому звезда обычно выигрывает.

⚠️ Частая ошибка: нормализовать измерения ради экономии гигабайтов на хранилище, где место дёшево, — расплачиваясь за это джойнами в каждом аналитическом запросе.

Короткий ответ: SCD (медленно меняющиеся измерения) описывают, как хранить изменения атрибутов измерения во времени. Type 1 перезаписывает значение (истории нет), Type 2 создаёт новую версию строки (полная история), Type 3 хранит предыдущее значение в отдельной колонке (ограниченная история). SCD2 — стандарт, когда важно видеть, как выглядели данные на момент события.

Подробно:

1. Type 1 — UPDATE поверх старого значения. Просто, но история теряется.
2. Type 2 — новая строка с суррогатным ключом, датами действия (valid_from/valid_to) и флагом is_current. Факты ссылаются на версию, актуальную на дату события.
3. Type 3 — колонка previous_value. Хранит только один шаг назад.

-- SCD2: закрываем старую версию, новую вставляем отдельным INSERT
MERGE INTO dim_customer AS tgt
USING staging_customer AS src
  ON tgt.customer_nk = src.customer_nk
 AND tgt.is_current = TRUE
WHEN MATCHED AND tgt.city <> src.city THEN
  UPDATE SET tgt.valid_to   = CURRENT_DATE,
             tgt.is_current = FALSE
WHEN NOT MATCHED THEN
  INSERT (customer_sk, customer_nk, city,
          valid_from, valid_to, is_current)
  VALUES (GENERATE_SK(), src.customer_nk, src.city,
          CURRENT_DATE, DATE '9999-12-31', TRUE);

⚠️ Частая ошибка: применить SCD1 там, где нужна история. Перезапись «города клиента» задним числом переписывает и все прошлые отчёты — событие привяжется к новому значению.

Короткий ответ: Три типа факт-таблиц: транзакционная (одна строка на событие), периодический снимок (срез метрик за период — день, месяц) и накопительный снимок (одна строка на процесс, обновляется по мере прохождения этапов). Выбор типа диктует грейн, а грейн объявляют до того, как выбирать столбцы.

Подробно:

1. Транзакционная — строка на каждое событие (продажа, клик); самый детальный грейн, растёт быстрее всех.
2. Периодический снимок — регулярный срез (остаток на складе на конец дня); строки не обновляются, удобно для трендов.
3. Накопительный снимок — строка на экземпляр процесса (заказ), колонки-даты этапов заполняются по мере движения (создан → оплачен → отгружен); строку обновляют.
4. Почему грейн первым — грейн задаёт, что означает одна строка; без него метрики разной детализации смешиваются и задваиваются.

⚠️ Частая ошибка: проектировать столбцы факт-таблицы до объявления грейна — потом окажется, что метрики нельзя сложить без двойного счёта.

Короткий ответ: Хранилище данных (warehouse) хранит структурированные смоделированные данные для BI с жёсткой схемой (schema-on-write). Дата-лейк хранит сырые файлы любого формата дёшево на объектном хранилище (schema-on-read). Лейкхаус добавляет к озеру слой с ACID-транзакциями и таблицами (Delta/Iceberg/Hudi), совмещая дешёвое хранение озера с надёжностью и производительностью витрины.

Подробно:

• Warehouse — Snowflake/BigQuery/Redshift; данные очищены и смоделированы, отличный BI и SQL, но дороже и хуже под сырьё и ML.
• Data lake — S3/GCS с Parquet/JSON; дёшево и гибко, хранит всё, но без транзакций легко превращается в «болото» (data swamp).
• Lakehouse — табличные форматы (Delta Lake, Apache Iceberg, Hudi) поверх объектного хранилища; дают ACID, time travel и schema enforcement прямо на файлах.

⚠️ Частая ошибка: сваливать сырьё в озеро без каталога, схемы и транзакций — через год это неуправляемое «болото данных», из которого никто ничего не может достать.

Короткий ответ: Натуральный ключ — это бизнес-идентификатор из источника (email, ИНН, номер заказа). Суррогатный ключ — искусственный технический идентификатор (обычно целочисленный автоинкремент), не несущий смысла. В хранилище измерения ключуют суррогатами: они стабильны, компактны в джойнах и, главное, позволяют хранить несколько версий одной бизнес-сущности при SCD2.

Подробно:

1. Стабильность — натуральный ключ в источнике может измениться (клиент сменил email); суррогат — никогда.
2. SCD2 — при версионировании один натуральный ключ порождает несколько строк, поэтому первичным ключом измерения должен быть суррогат.
3. Производительность — целочисленный суррогат компактнее и быстрее в джойнах, чем составной или строковый натуральный ключ.
4. Изоляция от источников — смена системы-источника не ломает факты, которые ссылаются на суррогаты.

CREATE TABLE dim_customer (
  customer_sk   BIGINT   PRIMARY KEY,   -- суррогатный ключ
  customer_nk   VARCHAR  NOT NULL,      -- натуральный ключ из источника
  email         VARCHAR,
  valid_from    DATE     NOT NULL,
  valid_to      DATE     NOT NULL,
  is_current    BOOLEAN  NOT NULL
);
-- факт ссылается на суррогат, а не на бизнес-ключ
-- fact_sales.customer_sk -> dim_customer.customer_sk

⚠️ Частая ошибка: сделать натуральный ключ первичным в измерении с SCD2 — при появлении второй версии строки ключ перестанет быть уникальным.

Короткий ответ: Инмон строит сверху вниз: сначала нормализованное корпоративное хранилище (3NF) как единый источник правды, из него — витрины. Кимбалл строит снизу вверх: сразу размерные витрины (звёзды), связанные общими измерениями (шина, conformed dimensions). Инмон дороже и медленнее на старте, но целостнее; Кимбалл быстрее даёт бизнес-ценность. Современный ELT на dbt чаще ближе к Кимбаллу.

Подробно:

• Inmon (сверху вниз) — центральное нормализованное EDW, из него строятся зависимые витрины; сильная целостность, высокая стоимость входа.
• Kimball (снизу вверх) — набор звёзд с общими (conformed) измерениями; быстрый time-to-value, интеграция через шину измерений.
• Где ELT/dbt — сырьё грузят в хранилище (ELT), а трансформации в dbt собирают staging → промежуточные модели → размерные марты. По духу это Кимбалл, но с элементами обоих подходов.

⚠️ Частая ошибка: строить независимые витрины без общих измерений — получаются «силосы», метрики между которыми невозможно сопоставить.

Короткий ответ: Data Vault — метод моделирования для интеграции данных из многих источников с полной историей и аудитом. Он раскладывает данные на три типа сущностей: хабы (бизнес-ключи), линки (связи между ключами) и сателлиты (описательные атрибуты с историей). В отличие от звезды, Data Vault оптимизирован не под запросы аналитика, а под гибкую загрузку и прослеживаемость (data lineage).

Подробно:

1. Hub (хаб) — уникальные бизнес-ключи сущности (customer_id) плюс метаданные загрузки; ядро стабильности.
2. Link (линк) — таблица связей многие-ко-многим между хабами (заказ ↔ клиент).
3. Satellite (сателлит) — контекст и атрибуты, привязанные к хабу или линку, с историей изменений (аналог SCD2).
4. Зачем — легко подключать новые источники без переделки модели, всё историзировано и аудируемо. Аналитики поверх Vault строят звёзды-витрины.

  ┌───────────┐        ┌───────────┐
  │   HUB     │        │   HUB     │
  │ customer  │        │  order    │
  └─────┬─────┘        └─────┬─────┘
        │      ┌──────┐      │
        └──────┤ LINK ├──────┘
               └──┬───┘
     ┌─────────┐  │  ┌─────────┐
     │   SAT   │◄─┴─►│   SAT   │
     │ атрибуты│     │ атрибуты│
     └─────────┘     └─────────┘

⚠️ Частая ошибка: отдавать Data Vault аналитикам как витрину для запросов — из-за обилия джойнов он неудобен для BI; поверх него всё равно строят размерные витрины.

Короткий ответ: One Big Table (OBT) — это одна широкая денормализованная таблица, где факты уже склеены со всеми атрибутами измерений, без джойнов на чтение. На современных колоночных хранилищах, где неиспользуемые колонки не сканируются, OBT даёт очень быстрые и простые запросы. Расплата — дублирование данных, раздувание при обновлении измерений и потеря переиспользуемости измерений.

Подробно:

• OBT — всё в одной таблице; ноль джойнов, простой SQL, идеально для дашбордов и BI-инструментов. Но изменение атрибута измерения приходится размазывать по всем строкам.
• Звезда — факты + отдельные измерения; переиспользуемые дименшны, дешевле хранение, чище SCD, но нужны джойны.
• Когда OBT — стабильные измерения, важна скорость и простота, колоночный движок (BigQuery/Snowflake). Когда звезда — много общих измерений, активный SCD, экономия хранения.

⚠️ Частая ошибка: делать OBT для быстро меняющихся измерений — каждое изменение атрибута требует переписать миллионы строк факта вместо одной строки в дименшне.

Короткий ответ: Партиционирование физически разбивает таблицу на части по значению колонки (чаще по дате), чтобы запрос сканировал только нужные партиции — это partition pruning. Кластеризация (sort key) упорядочивает данные внутри партиции по часто фильтруемым колонкам, чтобы движок пропускал нерелевантные блоки. Вместе они режут объём сканирования, а значит — время и стоимость запроса.

Подробно:

1. Партиционирование — деление по дате/региону; фильтр по ключу партиции отсекает лишние партиции (pruning). Выбирают колонку, которая почти всегда есть в WHERE.
2. Кластеризация / sort key — сортировка данных внутри партиции; по метаданным блоков движок пропускает те, что не подходят под фильтр (block/zone skipping).
3. Гранулярность — слишком мелкие партиции (по часам) плодят мелкие файлы и накладные расходы; слишком крупные — плохо отсекают.

-- BigQuery: партиционирование по дате + кластеризация
CREATE TABLE sales.fact_orders
PARTITION BY DATE(order_ts)          -- отсечение по дню
CLUSTER BY customer_id, product_id   -- пропуск блоков внутри дня
AS SELECT * FROM staging.orders;

-- запрос сканирует лишь 1 партицию из тысяч:
SELECT SUM(amount)
FROM sales.fact_orders
WHERE order_ts >= '2026-06-01'
  AND customer_id = 42;

⚠️ Частая ошибка: партиционировать по колонке с высокой кардинальностью (например, по customer_id) — получаются тысячи крошечных партиций, что замедляет, а не ускоряет запросы.