Optimizing Big Data Pipelines Using Coding Assistants

Data pipelines that feed financial and operational dashboards are often the hardest code in a company to improve: everyone knows they are slow and expensive, and nobody dares change SQL whose output must stay identical. In this talk, Kevin Postlewaite walks through using a coding assistant to attack that problem on a production dbt pipeline of roughly 80 models over a 12-billion-row fact table. Rather than optimizing first, he started by working with the coding assistant to build a test harness that freezes production inputs with point-in-time snapshots, replays the pipeline on samples and full clones, and proves bit-for-bit output equality with a hash-then-difference comparer. With that proof in hand, four ordinary SQL techniques cut chain runtime and cost by roughly 40 percent, and the rewrite was ported back into the production dbt project and taken through code review in the two weeks that followed. Along the way the harness surfaced a long-standing production data defect, a time-zone trap in date-dependent models, and non-deterministic floating-point aggregates. Kevin will also give an account of how he approached the problem and the parts of the process that the coding assistant couldn't help with.