ETL и ELT: разбираемся вместе

Больше года назад я писала короткую заметку что такое ETL-процессы, но до подробного разбора руки так и не дошли.

Тем временем Вова Никулин, автор канала Lost in Data классно раскрыл эту тему со стороны дата-инженера. Вот его пост про основы ETL: тык. В нём простое объяснение как работает ETL и как реализовать его с помощью Python и SQL. Мега полезно, если вы только начинаете погружаться в тему.

А в свежем посте он развивает тему, объясняя разницу между ETL и ELT и их связь с Data Lake. Ещё вы узнаете, что такое Data LakeHouse, как гибридный подход меняет работу с данными и о том, почему в современном мире разница между ETL и ELT постепенно размывается (спойлер: всё также мы говорим ETL, хотя по факту давно перешли на ELT и EtLT).

Рекомендую канал Lost in Data, если вы хотите развиваться в области хранилищ данных, аналитики или автоматизации 🚀

Happy New Year 🎄

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

Пусть 2025 год будет интересным и добрым 🥳 остальное приложится, если захотите 👍

Всем привет!

Хочу начать этот год с поста-знакомства 🐾

Я Юля Иванова – системный аналитик DWH из Сибири (слава удалёнке).

По образованию я спец по ИБ, но работала по профессии совсем недолго. Затем на какое-то грустное время выпадала из IT, но от судьбы не уйдёшь и я вернулась. Поработала на разных ролях от PM до QA, но вот уже третий год как обосновалась в работе с данными и это по большой любви. Ещё тысячу лет назад во времена универа самыми любимыми предметами были вышка и СУБД. А теперь я с командой строю DWH для международного холдинга.

О чём мой блог?
- О любви и работе с данными.
- О системном анализе и документации.
- Об используемых мной инструментах.
- Совсем чуть-чуть о разных размышлениях.

Пишу о том, с чем сталкиваюсь в работе, и стараюсь объяснять максимально понятно. Вероятно, вы не найдёте здесь уникальных знаний, но надеюсь мои заметки окажутся полезными. Когда-то я сама начинала путь на чужих статьях, и теперь стараюсь отдавать сообществу то, что получила. Не менее важен и принцип: "Объясняя другим — расту сам". Ну и тяга к писательству так сильна, что просто невозможно не писать и не делиться 😍

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

Спасибо, что остаётесь со мной 🤗 Давайте расти вместе.

Системный аналитик DWH — кто ты?

Интернет заполнен рекламой курсов по дата инженерии и мы уже более-менее разобрались кто эти ребята и чем занимаются. С дата аналитикой, кажется, тоже плюс минус всё понятно. Но кто такие системные аналитики DWH (SA DWH), зачем они нужны и можно ли обойтись без них?

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

В России исторически закрепилась вертикальная структура компаний, где каждый отдел имеет «ответственное лицо» за свою область. Так у хранилищ данных и появились «системные аналитики DWH», которые:
🔵 согласовывают требования с бизнесом
🔵 расписывают модели данных
🔵 передают задачи дата-инженерам.

По сути, системный аналитик — входная точка в хранилище данных. Да, во многих зарубежных компаниях выделенных SA DWH нет: эти обязанности совмещают Data Warehouse Architect, Data Solutions Analyst, Technical Data Analyst, Analytics Engineer (поговаривают, что эта роль потихоньку уходит в прошлое, но это не точно), ETL или Data Engineer. Однако в российских реалиях системные аналитики DWH — это связующее звено между бизнесом и техническим миром. Правда, иногда их тоже называют дата-инжеренами, выделяя в отдельное направление (как, например, в одном жёлтом банке).

Чем же занимается SA DWH?

