Зачем нужен DWH?

Зачем нужен Data Warehouse (DWH)

Определение

Data Warehouse (DWH) — это централизованное хранилище данных, оптимизированное для аналитических запросов, созданное путём интеграции данных из множества источников. Это НЕ то же самое, что операционная БД.

Проблема без DWH

Представь e-commerce платформу с несколькими источниками данных:

┌─────────────────┐
│ MySQL: Orders   │ (OLTP)
├─────────────────┤
│ PostgreSQL: CRM │ (OLTP)
├─────────────────┤
│ MongoDB: Logs   │ (NoSQL)
├─────────────────┤
│ S3: Events      │ (Raw data)
└─────────────────┘

Аналитик хочет выполнить запрос: "Какая выручка от пользователей старше 30 лет по регионам за последние 30 дней?"

Без DWH:

• Нужно писать сложный JOIN через 4 разные системы
• Каждый запрос бьёт по боевым операционным БД (снижает performance)
• Нет истории данных (кто удалит запись из MySQL — она пропадёт)
• Разные форматы данных, разные временные зоны
• Каждый аналитик создаёт свой ненадёжный скрипт

Решение: Data Warehouse

┌──────────────────────────────────────────┐
│  SOURCES                                 │
│  MySQL, PostgreSQL, MongoDB, S3, API     │
└──────────────────────────────────────────┘
           ↓ ETL Pipeline (nightly)
┌──────────────────────────────────────────┐
│  STAGING LAYER                           │
│  Raw data, exactly as it comes           │
└──────────────────────────────────────────┘
           ↓ Data Cleaning & Transformation
┌──────────────────────────────────────────┐
│  WAREHOUSE LAYER                         │
│  Normalized, historical, consistent      │
│  Facts & Dimensions (Star Schema)        │
└──────────────────────────────────────────┘
           ↓ Aggregated views
┌──────────────────────────────────────────┐
│  MART LAYER                              │
│  Optimized for BI tools & Dashboards     │
└──────────────────────────────────────────┘

Основные задачи DWH

1. Интеграция данных из разных источников

Ethereum данные приходят из разных систем в разных форматах:

# Данные из MySQL (orders)
ordersDF = spark.read.jdbc(
    "jdbc:mysql://mysql.internal:3306/production",
    "orders",
    properties={"user": "dw_user", "password": "***"}
)

# Данные из S3 (raw events)
eventsDF = spark.read.parquet("s3://events-bucket/2024-03-26")

# Объединяем в единую схему
warehouse_orders = ordersDF.select(
    col("id").alias("order_id"),
    col("user_id"),
    col("amount").cast("decimal(10,2)"),
    col("created_at").cast("timestamp")
)

2. Историческое хранение данных (Slowly Changing Dimensions)

Операционная БД хранит только текущее состояние. DWH хранит всю историю:

-- Type 2: Ведём историю изменений
CREATE TABLE dim_users (
    user_key INT,           -- суррогатный ключ
    user_id INT,            -- бизнес-ключ
    email VARCHAR(255),
    city VARCHAR(100),
    age INT,
    effective_date DATE,    -- когда вступило в силу
    end_date DATE,          -- когда перестало
    is_current BOOLEAN,
    PRIMARY KEY (user_key)
);

-- История: пользователь переехал
INSERT INTO dim_users VALUES
(1, 101, 'user@example.com', 'Moscow', 25, '2024-01-01', '2024-02-28', FALSE),
(2, 101, 'user@example.com', 'SPB', 25, '2024-03-01', NULL, TRUE);

3. Оптимизация под аналитику (Star Schema)

Модель данных в DWH — это Star Schema:

-- Центральная таблица фактов (Fact Table)
CREATE TABLE fact_sales (
    sales_id BIGINT PRIMARY KEY,
    user_key INT REFERENCES dim_users,
    product_key INT REFERENCES dim_products,
    date_key INT REFERENCES dim_date,
    amount DECIMAL(10,2),
    quantity INT
);

-- Справочники (Dimension Tables)
CREATE TABLE dim_users (user_key INT PRIMARY KEY, ...);
CREATE TABLE dim_products (product_key INT PRIMARY KEY, ...);
CREATE TABLE dim_date (date_key INT PRIMARY KEY, date DATE, year INT, month INT, ...);

