SQL для BI-разработчика: задачи для найма

BI-разработчик — роль, которую часто путают с аналитиком данных. Разница принципиальная: аналитик потребляет данные, BI-разработчик строит инфраструктуру для их потребления. SQL для BI — это не только SELECT, но и DDL, оптимизация и проектирование витрин.

Ключевые навыки BI-разработчика

В отличие от аналитика, BI-разработчик должен:

• Проектировать схемы витрин данных (Star/Snowflake схема)
• Писать сложные ETL-запросы с трансформациями
• Оптимизировать запросы для больших объёмов данных
• Работать с партиционированием и индексами
• Понимать работу материализованных представлений (MATERIALIZED VIEW)

Задача 1: Проектирование витрины (Middle)

Попросите кандидата описать структуру витрины для отчёта о продажах:

«Нам нужна витрина для BI-инструмента, которая позволит строить отчёты по продажам в разрезах: дата, продукт, категория, менеджер, регион, статус. Какую схему вы предложите?»

Хороший ответ включает:

-- Таблица фактов
CREATE TABLE fact_sales (
    sale_id         BIGINT PRIMARY KEY,
    date_id         INT REFERENCES dim_date(date_id),
    product_id      INT REFERENCES dim_product(product_id),
    manager_id      INT REFERENCES dim_manager(manager_id),
    region_id       INT REFERENCES dim_region(region_id),
    quantity        INT,
    amount          NUMERIC(12, 2),
    status          VARCHAR(20)
);

-- Измерение дат
CREATE TABLE dim_date (
    date_id         INT PRIMARY KEY,
    full_date       DATE,
    year            INT,
    quarter         INT,
    month           INT,
    week            INT,
    day_of_week     INT,
    is_holiday      BOOLEAN
);

Задача 2: Инкрементальная загрузка (Middle+)

-- Задача: напишите запрос для инкрементальной загрузки данных о продажах.
-- В таблицу fact_sales нужно загружать только новые и изменённые записи.

-- Метод 1: UPSERT (INSERT ON CONFLICT)
INSERT INTO fact_sales (sale_id, date_id, product_id, amount, updated_at)
SELECT
    s.id,
    d.date_id,
    p.product_id,
    s.amount,
    s.updated_at
FROM raw_sales s
JOIN dim_date d ON d.full_date = s.sale_date::DATE
JOIN dim_product p ON p.source_id = s.product_id
WHERE s.updated_at > (SELECT MAX(updated_at) FROM fact_sales)
ON CONFLICT (sale_id) DO UPDATE SET
    amount     = EXCLUDED.amount,
    updated_at = EXCLUDED.updated_at;

Что проверяем: понимание инкрементальной vs. полной загрузки, INSERT ON CONFLICT (UPSERT).

Задача 3: Работа с материализованными представлениями

-- Задача: создайте материализованное представление для
-- агрегированного отчёта по продажам, который используется часто

CREATE MATERIALIZED VIEW mv_sales_daily AS
SELECT
    d.full_date,
    d.year,
    d.month,
    p.category,
    r.region_name,
    SUM(f.amount)    AS total_revenue,
    COUNT(*)         AS sales_count,
    COUNT(DISTINCT f.manager_id) AS active_managers
FROM fact_sales f
JOIN dim_date    d ON d.date_id    = f.date_id
JOIN dim_product p ON p.product_id = f.product_id
JOIN dim_region  r ON r.region_id  = f.region_id
WHERE f.status = 'completed'
GROUP BY 1, 2, 3, 4, 5
WITH DATA;

-- Индекс для быстрой фильтрации
CREATE INDEX ON mv_sales_daily (full_date, category, region_name);

-- Обновление (можно добавить в расписание)
REFRESH MATERIALIZED VIEW CONCURRENTLY mv_sales_daily;

Что проверяем: знание MATERIALIZED VIEW, CONCURRENTLY (обновление без блокировки чтений).

Задача 4: Оптимизация тяжёлого ETL-запроса

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

-- Исходный медленный запрос
SELECT
    TO_CHAR(s.sale_date, 'YYYY-MM')    AS month,
    p.category,
    COUNT(*)                           AS sales,
    SUM(s.amount)                      AS revenue
FROM sales s
JOIN products p ON p.id = s.product_id
WHERE EXTRACT(YEAR FROM s.sale_date) = 2025
GROUP BY TO_CHAR(s.sale_date, 'YYYY-MM'), p.category;

Проблемы:

1. EXTRACT(YEAR FROM sale_date) блокирует индекс — заменить на диапазон дат
2. TO_CHAR в GROUP BY вычисляется дважды — использовать DATE_TRUNC или алиас через субзапрос

Оптимизированная версия:

SELECT
    DATE_TRUNC('month', s.sale_date)   AS month,
    p.category,
    COUNT(*)                           AS sales,
    SUM(s.amount)                      AS revenue
FROM sales s
JOIN products p ON p.id = s.product_id
WHERE s.sale_date >= '2025-01-01' AND s.sale_date < '2026-01-01'
GROUP BY DATE_TRUNC('month', s.sale_date), p.category
ORDER BY month, category;

Специфические вопросы для BI-разработчика

«Что такое SCD (Slowly Changing Dimension) и какие типы вы знаете?»

• SCD Type 1: перезаписать старое значение
• SCD Type 2: добавить новую запись с датой начала и окончания действия
• SCD Type 3: хранить текущее и предыдущее значение в отдельных колонках

«Чем MATERIALIZED VIEW отличается от обычного VIEW?» Обычный VIEW — это именованный запрос, который выполняется каждый раз при обращении. Материализованный — хранит результат на диске, обновляется по расписанию.

«Когда использовать партиционирование таблицы?» При объёмах от десятков миллионов строк, особенно когда запросы часто фильтруют по полю партиции (обычно дата).

Уровни оценки для BI-разработчика

Для BI-разработчика глубокое знание PostgreSQL и понимание производительности важнее, чем для аналитика. Направьте кандидата практиковаться на SQLlab.ru.