dimensional-modeling

Star schema design starts with a central fact table containing foreign keys and measures, surrounded by denormalized dimension tables. Define the grain first (e.g., one row per order line), then add surrogate keys, dimensions like customer and date, and numeric measures such as revenue and quantity.

A star schema uses denormalized dimensions requiring only 1-2 joins, making queries fast but storing redundant data. A snowflake schema normalizes dimensions into sub-tables, reducing redundancy at the cost of additional joins and more complex queries.

SCD Type 2 preserves history by closing the current row (setting end_date and is_current = FALSE) and inserting a new row with the updated attribute values. Each dimension row includes start_date, end_date, and is_current columns to track validity periods.

Use aggregation tables when fact table detail is too granular for dashboard queries, causing slow aggregations. Pre-aggregate measures by common dimensions like day, product, and region so summary queries scan far fewer rows.

Common mistakes include using too many dimensions (keep to 10-15 key ones), making fact tables overly detailed without aggregation tables, and joining on business keys instead of integer surrogate keys. Each of these degrades query performance significantly.