§ P1 — Архитектура · Production

DWH на Microsoft в 2026

Практическое руководство

В туториалах архитектура DWH выглядит так же, как у Кимбалла: Промежуточный слой, core со звездой, витрины. На реальном проекте, где факты исчисляются миллиардами строк, а источники — 1С, WB и OZON одновременно, учебные рецепты ломаются. Собрали 11 паттернов, которые мы отработали на проекте FMCG + маркетплейсы.

Для кого эта статья. Архитекторы DWH, дата-инженеры, BI-лиды, которые уже читали Кимбалла и знают, что такое dim/fact, SCD2 и star-schema. Здесь — дальше: что происходит, когда хранилище входит в продакшн и начинает жить.

Все примеры обезличены: конкретные имена клиентов, бизнес-логика и объёмы скрыты. Паттерны — реальные, из проекта на SQL Server + SSIS + SSAS Tabular + Python-ETL для компании среднего размера (несколько юрлиц на 1С + продажи на маркетплейсах).

1. Три слоя DWH — но не так, как в учебнике

Классика — три физически разделённых слоя: Промежуточный (сырьё), Core / EDW (нормализованная модель), Data Marts (денормализованные витрины). В нашей архитектуре их тоже три, но реализованы они через соглашения о префиксах и представления, а не через отдельные базы.

На практике это выглядит так:

Промежуточный — отдельный сервер (linked server), сырые таблицы wb_orders, wb_cards, wb_warehouse_remains. Туда пишет Python-ETL, туда смотрят SSIS-процедуры.
Core — схема dbo, таблицы по префиксам: tr * — измерения, tf * — факты, tb * — мосты M:M, tt * — календарь/время, ts * — системные (настройки, логи, метаданные).
Semantic — два уровня представлений: uni (unified, один-в-один над dbo) и ast (analytical, с бизнес-логикой поверх uni).

Префиксная именование — не красота ради красоты. В IntelliSense SSMS таблицы группируются по префиксу, в схеме сразу видно, что тип объекта — измерение или факт. Поиск tf WB мгновенно показывает все fact-таблицы маркетплейса.

2. Semantic-слой: зачем два уровня VIEW вместо одного

Классическая архитектура: fact/dim → business view или куб. У нас между ними — ещё один технический слой. Вот зачем.

uni.vf WB Продажи. Это один-в-один проекция над dbo.tf WB Продажи. Ничего не джойнится, никаких расчётов. Только переименование sales_id → [Продажа ID], amount → [Сумма продажи], customer_sid → [SID] — на бизнес-термины.

ast.vf WB Продажи. Это бизнес-слой поверх uni. Здесь:

JOIN-ы к измерениям (дата, номенклатура, контрагент)
ISNULL(tr.[Наименование], '(не определено)') — unknown-member для отсутствующих FK
Бизнес-правила: «продажа с префиксом R. Это возврат, маркируем соответственно»
Денормализация: tr Номенклатура.[Категория] добавляется прямо во view, чтобы потребитель не делал JOIN сам

Зачем два уровня? Чтобы можно было рефакторить dbo без ломки потребителей. Если завтра мы решаем разбить tf WB Продажи на две таблицы (по году), мы меняем только uni.vf — SQL внутри. ast.vf и SSAS-модель не замечают изменения.

3. Измерения из нескольких источников — без конфликта ключей

Классический вопрос: у нас номенклатура есть в 1С, в WB и в OZON. Должны ли быть три разные таблицы? Или одна?

Одна — с композитным ключом:

CREATE TABLE [dbo].[tr Номенклатура](
    [ID]           INT IDENTITY PRIMARY KEY,     -- surrogate, для JOIN-ов
    [NID]          VARCHAR(100),                  -- natural ID из источника
    [SID]          INT NOT NULL,                  -- source ID: 1=1С, 2=WB, 3=OZON
    [Наименование] NVARCHAR(500),
    [Категория]    NVARCHAR(200),
    [Удалено в WB] DATETIME NULL,                 -- soft delete
    CONSTRAINT UQ_номенклатура_source UNIQUE (NID, SID)
);

tr Источник — отдельная таблица, master-реестр: 1 — 1С Фиксированный, 2 — Wildberries, 3 — OZON. Можно добавлять новые источники, не ломая старые записи.

Плюсы:

Один LEFT JOIN из fact к dim — не три разных
BI-отчёт Продажи по номенклатуре работает сразу для всех источников
Когда один товар продаётся и в 1С, и в WB — в SSAS можно связать их через MID (master ID) и получить единый отчёт

4. SCD через MERGE с soft delete (не SCD2)

Тип 2 SCD — красиво в теории: каждая версия записи хранится с valid_from / valid_to. На практике для 90% измерений это overkill: вы никогда не спрашиваете «какая была категория этого товара в январе прошлого года». А SCD2 даёт дополнительные JOIN-условия во всех фактовых запросах.

На нашем проекте подход такой: SCD1 (overwrite) + soft delete. Один MERGE на каждое измерение:

