← вся теориятеория к собесу · DWH и моделирование данных

Слои и нормализация DWH

Слои DWH: staging → core → marts

Хранилище делят на слои, и каждый слой это контракт, а не папка для удобства. Собес проверяет, понимаешь ли ты, зачем сырьё отдельно от очищенного, а витрины - отдельно от ядра.

Стержень: поток строго вниз (источник → staging → core → mart), сырьё хранится, витрины пересобираемы, загрузка идемпотентна.

// Формулировки: «какие слои в DWH (data warehouse) и зачем?», «чем ELT отличается от ETL (extract, transform, load)?», «что такое медальон-архитектура?».

Три слоя и их роли

Staging хранит сырьё как в источнике, без трансформаций (иногда плюс техполя загрузки). Смысл - возможность переиграть обработку: если сырьё не сохранено, ошибку не исправить задним числом. Это и есть ELT-подход (extract, load, transform): грузим сырым, трансформируем уже в хранилище.

Core (ODS - operational data store) интегрирует источники: единые ключи, дедупликация, согласованные справочники - здесь живёт «одна версия правды», часто в 3НФ или Data Vault.

// Витрины (marts) денормализованы под чтение: звёзды и широкие таблицы под BI (business intelligence). Их особое свойство - пересобираемость: витрину можно дропнуть и построить заново из core, поэтому в ней не хранят ничего уникального.

// Две школы: Инмон строит сверху вниз - сначала нормализованное 3NF-хранилище, из него витрины; Кимбалл снизу вверх - сразу размерные звёзды под бизнес-процессы, связанные конформными измерениями. Оба реляционны, различие в порядке и нормализации.

staging: слой сырых данных «как в источнике»
ELT: загрузить сырым, трансформировать внутри хранилища

Разделение труда и направление потока

Нормализация в core против денормализации в витринах - не спор, а разделение труда: целостность и отсутствие аномалий обновления обеспечивает нормализованное ядро, скорость чтения - денормализованные витрины.

Поток идёт строго вниз: источник → staging → core → mart. Отчёт, читающий staging напрямую, или витрина, пишущая в core, это дыры в архитектуре, которые рано или поздно всплывут инцидентом.

// «BI поверх staging временно» - классическая ловушка: временное становится вечным и ломается при каждом изменении структуры источника, потому что staging по определению нестабилен.

core / ODS: интегрированный очищенный слой единой правды
data mart: денормализованная витрина под конкретного потребителя

Идемпотентность и медальон

Идемпотентность нужна на каждом слое: повторная загрузка партиции или периода не должна плодить дубли. Достигается через MERGE или перезапись партиций целиком, а не голым INSERT - ретрай загрузки с INSERT удваивает данные.

Медальон-схема (bronze/silver/gold) в lakehouse это те же три слоя другими словами: сырьё → очищенное → бизнес-готовое.

// Ещё антипаттерн - витрина как источник для другой витрины через пять хопов: получается lineage-спагетти, где происхождение числа не проследить и отладка превращается в археологию.

идемпотентность загрузки: повтор не плодит дубли: MERGE/overwrite вместо INSERT
bronze/silver/gold: медальон-слои lakehouse: сырьё → чистое → витрины

Как отвечать: «Зачем DWH делят на слои staging / core / marts?»

Чтобы развести три разные ответственности и не сломать всё сразу при изменении одной. Staging держит сырьё как в источнике это страховка: если трансформация оказалась кривой, я переигрываю её из сохранённого сырья, а не прошу источник отдать данные заново. Core интегрирует источники в единую правду - общие ключи, дедупликация, согласованные справочники, обычно нормализованно, чтобы не было аномалий обновления. Витрины денормализованы под чтение и полностью пересобираемы из core, поэтому их не страшно дропнуть и построить заново. Поток идёт строго вниз, и нарушения - отчёт поверх staging или запись в core из витрины это будущие инциденты. Плюс на каждом слое загрузка идемпотентна, чтобы ретрай не задвоил данные. По сути слои это контракты между этапами, а не просто папки.

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

На чём валят

−Трансформировать при загрузке и не хранить сырьё - ошибку не переиграть.
−BI поверх staging «временно» - временное становится вечным и ломается при каждом изменении источника.
−Денормализовать core «для скорости» - аномалии обновления расползаются по всем витринам.
−Витрина как источник для другой витрины через пять хопов - lineage-спагетти.
−INSERT без идемпотентности: ретрай загрузки = дубли в core.

Проверьте себя

Пять вопросов из банка по этой подтеме. Всего их 11, остальные разбираются в тренажёре.

