Course contentsShow
← Data Science
Lesson 1809 of 2,14542. Working with Big DataFree lesson

Dimension Tables and Slowly Changing Dimensions

Explore dimension tables for descriptive attributes and strategies for handling dimension changes over time.

Dimension Tables and Slowly Changing Dimensions

You'll learn how dimension tables store descriptive attributes and strategies for handling changes to those attributes over time.

What Are Dimension Tables?

In a data warehouse, dimension tables hold the descriptive, contextual information that gives meaning to your metrics. While fact tables (covered in the previous lesson) store measurements and events, dimension tables answer "who," "what," "where," and "when" about those facts.

For example, a Customer dimension might include attributes like customer name, address, segment, and loyalty tier. A Product dimension could store product name, category, brand, and price tier.

The Challenge: Slowly Changing Dimensions (SCD)

Here's the problem: dimensions change over time. A customer moves to a new city. A product gets reclassified into a different category. How do you handle these changes while maintaining historical accuracy?

Think of it like a photo album versus a live stream. Do you want to remember what things were at the time of each event, or only what they are now?

Common SCD Strategies

Type 0 (Fixed): Never update. Original values remain forever.

Type 1 (Overwrite): Replace the old value with the new one. Simple but loses history. If a customer moves from Chicago to Boston, you overwrite "Chicago" → "Boston" and can't analyze past behavior by original location.

Type 2 (Add New Row): Create a new row with the updated attributes, keeping the old row. Add effective dates or version flags to track validity periods. Most common for preserving full history.

Type 3 (Add Column): Store both current and previous values in separate columns (e.g., current_city, previous_city). Limited to one or two historical snapshots.

The choice depends on your analytics needs: Do you need full history (Type 2)? Just current state (Type 1)? Limited rollback capability (Type 3)?

Key Takeaway: Dimension tables provide descriptive context for facts; Slowly Changing Dimension (SCD) strategies like Type 1 (overwrite), Type 2 (versioning), and Type 3 (limited history columns) determine how you preserve or discard historical attribute changes.