Всем привет, меня зовут Сергей Прощаев и в этой статье расскажу про тестовое задание, которое мы даём кандидатам на позицию аналитика DWH: четыре задачи на оконные функции и SCD2, где кандидаты чаще всего допускают одни и те же ошибки.

Я Tech Lead и руководитель направления Java | Kotlin разработки в FinTech & E‑commerce и преподаю на курсах разработки и архитектуры в ОТУС. К DWH я пришёл со стороны бэкенда: наша команда поставляет события в хранилище, а потом обсуждает с аналитиками, почему витрина расходится с продовой базой. После десятка расследований в жанре «почему выручка разошлась на 6%» я смотрю на SQL кандидата не как на текст запроса, а как на набор гипотез о данных.

И вот что я заметил: кандидат уверенно рассказывает про оконные функции вроде ROW_NUMBER и LEAD, объясняет SCD Type 2 по Кимбаллу, а потом даёт запрос, который на десяти тестовых строках работает, а на проде незаметно задваивает денежные показатели. Ошибка не в синтаксисе — человек просто не задал себе четыре вопроса про данные.

Ниже разберём SQL‑тестовое задание для аналитика DWH: четыре коротких запроса из реального найма.

Все синтаксически корректны и дают правильный результат на контрольных данных. И в каждом спрятана предпосылка, при нарушении которой результат на продовых данных станет неверным.

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

Условие: что видит кандидат

Интернет‑магазин, хранилище на PostgreSQL‑совместимом движке — у нас Greenplum. Задание написано в стиле, близком к ANSI SQL: решать можно на любом диалекте, а конструкции вроде COUNT(*) FILTER или литерала TIMESTAMP '9999-12-31' заменяются эквивалентными с оговоркой об отличиях. Даём две таблицы и одно бизнес‑требование.

1. Стейджинг заказов. Данные приезжают из CDC‑потока, поэтому одна и та же бизнес‑запись может лежать в нескольких версиях.

-- (SQL, PostgreSQL/Greenplum)
CREATE TABLE stg.orders (
    order_id      bigint,
    customer_id   bigint,
    order_ts      timestamp,      -- время оформления заказа
    amount        numeric(18,2),
    status        text,           -- new / paid / cancelled
    updated_at    timestamp,      -- время изменения записи в источнике
    op            char(1)         -- I / U / D из CDC
);

2. Измерение клиентов в SCD2. Историчность ведётся руками, а не через готовый инструмент — так задание честнее проверяет понимание.

-- (SQL, PostgreSQL/Greenplum)
CREATE TABLE dds.dim_customer (
    customer_sk   bigint,         -- суррогатный ключ версии
    customer_id   bigint,         -- бизнес-ключ
    segment       text,           -- retail / vip / wholesale
    region        text,
    valid_from    timestamp,
    valid_to      timestamp,      -- у текущей версии NULL
    is_current    boolean
);

3. Требование бизнеса. Витрина «выручка по сегментам»: сумма оплаченных заказов, где сегмент клиента берётся на момент заказа, а не текущий. Плюс нарастающий итог по дням внутри сегмента.

Оговорка про модель. В задании текущая версия помечена valid_to IS NULL, альтернатива — sentinel‑дата 9999-12-31. Обе рабочие, но выдерживать надо одну во всём пайплайне: смесь NULL и sentinel в одной таблице даёт самые глупые расхождения. Дальше живём с NULL, а в конце покажу практический случай, где sentinel оказывается удобнее.

Чтобы задачи можно было решать не в вакууме, вот вводные данные:

-- (данные) stg.orders
order_id | customer_id | order_ts            | amount | status | updated_at          | op
1001     | 77          | 2026-03-05 10:12:00 | 100.00 | new    | 2026-03-05 10:12:00 | I
1001     | 77          | 2026-03-05 10:12:00 | 100.00 | paid   | 2026-03-05 10:19:31 | U
1002     | 77          | 2026-03-05 14:40:00 | 100.00 | paid   | 2026-03-06 09:00:00 | U
1002     | 77          | 2026-03-05 14:40:00 | 100.00 | paid   | 2026-03-06 09:00:00 | D
1003     | 77          | 2026-03-05 18:02:00 | 100.00 | paid   | 2026-03-05 18:02:00 | I

