Kill the UDF
A hard Spark interview practice problem on DataDriven. Write the Spark code and run it against the tables, with instant feedback.
- Domain
- Spark
- Difficulty
- hard
- Seniority
- L5
- Asked in
- Databricks
Problem
Each row of payments is one card transaction, and the starter's Python risk_score UDF scores it from ten of its columns through thresholds, a night-hours check and weighted sums, with a transaction flagged when its raw score clears FLAG_CUTOFF. Return every transaction's txn_id, merchant_id, risk_score rounded to three decimals and flagged, scoring exactly as the UDF does but with no Python left in the plan, since at two billion rows a day the JVM-to-Python round trip is 70% of the job's 4 hours.
2 billion rows through Python. One UDF at a time.
Tables
payments
Columns: txn_id INTEGER, merchant_id TEXT, amount REAL, settlement_day DATE, is_international BOOLEAN, channel_risk TEXT, velocity_1h INTEGER, velocity_24h INTEGER, device_trust REAL, ip_risk REAL, card_age_days INTEGER, txn_hour INTEGER, days_since_last_txn INTEGER
| txn_id | merchant_id | amount | settlement_day | is_international | channel_risk | velocity_1h | velocity_24h | device_trust | ip_risk | card_age_days | txn_hour | days_since_last_txn |
|---|---|---|---|---|---|---|---|---|---|---|---|---|
| 40001 | UBER_INTERNAL | 88.81 | 2026-02-04 | 0 | card_not_present | 3 | 7 | 0.079 | 0.037 | 13 | 5 | 11 |
| 40002 | UBER_INTERNAL | 176.12 | 2026-03-07 | 0 | ach | 6 | 14 | 0.108 | 0.074 | 26 | 10 | 22 |
| 40003 | MRC-UMBRELLA | 263.43 | 2026-04-10 | 0 | wallet | 9 | 21 | 0.137 | 0.111 | 39 | 15 | 33 |
| 40004 | MRC-STARK | 350.74 | 2026-05-13 | 0 | card_present | 12 | 28 | 0.166 | 0.148 | 52 | 20 | 44 |
| 40005 | MRC-WAYNE | 438.05 | 2026-06-16 | 1 | card_not_present | 1 | 35 | 0.195 | 0.185 | 65 | 1 | 55 |
| 40006 | MRC-HOOLI | 525.36 | 2026-07-19 | 0 | ach | 4 | 42 | 0.224 | 0.222 | 78 | 6 | 66 |
| 40007 | MRC-ACME | 612.67 | 2026-08-22 | 0 | wallet | 7 | 49 | 0.253 | 0.259 | 91 | 11 | 77 |
| 40008 | UBER_INTERNAL | 699.98 | 2026-09-01 | 0 | card_present | 10 | 56 | 0.282 | 0.296 | 104 | 16 | 88 |
| 40009 | UBER_INTERNAL | 787.29 | 2026-01-28 | 0 | card_not_present | 13 | 3 | 0.311 | 0.333 | 117 | 21 | 99 |
| 40010 | UBER_INTERNAL | 874.6 | 2026-02-03 | 1 | ach | 2 | 10 | 0.34 | 0.37 | 130 | 2 | 110 |
Expected output
| txn_id | merchant_id | risk_score | flagged |
|---|---|---|---|
| 40001 | UBER_INTERNAL | 0.588 | 0 |
| 40002 | UBER_INTERNAL | 0.426 | 0 |
| 40003 | MRC-UMBRELLA | 0.414 | 0 |
| 40005 | MRC-WAYNE | 0.99 | 1 |
| 40025 | UBER_INTERNAL | 1.044 | 1 |
Worked solution and explanation
What this really is
This is a serialization problem dressed up as a fraud model. Nothing in the score needs Python: it is ten if statements and some multiplication. The skill being probed is whether you see that the cost is the boundary, not the math, and whether you can move the math across that boundary without changing a single score. Anyone can write F.when. The trap is a sloppy translation: flag on the rounded score, lose a capped-versus-linear branch, and transactions quietly cross a cutoff that was tuned on the old numbers.
Building the native score
Step 1: Translate each if into a when
Every flat bonus becomes F.when(cond, bonus).otherwise(0.0). The membership check on NIGHT_HOURS becomes F.col("txn_hour").isin(0, 1, 2, 3): a literal set the optimizer folds into the expression, no lookup structure required.
Step 2: Keep both arms of the capped terms
4 terms are not bonuses, they are caps: velocity_1h, velocity_24h, ip_risk and amount contribute linearly until a threshold, then a fixed amount. The otherwise arm carries the linear part. Write .otherwise(0.0) there and every low-velocity customer loses points.
Step 3: Flag on the raw score, round on the way out
The UDF compares the unrounded score to FLAG_CUTOFF. Compute raw_score once, derive flagged from it, and apply F.round only to the column you return. Rounding first turns a 0.8004 into a 0.8 that no longer clears the cutoff.
payments = spark.table("payments")
raw_score = (
F.when(F.col("is_international") == 1, 0.35).otherwise(0.0)
+ F.when(F.col("channel_risk") == "card_not_present", 0.25).otherwise(0.0)
+ F.when(F.col("velocity_1h") > 10, 0.20).otherwise(F.col("velocity_1h") * 0.02)
+ F.when(F.col("velocity_24h") > 50, 0.15).otherwise(F.col("velocity_24h") * 0.003)
+ F.when(F.col("device_trust") < 0.3, 0.15).otherwise(0.0)
+ F.when(F.col("ip_risk") > 0.7, 0.10).otherwise(F.col("ip_risk") * 0.14)
+ F.when(F.col("card_age_days") < 30, 0.10).otherwise(0.0)
+ F.when(F.col("txn_hour").isin(0, 1, 2, 3), 0.08).otherwise(0.0)
+ F.when(F.col("days_since_last_txn") > 180, 0.05).otherwise(0.0)
+ F.when(F.col("amount") > 5000, 0.10).otherwise(F.col("amount") / 50000)
)
scored = payments.select("txn_id", "merchant_id", raw_score.alias("raw_score"))
result = scored.select(
"txn_id",
"merchant_id",
F.round("raw_score", 3).alias("risk_score"),
(F.col("raw_score") > 0.8).alias("flagged"),
)No udf, no Python worker: Catalyst sees one expression tree it can compile.
- Arrow-batched UDF
- Plan holds an
ArrowEvalPythonnode. Each 10,000-row batch is converted to Arrow, piped to a Python worker, scored, piped back and decoded. Executors hold both JVM rows and Arrow buffers, which is where the GC time comes from. - Native expressions
- Plan is a single
Projectfused with the scan by whole-stage codegen. Rows stay in Tungsten format in one JVM pass, there is no worker pool, and memory and GC drop with it.
Common follow-up questions
- The tier weights move into a small `merchants` table. How do you bring them in without a shuffle? (Broadcast join, or a literal map when the lookup is tiny and fixed.)
- How would you prove the rewrite changes no flag before turning off the old job? (Shadow run plus an anti-join on mismatched scores.)
- A column starts arriving with `NULL` values. What does each version do, and which do you want? (Null semantics differ across the boundary.)