Data Warehousing & Modeling

What it is

Data warehouse modeling organizes data for fast analytical queries (aggregations over large volumes) rather than the transactional read/write patterns an OLTP database is optimized for.

Key points

• Fact tables: hold measurable events (a sale, a click) plus foreign keys to dimensions — typically large and append-heavy.
• Dimension tables: hold descriptive attributes (customer, product, date) that facts join against — typically smaller and slower-changing.
• Star schema: dimensions are denormalized (flat), optimizing for simpler, faster joins at the cost of some redundancy.
• Snowflake schema: dimensions are further normalized into sub-dimensions, saving storage at the cost of more joins — the classic normalization-vs-query-simplicity tradeoff, just applied to analytics instead of OLTP.