Query profiling and optimization in dbt - Step-by-Step Execution

Concept Flow - Query profiling and optimization

Write SQL Model in dbt

↓

Run dbt compile & run

↓

Capture Query Profile

↓

Analyze Query Steps

↓

Identify Bottlenecks

↓

Apply Optimizations

↓

Re-run & Compare Performance

↓

Repeat if needed or Finalize

This flow shows how dbt runs a query, profiles it, finds slow parts, applies fixes, and checks improvements.

Execution Sample

dbt

select user_id, count(*) as orders_count
from {{ ref('orders') }}
group by user_id
order by orders_count desc
limit 5

This dbt model counts orders per user, orders by count descending, and limits to top 5 users.

Execution Table

Step	Action	Query Part	Profile Info	Result/Next Step
1	Compile dbt model	Full SQL with ref resolved	SQL ready to run	Proceed to run query
2	Run query	SELECT with GROUP BY and ORDER BY	Execution time: 1200ms, Rows scanned: 100000	Collect query profile
3	Profile analysis	GROUP BY aggregation	High CPU usage, 800ms spent here	Consider indexing or pre-aggregation
4	Profile analysis	ORDER BY and LIMIT	Sort takes 300ms	Check if sorting can be optimized
5	Optimization applied	Add index on user_id	Expected to reduce GROUP BY time	Re-run query
6	Re-run query	Same SQL	Execution time: 700ms, Rows scanned: 100000	Performance improved
7	Final check	Compare before and after	43% faster execution	Optimization successful

💡 Query optimized and performance improved, no further changes needed

Variable Tracker

Variable	Start	After Step 3	After Step 5	Final
Execution Time (ms)	N/A	1200	N/A	700
Rows Scanned	N/A	100000	N/A	100000
CPU Usage (ms)	N/A	800	N/A	500
Sort Time (ms)	N/A	300	N/A	200

Key Moments - 3 Insights

Why does the GROUP BY step take most of the time?

Because it processes many rows and aggregates them, as shown in execution_table row 3 where CPU usage is high.

How does adding an index help the query?

Adding an index on user_id speeds up grouping by allowing faster data access, reflected in execution_table row 6 with reduced execution time.

Why do we re-run the query after optimization?

To verify if the changes improved performance, as shown in execution_table rows 6 and 7 comparing before and after times.

Visual Quiz - 3 Questions

Look at the execution table, what was the execution time before optimization?

A 700 ms

B 1200 ms

C 800 ms

D 300 ms

💡 Hint

Check the 'Execution time' in row 2 of the execution_table.

At which step was the index added to optimize the query?

A Step 5

B Step 3

C Step 4

D Step 6

💡 Hint

Look for 'Optimization applied' in the execution_table.

If the sort time was not reduced after optimization, what would likely happen?

A Execution time would decrease more

B Execution time would increase

C Overall execution time would remain the same

D Rows scanned would increase

💡 Hint

Refer to variable_tracker columns for Sort Time and Execution Time changes.

Concept Snapshot

Query profiling in dbt means running your SQL models,
checking how long each part takes,
finding slow steps like GROUP BY or ORDER BY,
then applying fixes like indexes or pre-aggregations.
Re-run to confirm improvements.
Repeat until performance is good.

Full Transcript

In dbt, query profiling and optimization involves writing SQL models and running them to see how long each part takes. We start by compiling the model and running the query. Then we collect profile data like execution time and CPU usage. We analyze which parts, such as GROUP BY or ORDER BY, take the most time. Next, we apply optimizations like adding indexes. After that, we re-run the query to check if performance improved. This process repeats until the query runs efficiently. The execution table shows each step with timings and actions. Variable tracking helps us see how execution time and CPU usage change. Key moments clarify why grouping is slow, how indexes help, and why re-running is important. The quiz tests understanding of these steps and their effects. This method helps make dbt queries faster and more efficient.

Practice

1.

What is the main purpose of query profiling in dbt?

easy

A. To write new SQL queries from scratch

B. To create visual dashboards

C. To find slow parts of your data queries

D. To backup your database automatically

Solution

1. Step 1: Understand the goal of query profiling

   Query profiling is used to analyze how queries run and identify slow parts.

