Традиционные методы загрузки данных всё ещё актуальны: полная, инкрементальная и дельта-загрузка в DWH

При организации работы хранилища данных важно выбрать оптимальный метод загрузки. Существует множество современных способов переноса данных из источников, например, Change Data Capture (CDC) или прямая передача данных без необходимости их временного хранения. Они предлагают продвинутые возможности для репликации данных в реальном времени с отслеживанием историчности. Но иногда данные нужно перенести быстро или в силу бизнес-требований нет необходимости использовать трудозатратные способы. Тогда мы выбираем традиционные методы репликации.

Полная загрузка — это перенос всех данных из источника (объекта) в хранилище за один раз. Каждый раз, когда нам нужно обновить данные, мы снова перезагружаем объект целиком. Этот метод прост и надежен, если объем данных невелик (н-р, это актуально для редко обновляемых справочников) или есть строгое требование к целостности данных (а других способов гарантировать её нет). Однако, с увеличением количества данных, полная загрузка становится всё более времязатратной и ресурсоемкой.

Инкрементальная загрузка предполагает добавление только новых записей (операция Insert) в хранилище без обновления или удаления уже существующих (если что-то из уже загруженных данных в источнике изменилось, данные в хранилище перестанут быть достоверными). Этот метод эффективнее, так как за один раз загружается меньший объем данных. Однако он несёт в себе риски утраты доверия к данным.

Дельта-загрузка, или загрузка с частичной перезагрузкой, — более продвинутый метод. Он включает в себя загрузку не только новых записей (Insert), но и обновление существующих данных (Update), а иногда и удаление устаревших записей (Delete). Дельта-загрузка требует сложной логики отслеживания изменений в источнике, что позволяет синхронизировать хранилище с актуальным состоянием данных с высокой точностью. Если, конечно, система-источник может предоставить необходимую информацию об изменениях.

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

Примеры объектов и вариантов их репликации:
1. Справочник стран (обновляется редко, небольшой объем) — полная загрузка
2. Логи (старые данные не изменяются, только приходят новые) — инкрементальная загрузка
3. Текущее состояние заказа (данные в полях обновляются, есть отслеживание изменений) — дельта-загрузка

Мой совет: всегда ориентируйтесь именно на ваши бизнес-требования, внимательно изучайте источник, а не просто слепо следуйте "лучшим практикам". Гибкость в выборе метода загрузки — ваш ключ к эффективному управлению данными.

#dwh

Обзор традиционных методологий разработки в DWH: ищем свой путь в мире данных

Начнем с простого, Data Warehouse (DWH) — это специальные системы для хранения огромных объемов информации, собранной из различных источников. Она нужна для анализа и принятия обоснованных решений.

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

Методология Инмона
Уильям Х. Инмон считается одним из основателей концепции DWH. Он предложил создавать системы, где все данные будут храниться в одном месте, аккуратно организованы и легко доступны.

Основные принципы подхода Инмона:
— Централизованное хранилище: все данные собираются в одном месте, что обеспечивает единый источник правды для всей организации.
— Нормализованная модель: данные хранятся в нормализованной форме (3NF или выше), что минимизирует дублирование и обеспечивает высокую целостность данных.
— Историческая точность: хранилище содержит исторические данные, что позволяет анализировать изменения во времени.
— Больше времени и ресурсов на разработку: подход Инмона часто связан с большими затратами времени и ресурсов на начальном этапе из-за сложности интеграции и нормализации данных.

Методология Кимбалла
Ральф Кимбалл предложил более простой и понятный способ создания DWH, сосредоточенный на конкретных задачах бизнеса. Его идея в том, чтобы строить DWH по частям, используя схему "звезда" (поговорим об этом в отдельном посте). Этот подход позволяет быстрее запускать проекты и обеспечивает легкость внесения изменений.

Ключевые аспекты методологии Кимбалла:
— Моделирование "звезда": используется денормализованная модель данных (таблицы измерений и фактов), что упрощает запросы и анализ.
— Ориентация на бизнес-процессы: каждая схема строится вокруг конкретного бизнес-процесса, что облегчает разработку и понимание данных.
— Быстрая доставка: методология подразумевает итеративную разработку и доставку, позволяя бизнесу быстро получать ценность от данных.
— Гибкость в изменениях: Добавление новых данных или изменение существующих процессов проще в денормализованной среде.

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

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

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

