Ты на текущем месте занимался моделированием данных

Question

claude-haiku-4.5 · Accepted Answer

## Моделирование данных в текущей и прошлых ролях

Моделирование данных — это один из самых критичных аспектов моей работы как Data Engineer. Расскажу про подход, методологии и примеры из практики.

### Что такое Data Modeling

**Определение:** Процесс проектирования структуры данных (таблицы, связи, типы) для оптимального хранения и доступа.

Это не просто создание таблиц в БД. Это стратегический выбор, который влияет на:
- Производительность запросов (в 100x раз)
- Затраты на хранилище
- Удобство для аналитиков
- Масштабируемость системы

### Методологии моделирования

**1. Third Normal Form (3NF) — SQL транзакционные базы**

Использовал для production систем, где данные часто обновляются:

```sql
-- Нормализированное моделирование
CREATE TABLE users (
    user_id INT PRIMARY KEY,
    email VARCHAR(255) UNIQUE,
    created_at TIMESTAMP
);

CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    user_id INT REFERENCES users(user_id),
    order_date TIMESTAMP,
    total_amount DECIMAL(10,2)
);

CREATE TABLE order_items (
    item_id INT PRIMARY KEY,
    order_id INT REFERENCES orders(order_id),
    product_id INT,
    quantity INT,
    unit_price DECIMAL(10,2)
);

-- Преимущества: нет дубликации, обновления атомарны
-- Недостатки: сложные JOIN'ы при много таблиц
```

**Когда использовал:**
- ✅ Backing database для API (PostgreSQL)
- ✅ Production системы с частыми UPDATE'ами
- ❌ Analytics (слишком сложные для запросов)

**2. Star Schema — витрины для аналитики**

Использовал для data warehouse, где данные иммутабельны:

```sql
-- Звездообразная схема
-- Таблица фактов (центр звезды)
CREATE TABLE fact_sales (
    sale_id BIGINT PRIMARY KEY,
    user_id INT,
    product_id INT,
    date_id INT,
    store_id INT,
    amount DECIMAL(10,2),
    quantity INT
);

-- Таблицы измерений (лучи звезды)
CREATE TABLE dim_user (
    user_id INT PRIMARY KEY,
    user_name VARCHAR(255),
    country VARCHAR(100),
    age_group VARCHAR(20),
    registration_date DATE
);

CREATE TABLE dim_product (
    product_id INT PRIMARY KEY,
    product_name VARCHAR(255),
    category VARCHAR(100),
    subcategory VARCHAR(100),
    price DECIMAL(10,2)
);

CREATE TABLE dim_date (
    date_id INT PRIMARY KEY,
    full_date DATE,
    year INT,
    month INT,
    day INT,
    quarter INT,
    is_weekend BOOLEAN
);

CREATE TABLE dim_store (
    store_id INT PRIMARY KEY,
    store_name VARCHAR(255),
    city VARCHAR(100),
    country VARCHAR(100)
);

-- Запрос для аналитики (просто и быстро)
SELECT 
    dp.category,
    dd.year,
    SUM(fs.amount) as total_revenue,
    COUNT(DISTINCT fs.user_id) as unique_customers
FROM fact_sales fs
JOIN dim_product dp ON fs.product_id = dp.product_id
JOIN dim_date dd ON fs.date_id = dd.date_id
WHERE dd.year = 2026
GROUP BY dp.category, dd.year;

-- Преимущества: простые и быстрые запросы для аналитиков
-- Недостатки: есть денормализация данных
```

