РЕАЛЬНОЕ СОБЕСЕДОВАНИЕ DWH разработчик ГК Иннотех - Middle 200+ тыс.

18 августа 2025 г. · 142 мин. чтения

Сегодня мы разберем собеседование, в котором кандидат с опытом работы с Oracle, SQL и базовыми инструментами BI демонстрирует уверенные знания фундаментальных концепций хранилищ данных, нормализации и индексов, но местами теряется на практических задачах по SQL и оркестрации пайплайнов. Диалог строится в формате плотного технического скоринга: интервьюер системно проверяет глубину понимания DWH-архитектур, работу с NULL, JOIN, оконными функциями и индексами, что позволяет хорошо увидеть реальный уровень подготовки кандидата.

Вопрос 1. Кратко рассказать о своем опыте и текущих проектах в области разработки и работы с данными.

Таймкод: 00:01:26

Ответ собеседника: правильный. Кандидат более двух лет работает с данными, начинал как Python-разработчик с Oracle и самописным фреймворком, по факту занимался преимущественно SQL-разработкой. Сейчас работает в компании, занимающейся BI-решениями: участвовал в проекте электронного документооборота на Oracle и основном проекте по импортозамещению BI-систем, где помогает клиентам мигрировать с других BI-платформ на собственный продукт.

Правильный ответ:

Мой опыт сосредоточен на разработке решений, связанных с данными, высоконагруженными хранилищами и интеграцией с корпоративными системами.

Кратко по ключевым направлениям:

Начинал с разработки на Python в связке с Oracle, где основная задача заключалась в реализации бизнес-логики через сложные SQL-скрипты, процедурную логику (PL/SQL), оптимизацию запросов и работу с большим объемом данных.
Плотно работал с самописными фреймворками и легаси-решениями: разбирался в чужом коде, улучшал архитектуру, устранял узкие места по производительности и надежности, внедрял практики код-ревью и стандарты оформления.
В текущей роли занимаюсь разработкой и поддержкой BI-решений:
- Проекты электронного документооборота поверх Oracle: проектирование схем данных, реализация транзакционной логики, обеспечение согласованности и целостности данных, аудит действий пользователей.
- Проекты по импортозамещению BI-систем: миграция отчетности и витрин данных с иностранных BI-платформ на собственный продукт:
  - анализ существующих моделей данных и отчетов;
  - проектирование новых моделей под целевую платформу;
  - перенос логики вычислений, агрегаций, фильтрации;
  - оптимизация запросов под особенности конкретной СУБД;
  - обеспечение корректности данных, производительности и отказоустойчивости.

С технической стороны мой опыт включает:

Проектирование моделей данных (OLTP и OLAP), нормализация/денормализация в зависимости от сценариев использования.
Написание сложных SQL-запросов с джойнами, оконными функциями, подзапросами, CTE, оптимизацией планов выполнения.
Работа с транзакциями, конкурентным доступом, индексацией, анализом планов выполнения запросов.
Интеграция приложений с СУБД, продуманная работа с пулами соединений, ретраями, логированием и мониторингом.

Пример характерной задачи (на SQL-уровне):

WITH latest_status AS (
    SELECT
        d.document_id,
        s.status,
        s.changed_at,
        ROW_NUMBER() OVER (PARTITION BY d.document_id ORDER BY s.changed_at DESC) AS rn
    FROM documents d
    JOIN document_status_history s
        ON d.document_id = s.document_id
    WHERE d.created_at >= ADD_MONTHS(TRUNC(SYSDATE, 'MM'), -1)
)
SELECT document_id, status, changed_at
FROM latest_status
WHERE rn = 1;

Такие задачи типичны для отчетности, аудита и BI: получить актуальное состояние сущности на основе истории изменений эффективно и воспроизводимо.

Если говорить в контексте перехода к Go:

Мой опыт с данными хорошо ложится на разработку сервисов и backend-логики на Go: работа с транзакциями, оптимизацией запросов, чистой архитектурой, разделением уровней (domain / repository / transport), реализацией надежных и предсказуемых сервисов, которые интенсивно взаимодействуют с базами данных и внешними системами.

Вопрос 2. Как устроена текущая организация работы: состав команды, постановка задач, взаимодействие с заказчиком и полный жизненный цикл задач?

Таймкод: 00:03:06

Ответ собеседника: правильный. На примере проекта электронного документооборота для РЖД: заказчик формирует ТЗ в Jira, менеджер и старший разработчик оценивают и распределяют задачи, ежедневные созвоны с заказчиком, разработка на dev-контуре, проверка (ранее тестировщик, сейчас разработчик), перенос на test и prod, затем поддержка и доработки по инцидентам.

Правильный ответ:

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

Основные элементы процесса:

Состав команды:
- Обычно это небольшая кросс-функциональная команда: разработчики (backend, иногда frontend), аналитик/системный аналитик, тимлид/технический эксперт, менеджер проекта, иногда выделенный QA.
- Распределение ролей четкое: аналитик формализует требования, разработчики отвечают за реализацию и техническое качество, менеджер — за сроки и коммуникацию, технический лидер — за архитектуру и ключевые решения.
Источник задач:
- Основной источник — бизнес-требования заказчика: новые фичи, изменения процессов, интеграции с внешними системами, отчётность.
- Все задачи фиксируются в таск-трекере (например, Jira) в виде:
  - epic/feature (крупные блоки функциональности),
  - story (конкретные бизнес-сценарии),
  - task/sub-task (реализация, интеграции, миграции, тесты),
  - bug/incident (поддержка продакшена).
- Для изменений в критичных системах документация требований обязательна:
  - формализованное ТЗ,
  - схемы бизнес-процессов,
  - описание сценариев использования,
  - критерии приемки.
Взаимодействие с заказчиком:
- Регулярные синки (ежедневные/еженедельные):
  - уточнение требований,
  - приоритезация задач,
  - демонстрация прогресса и промежуточных результатов.
- Канал обратной связи для инцидентов: регистрируются через сервис-деск/таск-трекер, с указанием приоритетов (P1–P4), SLA и контекста.
- В процессе разработки активно прорабатываются:
  - граничные кейсы,
  - сценарии отказов,
  - требования к аудиту и безопасности (особенно в крупных корпорациях/госструктурах).
Жизненный цикл задачи (end-to-end):
1. Инициация:
  - Заказчик или аналитик формирует запрос: бизнес-проблема, ограничения, зависимые системы.
  - Описание попадает в backlog.
2. Аналитика и уточнение:
  - Проработка требований: данные, роли, права доступа, интеграции, нефункциональные требования.
  - Формирование понятного описания в задаче с критериями готовности (Definition of Ready).
3. Оценка:
  - Разработчики и технический лидер оценивают трудоемкость, риски, зависимости.
  - Оценка делается на уровне задач, а не абстрактно: это позволяет планировать релизы и коммуникацию с заказчиком.
4. Планирование и распределение:
  - На планировании задачи разбиваются на подзадачи:
    - изменение схемы БД / миграции,
    - реализация бизнес-логики,
    - интеграции (HTTP/gRPC/сообщения),
    - тесты (юнит, интеграционные, e2e),
    - документация.
  - Распределение задач по разработчикам с учётом компетенций и загрузки.
5. Разработка (Dev):
  - Вся разработка ведётся в отдельных ветках (Git-flow / trunk-based с feature-ветками).
  - Обязательно:
    - код-ревью,
    - статический анализ (linters),
    - форматирование кода,
    - unit и integration тесты.
  - Для серверной разработки (например, на Go или Python) — четкое разделение слоев:
    - transport (HTTP/gRPC),
    - бизнес-логика,
    - работа с БД (репозитории),
    - конфигурация и логирование.
  Пример простого HTTP-обработчика на Go в таком процессе:
```
type DocumentHandler struct {
    svc DocumentService
}

func (h *DocumentHandler) GetDocument(w http.ResponseWriter, r *http.Request) {
    ctx := r.Context()
    idStr := chi.URLParam(r, "id")
    id, err := strconv.ParseInt(idStr, 10, 64)
    if err != nil {
        http.Error(w, "invalid id", http.StatusBadRequest)
        return
    }

    doc, err := h.svc.GetByID(ctx, id)
    if errors.Is(err, ErrNotFound) {
        http.Error(w, "not found", http.StatusNotFound)
        return
    }
    if err != nil {
        http.Error(w, "internal error", http.StatusInternalServerError)
        return
    }

    w.Header().Set("Content-Type", "application/json")
    json.NewEncoder(w).Encode(doc)
}
```
6. Тестирование:
  - Dev-контур: разработчик проверяет функционал локально и на dev-среде.
  - Test/QA-контур:
    - функциональное тестирование,
    - регрессионные проверки для критичных процессов,
    - проверка производительности и корректности работы с реальными объемами данных, если это BI/документооборот.
7. Релиз (Prod):
  - Подготовка релизного состава, changelog, миграций БД.
  - Регламентированное окно деплоя (особенно для крупных заказчиков).
  - Наличие плана отката (rollback), версионирование схемы и API.
  Пример миграции для добавления аудита статуса документа (SQL):
```
ALTER TABLE documents
    ADD (status VARCHAR2(50));

CREATE TABLE document_status_audit (
    audit_id        NUMBER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    document_id     NUMBER NOT NULL,
    old_status      VARCHAR2(50),
    new_status      VARCHAR2(50),
    changed_at      DATE DEFAULT SYSDATE,
    changed_by      VARCHAR2(100)
);
```
8. Поддержка и развитие:
  - Мониторинг логов, метрик, ошибок.
  - Инциденты оформляются задачами и проходят такой же прозрачный цикл: анализ → исправление → ревью → тест → релиз.
  - На основе прод-проблем уточняются требования, усиливаются проверки, дописываются тесты.

Ключевые признаки зрелого процесса:

все задачи формализованы и прозрачно отслеживаются;
есть понятные Definition of Ready и Definition of Done;
регулярная коммуникация с заказчиком, без «серых» задач;
обязательное ревью, тестирование и контролируемые релизы;
четкое разделение окружений (dev/test/stage/prod) и соблюдение регламентов работы с продом.

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

Вопрос 3. Как организован процесс релизов и развёртывания изменений?

Таймкод: 00:05:33

Ответ собеседника: неполный. Упомянуто, что скрипты хранятся в Git, и отдельная команда сопровождения накатывает их на среды. Не раскрыты детали ветвления, code review, автоматизации, откатов и управления версиями.

Правильный ответ:

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

Ниже пример зрелого процесса.

Стратегия ветвления и версионирования

Используются понятные практики работы с Git:
- trunk-based development с короткоживущими feature-ветками или
- GitFlow-подход (main/master, develop, release/, hotfix/), в зависимости от требований к стабильности.
Каждое изменение (код сервиса, конфигурация, миграции БД) проходит через:
- отдельную ветку;
- обязательный pull request;
- code review минимум одним другим разработчиком.
Релизы версионируются (semver или внутренние версии): v1.12.3, 2025.03.01-rc1 и т.п.
В Git создаются теги для каждого релиза — это основа для воспроизводимых сборок и понятного отката.

Хранение и управление миграциями БД

Все изменения схемы и данных:
- версионируются в репозитории (SQL/DDL/конфигурационные файлы);
- оформляются как миграции с явным порядком применения.
Используются миграционные инструменты:
- для Go-проектов часто: golang-migrate/migrate, flyway, liquibase или кастомное решение.
Порядок: каждый релиз содержит набор миграций, привязанных к версии приложения, что исключает «ручные» неотслеживаемые изменения.

Пример миграции для Go (migrate):

-- 202511090001_add_documents_table.up.sql
CREATE TABLE documents (
    id           BIGSERIAL PRIMARY KEY,
    title        TEXT        NOT NULL,
    status       TEXT        NOT NULL,
    created_at   TIMESTAMPTZ NOT NULL DEFAULT now()
);

-- 202511090001_add_documents_table.down.sql
DROP TABLE documents;

Применение в Go-сервисе:

func RunMigrations(dbURL string) error {
    m, err := migrate.New(
        "file://migrations",
        dbURL,
    )
    if err != nil {
        return err
    }
    if err := m.Up(); err != nil && err != migrate.ErrNoChange {
        return err
    }
    return nil
}

CI/CD-пайплайн

Любое изменение после push/PR проходит через CI:
- сборка;
- линтеры;
- unit-тесты;
- интеграционные тесты (при наличии docker-compose/тестовых стендов);
- проверка миграций (пробный прогон на тестовой БД).
Только после успешного прохождения всех стадий:
- PR может быть смержен;
- артефакт сборки (docker-образ, бинарник) публикуется в реестр;
- формируется кандидат в релиз.

Типичный пайплайн для Go-сервиса:

stage 1: go vet, golangci-lint, unit-тесты;
stage 2: сборка docker-образа;
stage 3: прогон интеграционных тестов против test-окружения и тестовой БД;
stage 4: деплой в dev/stage по триггеру;
stage 5: ручное одобрение (manual approval) для продакшена.

Процесс релиза по средам

Стандартный маршрут изменений:

Dev:
- Автоматический деплой после merge в основную ветку (или отдельную dev-ветку).
- Разработчики проверяют работоспособность, прогоняют smoke-тесты.
Test/Stage:
- Релиз-кандидат собирается из конкретного коммита/тага.
- Заезжают те же миграции БД.
- Проводится функциональное тестирование, регресс, проверка интеграций.
- Для критичных систем — нагрузочное тестирование и проверка долгих запросов.
Prod:
- Деплой строго по регламенту:
  - окно релиза;
  - зафиксированный список изменений (release notes);
  - подтверждение от ответственных (менеджер/архитектор/представитель заказчика).
- Деплой автоматизирован (CI/CD, Ansible, Helm, ArgoCD, GitOps-подход и т.п.).
- Миграции БД запускаются как часть процесса деплоя, а не вручную.

Откаты и стратегия безопасных изменений

Критически важный аспект — четко продуманный rollback и безопасные схемы изменений:

Откат сервиса:
- Возможность быстро задеплоить предыдущую версию по тегу.
- Образы/артефакты старых версий доступны.
Откат БД:
- Для destructive-миграций (DROP, перезапись данных) желательно:
  - избегать необратимых операций без подготовки;
  - использовать двухшаговые изменения (расширение схемы → миграция данных → переключение логики → очистка позже).
- При использовании миграционных инструментов — наличие down-скриптов там, где это безопасно.
Backward-compatible изменения:
- Добавление колонок, таблиц, API-полей — без ломки текущего потребителя.
- Принцип: сначала деплой, который работает и со старой, и с новой схемой, затем постепенное переключение.

Пример безопасного изменения:

Добавляем новое поле и используем его опционально:

ALTER TABLE documents ADD COLUMN external_id TEXT NULL;

Меняем сервис так, чтобы он:

умел работать, даже если поле пустое;
не ломал старых клиентов.

После миграции данных и уверенности — делаем поле NOT NULL, если это требуется.

Контроль, мониторинг и пост-релиз

После выката на прод:

Мониторинг:
- метрики (латентность, ошибки, время ответов, нагрузка на БД);
- логирование с корреляцией запросов (trace_id);
- алерты по ключевым SLA.
Пост-релизная проверка:
- smoke-тесты;
- проверка ключевых бизнес-сценариев;
- верификация миграций.
Если обнаружены критичные проблемы:
- быстрый rollback приложения или feature-flag’и для отключения проблемного функционала;
- фикс оформляется как hotfix через тот же контролируемый процесс.

Кратко:

Зрелый процесс релизов — это:

версия всего кода и миграций в Git;
обязательное code review;
автоматизированный CI/CD;
понятная стратегия ветвления;
контролируемые деплои на dev/test/prod;
заранее продуманные rollback-стратегии;
мониторинг и прозрачный контроль качества после релиза.

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

Вопрос 4. Каков уровень владения Git и какие команды используются в повседневной работе?

Таймкод: 00:05:59

Ответ собеседника: неполный. Сообщает о знании базовых команд Git и готовности ответить по конкретике, но не приводит деталей. Упоминает только поверхностное знакомство с GitLab Flow/Rebase, без описания практик работы с ветками, конфликтами, ревью и историей изменений.

Правильный ответ:

В работе с Git важно не просто знать базовые команды, а уверенно управлять историей, ветками, ревью и релизным потоком. Для командной разработки и CI/CD Git — фундамент.

Кратко по ключевым аспектам, которые ожидаются от зрелого разработчика.

Основные повседневные операции:

Клонирование и базовая работа:
- git clone — получение репозитория.
- git status — контроль состояния рабочей директории.
- git diff — просмотр изменений перед коммитом.
- git add — подготовка изменений.
- git commit — создание логически целостных коммитов с информативным сообщением.
- git log, git log --oneline --graph --decorate — анализ истории.
Работа с удалённым репозиторием:
- git fetch — обновление ссылок без изменения локальных веток.
- git pull (осознанное использование) — часто в виде:
  - git pull --rebase для линейной истории.
- git push, в том числе:
  - git push --force-with-lease при аккуратном переписывании истории своих feature-веток.

Работа с ветками и моделями ветвления:

Создание и переключение веток:
- git branch, git checkout -b feature/my-task, git switch -c feature/my-task.
Типичный процесс:
- основная ветка (main / master / develop),
- короткоживущие feature-ветки для задач,
- отдельные ветки под release/hotfix при GitFlow или GitLab Flow.
Часто используемые команды:
- git merge — слияние веток (обычно через merge-коммиты в protected-ветки или через squash в UI GitLab/GitHub).
- git rebase — выравнивание feature-ветки поверх актуальной основной ветки для:
  - линейной истории,
  - избежания лишних merge-коммитов.
Понимание GitLab Flow / GitHub Flow:
- работа через merge requests / pull requests;
- feature-ветки от main;
- обязательный code review и CI перед merge;
- защищенные ветки и запрет прямого пуша.

Управление конфликтами:

Уверенная работа с конфликтами:
- git merge / git rebase → конфликты → ручное разрешение → git add → продолжение (git rebase --continue).
Понимание причин конфликтов:
- одновременные изменения в одних и тех же файлах/строках;
- решение в пользу актуальной бизнес-логики, а не механическое «оставить своё».

Переписывание истории (осознанно):

Для своих веток:
- git commit --amend — поправить последний коммит (сообщение или состав файлов).
- git rebase -i — интерактивное редактирование истории:
  - squash нескольких мелких коммитов в один,
  - переименование сообщений,
  - удаление технических коммитов.
Важно:
- не переписывать историю защищенных/общих веток (main, develop), чтобы не ломать коллегам репозитории.

Работа с тегами и релизами:

Теги:
- git tag v1.2.3 — пометить релиз.
- git push origin v1.2.3 — публикация тега.
Теги используются:
- для сборки релизных артефактов;
- для быстрой диагностики и отката (можно задеплоить конкретный тег).

Диагностика и безопасные операции:

Анализ:
- git show — детали коммита.
- git blame — кто и зачем изменил строку (для анализа, не для поиска виноватых).
Откаты:
- git revert — создание обратного коммита (без переписывания истории, корректно для прод-веток).
- git reset (mixed/soft/hard) — аккуратная локальная правка состояния (использовать осознанно).

Практики командной работы:

Структурированные коммиты:
- один коммит — логически цельное изменение (например, новая функция сервиса + тесты).
- информативные сообщения: что сделано и зачем, а не «fix», «update».
Интеграция с CI/CD:
- триггер пайплайнов по push/MR;
- использование protected branches;
- запрет merge без «зелёного» CI.
Код-ревью:
- Git используется как основа для MR/PR:
  - обзор diff;
  - обсуждение решений;
  - фиксация замечаний;
  - прозрачная история, почему принято то или иное решение.

Пример рабочего цикла для фичи:

Создать ветку:

git switch -c feature/add-reporting-endpoint

Внести изменения в Go-сервис (например, новый endpoint отчета), написать тесты.
Закоммитить:

git add .
git commit -m "Add reporting endpoint for monthly documents summary"

Обновить ветку относительно main:

git fetch origin
git rebase origin/main

Разрешить конфликты при необходимости, продолжить rebase.
Запушить:

git push -u origin feature/add-reporting-endpoint

Открыть Merge Request:

проходит CI;
проходит code review;
после одобрения — merge (squash/merge/rebase в зависимости от политики).

Такое владение Git позволяет уверенно работать в команде, поддерживать чистую и понятную историю, минимизировать конфликты и упрощать анализ проблем как в коде, так и в релизах.

Вопрос 5. С какими ETL-инструментами есть опыт работы и как они используются в реальных пайплайнах?

Таймкод: 00:06:24

Ответ собеседника: неполный. Упомянул Airflow для оркестрации и внутренний ETL-инструмент (аналог Oozie) с визуальными схемами и поддержкой SQL/Python-скриптов, но не раскрыл глубину опыта, промышленное использование, подходы к надежности, мониторингу и масштабированию.

Правильный ответ:

Опыт с ETL/ELT-инструментами важен не только на уровне «знаю интерфейс», а на уровне построения надежных, отказоустойчивых, прозрачно мониторимых пайплайнов для продакшена. В промышленной среде основной фокус — не на кнопках, а на архитектуре и практиках.

Показательный стек и подход:

Использование Airflow как основного оркестратора:
- управление зависимостями между задачами;
- планирование (schedule);
- ретраи, SLA, алерты;
- разделение окружений;
- версионирование дагов вместе с кодом.
Использование корпоративных/внутренних ETL-платформ (аналогов Oozie):
- визуальные пайплайны для прозрачности и поддержки;
- комбинирование шагов:
  - SQL-скрипты (трансформации в DWH/ODS),
  - Python-или shell-скрипты для обогащения, валидаций, интеграций;
  - вызовы внешних сервисов, API, очередей.
Глубина участия подразумевает:
- дизайн пайплайнов: от источников (OLTP, API, файлы, очереди) до витрин (DWH, marts, BI);
- оптимизацию порядка шагов и объема данных (инкрементальные загрузки, партиционирование);
- контроль качества данных и идемпотентность;
- обработку ошибок и частичных отказов;
- аудит: логирование, трекинг, повторный запуск.

Основные принципы зрелого ETL/ELT-подхода:

Явное разделение слоёв данных:
- Raw/Staging: данные в исходном виде, минимальные преобразования.
- ODS/DWH: нормализованная модель, консолидация, ключи, история (SCD).
- Data Marts / BI-витрины: агрегаты под конкретные отчеты и аналитические сценарии.
- Это важно для трассировки, повторной загрузки и неизменности источника.
Инкрементальная загрузка:
- Загрузка только изменившихся данных:
  - по timestamp (updated_at),
  - по флагам/журналам изменений (CDC),
  - по суррогатным ключам.
- Пример простого инкремента (SQL):
```
INSERT INTO dwh_orders AS d
    (order_id, customer_id, amount, updated_at)
SELECT
    s.order_id,
    s.customer_id,
    s.amount,
    s.updated_at
FROM staging_orders s
WHERE s.updated_at > (
    SELECT COALESCE(MAX(updated_at), TIMESTAMP '1970-01-01')
    FROM dwh_orders
);
```
- В прод-проектах к этому добавляются:
  - дедупликация,
  - защита от пропусков,
  - контроль последовательности загрузок.
Идемпотентность и повторный запуск:
- Каждый шаг пайплайна должен быть либо:
  - идемпотентным (повтор не ломает данные), либо
  - обёрнут в транзакцию/стейджинг-таблицу с явным commit/rollback.
- Типичный прием:
  - грузим во временную таблицу;
  - считаем метрики/контрольные суммы;
  - переключаем/мерджим в боевую таблицу атомарно.
  Пример MERGE (SQL):
```
MERGE INTO dwh_customers d
USING staging_customers s
   ON d.customer_id = s.customer_id
WHEN MATCHED THEN
    UPDATE SET
        d.name       = s.name,
        d.segment    = s.segment,
        d.updated_at = s.updated_at
WHEN NOT MATCHED THEN
    INSERT (customer_id, name, segment, updated_at)
    VALUES (s.customer_id, s.name, s.segment, s.updated_at);
```
Ошибки, ретраи, алерты:
- Настройка ретраев с backoff для нестабильных источников.
- Явное разделение:
  - бизнес-ошибки (грязные данные, неконсистентность) → специализированные обработчики;
  - технические ошибки (сеть, таймауты, падение сервиса).
- Интеграция с алертингом:
  - e-mail/Slack/Telegram/Prometheus Alertmanager;
  - SLA: если загрузка отчета/X не успела до 09:00, алерт.

Airflow на практике (пример):

ETL-процесс в Airflow для ежедневной загрузки данных заказов в витрину:

from airflow import DAG
from airflow.operators.python import PythonOperator
from airflow.providers.postgres.operators.postgres import PostgresOperator
from datetime import datetime, timedelta

default_args = {
    "owner": "data-platform",
    "retries": 3,
    "retry_delay": timedelta(minutes=5),
}

def validate_raw(**context):
    # Проверка качества сырых данных
    pass

with DAG(
    dag_id="daily_orders_dwh_load",
    start_date=datetime(2025, 1, 1),
    schedule_interval="0 2 * * *",
    catchup=False,
    default_args=default_args,
) as dag:

    extract = PostgresOperator(
        task_id="extract_orders_to_staging",
        postgres_conn_id="source_db",
        sql="sql/extract_orders.sql"
    )

    validate = PythonOperator(
        task_id="validate_staging_orders",
        python_callable=validate_raw
    )

    load_dwh = PostgresOperator(
        task_id="merge_orders_to_dwh",
        postgres_conn_id="dwh_db",
        sql="sql/merge_orders_dwh.sql"
    )

    extract >> validate >> load_dwh

Ключевые моменты:

код ETL хранится в Git, проходит ревью, деплой через CI;
задачи разделены по ответственности;
даг прозрачен: видно, что, откуда и куда.

Интеграция с Go-сервисами:

В современных архитектурах часто:
- Go-сервисы предоставляют API/stream/queue-интерфейсы для данных;
- ETL-оркестратор дергает эти API, читает очереди или работает поверх message broker’ов;
- часть логики вносится в Go-сервисы (валидация, нормализация, enrichment).
Пример фрагмента сервиса на Go, который играет роль источника для ETL:
```
func (h *ExportHandler) ExportNewOrders(w http.ResponseWriter, r *http.Request) {
    ctx := r.Context()

    sinceStr := r.URL.Query().Get("since")
    since, err := time.Parse(time.RFC3339, sinceStr)
    if err != nil {
        http.Error(w, "invalid since", http.StatusBadRequest)
        return
    }

    orders, err := h.repo.GetOrdersUpdatedSince(ctx, since)
    if err != nil {
        http.Error(w, "internal error", http.StatusInternalServerError)
        return
    }

    w.Header().Set("Content-Type", "application/json")
    json.NewEncoder(w).Encode(orders)
}
```
Такой endpoint легко интегрируется в Airflow/ETL-пайплайн как один из шагов.

Резюме по ожидаемому уровню:

Понимание не только инструментов (Airflow, Oozie-подобные, собственные решения), но и:
- как проектировать пайплайны под большие объемы;
- как гарантировать целостность и повторяемость загрузок;
- как организовать мониторинг, алертинг, SLA;
- как разрулить отказ источника или частичную загрузку;
- как интегрировать это с Git, CI/CD и практиками код-ревью.

Такой подход показывает не просто «умение пользоваться ETL-инструментом», а умение строить устойчивую инфраструктуру для данных.

Вопрос 6. Какой опыт работы с Airflow: разработка DAG’ов или только мониторинг?

Таймкод: 00:07:05

Ответ собеседника: неполный. Указывает, что коммерческого опыта с Airflow нет, DAG’и писал только в pet-проекте на GitHub, не раскрывает прод-подходы, структуру, практики версионирования, мониторинга и отказоустойчивости.

Правильный ответ:

В промышленном контуре важен не сам факт «писал DAG», а умение строить устойчивые, поддерживаемые и наблюдаемые пайплайны. Ожидается понимание:

принципов работы Airflow,
стандартных паттернов организации DAG’ов,
best practices по надежности, идемпотентности и мониторингу.

Ключевые моменты, которые стоит отражать в ответе.

Общие принципы работы с Airflow:

Airflow используется как оркестратор задач:
- декларативное описание DAG (Directed Acyclic Graph);
- задачи (Tasks) связываются зависимостями;
- расписания (Schedule) задают периодичность;
- есть механизмы retries, SLA, алертов, XCom, пулов и приоритетов.
Всё описывается кодом (Python), хранится в Git, проходит code review и раскатывается через CI/CD.

Типичный опыт промышленного использования:

Разработка и поддержка DAG’ов

Создание DAG’ов для:
- ежедневных/часовых загрузок из операционных БД в DWH;
- построения витрин для BI;
- запусков SQL- или Python-трансформаций;
- интеграций с внешними системами (REST, gRPC, S3, Kafka и т.п.).
Использование стандартных операторов:
- BashOperator, PythonOperator,
- Postgres/MySQL/Oracle/Snowflake операторы,
- Sensors (FileSensor, ExternalTaskSensor),
- KubernetesPodOperator и др.
Соблюдение best practices:
- один DAG — логически цельный процесс;
- задачи атомарны и идемпотентны;
- тяжёлая логика — в отдельных скриптах/модулях, а не в теле DAG’а;
- конфигурации и креды — через Connections/Variables/Secret Backend.

Пример базовой структуры промышленного DAG:

from datetime import datetime, timedelta
from airflow import DAG
from airflow.operators.python import PythonOperator
from airflow.providers.postgres.operators.postgres import PostgresOperator

