45 Minutes Turned Into 3.5 Hours

A medium Pipeline Design interview practice problem on DataDriven. Design the pipeline on the canvas: sources, transforms, storage and checks, then get feedback on it.

Domain

Pipeline Design

Difficulty

medium

Seniority

L5

Problem

Design the scheduled pipeline and remediation that land a daily bank-transaction aggregation on Databricks before the 6am risk dashboard reads its queryable output. The job should take 45 minutes but now runs 3.5 hours and misses that deadline.

Spark jobs are running. Just not fast enough.

Business requirements

• The team has been adding compute for weeks with no improvement; the fix has to reshape the job, not just run the same job on a bigger cluster.
• If the daily run is going to miss 6am, on-call has to be paged before the deadline, not after the dashboard is already stale.
• BI queries on this output have gotten slower as the table has grown; the output has to be laid out so a lookup reads a small slice.

Worked solution and explanation

What this problem is really asking

This is a performance diagnosis dressed as a design problem. The trap is the redesign answer (rebuild it in streaming, switch warehouses, add a cache) when the real question is why the existing job is slow. The team has already proven that more compute does not help, and that is the strongest hint that the bottleneck is not compute. It is join shape, key skew or data layout, and the fix lives at that layer. Answer with a rebuild and you have redesigned around a problem you never found: the 6am SLA keeps slipping on the new stack too.

The default move is to scale the cluster, add executors and let Spark figure it out. The job goes from too slow to slightly less too slow, the bill goes up, and 6am still slips. The real cause sits underneath: a wide join on a hot key, millions of tiny files, or a partitioning that matches neither the join nor the query. None of those get faster with more workers.

Walk the requirements

Step 1: Put the deadline under a scheduler that pages early

daily_orchestrator owns the run and the SLA. Its alert fires if the run has not landed by 5:30am and routes to on_call_alerting, so someone is paged while there is still time to rerun or fix forward. An alert that fires at 6:05 only tells on-call what the dashboard already shows.

Step 2: Profile, then reshape the join

More compute not helping rules out being CPU-bound on the right amount of work. The profile here shows one task on a hot account type running 45 minutes while the rest finish in 3, behind a full sort-merge shuffle of the transaction table against a dimension small enough to broadcast. broadcast_join_aggregation broadcasts the small dimensions and splits the hot key. The job is reshaped, not rescaled.

Step 3: Lay out the output so BI scans a slice

BI queries slowing as the table grows is a layout signal: every lookup reads every file. output_repartitioner writes the output in large files partitioned by date and clustered on the column the dashboard filters by, so a single-account lookup touches a handful of files in risk_warehouse. The right layout speeds both the dashboard and the next day's write.

The shape that fits

Common follow-up questions

• The profile shows a few hot values on one join key taking most of the runtime. What are the options, and which would you reach for first? (Tests whether the candidate names salting, broadcasting the smaller side, or pre-aggregating the hot keys separately, and which profile signal points to each.)
• After the layout fix, the dashboard still feels slow for the first few queries at 6am. Where does that time go? (Tests whether the candidate looks at the consumer side (a cold warehouse, extract rebuilds, missing materialised aggregates) rather than at the daily job, which already lands on time.)