-- (данные) dds.dim_customer
customer_sk | customer_id | segment | valid_from          | valid_to            | is_current
501         | 77          | retail  | 2025-01-01 00:00:00 | 2026-03-05 14:40:00 | false
502         | 77          | vip     | 2026-03-05 14:40:00 | NULL                | true

Обратите внимание: у заказа 1002 две строки с одинаковым updated_at, а сегмент клиента 77 сменился ровно в момент оформления этого заказа. Обе ситуации — из продовых инцидентов.

Дальше — четыре задачи.

Задача 1. Дедупликация CDC: почему одного ROW_NUMBER недостаточно

Первое, что нужно сделать с stg.orders, — оставить по одной актуальной версии на order_id.

Типичный кандидат пишет:

-- (SQL, PostgreSQL/Greenplum) — вариант кандидата
SELECT *
FROM (
    SELECT o.*,
           ROW_NUMBER() OVER (
               PARTITION BY o.order_id
               ORDER BY o.updated_at DESC
           ) AS rn
    FROM stg.orders o
) t
WHERE rn = 1;

Типовые неправильные ответы

• «Всё корректно, это стандартный паттерн». Самый частый ответ, и он же самый дорогой. Паттерн действительно стандартный, но у него есть предусловие, которое никто не проговаривает: ORDER BY должен задавать строгий порядок.

• «Заменить ROW_NUMBER на RANK». Хуже: RANK при равенстве вернёт несколько строк с рангом 1, и вы получите те самые дубли, от которых избавлялись.

• «Добавить DISTINCT». Лечение симптома: через месяц в источник добавят колонку, строки перестанут быть идентичными, и дубли вернутся.

Правильный ход мысли

Шаг первый: детерминированность сортировки. updated_at в этой схеме имеет точность до микросекунды, но точность и уникальность — разные вещи. CDC‑пайплайн вполне может присвоить нескольким изменениям одной записи одинаковый updated_at: например, если время берётся на уровне транзакции или с меньшей точностью, чем частота изменений. Как у заказа 1002 в наших вводных.

При равенстве ключа сортировки СУБД выбирает победителя произвольно, и на разных запусках это могут быть разные строки. Витрина «плавает» между прогонами — худший вид бага, невоспроизводимый на ретесте.

Лечится тай‑брейкером — дополнительным полем, которое задаёт однозначный порядок событий внутри order_id. Обычно это LSN, пара (partition, offset) Kafka или другой источник, однозначно задающий порядок изменений внутри бизнес‑ключа:

-- (SQL, PostgreSQL/Greenplum) — рабочий вариант
ROW_NUMBER() OVER (
    PARTITION BY order_id
    ORDER BY updated_at DESC, cdc_lsn DESC
)

Оговорюсь сразу: cdc_lsn нет в DDL стейджинга выше. Предполагается, что CDC‑пайплайн передаёт его дополнительно — именно поэтому вопрос «а есть ли у вас такое поле вообще» я и жду от кандидата.

Важно, чтобы тай‑брейкер не просто различал строки, но и упорядочивал их: уникальность и упорядоченность — разные свойства. Случайный UUID уникален, но по нему нельзя сказать, какое из двух событий произошло позже. Дописать order_id в ORDER BY бесполезно: он уже стоит в PARTITION BY, внутри партиции он одинаков у всех строк. Нужно поле, уникальное в пределах истории изменений записи.

Если такого поля нет, правильный ответ — не выбрать молча что‑нибудь, а написать отдельным пунктом: «источник не даёт детерминированного порядка, нужен LSN». Кандидат, который это пишет, сразу переходит у меня в другую категорию.

Шаг второй: удаления. Строка с op = 'D' — это последнее событие изменения заказа, но не то состояние, которое нужно материализовать в current‑state таблицу. Она отлично выигрывает сортировку по updated_at DESC и попадает в витрину как полноценный заказ — как заказ 1002 из вводных.

