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 TEXT, is_international INTEGER, 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

payments: 10 of 200 rows
txn_idmerchant_idamountsettlement_dayis_internationalchannel_riskvelocity_1hvelocity_24hdevice_trustip_riskcard_age_daystxn_hourdays_since_last_txn
40001UBER_INTERNAL88.812026-02-040card_not_present370.0790.03713511
40002UBER_INTERNAL176.122026-03-070ach6140.1080.074261022
40003MRC-UMBRELLA263.432026-04-100wallet9210.1370.111391533
40004MRC-STARK350.742026-05-130card_present12280.1660.148522044
40005MRC-WAYNE438.052026-06-161card_not_present1350.1950.18565155
40006MRC-HOOLI525.362026-07-190ach4420.2240.22278666
40007MRC-ACME612.672026-08-220wallet7490.2530.259911177
40008UBER_INTERNAL699.982026-09-010card_present10560.2820.2961041688
40009UBER_INTERNAL787.292026-01-280card_not_present1330.3110.3331172199
40010UBER_INTERNAL874.62026-02-031ach2100.340.371302110

Expected output

First 5 of 200 rows
txn_idmerchant_idrisk_scoreflagged
40001UBER_INTERNAL0.5880
40002UBER_INTERNAL0.4260
40003MRC-UMBRELLA0.4140
40005MRC-WAYNE0.991
40025UBER_INTERNAL1.0441

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.

Native scoring, one projection
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 ArrowEvalPython node. 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 Project fused 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