#dwh

Используете USING? Тогда мы идём к вам

Ранее я писала про то, почему не стоит использовать SELECT *, а сегодня хочу поговорить об USING.

USING — ключевое слово, которое используется в SQL-запросах для упрощения синтаксиса объединения таблиц по одноимённым столбцам.

Например, вместо:

SELECT o.order_id, o.amount, c.customer_surname, c.customer_name
FROM orders o
JOIN customers c 
ON o.customer_id = c.customer_id

Можно написать:

SELECT o.order_id, o.amount, c.customer_surname, c.customer_name
FROM orders o
JOIN customers c 
USING (customer_id)

В этом случае USING объединяет таблицы по столбцам с одинаковыми именами, указанными в условии.

Почему так делать не нужно?
1. USING хоть и добавляет компактности, но убирает ясность в коде (это особенно заметно при множественном объединении таблиц), а следовательно снижает его поддерживаемость.

2. При использовании USING, столбец, по которому происходит объединение, возвращается в результирующем наборе только один раз. Какой из нужных? Звучит неоднозначно, особенно при использовании не только INNER JOIN. Это может создать сложности при необходимости разделения значений из каждой таблицы в дальнейшем анализе.

3. Изменения неизбежны в любой базе данных. Столбцы могут быть переименованы, добавлены или удалены. И при явном указании столбцов объединения, мы легко найдём ошибку. В случае же использования USING, могут возникнуть ситуации, когда будет переименовано несколько столбцов в разных таблицах и логика объединения нарушится, превратив результаты запроса в мусор.

4. Использование USING иногда может ограничить возможности оптимизатора выбирать наиболее эффективный план выполнения.

5. Особенности отдельны СУБД. Если JOIN ON работает предсказуемо, то использование USING может привнести сюрпризы.
Например, в Snowflake при определённых условиях множественные USING по одинаковым столбцам могут вообще игнорироваться. Совет: всегда изучайте документацию.

USING — это синтаксический сахар и его использование далеко не всегда оправдано.

Как я уже говорила ранее, помните, что явное всегда лучше неявного. Точные условия объединения с использованием ON делают зависимости между таблицами понятными, тем самым повышая стабильность, эффективность и надежность запросов, улучшая их масштабируемость.

#sql

Управление документацией: превращаем хаос в порядок

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

Ещё не так давно никто знать не знал о системах управления документацией и чаще всего все использовали Word (кучи файлов на сетевом диске) или Google Docs. Минус такого подхода очевиден: проблемы с поиском, навигацией и версионностью. Важна доступность, а не просто наличие документов.

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

Confluence, часть экосистемы Atlassian, идеален для технических команд, которым нужны мощные функции для создания сложной документации и управления знаниями. Он предлагает интеграцию с JIRA и Bitbucket, что упрощает трекинг задач и управление кодом.

Notion выделяется своей универсальностью, гибкостью по доступной цене (плюс есть бесплатные версии). Он позволяет комбинировать заметки, базы знаний, канбан-доски и документы в одном месте. Это отличный выбор для маленьких и средних команд, которым нужен один инструмент для всего.

Выбирайте инструмент, исходя из нужд команды, важности интеграции с другими сервисами и предпочтений в работе друг с другом. Confluence идеален для крупных проектов, Notion — для гибкой работы над разнообразными задачами в командах малого и среднего размеров. Google Docs я предпочитаю использовать в случаях крайней необходимости. Место для хранения знаний должно быть централизованным.

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

#документация

Немного об оконных функциях

Рассказывать о простом SQL можно много и долго, но давайте перейдем к более глубоким темам. Например, поговорим об оконных функциях. И перед тем как перейти к деталям, выясним, что же такое окошки?

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

Окно — набор строк, который указанным образом связан с текущей строкой.