У меня был случай, когда после отката пакета заказов в источнике витрина показывала эту выручку ещё три недели, просто потому что в дедупликации никто не посмотрел на op.

Фильтр нужен после выбора последней версии, а не до:

-- (SQL, PostgreSQL/Greenplum)
WHERE rn = 1 AND op <> 'D'

Порядок важен. Если отфильтровать op = 'D' внутри подзапроса, вы вытащите предпоследнюю версию удалённой записи и оживите удалённый заказ.

Оговорка, которую я жду от сильного кандидата: удаление отбрасывается при материализации текущего состояния, но tombstone должен остаться в change log. Downstream‑процессам нужно знать не только что записи нет, но и когда она исчезла.

Предпосылка, которую должен проверить кандидат: updated_at однозначно определяет порядок событий, а op не влияет на результат. Обе предпосылки неверны. Навык — понимание, что оконная функция это не сортировка, а контракт на детерминированность.

Задача 2. Нарастающий итог: RANGE против ROWS

Дальше — накопленная выручка по дням внутри сегмента.

Кандидат пишет:

-- (SQL, PostgreSQL/Greenplum) — вариант кандидата
SELECT
    segment,
    order_date,
    amount,
    SUM(amount) OVER (
        PARTITION BY segment
        ORDER BY order_date
    ) AS running_total
FROM mart.daily_orders;

Типовые неправильные ответы

• «Классический running total, всё верно». Верно, если гранулярность результата совпадает с тем, по чему построена рамка окна. Здесь не совпадает: строка результата — заказ, а рамка считается по дате.

• «Значения будут накапливаться по строкам». Интуитивно да, фактически нет.

Правильный разбор

Когда в оконной функции указан ORDER BY и не указана рамка, стандарт SQL подставляет RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW. Ключевое слово — RANGE: рамка считается не по позициям строк, а по значениям ключа сортировки.

Все строки с одинаковым order_date считаются одной точкой, и каждая получает сумму, включающую весь этот день целиком. То есть три заказа по 100 за 5 марта дадут в каждой строке +300, а не 100, 200, 300.

Никакой ошибки, никакого предупреждения — просто другие числа в отчёте.

Два корректных варианта, в зависимости от того, что нужно бизнесу:

-- (SQL, PostgreSQL/Greenplum) — накопление по строкам
SUM(amount) OVER (
    PARTITION BY segment
    ORDER BY order_date, order_id
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
) AS running_total

-- (SQL, PostgreSQL/Greenplum) — накопление по дням, честно
SELECT
    segment,
    order_date,
    SUM(daily_amount) OVER (
        PARTITION BY segment
        ORDER BY order_date
        ROWS UNBOUNDED PRECEDING
    ) AS running_total
FROM (
    SELECT segment, order_date, SUM(amount) AS daily_amount
    FROM mart.daily_orders
    GROUP BY segment, order_date
) d;

Мой вариант в ревью: агрегировать до нужной гранулярности явным GROUP BY, а окно строить поверх. Читается лучше, и следующий человек не будет гадать, что имелось в виду.

Отдельно про производительность. ROWS и RANGE могут стоить по‑разному, потому что задают разные правила формирования рамки. Но конкретная стоимость зависит от СУБД и плана, а не от типа рамки как такового, поэтому проверять её надо через EXPLAIN ANALYZE. В одном из наших ночных расчётов переход на ROWS сократил время примерно на 40%, однако это результат конкретного профиля данных, а не гарантия.

Предпосылка, которую должен проверить кандидат: гранулярность строки результата совпадает с гранулярностью ключа сортировки в окне. Здесь три вещи работают вместе: рамка по умолчанию, семантика RANGE и гранулярность строк. Выпадает любая из трёх — в отчёте другие цифры.

Задача 3. SCD2: point‑in‑time join, где теряются или задваиваются деньги

SCD2 — это не набор колонок valid_from и valid_to. Это набор инвариантов, которые модель обязана сохранять. Сейчас увидим, что бывает, когда об этом не думают.

Задача: соединить заказы с измерением так, чтобы сегмент брался на дату заказа.

Типовой ответ:

-- (SQL, PostgreSQL/Greenplum) — вариант кандидата
SELECT o.order_id, o.amount, c.segment
FROM dds.fct_orders o
JOIN dds.dim_customer c
  ON c.customer_id = o.customer_id
 AND o.order_ts BETWEEN c.valid_from AND c.valid_to;

Прежде чем разбирать дефекты, посмотрите на рис. 2: там показано, что происходит с версией записи при обновлении атрибута в источнике.

Главная мысль рисунка: SCD2 — это не таблица с датами, а набор инвариантов. Обязательный здесь один: для бизнес‑ключа интервалы версий не пересекаются, и valid_from строго меньше valid_to. Держится — point‑in‑time join даёт не больше одной строки, не держится — получаете задвоение, и никакой DISTINCT не спасёт.

А вот непрерывность истории без разрывов — уже бизнес‑правило конкретного измерения, а не свойство SCD2 как такового: если клиент полгода отсутствовал в источнике и для бизнеса это значимо, разрыв корректен.

Дефект первый: NULL в valid_to

У текущей версии valid_to равен NULL. Условие BETWEEN c.valid_from AND c.valid_to для NULL даёт не TRUE и не FALSE, а NULL — строка в join не попадает. Результат: заказы клиентов, у которых сегмент ни разу не менялся, исчезают из витрины. Тихо: строк меньше, суммы меньше, ошибок ноль.

Лечится COALESCE(c.valid_to, TIMESTAMP '9999-12-31') или, что лучше, договорённостью хранить в valid_to “бесконечность” вместо NULL изначально.

Дефект второй: BETWEEN — включающий интервал с обеих сторон

BETWEEN в SQL — это >= и <=. Если при закрытии версии вы записали valid_to предыдущей строки равным valid_from следующей (а так делают почти все, это самый естественный способ), то в момент смены сегмента заказ попадает в обе версии сразу. Одна строка факта, две строки в результате, выручка задвоена.

Вернитесь к вводным данным: заказ 1002 оформлен в 14:40, версия retail закрыта в 14:40, версия vip открыта в 14:40. Этот заказ на 100 рублей превратится в витрине в 200 — по сотне в каждый сегмент. Ровно так у нас однажды разошёлся отчёт по каналам продаж: на 0,3% в целом по компании, но на 15% по одному сегменту, где переклассификация клиентов шла массово.

Правильно — полуоткрытый интервал:

-- (SQL, PostgreSQL/Greenplum) — корректный point-in-time join
SELECT o.order_id, o.amount, c.segment
FROM dds.fct_orders o
JOIN dds.dim_customer c
  ON c.customer_id = o.customer_id
 AND o.order_ts >= c.valid_from
 AND o.order_ts <  COALESCE(c.valid_to, TIMESTAMP '9999-12-31');

Для соседних SCD2-версий BETWEEN опасен именно тем, что включает обе границы. Полуоткрытая модель [valid_from, valid_to) снимает двусмысленность на стыке версий, и я бы предпочёл её в любом проекте, где соседние версии могут иметь общую временную границу. Частота изменений тут ни при чём: атрибут может меняться раз в десять лет, но если версии стыкуются в одной точке, BETWEEN задвоит строку ровно так же.

Проблема производительности: цена range join

Здесь я сам однажды ошибся в формулировке, и меня поправил коллега, так что передам как есть. Соблазнительно сказать «неравенство убивает hash join» — но это неверно. В запросе есть равенство по customer_id, и оптимизатор спокойно построит hash join по нему, а диапазонные предикаты повесит отдельным условием соединения:

Hash Join
  Hash Cond: o.customer_id = c.customer_id
  Join Filter: o.order_ts >= c.valid_from AND o.order_ts < c.valid_to

Проблема не в невозможности hash join, а в стоимости. При таком hash join плане для каждого заказа сначала формируются candidate matches — версии с тем же customer_id из соответствующей корзины, — и только потом Join Filter отбраковывает неподходящие по времени: при глубокой истории число проверяемых пар растёт кратно числу версий. Причём и этот план не единственный — движок может выбрать nested loop или переставить операции. Смотреть надо EXPLAIN ANALYZE, а не тип предиката.