default_args = {
    "owner": "data-team",
    "depends_on_past": False,
    "retries": 3,
    "retry_delay": timedelta(minutes=5),
    "email_on_failure": True,
    "email": ["alerts@dataplatform.local"],
}

def validate_staging(**context):
    # Проверка количества строк, контрольных сумм и бизнес-правил
    # В проде здесь может быть обращение к отдельному сервису валидации
    pass

with DAG(
    dag_id="orders_daily_etl",
    start_date=datetime(2025, 1, 1),
    schedule_interval="0 2 * * *",  # ежедневно в 02:00
    catchup=False,
    default_args=default_args,
    max_active_runs=1,
    tags=["dwh", "orders"],
) as dag:

    extract_to_staging = PostgresOperator(
        task_id="extract_orders_to_staging",
        postgres_conn_id="orders_source",
        sql="sql/extract_orders_to_staging.sql",
    )

    validate = PythonOperator(
        task_id="validate_staging_orders",
        python_callable=validate_staging,
        provide_context=True,
    )

    load_dwh = PostgresOperator(
        task_id="merge_orders_to_dwh",
        postgres_conn_id="dwh_db",
        sql="sql/merge_orders_to_dwh.sql",
    )

    extract_to_staging >> validate >> load_dwh

Что важно в этом примере:

Конфигурация вынесена в default_args.
Есть retries и алерты.
Даг идемпотентен: повторный запуск не ломает данные (за счет правильного SQL в merge_orders_to_dwh.sql).

Идемпотентность и работа с данными

Каждый Task должен быть написан так, чтобы безопасно перезапускаться.
Типичные подходы:
- загрузка во временные таблицы с последующим MERGE;
- очистка/перезапись партиции за конкретный день;
- использование execution_date/логических окон.

Пример SQL для идемпотентной загрузки витрины за день:

DELETE FROM mart_daily_orders
WHERE order_date = '{{ ds }}';

INSERT INTO mart_daily_orders (order_date, customer_id, total_amount)
SELECT
    DATE(o.created_at) AS order_date,
    o.customer_id,
    SUM(o.amount) AS total_amount
FROM dwh_orders o
WHERE DATE(o.created_at) = '{{ ds }}'
GROUP BY DATE(o.created_at), o.customer_id;

Мониторинг, алертинг и эксплуатация

Использование:
- SLA (sla, sla_miss_callback);
- retries для нестабильных интеграций;
- логов задач для диагностики.
Интеграция с:
- email, Slack, Teams, Telegram;
- Prometheus/Grafana для метрик (количество фейлов, длительность задач).
Практика:
- при падении — анализ причины, фиксация дефекта в трекере, при необходимости фиксы в ETL-логике или исходных данных.

Управление зависимостями и модульностью

DAG’и не превращаются в «спагетти»:
- сложные пайплайны разбиваются на несколько DAG’ов, связанные через ExternalTaskSensor или события;
- общая логика (валидация, SQL-шаблоны, клиенты к API) выносится в общие Python-модули.
Все изменения DAG’ов:
- проходят через Git, code review, тестирование на dev/stage Airflow-кластере.

Интеграция с сервисами и Go/микросервисами

Airflow управляет вызовами:
- Go-сервисов с API-эндпоинтами экспорта/импорта;
- задач, которые публикуют/читают сообщения из брокеров.
Логика:
- тяжелая бизнес-логика остаётся в сервисах;
- Airflow оркестрирует последовательность действий, а не заменяет приложения.

Пример PythonOperator, дергающий HTTP API (который может быть реализован на Go):

import requests
from airflow.operators.python import PythonOperator

def fetch_new_orders(**context):
    since = context["data_interval_start"].isoformat()
    resp = requests.get(
        "https://orders-service.internal/api/export",
        params={"since": since},
        timeout=30,
    )
    resp.raise_for_status()
    # Далее данные пишутся в staging или объектное хранилище

fetch_task = PythonOperator(
    task_id="fetch_new_orders",
    python_callable=fetch_new_orders,
)

Кратко:

Полноценный ответ про опыт с Airflow должен показывать:

умение разрабатывать DAG’и как код;
понимание идемпотентности, инкрементальных загрузок, работы с execution_date;
использование retries, SLA, алертов и логгирования;
хранение дагов в Git, деплой через CI/CD;
практики разделения окружений (dev/stage/prod) и безопасного внесения изменений.

Даже если часть опыта получена на pet-проектах, важно демонстрировать именно такое архитектурное мышление и практики, которые легко переносятся в промышленную среду.

Вопрос 7. Какие типы операторов Airflow можно назвать и в каких случаях они используются?

Таймкод: 00:07:35

Ответ собеседника: неполный. Упомянул только PythonOperator и BashOperator, не назвал операторы для работы с БД, внешними системами, сенсоры, провайдеры и паттерны использования, что демонстрирует ограниченное знакомство с экосистемой Airflow.

Правильный ответ:

Важна не просто механическая перечислялка операторов, а понимание их ролей и правильного применения при построении надёжных и читаемых DAG’ов.

Ключевые категории:

Базовые операторы

PythonOperator:
- Запуск Python-функции.
- Используется для лёгкой логики, интеграций, валидаций, работы с API.
- Важный момент: тяжелую бизнес-логику и большой код лучше выносить в отдельные модули/сервисы; DAG — это «оркестратор», а не место для «толстого» кода.
BashOperator:
- Выполнение shell-команд/скриптов.
- Подходит для запуска CLI-утилит, скриптов деплоя, простых data-job’ов.
- В проде требует аккуратности: логирование, таймауты, явная обработка ошибок.

Операторы для работы с базами данных и SQL

(конкретные реализации зависят от провайдера)

PostgresOperator / MySqlOperator / MsSqlOperator / SnowflakeOperator / BigQueryInsertJobOperator и др.:
- Выполнение SQL-скриптов (DDL/DML, ETL-трансформации).
- Часто используются для шагов «L» и «T» в ETL/ELT: загрузка в стейджинг, MERGE в DWH, построение витрин.
Generic SQL-операторы (например, SQLExecuteQueryOperator в новых версиях):
- Унифицированный интерфейс: один оператор, разные conn_id и движки.

Пример использования PostgresOperator:

from airflow.providers.postgres.operators.postgres import PostgresOperator

load_dwh = PostgresOperator(
    task_id="merge_orders_to_dwh",
    postgres_conn_id="dwh_db",
    sql="sql/merge_orders_dwh.sql",
)

Сенсоры (Sensors)

Сенсоры — это специализированные операторы, которые ожидают наступления условия:

FileSensor:
- Ждет появления файла (например, отчета, выгрузки партнера).
ExternalTaskSensor:
- Ждет завершения задачи или DAG в другом пайплайне.
S3KeySensor, HdfsSensor, SqlSensor и т.п.:
- Ждут события/данных во внешней системе.

Сенсоры важны для декомпозиции больших процессов на несколько DAG’ов и для синхронизации с внешними источниками.

Операторы для работы с внешними системами и облаками

Через провайдеры доступны десятки операторов, среди них:

HTTP-операторы:
- SimpleHttpOperator — вызов REST API.
Cloud-провайдеры:
- GCP (BigQuery, GCS, Dataproc),
- AWS (S3, Redshift, EMR, Lambda),
- Azure и др.
SSHOperator:
- Выполнение команд на удаленных серверах.

Они используются для интеграций: дернуть API сервиса на Go, запустить Spark-job, выгрузить данные в объектное хранилище и т.п.

Kubernetes / контейнерные операторы

Для изолированного и масштабируемого запуска задач:

KubernetesPodOperator:
- Запуск пода с нужным образом и окружением.
DockerOperator:
- Выполнение контейнера локально или на Docker-демоне.

Это стандартный подход для тяжёлых вычислений и data-job’ов: Airflow только оркестрирует, а логика живет в контейнере.

SubDagOperator / TaskGroup / Dynamic Tasks

TaskGroup:
- Группировка задач логически, без создания вложенных DAG’ов.
- Улучшает читаемость графа.
SubDagOperator (устаревающий паттерн, используется всё реже):
- Вложенный DAG как задача; в новых инсталляциях предпочитают TaskGroup и ExternalTaskSensor.

Branching и управление потоком

BranchPythonOperator:
- Ветвление логики в зависимости от условия (if-else-флоу).
ShortCircuitOperator:
- Прерывание дальнейших задач при невыполнении условия.

Используются для сценариев:

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

Dummy/EmptyOperator

EmptyOperator (ранее DummyOperator):
- Используется как:
  - заглушка,
  - точка синхронизации,
  - улучшение читаемости DAG.

Кастомные операторы

В реальных проектах часто создаются свои операторы:

Наследование от BaseOperator;
Инкапсуляция типичных паттернов:
- загрузка из конкретной системы,
- типовая валидация,
- вызов конкретного сервиса;
Это повышает переиспользуемость и снижает дублирование кода по DAG’ам.

Очень упрощенный пример кастомного оператора:

from airflow.models import BaseOperator
from airflow.utils.decorators import apply_defaults
import requests

class ExportFromServiceOperator(BaseOperator):

    @apply_defaults
    def __init__(self, endpoint, *args, **kwargs):
        super().__init__(*args, **kwargs)
        self.endpoint = endpoint

    def execute(self, context):
        resp = requests.get(self.endpoint, timeout=30)
        resp.raise_for_status()
        # Дополнительно: сохранить данные, залогировать объем, вернуть результат через XCom

Важный практический аспект

При перечислении операторов полезно показать понимание:

когда использовать PythonOperator vs SQL-операторы vs KubernetesPodOperator;
почему не стоит зашивать сложный SQL/Python прямо в DAG, а выносить в версииуемые файлы/модули;
как сочетать сенсоры, ветвление и ретраи, чтобы пайплайны были:
- идемпотентными,
- наблюдаемыми,
- легко поддерживаемыми.

Краткий «идеальный» ответ:

Назвать несколько ключевых операторов: PythonOperator, BashOperator, SQL/DB-операторы (PostgresOperator, MySqlOperator и т.п.), сенсоры (FileSensor, ExternalTaskSensor), HTTP-операторы, KubernetesPodOperator/DockerOperator, BranchPythonOperator, EmptyOperator.
Объяснить по 1–2 практических кейса, где какой используется.
Показать понимание того, что выбор оператора — часть архитектуры пайплайна, а не просто знание справочника.

Вопрос 8. Зачем в Airflow используются сенсоры?

Таймкод: 00:07:46

Ответ собеседника: неполный. Верно указывает, что сенсоры нужны для отслеживания событий, но не раскрывает, какие именно сценарии покрывают сенсоры, как они работают, какие есть типы, ограничения и best practices.

Правильный ответ:

Сенсоры в Airflow используются для реализации «event-driven» поведения внутри DAG: они позволяют задачам не просто запускаться по расписанию, а зависеть от появления данных, завершения других пайплайнов или наступления внешних условий.

Их ключевая цель — синхронизировать выполнение задач с готовностью ресурсов и данных.

Основные сценарии использования сенсоров:

Ожидание появления данных:
- Файл в хранилище (локальном, HDFS, S3, FTP и т.п.).
- Партиция/таблица в DWH.
- Обновление snapshot’а или выгрузки от стороннего поставщика.
- Пример:
  - Не запускать трансформацию, пока не пришел входной CSV/Parquet-файл за нужную дату.
Ожидание завершения внешних или связанных процессов:
- ExternalTaskSensor:
  - Ждет выполнения конкретной задачи или всего DAG в другом пайплайне.
  - Позволяет разбивать монолитные процессы на независимые DAG’и:
    - один DAG загружает сырые данные,
    - второй строит витрину, но стартует только после успешного завершения первого.
- Это снижает связанность и упрощает сопровождение.
Ожидание состояния во внешней системе:
- Сенсоры для API, очередей, хранилищ:
  - пример: ждать статуса «готово» от внешнего сервиса, который асинхронно обрабатывает данные;
  - ждать появления сообщения/флага, подтверждающего завершение.

Как сенсоры работают логически:

Сенсор — это задача, которая периодически (poke) проверяет условие:
- если условие не выполнено — задача остается RUNNING и повторяет проверку через заданный интервал;
- если выполнено — задача завершается SUCCESS и даёт двигаться дальше зависимым задачам;
- если истек timeout — задача падает (FAILED), что сигнализирует проблему (например, данные не пришли).
Ключевые параметры:
- poke_interval — как часто проверять;
- timeout — максимальное время ожидания;
- mode:
  - poke (по умолчанию; задача занимает worker всё время ожидания),
  - reschedule (более эффективный режим: задача освобождает worker между попытками, лучше для большого числа сенсоров).

Типичные примеры:

Ожидание файла (FileSensor):

from airflow.sensors.filesystem import FileSensor

wait_for_file = FileSensor(
    task_id="wait_for_daily_file",
    filepath="/data/in/daily_{{ ds }}.csv",
    poke_interval=60,
    timeout=60 * 60,  # ждать до часа
    mode="reschedule",
)

Ожидание завершения другого DAG (ExternalTaskSensor):

from airflow.sensors.external_task import ExternalTaskSensor

wait_for_upstream_dag = ExternalTaskSensor(
    task_id="wait_for_raw_load",
    external_dag_id="raw_layer_daily_load",
    external_task_id=None,  # ждать весь DAG
    execution_date_fn=lambda dt: dt,  # синхронизация по дате
    poke_interval=120,
    timeout=4 * 60 * 60,
    mode="reschedule",
)

После этого можно безопасно запускать витрины:

wait_for_upstream_dag >> build_mart_task

Best practices и важные нюансы:

Использовать сенсоры для явного управления зависимостями между данными и пайплайнами:
- вместо «магического» ожидания через sleep или надежды, что «по времени успеет».
Снижать нагрузку на кластер:
- использовать mode="reschedule" для долгих ожиданий;
- разумно выбирать poke_interval;
- не создавать сотни блокирующих сенсоров, которые висят часами.
Четко задавать timeout:
- если данные не пришли в окне ожидания — это инцидент, должен сработать алерт.
Не злоупотреблять сенсорами:
- если внешнюю зависимость можно описать иначе (например, триггером из внешней системы через REST API, TriggerDagRunOperator или event-based подход) — это часто лучше, чем активное «пуление».

Суть:

Сенсоры — инструмент синхронизации и контроля готовности данных/процессов. Они позволяют строить надежные, детерминированные пайплайны, которые стартуют не «по надежде» и не «по таймеру на глаз», а по факту выполнения необходимых условий. Грамотное использование сенсоров — один из признаков зрелого подхода к оркестрации данных.

Вопрос 9. Как связать несколько DAG в Airflow, чтобы запускать их последовательно?

Таймкод: 00:08:02

Ответ собеседника: неправильный. Признает, что не знает, не предлагает корректного механизма связки DAG’ов.

Правильный ответ:

В Airflow есть несколько корректных способов организовать последовательное выполнение нескольких DAG’ов, когда один должен стартовать только после успешного завершения другого. Ключевая идея — не превращать всё в один гигантский DAG, а грамотно выстраивать зависимости между независимыми пайплайнами.

Основные подходы.

ExternalTaskSensor: зависимость одного DAG от другого

Классический и наиболее распространенный способ:

В downstream-DAG (зависимом) используется ExternalTaskSensor, который:
- ожидает успешного выполнения конкретной задачи или всего DAG в upstream-DAG;
- синхронизируется по execution_date (или кастомной функции).

Пример: DAG B должен запускаться после успешного завершения DAG A за тот же день.

DAG A (источник):

with DAG(
    dag_id="dag_a_raw_load",
    schedule_interval="0 1 * * *",
    start_date=datetime(2025, 1, 1),
    catchup=False,
) as dag_a:
    load_raw = PythonOperator(
        task_id="load_raw",
        python_callable=load_raw_data,
    )

DAG B (зависимый):

from airflow.sensors.external_task import ExternalTaskSensor

with DAG(
    dag_id="dag_b_transform",
    schedule_interval="0 2 * * *",
    start_date=datetime(2025, 1, 1),
    catchup=False,
) as dag_b:

    wait_for_dag_a = ExternalTaskSensor(
        task_id="wait_for_dag_a",
        external_dag_id="dag_a_raw_load",
        external_task_id=None,  # ждать успешного завершения всего DAG A
        # при необходимости можно использовать execution_date_fn
        mode="reschedule",
        poke_interval=60,
        timeout=3 * 60 * 60,
    )

    transform = PythonOperator(
        task_id="run_transform",
        python_callable=run_transform_logic,
    )

    wait_for_dag_a >> transform

Что важно:

external_task_id=None — значит ждем весь DAG;
mode="reschedule" — не держим worker занятым;
задаем timeout, чтобы не ждать бесконечно.

Так можно выстраивать цепочки: DAG A → DAG B → DAG C.

TriggerDagRunOperator и event-driven запуск

Подход, когда один DAG явно триггерит другой.

Upstream-DAG по завершении вызывает TriggerDagRunOperator:
- запускает нужный DAG;
- можно передать конфигурацию;
- реализация последовательности более «push-моделью», в отличие от ExternalTaskSensor («pull-модель»).

Пример: DAG A после успеха стартует DAG B.

DAG A:

from airflow.operators.trigger_dagrun import TriggerDagRunOperator

with DAG(
    dag_id="dag_a_raw_load",
    schedule_interval="0 1 * * *",
    start_date=datetime(2025, 1, 1),
    catchup=False,
) as dag_a:
    
    load_raw = PythonOperator(
        task_id="load_raw",
        python_callable=load_raw_data,
    )

    trigger_b = TriggerDagRunOperator(
        task_id="trigger_dag_b",
        trigger_dag_id="dag_b_transform",
        reset_dag_run=True,  # опционально, для повторных прогонов
        wait_for_completion=False,  # можно и True, в зависимости от сценария
    )

    load_raw >> trigger_b

DAG B:

with DAG(
    dag_id="dag_b_transform",
    schedule_interval=None,  # запускается по триггеру
    start_date=datetime(2025, 1, 1),
    catchup=False,
) as dag_b:
    transform = PythonOperator(
        task_id="run_transform",
        python_callable=run_transform_logic,
    )

Особенности:

DAG B не привязан к cron — он стартует по событию.
Это удобно при сложных цепочках или нерегулярных нагрузках.

Комбинация: сенсоры + TriggerDagRunOperator

Более сложные сценарии:

можно:
- в DAG B использовать ExternalTaskSensor, чтобы ждать конкретный run DAG A;
- в других случаях — TriggerDagRunOperator для асинхронного запуска цепочки DAG’ов;
выбор зависит от того, нужна ли жесткая связка по execution_date или гибкий event-driven запуск.

Anti-pattern: не связывать DAG’и через внутренние хаковские решения

Чего делать не стоит:

использовать прямые обращения к Airflow-базе из задач;
руками крутить состояния задач через API без ясного контракта;
городить «суперDAG», к которому все привязано, вместо логичной декомпозиции.

Когда лучше один большой DAG, а когда несколько

Полезно понимать критерии:

Один DAG:
- если это один логический бизнес-процесс;
- если зависимости линейные и управляемые.
Несколько DAG’ов:
- когда есть переиспользуемые части (например, отдельный DAG для загрузки raw-слоя для разных витрин);
- когда разные команды отвечают за разные части пайплайна;
- когда нужно разделить SLA, права доступа, окна исполнения.

Связь DAG’ов через ExternalTaskSensor и TriggerDagRunOperator — стандарт, который:

делает зависимости явными;
сохраняет декомпозицию;
упрощает сопровождение и мониторинг.

Кратко:

Для последовательного запуска нескольких DAG’ов в Airflow корректно использовать:

ExternalTaskSensor — downstream-DAG ждёт успешного завершения upstream-DAG;
TriggerDagRunOperator — upstream-DAG по завершении явно запускает следующий. Эти механизмы обеспечивают прозрачную, детерминированную и поддерживаемую оркестрацию сложных цепочек пайплайнов.

Вопрос 10. Что такое витринное хранилище данных (DWH) и data lake, в чём их отличие и как выглядит общая архитектура слоёв хранилища?

Таймкод: 00:08:48

Ответ собеседника: правильный. Описывает DWH как корпоративное аналитическое хранилище со структурированными данными в СУБД. Data lake — как хранилище для сырых и неструктурированных данных (Hadoop, S3), которое может служить источником для DWH. Перечисляет слои: стейдж, core, витрины (datamart), упоминает Greenplum/PostgreSQL для DWH и ClickHouse для витрин и быстрых агрегаций; демонстрирует понимание ролей слоёв.

Правильный ответ:

Для систем аналитики и отчетности важно понимать различия между DWH, data lake и архитектурой слоёв, потому что от этого зависит масштабируемость, качество данных, сложность ETL и производительность.

Разберём по порядку.

DWH (Data Warehouse) — что это и зачем

DWH — это централизованное, структурированное хранилище данных, ориентированное на аналитические запросы и консистентное представление данных о деятельности компании.

Ключевые характеристики:

Структурированность:
- жёстко заданные схемы (tables, columns, types, constraints);
- продуманная модель данных (звезда, снежинка, Data Vault, корпоративная модель).
Интеграция источников:
- данные стягиваются из разных систем: CRM, ERP, биллинг, логистика, АБС, веб-сервисы;
- приводятся к единым справочникам, ключам, форматам.
Историчность:
- хранение изменений во времени (SCD), возможность ответить «как было на дату X».
Оптимизация под аналитику:
- индексы, партиционирование, колоночное хранение;
- типичные запросы: агрегации, отчёты, витрины, дашборды.
Технологии:
- PostgreSQL/Greenplum, Vertica, Snowflake, BigQuery, ClickHouse, MS SQL, Oracle и др.
- Нередко: отдельный DWH (например, Greenplum/PostgreSQL) + специализированный движок для быстрых витрин (ClickHouse).

Назначение DWH:

единый источник правды (single source of truth),
стабильная база для BI, отчётности, финансов, управленческой аналитики.

Data Lake — что это и зачем

Data lake — это хранилище сырых данных (raw data) в максимально оригинальном виде, без жёстких требований к схеме на входе.

Ключевые характеристики:

Гибкость:
- хранит структурированные, полуструктурированные и неструктурированные данные:
  - CSV, JSON, Parquet, Avro,
  - логи, события (event streams),
  - файлы (PDF, изображения) и т.п.
Schema-on-read:
- схема применяется при чтении (анализе), а не заранее.
- это упрощает приём данных, но усложняет последующий анализ и требует дисциплины.
Масштабируемость и цена:
- обычно основан на распределённых файловых системах/облачных хранилищах:
  - HDFS, S3, аналогичные объектные хранилища.
Роль:
- источник для DWH и витрин;
- площадка для data science, машинного обучения, продвинутой аналитики;
- позволяет хранить данные «на вырост», без немедленного моделирования.

Ключевое отличие DWH и Data Lake:

DWH:
- строго структурирован,
- жёстко контролируется качество и семантика,
- оптимизирован под бизнес-отчётность и проверенную аналитику.
Data Lake:
- более хаотичен по структуре,
- быстрее принимает новые источники,
- подходит для исследовательской и экспериментальной аналитики,
- сам по себе не гарантирует качества данных.

Типовая архитектура слоёв хранилища

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

Raw / Landing / Bronze (сырые данные)

Назначение:
- приём данных из источников в максимально неизменном виде.
Характеристики:
- минимальные преобразования (часто только формат/транспорт);
- необходим для аудита: всегда можно пересчитать.
Примеры:
- выгрузка логов в S3/HDFS;
- сырые JSON-события;
- полные дампы из OLTP-систем.

Staging / ODS (operational data store)

Назначение:
- временный буфер для последующей загрузки в core/DWH.
Характеристики:
- данные очищены от очевидного мусора, приведены к базовым типам;
- используется для инкрементальной загрузки (сравнение с прошлым состоянием).
Часто реализуется в реляционной СУБД или в том же DWH.

Пример простого staging-запроса (SQL):

INSERT INTO staging_orders (order_id, customer_id, amount, updated_at)
SELECT
    order_id,
    customer_id,
    amount,
    updated_at
FROM external_orders_raw
WHERE load_date = CURRENT_DATE;

Core / DWH / Silver (нормализованный корпоративный слой)

Назначение:
- консистентная модель данных компании;
- единые справочники, ключи, истории, связи сущностей.
Характеристики:
- продуманная модель:
  - факты (transactions, measures),
  - измерения (dimensions: клиенты, продукты, контрагенты).
- поддержка историчности (SCD Type 1/2).
Пример: таблица клиентов с историей:

CREATE TABLE dwh_customer_hist (
    customer_sk      BIGSERIAL PRIMARY KEY,
    customer_id      BIGINT NOT NULL,
    name             TEXT,
    segment          TEXT,
    valid_from       DATE NOT NULL,
    valid_to         DATE,
    is_current       BOOLEAN NOT NULL
);

Data Marts / Витрины / Gold

Назначение:
- оптимизированные под конкретные отчёты, команды или продукты представления данных.
Характеристики:
- денормализованные структуры;
- предрасчитанные агрегаты;
- быстрый ответ для BI-систем и дашбордов.
Технологии:
- могут жить в том же DWH (PostgreSQL/Greenplum),
- или в специализированных движках: ClickHouse для быстрых агрегаций, OLAP-кубы.
Пример витрины:

CREATE TABLE mart_daily_revenue AS
SELECT
    order_date,
    region,
    SUM(amount) AS revenue,
    COUNT(DISTINCT customer_id) AS unique_customers
FROM dwh_orders
GROUP BY order_date, region;

Дополнительные слои / паттерны

Часто добавляют:

Sandbox / Labs:
- для аналитиков и data scientists;
- данные берутся из DWH/lake, эксперименты не ломают боевую модель.
Serving Layer / API-слой:
- поверх витрин поднимаются сервисы (на Go, например),
- которые отдают агрегаты и метрики в реальном времени другим системам.

Пример простого агрегирующего endpoint на Go:

type RevenueRepo interface {
    GetDailyRevenue(ctx context.Context, date time.Time) (float64, error)
}

type RevenueHandler struct {
    repo RevenueRepo
}

func (h *RevenueHandler) GetDailyRevenue(w http.ResponseWriter, r *http.Request) {
    ctx := r.Context()
    dateStr := r.URL.Query().Get("date")
    if dateStr == "" {
        http.Error(w, "date is required", http.StatusBadRequest)
        return
    }

    date, err := time.Parse("2006-01-02", dateStr)
    if err != nil {
        http.Error(w, "invalid date", http.StatusBadRequest)
        return
    }

    revenue, err := h.repo.GetDailyRevenue(ctx, date)
    if err != nil {
        http.Error(w, "internal error", http.StatusInternalServerError)
        return
    }

    resp := map[string]interface{}{
        "date":    dateStr,
        "revenue": revenue,
    }
    w.Header().Set("Content-Type", "application/json")
    json.NewEncoder(w).Encode(resp)
}

Связь Data Lake и DWH в общей архитектуре

Корректный взгляд:

Data Lake:
- как «слой приёма и хранения всего» (сырые события, файлы, исторические данные);
- обеспечивает гибкость, масштаб, дешёвое хранение.
DWH:
- как слой проверенных, очищенных, интегрированных данных;
- питается из Data Lake и оперативных систем через формализованные ETL/ELT-процессы;
- служит основой для витрин, отчётности и управленческих решений.

Таким образом:

Data Lake — про ширину и гибкость.
DWH — про качество, структуру и доверие.
Витрины — про скорость и удобство потребления.
Многослойная архитектура (raw/stage/core/marts) позволяет:
- управлять качеством данных,
- отслеживать lineage,
- безопасно развивать модель,
- эффективно строить отчётность и подключать сервисы/микросервисы поверх данных.

Вопрос 11. Кратко описать схемы «звезда», «снежинка» и подход Data Vault.

Таймкод: 00:10:39

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

Правильный ответ:

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

схема «звезда» (Star Schema);
схема «снежинка» (Snowflake Schema);
Data Vault (DV 2.0 как современный стандарт).

Важно понимать структуру, плюсы/минусы и контексты применения каждого.

Схема «звезда» (Star Schema)

Суть:

В центре — таблица фактов (fact table).
Вокруг — денормализованные таблицы измерений (dimension tables), напрямую связанные с фактом по surrogate key.
Визуально — звезда: факт в центре, измерения — лучи.

Таблица фактов:

Хранит события/транзакции/агрегаты:
- ссылки (FK) на измерения: дата, клиент, продукт, магазин, канал;
- числовые показатели (measures): количество, сумма, цена, маржа.
Обычно большая по объему.

Таблицы измерений:

Хранят контекст:
- измерение Клиент: атрибуты клиента;
- измерение Продукт: характеристики продукта;
- измерение Время: календарные атрибуты;
- измерение География: города, регионы и т.п.
Денормализованы: «всё про сущность в одной таблице», чтобы упростить запросы.

Плюсы:

Простые и читаемые SQL-запросы.
Удобно для BI-инструментов и аналитиков.
Обычно хорошая производительность (особенно в колоночных движках).

Минусы:

Дублирование данных в измерениях;
Менее гибко при очень сложных иерархиях и частых структурных изменениях измерений.

Пример упрощённой «звезды» (SQL):

-- Измерение продукта
CREATE TABLE dim_product (
    product_key      BIGSERIAL PRIMARY KEY,
    product_id       BIGINT,
    name             TEXT,
    category         TEXT,
    brand            TEXT
);

-- Измерение даты
CREATE TABLE dim_date (
    date_key         INTEGER PRIMARY KEY,
    date_value       DATE,
    year             INTEGER,
    month            INTEGER,
    day              INTEGER
);

