Faster ETL Pipelines

Two slow pipelines

For a banking project, two large ETL pipelines were taking up too much of the daily data warehouse load window. One ran for roughly two hours, while the other took 35 minutes. Together, the pipelines touched more than 20 tables and joined several fact tables, some of which contained around one billion rows.

Both pipelines had already been divided across multiple DataStage jobs so their processing could be spread out. My task was to bring their total runtimes down and build several new ETL pipelines without introducing the same problems.

Understanding the first pipeline

Before I could change the first pipeline, I had to learn the project’s testing and deployment process and trace its processing from beginning to end. A large part of it depended on a proprietary historisation system that recorded how data changed over time. The system had several quirks and was implemented through mostly undocumented PostgreSQL procedures.

To avoid changing its results by accident, I traced those procedures and their quirks closely enough to preserve their behaviour. The DataStage jobs themselves had already been tuned quite heavily, so further adjustments inside the existing flows were unlikely to save enough time.

Moving less data

That analysis pointed to data transfer as the main opportunity. A large part of the runtime was spent loading data into DataStage and writing it back to the database. With datasets of this size, reducing that transfer time mattered more than another small adjustment to an individual DataStage step.

I redesigned the pipeline so the database handled more of the large joins and aggregations. DataStage then loaded smaller, already aggregated datasets for the final processing and merge.

For the first pipeline, that meant rewriting large parts of the process as complex, multi-step SQL. I tested database parallelisation hints and different ways of materialising subqueries until the runtime fell from roughly two hours to 30 minutes.

Applying it to the second pipeline

The same split also worked for the second pipeline, which was spread across three DataStage jobs. I consolidated it into one job that kept the large joins and aggregations in the database, while DataStage handled the final processing. Its runtime dropped from 35 minutes to five.

Five new pipelines

After the two larger pipelines, I carried the same approach into five smaller ones. DataStage handled the surrounding flow, while Automic scheduled the jobs as part of the existing processing chain.

Result

The two critical pipelines now finish in 30 and five minutes, cutting their combined runtime by roughly 80%. That leaves considerably more room in the nightly load window for the jobs that follow.