Что помогает: материализовать customer_sk прямо в таблице фактов на этапе загрузки, а не вычислять диапазонный join при каждом чтении витрины.

Вторым шагом можно рассмотреть партиционирование или кластеризацию измерения по временным атрибутам, но универсальным рецептом это не является: выигрыш зависит от диапазонов запросов, распределения версий и того, сработает ли partition pruning, поэтому решение подтверждается планом выполнения, а не общим правилом.

Первое снимает кажущееся противоречие. В классическом подходе Кимбалла диапазонный join — инструмент загрузки: один раз при обогащении факта вы находите нужную версию измерения и пишете её customer_sk.

Чтение витрины идёт после этого обычно уже через материализованный ключ, простым equality join по целому числу. Ровно ради этого суррогатные ключи и существуют — хотя обязательной такая материализация не является, и есть архитектуры, которые обходятся без неё.

Предпосылка, которую должен проверить кандидат: границы интервалов однозначны, а valid_from означает то же время, что и order_ts. Второе особенно коварно. Если valid_from собран из updated_at CDC‑потока, вы фиксируете момент доставки события, а не бизнес‑изменения: клиента перевели в VIP в 14:40:00, событие доехало в 14:40:03, заказ прошёл в 14:40:01 — и технически корректный join даёт бизнес‑неверный ответ.

Неприятная реальность в том, что бизнес‑эффективное время источник отдаёт не всегда: CRM может знать только updated_at. Тогда честный вариант — назвать вещи своими именами и зафиксировать это в контракте данных: в такой модели valid_from — это system time, время, когда изменение стало известно системе, а не business‑effective time, когда оно произошло в бизнесе.

Для отчётности это допустимо ровно до тех пор, пока такая семантика проговорена явно. Плохо, но управляемо. Неуправляемо — когда никто не знает, что там лежит.

Задача 4. Проверьте историю на целостность

Последняя задача проверяет не знание паттерна, а умение формализовать инварианты данных.

Условие: вам передали таблицу dim_customer от смежной команды и сказали «там SCD2, всё корректно».

Напишите запрос, который это проверит.

Типовые неправильные ответы

• «Проверю дубли по customer_id при is_current = true». Направление верное, но проверка неполная в обе стороны: она ловит две текущие версии и не ловит ноль текущих версий. Клиент, у которого закрылись все интервалы, из такой выборки просто выпадет.

• «Сверю количество строк с источником». Это проверка полноты, а не корректности истории: пересекающиеся интервалы количество бизнес‑ключей не меняют.

• «Схема с NOT NULL и первичным ключом всё гарантирует». В Greenplum ограничения работают не так, как в PostgreSQL: UNIQUE и PRIMARY KEY возможны только с оглядкой на ключ распределения, а внешние ключи не проверяются. Но дело даже не в конкретной СУБД: пересечение интервалов — это условие на пару строк, и обычным PRIMARY KEY оно не выражается нигде.

Минимальный набор инвариантов

Для выбранной нами модели SCD2, где одна бизнес‑версия начинается в одной временной точке, я жду как минимум шесть проверок:

1. customer_id заполнен;

2. valid_from заполнен;

3. у каждой закрытой версии valid_from < valid_to; текущая версия под это условие не подпадает и определяется отдельно, через valid_to IS NULL;

4. интервалы соседних версий не пересекаются;

5. пара customer_id + valid_from уникальна;

6. на бизнес‑ключ ровно одна текущая версия — не ноль и не две, это именно версия с максимальным valid_from, и valid_to у неё не заполнен, а у всех закрытых заполнен.

Третий пункт стоит прочитать внимательно. При выбранной модели у текущей версии valid_to равен NULL, поэтому сравнение valid_from < valid_to для неё даёт NULL и истинным быть не может в принципе. Это условие для закрытых версий, а актуальность проверяется отдельным признаком — иначе тест либо молча пропускает текущую строку, либо считает её дефектной.

Про пятый пункт часто забывают, а без него сортировка версий по valid_from недетерминирована и проверка пересечений начинает давать случайные результаты. Тот же принцип, что в задаче 1, только применённый к самому тесту.

