3НФ: спасаемся от хаоса в данных

В прошлый раз мы поговорили про 1 и 2НФ, пора двигаться дальше.

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

Отношение находится в 3НФ, когда находится во 2НФ и каждый не ключевой атрибут нетранзитивно зависит от первичного ключа.

Как следует из определения, чтобы отношение находилось в 3НФ, оно должно удовлетворять двум условиям:
1. Находиться в 2НФ (то есть быть приведенным к состоянию, где все неключевые атрибуты зависят от всего первичного ключа).
2. Каждый неключевой атрибут должен зависеть только от первичного ключа и ни от чего другого.

Непонятно? Рассмотрим на примере.
Представьте, что у нас есть таблица с информацией о заказах:

| order_id | product_id | product_name | category | qty |
|----------|------------|--------------|----------|-----|
| 1        | 101        | Ноутбук      | Техника  | 2   |
| 2        | 102        | Смартфон     | Техника  | 1   |
| 3        | 103        | Футболка     | Одежда   | 3   |

Эта таблица нарушает 3НФ, потому что есть зависимость product_id -> product_name -> category. То есть название товара и его категория зависят от id продукта, а не напрямую от id заказа.

Чтобы привести таблицу к 3НФ, нужно разбить ее на несколько связанных таблиц:

В итоге структура будет выглядеть так:

Таблица orders:

| order_id | product_id | qty |
|----------|------------|-----|
| 1        | 101        | 2   |
| 2        | 102        | 1   |
| 3        | 103        | 3   |

Таблица products:

| product_id | product_name | category |
|------------|--------------|----------|
| 101        | Ноутбук      | Техника  |
| 102        | Смартфон     | Техника  |
| 103        | Футболка     | Одежда   |

Внимательный читатель заметит, что таблица products всё еще не находится в 3НФ 😁. Как думаете, почему и как можно улучшить эту таблицу?

А мы пойдём дальше.

Определить нарушение 3НФ очень просто. Задайте себе несколько вопросов:
– Есть ли в таблице поля, значения которых повторяются (или могут повторяться) для разных строк?
– Можно ли определить значение одного поля через другое неключевое поле?
– При изменении одной записи, нужно ли обновлять другие записи с такими же данными?

Если хотя бы на один вопрос вы ответили "да", скорее всего, ваша таблица не соответствует 3НФ.

Применение 3НФ даёт несколько преимуществ:
– уменьшение избыточности данных: информация хранится только в одном месте, что упрощает обновление и поддержку.
– улучшение целостности данных: меньше шансов, что при обновлении информации возникнут несоответствия.
– гибкость при изменении структуры данных: если нужно добавить новые атрибуты, это не повлияет на таблицу заказов.

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

#dwh

Связи между данными: один-к-одному, один-ко-многим, многие-ко-многим

Захватим ещё немного основ (хотя, кажется, пора заканчивать с очевидным 😁).

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

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

Cамый простой тип связи — один к одному (1:1), то есть каждая запись в одной таблице соответствует только одной записи в другой таблице.
Например, есть таблица Сотрудники и таблица Паспортные данные. Каждый сотрудник имеет только один паспорт и каждый паспорт принадлежит только одному сотруднику.

Связь один-ко-многим (1:N) используется, когда одна запись в первой таблице может быть связана с несколькими записями во второй таблице. Например, в одном отделе может работать много сотрудников, но каждый сотрудник может работать только в одном отделе.

Связь многие-ко-многим (M:N) — самый сложный тип связи. Он используется, когда несколько записей из одной таблицы могут быть связаны с несколькими записями из другой таблицы. Обычно для реализации связи M:N используется промежуточная таблица.То есть такая связь разбивается на две связи "один ко многим" через промежуточную таблицу.

Классический пример — студенты и курсы. Один студент может посещать несколько курсов, и на одном курсе учится много студентов.

Таблица students:

student_id | name 
------------------
1          | Анна 
2          | Борис

Таблица courses:

course_id | name      
----------------------
101       | Математика
102       | Физика

Таблица students_courses:

student_id | course_id
-----------------------
1          | 101      
1          | 102      
2          | 101

Промежуточная таблица students_courses как раз и содержит комбинации ключей из обеих связанных таблиц.

Cтоит отметить, что в хранилищах данных мы иногда отходим от строгой реляционной модели и иногда можем хранить данные в более свободном формате😎. Но понимание этих базовых типов связей помогает нам правильно организовать данные для эффективного анализа.

#dwh

TABLE_DML_HISTORY: окно в мир изменений ваших данных

Вьюха TABLE_DML_HISTORY в Snowflake — инструмент, который помогает отслеживать и анализировать DML-операции (Data Manipulation Language) в таблицах. По сути он выводит агрегированную информацию о влиянии DML-операций на ваши данные.

Вот что там можно узнать:
🔵 какие таблицы изменялись
🔵 временные интервалы, в которые происходили изменения
🔵 количество добавленных, удаленных и обновленных строк

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

SELECT
    START_TIME,
    END_TIME,
    ROWS_ADDED,
    ROWS_UPDATED,
    ROWS_REMOVED
FROM SNOWFLAKE.ACCOUNT_USAGE.TABLE_DML_HISTORY
WHERE TABLE_NAME = 'SALES'
  AND START_TIME >= DATEADD('day', -1, CURRENT_TIMESTAMP())
ORDER BY START_TIME DESC;

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

SELECT
    TABLE_NAME,
    SUM(ROWS_ADDED) AS TOTAL_ROWS_ADDED,
    SUM(ROWS_UPDATED) AS TOTAL_ROWS_UPDATED,
    SUM(ROWS_REMOVED) AS TOTAL_ROWS_REMOVED
FROM SNOWFLAKE.ACCOUNT_USAGE.TABLE_DML_HISTORY
WHERE SCHEMA_NAME = 'SANDBOX'
    AND START_TIME >= DATEADD('day', -30, CURRENT_TIMESTAMP())
GROUP BY TABLE_NAME;

Но не бывает крутых функций без нюансов и ограничений. TABLE_DML_HISTORY:
🔵 содержит информацию по всем DML-операциям, выполненным за последние 365 дней;
🔵 задержка обновления данных может составлять до 6 часов;
🔵 не включает DML-операции на гибридных таблицах
🔵 доступ к этому представлению зависит от привилегий пользователя, обычно требуется роль ACCOUNTADMIN или соответствующие права на чтение из схемы ACCOUNT_USAGE.

Советы по использованию:
🔵мониторинг активности: регулярное отслеживание DML-операций помогает выявлять аномальные изменения и потенциальные проблемы с данными.
🔵аудит изменений: можно проводить аудит изменений в важных таблицах для обеспечения соответствия внутренним политикам и внешним требованиям.
🔵оптимизация производительности: анализ частоты и объема DML-операций может помочь в оптимизации запросов и пайплайнов.

TABLE_DML_HISTORY — хороший инструмент для мониторинга и аудита данных в Snowflake. Используйте его, чтобы лучше понимать, что происходит с вашими данными и вовремя вносить изменения в неоптимальные процессы.

Более подробную информацию вы всегда можете найти в официальной документации Snowflake.

#dwh #snowflake

Денормализация данных: когда и зачем нарушать правила

Денормализация — это намеренное введение избыточности данных в таблице. Звучит как ересь, не так ли? Мы же так долго их номрализовывали! Однако, в мире больших данных и высоких нагрузок этот подход имеет место быть.

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

Когда применять денормализацию?
🔵Есть часто выполняемые сложные запросы с множеством соединений — денормализация может значительно ускорить их выполнение за счёт хранения предварительно агрегированных данных в одной таблице.
🔵Для систем бизнес-аналитики (BI) и хранилищ данных денормализация — обычная практика. Она позволяет быстрее выполнять сложные расчеты и агрегации.
🔵В распределенных базах данных JOIN-ы между таблицами, хранящимися на разных узлах, могут быть очень дорогостоящими. Денормализация помогает избежать этих сложных операций.

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

