Модуль 3.6. SQL для системного аналитика

Запросы для проверок, источники данных, CDC/ETL-контекст (DWH/BI). Артефакты: «каталог SQL-проверок», «паспорт источников данных», «слой витрин». Практика: описать 5 эндпоинтов + ER-схему + SQL-проверки.

Системный аналитик (SA) не заменяет DBA/DE, но обязан:

уметь читать и писать SQL для валидации требований, проверок качества данных (DQ), расчёта метрик и «сшивки» интеграций;
понимать источники данных, слои DWH (staging → core → marts), CDC/ETL и как это влияет на «правду» и задержки;
быстро доказывать гипотезы: «почему не сошлась сумма», «куда делся платеж», «есть ли дубликаты».

На выходе у вас будут: каталог типовых SQL-проверок, шаблоны DQ-метрик, понимание CDC/ETL и практический мини-проект (5 эндпоинтов ↔ ER ↔ SQL-проверки).

Картина мира данных (как сотруднику — к исполнению)

Где живут данные

OLTP (продуктовая БД): «источник правды» для транзакций (PostgreSQL/MySQL).
Логи/события: Kafka/лог-файлы (outbox/CDC).
DWH: колонночное/облачное хранилище (Snowflake/ClickHouse/BigQuery/Redshift).
Витрины BI: агрегированные таблицы/представления для отчётов.

Правило: всегда фиксируйте в глоссарии источник правды для сущности (Order — OLTP; отчёт о выручке — DWH).

Слои DWH (минимальная дисциплина)

STG (staging) — как пришло (инкременты/CDC, без «косметики»).
CORE (модель) — нормализовано, типизировано, очищено, есть бизнес-ключи.
MARTS (витрины) — под конкретные отчёты/продукты (звезда: факты/измерения).

Изменения: схемы версионируйте; миграции сопровождайте тестами совместимости.

Инструментальный минимум SQL (PostgreSQL-диалект)

Частые конструкции

CTE для читаемости: WITH … AS (…) SELECT ….
Окна: row_number() over (partition by … order by …) для дедупликации.
MERGE/UPSERT: загрузка инкрементов.
Агрегации: sum(), count() filter (where …).
Дата/время: всегда timestamptz (UTC), аккуратнее с date_trunc().

Нейминг и типы (кратко)

Денежные: numeric(18,2) + currency CHAR(3); никаких float.
Идентификаторы: uuid как PK; натуральные ключи — UNIQUE.
Статусы/коды — реф-таблицы, не текст «как получится».

Кухня проверок: SQL-cookbook для SA

Домены примеров: «Заказы–Платежи–Возвраты», из модулей 3.1–3.5.

Полнота и валидность

-- Обязательные поля заказа при PAID
SELECT order_id
FROM "order"
WHERE status = 'PAID'
  AND (paid_at IS NULL OR currency !~ '^[A-Z]{3}$');

-- Валидность статуса против справочника (активного на сегодня)
SELECT o.order_id, o.status
FROM "order" o
LEFT JOIN ref_order_status s
  ON s.code = o.status
 AND coalesce(s.valid_to, '9999-12-31') >= now()
 AND s.valid_from <= now()
WHERE s.status_id IS NULL;

Целостность и инварианты

-- Сумма позиции заказа совпадает с итогом
SELECT o.order_id,
       SUM(i.qty * i.price)::numeric(18,2) AS calc_amount,
       o.total_amount
FROM "order" o
JOIN order_item i USING(order_id)
GROUP BY o.order_id, o.total_amount
HAVING SUM(i.qty * i.price)::numeric(18,2) <> o.total_amount;

-- Платёж в той же валюте, что и заказ
SELECT p.payment_id
FROM payment p
JOIN "order" o ON o.order_id = p.order_id
WHERE p.currency <> o.currency;

-- Возвраты не превышают списанное
WITH refunded AS (
  SELECT payment_id, SUM(amount) AS refunded_amount
  FROM refund
  WHERE status = 'COMPLETED'
  GROUP BY payment_id
)
SELECT p.payment_id
FROM payment p
LEFT JOIN refunded r USING(payment_id)
WHERE coalesce(r.refunded_amount,0) > p.amount;