Шестой пункт — самый коварный, к нему вернусь отдельно.

Проверка пересечений — как раз про оконные функции:

-- (SQL, PostgreSQL/Greenplum) — проверка пересечений и границ
WITH v AS (
    SELECT
        customer_id,
        customer_sk,
        valid_from,
        COALESCE(valid_to, TIMESTAMP '9999-12-31') AS valid_to,
        LEAD(valid_from) OVER (
            PARTITION BY customer_id
            ORDER BY valid_from, customer_sk
        ) AS next_valid_from
    FROM dds.dim_customer
)
SELECT
    customer_id,
    valid_from,
    valid_to,
    next_valid_from,
    CASE
        WHEN customer_id IS NULL
          OR valid_from IS NULL         THEN 'null_key'
        WHEN valid_from >= valid_to     THEN 'invalid_interval'
        WHEN next_valid_from < valid_to THEN 'overlap'
    END AS defect
FROM v
WHERE customer_id IS NULL
   OR valid_from IS NULL
   OR valid_from >= valid_to
   OR (next_valid_from IS NOT NULL AND next_valid_from < valid_to);

Два момента.

• Первый: ORDER BY valid_from, customer_sk — тот самый тай‑брейкер из задачи 1.

• Второй: проверка на NULL нужна не для красоты. Строка с valid_from IS NULL не удовлетворяет ни одному сравнению и тихо проходит любой тест на неравенствах. Битая строка, не попадающая в отчёт о битых строках, — это проверка, создающая ложное спокойствие.

И здесь же важная оговорка про customer_sk. Он нужен, чтобы порядок строк оставался детерминированным именно при нарушенном инварианте, и он не заменяет сам инвариант.

Наоборот: раз мы договорились, что пара customer_id + valid_from уникальна, двух версий с одинаковым valid_from быть не должно вообще, и наличие тай‑брейкера не даёт права спокойно их упорядочивать. Иначе запрос красиво отсортирует то, чего в корректной модели не существует, и дефект уедет в отчёт как валидная история.

Уникальность проверяем отдельным тестом — окно с ORDER BY valid_from, customer_sk дубли не обнаруживает:

-- (SQL, PostgreSQL/Greenplum) — уникальность пары «бизнес-ключ + начало версии»
SELECT
    customer_id,
    valid_from,
    COUNT(*) AS versions
FROM dds.dim_customer
GROUP BY customer_id, valid_from
HAVING COUNT(*) > 1;

Отдельно — текущая версия. Здесь недостаточно посчитать количество, надо ещё убедиться, что флаг стоит на правильной строке:

-- (SQL, PostgreSQL/Greenplum)
SELECT
    customer_id,
    COUNT(*) FILTER (WHERE is_current)                        AS current_versions,
    COUNT(*) FILTER (WHERE valid_to IS NULL)                  AS open_intervals,
    COUNT(*) FILTER (WHERE is_current AND valid_to IS NULL)   AS current_open_versions,
    MAX(valid_from)                                           AS last_version_from,
    MAX(valid_from) FILTER (WHERE is_current)                 AS current_version_from
FROM dds.dim_customer
GROUP BY customer_id
HAVING COUNT(*) FILTER (WHERE is_current) <> 1
    OR COUNT(*) FILTER (WHERE valid_to IS NULL) <> 1
    OR COUNT(*) FILTER (WHERE is_current AND valid_to IS NULL) <> 1
    OR MAX(valid_from) FILTER (WHERE is_current)
       IS DISTINCT FROM MAX(valid_from);

Группировка идёт по всей таблице, а не по отфильтрованным строкам: иначе клиенты без текущей версии в выборку не попадут, а это самый неприятный случай — витрина молча теряет клиента.

Последнее условие ловит то, что почти всегда пропускают: is_current = true стоит на старой версии, а свежая помечена закрытой. Текущая версия ровно одна, пересечений нет, интервалы валидны — все предыдущие проверки зелёные. И при этом любой отчёт по текущему сегменту показывает прошлогоднее значение. Обычно это следствие ручной правки данных во время инцидента.

