Data Warehousing

Analytical Databases

While OLTP (Online Transaction Processing) databases handle day-to-day operations, data warehouses are optimized for OLAP (Online Analytical Processing) — complex queries that analyze large volumes of historical data for business intelligence.

OLTP vs. OLAP

  OLTP (Transaction)              OLAP (Analytical)
  ─────────────────────           ─────────────────────
  Current data                    Historical data
  Small, fast operations          Large, complex queries
  Many concurrent users           Few analysts
  Row-oriented storage            Column-oriented storage
  Normalized schema               Denormalized schema
  e.g., MySQL, PostgreSQL         e.g., Redshift, BigQuery

Star Schema

  The most common data warehouse design:

              ┌─────────────┐
              │  Dim_Date   │
              │  DateKey    │
              │  Year       │
              │  Month      │
              └──────┬──────┘
                     │
  ┌────────────┐    ┌┴─────────────┐    ┌────────────┐
  │ Dim_Product│────│ Fact_Sales   │────│ Dim_Customer│
  │ ProductKey │    │ DateKey      │    │ CustomerKey │
  │ Name       │    │ ProductKey   │    │ Name        │
  │ Category   │    │ CustomerKey  │    │ Region      │
  └────────────┘    │ Quantity     │    └────────────┘
                    │ Amount       │
                    └──────────────┘
                          │
                    ┌─────┴──────┐
                    │ Dim_Store  │
                    │ StoreKey   │
                    │ Location   │
                    └────────────┘

  Fact table = measurements (sales, quantities)
  Dimension tables = context (who, what, when, where)