Three Hours for Yesterday's Numbers

A medium Spark interview practice problem on DataDriven. Write the Spark code and run it against the tables, with instant feedback.

Domain

Spark

Difficulty

medium

Seniority

L5

Asked in

Walmart

Problem

The nightly daily_category_sales job reads every date partition of transactions, yet the summary it rebuilds only covers sales dated on or after 2026-07-01, so the on-call engineer has to make the read touch that slice and nothing earlier. From those sales, produce each product category's total revenue as recorded on the sale and total units sold, with the category taken from products.

Three terabytes scanned. One day changed.

Tables

transactions

Columns: transaction_id INTEGER, user_id INTEGER, product_id INTEGER, quantity INTEGER, total_amount REAL, transaction_date TEXT

products

Columns: product_id INTEGER, product_name TEXT, category TEXT, price REAL, rating REAL, in_stock INTEGER

Expected output

Worked solution and explanation

What's really being tested

Strip the retail dressing away and this is a partition-pruning problem. The job's real defect is one empty bracket in the physical plan: PartitionFilters: []. The transactions fact table is split into 1,460 date directories, and every night this job opens all of them, 2.94 TB and 4.2 billion rows, to rebuild a summary that only covers sales from the window start onward. Anyone can write the join and the sums. What separates candidates is handing Catalyst a boundary it can use at plan time: a literal compared against the raw transaction_date. Miss it and you burn three hours and 48 GB of shuffle to produce ten category totals.

Read the plan, not the code

Before: full scan

PartitionFilters: []. The FileScan reads all 1,460 directories: 2.94 TB, 4.2B rows. The partial aggregates still shuffle 48.2 GB. Executors sit in GC for about 95s each. Wall clock: 183 minutes, SLA blown by 2h 18m.

After: pruned

PartitionFilters: [(transaction_date >= window start)]. The FileScan reads 89 directories: about 179 GB, 256M rows. products broadcasts, so shuffle drops to about 12 MB. GC falls to about 24s. Wall clock: about 33 minutes, inside the 45-minute SLA.

Step 1: Prune to the window before anything else

The window start arrives as a string like '2026-07-01'. Compare the raw column to it: because dates are stored as ISO strings, string order is calendar order, and the comparison is a shape Catalyst pushes straight into the scan. Put it before the join so the pruned scan is what feeds everything else.

Step 2: Broadcast products to attach category

category lives in products, so join on product_id. At 24 MB products sits above Spark's 10 MB default auto-broadcast threshold, so F.broadcast(products) makes the intent explicit: every executor gets a copy and the fact side never shuffles for the join. The inner join also drops sales with no catalog match, which is what the summary wants.

Step 3: Sum per category

Group by category and sum total_amount for revenue and quantity for units. Partial aggregation runs map-side, so the only shuffle carries ten rows of partial sums per task instead of raw sales.

from pyspark.sql import SparkSession, functions as F

spark = SparkSession.builder.getOrCreate()

# The scheduler hands the job its window start. A literal boundary on the
# partition column is what Catalyst turns into a PartitionFilter at plan time.
window_start = "2026-07-01"

transactions = spark.table("transactions")
products = spark.table("products")

window_sales = transactions.filter(F.col("transaction_date") >= window_start)

category_sales = (
    window_sales.join(F.broadcast(products), "product_id")
    .groupBy("category")
    .agg(
        F.sum("total_amount").alias("total_revenue"),
        F.sum("quantity").alias("total_units"),
    )
)

Common follow-up questions

• The source is clustered on `transaction_date` but not partitioned by it. How does your fix change? (Pruning depends on physical layout, not only on a filter.)
• The window start must come from another table at run time. Can Spark still skip partitions, and what would you look for in the plan? (Plan-time versus run-time pruning.)
• Late transactions land for a month that already left the window. How do you correct history without a full rebuild? (Idempotent overwrite of a bounded range.)