Skip to main content

Query Optimization inside Models

Changing your materialization to incremental or throwing a larger Snowflake warehouse at the problem are easy fixes. However, they cost money.

The most effective way to improve the performance of your dbt project is to simply write better SQL. A poorly written JOIN can take 4 hours to run on a massive warehouse, while an optimized JOIN on the exact same data can run in 3 seconds on an X-Small warehouse.

As a Data Engineer, you must master SQL query optimization.

Topics Covered​

  • CTE Best Practices
  • Filter Early, Join Late
  • Avoiding String Manipulation in Joins
  • Cartesian Explosion

1. CTE Best Practices (Do not nest!)​

In dbt, we use Common Table Expressions (WITH ___ AS (SELECT ...)) extensively to make our code readable.

However, a massive mistake junior engineers make is nesting CTEs inside other CTEs, or writing 15 CTEs in a single file when 3 would do.

The Rule: A dbt model should ideally follow the "Import, Logical, Final" structure. Do not nest subqueries inside CTEs.

BAD SQL:

WITH filtered_users AS (
SELECT * FROM (
SELECT user_id, city FROM {{ ref('stg_users') }}
) WHERE city = 'Mumbai'
)
...

GOOD SQL:

WITH users AS (
SELECT * FROM {{ ref('stg_users') }}
),

filtered_users AS (
SELECT user_id, city
FROM users
WHERE city = 'Mumbai'
)
...

Why? Modern query optimizers (like Snowflake's compiler) are incredible at optimizing flat, sequential CTEs. Nested subqueries confuse the optimizer and lead to poor performance.

2. Filter Early, Join Late​

This is the golden rule of big data.

If you have a massive fct_transactions table (5 billion rows) and a dim_customers table (10 million rows), and you only want to analyze transactions from Delhi, when should you filter for Delhi?

BAD SQL (Join then Filter):

WITH joined_data AS (
SELECT t.amount, c.city
FROM transactions t
JOIN customers c ON t.customer_id = c.customer_id
)
SELECT * FROM joined_data WHERE city = 'Delhi'

Why is this bad? The database just performed a computationally massive join on 5 billion rows, and then immediately threw away 90% of the result. You wasted massive compute.

GOOD SQL (Filter then Join):

WITH delhi_customers AS (
SELECT customer_id, city
FROM customers
WHERE city = 'Delhi'
),

joined_data AS (
SELECT t.amount, c.city
FROM transactions t
-- We are now joining 5 billion rows against a tiny fraction of customers!
JOIN delhi_customers c ON t.customer_id = c.customer_id
)
...

3. Avoiding String Manipulation in Joins​

Data Engineers often inherit messy data. Sometimes the customer_id in the orders table has a prefix (e.g., CUST_101), but the user table just has the integer (101).

BAD SQL:

SELECT *
FROM orders o
JOIN users u
-- NEVER DO THIS!
ON o.customer_id = CONCAT('CUST_', u.user_id::VARCHAR)

Why is this bad? In order to perform the join, Snowflake must run the CONCAT and CAST functions on every single row of the 10-million row user table simultaneously. This destroys join performance and disables clustering.

GOOD SQL: Clean the data in the staging models first!

-- In stg_orders.sql
SELECT
REPLACE(customer_id, 'CUST_', '')::INT as clean_customer_id
FROM raw_orders

-- In fct_orders.sql
SELECT *
FROM {{ ref('stg_orders') }} o
JOIN {{ ref('stg_users') }} u
-- A perfect, fast integer-to-integer join!
ON o.clean_customer_id = u.user_id

4. The Cartesian Explosion (Fan-out)​

If your query usually takes 10 seconds, but suddenly takes 2 hours and generates a "Memory Spilled to Disk" warning in Snowflake, you probably created a Cartesian Join (a Fan-out).

This happens when you JOIN two tables on a key that is not unique.

If Arjun has 1 row in the orders table, but accidentally has 3 duplicate rows in the users table, a JOIN will return 3 rows for Arjun's single order! If you sum the revenue, the company makes 3x the money.

The Fix: You must test your data! Every single table that is placed on the right side of a LEFT JOIN must have a unique dbt test on the join key. If it fails the uniqueness test, you must add an intermediate model to aggregate or deduplicate the table before joining it to your massive fact table.

Summary​

  • Flatten your CTEs. Avoid nested subqueries.
  • Filter Early, Join Late. Reduce the size of your datasets before executing expensive joins.
  • Clean keys in Staging. Never perform string manipulation or math inside a JOIN ... ON clause.
  • Prevent Fan-outs. Always ensure the right side of your joins are strictly unique.