data-warehouse-schema

Start by identifying the business process and choosing the grain, such as daily sales transactions. Then create a central fact table holding measures and foreign keys, surrounded by de-normalized dimension tables for time, customer, product, and store.

A star schema uses de-normalized dimensions joined directly to the fact table, giving faster queries with fewer joins. A snowflake schema normalizes dimensions into sub-tables, reducing storage for large hierarchies but adding join overhead.

Expire the current dimension row by setting its expiration_date and is_current flag, then insert a new row with the updated attributes and a new surrogate key. Queries look up the row valid for a given date using effective_date and expiration_date ranges.

Use snowflake when dimension tables contain millions of rows, strong hierarchies exist such as geography or product taxonomy, or storage is constrained. Accept the trade-off of more joins and slower queries.

Natural keys like customer IDs can change over time, breaking historical references. Integer surrogate keys never change, keep fact table rows narrow, and enable SCD Type 2 history where one natural key maps to multiple dimension versions.

Insert facts against a default Unknown dimension row rather than blocking the load when a dimension lookup fails. This keeps the ETL pipeline running while preserving referential integrity until the real dimension record arrives.