-- Факт продаж
CREATE TABLE fact_sales (
    sale_id          BIGSERIAL PRIMARY KEY,
    date_key         INTEGER REFERENCES dim_date(date_key),
    product_key      BIGINT  REFERENCES dim_product(product_key),
    quantity         NUMERIC,
    amount           NUMERIC
);

Схема «снежинка» (Snowflake Schema)

Суть:

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

Пример:

Вместо одной dim_product с category/brand:
- dim_product с ссылкой на dim_category и dim_brand;
- dim_category может ссылаться на dim_category_group и т.д.

Плюсы:

Меньше дублирования данных в измерениях.
Чёткая нормализованная структура для сложных иерархий.

Минусы:

Запросы сложнее: больше JOIN’ов.
BI-инструменты и аналитики сложнее ориентируются.
Иногда хуже производительность (особенно при большом числе JOIN’ов).

Пример «снежинки» (SQL):

CREATE TABLE dim_category (
    category_key     BIGSERIAL PRIMARY KEY,
    category_name    TEXT
);

CREATE TABLE dim_product (
    product_key      BIGSERIAL PRIMARY KEY,
    product_id       BIGINT,
    name             TEXT,
    category_key     BIGINT REFERENCES dim_category(category_key)
);

CREATE TABLE fact_sales (
    sale_id          BIGSERIAL PRIMARY KEY,
    product_key      BIGINT REFERENCES dim_product(product_key),
    quantity         NUMERIC,
    amount           NUMERIC
);

Когда использовать:

«Звезда» — предпочтительна как основа витрин для BI: проще, быстрее, понятнее.
«Снежинка» — допустима, если нужны строгие иерархии и уменьшается объем хранения, но сложность запросов оправдана.

Подход Data Vault

Data Vault — методология моделирования корпоративного хранилища, ориентированная на:

масштабируемость;
гибкость при добавлении новых источников;
сохранение истории и происхождения данных;
устойчивость к изменениям исходных систем.

Ключевая идея:

Сначала строится устойчивый «ядро-интегратор» (Raw Vault), не завязанный на конкретные отчёты.
Уже поверх него строятся витрины в формате звезды/снежинки (Business Vault, marts).

Основные сущности Data Vault:

Hubs (хабы)

Таблицы бизнес-ключей.
Хранят:
- натуральный бизнес-ключ (например, customer_number, account_id);
- surrogate key (hub_id);
- метаданные загрузки (load_date, source_system).
Представляют уникальные бизнес-сущности.

CREATE TABLE hub_customer (
    customer_hkey    BYTEA PRIMARY KEY,
    customer_id      TEXT,
    load_dts         TIMESTAMP,
    source           TEXT
);

Links (линки)

Таблицы связей между хабами.
Хранят:
- ссылки на hub-ключи (например, customer_hkey, account_hkey);
- метаданные загрузки.
Моделируют отношения «клиент–счет», «заказ–товар» и т.п.

CREATE TABLE link_customer_account (
    customer_hkey    BYTEA,
    account_hkey     BYTEA,
    load_dts         TIMESTAMP,
    source           TEXT,
    PRIMARY KEY (customer_hkey, account_hkey)
);

Satellites (спутники)

Таблицы атрибутов и истории изменений для хабов и линков.
Хранят:
- описательные поля (имя клиента, статус, лимит);
- временные границы действия версии;
- источник и метаданные.

CREATE TABLE sat_customer_details (
    customer_hkey    BYTEA,
    name             TEXT,
    segment          TEXT,
    valid_from_dts   TIMESTAMP,
    valid_to_dts     TIMESTAMP,
    load_dts         TIMESTAMP,
    source           TEXT
);

Ключевые преимущества Data Vault:

Гибкость:
- легко добавлять новые источники и атрибуты:
  - новый satellite вместо ломки существующей структуры.
Аудируемость:
- подробные метаданные загрузки;
- возможность отследить, откуда пришли данные.
Масштабируемость:
- модель хорошо ложится на MPP и распределенные системы.
Стабильное ядро:
- изменение бизнес-требований отражается в витринах, а не ломает всю модель хранения.

Недостатки/особенности:

Модель сложнее для прямого использования аналитиками:
- для BI почти всегда строят слой витрин (звезда/снежинка) поверх Data Vault.
Требует дисциплины в ETL/ELT:
- строгие правила генерации ключей,
- управление историей,
- единые паттерны загрузки.

Типичная связка:

Data Lake:
- сырые данные.
Raw Vault (Data Vault):
- интеграция, история, источник правды.
Business Vault:
- обогащенные и подготовленные структуры.
Data Marts (звезда/снежинка):
- удобные модели для BI, отчетности, сервисов.

Практическая перспектива:

Витрины в формате «звезды»/«снежинки»:
- работают непосредственно под отчеты, ClickHouse/Greenplum/PostgreSQL.
Data Vault:
- используется как внутренний «скелет» корпоративного DWH, позволяющий эволюционировать без постоянных миграций ядра.
Часто архитектура выглядит так:
- источники → data lake/raw → Data Vault (raw/business) → витрины (star/snowflake) → BI/сервисы.

Кратко:

«Звезда» — простая, быстрая, удобная для аналитиков модель: факт в центре, измерения вокруг.
«Снежинка» — нормализованный вариант «звезды» с разнесёнными измерениями, меньше дублирования, сложнее запросы.
Data Vault — методология построения гибкого и масштабируемого корпоративного слоя, ориентированного на интеграцию и историю; используется как фундамент, поверх которого строятся витрины в более удобных моделях.

Вопрос 12. Кратко описать схемы «звезда», «снежинка» и Data Vault в контексте моделирования хранилища.

Таймкод: 00:10:39

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

Правильный ответ:

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

Кратко и по сути:

Схема «звезда»:
- Центральная таблица фактов (facts): транзакции, события, измеримые показатели (amount, quantity, duration и т.п.).
- Вокруг — таблицы измерений (dimensions): сущности и их атрибуты (клиенты, продукты, даты, география).
- Связи: факт содержит surrogate keys измерений.
- Особенности:
  - денормализованные измерения;
  - простые для чтения и написания запросы;
  - оптимальный формат для витрин, BI, дашбордов.
- Типичный выбор для конечного слоя (data mart / витрина).
Схема «снежинка»:
- Логическое развитие «звезды» с нормализацией измерений.
- Измерения разбиваются на подтаблицы по иерархиям (продукт → категория → бренд, гео → город → регион → страна и т.д.).
- Особенности:
  - меньше дублирования данных в измерениях;
  - более формальная модель;
  - больше JOIN’ов, сложнее запросы, иногда хуже производительность.
- Используется, когда важна строгая нормализация и сложные иерархии, но для витрин часто остаются ближе к «звезде».
Data Vault:
- Подход для моделирования корпоративного ядра DWH, ориентированный на стабильность, масштабируемость и аудит.
- Основные сущности:
  - Hubs: бизнес-ключи сущностей (customer, account, contract).
  - Links: связи между хабами (customer–account, order–product).
  - Satellites: атрибуты и история изменений для хабов и линков (значения полей, период действия, источник).
- Особенности:
  - гибко переживает изменения источников и бизнес-логики;
  - чётко хранит историю и происхождение данных;
  - хорошо масштабируется при большом количестве источников.
- Обычно не используется напрямую для BI-пользователей:
  - поверх Data Vault строятся витрины уже в формате «звезды» или «снежинки».

Практическая связка:

Data Lake / raw-слой → Data Vault (корпоративное ядро) → витрины (звезда/снежинка).
Data Vault решает задачу интеграции и историчности,
«Звезда» и «снежинка» — задачу удобства и скорости аналитических запросов.

Вопрос 13. Что такое нормальные формы и какие из них наиболее важны при проектировании?

Таймкод: 00:12:23

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

Правильный ответ:

Нормальные формы — это формализованные правила декомпозиции таблиц в реляционных БД, которые помогают:

устранить избыточность данных;
избежать аномалий вставки, обновления и удаления;
сделать модель данных логичной, предсказуемой и удобной для сопровождения.

На практике чаще всего важны первые три нормальные формы: 1NF, 2NF, 3NF. Для аналитических моделей и витрин их требования могут осознанно ослабляться (денормализация ради производительности), но при проектировании OLTP и ядра DWH понимание нормализации обязательно.

Кратко по основным нормальным формам.

1NF (первая нормальная форма)

Требования:

Каждое поле — атомарное значение (нет массивов, списков, JSON вперемешку, «через запятую»).
Нет повторяющихся групп столбцов (item1, item2, item3).
Каждая строка уникальна (обычно через первичный ключ).

Пример нарушения:

-- Плохо: список телефонов в одном поле
CREATE TABLE customer (
    id          BIGINT PRIMARY KEY,
    name        TEXT,
    phones      TEXT  -- "12345, 67890"
);

Исправление (1NF):

CREATE TABLE customer (
    id          BIGINT PRIMARY KEY,
    name        TEXT
);

CREATE TABLE customer_phone (
    customer_id BIGINT REFERENCES customer(id),
    phone       TEXT,
    PRIMARY KEY (customer_id, phone)
);

2NF (вторая нормальная форма)

Актуальна, когда есть составной первичный ключ.

Требование:

Таблица должна быть в 1NF.
Каждый неключевой атрибут полностью функционально зависит от всего первичного ключа, а не от его части.
Нельзя хранить атрибуты, которые зависят только от части составного ключа.

Пример нарушения:

-- PK (order_id, product_id)
CREATE TABLE order_item (
    order_id      BIGINT,
    product_id    BIGINT,
    order_date    DATE,      -- зависит только от order_id
    product_name  TEXT,      -- зависит только от product_id
    quantity      INT,
    PRIMARY KEY (order_id, product_id)
);

Здесь:

order_date зависит от order_id,
product_name зависит от product_id,
они не зависят от полной комбинации (order_id, product_id) → нарушение 2NF.

Исправление:

В order: (order_id, order_date, ...),
В product: (product_id, product_name, ...),
В order_item: только ссылки + количество.

2NF снижает дублирование и логическую путаницу при составных ключах.

3NF (третья нормальная форма)

Самая важная на практике для OLTP-схем.

Требования:

Таблица в 2NF.
Нет транзитивных зависимостей: неключевые атрибуты не должны зависеть от других неключевых атрибутов.
Формулировка: каждый неключевой атрибут зависит только от ключа, от всего ключа и ни от чего, кроме ключа.

Пример нарушения (3NF):

CREATE TABLE customer (
    id            BIGINT PRIMARY KEY,
    name          TEXT,
    city_id       BIGINT,
    city_name     TEXT,   -- зависит от city_id, а не напрямую от id
    region_name   TEXT    -- зависит от города и справочников
);

city_name и region_name зависят от city_id, а не напрямую от customer.id → транзитивная зависимость.

Исправление:

CREATE TABLE city (
    id          BIGINT PRIMARY KEY,
    name        TEXT,
    region_name TEXT
);

CREATE TABLE customer (
    id        BIGINT PRIMARY KEY,
    name      TEXT,
    city_id   BIGINT REFERENCES city(id)
);

Преимущества 3NF:

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

BCNF, 4NF, 5NF — кратко

Для практики собеседования достаточно понимать:

BCNF:
- усиление 3NF: каждый детерминант — ключ.
- полезно при сложных зависимостях, но реже явно используется в бизнес-проектах.
4NF, 5NF:
- связаны с многозначными и более сложными зависимостями;
- в прикладных схемах встречаются редко, применяются точечно.

Практический баланс нормализации и денормализации

Важно уметь аргументированно выбирать:

Для OLTP и ядра DWH:
- целимся в 3NF / близко к BCNF;
- это уменьшает дублирование, повышает целостность и упрощает развитие.
Для витрин и аналитических слоев:
- осознанная денормализация (звезда, снежинка);
- цель — простые и быстрые запросы, а не идеальная нормальная форма.

Пример осознанной денормализации (витрина заказов):

-- Нормализованный вариант (ядро)
-- customer, city, region, product, category, order, order_item ...

-- Денормализованная витрина (звезда)
CREATE TABLE mart_orders (
    order_id        BIGINT,
    order_date      DATE,
    customer_id     BIGINT,
    customer_name   TEXT,
    city_name       TEXT,
    region_name     TEXT,
    product_id      BIGINT,
    product_name    TEXT,
    category_name   TEXT,
    quantity        INT,
    amount          NUMERIC
);

Здесь есть дублирование, но оно управляемо и оправдано целями аналитики.

Кратко:

Нормальные формы — это инструмент борьбы с аномалиями и избыточностью.
Для собеседования важно уверенно знать:
- 1NF: атомарность.
- 2NF: полная зависимость от составного ключа.
- 3NF: отсутствие транзитивных зависимостей, все неключевые поля зависят только от ключа.
Также важно уметь объяснить, почему в аналитике эти правила иногда осознанно нарушают ради производительности и удобства, опираясь на стабильное, корректно нормализованное ядро.

Вопрос 14. Дать определение первичного ключа.

Таймкод: 00:12:53

Ответ собеседника: правильный. Определяет первичный ключ как поле, по которому однозначно идентифицируется строка, и отмечает, что при объявлении первичного ключа накладываются ограничения UNIQUE и NOT NULL.

Правильный ответ:

Первичный ключ (PRIMARY KEY) — это минимальный набор столбцов таблицы, который однозначно идентифицирует каждую запись и служит опорой для логической и физической целостности данных.

Ключевые свойства первичного ключа:

Уникальность:
- никакие две строки не могут иметь одинаковое значение первичного ключа;
- на уровне СУБД обеспечивается уникальным индексом (явным или неявным).
NOT NULL:
- значения первичного ключа не могут быть NULL;
- идентификатор должен существовать для каждой строки.
Минимальность:
- в составе ключа нет лишних полей: каждое поле необходимо для обеспечения уникальности.
Неподвижность (желательно):
- значение PK по возможности не меняется во времени,
- изменение PK ведет к каскаду изменений во внешних ключах и может быть дорогостоящим.
Семантика:
- первичный ключ — это не просто техническое ограничение, а часть модели данных:
  - определяет, что мы считаем «уникальной сущностью» (customer_id, order_id, etc.).

Типы первичных ключей:

Натуральный ключ:
- основан на бизнес-данных (ИНН, номер паспорта, комбинация полей).
- проблема: бизнес-идентификаторы могут меняться, иметь коллизии или быть «грязными».
Суррогатный ключ:
- искусственный идентификатор (INT/BIGINT IDENTITY/SERIAL, UUID).
- не несет бизнес-смысла, стабилен, хорош как PK и для ссылок (FK).
- бизнес-идентификаторы хранятся отдельно и могут меняться независимо.

Примеры объявлений:

Простой суррогатный PK:

CREATE TABLE customers (
    id          BIGSERIAL PRIMARY KEY,
    name        TEXT NOT NULL,
    email       TEXT NOT NULL
);

Составной PK (например, для позиции заказа):

CREATE TABLE order_items (
    order_id    BIGINT NOT NULL,
    line_num    INT    NOT NULL,
    product_id  BIGINT NOT NULL,
    quantity    INT    NOT NULL,
    PRIMARY KEY (order_id, line_num)
);

Роль первичного ключа в архитектуре:

Основа для внешних ключей:
- другие таблицы ссылаются на PK, формируя целостные связи (FK).
Основа для индексации и производительности:
- выборки по PK обычно самые дешевые и предсказуемые.
Критичен при проектировании:
- неправильный выбор PK ведет к:
  - аномалиям,
  - проблемам интеграции,
  - дорогостоящим миграциям.

Практические рекомендации:

В большинстве прикладных систем:
- использовать суррогатный PK (BIGINT / UUID) как основной идентификатор строки;
- бизнес-ключи дополнительно контролировать через UNIQUE-ограничения.
Для связующих таблиц (many-to-many):
- разумно использовать составной PK из FK (как в примере с order_items).
В DWH:
- использовать surrogate keys (SK) в измерениях;
- факты ссылаются на SK, а не напрямую на натуральные ключи источников.

Кратко:

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

Вопрос 15. Дать определение суррогатного ключа и пояснить на примере, для чего он используется.

Таймкод: 00:13:38

Ответ собеседника: неполный. Верно называет суррогатный ключ искусственно созданным идентификатором (например, числовой ID вместо номера договора), отмечает удобство для индексации и JOIN’ов. Однако путает суррогатный ключ с составным бизнес-ключом в примере пересечения номеров договоров между разными продуктами, не до конца разделяя роли бизнес-ключа и суррогатного ключа.

Правильный ответ:

Суррогатный ключ — это искусственный, технический идентификатор строки, не несущий бизнес-смысла и используемый для:

однозначной идентификации записи;
упрощения и унификации ссылок (foreign keys);
устранения проблем с нестабильными или сложными бизнес-ключами;
повышения производительности JOIN’ов и индексов.

Ключевые свойства суррогатного ключа:

Не основан напрямую на бизнес-атрибутах:
- не номер договора «как в бумаге»,
- не ИНН, не e-mail, не комбинация из 5 полей.
Стабильный:
- не должен меняться при изменении бизнес-атрибутов.
Уникальный:
- однозначно идентифицирует строку.
Простой по типу:
- чаще всего BIGINT (IDENTITY/SERIAL/SEQUENCE) или UUID.
Используется как первичный ключ таблицы:
- особенно в DWH и в большинстве OLTP-моделей.

Важное разграничение:

Бизнес-ключ (натуральный ключ):
- идентификатор из предметной области;
- пример: номер договора, логин, номер счета, комбинация (серия, номер паспорта).
- может:
  - меняться со временем;
  - дублироваться между системами;
  - иметь грязные/некорректные значения;
  - иметь сложный состав (несколько полей).
Суррогатный ключ:
- технический идентификатор внутри нашей модели;
- не меняется при изменении бизнес-атрибутов;
- служит опорой для связей и ссылок.

Оба часто сосуществуют:

суррогатный ключ — PK,
бизнес-ключ — под UNIQUE или без, но под контролем валидаций.

Простой пример (OLTP / DWH):

Есть договоры, и бизнес называет договор по номеру, который может:

пересекаться между системами;
иметь разные форматы по продуктам;
меняться по правилам миграции.

Ошибочный путь:

делать PK как составной бизнес-ключ:
- (contract_number, product_code, source_system, …).
Такие ключи:
- длинные,
- тяжёлые для индексации и JOIN’ов,
- больно меняются при изменении бизнес-правил.

Правильный подход:

Вводим суррогатный ключ (PK):

CREATE TABLE contract (
    contract_sk      BIGSERIAL PRIMARY KEY,  -- суррогатный ключ
    contract_number  TEXT        NOT NULL,   -- бизнес-номер договора
    product_code     TEXT        NOT NULL,
    source_system    TEXT        NOT NULL,
    start_date       DATE        NOT NULL,
    end_date         DATE,
    status           TEXT        NOT NULL
);

Обеспечиваем уникальность бизнес-ключа в нужном контексте:

ALTER TABLE contract
ADD CONSTRAINT uq_contract_business_key
UNIQUE (contract_number, product_code, source_system, start_date);

Все внешние связи (оплаты, события, пролонгации) ссылаются на contract_sk (суррогат):

CREATE TABLE payment (
    payment_id       BIGSERIAL PRIMARY KEY,
    contract_sk      BIGINT NOT NULL REFERENCES contract(contract_sk),
    amount           NUMERIC(18,2) NOT NULL,
    payment_date     DATE NOT NULL
);

Что это дает:

JOIN’ы по одному BIGINT вместо нескольких полей:
- проще запросы,
- лучше планы выполнения,
- меньше места в индексах.
Бизнес-ключ можно менять:
- например, сменился формат contract_number;
- мы обновили поле, но contract_sk сохранился, все связи остались корректны.
Можно разрешать сложные кейсы:
- один и тот же contract_number в разных source_system/product_code:
  - различаем на уровне бизнес-ключа,
  - но внутри системы всегда работаем через стабильный contract_sk.

Пример для DWH/измерения:

В измерении клиента:

CREATE TABLE dim_customer (
    customer_sk      BIGSERIAL PRIMARY KEY,   -- суррогатный ключ
    source_customer_id TEXT NOT NULL,         -- ID из источника
    source_system    TEXT NOT NULL,
    name             TEXT,
    birth_date       DATE,
    is_active        BOOLEAN,
    CONSTRAINT uq_customer_business_key
        UNIQUE (source_system, source_customer_id)
);

Факт (покупки) ссылается на customer_sk:

CREATE TABLE fact_sales (
    sale_id          BIGSERIAL PRIMARY KEY,
    customer_sk      BIGINT REFERENCES dim_customer(customer_sk),
    product_sk       BIGINT,
    sale_date        DATE,
    amount           NUMERIC(18,2)
);

Если завтра:

клиент из одной системы переехал в другую,
изменилась схема идентификаторов; мы можем:
обновить/сопоставить бизнес-ключи,
не ломая ссылки в фактах, потому что они через суррогатный ключ.

Ключевые практические выводы:

Суррогатный ключ:
- не заменяет бизнес-ключ,
- дополняет его и упрощает архитектуру.
Для сложных, составных или нестабильных бизнес-ключей:
- суррогатный PK + уникальный бизнес-ключ в качестве ограничения — лучший подход.
Для связующих/факт-таблиц:
- ссылки на суррогатные ключи измерений:
  - упрощают моделирование,
  - повышают производительность,
  - позволяют аккуратно управлять историей и качеством данных.

Кратко:

Суррогатный ключ — это технический стабильный идентификатор строки, используемый как опорная точка для ссылок и интеграции. Бизнес-ключ отражает реальный мир и может быть сложным, дублирующимся или меняться. Грамотное разделение этих ролей — критично для корректного моделирования хранилищ и надежных связей между таблицами.

Вопрос 16. Объяснить, зачем нужны колоночные базы данных и в чём их преимущества.

Таймкод: 00:15:42

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

Правильный ответ:

Колоночные базы данных (column-oriented DBMS, column stores) спроектированы специально под аналитические нагрузки: большие объёмы данных, сложные агрегирующие запросы, сканирование таблиц, отчёты и BI. Они принципиально отличаются от строковых (row-oriented) СУБД способом физического хранения данных и, как следствие, профилем производительности.

Ключевая идея:

В строковой БД данные хранятся по строкам: значения всех колонок одной строки лежат рядом.
В колоночной БД данные хранятся по колонкам: сначала все значения одного столбца, затем другого и т.д.

Это влияет на:

какие нагрузки выполняются особенно быстро;
как работают индексы, сжатие и IO;
как проектировать схему под аналитику.

Как реально устроено хранение:

Упрощённо:

Row store:
- [user_id, name, age, city] для строки 1, затем для строки 2 и т.д.
Column store:
- отдельно сегмент user_id: [1, 2, 3, 4, ...]
- отдельно name: ["Alice", "Bob", ...]
- отдельно age: [25, 40, 31, ...]
- отдельно city: ["Moscow", "Moscow", "Berlin", ...]

Колоночная БД работает с наборами значений по колонкам (колонными сегментами), а не «одна колонка как одна строка». Это позволяет эффективно применять сжатие и выполнять векторные (SIMD) операции.

Основные преимущества колоночных БД для аналитики:

Чтение только нужных колонок

Типичный аналитический запрос:

«Посчитать SUM(amount) и AVG(amount) по фильтру region = 'X' за период Y»
Нужны:
- столбцы: region, date, amount;
- не нужны: email, phone, description, json_payload и т.п.

В row-store:

читаем все строки и все колонки, даже ненужные;
дисковый IO и кеш тратятся впустую.

В column-store:

читаем только нужные колонки (region, date, amount);
объем IO сильно меньше → запросы выполняются на порядки быстрее.

Эффективное сжатие

Данные в одной колонке однотипны и часто коррелированы:

статусы, категории, флаги,
повторяющиеся значения (город, код продукта),
числовые ряды.

Это позволяет применять:

RLE (Run-Length Encoding),
словарное сжатие,
битовые карты,
delta-encoding, frame-of-reference и т.п.

В итоге:

хранение дешевле (меньше места),
чтение быстрее (меньше данных с диска, разжатие в памяти быстрее, чем IO).

Пример:

колонка city: ["Moscow", "Moscow", "Moscow", "SPB", "SPB"] отлично сжимается,
фильтр WHERE city = 'Moscow' может работать через битовые маски без полной распаковки.

Высокая скорость агрегаций и сканирований

Аналитические запросы часто:

сканируют миллионы/миллиарды строк;
считают SUM, AVG, COUNT DISTINCT, PERCENTILE, GROUP BY по нескольким полям.

Column-store оптимален для:

векторных вычислений (обработка пачек значений колонок);
использования SIMD-инструкций;
параллельной обработки сегментов.

Результат:

ClickHouse, Vertica, BigQuery, Redshift, Druid и подобные позволяют выполнять сложные агрегаты по гигабайтам/терабайтам данных за секунды/десятки секунд при правильной схеме.

Пропуск нерелевантных данных (data skipping)

Многие колоночные движки хранят мини- и макс-значения по блокам/сегментам колонки:

если запрос фильтрует date BETWEEN '2025-01-01' AND '2025-01-31',
а блок колонки date имеет диапазон ['2024-01-01', '2024-01-31'],
этот блок полностью пропускается (data skipping), не читая и не проверяя каждую строку.

Это критично для:

партиционирования по датам,
больших логов,
событийных данных.

Хорошая масштабируемость для аналитических DWH/витрин

Колоночные СУБД отлично подходят для:

витрин (data marts),
событийных логов,
метрик и таймсерий,
аналитики по user-activity,
агрегаций в real-time и near real-time.

Например:

ClickHouse: логирование, метрики, аналитика поведения, отчёты по большим объёмам.
Используется как целевой движок для BI-отчетов, где нужны быстрые группировки и фильтрации.

Простой пример сравнения (SQL):

Есть витрина логов:

CREATE TABLE events (
    event_date      Date,
    user_id         UInt64,
    event_type      LowCardinality(String),
    payload         String
)
ENGINE = MergeTree()
PARTITION BY toYYYYMM(event_date)
ORDER BY (event_date, user_id);

Запрос:

SELECT
    event_type,
    count(*) AS cnt
FROM events
WHERE event_date BETWEEN '2025-01-01' AND '2025-01-31'
GROUP BY event_type
ORDER BY cnt DESC
LIMIT 10;

В колоночной БД:

читаются только event_date и event_type (payload можно не трогать);
используется партиционирование и индексы по диапазонам;
агрегация идёт по сжатым массивам значений.

В строковой БД:

для тех же данных пришлось бы читать много лишнего, больше I/O, меньше кеш-хитов.

Ограничения и когда колоночная БД — не лучший выбор:

Важно понимать и обратную сторону:

Не оптимальны для:
- частых точечных UPDATE/DELETE по отдельным строкам;
- большого количества мелких транзакций (OLTP).
Архитектурный вывод:
- колоночные БД идеальны для read-heavy, append-only или batch-обновлений (логов, фактов);
- для транзакционных систем лучше классические row-store (PostgreSQL, MySQL, Oracle и т.п.);
- нередко используется комбинация:
  - OLTP → DWH → колоночная витрина (ClickHouse) для отчетов.

Интеграция с сервисами на Go (пример):

Go-сервис, который читает агрегаты из ClickHouse для API отчетности:

import (
    "context"
    "database/sql"
    _ "github.com/ClickHouse/clickhouse-go/v2"
)

type StatsRepo struct {
    db *sql.DB
}

func (r *StatsRepo) GetDailyEventsCount(ctx context.Context, from, to string) (map[string]int64, error) {
    rows, err := r.db.QueryContext(ctx, `
        SELECT event_type, count()
        FROM events
        WHERE event_date BETWEEN ? AND ?
        GROUP BY event_type
    `, from, to)
    if err != nil {
        return nil, err
    }
    defer rows.Close()

    res := make(map[string]int64)
    for rows.Next() {
        var eventType string
        var cnt int64
        if err := rows.Scan(&eventType, &cnt); err != nil {
            return nil, err
        }
        res[eventType] = cnt
    }
    return res, rows.Err()
}

Такой паттерн типичен: тяжелая аналитика на колоночной БД, легкий API-слой на Go.

Кратко:

Колоночные СУБД нужны для высокопроизводительной аналитики по большим объёмам данных.
Главные преимущества:
- чтение только нужных колонок,
- сильное сжатие,
- быстрые агрегаты и сканирования,
- эффективное data skipping.
Они дополняют, а не заменяют строковые СУБД: одни под транзакции, другие под аналитику.
Важно корректно понимать модель хранения: отдельные последовательности значений по колонкам, а не «колонка как отдельная строка».

Вопрос 17. Назвать недостатки колоночных баз данных.

Таймкод: 00:16:27

Ответ собеседника: неполный. Вместо перечисления минусов продолжает говорить о преимуществах выборок по подмножеству полей и сравнении со строковыми СУБД, но не формулирует ключевые недостатки: неэффективность для частых точечных insert/update/delete, транзакционных OLTP-нагрузок, сложностей с изменением данных в реальном времени и др.

Правильный ответ:

Колоночные СУБД отлично подходят для аналитики, но имеют принципиальные ограничения, из-за которых их нельзя использовать как универсальную замену строковым (OLTP) базам. Важно ясно понимать эти минусы, чтобы правильно выбирать инструменты под задачу.

Ключевые недостатки.

Неэффективность для частых точечных изменений (OLTP-сценарии)