Все мы понимаем, что всё меняется от компании к компании, но примерно так:
🔵 общается с бизнесом и командами разработки, собирает требования к данным
(то есть координирует взаимодействие и выступает «переводчиком» между бизнесом, DevOps, ETL, BI и другими участниками дата-процессов).
🔵 проектирует схему хранилища: создает и документирует структуру таблиц, представлений, витрин (включая CDC, STG, ODS, EMART и другие «слои» DWH).
🔵 разрабатывает концепцию обработки данных: какие пайплайны нужны, как реализовать CDC и репликацию, и т.д.
🔵 ведёт анализ данных (и копается в куче legacy 🥲)
🔵 продумывает качество данных (иногда в составе отдельного подразделения по качеству, если позволяет масштаб компании)
🔵 создает стратегии по работе с историческими данными (сколько хранить, как обновлять, как архивировать)
🔵 планирует развитие DWH: какие таблицы добавить, какие поля стоит убрать, как обогатить данные, ...
🔵 проектирует витрины и описывает требования к ETL-процессам, чтобы дата-инженеры могли чётко реализовывать логику загрузки
🔵 разбирается с интеграцией легаси-систем (нередко это самый сложный блок).
🔵 ...

На самом деле список задач SA может сильно отличаться от команды к команде. Например, в идеальной ситуации есть выделенный DWH-архитектор, который строит целостную архитектуру и отвечает за масштабирование. Но если в компании такой роли нет, эти обязанности часто берёт на себя системный аналитик.

Для меня работа SA всегда где-то на грани творчества, экспертизы и здравого смысла. В этом и вся прелесть профессии.

Позже расскажу какими инструментами мы пользуемся в своей работе.

А что ближе вам — техническая внутрянка или работа с бизнес-контекстом и логикой?

#системный_анализ

GROUPING SETS для упрощения агрегации

Мы группируем данные десятки раз в день: по датам, категориям, клиентам или нескольким полям сразу. Но что, если нужно получить несколько уровней агрегации в одном результате? Объединять три разных запроса через UNION ALL? Писать вложенные подзапросы? Такой сценарий превращает простую задачу в головоломку с кучей повторяющегося кода 🔄

Теперь представьте: один запрос возвращает и детализацию, и промежуточные итоги, и общую сумму. И всё это без дублирования логики и потери производительности. Это не магия — это GROUP BY GROUPING SETS. Спойлер: после него вы вряд ли захотите возвращаться к старому подходу.

Синтаксис:

SELECT column1, column2, AGG_FUNC(column3) AS aggregate_result
FROM table_name
GROUP BY GROUPING SETS
(
    (column1),
    (column2),
    (column1, column2),
    () -- итоговая строка для всех данных
);

Итак, у нас есть таблица с заказами, и нужно вывести витрину продаж: по дням, категориям, дням и категориям, а также общие продажи.

| order_id | order_dt   | category    | price |
|----------|------------|-------------|-------|
| 1        | 2025-02-01 | Книги       | 100   |
| 2        | 2025-02-01 | Книги       | 200   |
| 3        | 2025-02-01 | Электроника | 700   |
| 4        | 2025-02-02 | Книги       | 150   |
| 5        | 2025-02-02 | Электроника | 250   |
| 6        | 2025-02-02 | Электроника | 550   |

Запрос:

SELECT 
    order_dt, 
    category, 
    SUM(price) AS total_sum
FROM orders
GROUP BY GROUPING SETS
(
    (order_dt, category), -- Группировка по дням и категориям
    (order_dt),           -- по дням
    (category),           -- по категориям
    ()                    -- Итоговая строка
);

Результат:

| order_dt   | category    | total_sum |
|------------|-------------|-----------|
| 2024-01-01 | Книги       | 300       |
| 2024-01-01 | Электроника | 700       |
| 2024-01-02 | Книги       | 150       |
| 2024-01-02 | Электроника | 800       |
| 2024-01-01 | NULL        | 1000      |
| 2024-01-02 | NULL        | 950       |
| NULL       | NULL        | 1950      |
| NULL       | Книги       | 450       |
| NULL       | Электроника | 1500      |

🔵Полные строки (order_dt и category заполнены) — детализированные данные.
🔵Строки с order_dt и NULL показывают суммы по каждому дню.
🔵Строки с category и NULL показывают суммы по каждой категории.
🔵Строка с двумя NULL — общая сумма.

Если нужно определить, какие строки являются результатом группировки, используйте функцию GROUPING(). Она возвращает 1 там, где значение агрегировано.

Пример:

SELECT
    order_dt,
    category,
    SUM(price) AS total_sales,
    GROUPING(order_dt) AS is_dt_agg,
    GROUPING(category) AS is_cat_agg
FROM orders
GROUP BY GROUPING SETS
(
    (order_dt),           -- Группировка по дням
    (category),           -- Группировка по категориям
    ()                    -- Итоговая строка
);