Оконные функции обрабатывают данные в рамках окна, которое задается с помощью параметров:
1. Разделение на группы — PARTITION BY.
2. Упорядочивание данных внутри каждой группы — ORDER BY.
3. Определение диапазона строк для группы, над которыми будет производиться вычисление (ROWS BETWEEN) — поддерживается не всеми оконками.

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

SELECT
    employee_id,
    department,
    salary,
    AVG(salary) OVER(PARTITION BY department) as department_salary_avg
FROM employees;

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

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

Следите за каналом) в следующих статьях рассмотрим применение конкретных оконных функций под различные задачи.

#sql #оконные_функции

Противостояние CTE и подзапросов

В области анализа данных есть два классных инструмента — общие табличные выражения (CTE) и подзапросы, играющие важную роль в структурировании и обработке информации. Их правильное применение может значительно повысить эффективность ваших запросов.

CTE — это временные именованные наборы результатов, к которым можно обращаться внутри операторов SELECT, INSERT, UPDATE или DELETE. Они обеспечивают структурированный подход и делают код более читаемым и легким для анализа.

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

Минусы:
— Могут быть менее эффективны при неоптимальном использовании, особенно в больших базах данных из-за временного хранения результатов.

Пример:

WITH category_sales AS (
    SELECT p.category,
           SUM(s.quantity) AS total_quantity
      FROM sales s
      JOIN products p 
        ON s.product_id = p.product_id
     GROUP BY p.category
)
SELECT p.product_name,
       p.category,
       SUM(s.quantity) AS product_quantity,
       cs.total_quantity AS category_total_quantity
  FROM sales s
  JOIN products p ON s.product_id = p.product_id
  JOIN category_sales cs ON p.category = cs.category
 GROUP BY p.product_name, p.category, cs.total_quantity;

В этом примере в CTE category_sales вычисляется общее количество продаж товаров для каждой категории. Затем основной запрос использует эту информацию для вывода количества продаж по каждому продукту вместе с общим количеством продаж по его категории. Таким образом, мы можем увидеть как общие продажи по категориям, так и детальные продажи по каждому продукту. Это облегчает анализ эффективности продаж в разрезе категорий. Этот пример также можно реализовать с помощью оконных функций, но об этом в другой раз.
Чтобы поэкспериментировать самостоятельно, используйте песочницу https://sqliteonline.com и мой код для создания таблиц из вложения.

Подзапросы – это запросы, вложенные в другой запрос. Они предоставляют набор результатов для использования в основном запросе. Звучит сложно? На самом деле всё просто, сначала делается выборка по подзапросу, а результаты этой выборки используются для дальнейших вычислений.

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

Минусы:
— Ограниченные возможности повторного использования и рекурсии по сравнению с CTE.
— Могут привести к ухудшению читаемости кода при сложных вложениях.

Пример:

SELECT p.product_name, p.price
  FROM products p 
WHERE p.price > (
      SELECT AVG(price) -- Вычисление средней цены по категории
        FROM products
      WHERE category = p.category
);

В этом примере подзапрос в WHERE-части вычисляет среднюю цену по категориям. Затем основной запрос сравнивает цену товара и выводит только ту, чья цена выше средней.
Чтобы поэкспериментировать самостоятельно, используйте песочницу https://sqliteonline.com и мой код для создания таблиц из вложения.

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

#sql

Противостояние CTE и подзапросов
В статье выше я рассказываю про разницу между CTE и подзапросами.

Чтобы поэкспериментировать самостоятельно, используйте песочницу https://sqliteonline.com и мой код для создания таблиц из вложения.

Немного о важности сбора и интерпретации данных

Математическое мышление в работе и жизни

Читая на Хабре статью Аналитик vs. презентация задачи, наткнулась в ней на упоминание математического мышления. Тема на мой взгляд интересная в рамках разговоров про soft-скиллы, поэтому хотела бы уделить ей немного внимания в своём блоге.

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

Главный плюс мат.мышления в том, что это не какой-то врожденный талант, а вполне развиваемая способность.