MERGE dbo.[tr WB Номенклатура] AS target
USING (SELECT * FROM staging.wb_cards) AS src
      ON target.NID = src.nm_id AND target.SID = 2  -- SID=2 это WB

WHEN MATCHED THEN
    UPDATE SET [Наименование] = src.title,
               [Категория]    = src.subject_name,
               [Обновлено]    = GETDATE()

WHEN NOT MATCHED BY TARGET THEN
    INSERT (NID, SID, [Наименование], [Категория], [Обновлено])
    VALUES (src.nm_id, 2, src.title, src.subject_name, GETDATE())

WHEN NOT MATCHED BY SOURCE AND target.SID = 2 AND [Удалено в WB] IS NULL THEN
    UPDATE SET [Удалено в WB] = GETDATE();

Третья ветка — ключевая. Если товар был в WB, но исчез в последней выгрузке, мы не удаляем запись, а проставляем [Удалено в WB]. BI-отчёты фильтруют по WHERE [Удалено в WB] IS NULL, исторические факты продолжают джойниться с полным измерением.

Для тех 15% измерений, где история важна (например, история цен), делаем отдельную fact-таблицу tf Цены с [Версия Дата Начала] / [Версия Дата Конца]. Это degenerate SCD2 на уровне факта, а не измерения — проще и быстрее.

5. Партиционирование + Columnstore: partition SWITCH как основной паттерн загрузки

Классический подход: пришли новые данные — DELETE + INSERT в fact-таблицу. На малых объёмах работает. На сотнях миллионов строк начинается боль: columnstore-индекс (CCI) накапливает delete bitmap, каждый следующий запрос читает bitmap + реальные данные, производительность падает по экспоненте.

Решение — партиционирование по дате + Промежуточный-зеркало + partition SWITCH. Паттерн 5-фазный:

Дедупликация источника. Batch-ы в Промежуточный append-only, за (аккаунт, дата) может быть несколько. Берём MAX(batch_id), остальные игнорируем. Это даёт идемпотентность.
Resolution измерений. JOIN-ы к tr * с OUTER APPLY SELECT TOP 1 (fallback по наименованию, если по коду не нашли).
Создание Промежуточный-зеркала. [tf WB Продажи _Staging] с идентичной структурой, тем же CCI, той же partition scheme.
Partition SWITCH цикл. По каждой затронутой партиции ($PARTITION.[pf Даты](@date)): SWITCH из main в Промежуточный (metadata-only, мгновенно), DELETE перезагружаемых дат в Промежуточный, INSERT новых с ORDER BY [Дата] (для лучших rowgroup-ов CCI), ALTER INDEX ... REBUILD PARTITION (физически удаляет delete bitmap), SWITCH обратно.
Финализация. Обновить ts Глубина обновления, залогировать выполнение.

Результат: основная fact-таблица никогда не содержит delete bitmap. Каждый REBUILD — локальный, по одной партиции, не затрагивает исторические данные. Загрузка дня данных занимает секунды, а не минуты, и не блокирует читателей.

6. Единая partition function — одна на всё хранилище

pf Даты — одна partition function на весь DWH, с ежедневной гранулярностью. Все fact-таблицы, все Промежуточный-копии — сидят на одной ps Даты. Это обязательное условие для SWITCH: source и target партиции должны использовать одну и ту же partition scheme.

Файл pf Даты.sql — ~10 000 границ (по одной на день с 2015 по 2035), весит 160 КБ. В PreDeployment он выполняется один раз. Дальше партиции просто существуют в каталоге, пустые — ничего не стоят.

7. Идемпотентность через batch_id и MAX(batch_id)

ETL в Python пишет в Промежуточный append-only: каждый запуск генерирует уникальный batch_id = int(time.time() - 1577836800) (unix-time со смещением от 01.01.2020, чтобы поместилось в INT). Этот же batch_id проставляется на каждую выгруженную строку.

При загрузке в core — INNER JOIN (SELECT MAX(batch_id) FROM staging GROUP BY account_id, order_dt) — берём только последний снимок для пары (аккаунт, дата). Все предыдущие — отбрасываются автоматически.

Что это даёт:

ETL можно запускать сколько угодно раз — данные не дублируются
Если выгрузка упала на середине — просто перезапустить с теми же параметрами, старый batch игнорируется
Для snapshot-таблиц (остатки, цены) — TOP 1 WITH TIES OVER (PARTITION BY account, date ORDER BY COUNT(*) DESC) — выбор «самого полного» batch-а как канонического снимка дня
Восстановление исторической даты из batch_id: DATEADD(SECOND, batch_id + 1577836800, '1970-01-01')

8. Metadata-driven ETL: 4 системные таблицы, которые снимают головную боль

Взрослый DWH хранит метаданные о самом себе. Минимум — четыре таблицы:

ts Настройки — глобальные параметры с типизированным значением (SQL_VARIANT). Примеры: ПРОДАЖИ.ДАТАНАЧАЛА_ГРАНИЦА = '2023-01-01', ГЛУБИНА_ОБНОВЛЕНИЯ_ДНЕЙ = 90, ALERT_EMAIL = 'hello@deeone.dev'. Функция fs Настройка(@Key, @Subkey) используется везде в хранимках вместо hard-coded констант.
ts Глубина обновления — watermark last-successful-load по каждой процедуре. MERGE при успешном завершении: {pu, объект, ДатаНачала, ДатаКонца, LastRun}.
ts Логирование выполнения процедур — append-only event log. {ID, pu, StartTime, EndTime, DurationSec, RowsAffected, Status, ErrorMsg}. Для алертов и анализа деградации производительности.
ts Данные — зашифрованные секреты (SMTP-пароли, FTP-креды) через EncryptByCert. Никаких plaintext в SSIS-пакетах или конфигах.

Эти четыре таблицы окупаются на первом же инциденте. Без них каждый сбой в продакшене превращается в раскопки: «когда последний раз обновлялись продажи?», «почему процедура упала вчера?», «где пароль для SMTP?». С ними — один SELECT.

9. Живая документация: каталог мер из SSAS через OPENQUERY

В любом зрелом DWH документация отстаёт от реальности. В SSAS Tabular 200+ мер DAX, в wiki — устаревшее описание 50 из них.

Решение — читать модель напрямую:

INSERT INTO dbo.[ts Справочник мер]
SELECT
    m.[Name]          AS [Мера],
    t.[Name]          AS [Таблица],
    m.[Expression]    AS [Формула DAX],
    m.[Description]   AS [Описание],
    m.[FormatString]  AS [Формат]
FROM OPENQUERY([$(olap_srv)], '
    SELECT * FROM $SYSTEM.TMSCHEMA_MEASURES
') m
INNER JOIN OPENQUERY([$(olap_srv)], '
    SELECT * FROM $SYSTEM.TMSCHEMA_TABLES
') t ON m.[TableID] = t.[ID];

Эта процедура запускается раз в сутки. Результат — живой реестр всех мер, включая DAX-формулы и описания. Power BI-отчёт для аналитиков — «каталог KPI компании» — построен прямо поверх ts Справочник.

10. Schema evolution: когда WB добавил новое поле в JSON

Маркетплейсы регулярно добавляют новые поля в API (появился cashback_amount, installment_cofinancing). Если ETL написан «в лоб» — скрипт упадёт с KeyError или, наоборот, молча проигнорирует новое поле.

В нашем подходе — schema evolution в runtime:

Перед INSERT в Промежуточный скрипт делает SELECT FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = ?
Сравнивает список колонок DataFrame (pandas) с колонками таблицы
Для отсутствующих колонок выводит тип по словарю (INT_COLS, FLOAT_COLS, BOOL_COLS, DATETIME_COLS — сопровождаемый вручную)
Выполняет ALTER TABLE ADD COLUMN ...
Теперь INSERT проходит

Несовместимые изменения (сменился тип, переименовали поле) — всё равно требуют ручного вмешательства. Но 90% случаев (просто новое поле) разруливаются автоматически, мы узнаём о них из логов через день.

11. Parquet как raw-слой: reproducibility за копейки

До того как писать данные в Промежуточный SQL, Python-ETL сохраняет raw-JSON в Parquet: raw_data/{method}_{account}_{batch_id}.parquet. Плюс флаг _SUCCESS в конце batch-а.

Что это даёт:

Reproducibility: если загрузка в Промежуточный упала, можно переиграть из Parquet — не обращаясь к API повторно (важно при WB с его rate limit 1 req/min)
Debug: если в core попали странные цифры, можно открыть Parquet в pandas и посмотреть, какой сырой ответ пришёл от API
Data lineage: для любой строки в core можно проследить, из какого batch-а она пришла, и найти соответствующий Parquet-файл
Долгосрочное хранение: Parquet сжат лучше SQL (для текстовых данных — в 10-20 раз), можно хранить 5+ лет истории на файловой системе

Что из этого забрать в свой проект

Если вы строите DWH с нуля — берите всё. Если развиваете существующий, вот приоритеты:

Partition SWITCH — если у вас fact-таблицы > 50 млн строк и они тормозят.
Двухуровневый semantic (uni/ast) — если рефакторинг физической модели ломает отчёты каждый раз.
Composite key NID+SID — если у вас несколько источников с пересекающимися сущностями.
4 metadata-таблицы — самое быстрое улучшение, можно добавить за день.
Parquet raw-layer — если источник медленный (API с rate limit) и приходится часто пересчитывать Промежуточный.

Ничего нового здесь — все эти паттерны есть в документации Microsoft и книгах Кимбалла. Разница в том, что в production они работают вместе, на одном проекте, а не по отдельности.

Хотите применить подобные паттерны на своём проекте? Мы проводим аудит DWH-архитектуры за 5 дней — с конкретными рекомендациями, что исправить в первую очередь, какой эффект это даст в часах экономии и рублях. Или запишитесь на 30-минутный звонок — разберём вашу архитектуру, покажем узкие места.