Уникальность и дедуп

-- Дубликаты клиентов по email (case-insensitive)
SELECT lower(email) AS email_norm, COUNT(*) c, array_agg(customer_id)
FROM customer
GROUP BY lower(email)
HAVING COUNT(*) > 1;

-- Идемпотентные ключи платежей (дубли)
SELECT idempotency_key, COUNT(*) c, MIN(created_at), MAX(created_at)
FROM payment
GROUP BY idempotency_key
HAVING COUNT(*) > 1;

Свежесть и задержки (для DWH/ETL)

-- Лаг загрузки заказов в DWH относительно OLTP (по order_id максимальной даты)
WITH src AS (
  SELECT max(created_at) AS src_max FROM oltp.order
), dwh AS (
  SELECT max(created_at) AS dwh_max FROM dwh.core_order
)
SELECT EXTRACT(EPOCH FROM (src.src_max - dwh.dwh_max))/60 AS lag_minutes
FROM src, dwh;

Реконсиляция API ↔ БД (сквозные проверки)

-- Сколько успешных ответов 201 Create Payment и сколько записей в payment за час
WITH api AS (
  SELECT date_trunc('minute', ts) AS m, count(*) AS c
  FROM api_logs
  WHERE path = '/v1/payments' AND method='POST' AND status=201
    AND ts >= now() - interval '1 hour'
  GROUP BY 1
),
db AS (
  SELECT date_trunc('minute', created_at) AS m, count(*) AS c
  FROM payment
  WHERE created_at >= now() - interval '1 hour'
  GROUP BY 1
)
SELECT coalesce(a.m,b.m) AS minute,
       coalesce(a.c,0) AS api_cnt,
       coalesce(b.c,0) AS db_cnt,
       coalesce(a.c,0) - coalesce(b.c,0) AS delta
FROM api a
FULL JOIN db b USING (m)
ORDER BY 1;

Дедуп по окну (в потоковых загрузках)

-- Оставить первую запись по business_key в окне 24 часа
WITH ranked AS (
  SELECT *, row_number() over (partition by business_key order by occurred_at) AS rn
  FROM stg_events
  WHERE occurred_at >= now() - interval '24 hours'
)
SELECT * FROM ranked WHERE rn = 1;

SCD Type 2 (история атрибутов)

-- Обновление измерения адресов клиента (Type 2)
MERGE INTO dwh.dim_customer_address d
USING staging_customer_address s
ON (d.customer_id = s.customer_id AND d.is_current = true AND d.hash <> s.hash)
WHEN MATCHED THEN
  UPDATE SET valid_to = s.valid_from - interval '1 millisecond', is_current = false
WHEN NOT MATCHED THEN
  INSERT (customer_id, address_id, country, city, hash, valid_from, valid_to, is_current)
  VALUES (s.customer_id, s.address_id, s.country, s.city, s.hash, s.valid_from, '9999-12-31', true);

CDC/ETL для SA: что требовать и как читать

Виды CDC

Логическая репликация/WAL (Debezium/Connect): надёжно, дешево по нагрузке.
Триггеры/аудит-таблицы: быстро стартануть, но нагрузка на OLTP.
События из outbox: договорённые факты (см. модуль 3.5).

На что смотреть в спецификации источника:

гарантии порядка (по ключу);
типы операций (I/U/D), tombstone;
повторные события;
схемы типов/NULL;
дедуп-политика.

Типовой pipeline

STG: «как есть» + технические поля (extract_ts, src_lsn, op).
DEDUP: row_number() over (partition by key order by extract_ts desc) → rn=1.
CORE: приведение типов, справочники, домены, FK-связи.
MARTS: звезда (факты/измерения), расчётные атрибуты.
DQ-чек-листы: запускаются после CORE и перед MARTS.

Поздно пришедшие факты (late arriving)

Принимаем и пересчитываем витрину за затронутые периоды (инкремент + backfill).
Логируйте перерасчёты: кто/когда/какой диапазон.

SQL для API-поддержки: как SA верифицирует контракты

«SQL как приёмка» (Given/When/Then → SELECT)

