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

Открыт набор в новый сезон крутой бесплатной менторской программу от Women in Tech и Women in Big Data. Эти женские комьюнити реально помогают девушкам расти и развиваться в IT 🚀
Как для ментора, так и для менти участие в программе открывает отличные возможности для нетворкинга и интересных знакомств, а также это в целом классный опыт.
Присоединяйтесь!

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

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

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

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

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

Сегодня каналу В мире больших данных 1 год 🥳

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

Когда ты пишешь для начинающих специалистов (и сам при этом ещё далеко не специалист экстра-класса), то сложно выдавать эксклюзивный экспертный контент. Но знаете что? Моя любовь к знаниям и желание ими делиться всё равно берут верх) так что "улыбаемся, машем, и продолжаем пилить контент" 💻

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

Спасибо каждому подписчку за то, что вы со мной. Вместе мы делаем мир данных чуточку понятнее ❤️

ANY_VALUE: функция для упрощения GROUP BY запросов

Привет! Сегодня расскажу про функцию ANY_VALUE в SQL. Она помогает упростить GROUP BY запросы, особенно когда вы работаете с большими наборами данных.

Если вы работали с агрегатными функциями и группировками GROUP BY, то, вероятно, сталкивались с ограничениями при выборе столбцов.

Представьте, у вас есть не очень нормализированная витрина с заказами (всё также рекомендую смотреть таблички в десктоп версии или развернуть телефон горизонтально🥲):

| ord_id | cust_id | cust_nm | product    | qty | price |
|--------|---------|---------|------------|-----|-------|
| 101    | 1       | Иван    | Ноутбук    | 2   | 1500  |
| 102    | 2       | Ольга   | Смартфон   | 1   | 800   |
| 103    | 1       | Иван    | Планшет    | 1   | 600   |
| 104    | 3       | Анна    | Наушники   | 3   | 150   |
| 105    | 2       | Ольга   | Умные часы | 2   | 400   |

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

SELECT cust_id, SUM(qty * price) as total_amount
FROM orders
GROUP BY cust_id

Но что если мы захотим добавить в результат customer name (cust_nm)? Получим ошибку, потому что cust_nm не входит в GROUP BY и не используется в агрегатной функции. Вот здесь и приходит на помощь ANY_VALUE:

SELECT 
  cust_id, 
  ANY_VALUE(cust_nm) as customer_name,
  SUM(qty * price) as total_amount
FROM orders
GROUP BY cust_id

Этот запрос выполнится без ошибок. ANY_VALUE говорит базе данных: "Возьми любое значение cust_nm для каждой группы cust_id".
Важно понимать, что ANY_VALUE не гарантирует, какое именно значение будет выбрано. Оно может меняться от запуска к запуску. Поэтому используйте эту функцию, только когда вам не важно, какое именно значение будет возвращено, или если вы уверены, что внутри группы значения одинаковы.

ANY_VALUE помогает оптимизировать запросы. В некоторых СУБД она дает понять оптимизатору, что порядок выбора значений не важен, что может привести к более эффективному плану выполнения, чем при использовании min-max на группе.

Однако, не все СУБД поддерживают ANY_VALUE. В PostgreSQL, например, как раз таки придётся использовать min или max:

SELECT 
  cust_id, 
  MIN(cust_nm) AS customer_name,
  SUM(qty * price) AS total_amount
FROM orders
GROUP BY cust_id;

ANY_VALUE — полезная функция для упрощения агрегатных запросов, когда точное значение не имеет значения. Главное — использовать его осознанно и понимать, когда его применение оправдано.

#sql

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

Всем привет, у меня отличные новости! Совсем скоро я выступаю на IT-конференции UIC Dev в Ижевске.

UIC Dev — конференция для всех, кто в теме IT: разработчиков, аналитиков, тестировщиков, дизайнеров, SEO-шников, копирайтеров и маркетологов.

Я и Head of DWH Emex Катя Колпакова выступим с докладом "Как мы построили DWH для международного холдинга командой из 6 человек".