orders:
| order_id | customer_id | order_date |
|----------|-------------|------------|
| 1        | 101         | 2024-09-01 |

customers:
| customer_id | name  | email          |
|-------------|-------|----------------|
| 101         | Алиса | [email protected] |

order_items:
| order_id | product_id | quantity |
|----------|------------|----------|
| 1        | 201        | 2        |

products:
| product_id | name     | price |
|------------|----------|-------|
| 201        | Ноутбук  | 50000 |

Для получения полной информации о заказе нам приходится объединять все эти таблицы:

SELECT 
    o.order_id,
    c.name AS customer_name,
    c.email AS customer_email,
    p.name AS product_name,
    oi.quantity,
    p.price,
    oi.quantity * p.price AS total_price,
    o.order_date
FROM orders o
JOIN customers c     ON o.customer_id = c.customer_id
JOIN order_items oi  ON o.order_id = oi.order_id
JOIN products p      ON oi.product_id = p.product_id
WHERE c.name = 'Алиса';

При высокой нагрузке это может стать узким местом. В денормализованной версии мы можем хранить все нужные нам столбцы и расчёты (н-р, order_id, customer_name, customer_email, product_name, quantity, price, total_price, order_date). Когда вся информация доступна в одной таблице, это значительно ускоряет запросы на чтение:

SELECT 
    order_id,
    customer_name,
    customer_email,
    product_name,
    quantity,
    price,
    total_price,
    order_date
FROM orders o
WHERE customer_name = 'Алиса';

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

Чтобы минимизировать эти риски, можно использовать различные техники. Например, материализованные представления позволяют создавать денормализованные таблицы, которые автоматически обновляются при изменении исходных данных.

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

А вы сталкивались с необходимостью денормализации в своих проектах? Какие подходы использовали для поддержания согласованности данных?

#dwh

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

Что самое главное в DWH? Конечно же, данные, которые как-то должны попасть в хранилище. Чаще мы используем современные методы загрузки, но на ранних этапах или из-за тех. ограничений, или при исследованиях, мы всё также прибегаем к классическим методам.

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

При полной загрузке мы каждый раз заново забираем все данные из источника, полностью заменяя существующие данные в хранилище. Почему "полностью" курсивом? Иногда нам нужно отслеживать удалённые строки, тогда мы не просто транкейтим, а размечаем отсутствующие строки флагом DELETED.

Полная загрузка — самый простой метод со своими особенностями:
➕ простота реализации
➕ 100% актуальность данных
➖ высокая нагрузка при больших объемах данных
➖ время выполнения
➖ неэффективное использование ресурсов при небольших изменениях.

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


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

Для больших таблиц с частыми апдейтами — то, что надо. Важно❗️если нужно отслеживать изменения, у таблиц должно быть поле, содержащее дату и время обновления строки. Убедитесь, что вы можете доверять ему. Комментарии к полям могут врать! В моей практике были случаи, когда дата содержала инфу об изменении только нескольких полей из таблицы, что не было нигде явно указано 🥲 (да, иногда нужно покапаться в коде источника)

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

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


Частичная перезагрузка — гибрид предыдущих способов. Здесь мы перезагружаем только часть данных, обычно за какой-то конкретный период.
➕ баланс актуальности и эффективности
➕ обновление за определенный период без полной перезагрузки
➕ удобно для данных с "окном актуальности"
➖ сложно определить оптимальный период
➖ риск дублей при неправильной реализации
➖ нужна дополнительная логика для определения границ загрузки

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


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

Благодаря тому, что источники не идеальны, работа системного аналитика всегда где-то на грани творчества и здравого смысла 😇

#dwh

Организация мониторинга и алертинга в DWH-системах

Мониторинг и алертинг — это не просто какие-то модные слова из мира IT. Это основа, на которой держится работа с данными. Давайте разберемся, как это устроено в современных хранилищах.

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

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

Простой пример: у вас есть ежедневный процесс обновления данных об отгрузках со склада, и обычно он выполняется за 15 минут. Если мониторинг фиксирует, что процесс вдруг начал занимать час, он оповещает, что что-то пошло не так. Возможно, сломалась загрузка из источника или увеличился объем данных (тоже не плохо обратить на это внимание).

