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

Ранжирующие функции в 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

Открыт набор в новый сезон крутой бесплатной менторской программу от 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 и пообщаемся лично! 😉