Query profiling and optimization in dbt - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ Predict Output

intermediate

What is the output of this dbt model's run result summary?

Given a dbt run with the following model configuration, what will be the value of execution_time in seconds if the model took 3 minutes and 45 seconds to run?

dbt

models/my_model.sql
-- model config:
-- materialized: table
-- run_started_at: 2024-04-01 10:00:00
-- run_finished_at: 2024-04-01 10:03:45

A 345

B 180

C 225

D 375

💡 Hint

Convert minutes and seconds to total seconds.

3 minutes and 45 seconds equals (3 * 60) + 45 = 225 seconds.

❓ data_output

intermediate

Which option shows the correct profile output for a slow query?

You ran a dbt model and got the following profile output snippet showing the query took 120 seconds and scanned 1 billion rows. Which option correctly represents this profiling data as a dictionary?

A {"execution_time_sec": 120, "rows_scanned": 1000000000}

B {"execution_time_sec": "120 seconds", "rows_scanned": 1000000000}

C {"execution_time_sec": 120, "rows_scanned": 1000000}

D {"execution_time_sec": 120, "rows_scanned": "1 billion"}

💡 Hint

Use numeric values for time and rows scanned.

The correct profile output uses numeric values for both execution time and rows scanned.

🔧 Debug

advanced

Identify the cause of slow query in this dbt model

Consider this dbt model SQL snippet:

select
  user_id,
  count(*) as total_orders
from orders
where order_date >= '2023-01-01'
group by user_id
order by total_orders desc
limit 10

Which of the following is the most likely cause of slow performance?

A Missing index on order_date column causing full table scan

B Using limit without order by causes slow sorting

C Grouping by user_id is invalid syntax

D Filtering on order_date is ignored by the database

💡 Hint

Think about what helps speed up filtering on date columns.

Without an index on order_date, the database must scan all rows, slowing the query.

🚀 Application

advanced

Choose the best dbt configuration to optimize incremental model runs

You have a large table that updates daily. You want your dbt incremental model to only process new rows based on a timestamp column updated_at. Which configuration snippet will achieve this?

A

incremental_strategy: append
unique_key: id
where: updated_at > (select max(updated_at) from {{ this }})

B

incremental_strategy: merge
unique_key: id
where: updated_at > (select max(updated_at) from {{ this }})

C

incremental_strategy: insert_overwrite
unique_key: id
where: updated_at < (select max(updated_at) from {{ this }})

D

incremental_strategy: append
unique_key: id
where: updated_at < (select min(updated_at) from {{ this }})

💡 Hint

Use a strategy that updates existing rows and inserts new ones.

The merge strategy with a unique key and filtering on updated_at greater than the max in the target table ensures only new or changed rows are processed.

🧠 Conceptual

expert

What is the primary benefit of using dbt's query profiling features?

Choose the best explanation for why dbt's query profiling is important in data projects.

A It generates visual dashboards for business users without coding.

B It automatically fixes SQL syntax errors in models.

C It replaces the need for database indexes by caching results.

D It helps identify slow queries and resource-heavy operations to optimize performance.

💡 Hint

Think about what profiling means in computing.

Profiling helps find slow or costly queries so you can improve them and save time and resources.

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