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.