DAGSmith: Dependency-Aware Rewriting for dbt-Style SQL Pipelines

Modern data systems are increasingly built as large pipelines of interconnected SQL queries, but existing optimizers mostly optimize each query separately. DAGSmith instead looks at the entire pipeline and uses the relationships between queries to find optimizations that are invisible when queries are considered one at a time. It uses an LLM to propose changes, verifies that those changes are safe, and jointly decides which changes and materialization choices work best together. On a real-world dbt pipeline, this substantially reduced both runtime and compute cost, outperforming existing single-query optimization and materialization-tuning approaches.

Abstract

Modern analytics is increasingly organized as recurring SQL pipelines rather than isolated SQL statements. Tools such as dbt—which have gained extreme popularity in recent years—allow teams to write each transformation as SQL and make dependencies between transformations explicit, producing directed acyclic graphs (DAGs) with hundreds or thousands of interdependent SQL models. Existing techniques are not effective at optimizing these expensive pipelines. Traditional query optimizers and source-to-source query rewriters operate on one query at a time, while materialized-view selection and multi-query optimization address narrower forms of reuse. They do not directly exploit the pipeline-level information exposed by explicit dependencies: how intermediate results are consumed, which downstream outputs depend on each computation, where expensive work occurs relative to data reduction, which intermediate results are worth persisting, and how refresh schedules relate to the rate at which inputs change and outputs are consumed. We introduce DAGSmith, to the best of our knowledge the first holistic dependency-aware source-to-source rewriting system for SQL pipeline DAGs. DAGSmith treats explicit dependencies as optimization signals. It analyzes each transformation together with its upstream inputs, downstream consumers, and position in the pipeline DAG, uses an LLM to propose pipeline-level refactorings, separates SQL generation and data-backed equivalence checks to reject unsafe rewrites, retunes persistence choices with a learned cost model, and selects a globally compatible set of rewrites that avoids conflicts across transformations. This enables dependencyedge simplification, non-local semantic reuse, downstream-aware pruning, pipeline-aware work placement, rewrite-materialization co-optimization, and frequency-aware optimization while keeping the pipeline executable by the same SQL engine and orchestration framework. Our extensive evaluation shows that, on the open-source Tuva dbt project, DAGSmith reduces elapsed time by 42.6% and warehouse compute cost by 67.7%; these reductions are 22.8%/83.0% larger than materialization-only tuning and 98.1%/348.3% larger than state-of-the-art single-query rewriting techniques.