Scenario: Slowly Changing Dimension (SCD Type 2) Ingestion

How can we maintain historical attribute changes with effective_date, end_date, and is_current flags (SCD Type 2)?

Production⏱ 3 Minutes⭐ 840 XP

🤔 THE QUESTION

How can we maintain historical attribute changes with effective_date, end_date, and is_current flags (SCD Type 2)?

💡 WHAT IS IT?

Comparing incoming dimension updates with current records to close out old versions (end_date) and insert new versions.

🎯 WHAT IS IT USED FOR?

Tracking customer address changes, sales rep territory reassignments, and product price history in data warehouses.

💻 EXAMPLE

df_updates = df_incoming.withColumn("effective_date", current_date()).withColumn("end_date", to_date(lit("9999-12-31"))).withColumn("is_current", lit(True))

🎯 Mission Objectives

Practice typing production-grade PySpark code for Scenario: Slowly Changing Dimension (SCD Type 2) Ingestion.

✓ SCD Type 2 pattern
✓ effective_date and end_date handling
✓ Historical dimension tracking

🚀 Start Mission

← Prev (Scenario: E-Commerce Funnel Conversion Analytics)Next Lesson (Scenario: IoT Sensor Telemetry Anomaly Detection) →