#normalization_layers1 / 5
Зачем в DWH суррогатные ключи вместо натуральных бизнес-ключей?
A)Суррогатные ключи нужны для того, чтобы строки красиво нумеровались по порядку по мере их добавления в таблицу измерения при загрузке
B)Стабильный ключ вне источника: даёт версии SCD2 и переживает смену бизнес-ключа
C)Натуральные ключи лучше, суррогатные — устаревшая практика DWH
D)Суррогатный ключ — это просто зашифрованная версия натурального бизнес-ключа
показать ответ и разбор
+B)Стабильный ключ вне источника: даёт версии SCD2 и переживает смену бизнес-ключа
// разбор: Суррогат — целочисленный ключ, не зависящий от источника. Он позволяет держать много SCD2-версий одного натурального ключа, переживает смену/слияние бизнес-ключей в источниках и ускоряет джойны. Натуральные ключи бывают составными, меняются и утекают со стороны источника — на них DWH ставить рискованно.
#normalization_layers2 / 5
Чем ELT отличается от классического ETL?
A)ETL трансформирует до загрузки; ELT грузит сырьё и трансформирует в хранилище
B)Аббревиатуры ETL и ELT — синонимичные названия одного процесса
C)ELT не предполагает трансформации данных на выходе — сырьё так и остаётся в хранилище без каких-либо изменений
D)ETL применим к маленьким файлам, а ELT — к очень большим
показать ответ и разбор
+A)ETL трансформирует до загрузки; ELT грузит сырьё и трансформирует в хранилище
// разбор: ETL трансформирует данные до загрузки в хранилище (нужен отдельный движок обработки). ELT сначала грузит сырьё в мощное хранилище и трансформирует уже внутри него средствами SQL. ELT стал популярен с облачными DWH (BigQuery, Snowflake): дешёвые хранение и вычисления делают выгодным держать сырьё и пересчитывать его на месте.
#normalization_layers3 / 5
Чем хранилище данных (DWH) отличается от озера данных (data lake)?
A)Хранилище DWH хранит сырые неструктурированные файлы, а озеро — чистые таблицы
B)DWH — структурированные данные под схему; lake — сырьё любого формата
C)DWH и data lake — это просто два разных названия одного и того же хранилища
D)Data lake работает в оперативной памяти без записи на диск
показать ответ и разбор
+B)DWH — структурированные данные под схему; lake — сырьё любого формата
// разбор: DWH хранит структурированные, вычищенные под заранее заданную схему данные (schema-on-write) и заточен под BI-аналитику. Data lake дёшево хранит сырьё любого формата (логи, json, картинки) и накладывает схему при чтении (schema-on-read). Lakehouse пытается объединить дешёвое хранение озера с транзакционностью и схемой хранилища.
#normalization_layers4 / 5
Как сделать загрузку в таблицу DWH идемпотентной?
A)Добавлять новые строки простым INSERT без каких-либо условий, ключей и проверок прямо в целевую таблицу ядра хранилища данных
B)MERGE/upsert по ключу или перезапись партиции целиком — повтор не создаёт дублей
C)Пересоздавать всю таблицу с нуля при каждой отдельной загрузке
D)Хранить данные в оперативной памяти без записи на диск сервера
показать ответ и разбор
+B)MERGE/upsert по ключу или перезапись партиции целиком — повтор не создаёт дублей
// разбор: Слепой INSERT при повторном запуске (ретрай, бэкфилл) задваивает строки. Идемпотентные приёмы: MERGE/upsert по бизнес-ключу или delete-insert — удалить партицию за период и вставить заново. Тогда сколько бы раз загрузка ни повторилась, результат один и тот же. Перезапись партиции особенно удобна при бэкфилле.
#normalization_layers5 / 5
Что такое подход One Big Table (OBT / wide table) и в чём его трейдофф?
A)Хранить каждую отдельную метрику в своей крошечной таблице по одной колонке в каждой ради максимальной гибкости и нормализованности итоговой схемы
B)Нормализовать все витрины до третьей нормальной формы (3NF)
C)Свести данные в одну широкую денормализованную таблицу: быстрое чтение ценой дублирования
D)Разбить одну таблицу на множество мелких шардов по разным физическим серверам
показать ответ и разбор
+C)Свести данные в одну широкую денормализованную таблицу: быстрое чтение ценой дублирования
// разбор: OBT — держать аналитические данные в одной широкой денормализованной таблице вместо звезды с джойнами. В колоночных СУБД (ClickHouse, BigQuery) это часто быстрее для BI: нет джойнов, а лишние колонки дёшевы за счёт колоночного сжатия. Плата — дублирование и сложность обновления измерений. Компромисс между скоростью чтения и нормализацией.

дальше

Теорию прочитали. Навык ставится повторением

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

Открыть тренажёр Вся тема: DWH и моделирование данных