Data Modeling & Schema Design

Моделирование данных для аналитики

В OLTP мы нормализуем (3NF). В аналитике правила другие — приоритет скорость чтения и простота запросов, а не экономия storage.

Normalized vs Denormalized: Trade-offs

Normalized (3NF):
  orders: order_id, customer_id, product_id, amount
  customers: customer_id, name, city_id
  cities: city_id, city_name, country_id
  
  [OK] No data redundancy
  [OK] Easy updates (one place)
  [NO] 3+ JOINs для простого отчёта
  [NO] Медленно для аналитики (OLAP)

Denormalized (Star/Wide):
  orders_wide: order_id, customer_name, city, country, 
               product_name, category, amount, order_date
  
  [OK] 0 JOINs — SELECT + WHERE + GROUP BY
  [OK] Columnar storage (Parquet) оптимизирован под это
  [NO] Data redundancy (customer_name repeated)
  [NO] Updates сложнее (SCD needed)

Wide Tables

Wide table — денормализованная таблица с десятками/сотнями колонок. Все данные в одном месте:

orders_wide (50+ columns):
  -- Order facts
  order_id, order_date, amount, quantity, discount
  -- Customer (denormalized)
  customer_id, customer_name, customer_segment, customer_city
  -- Product (denormalized)  
  product_id, product_name, category, subcategory, brand
  -- Store
  store_id, store_name, store_region
  -- Derived
  is_first_order, days_since_last_order, customer_ltv

Преимущество: аналитик пишет простой SQL без JOINs
Недостаток: 50+ columns × billions rows = storage cost

OBT — One Big Table

Экстремальная денормализация: все факты и все dimensions в одной таблице.

OBT: one_big_table
  [OK] Zero JOINs
  [OK] Максимальная производительность для BI
  [OK] Простота для end users
  [NO] Massive redundancy
  [NO] Schema evolution болезненна
  [NO] Rebuild при любом изменении

When OBT makes sense:
  - Read-heavy BI dashboards (95%+ reads)
  - Limited dimensions (до 20 таблиц)
  - Batch rebuild acceptable (daily/hourly)
  - Team без SQL expertise (drag & drop BI)

Schema Evolution Strategies

Safe schema evolution workflow:
  1. Add new column (nullable, with default)
  2. Dual-write: populate old + new columns
  3. Migrate consumers to new column
  4. Deprecate old column (mark in docs)
  5. Drop old column after grace period

Duration: days to weeks depending on consumers

Naming Conventions

Tables:
  Prefix: fact_, dim_, stg_, raw_
  Format: snake_case, singular (dim_customer, not DimCustomers)
  
Columns:
  IDs: {entity}_id (customer_id, order_id)
  Dates: {event}_at (created_at, updated_at) или _date для date-only
  Booleans: is_{condition} (is_active, is_deleted)
  Amounts: {metric}_amount (order_amount, discount_amount)
  Counts: {entity}_count (item_count)

Derived/Computed:
  Prefix: calc_ или _derived suffix
  calc_customer_ltv, revenue_derived

Выбор модели: Decision Framework

Вопрос 1: Сколько источников?
  1-3 → Star Schema
  10+ → Data Vault (Hub/Sat/Link)

Вопрос 2: Кто потребитель?
  BI analysts → Star/OBT (простой SQL)
  Data Scientists → Wide tables (feature-ready)
  ML Pipelines → Feature store format

Вопрос 3: Как часто schema changes?
  Редко → Star Schema + SCD Type 2
  Часто → Iceberg (add/drop/rename без rewrite)

Вопрос 4: Latency requirements?
  Batch (hourly/daily) → Any model works
  Near-real-time → Pre-aggregated materialized views