RBS: Build a Data Quality Gate for the Daily Customer Extract

PYSPARK coding challenge · Difficulty: hard · Topic: Data Quality Validation · +200 XP

Scenario

*This is a fictional learning scenario inspired by common banking data quality

workflows. It is not affiliated with RBS or NatWest Group and does not describe

their systems, data or requirements.*

You are a Data Engineer at RBS. Every morning the core banking system

exports the customer records changed since the last run. The extract lands as-is in the Bronze

layer, and a PySpark job promotes it to Silver, which feeds balance

reporting and the customer 360 view.

Last week the reporting team found wrong balances and missing customers

in Silver. The cause was the extract itself: missing IDs, blank names,

malformed emails, impossible ages, negative balances, unknown statuses and the

same customer sent twice. All of it flowed straight through.

You are building the data quality gate. It must not silently drop

anything. It checks every record against every rule and labels the record

with all the rules it breaks, so that:

• only VALID records are promoted to Silver, and
• INVALID records go to a quarantine table, where the operations team can see exactly what to fix.

Input

customer_accounts: today's extract, 17 records.

| Column | Type | Meaning |
| --- | --- | --- |
| `record_id` | int | Unique ID of the record in this extract (never NULL) |
| `customer_id` | int | Business key of the customer |
| `name` | string | Customer name |
| `email` | string | Contact email |
| `age` | int | Age in years |
| `account_balance` | double | Current balance |
| `account_status` | string | Account status |
| `updated_at` | string | When the source system last changed the record, `yyyy-MM-dd HH:mm:ss` |

| record_id | customer_id | name | email | age | account_balance | account_status | updated_at |
| --- | --- | --- | --- | --- | --- | --- | --- |
| 1 | 101 | Amit Sharma | amit.sharma@gmail.com | 32 | 50000.0 | ACTIVE | 2026-10-01 09:00:00 |
| 2 | 102 | Rahul Verma | rahul.verma@yahoo.com | 18 | 0.0 | INACTIVE | 2026-10-01 09:05:00 |
| 3 | 103 | Priya Nair | priya-example.com | 35 | 10000.0 | ACTIVE | 2026-10-01 09:10:00 |
| 4 | 104 | `"   "` (3 spaces) | john.doe@gmail.com | 42 | 5000.0 | CLOSED | 2026-10-01 09:15:00 |
| 5 | 105 | David Miller | NULL | 45 | -1000.0 | ACTIVE | 2026-10-01 09:20:00 |
| 6 | 106 | Neha Gupta | neha@gmail | 17 | 3000.0 | ACTIVE | 2026-10-01 09:25:00 |
| 7 | 107 | Raj Patel | raj.patel@gmail.com | 38 | 15000.0 | SUSPENDED | 2026-10-01 09:30:00 |
| 8 | 108 | Karan Mehta | karan.mehta@gmail.com | 29 | 14000.0 | ACTIVE | 2026-10-02 10:00:00 |
| 9 | 108 | Karan Mehta | karan.mehta@gmail.com | 29 | 12000.0 | ACTIVE | 2026-10-01 08:00:00 |
| 10 | NULL | Sonia Kapoor | sonia.kapoor@gmail.com | 31 | 20000.0 | ACTIVE | 2026-10-01 09:40:00 |
| 11 | NULL | Arjun Rao | arjun.rao@gmail.com | 40 | 8000.0 | ACTIVE | 2026-10-01 09:45:00 |
| 12 | 0 | Test User | test.user@rbs.co.uk | 25 | 100.0 | ACTIVE | 2026-10-01 09:50:00 |
| 13 | 109 | NULL | meera.iyer@outlook.com | NULL | 7500.0 | ACTIVE | 2026-10-01 09:55:00 |
| 14 | 110 | Vikram Singh | Vikram.Singh@RBS.co.uk | 100 | 250000.0 | CLOSED | 2026-10-01 11:00:00 |
| 15 | 110 | Vikram Singh | Vikram.Singh@RBS.co.uk | 100 | -500.0 | CLOSED | 2026-10-03 11:00:00 |
| 16 | 111 | Anjali Desai | anjali.desai@gmail.com | 101 | NULL | active | 2026-10-01 10:05:00 |
| 17 | 112 | Fatima Khan | fatima_khan+bank@gmail.com | 64 | 3200.5 | CLOSED | 2026-10-01 10:10:00 |

Data quality rules