Сравнение здесь намеренно через IS DISTINCT FROM, а не через <>. Обычное неравенство при NULL с любой стороны возвращает NULL, а не TRUE, и строка тихо не попадает в отчёт о дефектах — ровно та же ловушка, что абзацем выше. IS DISTINCT FROM даёт NULL‑safe сравнение и срабатывает как надо. В диалектах без него используйте эквивалент вроде NOT (a = b OR (a IS NULL AND b IS NULL)).

Два условия в середине закрывают шестой инвариант со стороны интервала: раз мы выбрали NULL маркером актуальности, флаг и граница обязаны рассказывать одну и ту же историю. И считать их по отдельности недостаточно — здесь легко остановиться на шаг раньше, чем нужно. Возьмите такую пару строк:

customer_id | valid_from | valid_to   | is_current
77          | 2025-01-01 | NULL       | false
77          | 2026-01-01 | 2026-12-01 | true

Текущая версия ровно одна. Открытый интервал ровно один. Флаг стоит на строке с максимальным valid_from. Три условия из четырёх зелёные — а модель сломана: актуальной помечена закрытая версия, открытым висит прошлогодний интервал. Ловит это только прямая проверка is_current AND valid_to IS NULL: признак актуальности и открытая граница должны сойтись в одной строке, а не просто встретиться в пределах бизнес‑ключа по разу.

Если для вашего измерения обязательна непрерывная история, добавьте проверку на разрывы: next_valid_from > valid_to. Повторю, это бизнес‑правило конкретной таблицы, а не универсальное требование SCD2.

Такие запросы стоит оформить как автоматические проверки: в dbt это data tests, в Great Expectations — expectations, а оркестратор вроде Airflow использует их результат как quality gate перед публикацией витрины. Инвариант, который никто не проверяет, рано или поздно перестаёт выполняться.

Помню, как мы нашли в измерении сотрудников 11 тысяч пересекающихся интервалов. Причина смешная: пересчёт истории запускался дважды за ночь после ручного перезапуска упавшего DAG, а MERGE не был идемпотентным. Загрузка отработала «успешно» оба раза. Отчёт по ФОТ завышал цифры полтора месяца, и обнаружил это не мониторинг, а финансовый контролёр со сверкой вручную.

Проблема была не только в самом MERGE: не было идемпотентного ключа загрузки, поэтому повторный запуск не отличал retry от нового изменения в источнике. Инвариант поймал бы последствие, ключ загрузки не дал бы причине возникнуть — нужно и то, и другое.

Как это делают команды в 2026 году

Писать SCD2 руками в 2026 году — сознательное решение, а не единственный путь. Вот что изменилось.

Историчность часто делегируют инструменту. Если проект уже живёт на dbt, MERGE для SCD2 обычно не пишут руками, а берут dbt snapshots.

Актуальная стабильная ветка на август 2026 — 1.12.x (1.12.0 вышел 16 июля 2026), и возможности, появившиеся ещё в 1.9, позволяют закрыть часть практических проблем, разобранных в задачах 1 и 3. Конфиг dbt_valid_to_current пишет в valid_to текущей версии не NULL, а, например, 9999-12-31 — чтобы запросы работали без COALESCE.

Конфиг hard_deletes со значением new_record добавляет строку с флагом dbt_is_deleted вместо тихой потери удалённых записей. Но здесь важно не переоценить: он решает только представление удаления, а требований к детерминированному порядку событий в исходном CDC‑потоке не отменяет. Проблема из задачи 1 остаётся вашей.

Вот и обещанный возврат к выбору модели: к sentinel‑дате в dbt пришли не от хорошей жизни. В issue dbt‑core #10187 люди описывали, как обходили NULL — строили поверх снапшота вью с COALESCE(dbt_valid_to, date(9999,12,31)), чтобы BI‑инструменты и джойны по диапазону не разваливались.

Важная деталь, на которой уже успели обжечься: dbt не переписывает существующие строки задним числом. Включив конфиг на живой таблице, вы получите ту самую смесь NULL у старых записей и 9999-12-31 у новых, и запрос без COALESCE начнёт врать сильнее, чем до изменения. Мигрировать нужно руками.

