Billion-row pipeline rebuild

A 5+ hour job kept exhausting platform capacity before finishing. A pre-aggregated curated table cut processing time and expanded the window from a rolling month to full history.

The problem

An application analytics pipeline ran against a source table exceeding five billion rows. It took more than five hours and repeatedly exhausted platform capacity before completing, which meant failed runs, stale data, and a reporting window capped at a rolling month.

That cap was the real cost. Analysts could not ask questions spanning more than a few weeks, so entire categories of trend analysis were off the table.

What I did

Rather than tuning the existing query in place, I looked at what the reporting layer actually needed and worked backwards. Most of the five billion rows were being scanned to produce a much smaller set of aggregates that changed slowly.

I designed a pre-aggregated curated table sized around those real reporting needs, with an incremental design so each run only processed what had changed.

The outcome

Processing time dropped substantially, the job stopped exhausting capacity, and the reporting window expanded from a rolling month to full history.

Expanding the window mattered more than the speed. It changed which questions the business could ask, not just how fast they got answers.