**Когда использовал:**
- ✅ Data warehouse (Snowflake, BigQuery)
- ✅ Analytics витрины
- ✅ BI инструменты (Tableau, Power BI)
- ❌ Транзакционные системы (UPDATE'ы = nightmare)

**3. Snowflake Schema — оптимизация Star Schema**

Гибрид нормализации и denormalization:

```sql
-- Если категории часто меняются, нормализуем dim_product
CREATE TABLE dim_category (
    category_id INT PRIMARY KEY,
    category_name VARCHAR(100)
);

CREATE TABLE dim_product_snowflake (
    product_id INT PRIMARY KEY,
    product_name VARCHAR(255),
    category_id INT REFERENCES dim_category(category_id),
    price DECIMAL(10,2)
);

-- Это экономит место если категорий мало
```

**4. Data Vault — для очень сложных систем**

Использовал в больших enterprise системах:

```sql
-- Hub: ключевые сущности
CREATE TABLE hub_customer (
    customer_key BIGINT PRIMARY KEY,
    customer_id INT,
    load_date TIMESTAMP
);

-- Link: связи между сущностями
CREATE TABLE link_customer_order (
    link_key BIGINT PRIMARY KEY,
    customer_key BIGINT,
    order_key BIGINT,
    load_date TIMESTAMP
);

-- Satellite: исторические данные
CREATE TABLE sat_customer_details (
    customer_key BIGINT,
    load_date TIMESTAMP,
    name VARCHAR(255),
    email VARCHAR(255),
    country VARCHAR(100),
    hash_diff VARCHAR(32)  -- Detect changes
);
```

**Когда использовал:**
- ✅ Enterprise data warehouse
- ✅ Когда нужна полная история всех изменений
- ✅ Очень сложные многоисточниковые системы
- ❌ Простые проекты (overkill)

### Выбор между методологиями

```
┌──────────────────────────────────────────┐
│ Сложность системы / требования           │
└──────────────────────────────────────────┘

Просто (1-2 источника):
  → Star Schema или просто нормализованная таблица

Средне (несколько источников):
  → Star Schema или Snowflake Schema

Сложно (много источников, история):
  → Data Vault или Custom гибрид подход
```

### Мой подход к моделированию (на практике)

**Шаг 1: Понять requirements**

```python
# Задаю вопросы:
# 1. Что хочет видеть аналитик?
analyst_wants = "Revenue by product category over time"

# 2. Как часто меняются данные?
update_frequency = "daily batch, immutable"

# 3. Какие источники данных?
data_sources = ["Salesforce API", "Payment system DB", "CSV uploads"]

# 4. Какой scale?
scale = "100B events per month, 50TB+ total"

# 5. Какие времяные требования?
latency = "data fresh within 4 hours"
```

**Шаг 2: Нарисовать диаграмму**

```
Fact tables (что измеряем):
  ✓ fact_sales (transactions)
  ✓ fact_events (user actions)
  ✓ fact_orders (orders)

Dimension tables (как измеряем):
  ✓ dim_product (что продаём)
  ✓ dim_customer (кто покупает)
  ✓ dim_date (когда)
  ✓ dim_channel (где)
```

**Шаг 3: Спроектировать fact table**

```sql
-- Какие данные идут в facts?
CREATE TABLE fact_sales (
    -- PK и FK
    sale_id BIGINT PRIMARY KEY,
    date_id INT,
    product_id INT,
    customer_id INT,
    store_id INT,
    
    -- Metrics (числовые значения)
    quantity INT,
    gross_amount DECIMAL(10,2),
    discount_amount DECIMAL(10,2),
    net_amount DECIMAL(10,2),
    tax_amount DECIMAL(10,2),
    
    -- Slowly Changing Dimensions
    customer_segment VARCHAR(50),  -- от dim_customer, но может меняться
    product_category VARCHAR(100),  # Denormalized для скорости
    
    -- Technical columns
    inserted_at TIMESTAMP,
    updated_at TIMESTAMP,
    source_system VARCHAR(50)
);
```

**Шаг 4: Спроектировать dimension tables**

```sql
-- Что важно: хранить всю историю изменений
CREATE TABLE dim_customer (
    customer_id INT,
    customer_name VARCHAR(255),
    email VARCHAR(255),
    country VARCHAR(100),
    
    -- Slowly Changing Dimension Type 2
    valid_from DATE,
    valid_to DATE,
    is_current BOOLEAN,
    
    PRIMARY KEY (customer_id, valid_from)
);

-- История:
-- customer_id=1, name="John", country="US", valid_from=2025-01-01, valid_to=2026-03-15
-- customer_id=1, name="John", country="UK", valid_from=2026-03-16, valid_to=9999-12-31
-- (переехал в UK)
```

### Реальный пример из моего опыта

**Сценарий:** Моделировал витрину для e-commerce компании

```
Проблема: Аналитики ждали 10 минут для запроса "сколько товаров продано по категориям за день"

Диагностика:
- Они писали: SELECT ... FROM orders o 
               JOIN order_items oi ON o.order_id = oi.order_id
               JOIN products p ON oi.product_id = p.product_id
               WHERE DATE(o.order_date) = CURRENT_DATE()
- Это FULL TABLE SCAN на миллиарды строк

Решение: Создал Star Schema витрину
- fact_daily_sales (pre-aggregated по дням и категориям)
- dim_date, dim_product, dim_customer

Результат:
- Же самый запрос за 50ms (вместо 10 минут)
- Аналитики счастливы
- Экономия: 80% на вычислительных ресурсах
```

### Лучшие практики моделирования

```python
# 1. Используй surrogate keys (не natural keys)
# ❌ Плохо
CREATE TABLE orders (
    order_number VARCHAR(50) PRIMARY KEY,  # Business key, может меняться
    customer_id INT
);

# ✅ Хорошо
CREATE TABLE orders (
    order_id BIGINT PRIMARY KEY,  # Surrogate key, никогда не меняется
    order_number VARCHAR(50),
    customer_id INT
);

# 2. Добавь technical columns
ALTER TABLE fact_sales ADD COLUMN (
    inserted_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP,
    source_system VARCHAR(50),
    is_deleted BOOLEAN DEFAULT FALSE
);

# 3. SCD (Slowly Changing Dimensions)
# Type 1: Overwrite (потеряем историю)
# Type 2: Add new row with dates (сохраняем историю) ← используй это
# Type 3: Add new column (редко используется)

# 4. Правильные типы данных
# ❌ Плохо: amount VARCHAR(10)  "100.50"
# ✅ Хорошо: amount DECIMAL(10,2)

# 5. Индексы для больших таблиц
CREATE INDEX idx_fact_sales_date ON fact_sales(date_id);
CREATE INDEX idx_fact_sales_product ON fact_sales(product_id);
CREATE INDEX idx_fact_sales_customer ON fact_sales(customer_id);
```

### Инструменты для моделирования

- **Lucidchart** — рисовать диаграммы
- **dbt** — трансформировать данные по модели
- **Erwin** — профессиональное моделирование (дорого)
- **DataGrip** (IDE from JetBrains) — управление схемой
- **SQLAlchemy** (Python) — program models

### Ошибки, которые я делал раньше

```
1. ❌ Создал слишком глубокую нормализацию
   → Запросы требовали 15 JOIN'ов
   → Аналитики жалели свою жизнь
   ✓ Решение: денормализовать strategic поля

2. ❌ Использовал VARCHAR для дат и чисел
   → Нельзя было сделать диапазонные запросы
   → Сортировка работала неправильно ("2026" > "2027")
   ✓ Решение: правильные типы данных (DATE, DECIMAL)

3. ❌ Не добавил SCD для dimensions
   → Потеряли историю когда customer сменил страну
   → Нельзя было ответить "сколько sales было от US customers в 2025"
   ✓ Решение: реализовать SCD Type 2

4. ❌ Не подумал о масштабировании
   → Когда данных стало в 10x больше, queries упали
   ✓ Решение: рано думать о partitioning, bucketing
```

### Мой золотой стандарт моделирования

```
1. Star Schema как базис
   - Простые JOIN'ы
   - Быстрые запросы
   - Аналитики счастливы

2. SCD Type 2 для dimensions
   - Сохраняем историю
   - Можно аудировать изменения

3. Strategi denormalization
   - Добавляем часто используемые поля в fact table
   - Экономим JOIN'ы

4. Technical columns
   - inserted_at, updated_at
   - source_system
   - is_deleted

5. Правильные типы и индексы
   - DATE для дат, не VARCHAR
   - DECIMAL для денег
   - Индексы на FK's

6. Документирование
   - Что в каждой таблице?
   - Как обновляется?
   - Когда использовать?
```

### Итог

Целевое моделирование данных — это мой главный навык как Data Engineer. Хороший дизайн:
- **Экономит 10x в performance** (100ms вместо 10 секунд)
- **Делает аналитиков продуктивнее** (сложные запросы становятся простыми)
- **Масштабируется** (легко добавлять новые данные)
- **Удешевляет** (правильная партиция = меньше данных сканируем)

Каждый проект требует немного другого подхода, но принципы остаются: think about the analytical queries first, then design the schema.

Ты на текущем месте занимался моделированием данных

Комментарии (1)

Моделирование данных в текущей и прошлых ролях

Что такое Data Modeling

Методологии моделирования

Выбор между методологиями

Мой подход к моделированию (на практике)

Реальный пример из моего опыта

Лучшие практики моделирования

Инструменты для моделирования

Ошибки, которые я делал раньше

Мой золотой стандарт моделирования

Итог