Iceberg v3 добавил механизм технического row lineage. В спецификации Apache Iceberg версии 3 описаны row lineage с полями rowid и lastupdated_sequence_number для строк таблицы и deletion vectors вместо позиционных delete‑файлов.

Формулировка спецификации тоньше, чем «колонка в каждой записи»: таблица обязана отслеживать row lineage для всех вновь создаваемых строк, но физически файл с новыми строками эти поля может не хранить — значения наследуются и достраиваются при чтении.

Спецификация и релиз библиотеки — разные вещи: полноценная реализация и поддержка ряда возможностей v3 появились в релизе Apache Iceberg 1.11.0 от 19 мая 2026. В AWS поддержка была включена в EMR, Glue и S3 Tables ещё в ноябре 2025, Snowflake объявил GA 7 мая 2026.

По движкам картина неровная: полнее всего у Spark, у Flink и Trino row lineage дорабатывается.

Для части сценариев инкрементальной обработки это позволяет опираться на метаданные Iceberg вместо самодельных хеш‑колонок. «Для части» — не вежливая оговорка: у row lineage есть ограничения, в частности для обновлений через equality deletes. И SCD2 он тем более не заменяет: row lineage отвечает на вопрос «какие строки изменились между коммитами», а витрине нужен ответ на другой — «какой сегмент был у клиента 5 марта». Технический CDC и бизнес‑историчность живут на разных слоях, и кандидат, который их путает, обычно предлагает «просто взять time travel».

Проверки инвариантов стали частью пайплайна. Тесты из задачи 4 гоняются на каждой загрузке и блокируют публикацию витрины. Не потому что модно, а потому что дешевле поймать 11 тысяч пересечений ночью, чем через полтора месяца от финансового контролёра.

Сведу это в маршрут, по которому я советую идти при проектировании измерения — рис. 3.

Главная мысль схемы: решение про SCD2 принимается не при написании SQL, а когда вы отвечаете на вопрос «нужна ли вообще история по этому атрибуту». Часть проблем в проде создают измерения, которые сделали историчными на всякий случай, не спросив у бизнеса, будет ли кто‑то смотреть прошлые значения.

Ограничения этого разбора

Решения выше рассчитаны на пакетную загрузку с суточной или часовой периодичностью. Поздние события сами по себе bitemporal‑модель не требуют: обычно хватает event time плюс метаданных загрузки.

Две пары интервалов нужны, когда бизнесу надо отвечать на вопрос «что мы считали правдой в прошлый вторник»; на тестовом я это не спрашиваю. И задание намеренно на диалекте, близком к ANSI: в Snowflake, ClickHouse и Greenplum оптимальные решения будут отличаться, но мне важнее ход мысли.

Какой навык на самом деле проверяло задание

Ни одна из четырёх задач не про синтаксис. Сильный аналитик DWH проверяет не только то, что запрос возвращает правильный результат на контрольных данных. Он явно формулирует предпосылки, границы и инварианты, при которых этот результат остаётся правильным, — и пишет проверку, которая заметит их нарушение раньше, чем это сделает финансовый контролёр.

Поэтому кандидат, который говорит «это работает при вот таком условии, и вот запрос, который его проверит», проходит дальше, даже ошибившись в конкретной формулировке.

Если на второй или третьей задаче вы поймали себя на мысли «а ведь у нас в проде так и написано» — сходите проверьте. Особенно BETWEEN в join с измерением.

Когда SQL проходит тесты, но витрина всё равно расходится с продом, обычно хочется искать ошибку в запросе. На практике проблема часто глубже: в неявных предпосылках о данных, временных границах и истории изменений.

Умение замечать такие условия помогает сначала корректно смоделировать данные и их историю, а затем строить DWH, цифрам в котором можно доверять — без расследований постфактум и ручных сверок.

Разобраться в этих темах подробнее можно на открытых уроках:

• 15 сентября в 20:00. «Моделирование данных для DWH». Записаться

• 16 сентября в 20:00. «Темпоральные данные в PostgreSQL 18: история и версии без триггеров». Записаться

Полный список открытых уроков собрали в дайджесте.