Why Slower SQL Can Make Your dbt Pipeline Faster

Why explicit dependencies let us go beyond rewriting one query at a time.

SQL is no longer only a hand-written interface for individual analytical questions. It is increasingly the implementation language for recurring data products: governed dashboards, AI-ready feature tables, self-service BI datasets, and org-wide reporting layers. At the same time, BI tools and now AI assistants are changing who can produce that SQL.

Someone who otherwise could not write a complex SQL can now state their question in natural language and ask an AI model to generate the SQL. By lowering that barrier, more people are now asking questions. For hand-written SQL, complexity was bounded by the user’s own skills. However, with frontier models, users can generate queries they could not write themselves. My expectation is more queries and more complex generated SQL—not just the same workload typed faster.

For context, ++dbt Labs’ 2025 survey++ found that 70% of data professionals surveyed use AI to assist with code development.

The efficiency gap

As a result, there is a growing efficiency gap between the complexity of generated SQL and what today’s query optimizers can effectively optimize. ++Source-to-source query rewriting++ tries to narrow that gap by expressing the same computation in a form the query optimizer is more likely to execute more efficiently.

But what should we ask a rewriter to optimize? Traditionally, the answer has been one SQL statement. That is increasingly a poor fit for a workload made up of interconnected computations, running over and over.

The rise of SQL pipelines

Another trend that has been happening is that data teams are increasingly applying software-engineering practices—modularity, testing, and version control—to large, recurring SQL pipelines. This trend is driven by complex business logic that must be computed repeatedly and reused consistently across governed dashboards, AI-ready feature tables, self-service BI datasets, compliance reports, and downstream applications.

Tools like ++dbt++ allow data teams to write transformations as version-controlled SQL SELECT statements, called “models,” declare dependencies among them (i.e., which model’s output should be fed into which model), and compile the resulting graph into SQL jobs executed by a data warehouse. In this post, I use “model” and “query” interchangeably.

Consider the toy example below. Here, we start with raw orders and clean them up. Then we use the cleaned data to calculate daily revenue and find active customers. Each model defines one of those steps; the arrows show which results it needs before it can run.

Figure 1. An example of a small dbt pipeline DAG. The arrows show explicit dependencies.

There are many other tools for defining these kinds of DAGs, e.g., ++SQLMesh++, ++Dagster++, and ++Airflow++. dbt just happens to be the most popular framework for defining SQL pipeline DAGs. Each edge records that one node reads another’s output. We also choose which results to persist and how often to rerun the queries.

The pipeline optimization gap

Unlike the toy example shown in Figure 1, most dbt projects have a lot more nodes. ++For example, a 2023 breakdown of dbt’s usage data reported that 52% of projects had at least 100 models, including 20% with 1,000 or more.++

At that scale, you cannot simply combine all the SQL into a single query. Inlining every upstream model’s SQL into each downstream query can produce a nested SQL query that’s tens or hundreds of layers deep, beyond what current query optimizers or rewriters can effectively handle.

Single-query rewriters, including LLM-based techniques such as ++GenRewrite++, can improve an individual query. But looking at that query alone does not tell us which parts of its result other models need, whether another part of the pipeline repeats the same work, or how often the result actually needs to be refreshed.

While that complexity may look like an optimization nightmare, I personally see the explicit dependencies as a blessing in disguise. They expose information that we miss when optimizing one query at a time.

When a slower query helps the whole pipeline

Traditional query rewriting has a straightforward goal: find an alternative query that’s both equivalent and faster than the original query. Interestingly, having access to explicit dependencies means we can now relax both requirements for the intermediate queries:

First, an intermediate query can produce extra outputs (and hence, not be equivalent to the original query), as long as the extra outputs are reused enough by later queries.

Similarly, the rewritten query may even be slower on its own, but its extra output (when materialized) may bring about sufficient savings that would make the entire end-to-end pipeline finish faster. 

The equivalence requirement still applies to the pipeline’s required outputs. However, as explained above we can be more liberal with the internal/intermediate models.

Dependencies as optimization signals

What can we do with those dependencies? Here are six ways to optimize a pipeline while preserving its required outputs.

Consider an insurance use case where we need a pipeline to process claims. Several models scan the same claims data to classify services. In the original pipeline example (see Figure 2a), the nursing model also reads the equipment model’s output to exclude equipment claims.

Another input is code_catalog, a lookup table of service codes. The claims_summary model joins claims to this catalog, then applies code-grouping logic to the descriptions. We’ll keep returning to this pipeline for all six optimizations; Figure 2b shows their combined effect.

