Нормализация, витрины и ETL

«Зачем в хранилище денормализуют данные, если нас учили нормализовать?» — вопрос, отделяющий аналитика от студента.

Нормализация оптимизирует базу под быструю и безопасную запись, денормализация — под быстрое чтение и аналитику. Это не «правильно и неправильно», а два разных сценария нагрузки.

Вопрос 1. Что такое нормальные формы и зачем они нужны?

Что проверяет интервьюер. Понимаете ли вы, что у нормализации есть цена. Заучить определения 1НФ–3НФ может каждый; ценно объяснить, почему в OLTP-базе интернет-магазина они нужны, а в витрине для BI — мешают.

Смысл всего этого один: каждый факт хранится ровно в одном месте. Тогда при изменении названия города его не придётся править в миллионе строк и не возникнет состояния, когда половина строк обновилась, а половина нет — так называемых аномалий обновления.

Вопрос 2. Почему в хранилище всё наоборот?

Потому что нагрузка другая. Сравнение, которое стоит проговорить на собеседовании:

Отчёт, которому нужно соединить восемь нормализованных таблиц, читается тяжело и выполняется долго. Поэтому в хранилище строят схему «звезда»: в центре таблица фактов (события с числовыми мерами — продажи, клики), вокруг — таблицы измерений (дата, клиент, товар, канал). Соединений мало, все — от факта к измерению.

-- таблица фактов: одна строка = одна продажа
CREATE TABLE fact_sales (
  sale_id      BIGINT,
  date_key     INT      REFERENCES dim_date(date_key),
  client_key   INT      REFERENCES dim_client(client_key),
  product_key  INT      REFERENCES dim_product(product_key),
  quantity     INT,
  amount       NUMERIC(12,2),
  discount     NUMERIC(12,2)
);

-- измерение: справочник с описательными атрибутами
CREATE TABLE dim_product (
  product_key  INT PRIMARY KEY,
  product_name TEXT,
  category     TEXT,
  brand        TEXT
);

Дальше почти наверняка спросят про гранулярность — уровень детализации одной строки факта. Это первое, что определяют при проектировании: строка = одна позиция чека, один чек или день по магазину? Ошибка в гранулярности означает, что метрики будут либо задваиваться, либо не считаться вовсе.

Вопрос 3. Что такое витрина и как выглядит ETL?

Витрина данных (data mart) — заранее посчитанная таблица под конкретную задачу или отдел: «выручка по дням и категориям», «воронка регистрации по каналам». Она денормализована и агрегирована так, чтобы дашборд открывался за секунду, а не собирал ту же логику заново при каждом обновлении.

Классический процесс наполнения — ETL: Extract (забрать из источников), Transform (привести к общему виду, посчитать), Load (положить в хранилище). Современный вариант — ELT: сырые данные сначала грузят в хранилище, а трансформации выполняют уже внутри него силами самой СУБД. ELT выигрывает, когда хранилище мощное и хочется сохранить сырой слой на случай, если логика расчёта изменится.

Слои, которые обычно называют на собеседовании:

1. Raw / staging — сырые данные «как есть», без изменений. Нужен, чтобы можно было пересчитать всё заново.
2. ODS / core — очищенные и типизированные данные, приведённые к единым справочникам.
3. DDS — модель «звезда»: факты и измерения.
4. Data marts — витрины под отделы и дашборды.

Ещё три вещи, которые почти всегда спрашивают следом:

• Инкрементальная загрузка — грузить не всю таблицу, а только новое и изменённое (по updated_at или CDC). Полная перезагрузка витрины на 500 млн строк каждую ночь просто не успеет.
• Идемпотентность — повторный запуск на тех же данных должен давать тот же результат, без задвоения. Обычно реализуется удалением партиции за день и повторной вставкой.
• SCD Type 2 — как хранить историю изменений измерения: клиент переехал из Казани в Москву, а старые заказы должны остаться привязанными к Казани. Для этого в измерение добавляют valid_from, valid_to и флаг актуальности.

Типичные ошибки кандидатов

• Пересказывают определения нормальных форм, но не могут объяснить, зачем в витринах их нарушают.
• Говорят «денормализация — плохая практика». В аналитическом хранилище это стандарт.
• Не упоминают гранулярность таблицы фактов — а это первый вопрос при проектировании.
• Не знают про инкрементальную загрузку и предлагают каждую ночь пересчитывать всё.
• Путают ETL и ELT или считают, что это одно и то же с переставленными буквами.

Как ответить кратко

«Нормализация — это про запись: каждый факт хранится один раз, поэтому нет аномалий обновления. Она нужна в боевой OLTP-базе. В аналитическом хранилище нагрузка обратная — сканируем миллионы строк, а не правим одну, поэтому данные денормализуют в схему "звезда": таблица фактов с мерами плюс измерения-справочники. Витрина — это уже готовая агрегированная таблица под конкретный дашборд. Наполняется она ETL или ELT по слоям raw → core → звезда → витрины, обязательно инкрементально по updated_at и идемпотентно, чтобы повторный запуск не задвоил данные. Историю изменений в измерениях храню через SCD Type 2».