Bronze to silver with a quarantine
PySpark data engineering interview problem. Difficulty: intermediate. Pattern: Medallion. About 18 minutes. Part of the Pro drill bank.
Route each raw event to the clean layer or to a quarantine with the reason it was rejected. Treat this as a production helper: match the contracted return shape, including empty and duplicate inputs.
bronze holds raw events exactly as they arrived, all as text (bronze is the raw layer, silver is the cleaned layer, and quarantine is where rejected rows go): event_id, user_id, amount and event_ts. Check each row against these rules, in this order, and stop at the first one it breaks: 1. missing_user: user_id is missing, empty, or only spaces 2. bad_amount: amount is missing, or is not a number (an optional minus sign, then digits, then optionally a dot and more digits; .5 and 5. are not valid) 3. negative_amount: amount is below 0 Return every bronze row with: event_id, user_id amount_value: amount as a decimal number for rows that pass, missing otherwise route: silver for rows that pass, quarantine for rows that break a rule reason: the rule that was broken, missing for rows that pass Sort by event_id. Assign the DataFrame to result.
Requirements
- Return the columns event_id, user_id, amount_value, route, reason.
- Sort by event_id.
Constraints
- Rules are applied in the order listed.
- A row gets at most one reason.
Examples
Input: bronze event_id | user_id | amount | event_ts 1 | u1 | 19.99 | 2024-06-01 10:00:00 2 | NULL | 5.00 | 2024-06-01 10:05:00 3 | u3 | abc | 2024-06-01 10:09:00 4 | u4 | -3.50 | 2024-06-01 10:15:00 5 | | 7 | 2024-06-01 10:20:00 6 | u2 | 120 | 2024-06-01 10:30:00 Output: event_id | user_id | amount_value | route | reason 1 | u1 | 19.99 | silver | NULL 2 | NULL | NULL | quarantine | missing_user 3 | u3 | NULL | quarantine | bad_amount 4 | u4 | NULL | quarantine | negative_amount 5 | | NULL | quarantine | missing_user 6 | u2 | 120 | silver | NULL Rows 1 and 6 pass. Row 2 has no user, 5 has a blank one, 3 is not a number and 4 is negative.
Topics: lakebench, pyspark, medallion, validation, quarantine, rlike.
More PySpark interview questions · All interview problems · Learn data engineering
Bronze to silver with a quarantine
Interview-style drill: Route each raw event to the clean layer or to a quarantine with the reason it was rejected.
`bronze` holds raw events exactly as they arrived, all as text (bronze is the raw layer, silver is the cleaned layer, and quarantine is where rejected rows go): `event_id`, `user_id`, `amount` and `event_ts`. Check each row against these rules, in this order, and stop at the first one it breaks: 1. `missing_user`: `user_id` is missing, empty, or only spaces 2. `bad_amount`: `amount` is missing, or is not a number (an optional minus sign, then digits, then optionally a dot and more digits; `.5` and `5.` are not valid) 3. `negative_amount`: `amount` is below 0 Return every bronze row with: - `event_id`, `user_id` - `amount_value`: `amount` as a decimal number for rows that pass, missing otherwise - `route`: `silver` for rows that pass, `quarantine` for rows that break a rule - `reason`: the rule that was broken, missing for rows that pass Sort by `event_id`. Assign the DataFrame to `result`.