Bank of Ireland: Upsert the Customer Master with a Delta MERGE (SCD Type 1)

PYSPARK coding challenge · Difficulty: medium · Topic: Delta Lake MERGE / SCD Type 1 · +100 XP

Scenario

You are a Data Engineer on the Bank of Ireland customer data platform.

The Customer Master is a Delta table that branch systems, the mobile app and

regulatory reporting all read. It must always hold **exactly one current row per

customer**. History is not kept here — when a customer moves from Dublin to

Drogheda, the master should simply say Drogheda. This is a

Slowly Changing Dimension Type 1 (SCD1) table.

Every morning the core-banking system sends a change feed (CDC). The feed is

not clean: a customer can appear more than once, records arrive out of order, and

a delayed record can be older than what the master already holds. In production

you apply the feed with a Delta Lake MERGE. Your job is to produce exactly the

table that a correct MERGE would leave behind.

Input

customer_master — the current Delta table (one row per customer_id):

| customer_id | customer_name | city | segment | last_updated |
| --- | --- | --- | --- | --- |
| C101 | Aoife Murphy | Dublin | Retail | 2026-09-30 10:00:00 |
| C102 | Ciaran Byrne | Cork | Retail | 2026-09-28 09:15:00 |
| C103 | Niamh Kelly | Galway | Premier | 2026-10-02 16:40:00 |
| C104 | Sean Walsh | Limerick | Business | 2026-10-03 11:05:00 |
| C105 | Roisin Doyle | Waterford | Retail | 2026-09-15 08:00:00 |

customer_updates — today's change feed:

| customer_id | customer_name | city | segment | last_modified |
| --- | --- | --- | --- | --- |
| C101 | Aoife Murphy-Nolan | Drogheda | Retail | 2026-10-04 09:00:00 |
| C102 | Ciaran Byrne | Cork | Premier | 2026-10-04 08:00:00 |
| C102 | Ciaran Byrne | Kinsale | Premier | 2026-10-04 11:30:00 |
| C103 | Niamh Kelly | Galway | Premier | 2026-10-04 07:00:00 |
| C104 | Sean Walsh | Ennis | Business | 2026-10-01 12:00:00 |
| C106 | Saoirse Ryan | Kilkenny | Retail | 2026-10-04 10:15:00 |
| C107 | Eoin Gallagher | Dundalk | Retail | 2026-10-04 09:45:00 |
| C107 | Eoin Gallagher | Sligo | Retail | 2026-10-04 12:00:00 |

All columns are strings. Timestamps use yyyy-MM-dd HH:mm:ss, so comparing them

as strings is also chronological. No customer has two feed rows with the same

last_modified.

Rules — what a correct MERGE does

The business key is customer_id. The tracked attributes are customer_name,

city and segment.

1. One source row per key. A Delta MERGE fails if two source rows match the

same target row. Collapse the feed to the latest row per customer_id

(highest last_modified) before applying it.

2. Insert new customers. A customer_id that is not in the master is

inserted with its feed values; its last_updated is the feed last_modified.

3. Update changed customers. An existing customer is overwritten — all three

tracked attributes, plus last_updated set to the feed last_modified — only

when both hold:

4. Ignore stale records. A feed row that is not newer than the master row

(a late arrival) must not change anything.

5. Ignore no-op records. A newer feed row whose tracked attributes are all the

same as the master leaves the row untouched — including last_updated.

Rewriting identical values creates churn for every downstream reader.

6. Keep everyone else. Master customers that are not in today's feed stay

exactly as they are. Nothing is ever deleted.

Output

Assign the merged master to df_result with exactly these columns, in this order,

sorted by customer_id, and finish with df_result.show():

customer_id, customer_name, city, segment, last_updated

Example

For the input above, df_result.show() prints:

`

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

|customer_id|     customer_name|     city| segment|       last_updated|

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

|       C101|Aoife Murphy-Nolan| Drogheda|  Retail|2026-10-04 09:00:00|
|       C102|      Ciaran Byrne|  Kinsale| Premier|2026-10-04 11:30:00|
|       C103|       Niamh Kelly|   Galway| Premier|2026-10-02 16:40:00|
|       C104|        Sean Walsh| Limerick|Business|2026-10-03 11:05:00|
|       C105|      Roisin Doyle|Waterford|  Retail|2026-09-15 08:00:00|
|       C106|      Saoirse Ryan| Kilkenny|  Retail|2026-10-04 10:15:00|
|       C107|    Eoin Gallagher|    Sligo|  Retail|2026-10-04 12:00:00|

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

`

Why each customer ends up this way

| customer_id | Rule | What happened |
| --- | --- | --- |
| C101 | 3 | Newer and changed (married name, moved) — updated |
| C102 | 1, 3 | Two feed rows — the 11:30 one wins; newer and changed — updated |
| C103 | 5 | Newer but identical — untouched, `last_updated` stays 2026-10-02 |
| C104 | 4 | The feed row (Oct 1) is older than the master (Oct 3) — ignored |
| C105 | 6 | Not in the feed — unchanged |
| C106 | 2 | New customer — inserted |
| C107 | 1, 2 | New customer with two rows — the 12:00 one is inserted |

Notes

recreate them.

result with DataFrame or Spark SQL operations. The editorial shows the same

logic as a real DeltaTable.merge(...) and MERGE INTO statement.

What this PYSPARK challenge teaches you

“Bank of Ireland: Upsert the Customer Master with a Delta MERGE (SCD Type 1)” is a medium-level PYSPARK challenge focused on Delta Lake MERGE / SCD Type 1. Working through it gives you hands-on practice with Delta Lake, MERGE, SCD Type 1, Upsert, CDC, 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

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:

  1. Start with rule 1. Rank the feed per customer_id by last_modified descending (Window + row_number) and keep rank 1. A real Delta MERGE throws an error if you skip this.
  2. A full outer join of the master and the deduplicated feed on customer_id gives you all three cases at once: matched (both sides), new (master side null) and untouched (feed side null).
  3. For matched rows, take the feed values only when the feed is newer AND some tracked column differs. eqNullSafe (or <=> in SQL) compares columns safely.
  4. When you keep the master row, keep ALL of it -- including last_updated. Only a real change moves the timestamp.
  5. Rename at the end: the output column is last_updated (the master's name), not the feed's last_modified. Sort by customer_id before show().

Where this comes up

Variations of this problem have been reported in interviews at Bank of Ireland. Interviewers use it to check whether you can express the logic cleanly and reason about correctness on edge cases such as ties, nulls and empty groups.

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

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 Delta Lake MERGE / SCD Type 1.

Solve this challenge free on PySpark.in