2. Step 2: Match the purpose with the options

   Only To find slow parts of your data queries describes finding slow parts, which is the main goal.

3. Final Answer:

   To find slow parts of your data queries -> Option C

4. Quick Check:

   Query profiling = find slow parts [OK]

Hint: Profiling = finding slow query parts fast [OK]

Common Mistakes:

• Confusing profiling with writing queries
• Thinking profiling creates dashboards
• Assuming profiling backs up data

2.

Which of the following is the correct way to run a dbt model named sales_summary from the command line?

easy

A. dbt build sales_summary

B. dbt run --model sales_summary

C. dbt execute sales_summary

D. dbt run model sales_summary

Solution

1. Step 1: Recall dbt command syntax for running models

   The correct command uses 'dbt run --model <model_name>'.

2. Step 2: Compare options with correct syntax

   Only dbt run --model sales_summary matches the correct syntax exactly.

3. Final Answer:

   dbt run --model sales_summary -> Option B

4. Quick Check:

   Run model = dbt run --model [OK]

Hint: Use 'dbt run --model' to run specific models [OK]

Common Mistakes:

• Using 'dbt execute' which is invalid
• Omitting the '--model' flag
• Using wrong command order

3.

Given this dbt model SQL snippet:

select customer_id, count(*) as order_count
from orders
group by customer_id

Which database command helps you understand why this query might be slow?

medium

A. SELECT * FROM orders LIMIT 10

B. DESCRIBE orders

C. SHOW TABLES

D. EXPLAIN SELECT customer_id, count(*) as order_count from orders group by customer_id

Solution

1. Step 1: Identify the tool for query profiling

   The EXPLAIN command shows how the database runs a query and where it may be slow.

2. Step 2: Match EXPLAIN with the given query

   EXPLAIN SELECT customer_id, count(*) as order_count from orders group by customer_id uses EXPLAIN with the query, which helps understand performance.

3. Final Answer:

   EXPLAIN SELECT customer_id, count(*) as order_count from orders group by customer_id -> Option D

4. Quick Check:

   EXPLAIN = query profiling tool [OK]

Hint: Use EXPLAIN before query to profile it [OK]

Common Mistakes:

• Using DESCRIBE which shows table structure only
• Using SHOW TABLES which lists tables
• Running SELECT without profiling

4.

Look at this dbt model SQL snippet:

select * from orders where order_date = '2023-01-01'

After profiling, you see a full table scan causing slowness. What is the best fix?

medium

A. Add an index on the order_date column

B. Remove the where clause

C. Select only order_id column

D. Change order_date to order_id in where clause

Solution

1. Step 1: Understand cause of full table scan

   Full table scans happen when no index supports the filter condition.

2. Step 2: Choose optimization to speed up filtering

   Adding an index on order_date helps the database find rows faster.

3. Final Answer:

   Add an index on the order_date column -> Option A

4. Quick Check:

   Index on filter column = faster queries [OK]

Hint: Index columns used in WHERE to avoid full scans [OK]

Common Mistakes:

• Removing WHERE loses filtering purpose
• Selecting fewer columns doesn't fix scan
• Changing filter column arbitrarily

5.

You have a dbt model joining two large tables:

select c.customer_id, sum(o.amount) as total
from customers c
join orders o on c.customer_id = o.customer_id
group by c.customer_id

Profiling shows the join is slow. Which optimization is best?

hard

A. Ensure both customer_id columns are indexed

B. Remove the GROUP BY clause

C. Replace join with a subquery

D. Add a LIMIT 10 to the query

Solution

1. Step 1: Identify cause of slow join

   Joins on large tables are slow if join keys lack indexes.

2. Step 2: Apply indexing to join keys

   Indexing customer_id on both tables speeds up join operations.

3. Final Answer:

   Ensure both customer_id columns are indexed -> Option A

4. Quick Check:

   Index join keys = faster joins [OK]

Hint: Index join keys to speed up large table joins [OK]

Common Mistakes:

• Removing GROUP BY changes query meaning
• Using subqueries may not improve speed
• Adding LIMIT only reduces output, not join speed