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.