Архитектура колоночных БД оптимизирована под:
- bulk insert (пакетные вставки),
- append-only / «дописать в конец»,
- сканирование и агрегации.
Операции:
- частые одиночные INSERT по одной строке,
- UPDATE отдельной строки,
- DELETE отдельной строки обычно:
- дороже по накладным расходам;
- приводят к фрагментации, необходимости слияний (merge/compaction) сегментов;
- часто реализованы «логически» (маркировка как удалённого, переразбор сегментов позже).

Вывод:

Для высоконагруженных транзакционных систем (финансовые транзакции, заказы, биллинг в онлайне) колоночная БД — плохой выбор в роли основной OLTP БД.
Обычно используется связка:
- OLTP в row-store (PostgreSQL, MySQL, Oracle),
- выгрузка/репликация в column-store (ClickHouse и др.) для аналитики.

Замедленные и сложные UPDATE/DELETE

В классической строковой БД:

UPDATE/DELETE по PK — точечная операция по индексу.

В колоночной:

Данные разбиты на сегменты/части по колонкам;
Для изменения часто:
- помечается старая версия как неактуальная;
- пишется новая версия в новые сегменты;
- периодически фоново выполняется merge/compaction (дорого по IO и CPU).

Следствия:

Массовые частые UPDATE/DELETE:
- приводят к росту объёма и нагрузке на merge;
- усложняют прогноз производительности;
- требуют аккуратного проектирования (сегменты, TTL, «мягкие удаления», партиционирование).

Сложности с транзакционностью и строгой консистентностью

Многие колоночные движки:

не ориентированы на сложные ACID-транзакции в стиле OLTP:
- ограниченная поддержка транзакций;
- eventual consistency между репликами;
- особенности видимости данных (read-your-writes не всегда гарантируется в привычном виде).
В аналитике это допустимо,
но для «денег в онлайне», критичных операций или сложных инвариантов это может быть недостатком.

Нужно внимательно смотреть на:

модель консистентности конкретной СУБД,
поддержку транзакций, изоляции, блокировок.

Более сложная модель для write-heavy реального времени

Хотя некоторые колоночные СУБД (например, ClickHouse) поддерживают near real-time сценарии, есть нюансы:

оптимальный режим — батчи (буферизация и периодическая запись);
если писать по одной записи в реальном времени:
- это бьёт по производительности и приводит к множеству маленьких партиций/частых merge;
нужно:
- проектировать ingestion-слой,
- агрегацию по времени,
- партиционирование.

То есть:

«нативный» OLTP-паттерн (частые одиночные записи + сильные транзакции) для column-store — неестественный.

Ограниченная гибкость для OLTP-реляционных операций

Некоторые особенности:

Не все колоночные СУБД одинаково хорошо поддерживают:
- сложные JOIN’ы по многим таблицам (особенно при real-time mix нагрузке),
- частые small-select запросы по нескольким записям;
В аналитике JOIN’ы по большим сетам работают нормально, но:
- как универсальный «application database» (для бизнес-логики, сложных транзакций, stateful workflows) column-store обычно хуже, чем классический row-store.

Потенциально более высокая сложность эксплуатации

Колоночные движки:

используют специфические механизмы:
- партиционирование,
- индексы/skip indexes,
- merge tree/segment tree,
- настройки хранения/сжатия.
Неочевидные настройки могут:
- привести к взрывному росту числа партиций;
- ухудшить merge-процессы;
- вызывать непредсказуемые провалы по производительности.

Требуется:

понимание внутренних механизмов;
аккуратное проектирование структуры таблиц и ключей.

Не всегда удобны как единственный источник правды

Из-за особенностей:

транзакционности,
актуальности,
сложности поддержки сложных бизнес-инвариантов,
особенностей UPDATE/DELETE

использовать column-store как «единственный источник правды» для критичных бизнес-данных часто рискованно.

Типичный зрелый подход:

Операционные данные:
- row-store (PostgreSQL/Oracle/MySQL и т.п.).
Аналитика/витрины/агрегации:
- column-store (ClickHouse/Vertica/Snowflake/BigQuery).
Синхронизация через:
- CDC, Kafka, регулярные ETL/ELT.

Пример архитектурного паттерна (SQL + интеграция):

Основная транзакционная таблица в PostgreSQL:

CREATE TABLE orders (
    id           BIGSERIAL PRIMARY KEY,
    customer_id  BIGINT NOT NULL,
    amount       NUMERIC(18,2) NOT NULL,
    status       TEXT NOT NULL,
    created_at   TIMESTAMPTZ NOT NULL DEFAULT now()
);

Периодическая выгрузка в ClickHouse для аналитики:

CREATE TABLE orders_ch (
    id           UInt64,
    customer_id  UInt64,
    amount       Float64,
    status       String,
    created_at   DateTime
)
ENGINE = MergeTree()
PARTITION BY toYYYYMM(created_at)
ORDER BY (created_at, id);

Go/ETL-сервис:

читает инкрементальные изменения из PostgreSQL (CDC/по времени),
пишет пакетами в ClickHouse.

Кратко:

Основные недостатки колоночных БД:

плохо подходят для:
- частых точечных insert/update/delete;
- высоконагруженных OLTP-сценариев;
- сложных ACID-транзакций и жёстких инвариантов в онлайне;
требуют:
- батчевой загрузки,
- грамотного партиционирования и настройки;
сложнее использовать как универсальное хранилище для всего.

Именно поэтому в зрелых системах колоночные БД — это специализированный инструмент для аналитики и витрин, а не замена классических транзакционных СУБД.

Вопрос 18. Что такое исторические таблицы и как обычно реализуется историчность (SCD)?

Таймкод: 00:17:20

Ответ собеседника: правильный. Описывает исторические таблицы как механизм отслеживания изменений значений во времени. Корректно раскрывает SCD0 (без изменений), SCD1 (перезапись), SCD2 (версии с периодами действия, актуальная запись с открытым интервалом) и упоминает важный момент для SCD2: сначала закрыть старую запись, затем добавить новую.

Правильный ответ:

Исторические таблицы нужны для того, чтобы отвечать не только на вопрос «как выглядит сущность сейчас», но и «как она выглядела в прошлом, и какие изменения происходили во времени». Это критично для финансов, отчетности, комплаенса, аналитики поведения, аудита и восстановления состояния системы на конкретный момент.

Slowly Changing Dimensions (SCD) — стандартные шаблоны ведения истории измерений в DWH. Рассмотрим ключевые типы и реализацию.

Базовые понятия:

Историческая таблица: структура, в которой для одной бизнес-сущности хранится несколько записей, отражающих её состояние в разные периоды.
Используется чаще всего для измерений (dimension tables), но может применяться и к референсам/справочникам.

SCD0 — без изменений (Fixed Dimension)

Логика:
- Значения атрибутов считаются неизменяемыми после загрузки.
- Изменения источника игнорируются.
Применение:
- стабильные справочники: типы операций, константы, коды стран (при условии, что мы верим в их неизменность).
Реализация:
- обычная таблица без истории.

SCD1 — перезапись (Overwrite)

Логика:
- При изменении атрибутов просто обновляем строку.
- История изменений не сохраняется.
Пример:
- исправление опечаток в имени клиента, не критично для исторической отчетности.
Реализация (SQL):

UPDATE dim_customer
SET name = :new_name
WHERE business_key = :bk;

Плюсы:

просто;
экономно по объёму.

Минусы:

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

SCD2 — хранение историй (Historical / Versioned)

Самый важный и часто используемый тип.

Логика:
- Каждое изменение значимых атрибутов порождает новую версию записи.
- У каждой версии есть период действия: [valid_from, valid_to).
- Актуальная запись имеет открытый конец (valid_to = '9999-12-31' или NULL).
Используется, когда:
- нужны корректные исторические отчеты;
- важно знать, какой статус/категория/тариф действовал в момент транзакции.

Структура таблицы (пример):

CREATE TABLE dim_customer_hist (
    customer_sk      BIGSERIAL PRIMARY KEY,  -- суррогатный ключ версии
    customer_bk      TEXT NOT NULL,          -- бизнес-ключ (например, customer_id из источника)
    name             TEXT,
    segment          TEXT,
    valid_from       DATE NOT NULL,
    valid_to         DATE NOT NULL,
    is_current       BOOLEAN NOT NULL,
    source_system    TEXT NOT NULL
);

CREATE UNIQUE INDEX uq_customer_bk_valid_period
ON dim_customer_hist (customer_bk, valid_from);

Принцип загрузки SCD2:

При первом появлении сущности:

добавляем запись с:
- valid_from = дата загрузки или бизнес-дата;
- valid_to = '9999-12-31';
- is_current = true.

При обнаружении изменения значимых атрибутов:

находим текущую версию (is_current = true);
закрываем её:
- valid_to = дата начала новой версии (или дата-1);
- is_current = false;
добавляем новую версию:
- с обновлёнными атрибутами,
- valid_from = дата изменения,
- valid_to = '9999-12-31',
- is_current = true.

Важно:

сначала закрыть старую запись, затем вставить новую;
следить за непрерывностью и непересечением периодов.

Упрощённый пример обработки изменения (SQL):

-- Закрываем старую версию
UPDATE dim_customer_hist
SET valid_to = :change_date,
    is_current = FALSE
WHERE customer_bk = :bk
  AND is_current = TRUE;

-- Создаём новую версию
INSERT INTO dim_customer_hist (
    customer_bk, name, segment,
    valid_from, valid_to, is_current, source_system
)
VALUES (
    :bk, :new_name, :new_segment,
    :change_date, DATE '9999-12-31', TRUE, :source
);

Использование в фактах:

Факт-таблицы обычно ссылаются:

либо на суррогатный ключ версии измерения (customer_sk),
либо вычисляют версию по дате факта (join по bk и диапазону дат).

Пример запроса, выбирающего актуальную на момент покупки версию клиента:

SELECT
    f.order_id,
    f.order_date,
    c.name,
    c.segment
FROM fact_orders f
JOIN dim_customer_hist c
  ON c.customer_bk = f.customer_bk
 AND f.order_date >= c.valid_from
 AND f.order_date <  c.valid_to;

Так обеспечивается корректная историческая аналитика.

Другие варианты (для полноты картины):

SCD3:
- хранение ограниченной истории (например, текущего и одного предыдущего значения).
- редкое использование, обычно для специфических задач.
SCD4:
- вынос исторических записей в архивную таблицу.
SCD6:
- комбинация SCD1 + SCD2 (часть полей перезаписывается, часть версионируется).

Практические моменты:

Для измерений с SCD2 почти всегда:
- используется суррогатный ключ версии (для ссылок из факт-таблиц),
- бизнес-ключ + период используются для поиска версии.
В ETL/ELT:
- важна идемпотентность:
  - повторная загрузка за день не должна давать дубликаты версий;
- обязательны проверки:
  - не пересекаются ли периоды,
  - правильно ли закрыты старые версии.

Go-пример получения актуального состояния из исторической таблицы:

func (r *CustomerRepo) GetCurrentCustomer(ctx context.Context, bk string, at time.Time) (*Customer, error) {
    const q = `
        SELECT customer_sk, customer_bk, name, segment
        FROM dim_customer_hist
        WHERE customer_bk = $1
          AND $2 >= valid_from
          AND $2 <  valid_to
        LIMIT 1;
    `
    row := r.db.QueryRowContext(ctx, q, bk, at)
    var c Customer
    if err := row.Scan(&c.SK, &c.BK, &c.Name, &c.Segment); err != nil {
        return nil, err
    }
    return &c, nil
}

Кратко:

Исторические таблицы позволяют корректно восстанавливать состояние сущностей во времени.
Критически важно уверенно владеть SCD0, SCD1, SCD2:
- SCD1 — перезапись, без истории;
- SCD2 — полная история версий с периодами действия;
Для SCD2 архитектурно важно:
- аккуратно вести периоды,
- использовать суррогатные ключи,
- проектировать запросы и ETL так, чтобы история была непротиворечивой и детерминированной.

Вопрос 19. Как определить инкрементальные данные для загрузки в хранилище?

Таймкод: 00:18:55

Ответ собеседника: неполный. Упоминает использование даты или идентификаторов для определения инкремента, но не дает решения для случая изменений внутри уже загруженного периода и не рассматривает механизмы сравнения, хэширования, CDC и защиту от пропусков/дубликатов.

Правильный ответ:

Инкрементальная загрузка — ключевой элемент производительного и надежного DWH/витрин: мы не перечитываем весь объем данных, а выбираем только новые или изменившиеся записи. Задача сложнее, чем просто «фильтровать по дате», особенно когда:

источник может присылать исправленные данные задним числом;
возможны задержки и повторы;
бизнес требует консистентной и воспроизводимой логики.

Рассмотрим основные подходы и практики.

Базовые варианты определения инкремента

По дате/времени изменения (updated_at / last_modified)

Классический и предпочтительный вариант, если источник поддерживает это корректно.

Требования:

В источнике есть поле:
- updated_at / last_update_ts / modified_at;
- которое:
  - заполняется при вставке,
  - обновляется при любом изменении записи.
Значения монотонно растут и корректно отражают время изменения.

Схема:

В DWH хранится последнее успешно обработанное значение updated_at.
На следующем цикле выбираем записи:
- updated_at > last_max_updated_at
- (часто с запасом по времени для надёжности).

Пример SQL на стороне источника:

SELECT *
FROM orders
WHERE updated_at > :last_max_updated_at
ORDER BY updated_at, id;

Важно:

Добавлять second key (например, id), чтобы избежать потерь на границах времени.
Часто используют:
- updated_at > last_max_updated_at
- OR (updated_at = last_max_updated_at AND id > last_max_id)

По монотонному идентификатору (sequence, auto-increment)

Подходит, когда:

ID генерируется строго монотонно (не UUID, а sequence/identity);
новые записи только добавляются, не обновляются задним числом.

Схема:

Сохраняем last_max_id, на следующем шаге выбираем:
- id > last_max_id.

Пример:

SELECT *
FROM events
WHERE id > :last_max_id
ORDER BY id;

Ограничение:

Не подходит для отслеживания изменений существующих записей (UPDATE);
Хорош для append-only логов, событий.

Проблемы и как их решать

Изменения задним числом (late arriving data, исправления истории)

Кейс:

Данные за вчера уже загружены,
но сегодня в источнике изменили запись «вчерашнего» заказа (исправление суммы, статуса).

Если фильтровать только по created_at, это изменение потеряется.

Решение:

Использовать updated_at (время фактического изменения), а не только created_at.
Для полностью ненадежных источников — сравнение состояний, хэширование или CDC.

Ненадежный updated_at

Если:

поле updated_at не всегда обновляется,
или обновляется только для части полей,
или источников несколько и поведение разное —

нужны дополнительные механизмы.

Подходы для надежного инкремента

Change Data Capture (CDC)

Лучший вариант при возможности интеграции.

Идея:

Не вычислять инкремент по запросам, а читать поток изменений из:
- логов транзакций (binlog, WAL),
- специальных CDC-механизмов СУБД (Oracle GoldenGate, SQL Server CDC),
- Debezium, Kafka Connect и т.п.
События вида:
- INSERT, UPDATE, DELETE (до/после),
- с временем, ключами, значениями.

Плюсы:

Ничего не теряется,
есть точная история изменений,
легко восстанавливать состояние.

Минусы:

Требует поддержки на стороне источника и более сложной инфраструктуры.

Сравнение снимков (Snapshot diff)

Если нет корректного updated_at и CDC:

периодически делаем снапшот (выборку) данных источника;
сравниваем с предыдущим снапшотом:
- новые записи,
- удаленные,
- измененные (по ключу).

Оптимизация:

сравнение не по всем полям, а по хэшу значимых атрибутов.

Пример структуры:

-- Таблица снапшота источника
CREATE TABLE src_orders_snapshot (
    order_id    BIGINT,
    hash_value  TEXT,
    snapshot_dt DATE
);

Алгоритм:

На каждом цикле:
- читаем из источника (order_id, бизнес-поля),
- считаем hash_value (например, md5 от конкатенации значимых полей),
- сравниваем с предыдущим снапшотом:
  - если новый order_id → INSERT;
  - если hash_value изменился → UPDATE;
  - если order_id исчез → DELETE/обработка как логическое удаление.

Это дороже по ресурсам, но надёжно при отсутствии нормальных метаданных.

Хэширование в рамках ETL

Даже если updated_at есть, для снижения нагрузки:

можно брать кандидатов по updated_at,
а затем проверять, действительно ли значения изменились:
- сравнивая хэш атрибутов с тем, что уже в DWH.

Пример (псевдо-SQL):

SELECT
    o.order_id,
    md5(o.customer_id || '|' || o.amount || '|' || o.status) AS hash_value
FROM source_orders o
WHERE o.updated_at > :last_max_updated_at;

В DWH:

если hash_value отличается от сохраненного — обновляем (SCD1/SCD2),
если совпадает — игнорируем.

Защита от пропусков и дубликатов

Хороший инкремент всегда включает:

идемпотентность:
- повторный запуск одного и того же окна не должен дублировать данные;
работу с «окном перекрытия»:
- при выборке по updated_at логично брать небольшой оверлап (например, последние N минут) и пересчитать этот сегмент, используя уникальные ключи/hard constraints.

Пример:

last_max_updated_at = '2025-01-10 12:00:00'
следующий запуск:
- выбираем updated_at >= '2025-01-10 11:55:00'
- но при загрузке:
  - ориентируемся на бизнес-ключ + updated_at,
  - выполняем MERGE (upsert), чтобы не было дублей.

Реализация upsert (MERGE) в DWH

Пример инкрементальной загрузки в ядро (SCD1):

MERGE INTO dwh_orders d
USING staging_orders s
ON (d.order_id = s.order_id)
WHEN MATCHED AND (
       d.amount != s.amount
    OR d.status != s.status
) THEN
    UPDATE SET
        d.amount = s.amount,
        d.status = s.status,
        d.updated_at = s.updated_at
WHEN NOT MATCHED THEN
    INSERT (order_id, customer_id, amount, status, created_at, updated_at)
    VALUES (s.order_id, s.customer_id, s.amount, s.status, s.created_at, s.updated_at);

Ключевые моменты:

MATCHED + условия различий защищают от лишних апдейтов;
NOT MATCHED — добавляет новые записи.

Интеграция в пайплайн (пример на Go)

Фрагмент сервиса, который отдаёт инкрементальные данные по updated_at:

func (r *OrdersRepo) GetIncremental(ctx context.Context, since time.Time, limit int) ([]Order, error) {
    const q = `
        SELECT order_id, customer_id, amount, status, created_at, updated_at
        FROM orders
        WHERE updated_at > $1
        ORDER BY updated_at, order_id
        LIMIT $2;
    `
    rows, err := r.db.QueryContext(ctx, q, since, limit)
    if err != nil {
        return nil, err
    }
    defer rows.Close()

    var res []Order
    for rows.Next() {
        var o Order
        if err := rows.Scan(
            &o.ID, &o.CustomerID, &o.Amount, &o.Status, &o.CreatedAt, &o.UpdatedAt,
        ); err != nil {
            return nil, err
        }
        res = append(res, o)
    }
    return res, rows.Err()
}

Такой endpoint удобно использовать в ETL/оркестраторе для инкрементальной загрузки.

Кратко:

Надежное определение инкремента включает:

при наличии — использование updated_at с аккуратной логикой границ и upsert;
для append-only — использование монотонных ID;
при сложных кейсах — CDC, хэши, сравнение снапшотов;
идемпотентность, обработку late arriving data и защиту от пропусков/дубликатов.

Ожидаемый ответ на интервью:

назвать не только «по дате», но и:
- updated_at vs created_at,
- монотонные ключи,
- CDC,
- хэширование/дифф,
- необходимость MERGE/upsert и перекрывающихся окон;
показать понимание проблем: изменения задним числом, ненадёжные поля, повторные прогоны.

Вопрос 20. Объяснить подход к отслеживанию изменений бизнес-атрибутов внутри периода действия записи.

Таймкод: 00:19:27

Ответ собеседника: неправильный. На пример с изменениями внутри уже открытого периода не предлагает рабочего метода; не упоминает контрольные хэши, сравнение версий, CDC или полноценный механизм детектирования изменений, идеи фактически подсказываются интервьюером.

Правильный ответ:

Задача: надежно выявлять изменения бизнес-атрибутов (имя, статус, тариф, лимит, сегмент и т.п.) для уже существующих сущностей, в том числе внутри текущего периода действия записи (актуальной версии), чтобы:

корректно вести SCD2-историю;
не пропускать изменения, пришедшие «задним числом»;
избегать избыточных обновлений и дубликатов версий;
обеспечивать идемпотентность загрузки.

Ключевой принцип: нельзя полагаться только на диапазон дат или факт, что «запись уже загружена в этот период». Нужно уметь сравнить старое и новое состояние. Основные подходы.

Сравнение по контрольному хэшу (hash diff)

Один из наиболее практичных и распространённых подходов.

Идея:

Для набора значимых бизнес-атрибутов вычисляется хэш (hash_diff).
При каждой загрузке для сущности:
- если hash_diff не изменился — бизнес-состояние не изменилось, можно не создавать новую версию;
- если hash_diff изменился — фиксируем новую версию (SCD2: закрываем старую, создаём новую).

Как это работает:

Определяем набор полей, которые считаем значимыми для истории:
- например: name, segment, status, limit_amount.
В staging/ETL слое считаем хэш. Пример (PostgreSQL):

SELECT
    customer_id,
    md5(
        coalesce(name, '') || '|' ||
        coalesce(segment, '') || '|' ||
        coalesce(status, '') || '|' ||
        coalesce(limit_amount::text, '')
    ) AS hash_diff
FROM source_customer;

В таблице историй (SCD2) храним hash_diff вместе с версией:

CREATE TABLE dim_customer_hist (
    customer_sk      BIGSERIAL PRIMARY KEY,
    customer_bk      TEXT NOT NULL,
    name             TEXT,
    segment          TEXT,
    status           TEXT,
    limit_amount     NUMERIC(18,2),
    hash_diff        CHAR(32) NOT NULL,
    valid_from       TIMESTAMP NOT NULL,
    valid_to         TIMESTAMP NOT NULL,
    is_current       BOOLEAN NOT NULL,
    source_system    TEXT NOT NULL
);

Логика обновления:

Выбираем текущую версию по business key (customer_bk) с is_current = true.
Сравниваем hash_diff из источника с hash_diff текущей версии:
- если совпадают:
  - ничего не делаем — атрибуты не менялись, даже если updated_at источника изменился;
- если различаются:
  - это реальное изменение бизнес-состояния:
    - закрываем старую запись,
    - добавляем новую версию с новым hash_diff.

Упрощённый MERGE-подход (концептуально):

-- Pseudo-logic:
IF incoming.hash_diff != current.hash_diff THEN
    UPDATE dim_customer_hist
       SET valid_to = :change_ts,
           is_current = FALSE
     WHERE customer_bk = :bk
       AND is_current = TRUE;

    INSERT INTO dim_customer_hist (
        customer_bk, name, segment, status, limit_amount,
        hash_diff, valid_from, valid_to, is_current, source_system
    )
    VALUES (
        :bk, :name, :segment, :status, :limit_amount,
        :hash_diff, :change_ts, TIMESTAMP '9999-12-31', TRUE, :source
    );
END IF;

Преимущества:

Быстрая проверка изменений (сравнение хэшей вместо всех полей).
Идемпотентность:
- повторная загрузка тех же данных не плодит новые версии.
Четкий контроль именно бизнес-изменений.

Прямое сравнение атрибутов (без хэша)

Альтернативный, более прямолинейный, подход:

На входе staging есть новая версия атрибутов.
Сравниваем по полям с текущей версией:
- если хотя бы один значимый атрибут отличается — создаём новую SCD2-версию;
- иначе — пропускаем.

Пример:

WHEN MATCHED AND (
       d.name        IS DISTINCT FROM s.name
    OR d.segment     IS DISTINCT FROM s.segment
    OR d.status      IS DISTINCT FROM s.status
    OR d.limit_amount IS DISTINCT FROM s.limit_amount
) THEN
    -- закрыть старую, добавить новую версию

Плюсы:

прозрачно, без хэшей.

Минусы:

сложнее и шумнее SQL при большом числе полей;
выше риск ошибки при изменении списка атрибутов.

Хэш по сути инкапсулирует эту проверку.

CDC (Change Data Capture)

Если доступен CDC (Debezium, binlog, GoldenGate и т.п.):

Мы получаем поток изменений: BEFORE/AFTER для каждой операции;
Уже на уровне CDC известно, что атрибуты изменились;
В DWH:
- применяем SCD2-логику напрямую к событиям:
  - каждое UPDATE порождает закрытие старой версии и создание новой.

Плюсы:

максимально точное отражение изменений;
можно восстанавливать историю в деталях.

Минусы:

сложнее инфраструктура, зависит от источников.

Повторная загрузка окна с диффом (rebuild периодов)

Для источников, которые пересылают данные за период целиком (например, ежедневный snapshot):

на каждый день получаем «полную картину» на конец дня;
для слоёв историчности:
- сравниваем текущий снапшот с прошлым:
  - новые записи → новые версии,
  - изменённые → закрыть старую, открыть новую,
  - удалённые → закрыть период (или отметить как inactive).

Этот подход тоже можно строить на хэшах.

Важные нюансы корректной реализации

Идемпотентность:
- один и тот же набор входных данных всегда даёт одно и то же состояние истории;
- повторный запуск не создает лишних версий.
Не плодить версии без реальных изменений:
- фильтровать по hash_diff / сравнению атрибутов;
- не считать изменением изменения только технических полей (load_ts).
Управление временем:
- корректно выбирать valid_from:
  - бизнес-время изменения (если доступно),
  - или время поступления изменений в DWH;
- следить, чтобы периоды не пересекались, не было дырок и дубликатов is_current.
Разделять техизменения и бизнес-изменения:
- изменение времени загрузки, технического статуса не должно создавать новой бизнес-версии.

Пример минимальной SCD2-логики с hash_diff (SQL-эскиз)

Пусть staging_customers — снимок актуального состояния из источника с рассчитанным hash_diff.

-- Закрываем старые версии, где hash_diff изменился
UPDATE dim_customer_hist d
SET valid_to = :as_of_ts,
    is_current = FALSE
FROM staging_customers s
WHERE d.customer_bk = s.customer_bk
  AND d.is_current = TRUE
  AND d.hash_diff <> s.hash_diff;

-- Добавляем новые версии для изменившихся
INSERT INTO dim_customer_hist (
    customer_bk, name, segment, status, limit_amount,
    hash_diff, valid_from, valid_to, is_current, source_system
)
SELECT
    s.customer_bk, s.name, s.segment, s.status, s.limit_amount,
    s.hash_diff, :as_of_ts, TIMESTAMP '9999-12-31', TRUE, s.source_system
FROM staging_customers s
LEFT JOIN dim_customer_hist d
  ON d.customer_bk = s.customer_bk
 AND d.is_current = TRUE
WHERE d.customer_bk IS NULL
   OR d.hash_diff <> s.hash_diff;

Это типичный промышленный паттерн.

Кратко:

Для отслеживания изменений бизнес-атрибутов внутри периода действия записи применяются:

контрольные хэши (hash_diff) по значимым полям;
либо явное сравнение всех значимых атрибутов;
либо CDC на уровне источника;
плюс SCD2-логика:
- при обнаружении изменения:
  - закрыть старую версию,
  - создать новую с актуальными атрибутами и корректным периодом.

Такой подход обеспечивает:

отсутствие потерь изменений,
отсутствие лишних версий,
детерминированность и идемпотентность загрузок,
корректную историческую аналитику «как было на момент X».

Вопрос 21. Что такое NULL в базе данных и как с ним работать в SQL?

Таймкод: 00:20:32

Ответ собеседника: правильный. Определяет NULL как отсутствие значения, приводит COALESCE для подстановки значений по умолчанию, правильно использует IS NULL / IS NOT NULL для фильтрации, отмечает, что арифметические операции с NULL дают NULL.

Правильный ответ:

NULL в реляционной базе данных — это специальное маркерное значение, обозначающее «нет данных», «неизвестно» или «не применимо». Критически важно понимать, что:

NULL — не 0, не пустая строка и не «значение по умолчанию»;
это «неизвестно», и логика работы с ним подчиняется трёхзначной логике (TRUE / FALSE / UNKNOWN).

Основные свойства NULL:

Сравнение с NULL:

Операторы =, <>, >, < и т.п. с участием NULL дают UNKNOWN (не TRUE и не FALSE).

Поэтому выражения вида:

WHERE col = NULL      -- неверно
WHERE col <> NULL     -- неверно

не работают как ожидается.

Правильно:
```
WHERE col IS NULL
WHERE col IS NOT NULL
```

NULL в логических выражениях и JOIN’ах:

В условиях:
```
WHERE col = 10 AND other_col = NULL
```
вторая часть даёт UNKNOWN, вся конструкция не будет отобрана.
В JOIN:
- сравнение по полям с NULL не совпадает:
  - запись с NULL-значением ключа не сматчится по = с NULL в другой таблице;
- это важно для внешних ключей, связей и аналитических запросов.

NULL в агрегатах:

COUNT(*) — считает все строки.
COUNT(col) — не считает строки, где col IS NULL.
SUM(col), AVG(col) — игнорируют NULL, считают только по не-NULL значениям.
MIN(col), MAX(col) — игнорируют NULL.

Примеры:

SELECT
    COUNT(*)           AS total_rows,
    COUNT(salary)      AS salary_not_null,
    AVG(salary)        AS avg_salary
FROM employees;

