SQL & Data Modeling — Data Engineering Interview Cheatsheet

Advanced SQL: Window FunctionsПродвинутый SQL: оконные функции

Anatomy: func() OVER (PARTITION BY ... ORDER BY ... frame). The frame only applies when an ORDER BY is present and controls which rows feed the aggregate.

Анатомия: func() OVER (PARTITION BY ... ORDER BY ... frame). Frame применяется только при наличии ORDER BY и контролирует, какие строки попадают в агрегат.

SUM(amount) OVER (
  PARTITION BY user_id
  ORDER BY event_date
  ROWS BETWEEN 6 PRECEDING AND CURRENT ROW   -- 7-day running window
)

Ranking familyСемейство ранжирования

Func	On ties (1,1,?)
`ROW_NUMBER()`	1,2,3 — always unique
`RANK()`	1,1,3 — gaps after ties
`DENSE_RANK()`	1,1,2 — no gaps
`NTILE(n)`	splits into n buckets

Функция	При дублях (1,1,?)
`ROW_NUMBER()`	1,2,3 — всегда уникальны
`RANK()`	1,1,3 — пропуски после дублей
`DENSE_RANK()`	1,1,2 — без пропусков
`NTILE(n)`	разбивает на n корзин

When Dedup / top-N → ROW_NUMBER. "Nth highest distinct value" → DENSE_RANK. Leaderboards w/ standard ranking → RANK.

Когда Дедуп / top-N → ROW_NUMBER. «N-е наибольшее уникальное значение» → DENSE_RANK. Рейтинги со стандартным ранжированием → RANK.

Offset & positionalСмещение и позиционные

LAG(col, n, default) — prior row value (deltas, churn flags)
LEAD(col, n, default) — next row value (gap to next event)
FIRST_VALUE / LAST_VALUE — careful: LAST_VALUE needs a full frame
NTH_VALUE(col, k)

LAG(col, n, default) — значение предыдущей строки (дельты, флаги оттока)
LEAD(col, n, default) — значение следующей строки (промежуток до следующего события)
FIRST_VALUE / LAST_VALUE — внимание: LAST_VALUE требует полный фрейм
NTH_VALUE(col, k)

-- day-over-day delta
amount - LAG(amount) OVER (PARTITION BY id ORDER BY d)

ROWS vs RANGE — the classic gotchaROWS vs RANGE — классический подвох

	ROWS	RANGE
Counts by	physical row positions	logical ORDER BY values (peers)
Ties	each row separate	tied rows treated as one group
Use for	"last 6 rows", precise N-row windows	"all rows with same date", value ranges

	ROWS	RANGE
Считает по	физическим позициям строк	логическим значениям ORDER BY (пиры)
Дубли	каждая строка отдельно	одинаковые строки как одна группа
Использовать для	«последние 6 строк», точные N-строчные окна	«все строки с той же датой», диапазоны значений

Gotcha Default frame when you write ORDER BY but no explicit frame is RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW. With duplicate ORDER BY keys this sums all peers → running total looks "jumpy". Use ROWS for a true row-by-row running total.

Подвох Фрейм по умолчанию при наличии ORDER BY без явного фрейма — это RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW. С дублями ключей ORDER BY это суммирует всех пиров → running total выглядит «скачущим». Используй ROWS для построчного running total.

Interviewer will probe "Why does your running total include rows you didn't expect?" → explain default RANGE frame & switch to ROWS. "Why is LAST_VALUE returning the current row?" → default frame ends at CURRENT ROW; add ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING.

О чём спросит интервьюер «Почему твой running total включает строки, которых ты не ожидал?» → объясни дефолтный RANGE-фрейм и переключи на ROWS. «Почему LAST_VALUE возвращает текущую строку?» → дефолтный фрейм заканчивается на CURRENT ROW; добавь ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING.