Check every record against all seven rules. **A missing (NULL) value always

fails its rule. It never passes by accident.**

| # | Error code | The record fails when… |
| --- | --- | --- |
| 1 | `INVALID_CUSTOMER_ID` | `customer_id` is NULL, or is not greater than 0 |
| 2 | `INVALID_NAME` | `name` is NULL, empty, or only whitespace |
| 3 | `INVALID_EMAIL` | `email` is NULL, or does not fully match the pattern below |
| 4 | `INVALID_AGE` | `age` is NULL, or is outside **18 to 100 inclusive** |
| 5 | `INVALID_BALANCE` | `account_balance` is NULL, or is negative (0.0 is allowed) |
| 6 | `INVALID_ACCOUNT_STATUS` | `account_status` is not exactly `ACTIVE`, `INACTIVE` or `CLOSED` (case-sensitive; NULL fails) |
| 7 | `DUPLICATE_CUSTOMER` | a **newer** record (later `updated_at`) exists for the same non-NULL `customer_id` |

Email pattern (check the value as-is, without trimming it):

`

^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}$

`

How the duplicate rule works:

• Spark DataFrames have no row order, so "the last row in the list" means nothing. The latest record is the one with the highest updated_at. The timestamp format sorts correctly as a string. No customer has two records with the same updated_at.
• Only the older copies get DUPLICATE_CUSTOMER. The latest record is judged by rules 1–6 like any other.
• The latest record wins even if it is invalid. An older valid copy is still out of date, so it must not reach Silver.
• Records with a NULL customer_id are not duplicates of each other. They only fail rule 1.

Output

Build a DataFrame named dq_report with one row per input record (all 17)

and exactly these columns:

| Column | Content |
| --- | --- |
| `record_id` | from the input |
| `customer_id` | from the input |
| `dq_status` | `VALID` if the record broke no rule, otherwise `INVALID` |
| `validation_errors` | array of the error codes the record broke, **in rule order (1 → 7)**; an empty array `[]` for a valid record |

Sort by record_id and finish with:

`python

dq_report.show(truncate=False)

`

truncate=False matters: by default show() cuts every value to 20

