Skip to content
LakeBench
ProblemsCommunityPricing
Sign inStart practicing
Back
  1. Home
  2. Interview prep
  3. SCD overview: Type 1, 2, and 3

Data modeling · Foundations

SCD overview: Type 1, 2, and 3

Mediummodel-08
SCDSCD1SCD2SCD3dimensions

Question

What are Slowly Changing Dimensions? Explain Type 1, Type 2, and Type 3.

Solution

Slowly Changing Dimensions (SCD) describe how dimension attributes change over time (customer city, product category, employee department).

Type 1: overwrite

Replace the old value. No history.

customer_id=7 city: Austin -> Dallas
table now shows Dallas only

Type 2: new row (history)

Insert a new version row; keep old rows with validity windows (valid_from / valid_to or is_current).

customer_id=7
row1: Austin  valid 2024-01-01 to 2026-03-01  is_current=false
row2: Dallas  valid 2026-03-01 to null        is_current=true

Type 3: previous value column

Keep limited history in columns like current_city and previous_city (or city and city_prior). Only one prior value (or a fixed few), not full history.

customer_id=7  city=Dallas  previous_city=Austin

Quick compare

| Type | History | Typical use | |---|---|---| | 1 | None | Corrections, typos | | 2 | Full (row versions) | "What city when they ordered?" | | 3 | Limited (columns) | Need prior + current only |

Interview tip

Lead with Type 1 vs Type 2; mention Type 3 as "prior value columns for limited history."

PreviousNext