Tip Window functions run after WHERE/GROUP BY/HAVING but before the final SELECT alias resolution. You cannot filter on a window result in WHERE — wrap it in a CTE/subquery and filter outside (this is the #1 reason people reach for a CTE).

Совет Оконные функции выполняются после WHERE/GROUP BY/HAVING, но до финального разрешения алиасов SELECT. Нельзя фильтровать результат оконной функции в WHERE — оборачивай в CTE/подзапрос и фильтруй снаружи (это причина №1, по которой люди идут за CTE).

CTEs & Recursive CTEsCTE и рекурсивные CTE

Plain CTEsОбычные CTE

Readable, composable, chainable. Reference earlier CTEs in later ones.

Читаемы, композируемы, цепочечны. Ссылаешься на ранние CTE в поздних.

Gotcha A CTE is not guaranteed to be materialized. Some engines inline it (re-execute per reference). If reused many times & expensive, prefer a temp table or MATERIALIZED hint (Postgres) / stage table (Spark). Don't assume "CTE = cached".

Подвох CTE не гарантированно материализуется. Некоторые движки инлайнят его (переисполняют на каждую ссылку). Если переиспользуется многократно и дорого, лучше временная таблица или хинт MATERIALIZED (Postgres) / stage-таблица (Spark). Не предполагай «CTE = кеш».

Recursive CTE shapeСтруктура рекурсивного CTE

WITH RECURSIVE org AS (
  -- anchor
  SELECT id, manager_id, 1 AS lvl
  FROM employees WHERE manager_id IS NULL
  UNION ALL
  -- recursive
  SELECT e.id, e.manager_id, org.lvl + 1
  FROM employees e
  JOIN org ON e.manager_id = org.id
)
SELECT * FROM org;

Interviewer will probe "How do you stop infinite recursion?" → ensure the recursive arm strictly progresses toward the anchor; add a depth guard (lvl < 50) or cycle detection. Use cases: org hierarchies, BOM explosion, graph reachability, date spines.

О чём спросит интервьюер «Как остановить бесконечную рекурсию?» → убедись, что рекурсивное плечо строго движется к якорю; добавь ограничение глубины (lvl < 50) или детект циклов. Кейсы: оргструктуры, взрывы BOM, достижимость в графах, шпалы дат.

Joins (incl. Anti / Semi)Джойны (включая Anti / Semi)

Join	Returns	Idiom
INNER	matches only	standard
LEFT / RIGHT	all left/right + matches	enrichment, keep base rows
FULL OUTER	everything, NULLs where no match	reconciliation / table diff
CROSS	cartesian product	date spine × dims (beware blow-up)
SEMI (EXISTS / IN)	left rows that have a match, no dup, no right cols	"users who did X"
ANTI (NOT EXISTS)	left rows with no match	"users who never did X", missing dim keys

Джойн	Возвращает	Идиома
INNER	только совпадения	стандарт
LEFT / RIGHT	все левые/правые + совпадения	обогащение, сохранять базовые строки
FULL OUTER	всё, NULL где нет совпадения	сверка / diff таблиц
CROSS	декартово произведение	шпала дат × измерения (осторожно с раздуванием)
SEMI (EXISTS / IN)	левые строки, у которых есть совпадение, без дублей, без правых колонок	«пользователи, сделавшие X»
ANTI (NOT EXISTS)	левые строки без совпадения	«пользователи, никогда не делавшие X», отсутствующие ключи измерений

-- ANTI join: prefer NOT EXISTS over NOT IN
SELECT u.* FROM users u
WHERE NOT EXISTS (
  SELECT 1 FROM orders o WHERE o.user_id = u.id
);

Gotcha NOT IN (subquery) returns zero rows if the subquery yields a single NULL (3-valued logic). Always use NOT EXISTS for anti-joins, or filter NULLs out of the IN list.

Подвох NOT IN (subquery) возвращает ноль строк, если подзапрос даёт хотя бы один NULL (трёхзначная логика). Всегда используй NOT EXISTS для анти-джойнов или фильтруй NULL из IN-списка.

Interviewer will probe Row-count surprises after a join. Diagnose: fan-out (1-to-many on the "lookup" side multiplies base rows). Fix by deduping the lookup to its grain first, or aggregate after. Always state the grain of each side before joining.

О чём спросит интервьюер Сюрпризы в числе строк после джойна. Диагностика: fan-out (1-ко-многим на стороне «справочника» множит базовые строки). Лечение: дедуплицировать справочник до его грануляции сначала либо агрегировать после. Всегда проговаривай грануляцию каждой стороны перед джойном.

Gotcha Filtering the right table in the WHERE clause of a LEFT JOIN silently turns it into an INNER JOIN (NULLs fail the predicate). Put right-table filters in the ON clause to preserve outer semantics.

Подвох Фильтрация правой таблицы в WHERE при LEFT JOIN тихо превращает его в INNER JOIN (NULL не проходят предикат). Помещай фильтры правой таблицы в ON, чтобы сохранить outer-семантику.

Core Query PatternsОсновные паттерны запросов

Deduplication — keep latest per keyДедупликация — оставить последнюю запись на ключ

WITH ranked AS (
  SELECT *,
    ROW_NUMBER() OVER (
      PARTITION BY user_id
      ORDER BY updated_at DESC, event_id DESC  -- tiebreaker!
    ) AS rn
  FROM events
)
SELECT * FROM ranked WHERE rn = 1;

Tip Always add a deterministic tiebreaker; otherwise "latest" is non-deterministic on ties. QUALIFY (Snowflake/BigQuery) lets you skip the CTE: QUALIFY ROW_NUMBER() OVER(...) = 1.

Совет Всегда добавляй детерминированный тайбрейкер; иначе «последний» недетерминирован при дублях. QUALIFY (Snowflake/BigQuery) позволяет пропустить CTE: QUALIFY ROW_NUMBER() OVER(...) = 1.

Gaps & Islands — consecutive runsGaps & Islands — последовательные пробеги

The trick: a constant difference between ROW_NUMBER() and the sequence value identifies an "island" of consecutive items.

Трюк: постоянная разница между ROW_NUMBER() и значением последовательности идентифицирует «остров» последовательных элементов.

-- consecutive login days per user
WITH grp AS (
  SELECT user_id, login_date,
    DATE_SUB(login_date,
      ROW_NUMBER() OVER(PARTITION BY user_id ORDER BY login_date)) AS island
  FROM (SELECT DISTINCT user_id, login_date FROM logins)
)
SELECT user_id, MIN(login_date) start_d, MAX(login_date) end_d,
       COUNT(*) streak_len
FROM grp GROUP BY user_id, island;

Probe "Why DISTINCT first?" duplicates break the row_number↔date alignment. "Why does the offset trick work?" consecutive dates increment by 1 and so does row_number, so their difference is constant within a run.

Вопрос «Зачем сначала DISTINCT?» дубли ломают выравнивание row_number↔дата. «Почему работает трюк со смещением?» последовательные даты инкрементируются на 1, как и row_number, поэтому их разница постоянна внутри пробега.

Sessionization — split events into sessionsСессии — разбить события на сессии

Start a new session when the gap from the previous event exceeds a threshold (e.g. 30 min). Cumulative sum of "is-new-session" flags gives the session id.

Начинать новую сессию, когда промежуток от предыдущего события превышает порог (например, 30 мин). Кумулятивная сумма флагов «новая-сессия» даёт id сессии.

WITH flagged AS (
  SELECT user_id, ts,
    CASE WHEN DATEDIFF('minute',
      LAG(ts) OVER(PARTITION BY user_id ORDER BY ts), ts) > 30
      OR LAG(ts) OVER(PARTITION BY user_id ORDER BY ts) IS NULL
    THEN 1 ELSE 0 END AS is_new
  FROM events
)
SELECT *,
  SUM(is_new) OVER(PARTITION BY user_id ORDER BY ts
                  ROWS UNBOUNDED PRECEDING) AS session_id
FROM flagged;

Top-N per groupTop-N на группу

SELECT * FROM (
  SELECT *, ROW_NUMBER() OVER(
    PARTITION BY category ORDER BY sales DESC) rn
  FROM products
) WHERE rn <= 3;

Tip Top-N with ties included → use RANK()/DENSE_RANK() instead of ROW_NUMBER.

Совет Top-N с дублями → используй RANK()/DENSE_RANK() вместо ROW_NUMBER.

Pivot / UnpivotPivot / Unpivot

-- pivot: long -> wide (portable conditional-agg)
SELECT user_id,
  SUM(CASE WHEN action='click' THEN 1 ELSE 0 END) clicks,
  SUM(CASE WHEN action='view'  THEN 1 ELSE 0 END) views
FROM events GROUP BY user_id;

-- unpivot: wide -> long via UNION ALL / cross join + array
SELECT user_id, 'clicks' metric, clicks val FROM t
UNION ALL SELECT user_id, 'views', views FROM t;

Tip Conditional aggregation is the most portable pivot (works in every engine). PIVOT/UNPIVOT syntax exists in Snowflake/BigQuery but is dialect-specific.

Совет Условная агрегация — самый портируемый pivot (работает в каждом движке). Синтаксис PIVOT/UNPIVOT есть в Snowflake/BigQuery, но специфичен для диалекта.

Date / time bucketing & date spinesБакетирование дат/времени и шпалы дат

DATE_TRUNC('week', ts)              -- bucket to week start
DATE_TRUNC('month', ts)
FLOOR(EXTRACT(epoch FROM ts)/3600)  -- hourly bucket

Gotcha If a day/week has zero events it disappears from a GROUP BY. Build a date spine (recursive CTE or a calendar dim) and LEFT JOIN facts onto it so empty buckets show as 0, not missing.

Подвох Если день/неделя без событий, он исчезает из GROUP BY. Построй шпалу дат (рекурсивный CTE или календарное измерение) и LEFT JOIN факты к ней, чтобы пустые бакеты показывались как 0, а не пропадали.

NULL Handling & Three-Valued LogicОбработка NULL и трёхзначная логика

NULL = NULL → UNKNOWN, not TRUE. Use IS NULL / IS DISTINCT FROM.
Aggregates (SUM, AVG, COUNT(col)) ignore NULLs; COUNT(*) counts all rows.
AVG(x) ≠ SUM(x)/COUNT(*) when NULLs exist.
COALESCE(a,b,c) first non-null; NULLIF(a,b) → NULL if equal (guard div-by-zero).
NULLs sort last (default) in many engines; control with NULLS FIRST/LAST.

NULL = NULL → UNKNOWN, не TRUE. Используй IS NULL / IS DISTINCT FROM.
Агрегаты (SUM, AVG, COUNT(col)) игнорируют NULL; COUNT(*) считает все строки.
AVG(x) ≠ SUM(x)/COUNT(*) при наличии NULL.
COALESCE(a,b,c) первый не-null; NULLIF(a,b) → NULL если равны (защита от деления на ноль).
NULL сортируются последними (по умолчанию) во многих движках; управлять через NULLS FIRST/LAST.

Gotcha x <> 5 excludes rows where x IS NULL. To include them: x <> 5 OR x IS NULL or x IS DISTINCT FROM 5.

Подвох x <> 5 исключает строки, где x IS NULL. Чтобы включить их: x <> 5 OR x IS NULL или x IS DISTINCT FROM 5.

Gotcha COUNT(DISTINCT col) excludes NULL — a NULL is never "distinct value". Decide explicitly whether NULL should count.

Подвох COUNT(DISTINCT col) исключает NULL — NULL никогда не «уникальное значение». Решай явно, должен ли NULL учитываться.

Safe ratio numerator * 1.0 / NULLIF(denominator, 0)

Безопасное деление numerator * 1.0 / NULLIF(denominator, 0)

Query Optimization & EXPLAIN PlansОптимизация запросов и EXPLAIN-планы

Reading an EXPLAIN plan (Spark / Presto mindset)Чтение EXPLAIN-плана (подход Spark / Presto)

Read bottom-up: leaves are scans, root is the final output. Watch row-count estimates vs actuals — bad estimates → bad join strategy.
Scan node: confirm Partition filters and PushedFilters appear → predicate pushdown & partition pruning are working. If WHERE columns are missing there, you scan the whole table.
Join node: identify the strategy (broadcast vs shuffle/sort-merge) and which side is the build side.
Exchange / Shuffle: expensive data movement across the network. Minimise these.

Читай снизу вверх: листья — сканирования, корень — финальный вывод. Следи за оценкой числа строк vs факт — плохие оценки → плохая стратегия джойнов.
Узел сканирования: убедись, что Partition filters и PushedFilters присутствуют → предикатный pushdown и партиционный pruning работают. Если колонки WHERE отсутствуют, сканируется вся таблица.
Узел джойна: определи стратегию (broadcast vs shuffle/sort-merge) и какая сторона build-сторона.
Exchange / Shuffle: дорогое перемещение данных по сети. Минимизируй.

Join strategiesСтратегии джойнов

Strategy	When	Cost
Broadcast (map-side)	one side small (< threshold ~10MB Spark)	cheap, no shuffle; small side copied to all nodes
Shuffle / Sort-Merge	both sides large	shuffle both by key, sort, merge
Shuffle Hash	one side fits in memory per partition	builds hash table, no full sort

Стратегия	Когда	Цена
Broadcast (map-side)	одна сторона мала (< ~10MB порог в Spark)	дёшево, нет shuffle; малая сторона копируется на все ноды
Shuffle / Sort-Merge	обе стороны большие	shuffle обеих по ключу, сортировка, слияние
Shuffle Hash	одна сторона помещается в память на партицию	строит хеш-таблицу, без полной сортировки

Tip Force/hint a broadcast for a small dim to kill a shuffle: /*+ BROADCAST(dim) */ (Spark/Presto). Verifies the optimizer's size estimate is right.

Совет Принудительный/хинт broadcast для малого измерения убивает shuffle: /*+ BROADCAST(dim) */ (Spark/Presto). Проверяет, что оценка размера оптимизатора верна.

Data skewData skew (перекос)

One key (NULL, "guest", a whale user) gets a giant partition → one straggler task dominates runtime.

Один ключ (NULL, "guest", кит-пользователь) получает гигантскую партицию → одна отстающая задача доминирует во времени выполнения.

Detect: a few tasks take 100× longer; histogram of key counts.
Fix: salting (append random suffix to hot key, join on salted key, then un-salt).
Fix: filter/handle NULL keys separately before the join.
Spark 3+: AQE skew-join handling auto-splits skewed partitions.

Детект: несколько задач в 100× дольше; гистограмма счётов ключей.
Лечение: salting (добавить случайный суффикс к горячему ключу, джойнить на salted-ключе, затем убрать соль).
Лечение: фильтровать/обрабатывать NULL-ключи отдельно до джойна.
Spark 3+: AQE авто-расщепляет перекошенные партиции.

General optimization checklistОбщий чеклист оптимизации

Prune early: filter + project before joins/aggregations (predicate & projection pushdown). Select only needed columns; columnar formats (Parquet/ORC) reward this.
Partition pruning: filter on the partition column with a literal/constant, not a function wrapping it (WHERE ds = '2026-06-14', not WHERE CAST(ds AS date) = ...).
Avoid SELECT * over wide columnar tables. Avoid COUNT(DISTINCT) on huge cardinality — use approx (APPROX_COUNT_DISTINCT) if exactness isn't required.
Pre-aggregate before joining to reduce row volume into the join.
Window vs self-join: a window function usually beats a correlated self-join for running calcs.

Обрезай рано: фильтр + проекция до джойнов/агрегаций (предикатный и проекционный pushdown). Выбирай только нужные колонки; колоночные форматы (Parquet/ORC) это вознаграждают.
Партиционный pruning: фильтруй по колонке партиции с литералом/константой, а не оборачивай функцией (WHERE ds = '2026-06-14', не WHERE CAST(ds AS date) = ...).
Избегай SELECT * по широким колоночным таблицам. Избегай COUNT(DISTINCT) на огромной кардинальности — используй approx (APPROX_COUNT_DISTINCT), если точность не нужна.
Предварительная агрегация до джойна, чтобы уменьшить объём строк в джойне.
Оконная функция vs self-join: оконная функция обычно бьёт коррелированный self-join для running-вычислений.

Interviewer will probe "This query is slow, what do you do?" → Walk the funnel out loud: (1) read EXPLAIN, (2) confirm partition pruning & pushdown, (3) check join strategy & build side, (4) look for skew/shuffles, (5) reduce data scanned (columns + partitions), (6) pre-aggregate. State assumptions about table sizes.

О чём спросит интервьюер «Этот запрос медленный, что делаешь?» → Пройдись по воронке вслух: (1) читай EXPLAIN, (2) подтверди партиционный pruning и pushdown, (3) проверь стратегию джойна и build-сторону, (4) ищи skew/shuffle, (5) уменьши объём сканированных данных (колонки + партиции), (6) предварительная агрегация. Проговори предположения о размерах таблиц.

Dimensional ModelingРазмерностное моделирование

Fact vs DimensionФакт vs Измерение

Fact: measurements/events at a grain; numeric, additive measures; foreign keys to dims. Tall & narrow, grows fast.
Dimension: descriptive context (who/what/where/when). Wide & short, slowly changing.
Fact types: transaction, periodic snapshot, accumulating snapshot.
Measure additivity: fully additive (revenue), semi-additive (balances — not over time), non-additive (ratios, %).

Факт: измерения/события на зерне (grain); числовые, аддитивные меры; внешние ключи к измерениям. Высокие и узкие, быстро растут.
Измерение: описательный контекст (кто/что/где/когда). Широкие и короткие, медленно меняются.
Типы фактов: транзакция, периодический снимок, накапливающий снимок.
Аддитивность мер: полная аддитивность (выручка), полу-аддитивность (балансы — не по времени), не-аддитивность (соотношения, %).

Grain — declare it firstЗерно (grain) — объяви его первым

The grain is "what one fact row means" (e.g. one row per order line per day). Decide grain before picking dims/measures. Mixing grains is the #1 modeling bug → double counting.

Зерно — это «что означает одна строка факта» (например, одна строка на строку заказа на день). Решай зерно до выбора измерений/мер. Смешивание зёрен — ошибка моделирования №1 → двойной подсчёт.

Tip Lowest practical grain = most flexible (you can always roll up, never down). Pre-aggregated facts answer one question fast but lose detail.

Совет Самое низкое практичное зерно = самая гибкая (всегда можешь свернуть вверх, никогда вниз). Предварительно агрегированные факты отвечают на один вопрос быстро, но теряют детали.

Star vs SnowflakeЗвезда vs Снежинка

	Star	Snowflake
Dims	denormalized, flat	normalized into sub-dims
Joins for a query	fewer (fact → dim)	more (fact → dim → sub-dim)
Query speed	faster, simpler	slower, more joins
Storage / redundancy	more redundancy	less, tidier
BI-friendliness	high (industry default)	lower

	Звезда	Снежинка
Измерения	денормализованы, плоские	нормализованы в под-измерения
Джойнов на запрос	меньше (факт → измерение)	больше (факт → измерение → под-измерение)
Скорость запроса	быстрее, проще	медленнее, больше джойнов
Хранение / избыточность	больше избыточности	меньше, аккуратнее
BI-дружелюбность	высокая (индустриальный дефолт)	ниже

Default Star schema is the go-to for analytics/BI. Snowflake only when a dim is huge & volatile and normalization meaningfully saves storage/maintenance.

Дефолт Схема-звезда — стандарт для analytics/BI. Снежинка только когда измерение огромно и волатильно, и нормализация значимо экономит хранение/поддержку.

Surrogate keysСуррогатные ключи

Synthetic, system-generated PK for each dim row (integer/hash). Independent of source "natural"/business keys.
Required for SCD2: same business key has multiple dim rows over time, each with its own surrogate key.
Insulates the warehouse from source key changes & enables fast integer joins.

Синтетический, системно-генерируемый PK для каждой строки измерения (integer/hash). Независим от исходных «натуральных»/бизнес-ключей.
Требуется для SCD2: тот же бизнес-ключ имеет несколько строк измерения со временем, каждая со своим суррогатным ключом.
Изолирует хранилище от изменений исходных ключей и включает быстрые integer-джойны.

Conformed dimensionsКонформные измерения

A dimension shared & consistent across multiple fact tables / data marts (same keys, same meaning) — e.g. one dim_date, one dim_user used everywhere. Enables cross-process drill-across and consistent metrics. The backbone of an enterprise bus matrix.

Измерение, разделённое и согласованное между несколькими таблицами фактов / data mart'ами (одни и те же ключи, одинаковый смысл) — например, один dim_date, один dim_user, используемые везде. Позволяет кросс-процессное drill-across и согласованные метрики. Костяк enterprise bus-матрицы.

Normalization vs DenormalizationНормализация vs Денормализация

	Normalized (3NF)	Denormalized
Goal	no redundancy, write integrity	read speed, fewer joins
Best for	OLTP / source systems	OLAP / analytics, wide tables
Writes	cheap, single place	update anomalies, must propagate
Reads	many joins	fast, self-contained rows

	Нормализованные (3NF)	Денормализованные
Цель	нет избыточности, целостность записи	скорость чтения, меньше джойнов
Лучше для	OLTP / исходные системы	OLAP / аналитика, широкие таблицы
Записи	дёшево, одно место	аномалии обновления, надо распространять
Чтения	много джойнов	быстро, самодостаточные строки

Probe "Would you normalize a warehouse?" → Analytics favours denormalized star schemas for read performance; OLTP favours 3NF for write integrity. Name the tradeoff (storage/maintenance vs query simplicity/speed) rather than picking dogmatically.

Вопрос «Нормализовал бы хранилище?» → Аналитика предпочитает денормализованные схемы-звёзды ради производительности чтения; OLTP предпочитает 3NF ради целостности записи. Назови компромисс (хранение/поддержка vs простота/скорость запросов), а не выбирай догматично.

Slowly Changing Dimensions (SCD)Медленно меняющиеся измерения (SCD)

Type	Behaviour	History	Use
0	never changes (fixed at insert)	n/a	birth date, original signup source
1	overwrite in place	none — loses old value	corrections, no history needed
2	new row per change (versioning)	full	most common; point-in-time truth
3	add column (prev_value)	limited (1 prior)	track "previous" only

Тип	Поведение	История	Применение
0	никогда не меняется (фиксируется при вставке)	н/п	дата рождения, исходный источник регистрации
1	перезапись на месте	нет — теряет старое значение	исправления, история не нужна
2	новая строка на каждое изменение (версионирование)	полная	самый частый; истина на момент времени
3	добавить колонку (prev_value)	ограниченная (1 предыдущее)	отслеживать только «предыдущее»

SCD2 columnsКолонки SCD2

dim_user(
  user_sk      -- surrogate key (PK),
  user_id      -- business/natural key,
  attributes...,
  valid_from   -- effective start,
  valid_to     -- effective end (NULL or 9999-12-31 = current),
  is_current   -- boolean flag
)

Probe "How do you join a fact to an SCD2 dim?" → join on business key AND fact.event_ts BETWEEN valid_from AND valid_to to get the version that was active at event time (point-in-time correctness). Joining on is_current only gives today's attributes — wrong for historical facts.

Вопрос «Как джойнить факт к SCD2-измерению?» → джойнить по бизнес-ключу И fact.event_ts BETWEEN valid_from AND valid_to, чтобы получить версию, активную на момент события (точечно-временная корректность). Джойн на is_current даёт только сегодняшние атрибуты — неверно для исторических фактов.

Gotcha Watch for overlapping/gapped validity ranges from bad merges. Use a half-open interval [valid_from, valid_to) to avoid a fact matching two versions on the boundary.

Подвох Следи за перекрывающимися/пропущенными диапазонами validity от плохих мерджей. Используй полуоткрытый интервал [valid_from, valid_to), чтобы факт не попадал на две версии на границе.

Idempotent Pipeline PatternsПаттерны идемпотентных пайплайнов

Idempotent = re-running a job for the same inputs produces the same output, no duplicates, no drift. Essential for backfills & retries.

Идемпотентный = перезапуск джобы на тех же входах даёт тот же выход, без дублей, без дрифта. Критично для backfill'ов и ретраев.

Partition overwrite (insert-overwrite)Перезапись партиции (insert-overwrite)

Recompute a whole partition (e.g. one ds) and atomically replace it. Naturally idempotent for daily batch.

Пересчитать всю партицию (например, один ds) и атомарно заменить её. Естественно идемпотентно для дневного батча.

INSERT OVERWRITE TABLE fct_x PARTITION(ds='2026-06-14')
SELECT ... ;  -- rerun safe: replaces the partition

Tip Beware dynamic partition overwrite semantics (Spark spark.sql.sources.partitionOverwriteMode=dynamic) — replaces only written partitions, not the whole table.

Совет Осторожно с семантикой динамической перезаписи партиций (Spark spark.sql.sources.partitionOverwriteMode=dynamic) — заменяет только записанные партиции, не всю таблицу.

MERGE / upsertMERGE / upsert

MERGE INTO target t
USING staged s ON t.id = s.id
WHEN MATCHED THEN UPDATE SET ...
WHEN NOT MATCHED THEN INSERT ...;

Gotcha MERGE errors (or non-determines) if the source has >1 row per match key. Dedup the source to the merge grain first (ROW_NUMBER = 1).

Подвох MERGE выдаёт ошибку (или недетерминирован), если источник имеет >1 строки на ключ совпадения. Дедуп источника до грануляции мерджа сначала (ROW_NUMBER = 1).

Interviewer will probe "How do you make a backfill safe to rerun?" → deterministic transforms (no now()/random in logic), partition-scoped overwrite or MERGE on a stable key, no append-without-dedup. Watermarks for incremental + late data handling.

О чём спросит интервьюер «Как сделать backfill безопасным к перезапуску?» → детерминированные трансформации (никакого now()/random в логике), партиционно-ограниченная перезапись или MERGE на стабильном ключе, никакого append-без-дедупа. Watermark'и для инкрементальных + обработки late data.

Data Quality ChecksПроверки качества данных

CategoriesКатегории

Completeness: row counts vs expected; non-null on required cols; partition landed daily (≥99%).
Uniqueness: PK/grain has no duplicates.
Validity: values in allowed range/enum; FK references resolve (no orphans).
Consistency: totals reconcile vs source/loaders; sum of parts = whole.
Timeliness/Freshness: max(event_ts) within SLA.
Accuracy/Anomaly: volume within expected band vs trailing avg.

Полнота: число строк vs ожидаемое; non-null на обязательных колонках; партиция приземлилась ежедневно (≥99%).
Уникальность: PK/grain без дублей.
Валидность: значения в допустимом диапазоне/enum; FK-ссылки разрешаются (нет сирот).
Согласованность: тоталы сходятся с источником/лоадерами; сумма частей = целому.
Своевременность/Свежесть: max(event_ts) в рамках SLA.
Точность/Аномалии: объём в ожидаемом диапазоне vs скользящего среднего.

Concrete checksКонкретные проверки

-- PK uniqueness (should return 0 rows)
SELECT id, COUNT(*) FROM t
GROUP BY id HAVING COUNT(*) > 1;

-- orphan FK
SELECT COUNT(*) FROM fct f
LEFT JOIN dim d ON f.dim_id=d.id
WHERE d.id IS NULL;

-- volume anomaly vs 7d avg
SELECT ds, cnt,
  cnt / AVG(cnt) OVER(ORDER BY ds
    ROWS BETWEEN 7 PRECEDING AND 1 PRECEDING) ratio
FROM daily_counts;

Tip Frame DQ as blocking (fail the pipeline: PK dup, completeness) vs alerting (warn: volume drift). Tools: dbt tests, Great Expectations, custom assertion SQL gated before publish.

Совет Формируй DQ как блокирующие (фейлят пайплайн: дубли PK, полнота) vs алертящие (предупреждают: дрифт объёма). Инструменты: dbt tests, Great Expectations, кастомный assertion SQL перед публикацией.

Practice Problems — flip to revealПрактические задачи — переверни, чтобы увидеть

Click a card to flip and reveal the solution. Try to write it out loud / on paper first, the way you would in the screen.

Кликни на карточку, чтобы перевернуть и увидеть решение. Попробуй сначала написать вслух / на бумаге, как на экране интервью.

1Nth highest salary (3rd highest distinct), no LIMIT trick.N-я наибольшая зарплата (3-я наибольшая уникальная), без трюка с LIMIT.