characters, so long error lists would come out as [INVALID_AGE, INVAL....

Print only this table. Submit reads every table your code prints.

Expected output

`

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

|record_id|customer_id|dq_status|validation_errors                                     |

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

|1        |101        |VALID    |[]                                                    |
|2        |102        |VALID    |[]                                                    |
|3        |103        |INVALID  |[INVALID_EMAIL]                                       |
|4        |104        |INVALID  |[INVALID_NAME]                                        |
|5        |105        |INVALID  |[INVALID_EMAIL, INVALID_BALANCE]                      |
|6        |106        |INVALID  |[INVALID_EMAIL, INVALID_AGE]                          |
|7        |107        |INVALID  |[INVALID_ACCOUNT_STATUS]                              |
|8        |108        |VALID    |[]                                                    |
|9        |108        |INVALID  |[DUPLICATE_CUSTOMER]                                  |
|10       |NULL       |INVALID  |[INVALID_CUSTOMER_ID]                                 |
|11       |NULL       |INVALID  |[INVALID_CUSTOMER_ID]                                 |
|12       |0          |INVALID  |[INVALID_CUSTOMER_ID]                                 |
|13       |109        |INVALID  |[INVALID_NAME, INVALID_AGE]                           |
|14       |110        |INVALID  |[DUPLICATE_CUSTOMER]                                  |
|15       |110        |INVALID  |[INVALID_BALANCE]                                     |
|16       |111        |INVALID  |[INVALID_AGE, INVALID_BALANCE, INVALID_ACCOUNT_STATUS]|
|17       |112        |VALID    |[]                                                    |

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

`

Why each record ends up this way

| record_id | Result | What it teaches |
| --- | --- | --- |
| 1, 17 | VALID | Normal good records. `fatima_khan+bank@gmail.com` is a valid email (`_` and `+` are allowed) |
| 2 | VALID | Boundaries are inclusive: age 18 and balance 0.0 both pass |
| 3 | INVALID_EMAIL | No `@` |
| 4 | INVALID_NAME | Three spaces is not a name. Compare after `trim()` |
| 5 | INVALID_EMAIL, INVALID_BALANCE | **NULL email.** `rlike()` on NULL returns NULL, which a filter treats as false, so a naive regex check lets it through |
| 6 | INVALID_EMAIL, INVALID_AGE | `neha@gmail` has no domain suffix; 17 is under 18 |
| 7 | INVALID_ACCOUNT_STATUS | `SUSPENDED` is not an allowed status |
| 8, 9 | 8 VALID, 9 DUPLICATE | **Record 9 comes later in the list but is older.** The latest record is the 2 Oct one |
| 10, 11 | INVALID_CUSTOMER_ID | Two NULL IDs are not "the same customer". A window puts all NULL keys in one partition, so guard against it |
| 12 | INVALID_CUSTOMER_ID | 0 is not greater than 0 |
| 13 | INVALID_NAME, INVALID_AGE | **NULL name and NULL age.** `trim(NULL) == ""` and `NULL BETWEEN 18 AND 100` are both NULL, not true |
| 14, 15 | 14 DUPLICATE, 15 INVALID_BALANCE | The latest record (15) is invalid, and it still wins. Promoting the older valid copy (14) would publish a balance that is no longer true |
| 16 | INVALID_AGE, INVALID_BALANCE, INVALID_ACCOUNT_STATUS | One record, three failures. Report them all. 101 is over 100, NULL balance, and `active` is not `ACTIVE` |

Going further (not graded)

Once dq_report is correct, try these with Run. Then remove them before

you Submit, because any extra table you print counts as part of your

answer.

1. Split the report: valid_df (the 4 VALID records → Silver) and invalid_df (the 13 INVALID records with their errors → quarantine).

2. Compute a DQ summary from the data, with no hard-coded numbers: total_records 17, valid_records 4, invalid_records 13, duplicate_records 2, dq_score 23.53, where dq_score = valid_records / total_records * 100, rounded to 2 decimals.

3. Count failures per rule. explode() the arrays and group: INVALID_CUSTOMER_ID 3, INVALID_NAME 2, INVALID_EMAIL 3, INVALID_AGE 3, INVALID_BALANCE 3, INVALID_ACCOUNT_STATUS 2, DUPLICATE_CUSTOMER 2. The rule counts add up to 18, more than the 13 invalid records, because one record can break several rules.

Constraints

• customer_accounts and spark are already defined. Do not recreate them.
• Use the DataFrame API or Spark SQL. Do not collect rows to the driver or convert to Pandas, and do not loop over rows in Python. Looping over the list of rules to build column expressions is fine.
• Hard-code nothing about specific records: the same code must work on tomorrow's extract.

What this PYSPARK challenge teaches you

“RBS: Build a Data Quality Gate for the Daily Customer Extract” is a hard-level PYSPARK challenge focused on Data Quality Validation. Working through it gives you hands-on practice with Data Quality, Data Validation, Null Handling, Regex, Window Functions, 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

• Data Quality
• Data Validation
• Null Handling
• Regex
• Window Functions
• Deduplication
• Higher-Order Functions
• Banking

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. Write one condition per rule and turn each into F.when(condition, F.lit("ERROR_CODE")) with no otherwise(): it gives the code when the rule fails and NULL when it passes. Put all seven in F.array(...), then drop the NULLs with F.filter(arr, lambda e: e.isNotNull()) (or F.array_compact in Spark 3.4+).
2. The NULL trap: rlike(), between(), trim(...) == "" and isin() all return NULL when the column is NULL, and F.when(NULL, ...) behaves like false. So a missing email would silently pass. Test isNull() first: col.isNull() | ~col.rlike(pattern).
3. Duplicates: Window.partitionBy("customer_id").orderBy(F.col("updated_at").desc()) with row_number(). Row 1 is the latest; anything above 1 is an older copy. Spark gives the window ONE partition for all NULL keys, so only flag rows whose customer_id is not NULL.
4. Do not pick the latest valid record. Pick the latest record, then judge it. Record 15 is invalid, and it still makes record 14 a duplicate.
5. dq_status is just F.when(F.size("validation_errors") == 0, "VALID").otherwise("INVALID"). Select the four output columns, orderBy("record_id"), and call show(truncate=False).

Where this comes up

Variations of this problem have been reported in interviews at RBS. 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

• Fix the Broken Pipeline
• Optimize the 100M-Row Join
• Parse Apache Logs with Regex
• Optimize Small DataFrame Join with Broadcast
• Optimize Average Rating Calculation for Products
• Deduplicate and Aggregate User Actions with Latest Session

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 hard and covers Data Quality Validation.

Solve this challenge free on PySpark.in