34. Build an SCD Type 2 History

Difficulty: Hard · Topics: Window Functions, Null Handling, Filtering & Selection

customer_updates is a change log: every time a customer's record was saved, a row with their city and updated_at. Saves often repeat the same city.

Build a Slowly Changing Dimension Type 2 table: one row per actual change of city, with valid_from (when it took effect), valid_to (when the next change took effect, null for the current row) and is_current.

Return customer_id, city, valid_from, valid_to, is_current.

Row order does not matter; column names must match. Your code is graded on 3 test cases, including hidden edge cases.

Sample data

customer_updates

customer_id	city	updated_at
1	Pune	2024-01-01
1	Pune	2024-02-01
1	Delhi	2024-03-01
2	Goa	2024-01-15

Expected output

customer_id	city	valid_from	valid_to	is_current
1	Pune	2024-01-01	2024-03-01	false
1	Delhi	2024-03-01	null	true
2	Goa	2024-01-15	null	true

Hints

Hint 1

First drop saves that didn't change the city: compare with F.lag("city") per customer ordered by updated_at.

Hint 2

On the remaining rows, F.lead("updated_at") is the end of each version.

Hint 3

is_current is simply "valid_to is null".

PySpark functions you'll practise

• Lag
• Lead
• Window spec
• isNull
• filter
• select
• orderBy

Related problems

• Sessionize a Clickstream · Hard · Window Functions
• Latest Order per Customer · Medium · Window Functions
• Top Two Salary Levels per Department · Medium · Window Functions
• Three-Day Login Streak · Hard · Window Functions
• Trailing 3 Calendar Days (Range Frame) · Hard · Window Functions

Browse

Topics: Window Functions · Joins · Aggregations · Pivot, Unpivot & Rollup · Arrays · Null Handling · Conditional Logic · Dates · Filtering & Selection · Strings

Difficulty: Easy · Medium · Hard · PySpark interview roadmap · All problems