Data Vault: революция в организации корпоративных хранилищ данных

Теперь, когда мы разобрались с основными терминами Data Vault, давайте рассмотрим, как эта методология работает. Она сочетает в себе уже знакомую вам "звезду" и 3-ю нормальную форму (о которой я подробно ещё здесь не написала 😁).

Методологию разработал Дэн Линстедт в 2000 году, и это стало настоящим прорывом в организации корпоративных хранилищ. Его целью было создать метод, сочетающий гибкость Кимбалла и надежность Инмона. И у него получилось!

Сегодня существует две версии Data Vault: 1.0 и 2.0. Различия между ними мы обсудим в следующих статьях, а сейчас осветим общие моменты.

Data Vault помогает справиться с проблемами, которые часто возникают при работе с большими объемами информации из разных источников.

Когда новые данные попадают в хранилище (про ETL-ELT проговорим ещё раз позже), они распределяются по Hub, Link и Satellite таблицам. Хабах хранят только уникальные бизнес-ключи. В Линках — связи между хабами, а в Сателлитах содержатся атрибуты, описывающие хабы и линки.

Главная фишка Data Vault — его гибкость. Вы можете добавлять новые данные, не ломая то, что уже построено.

Также Data Vault отлично справляется с хранением истории изменений. Вы всегда можете "отмотать" данные назад и увидеть, как они выглядели в любой момент времени. Это особенно полезно для анализа трендов или аудита.

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

Но у Data Vault есть и свои сложности. Его внедрение требует тщательного планирования и может занять много времени. Дело в том, что Data Vault использует концепцию "бизнес-ключей" вместо суррогатных ключей, что позволяет легко интегрировать данные из разных систем. Но при этом очень усложняет первоначальное проектирование. Поэтому очень важны специалисты, которые хорошо понимают эту методологию (иначе беды не избежать 😈).

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

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

#dwh

1 и 2 НФ: первые шаги к упорядоченным данным

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

Начнем с 1НФ.

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

Представьте, что у вас есть таблица с данными о студентах и их курсах. И в одной ячейке вы храните несколько курсов через запятую. Это нарушает 1НФ.

Пример таблицы, нарушающей 1НФ (таблицы могут некорректно отображаться на небольших телефонах 🥲 смотрите в горизонтальной ориентации):

| student_id | student_name | courses             |
|------------|--------------|---------------------|
| 1          | Иван         | Математика, Физика  |
| 2          | Марья        | Химия, Биология     |

Чтобы привести таблицу к 1НФ, нужно:
— Убрать повторяющиеся группы значений из отдельных ячеек
— Создать отдельную запись для каждого значения в исходной или новой связанной таблице
— Определить уникальный первичный ключ для каждой таблицы

Пример таблиц, приведенных к 1НФ:

| student_id | student_name |
|------------|--------------|
| 1          | Иван         |
| 2          | Марья        |

| student_id | course       |
|------------|--------------|
| 1          | Математика   |
| 1          | Физика       |
| 2          | Химия        |
| 2          | Биология     |

Теперь таблицы приведены к 1НФ, и данные структурированы таким образом, чтобы избежать дублей и обеспечить целостность данных.

2НФ строится на основе 1НФ.

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

Здесь главное избавиться от частичных зависимостей. Например, если у вас есть таблица "student_courses" с составным ключом из student_id и course_id, а поле "student_name" зависит только от student_id — это нарушение 2НФ.

Пример таблицы, нарушающей 2НФ:

| student_id | course_id | student_name | grade |
|------------|-----------|--------------|-------|
| 1          | 101       | Иван         | 5     |
| 1          | 102       | Иван         | 4     |
| 2          | 101       | Мария        | 3     |

Чтобы привести к 2НФ:
— Выделите зависимые атрибуты в отдельную таблицу
— Свяжите новую таблицу с исходной через первичный ключ

Пример таблиц, приведенных к 2НФ:

| student_id | student_name |
|------------|--------------|
| 1          | Иван         |
| 2          | Мария        |

| student_id | course_id | grade |
|------------|-----------|-------|
| 1          | 101       | 5     |
| 1          | 102       | 4     |
| 2          | 101       | 3     |

Теперь данные о студентах будут в отдельной таблице. Это уменьшит избыточность и упростит анализ информации.

Применение 1НФ и 2НФ помогает:
+ Улучшить целостность данных
+ Уменьшить избыточность
+ Упростить обновление информации

Помните, нормализация — это непрерывный процесс.

Также стоит отметить, что современные системы управления базами данных (СУБД) часто автоматизируют процесс нормализации. Например, PostgreSQL с версии 10 предлагает функции для автоматической нормализации таблиц. Но не все и не всегда ими пользуются, и не везде это работает корректно 😁 так что понимать основы нужно обязательно.

В следующий раз уделим немного внимания 3НФ.

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

#dwh

Ранжирующие функции в SQL: как создавать рейтинги и топы

Привет! Сегодня поговорим о ранжирующих оконных функциях в SQL. С ними вы легко сможете находить лучшие продукты, оценивать эффективность сотрудников или составлять списки топовых клиентов.

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