AC: «p95 POST /payments < 3с 7 дней» → запрос к метрике или лог-таблице.
AC: «PATCH /orders/{id}/delivery-address запрещён при SHIPPED» → SQL, находящий нарушения в логах изменений.

-- Нельзя менять адрес, если статус SHIPPED (по факту изменений)
SELECT o.order_id, l.changed_at
FROM audit_order_address l
JOIN "order" o USING(order_id)
WHERE o.status = 'SHIPPED' AND l.changed_at >= o.shipped_at;

Срезы для REST/GraphQL

REST /orders?status=PAID&cursor=… → подготовьте представление с устойчивой сортировкой (created_at, order_id), курсор = base64(concat(created_at,'|',order_id)).
GraphQL connection → тот же подход курсора, но в разрезе поля сортировки.

Производительность: как не «убить» базу проверками

Фильтры по индексируемым полям (order_id, status, created_at).
Не делайте SELECT * на OLTP; используйте реплики для чтения/аналитики.
Для больших сверок — «батчи» (по времени или по id діапазонам).
Смотрите EXPLAIN (ANALYZE, BUFFERS) перед регулярным запуском тяжёлого запроса.
Храните промежуточные агрегаты в витрине, если сверка нужна ежедневно.

Артефакты модуля (шаблоны — копируйте)

Паспорт источника данных

Источник: OLTP.Orders (PostgreSQL)
Схема: public
Таблицы: order, order_item, payment, refund, customer
Owner: команда Checkout
SLA: RPO 0, RTO 15 min; read-replica для аналитики
Доступ: только readonly пользователи для проверок (user: sa_ro)
CDC: Debezium (op: c/u/d; ключ order_id; гарантии: per-key ordering)
Ограничения: PII поля маскируем, выгрузка только через view с масками

Каталог SQL-проверок (фрагмент)

ID: DQ-ORD-AMOUNT-001
Описание: несоответствие суммы заказа сумме позиций
Частота: ежедневно 08:05
SQL: /dq/sql/ord_amount_check.sql
SLO: rate < 0.1%
Алерт: SEV-2 при rate > 0.5%
Владелец: Billing Squad
---
ID: DQ-PAY-CURR-002
Описание: валюта платежа не равна валюте заказа
Частота: каждый час
SLO: 0 нарушений

Шаблон витрины (звезда)

fact_order (order_id, customer_id, status, created_at, paid_at, total_amount, currency, d_order, d_customer)
dim_customer (customer_id, segment, country, valid_from, valid_to, is_current)
dim_date (d_key, date, y, q, m, d, is_weekend)

Практика (90–120 мин): «5 эндпоинтов + ER-схема + SQL-проверки»

Задача: на домене «Заказы–Платежи–Возвраты» спроектировать 5 эндпоинтов, показать связку с ER и написать SQL-проверки приёмки.

Эндпоинты (примерный набор)

GET /v1/orders?status=&cursor=&limit= — курсорная пагинация.
GET /v1/orders/{id} — чтение заказа.
POST /v1/payments — идемпотентно.
GET /v1/payments/{id} — чтение платежа.
POST /v1/refunds — возврат по paymentId (идемпотентно).

ER-фрагмент (минимум)

order(order_id PK, customer_id FK, status, total_amount, currency, created_at, paid_at)
order_item(order_item_id PK, order_id FK, product_id, qty, price)
payment(payment_id PK, order_id FK, status, amount, currency, idempotency_key UNIQUE, created_at, captured_at)
refund(refund_id PK, payment_id FK, amount, status, created_at)

SQL-проверки к эндпоинтам (по AC)

AC-1 (GET /orders): при status=PAID возвращаются только оплаченные.

SELECT COUNT(*)
FROM api_dump_orders_resp r
WHERE r.status_filter = 'PAID' AND r.order_status <> 'PAID';

AC-2 (POST /payments): повтор с тем же Idempotency-Key возвращает тот же paymentId.

SELECT idempotency_key, COUNT(DISTINCT payment_id) AS ids
FROM payment
GROUP BY idempotency_key
HAVING COUNT(DISTINCT payment_id) > 1;

AC-3 (GET /payments/{id}): валюта = валюте заказа.

