Расскажи про различиях в моделях данных

Различия в моделях данных

Основные модели данных

Данные можно организовать по-разному в зависимости от целей и задач. Основные модели:

1. Star Schema (Звёздная схема)

Структура: Центральная таблица фактов (facts) окружена таблицами измерений (dimensions).

        Dim_Product
            ↑
            |
Dim_Date ← Fact_Sales → Dim_Customer
            |
            ↓
        Dim_Store

Пример:

CREATE TABLE Fact_Sales (
    sales_id INT PRIMARY KEY,
    product_id INT FOREIGN KEY,
    customer_id INT FOREIGN KEY,
    store_id INT FOREIGN KEY,
    date_id INT FOREIGN KEY,
    amount DECIMAL(10,2),
    quantity INT
);

CREATE TABLE Dim_Product (
    product_id INT PRIMARY KEY,
    product_name VARCHAR,
    category VARCHAR,
    price DECIMAL(10,2)
);

CREATE TABLE Dim_Date (
    date_id INT PRIMARY KEY,
    date DATE,
    month INT,
    year INT,
    quarter INT
);

Преимущества:

• Простые и быстрые запросы
• Хорошо денормализована для BI
• Понятна бизнес-пользователями

Недостатки:

• Данные дублируются в Dimensions
• Сложнее обновлять данные

2. Snowflake Schema (Снежинка)

Структура: Более нормализованная, чем Star. Dimensions разбиты на подтаблицы.

     Dim_Product_Category
            ↑
     Dim_Product
            ↑
            |
Dim_Date ← Fact_Sales → Dim_Customer
            |
            ↓
     Dim_Store_Region
            ↑
     Dim_Store

Пример:

CREATE TABLE Dim_Product (
    product_id INT PRIMARY KEY,
    product_name VARCHAR,
    category_id INT FOREIGN KEY
);

CREATE TABLE Dim_Category (
    category_id INT PRIMARY KEY,
    category_name VARCHAR
);

CREATE TABLE Dim_Store (
    store_id INT PRIMARY KEY,
    store_name VARCHAR,
    region_id INT FOREIGN KEY
);

CREATE TABLE Dim_Region (
    region_id INT PRIMARY KEY,
    region_name VARCHAR
);

Преимущества:

• Меньше дублирования данных
• Экономит место на диске
• Проще обновлять данные

Недостатки:

• Больше JOINов в запросах
• Медленнее для больших аналитических запросов

3. Slowly Changing Dimension (SCD)

Отслеживание изменений в измерениях.

SCD Type 1: перезапись старых данных

-- До обновления
Dim_Product: id=1, name="iPhone", price=800

-- Обновили цену
Dim_Product: id=1, name="iPhone", price=900
-- Старая история потеряна!

SCD Type 2: новая версия записи

Dim_Product:
id=1, product_id=100, name="iPhone", price=800, valid_from=2024-01-01, valid_to=2024-06-30, is_current=true
id=2, product_id=100, name="iPhone", price=900, valid_from=2024-07-01, valid_to=9999-12-31, is_current=true

-- Сохраняем историю!

SCD Type 3: добавляем предыдущее значение

Dim_Product:
id=1, name="iPhone", 
price_current=900, price_previous=800,
date_updated=2024-07-01

4. Fact Table Types

Additive Facts (суммируемые)

# Можно суммировать
SUM(amount) by date  # Ok
SUM(quantity) by store  # Ok

Semi-additive Facts (частично суммируемые)

# Можно суммировать только по некоторым измерениям
SUM(balance) by account  # Ok (по счётам)
SUM(balance) by date  # Not Ok (баланс не суммируется по датам)

Non-additive Facts (несуммируемые)

# Нельзя суммировать
SUM(price) by product  # Not Ok
SUM(percentage) by month  # Not Ok

5. Dimensional Modeling vs Transactional Modeling

Dimensional (для BI/аналитики):

Оптимизирована на READ
Цель: быстрые аналитические запросы
Использует: Star/Snowflake Schema
Денормализована

Transactional (для OLTP):

Оптимизирована на WRITE
Цель: быстрые вставки/обновления отдельных записей
Использует: 3NF нормализацию
Много JOIN при чтении

Пример:

-- Transactional Model (PostgreSQL)
CREATE TABLE users (
    id INT PRIMARY KEY,
    email VARCHAR UNIQUE,
    name VARCHAR
);

CREATE TABLE orders (
    id INT PRIMARY KEY,
    user_id INT FOREIGN KEY,
    amount DECIMAL,
    created_at TIMESTAMP
);

-- Запрос требует JOIN
SELECT u.name, COUNT(*) as order_count
FROM users u
JOIN orders o ON u.id = o.user_id
GROUP BY u.name;

-- Dimensional Model (Data Warehouse)
CREATE TABLE Fact_Orders (
    order_id INT,
    customer_id INT,
    date_id INT,
    product_id INT,
    amount DECIMAL,
    quantity INT
);

CREATE TABLE Dim_Customer (
    customer_id INT,
    customer_name VARCHAR,
    email VARCHAR,
    segment VARCHAR  -- Pre-calculated!
);

-- Запрос простой и быстрый
SELECT c.customer_name, SUM(f.amount)
FROM Fact_Orders f
JOIN Dim_Customer c ON f.customer_id = c.customer_id
GROUP BY c.customer_name;

6. Time-Bucketing Dimension

Предварительно вычисленная таблица для дат.

CREATE TABLE Dim_Date (
    date_id INT PRIMARY KEY,
    calendar_date DATE,
    year INT,
    quarter INT,
    month INT,
    day INT,
    week INT,
    day_of_week INT,
    is_weekend BOOLEAN,
    is_holiday BOOLEAN,
    fiscal_year INT
);

-- Вместо вычисления для каждой строки
SELECT YEAR(date) FROM Fact_Sales  -- медленно

-- Просто читаем из Dimension
SELECT year FROM Dim_Date WHERE date_id = fact.date_id  -- быстро

7. Conformed Dimensions

Одна Dimension используется несколькими Fact таблицами.

Fact_Sales ← Dim_Product → Fact_Inventory
Fact_Returns ↑

-- Одна таблица Dim_Product для всех фактов
Fact_Sales.product_id → Dim_Product.product_id
Fact_Inventory.product_id → Dim_Product.product_id
Fact_Returns.product_id → Dim_Product.product_id

Сравнение моделей

Когда что использовать?

Star Schema:

• Хранилище данных для BI
• Большие объёмы данных
• Простые отчёты

Snowflake:

• Когда место критично
• Много обновлений
• Нужна нормализация

3NF:

• Трансакционные системы (OLTP)
• Много INSERT/UPDATE
• Мало аналитических запросов

Заключение

Выбор модели данных критичен:

• OLTP (PostgreSQL): 3NF
• OLAP (Data Warehouse): Star/Snowflake
• Real-time: Event-based
• Modern: Data Mesh с Conformed Dimensions