Стандарты математической практики, разработанные в рамках Common Core в США, описывают математическое мышление через восемь практик:
— Умение решать проблемы
— Логическое рассуждение
— Аргументация и доказательство
— Моделирование
— Использование инструментов
— Точность
— Структурное мышление
— Обобщение и экстракция сущности

Согласитесь, всё это крайне важные вещи в работе дата инженера, системного аналитика, да и в целом в жизни любого IT специалиста человека.

Если хотите почитать чуть подробнее про некоторые полезные математические навыки, то рекомендую познакомиться с переводом статьи "Привычки людей с математическим складом ума".

А из книг выделю: "Thinking Mathematically" by John Mason, Leone Burton, Leone Burton (2010) и "How Not to Be Wrong: The Power of Mathematical Thinking" by Jordan Ellenberg (2014). Обе есть в русском переводе

#soft_skills

Методология Кимбалла: эволюция подходов к хранилищам данных

В отличие от подхода Билла Инмона, Ральф Кимбалл в конце 1980-х годов предложил построение хранилища данных снизу вверх. Сначала создаются маленькие, но полнофункциональные модули — звёздные схемы, которые легко масштабируются и интегрируются. Это упрощает доступ к данным и их анализ.

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

Важные принципы методологии:
— Узнаем какие отчеты нужны, изучаем источники, на этапе подготовки преобразуем данные, затем создаем витрины.
— Денормализация данных уменьшает количество соединений (JOIN) в запросах.
— Простая и понятная архитектура ускоряет разработку и внедрение. Нет детального слоя в понимании Инмона.
— Методология способствует быстрой адаптации к изменениям в бизнес-требованиях, благодаря модульному подходу.

Однако, методология Кимбалла может привести к некоторым проблемам с управлением данными на больших объемах из-за денормализации. Здесь важен баланс между производительностью и точностью данных.

Преимущества и недостатки:
+ Быстрая реализация проектов с "нуля".
+ Гибкость в добавлении новых источников данных.
+ Простота понимания и управления.
+ Простота запросов.
- Риск возрастания избыточности данных.
- Нет единого источника истины.
- Потенциальные трудности в поддержании целостности данных на больших объемах.

Основные отличия подхода Кимбалла от Инмона:
1. Проектирование сверху вниз против снизу вверх.
2. Денормализация данных.
3. Ориентация на быстрый доступ и анализ данных.
4. Низкая целостность данных.
4. Простота и скорость внедрения (по сравнению с Инмоном).

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

#dwh

Как писать документацию, которую захотят читать

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

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

1. Читаемость и компактность. Текст должен быть кратким и понятным. В инструкции "Открыть терминал. Ввести команду Х" звучит лучше, чем "Запустите командную строку и выполните следующую последовательность действий". Чуть полнее этот тезис я раскрывала в посте "ТЗ должно быть полным, но кратким".

2. Структура. Документация должна быть структурированной. Используем заголовки, подзаголовки и списки. Если есть несколько разделов, обязательно добавляем в начало оглавление. Читатель сразу увидит, где искать нужную информацию.

3. Выразительность. Пишем просто и ясно. Избегаем сложных терминов, если в этом нет необходимости. Если нужно использовать сложные термины, обязательно объясняем их. Так читатель не запутается.

4. Конкретность. Используем конкретные примеры. Это помогает лучше понять написанное. Например, если объясняем, как подключиться к базе данных, приводим примеры кода и скриншоты. Это лучше, чем абстрактное описание.

5. Последовательность. Нужно быть последовательным в использовании терминов и обозначений. Например, если в одном месте пишем "сервис", а в другом "служба", это может сбить с толку. Всегда используем одно и то же слово для одного и того же понятия.

6. Комментирование. Не забываем про комментарии. Хорошие комментарии помогут понять сложные моменты. Например, если есть сложный кусок кода, объясняем, что он делает (а не ждём, что читающий с одного беглого взгляда всё поймёт). Это сэкономит время читателю.

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

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

9. Инструменты. Используем инструменты для написания документации. Это могут быть редакторы с поддержкой разметки, системы контроля версий и/или платформы для совместной работы. Чуть подробнее об инструментах я писала здесь.

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

#документация