Мы расскажем о нашем пути создания хранилища данных для Emex. Поделимся, почему этот проект был сложным из-за большого количества бизнес-моделей, кучи легаси и запутанных связей. Расскажем, как мы выбирали базу данных, какие методологии и технологии использовали. А также раскроем некоторые фишки нашего DWH.

Что ещё ждёт вас на конференции:
🔘6 направлений: от аналитики до разработки (программа тут)
🔘Больше 70 докладов про инструменты, кейсы, "боли" IT-сообщества, AI и будущее
🔘Обсуждение трендов в IT
🔘Общение с единомышленниками и обмен опытом
🔘Интерактивы и призы

Вечером первого дня — афтепати с живой музыкой (“Стихи на окнах подъезда №8”) и дискотекой.

🎁 Кстати, для вас скидка 30% по промокоду IVANOVA при покупке билетов на сайте uic.dev/#tickets.

Не пропустите — будет интересно! Увидимся на UIC Dev и пообщаемся лично! 😉

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

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

Часто нормализацию сознательно нарушают для повышения производительности системы, так как большое количество 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

QUALIFY: фильтруем результаты оконных функций

QUALIFY — SQL-конструкция, которая позволяет отфильтровать результаты после применения оконных функций 😍

Она работает аналогично WHERE, но с той разницей, что QUALIFY применяется после оконных функций, а WHERE — до них.

Напомню порядок выполнения запроса:
1. From
2. Where
3. Group by
4. Having
5. Window
6. QUALIFY
7. Distinct
8. Order by
9. Limit

Представим, что у нас есть таблица продаж, и мы хотим выбрать топ-5 продаж по каждой категории товаров. С помощью QUALIFY это можно сделать просто и эффективно:

SELECT
    category,
    product,
    sales_amount,
    ROW_NUMBER() OVER (PARTITION BY category ORDER BY sales_amount DESC) AS rn
FROM sales
QUALIFY rn <= 5;

В этом запросе сначала нумеруем продажи в каждой категории по убыванию суммы продаж с помощью ROW_NUMBER(). Затем с помощью QUALIFY оставляем только первые пять записей в каждой категории. Легко и понятно.

Почему нельзя использовать WHERE вместо QUALIFY? Потому что WHERE фильтрует данные до выполнения оконных функций, а нам нужно отфильтровать данные после. Если попробовать использовать WHERE rn <= 5, SQL не поймёт, что такое rn, потому что на этапе выполнения WHERE эта колонка ещё не создана.

Конечно, можно использовать QUALIFY и с другими оконными функциями, например, RANK():

SELECT
    employee_id,
    department,
    salary,
    RANK() OVER (PARTITION BY department ORDER BY salary DESC) 
                  AS salary_rank
FROM employees
QUALIFY salary_rank = 1;

Этот запрос выберет сотрудников с наивысшей зарплатой в каждом отделе.

Стоит отметить, что QUALIFY поддерживается не во всех СУБД. Например, в Snowflake и Teradata эта функция есть, а в PostgreSQL или MySQL её нет. В таких случаях приходится использовать подзапросы или CTE (Common Table Expressions).

Например так:

WITH ranked_sales AS (
    SELECT
        category,
        product,
        sales_amount,
        RANK() OVER (PARTITION BY category ORDER BY sales_amount DESC) AS rn
    FROM sales
)
SELECT *
FROM ranked_sales
WHERE rn <= 5;

Согласитесь, что использование QUALIFY делает код более кратким и читаемым. Используйте его, когда это возможно 😎

#sql

На прошлой неделе я выступала на конференции UIC Dev с докладом "Автоматизируй это: как мы построили DWH для международного холдинга командой из 6 человек". Это был мой первый опыт участия в качестве спикера и, кажется, я справилась неплохо. Хочется зафиксировать для себя этот момент.

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

Несмотря на то, что конференция была "всё обо всём" и в какой-то момент мы стали волноваться, что тема DWH не вызовет большого интересна, нас приняли отлично и вопросы задавали хорошие и к месту. Это ценно! Спасибо 🙂

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

Спасибо UIC Dev. Отличный опыт, было интересно 🐱

#life

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

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

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

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

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

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


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

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

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

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


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

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


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

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

#dwh

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

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

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

Будьте здоровы 🍀

#life