NULL в выражениях:

В арифметике:
```
5 + NULL  -> NULL
```
В конкатенации (зависит от СУБД, но часто):
```
'Hello ' || NULL  -> NULL
```
Поэтому для безопасной обработки используют функции подстановки:
- COALESCE(col, default_value):
  - возвращает первый не-NULL аргумент.
- NVL (Oracle), IFNULL (MySQL), ISNULL (SQL Server) — аналоги.

Примеры:

SELECT
    COALESCE(middle_name, '') AS middle_name_safe
FROM person;

SELECT
    COALESCE(discount, 0) * amount AS discount_amount
FROM orders;

Особенности в условиях фильтрации и аналитике:

Частое «скрытое» поведение:
```
WHERE col <> 0
```
не вернет строки, где col IS NULL, потому что:
- col <> 0 → UNKNOWN для NULL, а не TRUE;
- в WHERE остаются только TRUE.
Если нужно трактовать NULL как 0 или другое значение, делайте это явно:
```
WHERE COALESCE(col, 0) <> 0
```

Дизайн схемы и NULL:

NULL лучше использовать осознанно:
- когда значение реально может отсутствовать или не применимо.
Если поле логически обязательно:
- задавать NOT NULL;
- это упрощает бизнес-логику и запросы.
Для булевых полей:
- три состояния (TRUE/FALSE/NULL) часто вносят путаницу;
- если третье состояние не нужно — делаем NOT NULL и задаем явный default.

Пример практического использования (аналитика и витрины):

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

SELECT
    o.order_id,
    o.amount,
    COALESCE(o.discount, 0)                          AS discount,
    o.amount - COALESCE(o.discount, 0)               AS amount_after_discount
FROM orders o;

И пример фильтра «актуальных» (не удаленных) записей при soft delete:

SELECT *
FROM customers
WHERE deleted_at IS NULL;

NULL и Go (когда полезно понимать на бэкенде):

При работе с БД из Go:

Для nullable-полей лучше использовать:
- sql.NullString, sql.NullInt64, sql.NullTime,
- или кастомные типы/генерики.
Это помогает явно различать:
- «значение есть» и «значения нет (NULL)»,
- а не смешивать с нулевыми значениями по типу.

Кратко:

NULL — это «нет значения/неизвестно», а не число, не пустая строка.
Корректная работа с NULL включает:
- IS NULL / IS NOT NULL вместо = NULL;
- использование COALESCE/IFNULL/NVL для подстановки значений;
- понимание, как агрегаты и условия трактуют NULL;
- аккуратный дизайн схем: где разрешать NULL, а где нет.
Уверенное владение этими нюансами критично для корректных запросов, отчётов и бизнес-логики.

Вопрос 22. Соединяются ли записи с NULL в ключевых полях при соединении таблиц?

Таймкод: 00:21:34

Ответ собеседника: правильный. Указывает, что значения с NULL в ключах не соединяются в JOIN, ссылаясь на то, что NULL не равен NULL, хотя отмечает, что ранее явно это не проверял.

Правильный ответ:

Коротко: при обычном JOIN строки с NULL в соединяемых полях не «сматываются» друг с другом, даже если в обеих таблицах NULL, потому что:

NULL не равен NULL;
любое сравнение через = с участием NULL даёт UNKNOWN, а не TRUE.

Подробности:

Как работает JOIN с NULL

При INNER JOIN и большинстве условий вида:

SELECT *
FROM t1
JOIN t2 ON t1.key = t2.key;

условие t1.key = t2.key для строк, где:

t1.key IS NULL и t2.key IS NULL

даёт результат:

NULL = NULL → UNKNOWN

В фильтре ON/WHERE в результат попадают только строки, где условие TRUE. Строки с UNKNOWN (и FALSE) отбрасываются, следовательно:

записи с NULL в ключевом поле не соединяются друг с другом.

Примеры

CREATE TABLE t1 (id INT);
CREATE TABLE t2 (id INT);

INSERT INTO t1 VALUES (1), (NULL);
INSERT INTO t2 VALUES (1), (NULL);

SELECT *
FROM t1
JOIN t2 ON t1.id = t2.id;

Результат:

будет только строка (1, 1);
пара (NULL, NULL) не попадёт в результат.

LEFT / RIGHT / FULL JOIN и NULL

LEFT JOIN:
```
SELECT *
FROM t1
LEFT JOIN t2 ON t1.id = t2.id;
```
- строки из t1 с NULL в id попадут в результат,
- но без матчинга в t2 (все поля t2 будут NULL),
- потому что соединения по = всё равно не произойдёт.
FULL OUTER JOIN:
- строки с NULL в ключах попадут каждая отдельно:
  - из t1 — с NULL в полях t2,
  - из t2 — с NULL в полях t1,
- но не как пара «NULL-NULL».

Выводы для проектирования

Поля, участвующие в ключах и JOIN’ах:
- по возможности делать NOT NULL;
- если значение «отсутствует», лучше использовать:
  - отдельные состояния/флаги,
  - специальные справочники,
  - или обрабатывать такие записи отдельно.
Нельзя рассчитывать на то, что строки с NULL в join-колонке «сопоставятся между собой» в обычном JOIN — они не сопоставятся.

Если нужно соединять «пустые» значения

Иногда требуется трактовать NULL как «одно и то же отсутствие значения». Тогда нужно указывать это явно:

SELECT *
FROM t1
JOIN t2
  ON COALESCE(t1.key, 'N/A') = COALESCE(t2.key, 'N/A');

Или:

SELECT *
FROM t1
JOIN t2
  ON (t1.key = t2.key)
  OR (t1.key IS NULL AND t2.key IS NULL);

Это уже осознанное отклонение от стандартной семантики NULL, а не поведение по умолчанию.

Кратко:

В стандартном SQL:
- NULL не равен NULL;
- JOIN по = не соединяет NULL-значения.
Если нужно иное поведение, его задают явно через COALESCE или дополнительные условия в ON.

Вопрос 23. Какие виды соединений таблиц существуют?

Таймкод: 00:22:13

Ответ собеседника: правильный. Перечисляет INNER JOIN и OUTER JOIN (LEFT, RIGHT, FULL), упоминает CROSS JOIN и возможность соединения таблицы самой с собой.

Правильный ответ:

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

Основные типы JOIN:

INNER JOIN

Возвращает только те строки, для которых условие соединения истинно с обеих сторон.
Если соответствующей строки нет хотя бы в одной из таблиц — пара не попадает в результат.

Пример:

SELECT o.id, o.customer_id, c.name
FROM orders o
INNER JOIN customers c ON o.customer_id = c.id;

Использование:

когда нужны только «полные матчинг-пары»;
типично для бизнес-логики: заказы только с существующими клиентами и т.п.

LEFT OUTER JOIN (LEFT JOIN)

Возвращает все строки из левой таблицы.
Для тех, у кого нет совпадения справа — значения правой таблицы будут NULL.

Пример:

SELECT c.id, c.name, o.id AS order_id, o.amount
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id;

Использование:

найти сущности «без связей»:
- клиенты без заказов;
- сотрудники без задач.
построение отчетов, где левая сущность обязательна, правая — опциональна.

RIGHT OUTER JOIN (RIGHT JOIN)

Аналог LEFT JOIN, но с приоритетом правой таблицы:
- возвращает все строки из правой,
- плюс совпавшие из левой,
- при отсутствии совпадения слева — NULL.

Пример:

SELECT c.id, c.name, o.id AS order_id
FROM customers c
RIGHT JOIN orders o ON o.customer_id = c.id;

На практике:

используется реже LEFT JOIN, часто можно переписать, поменяв местами таблицы и сделав LEFT JOIN.

FULL OUTER JOIN (FULL JOIN)

Объединяет LEFT и RIGHT:
- возвращает:
  - пары, где есть совпадения слева и справа (как INNER),
  - строки только слева (с NULL справа),
  - строки только справа (с NULL слева).

Пример:

SELECT c.id AS customer_id, o.id AS order_id
FROM customers c
FULL OUTER JOIN orders o ON o.customer_id = c.id;

Использование:

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

CROSS JOIN

Декартово произведение:
- каждая строка левой таблицы соединяется с каждой строкой правой.
Без условия соединения; размер результата = N * M.

Пример:

SELECT *
FROM currencies c
CROSS JOIN dates d;

Использование:

генерация матриц (например, все комбинации дат и валют);
редко используется «случайно» — при ошибке, когда забыли ON.

SELF JOIN

Не отдельный тип, а приём:

соединение таблицы самой с собой (любым видом JOIN);
используется с алиасами.

Примеры:

Иерархии (сотрудник–руководитель):

SELECT e.id, e.name, m.name AS manager_name
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.id;

Поиск «пар» записей по условию внутри одной таблицы.

JOIN с неравенством и сложными условиями

Условия соединения могут быть сложнее, чем =:

диапазоны,
составные условия,
несколько полей.

Пример диапазонного JOIN (типично для тарифов, временных интервалов):

SELECT s.id, t.tariff_name
FROM sessions s
JOIN tariffs t
  ON s.start_at >= t.valid_from
 AND s.start_at <  t.valid_to;

Это всё еще INNER JOIN, просто с нетривиальным ON.

Практические моменты и частые ошибки:

NULL в условиях соединения:
- по = не соединяется (NULL не равен NULL).
Неправильное условие ON:
- пропущенное условие → неявный CROSS JOIN → взрыв строки.
Логика фильтрации:
- условия в ON vs в WHERE:
  - для OUTER JOIN важно:
    - фильтрацию по полям присоединяемой таблицы, которая может быть NULL, часто нужно оставлять в ON, а не в WHERE, чтобы не «убить» полустроки.

Пример типичной ошибки:

-- Хотели: все клиенты + их заказы, если есть
SELECT c.id, o.id
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
WHERE o.status = 'PAID';  -- превращает LEFT JOIN в INNER

-- Правильно:
SELECT c.id, o.id
FROM customers c
LEFT JOIN orders o
  ON o.customer_id = c.id
 AND o.status = 'PAID';

Кратко:

Базовые типы: INNER, LEFT, RIGHT, FULL, CROSS, плюс self join как паттерн.
Важно понимать:
- какие строки обязательны,
- какие могут отсутствовать,
- куда ставить условия.
Уверенное владение JOIN’ами — основа корректных отчетов, витрин, агрегаций и бизнес-логики в SQL.

Вопрос 24. Определить минимальное и максимальное число строк при LEFT JOIN таблиц с 100 и 10 записями.

Таймкод: 00:22:37

Ответ собеседника: правильный. Говорит, что при LEFT JOIN результат всегда содержит 100 строк: и при наличии совпадений, и при их отсутствии, так как берутся все строки из левой таблицы.

Правильный ответ:

Для корректного ответа важно уточнить контекст: речь идет о классическом LEFT JOIN двух таблиц:

левая таблица: 100 строк;
правая таблица: 10 строк;
соединение по какому-то условию ON left.key = right.key.

Семантика LEFT JOIN:

Всегда возвращает все строки из левой таблицы.
Для каждой строки слева:
- если есть совпадающие строки справа — они присоединяются (может быть несколько);
- если нет совпадений — поля правой таблицы будут NULL.

Отсюда:

Минимальное количество строк в результате:
- всегда равно числу строк левой таблицы.
- В нашем примере: минимум = 100.
- Это случается, если:
  - для каждой строки слева нет совпадений справа,
  - или максимум по одному совпадению, так что нет «размножения».
Максимальное количество строк:
- теоретически определяется числом совпадений справа на одну левую строку.
- В простейшей постановке вопроса (без уточнения ключей) можно сказать:
  - минимально гарантированное количество строк: 100,
  - верхняя граница при условии, что каждая правая строка матчится с несколькими левыми, а левая с несколькими правыми, может быть вплоть до декартова произведения (100 * 10 = 1000), если условие соединения фактически таково, что каждая из 10 строк справа подходит ко всем 100 строкам слева.
- Но при классическом кейсе «ключ уникален справа»:
  - каждая левая строка матчит максимум одну правую,
  - максимум также будет 100.

Итого по типовым сценариям:

Если правая таблица по ключу уникальна:
- и есть совпадения — результат 100 строк;
- и нет совпадений — всё равно 100 строк;
- минимальное = максимальное = 100.
В общем случае (ключ не уникален справа):
- минимум: 100 (нет совпадений или по одному);
- максимум: 1000 (каждая из 10 строк справа совпадает со всеми 100 строками слева).

Ключевая мысль:

LEFT JOIN гарантирует минимум строк = количество строк левой таблицы;
рост результата сверху зависит от кратности совпадений справа.

Вопрос 25. Определить минимальное и максимальное число строк при INNER JOIN таблиц с 100 и 10 записями.

Таймкод: 00:23:52

Ответ собеседника: правильный. Указывает, что при INNER JOIN:

минимум строк — 0 (если нет пересечений по ключу),
максимум — 10 (если все ключи правой таблицы найдены в левой), при предположении уникальности соединяемых ключей в обеих таблицах.

Правильный ответ:

Для INNER JOIN результат содержит только те строки, для которых условие соединения истинно одновременно для левой и правой таблицы.

Условия задачи:

левая таблица: 100 строк;
правая таблица: 10 строк;
рассматриваем «обычный» случай с уникальными ключами в правой таблице (и в формулировке ответа кандидата — фактически подразумевается уникальность с обеих сторон по join-колонке).

Тогда:

Минимальное количество строк:
- 0 — если ни один ключ правой таблицы не найден в левой,
- т.е. нет ни одной пары, удовлетворяющей условию ON.
Максимальное количество строк (при уникальных ключах):
- 10 — если все 10 ключей правой таблицы присутствуют в левой (для каждого ключа в правой есть хотя бы одно совпадение слева, и при уникальности — ровно одно).
- Лимит по максимуму задаётся меньшим количеством строк (правой таблицей), так как каждая строка правой при уникальности ключей может сматчиться не более чем с одной строкой левой.

Важно понимать более общий случай:

Если ключи в левой таблице не уникальны (могут быть несколько строк с одним и тем же join-значением), а в правой — уникальны:
- максимальное количество строк может быть больше 10:
  - каждая из 10 строк справа может сматчиться с несколькими строками слева;
  - теоретический максимум: сумма кратностей совпадений по каждому ключу.
Если ключи не уникальны в обеих таблицах:
- возможен эффект «умножения» строк (many-to-many через JOIN).

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

минимум: 0,
максимум: 10.

Вопрос 26. Определить минимальное и максимальное число строк при FULL OUTER JOIN и при CROSS JOIN.

Таймкод: 00:24:08

Ответ собеседника: частично правильный. Для FULL OUTER JOIN называет диапазон 100–110 строк, исходя из объединения LEFT и RIGHT JOIN для таблиц 100 и 10 строк, что верно только при предположении уникальных ключей и ограниченных пересечениях. Для CROSS JOIN корректно указывает 1000 строк (декартово произведение 100 × 10).

Правильный ответ:

Здесь важно разделить:

общий случай;
частный случай с предположениями об уникальности и характере пересечения данных.

FULL OUTER JOIN

FULL OUTER JOIN возвращает:

все совпавшие пары строк (как INNER JOIN),
плюс строки только из левой таблицы, для которых нет совпадений справа,
плюс строки только из правой таблицы, для которых нет совпадений слева.

Формула на уровне множеств (по ключам):

количество строк в результате:
- = |совпавшие| + |только слева| + |только справа|.

При заданных размерах:

левая: 100 строк,
правая: 10 строк.

Общий случай (без доп. условий):

Минимум:
- если ВСЕ строки правой таблицы находят соответствия в левой,
- и нет ни одной строки левой, которая была бы вообще без совпадений справа,
- то результат равен числу строк левой:
  - минимум = 100.
Максимум:
- если НЕТ пересечений по ключам вообще:
  - ни одна строка правой не совпала ни с одной из левой;
- тогда в результат войдут:
  - все 100 строк левой,
  - плюс все 10 строк правой,
  - максимум = 110.

Таким образом, при предположении уникальных ключей и отсутствии дублирующих совпадений:

диапазон для FULL OUTER JOIN: от 100 до 110 строк.

Однако важно понимать:

Если соединяемый ключ не уникален и возможны multiple matches (1 ко многим, многие ко многим), то:
- число строк в части совпавших пар может быть больше, чем 100 или 10,
- тогда верхняя граница будет больше 110.
То есть 100–110 — это корректный диапазон только при условиях:
- уникальность ключей в обеих таблицах,
- отсутствие многократных совпадений.

На собеседовании правильно:

явно проговорить:
- FULL OUTER JOIN ≥ max(кол-во строк слева, кол-во строк справа),
- и ≤ (кол-во строк слева + кол-во строк справа), если считаем, что совпадения не приводят к размножению строк.

Для данного примера с такими предположениями корректно:

минимум: 100,
максимум: 110.

CROSS JOIN

CROSS JOIN — декартово произведение двух таблиц:

каждая строка левой таблицы соединяется с каждой строкой правой.

Формула:

результат = N_left × N_right.

Для таблиц:

левая: 100 строк,
правая: 10 строк,

результат:

100 × 10 = 1000 строк.

Здесь нет диапазона: размер строго фиксирован.

Кратко:

FULL OUTER JOIN (при уникальных ключах и без мульти-сопоставлений):
- минимум: 100,
- максимум: 110.
CROSS JOIN:
- всегда 1000 строк для 100 и 10 записей.

Вопрос 27. Какие агрегатные функции используются и для чего нужна HAVING?

Таймкод: 00:24:49

Ответ собеседника: правильный. Указывает использование основных агрегатных функций (SUM, MAX, MIN и т.п.) и корректно объясняет, что HAVING применяется для фильтрации результатов после группировки.

Правильный ответ:

В SQL агрегатные функции и предложение HAVING — ключевые инструменты для аналитики, построения отчетов и витрин. Важно не только знать названия функций, но и чётко понимать порядок выполнения запроса и разницу между WHERE и HAVING.

Основные агрегатные функции:

COUNT:
- COUNT(*) — количество строк.
- COUNT(col) — количество не-NULL значений в колонке.
SUM(col):
- сумма значений (игнорирует NULL).
AVG(col):
- среднее значение (игнорирует NULL).
MIN(col), MAX(col):
- минимум/максимум по столбцу (игнорируют NULL).

Примеры:

SELECT
    COUNT(*)                  AS total_orders,
    SUM(amount)               AS total_revenue,
    AVG(amount)               AS avg_order_amount,
    MIN(amount)               AS min_order_amount,
    MAX(amount)               AS max_order_amount
FROM orders;

Часто используемые производные и доп. функции (в разных СУБД):

COUNT(DISTINCT col) — количество уникальных значений.
Медиана, перцентили, статистические функции:
- PERCENTILE_CONT, PERCENTILE_DISC, STDDEV, VARIANCE и т.п.
В колоночных/аналитических БД:
- специальные агрегаты: uniqExact, uniqCombined, quantile, groupArray и др.

HAVING: для чего нужен и как работает

HAVING используется для фильтрации результатов после агрегирования (после GROUP BY). Критическое отличие:

WHERE:
- фильтрует строки до группировки;
- не может использовать агрегатные функции (в обычном смысле).
HAVING:
- фильтрует группы после применения GROUP BY и агрегатов;
- в нём можно и нужно использовать агрегатные функции.

Простой пример:

Найти клиентов, у которых суммарный объем заказов больше 10 000.

Неправильно:

SELECT customer_id, SUM(amount) AS total_amount
FROM orders
WHERE SUM(amount) > 10000    -- так нельзя
GROUP BY customer_id;

Правильно:

SELECT customer_id, SUM(amount) AS total_amount
FROM orders
GROUP BY customer_id
HAVING SUM(amount) > 10000;

Здесь:

GROUP BY собирает строки по customer_id,
SUM(amount) считает сумму по каждой группе,
HAVING отфильтровывает только те группы, у которых SUM(amount) > 10000.

Комбинация WHERE и HAVING:

Часто используются вместе:

WHERE — отсечь лишние строки до агрегации (для производительности и корректности),
HAVING — наложить условия на агрегаты.

Пример: найти товары, у которых в 2025 году суммарные продажи > 1 000 000.

SELECT product_id, SUM(amount) AS total_amount
FROM sales
WHERE sale_date >= DATE '2025-01-01'
  AND sale_date <  DATE '2026-01-01'
GROUP BY product_id
HAVING SUM(amount) > 1000000;

Порядок:

WHERE ограничивает строки 2025 годом,
GROUP BY группирует по product_id,
HAVING фильтрует группы по сумме.

Практические рекомендации:

Всегда использовать WHERE для «доагрегатной» фильтрации — это уменьшает объем данных.
HAVING использовать осознанно для условий на агрегаты или в редких случаях для условий на выражения группировки.
Не злоупотреблять HAVING там, где можно фильтровать в WHERE: это влияет на производительность.

Кратко:

Агрегатные функции (COUNT, SUM, AVG, MIN, MAX и др.) — ядро аналитических запросов.
HAVING — фильтр по результатам группировки и агрегатов; WHERE — фильтр по исходным строкам.
Корректное разделение ролей WHERE и HAVING критично для правильных и эффективных запросов.

Вопрос 28. Можно ли в одном запросе использовать несколько агрегатных функций и можно ли делать GROUP BY по всем выбранным полям без агрегатов?

Таймкод: 00:25:14

Ответ собеседника: правильный. Подтверждает, что в одном SELECT можно использовать несколько агрегатных функций. Верно отмечает, что если в SELECT нет агрегатов, а все выбранные поля перечислены в GROUP BY, то результат соответствует уникальным комбинациям значений этих полей.

Правильный ответ:

Кратко:

Да, в одном запросе можно использовать несколько агрегатных функций одновременно.
Да, можно (и корректно) делать GROUP BY по всем выбранным полям без агрегатов — это способ получить уникальные комбинации.

Подробнее с примерами.

Несколько агрегатных функций в одном запросе

Это стандартная и очень распространенная практика.

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

SELECT
    customer_id,
    COUNT(*)           AS orders_count,
    SUM(amount)        AS total_amount,
    AVG(amount)        AS avg_amount,
    MIN(amount)        AS min_amount,
    MAX(amount)        AS max_amount
FROM orders
GROUP BY customer_id;

Особенности:

В SELECT могут быть:
- колонки из GROUP BY (customer_id),
- любые агрегатные функции по сгруппированным строкам.
СУБД посчитает все агрегаты за один проход по данным (в пределах группы), что эффективно.

GROUP BY по всем выбранным полям без агрегатов

Если в SELECT используются только поля, указанные в GROUP BY, и нет агрегатных функций:

запрос вернет набор уникальных комбинаций этих полей;
это эквивалентно DISTINCT по тем же полям (в подавляющем большинстве СУБД).

Пример:

SELECT DISTINCT country, city
FROM customers;

SELECT country, city
FROM customers
GROUP BY country, city;

дают одинаковый результат: список уникальных пар (country, city).

Использование:

когда нужно убрать дубликаты,
когда GROUP BY готовит базу для последующих JOIN/подзапросов.

Практические моменты и типичные ошибки

Все неагрегированные поля в SELECT должны быть в GROUP BY:
- это требование SQL-стандарта (и большинства строгих СУБД, таких как PostgreSQL);
- некоторые СУБД (MySQL в старых режимах) это нарушали, что приводило к трудноуловимым багам.
Если поле не в GROUP BY и не в агрегате:
- запрос либо невалиден, либо поведение неопределенно.

Неправильно (в строгих СУБД):

SELECT customer_id, amount
FROM orders
GROUP BY customer_id;  -- amount не в GROUP BY и не в агрегате

Правильно:

SELECT customer_id, SUM(amount) AS total_amount
FROM orders
GROUP BY customer_id;

или:

SELECT customer_id, amount
FROM orders
GROUP BY customer_id, amount;

(второй вариант вернет уникальные пары (customer_id, amount)).

Кратко:

Несколько агрегатов в одном запросе — норма: считаем разные метрики по тем же группам.
GROUP BY по всем выбранным полям без агрегатов — корректный способ получить уникальные комбинации значений (аналог DISTINCT).
Важно соблюдать правило: каждое поле в SELECT либо агрегат, либо включено в GROUP BY.

Вопрос 29. Как посчитать сумму значений в одном столбце таблицы?

Таймкод: 00:26:01

Ответ собеседника: правильный. Предлагает использовать SELECT SUM(столбец) FROM таблица без группировки, что корректно решает задачу.

Правильный ответ:

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

Базовый пример:

SELECT SUM(amount) AS total_amount
FROM orders;

Ключевые моменты:

SUM(col):
- суммирует только не-NULL значения;
- NULL игнорируются.
Если нужно суммировать по группам (например, по клиенту или дате), добавляем GROUP BY:

SELECT customer_id, SUM(amount) AS total_amount
FROM orders
GROUP BY customer_id;

Если есть риск, что все значения NULL (или нет строк), итог может быть NULL:
- при необходимости подставить 0:

SELECT COALESCE(SUM(amount), 0) AS total_amount
FROM orders;

Это стандартный и корректный способ для расчета суммы по одному столбцу.

Вопрос 30. Как получить значения из первой таблицы, которых нет во второй?

Таймкод: 00:26:34

Ответ собеседника: неполный. Упоминает операции над множествами и INTERSECT, затем идею подзапроса, соглашается с подсказкой про LEFT JOIN, но не формулирует самостоятельно чёткий, корректный пример (LEFT JOIN ... WHERE вторая.поле IS NULL или EXCEPT).

Правильный ответ:

Задача: найти записи, которые присутствуют в таблице A, но отсутствуют в таблице B по некоторому ключу или набору полей. Это классический кейс для сверки данных, поиска «висячих» ссылок, контроля целостности и проверки загрузок в DWH.

Есть три основных корректных подхода (в зависимости от СУБД и требований).

LEFT JOIN + IS NULL (наиболее универсальный способ)

Идея:

Делаем LEFT JOIN таблицы A к таблице B по ключу.
Строки из A, которым не нашлось соответствия в B, получат NULL в полях B.
Фильтруем по B.key IS NULL.

Пример:

SELECT a.id
FROM table_a a
LEFT JOIN table_b b
    ON a.id = b.id
WHERE b.id IS NULL;

Семантика:

Берём все строки из A.
Для тех, где не нашлось совпадения в B по id, значения полей B будут NULL.
WHERE b.id IS NULL оставляет только те id из A, которых нет в B.

Этот подход:

универсален — работает во всех SQL-СУБД;
хорошо читается;
подходит как для одиночного ключа, так и для составного:

SELECT a.key1, a.key2
FROM table_a a
LEFT JOIN table_b b
    ON a.key1 = b.key1
   AND a.key2 = b.key2
WHERE b.key1 IS NULL;

NOT EXISTS (часто самый надёжный и семантически точный)

Идея:

Для каждой строки из A проверяем, существует ли соответствующая строка в B.
Если не существует — строка из A включается в результат.

Пример:

SELECT a.id
FROM table_a a
WHERE NOT EXISTS (
    SELECT 1
    FROM table_b b
    WHERE b.id = a.id
);

Плюсы:

Чёткая семантика: «нет ни одной строки в B с таким ключом».
Корректно работает при NULL, сложных условиях и составных ключах.
Во многих СУБД оптимизируется так же эффективно, как LEFT JOIN.

Для составного ключа:

SELECT a.key1, a.key2
FROM table_a a
WHERE NOT EXISTS (
    SELECT 1
    FROM table_b b
    WHERE b.key1 = a.key1
      AND b.key2 = a.key2
);

EXCEPT / MINUS (операции над множествами)

Поддерживается не везде одинаково (EXCEPT в PostgreSQL, MINUS в Oracle, и т.п.), но семантически:

Возвращает строки, которые есть в одном наборе, но нет в другом.

Пример (PostgreSQL):

SELECT id
FROM table_a
EXCEPT
SELECT id
FROM table_b;

Пример (Oracle):

SELECT id
FROM table_a
MINUS
SELECT id
FROM table_b;

Особенности:

Работает с множествами: дубликаты устраняются (поведение как DISTINCT).
Удобно для сверок на уровне ключей.
Менее гибко, если нужно тащить дополнительные поля из A.

Практические нюансы:

LEFT JOIN ... IS NULL vs NOT EXISTS:
- Оба варианта корректны; выбор — вопрос читаемости и вкуса, с учётом оптимизатора конкретной СУБД.
- NOT IN с NULL’ами — опасен:
  - конструкция вида:
    WHERE a.id NOT IN (SELECT id FROM table_b)
    даёт некорректные результаты, если в подзапросе есть NULL:
    - NULL в подзапросе превращает условие в UNKNOWN.
  - Лучше использовать NOT EXISTS или гарантировать, что подзапрос не содержит NULL (через WHERE id IS NOT NULL).
Для больших объёмов:
- важна индексация по join-ключам;
- операторы оптимизируются до anti-join (анти-соединения).

Примеры из практики DWH:

Найти «висячие» факты без соответствующей записи в измерении:

SELECT f.customer_id
FROM fact_sales f
LEFT JOIN dim_customer d
  ON f.customer_id = d.customer_id
WHERE d.customer_id IS NULL;

Сверить загруженные данные:

SELECT src.id
FROM src_orders src
WHERE NOT EXISTS (
    SELECT 1 FROM dwh_orders d WHERE d.id = src.id
);

Кратко:

Корректные способы получить значения из первой таблицы, которых нет во второй:
- LEFT JOIN + IS NULL,
- NOT EXISTS (рекомендуется),
- EXCEPT/MINUS для простых кейсов с поддержкой операций над множествами.
NOT IN использовать с осторожностью из-за поведения с NULL.
В ответе на интервью важно назвать хотя бы LEFT JOIN ... WHERE b.key IS NULL и NOT EXISTS с понятным примером.

Вопрос 31. Как удалить дубликаты из таблицы, оставив по одному экземпляру каждого значения?

Таймкод: 00:28:04

Ответ собеседника: неполный. Упоминает DISTINCT и GROUP BY, что решает только задачу выборки без дублей, но не изменяет данные в таблице. Далее частично описывает идею с оконной функцией ROW_NUMBER для маркировки дублей, однако не формулирует полноценный корректный DELETE-запрос и не показывает законченный рабочий паттерн.

Правильный ответ:

Задача удаления дубликатов в реальной базе — это не просто сделать SELECT DISTINCT. Важно:

явно определить, что считать дублем (по какому набору полей);
аккуратно удалить лишние строки, оставив ровно один экземпляр каждой уникальной комбинации;
обеспечить идемпотентность и безопасность (особенно в боевой системе).

Рассмотрим несколько рабочих подходов.

Условие задачи (обобщённо):

Есть таблица some_table.
Считаем дубликатами строки, у которых совпадают значения одного или нескольких полей (например, по столбцу value или по набору (col1, col2, col3)).
Нужно оставить по одной записи на каждую уникальную комбинацию этих полей.

SELECT DISTINCT и пересоздание таблицы (радикальный, но понятный способ)

Подходит:

для разовой очистки;
когда можно позволить себе пересоздание (нет жёстких ограничений по доступности).

Пошагово:

Создать временную таблицу с уникальными строками:

CREATE TABLE some_table_dedup AS
SELECT DISTINCT col1, col2, col3, ...
FROM some_table;

Убедиться, что данные корректны (проверки, сравнение количества).
Переименовать таблицы:

ALTER TABLE some_table RENAME TO some_table_backup;
ALTER TABLE some_table_dedup RENAME TO some_table;

Добавить ограничение UNIQUE, чтобы не допустить повторного появления дублей:

ALTER TABLE some_table
ADD CONSTRAINT uq_some_table UNIQUE (col1, col2, col3);

Плюсы:

просто и прозрачно;
хорошо для больших разовых чисток.

Минусы:

требует блокировок/окна обслуживания;
нужно аккуратно перенастроить внешние ключи, индексы, права.

Удаление через оконную функцию ROW_NUMBER (практический стандарт)

Это более точечный и гибкий метод, часто поддерживаемый всеми современными СУБД (PostgreSQL, Oracle, SQL Server и др.).

Идея:

Для каждой группы дублей назначить порядковый номер;
Оставить запись с row_number = 1;
Удалить записи с row_number > 1.

Пример (PostgreSQL / SQL Server / Oracle):

Пусть дубликаты определяются по (col1, col2):

WITH cte AS (
    SELECT
        id,
        col1,
        col2,
        ROW_NUMBER() OVER (
            PARTITION BY col1, col2
            ORDER BY id
        ) AS rn
    FROM some_table
)
DELETE FROM some_table
WHERE id IN (
    SELECT id
    FROM cte
    WHERE rn > 1
);

Объяснение:

PARTITION BY col1, col2:
- группируем строки по сочетанию полей, по которым ищем дубликаты.
ORDER BY id:
- определяет, какую строку считать "основной" (rn = 1);
- остальные (rn > 1) считаются дубликатами.
Удаляем только те id, у которых rn > 1.

Вариант для СУБД, поддерживающих DELETE ... USING (PostgreSQL):

WITH cte AS (
    SELECT
        id,
        ROW_NUMBER() OVER (
            PARTITION BY col1, col2
            ORDER BY id
        ) AS rn
    FROM some_table
)
DELETE FROM some_table t
USING cte
WHERE t.id = cte.id
  AND cte.rn > 1;

Плюсы:

Точно контролируем, что остаётся, что удаляется;
Можно запускать инкрементально;
Не требуются пересоздания таблицы.

Ключевой момент:

Должен быть столбец или набор столбцов, по которым можно однозначно удалить конкретную строку:
- обычно это суррогатный первичный ключ (id).

Удаление через подзапрос с MIN()/MAX()/COUNT()

Если оконные функции недоступны, можно использовать агрегаты.

Пример:

Оставить запись с минимальным id в группе дублей:

DELETE FROM some_table
WHERE id NOT IN (
    SELECT MIN(id)
    FROM some_table
    GROUP BY col1, col2
);

Предупреждение:

Для больших таблиц это может быть дорого и не везде оптимально;
NOT IN опасен в присутствии NULL — обычно добавляют WHERE id IS NOT NULL;
Лучше использовать JOIN или EXISTS.

Более безопасный вариант:

DELETE FROM some_table t
WHERE EXISTS (
    SELECT 1
    FROM some_table t2
    WHERE t2.col1 = t.col1
      AND t2.col2 = t.col2
      AND t2.id < t.id
);

Семантика:

Удалить запись t, если существует другая запись t2 с теми же col1, col2 и меньшим id;
В результате останется запись с минимальным id в каждой группе.

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

После очистки дубликатов:

необходимо предотвратить их повторное появление.

Стандартный шаг:

ALTER TABLE some_table
ADD CONSTRAINT uq_some_table UNIQUE (col1, col2);

Или:

добавить уникальный индекс.

Это особенно важно для DWH и реестровых таблиц:

слой ядра должен быть чистым;
вся «грязь» должна ловиться либо на входе, либо логироваться.

Практическая стратегия (рекомендованная)

Шаг 1: определить, что такое «дубликат»:
- список полей, по которым значения должны быть уникальны.
Шаг 2: выполнить чистку через:
- CTE + ROW_NUMBER + DELETE по id;
- или через EXISTS/агрегаты в старых СУБД.
Шаг 3: добавить UNIQUE-ограничение для гарантии.
Шаг 4: реализовать проверки на этапе загрузки данных, чтобы не плодить дубликаты.

Пример полного решения (PostgreSQL):

-- 1. Удаляем дубликаты, оставляя запись с минимальным id
WITH cte AS (
    SELECT
        id,
        ROW_NUMBER() OVER (
            PARTITION BY col1, col2
            ORDER BY id
        ) AS rn
    FROM some_table
)
DELETE FROM some_table
WHERE id IN (
    SELECT id
    FROM cte
    WHERE rn > 1
);

-- 2. Гарантируем отсутствие дублей в будущем
ALTER TABLE some_table
ADD CONSTRAINT uq_some_table UNIQUE (col1, col2);

Кратко:

DISTINCT/GROUP BY решают только выборку без дублей, но не чистят таблицу.
Корректные практические решения:
- CTE + ROW_NUMBER() OVER (PARTITION BY ...) + DELETE по первичному ключу;
- или DELETE с EXISTS/агрегатами;
- или пересоздание таблицы через SELECT DISTINCT.
После чистки обязательно ввести UNIQUE-ограничение, чтобы проблема не повторялась.

Вопрос 32. Как можно сгенерировать последовательность чисел в SQL для решения задачи?

Таймкод: 00:31:22

Ответ собеседника: неполный. Говорит, что не помнит конкретный синтаксис в текущей СУБД, упоминает опыт в Oracle, рассматривает варианты через рекурсивный запрос или временную таблицу. Идея понятна, но не даёт чётких, практических, переносимых решений.

Правильный ответ:

Генерация последовательности чисел в SQL — типичный приём для:

построения календарных таблиц (по дням, месяцам);
заполнения пропусков в рядах;
join с временными интервалами;
тестовых данных;
развертывания диапазонов.

Подход зависит от СУБД, но есть несколько стандартных паттернов, которые стоит уверенно знать.

Ключевые варианты.

Рекурсивный CTE (универсальный способ)

Поддерживается в большинстве современных СУБД: PostgreSQL, SQL Server, Oracle (с WITH), DB2 и др.

Пример: последовательность от 1 до 100.

WITH RECURSIVE seq AS (
    SELECT 1 AS n
    UNION ALL
    SELECT n + 1
    FROM seq
    WHERE n < 100
)
SELECT n
FROM seq;

Особенности:

Универсальный способ, если нет специальных генераторов.
Нужно следить за ограничениями по глубине рекурсии (max_recursion).

Пример для диапазона дат:

WITH RECURSIVE dates AS (
    SELECT DATE '2025-01-01' AS d
    UNION ALL
    SELECT d + INTERVAL '1 day'
    FROM dates
    WHERE d < DATE '2025-01-31'
)
SELECT d
FROM dates;

Специализированные функции и генераторы (PostgreSQL, ClickHouse, др.)

PostgreSQL: generate_series

Очень мощный и простой инструмент.

SELECT generate_series(1, 100) AS n;

С шагом:

SELECT generate_series(0, 100, 5) AS n;

Для дат/времени:

SELECT generate_series(
    TIMESTAMP '2025-01-01 00:00:00',
    TIMESTAMP '2025-01-01 23:00:00',
    INTERVAL '1 hour'
) AS ts;

ClickHouse: функции sequence, numbers, etc.

SELECT number AS n
FROM numbers(100);  -- 0..99

Использование "числовой" / "service" таблицы

Паттерн, который часто используют в промышленных системах:

создаётся отдельная таблица numbers (или calendar/date_dim);
заполняется последовательностью заранее;
затем переиспользуется в запросах.

Пример создания (PostgreSQL-стиль):

CREATE TABLE numbers (n INT PRIMARY KEY);

INSERT INTO numbers(n)
SELECT generate_series(1, 1000000);

Использование:

SELECT n
FROM numbers
WHERE n BETWEEN 1 AND 100;

Плюсы:

быстро,
не зависит от рекурсий,
удобно для частого использования (особенно для дат и временных интервалов).

Часто делают отдельную date_dim / calendar_dim с богатой информацией (год, месяц, неделя, флаги рабочих дней и т.д.), а не генерируют даты на лету.

Трюки через системные/служебные таблицы (менее желательны)

В некоторых СУБД используют:

системные таблицы;
cross join нескольких наборов для получения диапазона.

Пример (MySQL до появления CTE, условный):

SELECT
    (units.n + tens.n * 10 + hundreds.n * 100) AS n
FROM (SELECT 0 n UNION ALL SELECT 1 UNION ALL ... UNION ALL SELECT 9) units
CROSS JOIN (SELECT 0 n UNION ALL SELECT 1 ... UNION ALL SELECT 9) tens
CROSS JOIN (SELECT 0 n UNION ALL SELECT 1 ... UNION ALL SELECT 9) hundreds
WHERE (units.n + tens.n * 10 + hundreds.n * 100) BETWEEN 1 AND 100;

Сейчас это чаще исторический хак; лучше использовать CTE или нормальные инструменты.

Практический пример: заполнение пропусков по дням

Задача: есть таблица агрегатов по заказам не за каждый день; нужно получить непрерывный ряд.

PostgreSQL:

WITH dates AS (
    SELECT generate_series(
        DATE '2025-01-01',
        DATE '2025-01-31',
        INTERVAL '1 day'
    )::date AS dt
)
SELECT
    d.dt,
    COALESCE(SUM(o.amount), 0) AS daily_amount
FROM dates d
LEFT JOIN orders o
    ON o.order_date = d.dt
GROUP BY d.dt
ORDER BY d.dt;

Рекурсивный CTE (универсальный вариант):

WITH RECURSIVE dates AS (
    SELECT DATE '2025-01-01' AS dt
    UNION ALL
    SELECT dt + INTERVAL '1 day'
    FROM dates
    WHERE dt < DATE '2025-01-31'
)
SELECT
    d.dt,
    COALESCE(SUM(o.amount), 0) AS daily_amount
FROM dates d
LEFT JOIN orders o
    ON o.order_date = d.dt
GROUP BY d.dt
ORDER BY d.dt;

Интеграция с Go-приложением (частый паттерн)

Иногда проще сгенерировать диапазон в приложении, а не в SQL.

Пример на Go:

func GenerateIntRange(from, to int) []int {
    if to < from {
        return nil
    }
    res := make([]int, 0, to-from+1)
    for i := from; i <= to; i++ {
        res = append(res, i)
    }
    return res
}

Но для сложных SQL-агрегаций (например, заполнение пропусков в DWH) генерация в SQL (CTE/generate_series) обычно удобнее и эффективнее.

Кратко:

Универсальное решение: рекурсивный CTE для генерации последовательности чисел или дат.
Специализированные решения:
- PostgreSQL: generate_series;
- ClickHouse: numbers/sequence;
- другие СУБД — свои функции.
Для частых задач:
- выделенная таблица чисел/календаря.
На собеседовании хороший ответ:
- назвать рекурсивный CTE,
- generate_series (если уместно),
- LEFT JOIN с календарной/числовой таблицей для реальных задач.

Вопрос 33. Каков опыт использования CTE и для чего они применяются?

Таймкод: 00:32:30

Ответ собеседника: правильный. Отмечает, что в основном использует подзапросы, знаком с CTE и понимает, что они упрощают структуру и читаемость сложных запросов, существуют в рамках одного запроса.

Правильный ответ:

CTE (Common Table Expression, выражения WITH) — это именованные временные результаты, определяемые в начале запроса и доступные только в его пределах. Они используются для разбиения сложной логики на понятные шаги, переиспользования промежуточных результатов и, в ряде случаев, для реализации рекурсивных алгоритмов.

Ключевые свойства:

Объявляются через WITH перед основным SELECT/INSERT/UPDATE/DELETE.
Живут только в пределах одного SQL-выражения.
Могут ссылаться друг на друга (в определённых СУБД).
Могут быть рекурсивными (WITH RECURSIVE).

Базовый синтаксис (нерекурсивный CTE):

WITH filtered_orders AS (
    SELECT *
    FROM orders
    WHERE status = 'PAID'
      AND order_date >= DATE '2025-01-01'
),
customer_totals AS (
    SELECT
        customer_id,
        SUM(amount) AS total_amount
    FROM filtered_orders
    GROUP BY customer_id
)
SELECT *
FROM customer_totals
WHERE total_amount > 100000
ORDER BY total_amount DESC;

Зачем использовать CTE (практические случаи):

Повышение читаемости и структурирование сложных запросов

Вместо вложенных подзапросов на полэкрана:
- логика разбивается на шаги с понятными именами: filtered_orders, customer_totals и т.п.
Упрощает:
- код-ревью,
- поддержку,
- поиск ошибок.

Переиспользование промежуточных результатов

Один и тот же подзапрос не нужно дублировать.
Пример: один набор данных фильтруется, потом используется и для агрегаций, и для join’ов.

WITH active_customers AS (
    SELECT id
    FROM customers
    WHERE is_active = TRUE
)
SELECT
    c.id,
    COUNT(o.id) AS orders_count
FROM active_customers c
LEFT JOIN orders o ON o.customer_id = c.id
GROUP BY c.id;

Логическая декомпозиция ETL/витрин

В DWH-процессах CTE удобно использовать для:

поэтапных трансформаций:
- очистка данных,
- нормализация,
- join с референсами,
- финальные агрегации.
Можно последовательно показать pipeline «от сырья к витрине» в одном запросе.

Пример (упрощённый):

WITH raw_clean AS (
    SELECT
        order_id,
        customer_id,
        amount::NUMERIC AS amount,
        order_ts::timestamp AS order_ts
    FROM raw_orders
    WHERE amount IS NOT NULL
),
with_customers AS (
    SELECT
        r.order_id,
        r.amount,
        r.order_ts,
        c.customer_sk
    FROM raw_clean r
    JOIN dim_customer c
      ON c.customer_bk = r.customer_id
)
SELECT
    customer_sk,
    date_trunc('day', order_ts) AS order_date,
    SUM(amount) AS total_amount
FROM with_customers
GROUP BY customer_sk, date_trunc('day', order_ts);

Такой стиль облегчает сопровождение по сравнению с несколькими вложенными подзапросами.

Рекурсивные CTE

Рекурсивные CTE позволяют:

строить иерархии (деревья, оргструктуры, каталоги);
генерировать последовательности чисел или дат;
обходить графы в пределах одного SQL.

Пример: генерация последовательности (как разбирали ранее):

WITH RECURSIVE seq AS (
    SELECT 1 AS n
    UNION ALL
    SELECT n + 1
    FROM seq
    WHERE n < 100
)
SELECT n
FROM seq;

Иерархия сотрудников:

WITH RECURSIVE org AS (
    SELECT
        id,
        manager_id,
        name,
        0 AS level
    FROM employees
    WHERE id = :root_manager_id

    UNION ALL

    SELECT
        e.id,
        e.manager_id,
        e.name,
        o.level + 1
    FROM employees e
    JOIN org o ON e.manager_id = o.id
)
SELECT *
FROM org
ORDER BY level, id;

Использование CTE в DML

Во многих СУБД CTE можно применять не только для SELECT, но и в:

INSERT,
UPDATE,
DELETE,
MERGE.

Пример: удалить дубликаты по паттерну ROW_NUMBER (продолжая предыдущий вопрос):

WITH cte AS (
    SELECT
        id,
        ROW_NUMBER() OVER (
            PARTITION BY col1, col2
            ORDER BY id
        ) AS rn
    FROM some_table
)
DELETE FROM some_table
WHERE id IN (
    SELECT id FROM cte WHERE rn > 1
);

Поведенческие детали и оптимизация

В большинстве современных СУБД CTE — это логический синтаксический сахар:
- оптимизатор способен «встраивать» их в основной план (inline), как подзапросы.
Исключение — некоторые реализации (например, старые версии PostgreSQL до 12), где:
- CTE всегда материализовывался (временный результат),
- это могло приводить к лишнему IO.
Поэтому:
- важно знать поведение конкретной СУБД;
- не плодить тяжёлые CTE без необходимости, если они реально материализуются.

Практический вывод:

CTE — инструмент для:
- читаемости,
- декомпозиции,
- переиспользования,
- рекурсивных задач.
При грамотном использовании они делают сложные запросы понятными и поддерживаемыми.
При ответе на интервью достаточно:
- показать, что вы:
  - понимаете, что CTE живёт в рамках одного запроса;
  - используете их для структурирования сложных запросов;
  - знаете про рекурсивные CTE и их типичные применения.

Вопрос 34. Как посчитать сумму и нарастающий итог по группам с помощью оконных функций?

Таймкод: 00:36:26

Ответ собеседника: неполный. Верно указывает идею использования оконных функций с PARTITION BY по группе и ORDER BY по идентификатору для нарастающего итога, но путается в синтаксисе и попытке получить итог по группе через LAST_VALUE; не формулирует финальное корректное решение.

Правильный ответ:

Оконные функции — ключевой инструмент для аналитики в SQL. Они позволяют:

считать агрегаты «поверх» строк, не схлопывая их (в отличие от GROUP BY);
получать нарастающие итоги (running/rolling totals);
считать агрегаты по группам в каждой строке (total per group).

Рассмотрим два типичных кейса:

общая сумма по группе в каждой строке;
нарастающий итог по группе.

Предположим, есть таблица payments:

CREATE TABLE payments (
    customer_id  INT,
    pay_date     DATE,
    amount       NUMERIC
);

Общая сумма по группе (total per group) через оконную функцию

Задача:

для каждой строки видеть сумму amount по всем платежам данного клиента.

Решение:

SELECT
    customer_id,
    pay_date,
    amount,
    SUM(amount) OVER (
        PARTITION BY customer_id
    ) AS total_by_customer
FROM payments;

Что здесь происходит:

PARTITION BY customer_id:
- делит строки на группы по клиенту.
SUM(amount) OVER (...):
- считает сумму по всей «партиции» (по всем строкам клиента),
- и записывает одно и то же значение total_by_customer во все строки этого клиента.
ORDER BY не указываем:
- значит, окно — вся группа целиком.

Это корректный и простой способ получить общую сумму по группе в каждой строке, без LAST_VALUE.

Нарастающий итог (running total) по группе

Задача:

для каждого клиента посчитать кумулятивную сумму его платежей в порядке времени.

Решение:

SELECT
    customer_id,
    pay_date,
    amount,
    SUM(amount) OVER (
        PARTITION BY customer_id
        ORDER BY pay_date, amount, customer_id
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS running_total
FROM payments
ORDER BY customer_id, pay_date;

Объяснение ключевых частей:

PARTITION BY customer_id:
- нарастающий итог считается отдельно для каждого клиента.
ORDER BY pay_date:
- задает порядок, в котором суммируются платежи.
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW:
- явное указание рамки окна:
  - от первой строки в группе до текущей;
- во многих СУБД для SUM() по умолчанию при наличии ORDER BY уже используется именно эта рамка, но явное указание — хорошая практика, особенно для сложных случаев.

Пример интерпретации:

Для клиента 1:

строка 1: running_total = amount_1
строка 2: running_total = amount_1 + amount_2
строка 3: running_total = amount_1 + amount_2 + amount_3 и т.д.

Общая сумма + нарастающий итог в одном запросе

Частый практический кейс — нужно и total по группе, и cumulative по каждому шагу.

SELECT
    customer_id,
    pay_date,
    amount,
    SUM(amount) OVER (
        PARTITION BY customer_id
    ) AS total_by_customer,
    SUM(amount) OVER (
        PARTITION BY customer_id
        ORDER BY pay_date, amount, customer_id
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS running_total
FROM payments
ORDER BY customer_id, pay_date;

Почему не LAST_VALUE «в лоб»

Иногда пытаются сделать так:

LAST_VALUE(SUM(amount)) OVER (...)

или подобные комбинации — это неверно.

Если нужна «итоговая сумма по группе» в каждой строке, правильно сразу использовать оконный SUM с PARTITION BY, без ORDER BY:

SUM(amount) OVER (PARTITION BY customer_id)

LAST_VALUE применим, но требует аккуратного задания рамок окна и обычно избыточен для задачи «total per group».

Практический пример для витрины

Пусть нужно:

по каждому клиенту:
- общую сумму покупок;
- нарастающий итог по датам для построения графика.

SQL (похож на реальный код DWH):

SELECT
    customer_id,
    order_date,
    amount,
    SUM(amount) OVER (PARTITION BY customer_id) AS customer_total,
    SUM(amount) OVER (
        PARTITION BY customer_id
        ORDER BY order_date, order_id
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS customer_running_total
FROM fact_orders
ORDER BY customer_id, order_date, order_id;

Интеграция с Go (как потреблять такой результат)

Фрагмент чтения нарастающего итога в сервисе на Go:

type CustomerPayment struct {
    CustomerID       int
    Date             time.Time
    Amount           float64
    TotalByCustomer  float64
    RunningTotal     float64
}

func (r *Repo) GetCustomerRunningTotals(ctx context.Context) ([]CustomerPayment, error) {
    const q = `
        SELECT
            customer_id,
            pay_date,
            amount,
            SUM(amount) OVER (PARTITION BY customer_id) AS total_by_customer,
            SUM(amount) OVER (
                PARTITION BY customer_id
                ORDER BY pay_date, amount, customer_id
                ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
            ) AS running_total
        FROM payments
        ORDER BY customer_id, pay_date;
    `
    rows, err := r.db.QueryContext(ctx, q)
    if err != nil {
        return nil, err
    }
    defer rows.Close()

    var res []CustomerPayment
    for rows.Next() {
        var p CustomerPayment
        if err := rows.Scan(
            &p.CustomerID,
            &p.Date,
            &p.Amount,
            &p.TotalByCustomer,
            &p.RunningTotal,
        ); err != nil {
            return nil, err
        }
        res = append(res, p)
    }
    return res, rows.Err()
}

Кратко:

Общая сумма по группе: SUM(col) OVER (PARTITION BY group_col).
Нарастающий итог по группе: SUM(col) OVER (PARTITION BY group_col ORDER BY sort_col ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW).
Не нужно городить сложные LAST_VALUE по SUM; оконный SUM с правильным PARTITION/ORDER/BETWEEN решает задачу явно и корректно.

Вопрос 35. Что такое индексы, какие их виды существуют и зачем они нужны?

Таймкод: 00:39:43

Ответ собеседника: правильный. Перечисляет кластеризованные и некластеризованные индексы, объясняет, что кластеризованный индекс один на таблицу и задаёт физический порядок данных, некластеризованных может быть несколько. Упоминает B-tree как основной тип, уникальные и полнотекстовые индексы. Правильно говорит, что индексы нужны для ускорения поиска.

Правильный ответ:

Индекс в базе данных — это специализированная структура данных (обычно отдельное, организованное представление ключей и ссылок на строки), которая позволяет существенно ускорить операции поиска, фильтрации, соединения и сортировки, аналогично алфавитному указателю в книге.

Ключевая идея:

Без индекса: СУБД для WHERE col = ? может быть вынуждена просканировать всю таблицу (full scan).
С индексом: СУБД быстро находит нужные ключи в индексе и затем обращается к конкретным строкам.

Но индексы — не "бесплатны": они ускоряют чтение, но стоят места и ресурсов на запись/обновление. Зрелый подход — понимающая и экономная их настройка.

Основные типы и классификации индексов

По роли и ограничениям

Обычный (non-unique) индекс:
- ускоряет поиск, но не ограничивает данные;
- допускает дубликаты значений.
Уникальный индекс (UNIQUE):
- гарантирует, что значения ключа уникальны;
- одновременно и средство ускорения поиска, и средство обеспечения целостности.
Индекс первичного ключа:
- PK почти всегда реализуется через уникальный индекс (кластеризованный или нет — зависит от СУБД).

По способу хранения относительно данных

Термины зависят от СУБД (SQL Server, InnoDB, PostgreSQL и т.п.):

Кластеризованный индекс (clustered index):
- Определяет физический (или логический) порядок хранения строк таблицы.
- Таблица хранится «по этому индексу».
- Обычно:
  - один кластеризованный индекс на таблицу.
- Хороший кандидат:
  - монотонно растущий первичный ключ (BIGINT ID, timestamp/id),
  - чтобы избежать фрагментации.
Некластеризованный индекс (non-clustered):
- Отдельная структура (обычно B-tree), которая хранит:
  - ключи индекса,
  - ссылки на реальные строки (RID или значение PK).
- Может быть несколько на таблицу.
- Оптимизируется под частые запросы по конкретным колонкам.

В PostgreSQL формально нет термина "clustered index" как в SQL Server/Innodb (есть CLUSTER как операция), но логика аналогична: есть основной индекс (часто по PK), к которому привязаны ссылки, и отдельные индексы для вторичных ключей.

По структуре данных

Наиболее распространённые:

B-tree индекс:
- дефолтный тип в большинстве СУБД (PostgreSQL, MySQL, SQL Server, Oracle);
- эффективен для:
  - точечного поиска (=),
  - диапазонов (>, <, BETWEEN),
  - сортировки (ORDER BY).
- Используется для большинства "обычных" запросов.
Hash индекс:
- оптимизирован под точные сравнения (=);
- неподходящ для диапазонных запросов;
- поддержка зависит от СУБД (в PostgreSQL hash-индексы есть, но используются редко, B-tree чаще предпочтителен).
Bitmap индекс (Oracle, DWH-сценарии):
- эффективен для колонок с низким числом уникальных значений (gender, status);
- подходит для аналитических запросов и комбинированных фильтров;
- не подходит для частых изменений в OLTP.
GiST, GIN, RUM (PostgreSQL):
- для полнотекстового поиска, JSONB, геоданных, массивов.
Специализированные индексы:
- полнотекстовые (FULLTEXT в MySQL, GIN по ts_vector в PostgreSQL);
- пространственные (spatial),
- индексы по выражениям (functional / expression indexes),
- partial / filtered indexes (по условию WHERE).

Составные индексы (composite / multi-column)

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

CREATE INDEX idx_orders_customer_date
    ON orders (customer_id, order_date);

Особенности:

Важен порядок полей:
- индекс (customer_id, order_date) хорошо для:
  - WHERE customer_id = ?;
  - WHERE customer_id = ? AND order_date BETWEEN ...;
- но не для:
  - WHERE order_date = ? (без customer_id), оптимизатор может использовать только часть в некоторых СУБД, но общая рекомендация — ориентироваться на порядок.
Правильный дизайн составных индексов — критичен для сложных фильтров и JOIN’ов.

Зачем нужны индексы (ключевые сценарии)

Ускорение поиска (WHERE)

Пример:

CREATE INDEX idx_users_email ON users(email);

SELECT * FROM users WHERE email = 'user@example.com';

Без индекса:

full scan по всей таблице.

С индексом:

логарифмический поиск по B-tree, несколько I/O вместо тысяч/миллионов.

Ускорение JOIN

JOIN по полям без индексов:

может приводить к nested loop с полным сканированием.

Если поля соединения проиндексированы:

планировщик выбирает эффективные стратегии (index nested loop, merge join, hash join с быстрым доступом к ключу).

Ускорение сортировки (ORDER BY) и GROUP BY

Если есть индекс, соответствующий:

ORDER BY col1, col2
или GROUP BY col1, col2

СУБД может:

читать строки в уже отсортированном порядке;
уменьшить необходимость отдельной сортировки.

Обеспечение уникальности и целостности

UNIQUE индекс:

ALTER TABLE users
ADD CONSTRAINT uq_users_email UNIQUE (email);

Гарантия:

двух пользователей с одинаковым email не появится;
заодно быстрый поиск по email.

Частичные (filtered) индексы

Например, только по активным пользователям:

CREATE INDEX idx_users_active_email
    ON users(email)
    WHERE is_deleted = false;

Это:

уменьшает размер индекса,
ускоряет запросы по самому частому сценарию.

Важные недостатки и нюансы (что обязательно упомянуть)

Индексы ускоряют чтение, но замедляют запись:
- INSERT: нужно обновить все релевантные индексы.
- UPDATE ключевых колонок: перестроение/перемещение в индексе.
- DELETE: удаление записей из индексов.
Избыточное количество индексов:
- увеличивает размер базы,
- ухудшает производительность DML,
- усложняет сопровождение.
Не все запросы используют индексы:
- маленькие таблицы (full scan быстрее);
- плохие селективные условия (столбец с 2-3 значениями на миллионы строк);
- условия, которые ломают возможность использовать индекс:
  - функции над полем без функционального индекса (например, WHERE LOWER(email) = '...' при индексе на email);
  - несоответствие ORDER BY структуре индекса.

Хорошие практики:

Индексировать:
- PK и FK — почти всегда;
- часто используемые фильтры (WHERE),
- поля JOIN,
- ключи уникальности.
Проверять планы запросов (EXPLAIN/EXPLAIN ANALYZE).
Не плодить индексы «на всё подряд».
Для полнотекста/JSON/гео — использовать специализированные индексы (GIN/GiST и т.п.).

Минимальный SQL-пример:

-- Таблица заказов
CREATE TABLE orders (
    id           BIGSERIAL PRIMARY KEY,
    customer_id  BIGINT NOT NULL,
    created_at   TIMESTAMPTZ NOT NULL,
    amount       NUMERIC(18,2) NOT NULL
);

-- Индекс для частых выборок по customer_id и дате
CREATE INDEX idx_orders_customer_date
    ON orders (customer_id, created_at);

Типичный запрос:

SELECT *
FROM orders
WHERE customer_id = 123
  AND created_at >= now() - INTERVAL '30 days';

Этот запрос эффективно использует составной индекс.

Кратко:

Индексы — структурированный способ ускорить поиск, соединения, сортировки и обеспечить уникальность.
Ключевые виды:
- кластеризованные / некластеризованные,
- уникальные,
- B-tree, hash, bitmap, полнотекстовые, функциональные, частичные.
Их нужно проектировать осознанно:
- под реальные запросы,
- с учётом баланса между скоростью чтения и стоимостью записи.

Вопрос 36. Почему B-tree индекс ускоряет поиск и что значит сбалансированное дерево?

Таймкод: 00:41:12

Ответ собеседника: неполный. Правильно указывает, что в несбалансированном дереве поиск может деградировать почти до линейного (почти полный перебор), а в сбалансированном глубина ограничена, что даёт логарифмическое число шагов. Однако формальное объяснение баланса и структуры B-tree даёт неточно и местами путано.

Правильный ответ:

B-tree и его разновидности (B+Tree и т.п.) — базовая структура для индексов в реляционных СУБД. Понимание, как она работает, помогает осознанно проектировать индексы, понимать стоимость запросов и причины деградации.

Суть в двух тезисах:

индекс на B-tree позволяет находить нужные ключи за O(log N) обращений вместо O(N);
«сбалансированное» дерево гарантирует, что путь от корня до любого листа имеет примерно одинаковую длину и остаётся небольшим даже при больших объёмах данных.

Разберём по шагам.

Что такое B-tree индекс (на интуитивном уровне)

B-tree — многовариантное (multi-way) сбалансированное дерево поиска:

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

Поиск в таком индексе:

Начинается с корневого узла (1 диск/страница).
В корне бинарным или линейным поиском среди нескольких ключей определяется нужный диапазон.
Переход к соответствующему дочернему узлу.
Повтор шагов до достижения листа.
В листе находится точный ключ (или ближайший диапазон), после чего известно положение данных.

Благодаря этому:

глубина дерева очень мала даже для миллионов/десятков миллионов строк;
каждая операция поиска требует очень небольшого числа чтений страниц.

Пример оценочно:

если в одном узле сотни ключей,
дерево для миллионов записей имеет глубину порядка 3–4 уровней;
это буквально несколько дисковых/буферных обращений вместо линейного сканирования.

Почему важна сбалансированность

«Сбалансированное дерево» в этом контексте означает:

высота (глубина) дерева от корня до любого листа:
- одинакова или отличается не более чем на 1 (для B-tree — по сути одинакова),
- контролируется алгоритмами вставки/удаления;
дерево не «перекашивается» в длинную цепочку (как несбалансированное бинарное дерево).

Что это даёт:

гарантированная оценка сложности поиска O(log N);
отсутствие худшего случая в виде почти линейной глубины;
стабильная производительность при росте данных.

Как B-tree сохраняет баланс:

При вставке:
- если узел переполнен (слишком много ключей), он делится (split),
- часть ключей уходит в новый узел, средний ключ поднимается выше;
- это может рекурсивно подняться до корня, увеличив высоту максимум на 1.
При удалении:
- если узел становится слишком «пустым», происходит объединение (merge) или перераспределение (rebalance) с соседями.
Алгоритмы гарантируют:
- все листья находятся на одной глубине,
- количество ключей в узлах в пределах заданного диапазона.

За счёт этого:

нет сценария, где дерево превращается в длинный «список»;
поиск, вставка, удаление остаются логарифмическими по времени, а количество уровней мало.

Почему поиск через B-tree быстрее, чем full scan

Без индекса:

Запрос вида:
```
SELECT * FROM orders WHERE id = 123;
```
может требовать проверки значительной части таблицы:
- в худшем случае — всех строк.

Это O(N) операций чтения и сравнения.

С B-tree индексом по id:

Поиск ключа в индексе:
- O(log N) шагов;
- каждый шаг — одно обращение к странице индекса в памяти/на диске.
Затем одно обращение к нужной строке таблицы (или сразу в листьях, если кластеризованный индекс).

При больших N это даёт порядок(ы) выигрыша.

Примерно:

1 млн строк:
- full scan: до 1 000 000 проверок;
- B-tree глубины 3–4: 3–4 чтения индекса + 1 чтение строки.

Почему B-tree хорош не только для "="

B-tree индекс:

хранит ключи в отсортированном виде;
это позволяет эффективно обрабатывать:
- =
- >
- <
- BETWEEN
- ORDER BY по префиксу индекса
- поиск по диапазонам.

Пример:

CREATE INDEX idx_orders_customer_date
    ON orders (customer_id, created_at);

SELECT *
FROM orders
WHERE customer_id = 10
  AND created_at BETWEEN '2025-01-01' AND '2025-01-31';

Индекс:

быстро находит первую позицию (customer_id=10, created_at>=...),
затем последовательно читает нужный диапазон.

Это работает эффективно именно благодаря отсортированности и балансировке дерева.

Что важно упомянуть на практике:

Сбалансированность:
- про одинаковую глубину и контролируемый рост дерева;
- обеспечивает стабильный O(log N) доступ.
B-tree в СУБД:
- оптимизирован под страничное хранение (узлы ~ размер страницы),
- минимизирует количество I/O.
Индексы не бесплатны:
- вставки/обновления/удаления требуют обновления дерева,
- поэтому избыток индексов замедляет запись.

Краткое сравнение с несбалансированным деревом:

Несбалансированное бинарное дерево в худшем случае:
- вырождается в список → глубина O(N) → поиск почти как полный перебор.
B-tree:
- всегда держит дерево «плотным и низким»;
- глубина растёт медленно, прогнозируемая сложность.

Кратко:

B-tree индекс ускоряет поиск за счёт:
- отсортированного хранения ключей;
- многоветвистой древовидной структуры;
- малого и контролируемого числа шагов (O(log N)).
«Сбалансированное дерево» значит:
- все пути от корня до листьев примерно одинаковой длины;
- нет деградации в длинную цепочку;
- операции поиска/вставки/удаления сохраняют логарифмическую сложность и стабильную производительность.

Вопрос 37. Что такое bitmap-индекс и когда его стоит использовать?

Таймкод: 00:42:42

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

Правильный ответ:

Bitmap-индекс — это структура данных, которая вместо хранения набора ключей в виде сбалансированного дерева (как B-tree) хранит для каждого возможного значения столбца битовую маску (bitmap), показывающую, в каких строках таблицы это значение встречается.

Идея:

Для каждого уникального значения столбца создаётся битовый вектор длиной N (число строк в таблице).
В позиции i бит = 1, если в i-й строке таблицы столбец имеет это значение; иначе 0.
Фильтрация и комбинация условий превращаются в операции над битовыми массивами (AND, OR, NOT), которые выполняются очень быстро и эффективно используют CPU.

Пример (упрощённый):

Пусть есть столбец status с возможными значениями: 'NEW', 'PAID', 'CANCELLED' и 8 строк данных.

Для 'NEW': 1 0 0 1 0 0 0 0
Для 'PAID': 0 1 0 0 1 1 0 0
Для 'CANCELLED':0 0 1 0 0 0 1 1

Запрос:

status IN ('PAID', 'CANCELLED') можно обработать как:
bitmap('PAID') OR bitmap('CANCELLED') и затем по 1-битам быстро найти строки.

Где bitmap-индексы особенно полезны

Низкая кардинальность столбца

Типичный случай:
- флаги (is_active, is_deleted),
- статусы (5–20 вариантов),
- категории (несколько вариантов),
- ограниченный набор значений.
При малом числе уникальных значений bitmap-компрессия даёт:
- очень компактное хранение,
- быстрые операции над битовыми масками.

Аналитические, read-heavy системы (DWH, OLAP)

Bitmap-индексы хорошо работают там, где:

много сложных аналитических запросов;
частые фильтры по нескольким слабо-кардинальным полям:
- region, gender, segment, status, product_group и т.п.;
нужны быстрые пересечения условий:
- например: status = 'PAID' AND region = 'EU' AND segment = 'VIP'.

Комбинация bitmap'ов:

вычисляется побитовым AND/OR/NOT;
это делается очень быстро и векторизованно.

Композитные условия

Bitmap-индексы отлично подходят, когда:

много колонок с низкой кардинальностью;
запросы используют комбинации условий по этим колонкам.

Вместо сложных B-tree-комбинаций:

СУБД берет несколько bitmap-индексов,
комбинирует их побитовыми операциями,
получает точный набор строк.

Почему это быстро:

битовые операции очень дешевые,
современные CPU хорошо оптимизированы под операции над битовыми массивами,
bitmap легко сжимаются (RLE, word-aligned compression и т.п.).

Когда bitmap-индексы применять не стоит

Ключевое ограничение — поведение при частых изменениях.

Высокая нагрузка на запись (OLTP)

Bitmap-индексы:

плохо подходят для частых INSERT/UPDATE/DELETE:
- изменение одной строки может требовать изменений в нескольких битовых векторах;
- это может приводить к блокировкам и деградации производительности.
В классических OLTP-системах (онлайн-транзакции, частые обновления) bitmap-индексы обычно не используют:
- предпочитают B-tree.

Высокая кардинальность

Если столбец имеет очень много уникальных значений (почти уникальный ID или близко к этому):

bitmap-индекс становится:
- большим по размеру,
- менее эффективным по компрессии,
- теряет свои преимущества,
в этом случае B-tree подходит лучше.

Типичные рекомендации по использованию

Использовать bitmap-индексы:
- в DWH/OLAP,
- в колонках с низкой или умеренной кардинальностью,
- при преобладании чтения над записью,
- для сложных фильтров и аналитических запросов.
Не использовать:
- в транзакционных таблицах с частыми изменениями,
- на полях с высокой кардинальностью.
В некоторых СУБД (например, Oracle):
- bitmap-индексы — стандартный инструмент для аналитических схем.
В других (PostgreSQL, MySQL) bitmap-подход применяется внутри планировщика (bitmap index scan) поверх B-tree индексов, а не как явный отдельный тип — важно это понимать:
- там мы чаще говорим про bitmap-скан как способ комбинировать обычные индексы,
- а не про явный bitmap-index, как в Oracle или специализированных системах.

Кратко:

Bitmap-индекс — индекс, основанный на битовых масках для каждого значения.
Даёт максимальный эффект:
- при низкой кардинальности,
- в аналитических (read-heavy) сценариях,
- при сложных комбинированных фильтрах.
Плохо подходит для write-heavy OLTP и высоко-кардинальных колонок.

Вопрос 38. Есть ли различия между B-tree и B+tree индексами?

Таймкод: 00:42:24

Ответ собеседника: неправильный. Говорит, что про B+tree не знает и не сталкивался, не объясняет отличия от B-tree.

Правильный ответ:

Да, различия есть. B+tree — это модификация классического B-tree, специально оптимизированная под дисковые и строчные хранилища. Большинство промышленных СУБД (InnoDB в MySQL, многие реализации в PostgreSQL, современные движки индексов) фактически используют B+tree или близкие к нему структуры, хотя часто в документации упрощённо пишут “B-tree”.

Кратко суть:

B-tree: ключи и данные могут храниться во всех узлах (и во внутренних, и в листьях).
B+tree: все реальные данные (или ссылки на строки) хранятся только в листьях, а внутренние узлы содержат только ключи-направляющие. Листья связаны в упорядоченный связный список.

Подробнее.

Структура B-tree (классическая модель):

Каждый узел содержит:
- набор ключей (k1 < k2 < ... < kn),
- дочерние указатели (для внутренних узлов),
- и, в зависимости от варианта, может содержать сами записи данных или ссылки на них.
Поиск:
- спускаемся от корня к листу, на каждом уровне выбирая нужный диапазон.
Данные могут быть распределены по всем уровням дерева (не только в листьях).

Структура B+tree:

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

Ключевые отличия и их последствия:

Где хранятся данные

B-tree:
- данные могут быть и во внутренних узлах, и в листьях.
B+tree:
- все данные (payload) — только в листьях;
- внутренние узлы — чисто навигация.

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

B+tree обеспечивает:
- более компактные внутренние узлы (только ключи и указатели),
- меньшую высоту дерева,
- быстрее навигацию по дисковым страницам.

Последовательный обход (range scan)

За счёт связного списка листьев:

В B+tree:
- после нахождения стартового ключа можно итерироваться по листьям последовательно:
  - очень эффективно для:
    - диапазонных запросов (BETWEEN),
    - сортировок по ключу,
    - сканирования префикса индекса.
В классическом B-tree:
- последовательный обход сложнее: нужно подниматься/спускаться по дереву.

Это одна из причин, почему B+tree практически всегда выигрывает для диапазонных и ORDER BY-операций, характерных для СУБД.

Высота дерева и производительность

Так как внутренние узлы B+tree не содержат данных, они:

могут содержать больше ключей на одну страницу;
дерево получается более "широким" и "низким";
меньшее число уровней → меньше I/O → стабильно быстрый поиск.

Это критично для больших таблиц:

глубина обычно 2–4 уровня даже при десятках/сотнях миллионов записей.

Поведение для точечных и диапазонных запросов

Точечный поиск (=):
- и B-tree, и B+tree дают O(log N).
- В реализациях СУБД обычно используется B+tree (даже если называется B-tree).
Диапазоны (<, >, BETWEEN, ORDER BY):
- B+tree особенно эффективен:
  - нашли первую позицию;
  - затем просто идём по связанным листьям.

Практический вывод для SQL-разработчика

На уровне прикладного SQL:

Вы указываете тип индекса как B-tree (PostgreSQL, MySQL), но под капотом это, как правило, реализация B+tree-подобной структуры.
Знание различий важно для понимания, почему:
- индексы хорошо поддерживают сортировку и диапазоны;
- планировщик может использовать index-only scan (когда все нужные данные есть в листьях индекса);
- последовательное чтение по индексу эффективно.

Например, index-only scan:

Если индекс (customer_id, created_at, amount) покрывает все поля запроса:

SELECT customer_id, created_at, amount
FROM orders
WHERE customer_id = 123
ORDER BY created_at;

СУБД может:

ответить, читая только листья индекса, вообще не лезя в основную таблицу;
это становится особенно эффективно именно благодаря свойствам B+tree.

Кратко:

B-tree и B+tree — родственные структуры; B+tree — практическая эволюция B-tree для СУБД.
Главные отличия B+tree:
- данные только в листьях,
- внутренние узлы — только ключи и ссылки,
- листья связаны в список.
Это даёт:
- компактные уровни,
- малую высоту дерева,
- быстрые range scan и index-only scan.
В реальных СУБД под "B-tree индексом" почти всегда подразумевается B+tree-реализация или её вариация.

Вопрос 39. Имеет ли значение порядок колонок в составном индексе и как он влияет на использование индекса?

Таймкод: 00:43:48

Ответ собеседника: неполный. Делится практическим кейсом, что составной индекс по трём полям не использовался при соединении только по двум, то есть интуитивно понимает зависимость от набора ключей. Однако прямо говорит, что не уверен, важен ли порядок колонок при создании индекса, и не объясняет принцип лево-префиксного использования (leftmost prefix rule).

Правильный ответ:

Да, порядок колонок в составном индексе критически важен. От него зависит, для каких условий запросов этот индекс может быть эффективно использован. Базовый принцип, который нужно знать и уметь применять: правило левого префикса (leftmost prefix rule).

Ключевая идея:

Составной индекс по (A, B, C) может эффективно использоваться для условий, которые начинают фильтрацию или сортировку с левого префикса:

(A)
(A, B)
(A, B, C)

Но не для:

только (B),
только (C),
(B, C) без A,

если конкретная СУБД не применяет дополнительных трюков (некоторые умеют частично использовать, но в общем случае рассчитывать на это нельзя).

Разберём по шагам.

Пример составного индекса

Создадим индекс:

CREATE INDEX idx_orders_cust_date_status
    ON orders (customer_id, order_date, status);

Этот индекс хранит ключи в отсортированном виде по трём полям: сначала по customer_id, внутри него по order_date, внутри — по status.

Индекс хорошо подходит для запросов, где:

есть условие по customer_id (и, возможно, по следующим полям),
результат можно прочитать в порядке этого индекса.

Когда индекс будет использоваться эффективно

Индекс (A, B, C) эффективно поддерживает запросы, где:

фильтрация или JOIN начинается с A:
- WHERE A = ?
- WHERE A = ? AND B = ?
- WHERE A = ? AND B = ? AND C = ?
- WHERE A = ? AND B BETWEEN ... AND ...
сортировка по префиксу:
- ORDER BY A, B
- ORDER BY A, B, C
- (часто без дополнительной сортировки).

Примеры для (customer_id, order_date, status):

Использует индекс:

-- 1. Фильтрация по A
SELECT *
FROM orders
WHERE customer_id = 123;

-- 2. Фильтрация по A и B
SELECT *
FROM orders
WHERE customer_id = 123
  AND order_date >= DATE '2025-01-01'
  AND order_date <  DATE '2025-02-01';

-- 3. Всё по A,B,C
SELECT *
FROM orders
WHERE customer_id = 123
  AND order_date = DATE '2025-01-10'
  AND status = 'PAID';

-- 4. Сортировка
SELECT *
FROM orders
WHERE customer_id = 123
ORDER BY order_date, status;

Во всех этих случаях порядок полей в индексе соответствует использованию «слева направо», поэтому планировщик может выполнить index range scan.

Когда индекс (A, B, C) не сработает как ожидается

Проблемные случаи:

Нет условия по A (левый столбец пропущен):

-- Часто индекс не используется или используется хуже
WHERE order_date >= ...

Есть условия только по B, C:

WHERE status = 'PAID'

или

WHERE order_date >= ... AND status = 'PAID'

Потому что:

структура индекса отсортирована сначала по A;
без фиксации/диапазона по A нельзя эффективно сузить поиск по дереву;
СУБД может:
- либо проигнорировать индекс и сделать full scan,
- либо использовать индекс частично/через bitmap/скан, но это уже не классический эффективный range scan.

Именно это часто наблюдается в практике:

есть индекс по трём полям,
запрос по двум «правым» полям,
индекс в плане не используется или используется неэффективно.

Как выбирать порядок колонок в индексе

Практический подход:

Левее ставим то, что:
- чаще всего используется в фильтрах и join’ах (равенство, точный матч),
- максимально селективно (сильно сужает выборку),
- задаёт основные группы.

Типичные принципы:

Сначала ключи соединений/фильтров (=),
затем поля для диапазонов (BETWEEN, >, <),
затем — для сортировки/вторичных условий.

Примеры:

Лог запросов:

CREATE INDEX idx_logs_ts_user
    ON logs (event_time, user_id);

Часто подходят запросы вида:

WHERE event_time BETWEEN ...
WHERE event_time BETWEEN ... AND ... AND user_id = ...
ORDER BY event_time
Заказы:

CREATE INDEX idx_orders_customer_date
    ON orders (customer_id, created_at);

Потому что почти все запросы:

фильтруют по customer_id,
берут диапазон по created_at.

Если бы часто фильтровали по дате, а customer_id был второстепенен — порядок был бы другим.

Связка с покрывающими индексами

Составной индекс может быть покрывающим (covering index):

если индекс содержит все поля, которые нужны в SELECT/WHERE/ORDER BY,
СУБД может выполнить index-only scan (не читать саму таблицу).

Выбор порядка также важен, чтобы:

и фильтрация эффективно шла по префиксу,
и остальные поля были доступны как включенные (INCLUDE / ключи индекса).

Пример (SQL Server / PostgreSQL 15+ с INCLUDE):

CREATE INDEX idx_orders_customer_date
    ON orders (customer_id, created_at)
    INCLUDE (amount, status);

Антипаттерны

«Сделаем один большой индекс (A, B, C, D, E) и он всё решит»:
- нет, если запросы не используют левый префикс, индекс будет мало полезен;
- каждый лишний индекс — это издержки на INSERT/UPDATE/DELETE.
Игнорировать порядок:
- индекс (status, created_at) и (created_at, status) — это разные индексы с разным профилем использования.

Краткий пример для запоминания:

Есть индекс:

CREATE INDEX idx_example ON t(a, b, c);

Эффективно:

WHERE a = ...
WHERE a = ... AND b = ...
WHERE a = ... AND b = ... AND c = ...
WHERE a = ... AND b BETWEEN ...
ORDER BY a, b, c (с подходящим WHERE)

Проблемно / частично:

WHERE b = ...
WHERE c = ...
WHERE b = ... AND c = ...
ORDER BY b, c без условия по a.

Кратко:

Да, порядок колонок в составном индексе принципиально важен.
Работает правило левого префикса:
- составной индекс (A, B, C) полноценно помогает только для условий, начинающихся с A, затем A,B и т.д.
Грамотный выбор порядка:
- основан на реальных запросах, селективности и сценариях JOIN/WHERE/ORDER BY.
Игнорирование этого приводит к ситуации, которую кандидат наблюдал в практике: индекс есть, но планировщик им почти не пользуется.

Вопрос 40. Почему нельзя повесить индексы на все поля и все возможные комбинации?

Таймкод: 00:44:32

Ответ собеседника: правильный. Объясняет, что избыточные индексы замедляют вставку и обновление данных, поскольку при изменении строк индексы нужно обновлять, что создаёт накладные расходы.

Правильный ответ:

Идея «давайте проиндексируем всё» кажется привлекательной только до первого реального продакшена. На практике это ведет к деградации производительности записи, росту объема хранения, сложностям с сопровождением и иногда к парадоксальному замедлению чтения.

Ключевые причины, почему нельзя и не нужно индексировать всё подряд.

Каждое изменение строки требует обновления всех индексов

При выполнении:

INSERT:
- строка добавляется в таблицу,
- для каждого индекса:
  - добавляется запись в индексную структуру;
UPDATE:
- если изменяется колонка, участвующая в индексе:
  - старая запись удаляется из индекса,
  - новая добавляется;
- даже изменение неиндексного поля может затрагивать покрывающие индексы или include-колонки;
DELETE:
- запись удаляется и из таблицы, и из всех индексов.

Чем больше индексов:

тем больше операций над B-tree / B+tree структурами;
тем больше I/O, перезаписей страниц, блокировок;
тем медленнее любые DML-операции (INSERT/UPDATE/DELETE, bulk load, ETL).

В высоконагруженных системах это критично.

Индексы занимают место и память

Каждый индекс:

физически хранится на диске;
загружается страницами в RAM (buffer cache);
требует обслуживания.

Если:

«повесить всё на всё», то:
- размер индексов может превысить размер таблиц;
- полезные данные вытесняются из кеша индексами;
- общее число страниц, с которыми работает система, растет → хуже cache hit ratio → больше реального I/O.

В колоночных и аналитических системах это особенно чувствительно:

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

Конкуренция индексов и неопределенность планов

Слишком много индексов:

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

Меньшее количество хорошо подобранных индексов:

даёт более предсказуемые планы;
упрощает диагностику.

Большинство индексов никогда не используется

На практике:

типичный набор рабочих запросов покрывает ограниченный список полей и комбинаций;
индексы «про запас»:
- не попадают в планы,
- но продолжают отжирать ресурсы на обновление.

Это чистый минус:

нет выигрыша в чтении,
есть потери на записи и хранении.

Составные индексы и взрыв комбинаций

Если попытаться проиндексировать «все комбинации полей»:

количество потенциальных индексов растёт комбинаторно;
например, для 6 колонок все пары — это уже 15 индексов, все тройки — ещё 20, и т.д.;
это не масштабируется и разрушает производительность.

Правильный путь:

проектировать составные индексы под реальные паттерны запросов:
- WHERE,
- JOIN,
- ORDER BY,
- GROUP BY;
использовать правило левого префикса;
минимизировать пересечения и дублирующие индексы.

Индекс может вредить чтению

Да, иногда лишний индекс влияет и на чтение:

из-за избыточных index-only scan, когда планировщик выбирает «не тот» индекс;
из-за большого числа уровней и фрагментации;
из-за ухудшения locality (особенно для кластеризованных индексов и вторичных по ним).

Хотя чаще вред опосредован через DML и I/O, а не прямой.

Правильный подход к индексации

Зрелая стратегия индексации:

Исходить от запросов, а не от схемы:
- анализировать top-N самых тяжёлых запросов (EXPLAIN / ANALYZE, профайлеры);
- под них проектировать индексы.
Обязательно индексируются:
- первичные ключи (PK),
- внешние ключи (FK) — почти всегда,
- уникальные бизнес-ключи.
Добавлять индексы:
- под частые фильтры и JOIN;
- под важные ORDER BY / GROUP BY.
Использовать:
- покрывающие индексы (INCLUDE) там, где это даёт выгоду;
- частичные индексы (WHERE ...) для активного подмножества данных;
- специализированные индексы (GIN/GiST/bitmap/fulltext) под конкретные задачи.
Периодически:
- ревизия неиспользуемых индексов (по статистике СУБД);
- удаление мёртвых/дублирующих индексов.

Пример из практики

Плохой вариант:

-- Антипаттерн
CREATE INDEX idx_users_email        ON users(email);
CREATE INDEX idx_users_email_name   ON users(email, name);
CREATE INDEX idx_users_name_email   ON users(name, email);
CREATE INDEX idx_users_is_active    ON users(is_active);
CREATE INDEX idx_users_all          ON users(email, name, is_active, created_at);

Часть этих индексов:

пересекаются по функционалу,
никогда не используется оптимизатором,
дублируют друг друга по левому префиксу.

Лучший вариант (под реальные запросы):

-- Основной поиск по email
CREATE UNIQUE INDEX uq_users_email ON users(email);

-- Частый запрос: активные пользователи по created_at
CREATE INDEX idx_users_active_created
    ON users(is_active, created_at);

Кратко:

Индексы — мощный, но дорогой инструмент.
«Повесить на всё» нельзя, потому что:
- сильно замедлит INSERT/UPDATE/DELETE,
- взорвет размер базы,
- усложнит планы,
- приведет к избыточности и деградации.
Индексация должна быть осмысленной, основанной на реальных запросах и профилировании.

Вопрос 41. Почему большое количество индексов замедляет операции INSERT и UPDATE, и какие ещё есть минусы?

Таймкод: 00:45:21

Ответ собеседника: правильный. Отмечает, что при вставке и обновлении записи нужно обновлять все соответствующие индексы, из-за чего DML-операции замедляются, и что индексы дополнительно занимают место.

Правильный ответ:

Индексы — это не только ускорение чтения, но и обязательные накладные расходы на запись, хранение и сопровождение. Чем больше индексов, тем дороже каждая модификация данных и сложнее жизнь эксплуатационной части системы.

Разберём детально, что именно происходит и к каким последствиям это приводит.

Основная причина замедления DML

При каждой операции изменения данных СУБД должна поддерживать согласованность всех индексных структур:

INSERT

Для каждой новой строки:

вставить строку в таблицу;
для каждого индекса:
- вычислить ключ (по соответствующим колонкам / выражениям),
- вставить запись в B-tree/B+tree/bitmap/GIN/GiST и т.п.,
- при необходимости:
  - разделить узлы (split),
  - обновить метаданные.

Чем больше индексов:

тем больше операций вставки в разные структуры;
тем больше I/O и блокировок;
тем медленнее массовые загрузки (ETL, batch).

UPDATE

Если обновляется поле, входящее в индекс:

старая запись должна быть удалена из индекса;
новая — добавлена (по новому значению ключа).

Это фактически:

DELETE + INSERT внутри индексной структуры.

Даже если обновляемое поле не индексное:

некоторые типы индексов (покрывающие, включающие поля) или триггерная логика могут всё равно затрагиваться;
плюс СУБД должна проверять ограничения (UNIQUE, FK), которые тоже опираются на индексы.

DELETE

При удалении строки:

запись удаляется из таблицы;
для каждого индекса:
- находят и удаляют соответствующую запись;
- это тоже операция в дереве/bitmap/GIN.

Итого:

каждый дополнительный индекс = дополнительные операции при каждом INSERT/UPDATE/DELETE;
в write-heavy системах это может стать доминирующим фактором деградации производительности.

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

Рост объема хранения

Каждый индекс — физическая структура на диске.
При множестве индексов:
- суммарный размер индексов может многократно превышать размер таблицы;
- это:
  - увеличивает требования к диску,
  - увеличивает объём backup’ов,
  - замедляет репликацию.

Ухудшение работы с памятью и кешем

Индексы занимают место в buffer cache.
Если индексов слишком много:
- они конкурируют между собой и с таблицами;
- полезные страницы чаще вытесняются;
- увеличивается доля реальных дисковых обращений;
- падает общая производительность чтения.

Долгие операции обслуживания

VACUUM, ANALYZE, REINDEX, CLUSTER, миграции, rebuild:
- должны обрабатывать каждый индекс;
- чем их больше, тем дольше идут сервисные операции,
- выше влияние на окна обслуживания и блокировки.
Массовые DDL/ETL (перегенерация таблиц, SCD-обновления):
- замедляются из-за необходимости перестраивать кучу индексов.

Сложность планирования и диагностики

Чем больше индексов:
- тем больше вариантов выбора у оптимизатора;
- выше риск, что будет выбран «почти подходящий, но не лучший» индекс;
- планы запросов становятся менее предсказуемыми.
Анализ EXPLAIN-планов усложняется:
- нужно понимать, зачем существует каждый индекс,
- нет ли дублирующих и конфликтующих вариантов.

Риск логических ошибок и пересечений

Индексы могут дублировать друг друга:
- например: (a), (a, b), (a, b, c);
- многие из них избыточны — один составной индекс уже покрывает более простые кейсы.
Непродуманные индексы под «редкие» запросы:
- в сумме дают серьёзные накладные расходы,
- но почти не используются.

Влияние на блокировки и конкурентность

При интенсивной записи обновление индексов:
- повышает вероятность блокировок страниц индексов;
- может приводить к локальным «горячим точкам» (hot pages), особенно для монотонно растущих ключей при неправильной конфигурации;
- это влияет и на latency, и на throughput.

Практические рекомендации

Индексы должны быть:
- выведены из реальных запросов (WHERE, JOIN, ORDER BY, GROUP BY);
- обоснованы частотой и критичностью этих запросов.
Обязательно:
- индексировать PK и FK;
- добавлять уникальные индексы для бизнес-ключей;
- точечно индексировать горячие фильтры и JOIN-колонки.
Избегать:
- индексирования каждого поля на всякий случай;
- создания множества похожих составных индексов;
- индексов под разовые, не критичные запросы.
Регулярно:
- проверять статистику использования индексов (pg_stat_user_indexes и аналоги);
- удалять неиспользуемые и дублирующие индексы.

Кратко:

Большое количество индексов замедляет INSERT/UPDATE/DELETE, потому что каждое изменение строки требует обновления всех соответствующих индексных структур.
Плюс:
- рост размера базы,
- ухудшение работы кеша,
- удлинение сервисных операций,
- усложнение планирования и диагностики.
Эффективная индексация — это точечный инструмент под реальные паттерны использования данных, а не "поставить индекс на всё".

Вопрос 42. Почему большое число индексов может замедлить SELECT-запросы?

Таймкод: 00:46:21

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

Правильный ответ:

Интуитивно кажется: «чем больше индексов, тем быстрее SELECT». На практике избыточное количество индексов может ухудшать и чтение, не только запись. Причины в работе оптимизатора, в поведении кеша, в структуре планов и в реальной стоимости доступа к данным.

Основные причины, почему много индексов может замедлять SELECT:

Усложнение работы оптимизатора (plan search space)

Оптимизатор при построении плана:

рассматривает варианты:
- какой индекс использовать для каждой таблицы;
- как комбинировать индексы (bitmap-сканы, index merge и т.д.);
- как строить join-стратегию (nested loop / hash / merge) с учетом доступных индексов;
чем больше индексов:
- тем больше комбинаций,
- тем выше стоимость подбора плана.

Последствия:

рост времени планирования (особенно на сложных запросах);
повышенный риск выбора «почти оптимального», но не лучшего плана:
- особенно при неидеальной статистике;
- особенно если есть похожие/перекрывающиеся индексы.

Ошибочные оценки селективности и выбор «не того» индекса

Много индексов по похожим полям:

idx_orders_customer
idx_orders_customer_status
idx_orders_status
idx_orders_customer_date
...

Оптимизатор:

опирается на статистику (которая может быть устаревшей или усреднённой),
должен решить:
- какой индекс даст меньше строк (лучшую селективность),
- какой план в сумме дешевле.

При большом числе индексов:

растет вероятность, что оптимизатор:
- переоценит селективность одного индекса,
- недооценит другого;
в итоге:
- может выбрать индекс, который приводит к большому числу random I/O,
- вместо более подходящего range scan или seq scan.

Пример типичного эффекта:

есть индекс только по статусу (низкая селективность, почти все строки),
и составной индекс по (customer_id, created_at);
если статистика плохая, оптимизатор может взять индекс по статусу и прочитать почти всё через index scan вместо более разумного сканирования по диапазону/клиенту.

Чем больше «шумных» индексов, тем чаще такие случаи.

Конкуренция за кеш (buffer cache / page cache)

Индексы:

занимают место в памяти наравне с данными.

Если индексов много:

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

Результат:

запросы чаще упираются в физические чтения с диска;
падает общая производительность даже тех SELECT, которые используют правильные индексы:
- нужные страницы таблицы/индексов к моменту запроса уже выгружены менее полезными структурами.

Неоптимальные планы из-за лишних вариантов

Слишком много индексов даёт оптимизатору «ложные свободы»:

он может:
- выбрать index-only scan по «широкому» индексу, который на самом деле:
  - большой,
  - плохо лежит в кеше;
- вместо простого seq scan или более подходящего индекса.
может попытаться комбинировать несколько индексов (bitmap merge), когда один правильный составной индекс решил бы задачу лучше.

Это особенно критично:

при сложных запросах с множеством условий;
при неточных или редких паттернах доступа.

Избыточные составные индексы и дублирование

Пример:

CREATE INDEX idx_a     ON t(a);
CREATE INDEX idx_a_b   ON t(a, b);
CREATE INDEX idx_a_b_c ON t(a, b, c);

Многие планы:

могут быть построены эффективно только на одном из этих индексов;
остальные:
- дублируют функциональность,
- создают нагрузку на запись,
- создают лишние варианты для оптимизатора.

Если оптимизатор выберет (a) вместо (a, b) там, где нужно много фильтраций и сортировок, запрос придётся дороже.

Псевдо-ускорение: index scan против seq scan

Иногда:

оптимизатор выбирает index scan по неудачному индексу:
- множество мелких случайных чтений (random I/O);
вместо:
- последовательного чтения (seq scan), которое могло бы быть быстрее на большом объёме данных.

Чем больше «подходящих внешне» индексов:

тем сложнее оптимизатору понять, что seq scan в данном случае реально дешевле;
тем выше риск медленных "индексных" планов, которые выглядят «умно», но работают хуже.

Практический вывод: избыток индексов увеличивает вероятность неверного выбора.

Практические рекомендации

Чтобы индексы помогали SELECT, а не мешали:

Проектировать индексы от запросов, а не от полей:
- анализировать реальные WHERE/JOIN/ORDER BY;
- проектировать составные индексы под частые паттерны.
Избегать:
- дублирующих индексов (например, (a, b) и (a));
- индексов под разовые или малозначимые запросы;
- индексов по полям с низкой селективностью, если они не используются в комбинации.
Следить за статистикой:
- регулярно ANALYZE/auto-analyze;
- чтобы оптимизатор имел адекватные оценки селективности.
Профилировать:
- EXPLAIN/EXPLAIN ANALYZE;
- смотреть, какие индексы реально используются;
- удалять индексы, которые стабильно не используются.

Кратко:

Да, большое число индексов может замедлять SELECT:
- усложняя выбор плана,
- провоцируя неверные решения оптимизатора,
- размывая кеш и увеличивая реальные I/O.
Лишние индексы — это не только проблема записи, но и фактор нестабильной и неоптимальной работы чтения.
Эффективная стратегия — небольшое число хорошо подобранных индексов, строго под реальные и критичные запросы.

Вопрос 43. На какие аспекты плана запроса стоит обращать внимание и какие существуют методы сканирования и соединения?

Таймкод: 00:47:17

Ответ собеседника: правильный. Подчеркивает важность способа доступа к данным. Называет full table scan и index scan, с подсказки вспоминает index only scan. Упоминает методы соединения: nested loop, merge join и т.п. Демонстрирует базовое понимание структуры плана и основных типов операций.

Правильный ответ:

Умение читать планы выполнения запросов — обязательный навык при работе с высоконагруженными системами, DWH, витринами и сложной бизнес-логикой. Важно понимать:

какие операции реально выполняет СУБД,
какие структуры (индексы, сортировки, join-стратегии) она использует,
где «бутылочные горлышки».

Разберём ключевые аспекты плана и основные типы сканирования и соединения.

На что смотреть в плане запроса

При анализе EXPLAIN / EXPLAIN ANALYZE (PostgreSQL, MySQL, SQL Server, Oracle и т.п.) важно обращать внимание на:

Тип доступа к данным:
- Seq Scan / Full Table Scan,
- Index Scan,
- Index Only Scan,
- Bitmap Index Scan / Bitmap Heap Scan,
- и их аналоги.
Типы соединений:
- Nested Loop,
- Hash Join,
- Merge Join.
Оценки cardinality:
- сколько строк ожидает получить планировщик на каждом шаге (rows, estimates);
- насколько эти оценки близки к факту (EXPLAIN ANALYZE).
Стоимость (cost):
- относительные оценки (cost=...), показывающие, где «дорого».
Сортировки:
- есть ли Sort-операторы, сколько строк они сортируют;
- можно ли их избежать индексами.
Фильтры:
- где реально применяются условия WHERE/HAVING;
- нет ли лишнего чтения с поздней фильтрацией.
Цепочки операций:
- нет ли лишних nested loop по большим наборам,
- нет ли повторных сканов одной и той же таблицы.

Основные методы сканирования (access methods)

Sequential Scan / Full Table Scan

Читает всю таблицу целиком.
Эффективен:
- для маленьких таблиц;
- когда селективность низкая (условия выбирают большую часть строк);
- когда нет подходящих индексов.
Если вы видите Seq Scan на большой таблице, а ожидали точечный поиск:
- это сигнал проверить индексы и условия WHERE.

Index Scan

Использует индекс (обычно B-tree/B+tree) для поиска подходящих записей.
Работает:
- для точечных (=, IN) и диапазонных (BETWEEN, <, >) условий,
- когда есть подходящий (и селективный) индекс.
Шаги:
- найти ключи в индексе (логарифмически),
- по ссылкам прочитать строки из таблицы.
Хорош для:
- выборки малого процента строк;
- запросов с хорошей селективностью фильтра.

Index Only Scan

Оптимизация: СУБД читает только индекс, не трогая таблицу.
Возможен, если:
- все нужные для запроса поля покрываются индексом (ключи + INCLUDE/leaf data);
- видимость строк можно подтвердить через visibility map (PostgreSQL) или аналог.
Очень эффективен для:
- частых чтений горячих данных;
- витрин, где запросы используют покрывающие индексы.

Пример (PostgreSQL):

CREATE INDEX idx_orders_cover
    ON orders (customer_id, created_at, amount);

SELECT customer_id, created_at, amount
FROM orders
WHERE customer_id = 123
ORDER BY created_at;

Планировщик может выполнить Index Only Scan по idx_orders_cover.

Bitmap Index Scan / Bitmap Heap Scan

Используется, когда:
- условий несколько,
- индексы на каждое поле отдельно,
- нужно объединить результаты (AND/OR).
Механика:
- Bitmap Index Scan по каждому индексу → битовые карты строк,
- объединение битмапов,
- Bitmap Heap Scan для чтения только отмеченных строк.
Хороший компромисс:
- между полным сканом и одиночным index scan.
Типичен для аналитических запросов с несколькими фильтрами по индексированным полям.

Другие варианты

В спец-движках (column store, MPP) могут быть свои типы:
- скан по партициям,
- vectorized scan,
- и т.п.
Принцип тот же: минимизировать лишнее чтение.

Основные методы соединения (join methods)

Nested Loop Join

Для каждой строки из внешней (outer) таблицы ищем подходящие строки во внутренней (inner).
Если на колонках inner есть индекс:
- для каждой строки outer делаем быстрый index lookup;
Эффективен:
- когда:
  - одна таблица маленькая,
  - другая имеет индекс по join-ключу,
  - либо внешний результат уже сильно отфильтрован;
Опасен:
- если обе стороны большие и нет индекса — получится квадратичная по сути операция.

Признак проблемы:

большой nested loop по 100k × 100k без индексов → надо чинить.

Hash Join

Строится хеш-таблица по ключу для одной стороны (обычно меньшей).
Затем:
- вторая сторона сканируется,
- по ключу ищется в хеше.
Эффективен:
- для больших объёмов;
- когда нет подходящих индексов;
- для равенств (A.key = B.key).
Требует память (для hash table), но хорошо масштабируется.

Часто лучший выбор для DWH и тяжёлых JOIN’ов.

Merge Join

Обе таблицы должны быть отсортированы по join-ключу:
- либо уже в нужном порядке (по индексу),
- либо предварительно отсортированы.
Затем:
- происходит «слияние» двух отсортированных потоков (как merge в merge sort).
Эффективен:
- для больших объёмов,
- когда есть индексы или сортировка по join-ключу,
- когда нужна комбинация равенств и диапазонов.
Может быть очень быстрым при правильных индексах.

Что важно уметь видеть и объяснять

При чтении плана:

Понимать, почему выбран именно этот метод:
- Nested Loop с Index Scan для малых выборок;
- Hash Join для больших таблиц без индексов;
- Merge Join, когда обе стороны уже отсортированы подходящими индексами.
Уметь сказать:
- «этот Seq Scan здесь нормален» (малая таблица / низкая селективность),
- или «здесь Seq Scan по 100M строк — плохо, нужен индекс/переписывание запроса».
Смотреть, нет ли:
- nested loop по большим наборам без индекса,
- сортировок по миллионам строк, которые можно было бы заменить index scan’ом,
- избыточных bitmap операций из-за неудачной индексации.

Пример короткого анализа (PostgreSQL-style):

Видим:
- Seq Scan on big_table (cost 0..100000) Filter: ...
- → Нет индекса по полю фильтра, читаем все — надо добавить индекс, если запрос частый.
Видим:
- Nested Loop
  - -> Index Scan using idx_small on small_table
  - -> Index Scan using idx_big_join on big_table
- → Нормальный вариант: маленькая таблица по индексу, по ключам в большую.
Видим:
- Hash Join
  - -> Seq Scan on large_a
  - -> Seq Scan on large_b
- → Если частый запрос и join по устойчивому ключу — возможно, стоит добавить индексы для других планов или принять hash join как норму.

Кратко:

Ключевые аспекты плана:
- типы сканов (seq, index, index-only, bitmap),
- методы join (nested loop, hash, merge),
- оценки строк и стоимости,
- сортировки и фильтры.
Основные методы:
- сканирование: full scan, index scan, index only scan, bitmap scan;
- соединения: nested loop, hash join, merge join.
Уверенное понимание этих элементов позволяет:
- объяснить поведение запроса,
- оптимизировать индексы и SQL,
- предсказать влияние изменений на производительность.

Вопрос 44. Какова роль статистики в работе индексов и оптимизатора запросов?

Таймкод: 00:48:29

Ответ собеседника: правильный. Говорит, что оптимизатор использует статистику по таблицам и индексам (число строк, распределение и типы данных и т.п.) для выбора плана, и что статистику нужно обновлять после значительных изменений объёма данных, иначе план может стать неоптимальным.

Правильный ответ:

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

Что такое статистика в контексте СУБД

Обычно включает:

Общее количество строк в таблице.
Число страниц (блоков) на диске.
Для каждого индексированного и/или значимого столбца:
- количество различных значений (NDV — number of distinct values),
- оценка селективности,
- распределение значений (гистограммы),
- доля NULL.
Статистика по индексам:
- глубина дерева,
- распределение ключей,
- фрагментация.

Эти данные хранятся в служебных системных таблицах (например, pg_statistic в PostgreSQL) и обновляются:

автоматически (auto-analyze, auto update stats);
или вручную (ANALYZE, UPDATE STATISTICS, DBMS_STATS и аналоги).

Как оптимизатор использует статистику

Оптимизатор — это компонент, который выбирает план выполнения запроса. Для этого он оценивает стоимость разных вариантов:

использовать ли индекс или сделать full scan;
каким join-алгоритмом соединять таблицы;
в каком порядке соединять несколько таблиц;
нужна ли сортировка или индекс уже даёт нужный порядок;
стоит ли применять hash join, merge join или nested loop.

Ключевые решения основаны на статистике:

Выбор между Index Scan и Full Scan

Допустим:

SELECT * FROM orders WHERE status = 'PAID';

Оптимизатор:

смотрит селективность status:
- если 'PAID' — 0.1% строк → индекс выгоден;
- если 'PAID' — 90% строк → индекс может быть дороже, чем seq scan (много random I/O);
без правильной статистики:
- может ошибочно считать, что фильтр очень селективен,
- выбрать Index Scan,
- и фактически сделать кучу случайных чтений вместо одного последовательного.

Выбор порядка JOIN

Для запроса:

SELECT *
FROM a
JOIN b ON ...
JOIN c ON ...
WHERE ...

Оптимизатор:

оценивает, сколько строк будет после каждого шага:
- если статистика верная:
  - выберет порядок, минимизирующий промежуточные наборы;
- если статистика старая/кривая:
  - может предположить, что таблица маленькая (10k строк), а в реальности там 10M,
  - выберет nested loop, который взорвётся по времени;
именно статистика по кардинальности (rows) определяет:
- какой join-алгоритм и порядок брать.

Выбор join-алгоритма (nested loop / hash / merge)

Hash Join выгоден, если:
- хотя бы одна сторона относительно небольшая,
- есть точное понимание размера.
Merge Join выгоден при:
- отсортированных данных,
- разумном размере.
Nested Loop выгоден, если:
- одна сторона очень маленькая,
- по другой есть хороший индекс.
Без статистики:
- оптимизатор может считать большую таблицу маленькой и выбрать nested loop → шлейф запросов.

Использование композитных и частичных индексов

При наличии составного индекса:

CREATE INDEX idx_orders_cust_date
    ON orders (customer_id, created_at);

Оптимизатор:

использует статистику по (customer_id) и (customer_id, created_at):
- чтобы понять, насколько узким будет диапазон по индексу;
- принять решение, стоит ли идти по индексу или делать seq scan.

Если статистика по комбинациям отсутствует или грубая:

план может быть неоптимальным.

Чем опасна устаревшая или некорректная статистика

Неверный выбор плана

Типичные симптомы:

Вчера запрос работал быстро, сегодня — медленно,
- хотя индексы и код не менялись;
План «переехал» с hash join на nested loop или с seq scan на «токсичный» index scan.

Часто причина:

таблица сильно выросла/уменьшилась;
распределение значений изменилось (например, большинство строк теперь со status='PAID');
статистика не была обновлена.

Ошибки в оценке селективности

Без точных гистограмм:

оптимизатор может считать, что:
- значения распределены равномерно,
- хотя в реальности есть skew (перекос):
  - один статус занимает 95% строк.
В результате:
- берутся планы, эффективные на «воображаемых» данных, но не на реальных.

Проблемы с партиционированием

Для партиционированных таблиц:

статистика по партициям влияет на:
- pruning (отсечение лишних партиций),
- выбор стратегии сканирования;
устаревшая статистика → чтение лишних партиций → рост времени.

Практические рекомендации

Регулярно обновлять статистику

В продакшене:
- полагаться на автообновление (auto-analyze), но контролировать пороги;
- при массовых загрузках/перестройках:
  - вручную запускать ANALYZE / UPDATE STATISTICS / DBMS_STATS.

Следить за планами «подозрительных» запросов

Если запрос внезапно стал медленным:
- первым делом смотреть EXPLAIN ANALYZE;
- сравнивать estimates vs actual rows;
- если расхождения большие — проблема в статистике.

Использовать расширенную статистику (где доступно)

PostgreSQL:
- extended statistics (корреляции, зависимости между полями);
Это помогает:
- лучше оценивать селективность по нескольким условиям,
- уменьшить ошибки оптимизатора.

Чистить «мёртвые» индексы

Лишние индексы:
- усложняют планирование,
- увеличивают требования к статистике.
Регулярный аудит индексов — часть гигиены.

Кратко:

Статистика — ключевой источник правды для оптимизатора.
Она определяет:
- использовать ли индекс,
- как соединять таблицы,
- в каком порядке и каким методом.
Устаревшая или неправильная статистика приводит к:
- неэффективным планам,
- неожиданным провалам в производительности.
Зрелый подход:
- следить за актуальностью статистики,
- проверять расхождения estimated vs actual,
- использовать статистику как инструмент, а не «магическую чёрную коробку».

Вопрос 1. Кратко рассказать о своем опыте и текущих проектах в области разработки и работы с данными.​

Вопрос 2. Как устроена текущая организация работы: состав команды, постановка задач, взаимодействие с заказчиком и полный жизненный цикл задач?​

Вопрос 3. Как организован процесс релизов и развёртывания изменений?​

Вопрос 4. Каков уровень владения Git и какие команды используются в повседневной работе?​

Вопрос 5. С какими ETL-инструментами есть опыт работы и как они используются в реальных пайплайнах?​

Вопрос 6. Какой опыт работы с Airflow: разработка DAG’ов или только мониторинг?​

Вопрос 7. Какие типы операторов Airflow можно назвать и в каких случаях они используются?​

Вопрос 8. Зачем в Airflow используются сенсоры?​

Вопрос 9. Как связать несколько DAG в Airflow, чтобы запускать их последовательно?​

Вопрос 10. Что такое витринное хранилище данных (DWH) и data lake, в чём их отличие и как выглядит общая архитектура слоёв хранилища?​

Вопрос 11. Кратко описать схемы «звезда», «снежинка» и подход Data Vault.​

Вопрос 12. Кратко описать схемы «звезда», «снежинка» и Data Vault в контексте моделирования хранилища.​

Вопрос 13. Что такое нормальные формы и какие из них наиболее важны при проектировании?​

Вопрос 14. Дать определение первичного ключа.​

Вопрос 15. Дать определение суррогатного ключа и пояснить на примере, для чего он используется.​

Вопрос 16. Объяснить, зачем нужны колоночные базы данных и в чём их преимущества.​

Вопрос 17. Назвать недостатки колоночных баз данных.​

Вопрос 18. Что такое исторические таблицы и как обычно реализуется историчность (SCD)?​

Вопрос 19. Как определить инкрементальные данные для загрузки в хранилище?​

Вопрос 20. Объяснить подход к отслеживанию изменений бизнес-атрибутов внутри периода действия записи.​

Вопрос 21. Что такое NULL в базе данных и как с ним работать в SQL?​

Вопрос 22. Соединяются ли записи с NULL в ключевых полях при соединении таблиц?​

Вопрос 23. Какие виды соединений таблиц существуют?​

Вопрос 24. Определить минимальное и максимальное число строк при LEFT JOIN таблиц с 100 и 10 записями.​

Вопрос 25. Определить минимальное и максимальное число строк при INNER JOIN таблиц с 100 и 10 записями.​

Вопрос 26. Определить минимальное и максимальное число строк при FULL OUTER JOIN и при CROSS JOIN.​

Вопрос 27. Какие агрегатные функции используются и для чего нужна HAVING?​

Вопрос 28. Можно ли в одном запросе использовать несколько агрегатных функций и можно ли делать GROUP BY по всем выбранным полям без агрегатов?​

Вопрос 29. Как посчитать сумму значений в одном столбце таблицы?​

Вопрос 30. Как получить значения из первой таблицы, которых нет во второй?​

Вопрос 31. Как удалить дубликаты из таблицы, оставив по одному экземпляру каждого значения?​

Вопрос 32. Как можно сгенерировать последовательность чисел в SQL для решения задачи?​

Вопрос 33. Каков опыт использования CTE и для чего они применяются?​

Вопрос 34. Как посчитать сумму и нарастающий итог по группам с помощью оконных функций?​

Вопрос 35. Что такое индексы, какие их виды существуют и зачем они нужны?​

Вопрос 36. Почему B-tree индекс ускоряет поиск и что значит сбалансированное дерево?​

Вопрос 37. Что такое bitmap-индекс и когда его стоит использовать?​

Вопрос 38. Есть ли различия между B-tree и B+tree индексами?​

Вопрос 39. Имеет ли значение порядок колонок в составном индексе и как он влияет на использование индекса?​

Вопрос 40. Почему нельзя повесить индексы на все поля и все возможные комбинации?​

Вопрос 41. Почему большое количество индексов замедляет операции INSERT и UPDATE, и какие ещё есть минусы?​

Вопрос 42. Почему большое число индексов может замедлить SELECT-запросы?​

Вопрос 43. На какие аспекты плана запроса стоит обращать внимание и какие существуют методы сканирования и соединения?​

Вопрос 44. Какова роль статистики в работе индексов и оптимизатора запросов?​

Вопрос 1. Кратко рассказать о своем опыте и текущих проектах в области разработки и работы с данными.

Вопрос 2. Как устроена текущая организация работы: состав команды, постановка задач, взаимодействие с заказчиком и полный жизненный цикл задач?

Вопрос 3. Как организован процесс релизов и развёртывания изменений?

Вопрос 4. Каков уровень владения Git и какие команды используются в повседневной работе?

Вопрос 5. С какими ETL-инструментами есть опыт работы и как они используются в реальных пайплайнах?

Вопрос 6. Какой опыт работы с Airflow: разработка DAG’ов или только мониторинг?

Вопрос 7. Какие типы операторов Airflow можно назвать и в каких случаях они используются?

Вопрос 8. Зачем в Airflow используются сенсоры?

Вопрос 9. Как связать несколько DAG в Airflow, чтобы запускать их последовательно?

Вопрос 10. Что такое витринное хранилище данных (DWH) и data lake, в чём их отличие и как выглядит общая архитектура слоёв хранилища?

Вопрос 11. Кратко описать схемы «звезда», «снежинка» и подход Data Vault.

Вопрос 12. Кратко описать схемы «звезда», «снежинка» и Data Vault в контексте моделирования хранилища.

Вопрос 13. Что такое нормальные формы и какие из них наиболее важны при проектировании?

Вопрос 14. Дать определение первичного ключа.

Вопрос 15. Дать определение суррогатного ключа и пояснить на примере, для чего он используется.

Вопрос 16. Объяснить, зачем нужны колоночные базы данных и в чём их преимущества.

Вопрос 17. Назвать недостатки колоночных баз данных.

Вопрос 18. Что такое исторические таблицы и как обычно реализуется историчность (SCD)?

Вопрос 19. Как определить инкрементальные данные для загрузки в хранилище?

Вопрос 20. Объяснить подход к отслеживанию изменений бизнес-атрибутов внутри периода действия записи.

Вопрос 21. Что такое NULL в базе данных и как с ним работать в SQL?

Вопрос 22. Соединяются ли записи с NULL в ключевых полях при соединении таблиц?

Вопрос 23. Какие виды соединений таблиц существуют?

Вопрос 24. Определить минимальное и максимальное число строк при LEFT JOIN таблиц с 100 и 10 записями.

Вопрос 25. Определить минимальное и максимальное число строк при INNER JOIN таблиц с 100 и 10 записями.

Вопрос 26. Определить минимальное и максимальное число строк при FULL OUTER JOIN и при CROSS JOIN.

Вопрос 27. Какие агрегатные функции используются и для чего нужна HAVING?

Вопрос 28. Можно ли в одном запросе использовать несколько агрегатных функций и можно ли делать GROUP BY по всем выбранным полям без агрегатов?

Вопрос 29. Как посчитать сумму значений в одном столбце таблицы?

Вопрос 30. Как получить значения из первой таблицы, которых нет во второй?

Вопрос 31. Как удалить дубликаты из таблицы, оставив по одному экземпляру каждого значения?

Вопрос 32. Как можно сгенерировать последовательность чисел в SQL для решения задачи?

Вопрос 33. Каков опыт использования CTE и для чего они применяются?

Вопрос 34. Как посчитать сумму и нарастающий итог по группам с помощью оконных функций?

Вопрос 35. Что такое индексы, какие их виды существуют и зачем они нужны?

Вопрос 36. Почему B-tree индекс ускоряет поиск и что значит сбалансированное дерево?

Вопрос 37. Что такое bitmap-индекс и когда его стоит использовать?

Вопрос 38. Есть ли различия между B-tree и B+tree индексами?

Вопрос 39. Имеет ли значение порядок колонок в составном индексе и как он влияет на использование индекса?

Вопрос 40. Почему нельзя повесить индексы на все поля и все возможные комбинации?

Вопрос 41. Почему большое количество индексов замедляет операции INSERT и UPDATE, и какие ещё есть минусы?

Вопрос 42. Почему большое число индексов может замедлить SELECT-запросы?

Вопрос 43. На какие аспекты плана запроса стоит обращать внимание и какие существуют методы сканирования и соединения?

Вопрос 44. Какова роль статистики в работе индексов и оптимизатора запросов?