| order_dt   | category   | total_sales | is_dt_agg | is_cat_agg |
|------------|------------|-------------|-----------|------------|
| 2024-01-01 | NULL       | 1000        | 0         | 1          |
| 2024-01-02 | NULL       | 950         | 0         | 1          |
| NULL       | NULL       | 1950        | 1         | 1          |
| NULL       | Книги      | 450         | 1         | 0          |
| NULL       | Электроника| 1500        | 1         | 0          |

Почему GROUPING SETS лучше UNION ALL?
➕ один запрос вместо нескольких
➕ оптимизация выполнения — СУБД сканирует таблицу один раз и для каждой строки вычисляет все группировки параллельно
➕ читабельность кода

➖ поддерживаются не все диалекты SQL (но основные — PostgreSQL, Oracle, SQL Server, Snowflake, BigQuery — да)

GROUP BY GROUPING SETS полезен для отчетности и аналитических анализов, где нужны сводные данные разной детализации. Это инструмент работает:
🟢 удобно: меньше кода, меньше ошибок
🟢 быстро: один проход по данным
🟢 гибко: возможны любые комбинации группировок

#sql

Привет 👋 Живя за тысячи километров от «цивилизации», не хватает нетворкинга и свежего взгляда на дата-мир. Посты, подкасты, видео — всё это хорошо, но хочется живого общения.

Хочется узнать больше о вас, мои читатели. Расскажите о себе и вашем опыте. Как у вас строятся дата-пайплайны? С какими сложностями сталкиваетесь? Или, может, вы только начинаете свой путь в мире данных?

Моя текущая боль — интеграция с 1С, данные из которой падают в кафку в куче всяческих вариаций структур. И надо всё разложить по красоте, ничего не потеряв по пути и обложив проверками. Бесконечная вложенность объектов, непредсказуемые структуры, русские наименования сущностей... в общем, то ещё извращение 😏 А в жире уже ждет следующая пачка 1С на интеграцию. Вы как, дружите с 1С?) Делитесь опытом!

SQL под капотом: как выполняются запросы

Знаете ли вы, что происходит под капотом СУБД, когда вы выполняете SQL-запрос? База данных запускает целый процесс, шаг за шагом превращая код в набор данных. Каждая команда проходит проверку, оптимизацию, выполнение, обработку и вывод результата. Давайте посмотрим на каждый из этапов:

1. Query Parsing (разбор запроса)
Сначала сервер проверяет, правильно ли написан запрос. Проводит так называемый синтаксический анализ. Ошиблись в запятой или перепутали порядок ключевых слов? Получите ошибку.

После синтаксического анализа начинается семантический разбор: существуют ли таблицы и колонки, есть ли у вас права на запрос? Если все ок, база строит parse tree.

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

2. Query Optimization (оптимизация запроса)
На этом этапе в работу вступает умный планировщик. Он оценивает различные стратегии выполнения запроса, чтобы определить наиболее эффективную и менее ресурсоёмкую. Оптимизаторы сильно отличаются от СУБД к СУБД, но, к примеру, в Snowflake он, действительно, умный и даже плохо написанный запрос в большинстве случаев "переписывает" оптимально самостоятельно (это, конечно, не значит что стоит писать запросы как попало 👿).

Оптимизатор, в зависимости от СУБД может проверять:
Как соединять таблицы — Nested Loop, Hash Join, Merge Join?
Как фильтровать и сортировать данные?
Использовать индексы или нет?

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

3. Query Execution (выполнение запроса)
После этого база данных начинает пошагово выполнять запрос, согласно выбранному плану.

Запросы могут выполняться через:
🔵 Table Scan — полный перебор строк в таблице (долго).
🔵 Index Seek — точечный поиск через индекс (быстро, но требует индекса).

4. Извлечение или изменение данных
Если наш запрос извлекает данные (SELECT - Data Query Language), база выбирает нужные строки из таблиц и формирует результат. Если же запрос изменяет данные (INSERT, MERGE, UPDATE или DELETE - Data Manipulation Language), информация в таблице обновляется, удаляется или дополняется.

