Evolve Customer Schema with Incremental Updates

PYSPARK coding challenge · Difficulty: medium · Topic: Schema Evolution · +100 XP

A customer master table base_customers holds customer_id, name, email, start_date. A newer upstream system now sends incremental updates in customer_updates, and its schema has drifted:

the columns arrive in a different order;
email has been renamed to email_address;
two new columns, phone and address, exist only in the updates;
each update carries its own timestamp update_time (base rows use start_date).

Build one DataFrame df_result with the unified schema customer_id, name, email, phone, address, last_updated and keep exactly one row per customer -- the most recent record across both sources, compared on its timestamp (start_date for base rows, update_time for update rows; dates are YYYY-MM-DD strings). If a base row and an update have the same timestamp, the update wins. A winning base row has NULL phone and address. Do not mix columns from different records. Order by customer_id and call df_result.show().

Example Input

base_customers:

customer_id | name | email | start_date

1 | Asha | asha@old.com | 2024-01-10

2 | Ben | ben@mail.com | 2024-03-01

3 | Chen | chen@mail.com | 2024-02-15

4 | Dara | dara@mail.com | 2024-01-20

customer_updates:

2024-02-01 | 1 | asha@new.com | Asha | 555-0101 | 12 Hill St

2024-02-20 | 2 | ben@mail.com | Benjamin | 555-0102 | 9 Lake Rd

2024-01-20 | 4 | dara@mail.com | Dara | 555-0104 | 4 Elm Ave

2024-03-05 | 5 | eli@mail.com | Eli | 555-0105 | 7 Oak Ln

Expected Output

+-----------+----+-------------+--------+----------+------------+

|customer_id|name|        email|   phone|   address|last_updated|

+-----------+----+-------------+--------+----------+------------+

|          1|Asha| asha@new.com|555-0101|12 Hill St|  2024-02-01|
|          2| Ben| ben@mail.com|    NULL|      NULL|  2024-03-01|
|          3|Chen|chen@mail.com|    NULL|      NULL|  2024-02-15|
|          4|Dara|dara@mail.com|555-0104| 4 Elm Ave|  2024-01-20|
|          5| Eli| eli@mail.com|555-0105|  7 Oak Ln|  2024-03-05|

+-----------+----+-------------+--------+----------+------------+

Customer 2's update (2024-02-20) is OLDER than the base row (2024-03-01), so the base row wins and has no phone or address. Customer 4 ties on 2024-01-20, so the update wins. Customer 5 exists only in the updates.

What this PYSPARK challenge teaches you

“Evolve Customer Schema with Incremental Updates” is a medium-level PYSPARK challenge focused on Schema Evolution. Working through it gives you hands-on practice with schema evolution, unionByName, window functions, row_number, deduplication — the kind of transformation you are asked to write in real data engineering work and in technical interviews. You can solve it directly in the browser: the dataset is pre-loaded, so you write the query or DataFrame code, run it, and compare your output against the expected result immediately.

Concepts covered

schema evolution
unionByName
window functions
row_number
deduplication

How to approach it

If you get stuck, work through these steps in order before looking at a full solution — each one narrows the problem down:

Make both sides speak the same schema: rename email_address to email and give each side one timestamp column (last_updated).
unionByName(other, allowMissingColumns=True) lines columns up by name and fills phone/address with NULL for base rows.
Add a source flag (base = 0, update = 1) so a tie on the timestamp can be broken in favour of the update.
row_number() over a window partitioned by customer_id, ordered by last_updated DESC then the flag DESC, keeps the winning record.
Keep whole records: do not coalesce columns from different rows.

How to practise it on PySpark.in

Open the challenge, write your PySpark code in the editor and press Run to execute it against the sample dataset. Submitting checks your output against every test case, including hidden ones, so you find out straight away whether your logic holds up. You can retry as often as you like, and each solved challenge adds to your XP.

Related PYSPARK challenges

Helpful resources

Open the notebook

Frequently asked questions

Do I need to install Spark or a database to solve this?

No. The PYSPARK environment runs in your browser with the sample data already loaded, so there is nothing to install or configure.

Is this challenge free?

Yes - the problem, the sample dataset, the hints and unlimited test runs are free.

What level is it?

It is rated medium and covers Schema Evolution.

Solve this challenge free on PySpark.in