SELECT p.payment_id
FROM payment p JOIN "order" o USING(order_id)
WHERE p.payment_id = :id AND p.currency <> o.currency;

AC-4 (POST /refunds): сумма возврата не превышает списанное.

WITH cap AS (
  SELECT payment_id, amount AS captured FROM payment WHERE payment_id = :payment_id
), ref AS (
  SELECT payment_id, SUM(amount) AS refunded
  FROM refund WHERE payment_id = :payment_id AND status <> 'FAILED' GROUP BY payment_id
)
SELECT (r.refunded + :new_amount) > c.captured AS violates
FROM cap c LEFT JOIN ref r USING(payment_id);

AC-5 (GET /orders): курсорная пагинация стабильна (без пропусков/дубликатов) при вставках.
(Проверка на отсутствие дублей order_id между «страницами» за период).

WITH page1 AS (
  SELECT order_id, created_at FROM "order"
  WHERE created_at >= now() - interval '1 day'
  ORDER BY created_at, order_id
  LIMIT 50
),
page2 AS (
  SELECT order_id, created_at FROM "order"
  WHERE (created_at, order_id) > (SELECT max(created_at), max(order_id) FROM page1)
  ORDER BY created_at, order_id
  LIMIT 50
)
SELECT COUNT(*) FROM (
  SELECT order_id FROM page1
  INTERSECT
  SELECT order_id FROM page2
) dup;

Критерии зачёта

Эндпоинты дружат с ER (поля/типы/статусы совпадают).
Есть проверки полноты/валидности/целостности/идемпотентности/свежести.
SQL-проверки выполняемы на read-реплике/стейджинге, не бьют производительность.
Положены в «каталог SQL-проверок» с владельцами/частотой/SLO.

Риски и как их гасить

Сравнение грошей разными типами (float vs numeric) → только numeric(18,2), единые правила округления.
Часовые пояса/границы суток → timestamptz + явные интервала; отчётные срезы с «локальным» поясом — переводите в SQL.
Dirty reads на OLTP → использовать реплики/снапшот-изоляцию.
Отсутствие индексов → «сканы всего мира» → согласуйте с DBA, добавляйте по условиям фильтра/сортировки.
Неготовая CDC-семантика (нет tombstone/порядка) → задокументируйте и закладывайте дедуп и «last write wins».
Путаница источника правды (OLTP vs DWH) → явные правила «где считаем».
*«SELECT » в проверках → выбирайте только нужные поля; для регулярных отчётов — витрины.

Вопрос–Ответ

Q: Мне как SA обязательно знать оконные функции?
A: Да. Дедуп, ранжирование, скользящие метрики — ежедневные задачи аналитика.

Q: Где держать SQL-проверки?
A: В Git с версионированием, рядом — расписание (Airflow/dbt), SLO и владельцы. Итоги — в BI/дашборде.

Q: Что выбрать для CDC: триггеры или Debezium?
A: Если есть выбор — логическая репликация (Debezium/Connect): меньше нагрузки и «ближе к правде». Триггеры — временный компромисс.

Q: Как проверять p95 латентности API SQL’ем?
A: Если метрики хранятся в БД/лог-таблицах — percentile_disc(0.95) within group (order by duration_ms) по окну.

Q: Можно ли сравнивать отчёт DWH с OLTP на «прямую»?
A: Да, но учитывайте lag (freshness) и возможные поздние факты. Договоритесь об «окнах истины».

Шпаргалка (распечатайте)

OLTP = источник правды по фактам; DWH = реплика для анализа.
STG→CORE→MARTS, между слоями — DQ-чек-листы.
Денежные — numeric(18,2) + валюта; даты — timestamptz.
Окна/CTE/UPSERT — инструменты №1 для SA.
CDC: at-least-once + дедуп; outbox для фактов.
SQL-проверки в Git, с владельцем, частотой и SLO.
Не убивайте OLTP — читайте реплики, индексируйте фильтры, используйте курсоры.

← Предыдущая статья

Модуль 3.5. Событийная модель и доменная интеграция

Следующая статья →

Модуль 4.1. Архитектурные стили и разбиение системы