Почему Star Schema быстрая?

• JOIN между фактом и несколькими справочниками — очень быстро
• Денормализация справочников — кэширование в памяти
• Индексы по всем ключам

4. Разделение OLTP и OLAP

OLTP (Online Transaction Processing):      OLAP (Online Analytical Processing):
Mysql/PostgreSQL (Операционная БД)          DWH/BigQuery (Аналитика)
- Много небольших INSERT/UPDATE             - Редкие, но большие SELECT
- Строится на Normalized schema             - Денормализованная Star Schema
- Транзакции важны                          - История важна
- 100ms queries OK                          - 10 минут OK, если insights правильные
- Блокировки/Deadlocks                      - Read-only (mostly)

Боевая БД (MySQL):

SELECT * FROM orders WHERE user_id = 123;
-- Execution time: 5ms

DWH (BigQuery):

SELECT DATE_TRUNC(order_date, MONTH), SUM(amount)
FROM fact_sales
WHERE order_date >= '2023-01-01'
GROUP BY DATE_TRUNC(order_date, MONTH);
-- Execution time: 2 сек (сканит 10 млрд строк)

Архитектура современного DWH

Облачные решения

BigQuery (Google Cloud):

• Serverless, масштабируется автоматически
• Columnar storage (быстро для аналитики)
• Дешевле на больших объёмах

from google.cloud import bigquery

client = bigquery.Client()
query = """
    SELECT user_id, SUM(amount) as total
    FROM `project.dataset.sales`
    WHERE date >= '2024-01-01'
    GROUP BY user_id
    LIMIT 1000
"""
df = client.query(query).to_dataframe()

Snowflake:

• Separates storage и compute (pay only for query)
• Multi-cloud (AWS, Azure, GCP)
• Отличная производительность

-- Snowflake SQL
CREATE OR REPLACE TABLE sales_summary AS
SELECT 
    DATE_TRUNC('month', order_date) as month,
    region,
    SUM(amount) as revenue
FROM raw_sales
GROUP BY month, region;

On-premise решения

PostgreSQL + Star Schema:

# Всё работает на собственных серверах
docker-compose up postgres_dwh

Pipeline обработки данных в DWH

Day 1:
  01:00 — Extract data from sources (MySQL, S3, API)
  02:00 — Load to Staging (raw data, exactly as-is)
  03:00 — Transform: clean, normalize, combine
  04:00 — Load to Warehouse (star schema, facts & dims)
  05:00 — Build aggregated marts
  06:00 — Ready for BI tools
  
09:00 — Аналитики пишут запросы к готовым данным

Примеры использования DWH

1. Executive Dashboard:

SELECT 
    DATE_TRUNC(order_date, MONTH),
    SUM(amount) as revenue,
    COUNT(DISTINCT user_id) as customers,
    SUM(amount) / COUNT(DISTINCT user_id) as avg_ltv
FROM fact_sales
GROUP BY month;

2. Когортный анализ:

WITH cohort AS (
    SELECT 
        user_id,
        DATE_TRUNC(first_purchase_date, MONTH) as cohort_month
    FROM dim_users
)
SELECT 
    cohort_month,
    DATE_PART('month', AGE(order_date, first_purchase_date)) as months_since_purchase,
    COUNT(DISTINCT user_id) as users
FROM fact_sales f
JOIN cohort c ON f.user_id = c.user_id
GROUP BY cohort_month, months_since_purchase;

Метрики успеха DWH

• ✅ Время создания нового отчёта: было 5 дней → стало 1 день
• ✅ Согласованность данных: все видят одни цифры
• ✅ Доступность: аналитики не запрашивают data engineers
• ✅ Performance: запросы выполняются < 1 минуты
• ✅ Стоимость: Data Lake часто дешевле многих операционных БД

Заключение

Data Warehouse — это не люкс, а необходимость для компаний, работающих с данными. Без DWH аналитики бьют по боевым БД, получают противоречивые отчёты, и каждый создаёт свои ненадёжные скрипты. Хороший DWH — это основа data-driven компании.