5. Формирование результата
Когда SQL-движок собрал нужные строки, он финально формирует итоговый результат: сортирует, группирует, выполняет агрегатные вычисления. Однако часть агрегаций, особенно в запросах с GROUP BY, может выполняться еще на этапе извлечения данных, если движок решит, что это эффективнее. То есть это зависит от плана выполнения запроса и используемого метода обработки.

6. Результат
Когда всё готово, результат возвращается в клиентское приложение, которое уже отображает его пользователю.

Для SELECT-запросов, если данных много, они передаются частями, чтобы не перегружать память.
Некоторые базы поддерживают Lazy Execution — строки выгружаются только при необходимости.


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

Не всегда имеет смысл знать, что происходит под капотом, но хотя бы верхнеуровневое понимание помогает нам самим работать эффективнее. Если что-то идет не так, вы будете знать, где искать проблему и как ее решить. Понимание происходящего — ключ к написанию быстрых и оптимизированных запросов.

#sql

Помню, что задолжала пост про инструменты в руках системного аналитика.

Но много думала об этом и выходит слишком скользкая тема) ведь главное — это не инструменты, а результат. А любой инструмент — это всего лишь путь к нему. Поэтому от компании к компании, да даже в рамках одной компании инструменты будут постоянно меняться.

Можно, конечно, поковырять всего понемногу из какой-нибудь таблички как на примере выше 😬, но сначала спросите себя "зачем". Быть в курсе трендов и быстро меняющегося мира данных — ок, знать всё обо всём без практической применимости — достижение уже спорное. Так или иначе, в работе вы будете погружаться в новые для вас инструменты и технологии.

Когда я приходила на свою первую работу без DWH бекграунда, то знала что такое airflow чисто теоретически из пары обзорных видео. Но жизнь заставила быстро разобраться что к чему. На текущей работе также пришлось моментально погружаться в Snowflake, про который каждый из коллег знал примерно столько же (так как зарождение DWH в компании началось с нас).

Выходит, что лучший наш инструмент — это мозг и умение (и что важно — желание) учиться и адаптироваться. Смотреть на мир широко и системно. Не фукать на что-то новое (частенько встречаю такое в IT-мире). Развивать софт-скиллс. Не зря всё чаще в статьях и конференциях упор делается именно на развитие мягких навыков, которые дополняют техническую экспертизу (или у меня это иллюзия частотности? 🤨).

Возвращаясь к инструментам. Чем пользуюсь в работе ежедневно сейчас: snowflake, airflow, kafka, tfs, базы различных наших источников (MS SQL, PostgreSQL и прочее), а также множеством самописных фреймворков (в том числе etl), разработанных нашими инженерами, тестирую новые загрузки через питон+тетрадку, исследую сторонние апи через постман (или ту же тетрадку). Заглядываю в датахаб и табло. Периодически сталкиваюсь с чем-то новым. В прошлом месте инструментарий был совсем другой) например, был гораздо более плотный опыт взаимодействия с Oracle, Greenplum и ClickHouse. Какие-то популярные решения, типа dbt ещё не было возможности потрогать в бою) только в качестве личного "факультатива".

Так что инструменты приходят и уходят, а мозг остаётся с нами всегда 😀 наше лучшее вложение 🤑🤫

#системный_анализ

Иметь перед глазами шпаргалки-напоминалки и короткие конспекты порой очень удобно, особенно, если чем-то пользуетесь не на регулярной основе. Пользуйтесь 😎

Разбираемся с дублями

Если вашу выборку нужно почистить от дублей, вы можете сделать это очень просто:

SELECT *
FROM your_table
QUALIFY ROW_NUMBER() OVER(PARTITION BY column_id ORDER BY column_dt DESC) = 1;

В результате получим в выводе только уникальные строки (вместо *, конечно же, указываем корректный список полей).

QUALIFY + ROW_NUMBER() = никаких лишних подзапросов 🙃

Недостаток: пока что работает не во всех СУБД 🥲

Если СУБД не поддерживает оператор QUALIFY, можем чистить так:

WITH cte AS (
    SELECT *,
           ROW_NUMBER() OVER (PARTITION BY column_id ORDER BY column_dt DESC) AS rn
    FROM your_table
)
SELECT *
FROM cte
WHERE rn = 1;

P.S. Про сам QUALIFY я уже писала здесь.

#sql