Rescuing a next-best-offer pipeline from memory-limit failure
A next-best-offer pipeline was rejected by the cluster's memory admission control; a redesign around narrow ranking and staged joins brought it back under the pool limit.
- Role
- Lead analyst / owner
- Where
- stc Bahrain
Some specifics are anonymised to respect commercially sensitive information.
Context
The next-best-offer pipeline scores the customer base against a set of candidate offers and picks the best action for each customer. It runs on a shared analytics cluster with memory admission control — a query whose planned memory exceeds the pool is rejected before it starts.
Problem
The pipeline query stopped running. The planner estimated on the order of ~797 GB against a pool limit near 400 GB, so admission control refused it outright. The usual levers — more selective filters, smaller date windows — did not move the estimate enough, and the campaign that depended on it was blocked.
What I did
I read the query plan and found two causes: a common table expression being re-evaluated rather than materialised, and an OR condition in a join that fanned out the row count. I rewrote the pipeline to rank first on a narrow set of columns, materialise that ranked set to a staged Parquet table, then join the wide attributes back onto the much smaller result. Each stage was sized to sit well inside the pool.
Result
The pipeline completed again, with a planned memory footprint under the pool limit, and the campaign that depended on it was unblocked. The staged pattern became the template for other wide-join scoring jobs.
What I learned
On a shared cluster the memory plan is the constraint, not just the runtime — and the plan is very sensitive to query shape. Ranking narrow and joining wide back afterwards is almost always cheaper than carrying every column through the ranking step.