Figure 2. Before and after: service_flags replaces repeated classification work, equipment disappears, and code grouping moves into its own model.

1. Dependency-edge simplification

Sometimes a model reads another model only to exclude a set of rows. Seeing both SQL definitions can reveal a direct filter that removes the dependency.

In the example above, we can replace nursing’s use of equipment as an exclusion list with an equivalent filter over claims.

Here is the original and rewritten SQL for this simplified example. Assume claim_id is a primary key and category is non-NULL, with claims declared under a source named raw:

-- BEFORE: equipment model:
SELECT claim_id
FROM {{ source('raw', 'claims') }}
WHERE category = 'equipment'

-- BEFORE: nursing model:
SELECT *
FROM {{ source('raw', 'claims') }} c
WHERE c.facility_code IN ('31', '32')
  AND NOT EXISTS (
    SELECT 1 FROM {{ ref('equipment') }} e
    WHERE e.claim_id = c.claim_id
  )

-- AFTER: nursing model:
SELECT *
FROM {{ source('raw', 'claims') }} c
WHERE c.facility_code IN ('31', '32')
  AND c.category <> 'equipment'

Note that both versions of nursing return the same rows, but the rewritten query no longer reads equipment. The ++ref(‘equipment’)++ call declares that model dependency; after rewriting, only ++source(‘raw’, ‘claims’)++ remains.

2. Non-local semantic reuse

The same business logic can appear in distant parts of the DAG, even when the SQL looks different. We can factor that repeated work into a shared computation to save cost and time.

Going back to our insurance example, we can put the classifications in a shared service_flags model and reuse them across dialysis, psychiatric, and nursing (Figure 2b).

3. Downstream-aware pruning

Downstream dependencies tell us which results actually matter. We can remove columns, joins, or entire intermediate models if we can show they do not impact the required outputs of the DAG.

Using our running example, after removing nursing’s dependency on equipment in #1, we can drop equipment if nothing else needs it and it is not itself a required output.

4. Pipeline-aware work placement

An expensive computation may happen before a later model filters out most rows, or after a join expands them. In those cases, moving the expensive computation after the filter or before the join can reduce the work without changing the required outputs.

In the lower branch of Figure 2, we can compute the code groups in service_code_flags before joining code_catalog to claims. That means doing the expensive work on the small catalog, rather than repeating it for every matching claim.

5. Rewrite-materialization co-optimization

Materializing a model’s output and rewriting its SQL can each be effective optimizations on their own. However, some rewrites make a query more expensive but produce additional outputs that, when materialized and reused downstream, reduce the pipeline’s total cost. Choosing rewrites and materializations separately can miss these combinations, which is why we should co-optimize them in a DAG.

In our example, service_flags computes both dialysis and psychiatric flags. That extra work may only pay off if we also materialize service_flags for the downstream models to reuse.

6. Frequency-aware optimization

We can avoid rerunning queries whose inputs have not changed or whose outputs are not yet needed. Slow-changing computations can also move into separate models, so that each can be scheduled according to its downstream freshness needs..

The service_code_flags model from #4 depends only on code_catalog. We can then rerun the grouping to reflect catalog changes, not every time new claims arrive.

Turning these ideas into a system: DAGSmith

Recently, in collaboration with ++Jie Liu++ and ++Lin Ma++, we built ++DAGSmith++. It is a dependency-aware source-to-source rewriting system for SQL pipeline DAGs. DAGSmith rewrites queries, chooses which results to materialize, and adjusts how often each query runs to reduce total pipeline cost.

Rather than asking an LLM to rewrite the entire project in one shot, DAGSmith first analyzes the SQL and dependencies to find promising regions. An LLM then proposes refactorings within those regions. But generating a rewrite is only the first step. DAGSmith uses separate stages to propose changes, criticize them, generate SQL, and check the results against real data.

DAGSmith evaluates each candidate together with its materialization choices using a learned cost model, then selects rewrites that do not conflict with one another. It also accounts for how often queries run to identify unnecessary reruns and opportunities to separate slow-changing work from frequently run models.

You can still execute the rewritten pipeline with the same SQL engine and orchestration framework. In terms of correctness guarantees, DAGSmith currently tests its rewrites on the project’s own data but does not guarantee equivalence for every possible input. The user/analyst still needs to review the rewrite suggestions before deployment or rely on their existing CI/CD process for testing.

You can read more details and the full experiments in our full paper, ++DAGSmith: Dependency-Aware Rewriting for dbt-Style SQL Pipelines++.

Is your team struggling with expensive SQL pipelines? Feel free to reach out; we are happy to brainstorm and potentially collaborate with you.