Еще одна задача — контроль качества данных. Иногда сами данные могут приходить с ошибками. Представьте, что ваш ETL процесс получил пустые строки об отгрузках вместо обычного объема данных. Хорошо настроенный мониторинг заметит это и оповестит команду.

Что конкретно стоит мониторить?
1. Процессы загрузки данных: время выполнения, наличие ошибок, объемы данных.
2. Качество данных: проверки на пустые значения, аномалии, отклонения от ожидаемых значений.
3. Работа сервера: утилизация CPU, память, доступное место на диске. Особенно важно в облачных решениях, где рост нагрузки приводит к дополнительным затратам.


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

Ещё один пример: если ETL процесс падает три раза подряд — это явно повод для тревоги. Алертинг должен отправить сообщение дежурным инженерам по оперативным каналам (но не засыпать этот канал оповещениями нон-стоп). Для этого удобно использовать мессенджеры вроде Slack, хуже — email уведомления.

Инструменты для мониторинга и алертинга
🟠 Prometheus + Grafana: отличный вариант для отслеживания метрик и визуализации данных. Prometheus собирает метрики, а Grafana показывает их в удобных дашбордах.
🟠 dbt Cloud: инструмент для разработки и тестирования ELT процессов, который также имеет встроенные возможности для мониторинга.
🟠 Snowflake Native Alerts: позволяет создавать алерты на основе запросов. Например, если количество записей в таблице падает ниже ожидаемого уровня, система автоматически уведомит об этом.
🟠 самописные решения 🐱

С чего начать?
Например, с базовых метрик для ETL процессов, а затем добавить проверки на качество данных. Используйте оповещения с пороговыми значениями. Например, алерт сработает, если время выполнения процесса превышает обычное более чем на 10%.

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

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

#dwh

Материализованные представления: ускоряем аналитику

Материализованные представления (Materialized Views, MV, матвью) — способ ускорить выполнение аналитических запросов за счет предварительного вычисления и сохранения данных.

Чтобы понять матвью, давайте вернёмся на шаг назад и вспомним, что такое вью (view, V, представление). Представление — это виртуальные таблицы, которые хранят текст SQL-запроса и запускаются на лету. Они не сохраняют сами данные (в некоторых бд могут кешироваться в рамках сеанса до изменения источников).

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

Пример: есть таблица с данными о продажах, которые обновляются раз в сутки. Аналитики несколько раз в день строят отчёты с расчётом суммарного дохода по регионам и категориям товаров. Создание MV позволяет агрегировать данные раз в сутки сразу после обновления источников. Это ускоряет построение отчетов и снижает нагрузку на базу данных.

Рассмотрим пример создания MV для Snowflake:

CREATE MATERIALIZED VIEW sales_summary_mv AS
SELECT 
    region,
    category,
    SUM(revenue) AS total_revenue,
    COUNT(*) AS transaction_count
FROM sales
GROUP BY region, category;

Как происходит синхронизация данных?
Чаще всего MV автоматически обновляются при изменении исходных данных. Однако частота и способ обновления зависят от СУБД:
— Snowflake: обновляются инкрементально, снижая нагрузку на хранилище.
— PostgreSQL: обновление требует явного выполнения команды REFRESH MATERIALIZED VIEW, что добавляет ручной работы.
— Oracle: поддерживаются как полные, так и инкрементальные обновления в зависимости от настроек.
— Microsoft SQL Server: MV называются индексированными представлениями и обновляются автоматически, но с ограничениями на типы запросов.

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

Когда не стоит использовать MV
🟣 запросы редкие или исследовательские (ad hoc) — тогда затраты перевешивают выгоду;
🟣 ограничено место для хранения (ведь данные сохраняются физически);
🟣 данные обновляются слишком часто — материализованные представления необходимо часто обновлять, что может привести к дополнительным накладным расходам (актуально не для всех систем);
🟣 запросы содержат конструкции, не поддерживаемые MV (зависит от БД).

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

#dwh