В SQL есть три основные ранжирующие функции:
- ROW_NUMBER() или простая нумерация — присваивает уникальный номер каждой строке. Даже если значения в строках одинаковы, номера будут различаться.
- RANK() или ранжирование с пропусками — присваивает одинаковый ранг строкам с одинаковыми значениями. Следующая строка получает номер с пропуском на количество одинаковых значений (т.е., например, 1 1 1 4). Можно использовать, когда важно показать, сколько объектов находится выше по рейтингу.
- DENSE_RANK() или ранжирование без пропусков — похожа на RANK(), но не пропускает номера. Если несколько строк имеют одинаковый ранг, следующая строка получит номер, идущий непосредственно за ними (1 1 1 2). Пригодится для создания категорий или групп на основе значений.

Пример ранжирования с пропусками:

SELECT 
    product_name,
    sales_amount,
    DENSE_RANK() OVER (ORDER BY sales_amount DESC) AS sales_rank
FROM product_sales;

Результат:

| product_name | sales_amount | sales_rank |
|--------------|--------------|------------|
| iPhone       | 100000       | 1          |
| MacBook      | 100000       | 1          |
| AirPods      | 80000        | 2          |
| iPad         | 60000        | 3          |

Если нужна нумерация внутри групп, необходимо скомбинировать ранжирующие функции с PARTITION BY. Например, разобъём данные на группы по категориям:

SELECT 
    category,
    product_name,
    sales_amount,
    RANK() OVER (PARTITION BY category ORDER BY sales_amount DESC) AS category_rank
FROM product_sales;

Функция присваивает ранг каждой строке в пределах группы (категории). Если две строки имеют одинаковое значение sales_amount, они получат одинаковый ранг, а следующая строка пропустит номер и возьмёт следующий. Не понятно?) Посмотрим на примере вывода:

| category | product_name   | sales_amount | category_rank |
|----------|----------------|--------------|---------------|
| Phones   | iPhone 13      | 150000       | 1             |
| Phones   | Galaxy S21     | 130000       | 2             |
| Phones   | Pixel 6        | 130000       | 2             |
| Phones   | OnePlus 9      | 90000        | 4             |
| Laptops  | MacBook Pro    | 200000       | 1             |
| Laptops  | Dell XPS       | 180000       | 2             |
| Laptops  | ThinkPad X1    | 150000       | 3             |
| Laptops  | MateBook 14    | 150000       | 3             |

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

В следующих статьях мы разберем каждую функцию подробнее и посмотрим на более сложные примеры их применения. А пока попробуйте применить их к своим данным 😉

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

Путешествие по миру современных баз данных

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

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

Фишка: поддерживают сложные запросы и гарантируют целостность данных.

Согласно отчету DB-Engines Ranking на сегодня, Oracle, MySQL и MS SQL остаются самыми популярными СУБД в мире.
══════════
NoSQL — предлагает подходы, отличные от стандартного реляционного шаблона. Они появились, когда стало ясно, что не все данные удобно хранить в таблицах. Эти СУБД бывают документоориентированные (MongoDB), ключ-значение (Redis), графовые (Neo4j). Они часто используются в веб-приложениях, системах реального времени или для работы с большими данными.

Фишка: легко масштабируются и быстро обрабатывают большие объемы данных.

MongoDB — самая популярная NoSQL база среди разработчиков по данным Stack Overflow Developer Survey 2023.
══════════
Колоночные базы данных — в них данные также организованы в таблицы, но хранятся по столбцам, а не по строкам. Отлично подходят для аналитики с большими объемами данных.

Фишка: молниеносно обрабатывают аналитические запросы на терабайтах данных.

Примеры таких СУБД: ClickHouse, Google BigQuery, Apache Cassandra.
══════════
NewSQL базы данных наследуют реляционную структуру и семантику, но построены с использованием более современных, масштабируемых конструкций, обеспечивая высокую масштабируемость и согласованность данных.

Фишка: могут обрабатывать тысячи транзакций в секунду, сохраняя при этом ACID-свойства.

Популярные системы: CockroachDB, Google Spanner, VoltDB. Они хорошо подходят для приложений, которым нужна высокая доступность и горизонтальная масштабируемость.
══════════
Многомодельные базы данных поддерживают несколько моделей данных в рамках одной системы. Они упрощают разработку сложных приложений, где нужны разные типы данных и связей между ними.

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

Пример: ArangoDB (работает с документами, графами и данные в формате ключ-значение).
══════════
Базы данных на основе блокчейна используют технологию распределенного реестра. Они обеспечивают высокую безопасность и неизменяемость данных.

Фишка: гарантируют прозрачность и защиту от несанкционированных изменений.

Примеры таких баз: BigchainDB, Bluzelle. Они популярны в финтехе, управлении цепочками поставок и других областях, где важна прозрачность и безопасность.
══════════
Хранилища данных и базы данных для аналитики оптимизированы для обработки огромных объемов данных и сложных аналитических запросов.

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

Примеры: Snowflake, Amazon Redshift, Google BigQuery.
══════════
In-Memory базы данных хранят данные в оперативной памяти, что обеспечивает молниеносную сверхвысокую скорость работы. Часто используются как кэш или для обработки данных в реальном времени, особенно в приложениях, требующих минимальной задержки.

Фишка: обеспечивают время отклика в микро- или даже наносекундах, что критично для таких приложений, как финансовые системы, системы интернет-рекламы и игровые платформы.

Самые известные представители: Redis, Memcached, SAP HANA (для более сложных аналитических задач), Apache Ignite (для распределенных вычислений и кэширования).
══════════

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

Признаюсь честно, пока писала эту статью, узнала о нескольких новых для себя видах. А вы? С чем приходилось работать? 😎

#databasedesign #dwh

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