Data Warehouse Architecture
What you'll learn: How data warehouses organize data using specialized schemas and table types to optimize analytical query performance.
Core Architecture Components
A data warehouse isn't just a big database—it's purpose-built for Online Analytical Processing (OLAP). Unlike transactional databases that handle many small writes, data warehouses excel at complex queries that aggregate massive datasets.
The architecture revolves around two table types:
Fact tables contain the measurable events or transactions—sales amounts, click counts, temperatures. These are typically enormous and append-only.
Dimension tables describe the "who, what, when, where"—customer details, product catalogs, dates. These are smaller lookup tables that give context to facts.
Schema Patterns
Star Schema is the simplest: one central fact table surrounded by dimension tables, like planets around a sun. Each dimension connects directly to the fact table. This denormalizes some data (dimension tables may repeat information) but makes queries blazingly fast because joins are simple and predictable.
Snowflake Schema normalizes dimensions further—breaking a customer dimension into separate customer, city, and country tables. This saves storage but requires more joins, slowing queries. Think of it as a star whose points have split into smaller branches.
OLAP Optimizations
Data warehouses leverage these schemas with:
- Columnar storage: storing columns together instead of rows, perfect for aggregations
- Materialized views: pre-computed aggregations of common queries
- Partition pruning: organizing data by time ranges so queries only scan relevant chunks
- Compression: similar values in columns compress extremely well
Key Takeaway: Data warehouses use star or snowflake schemas with fact and dimension tables, optimized for fast analytical queries through columnar storage, partitioning, and pre-aggregation techniques.