Warehouse-specific optimizations in dbt - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ Predict Output

intermediate

Effect of clustering keys on query performance

Given a table clustered by customer_id, what is the expected effect on query performance when filtering by customer_id?

dbt

select * from sales where customer_id = 12345;

A The query will perform slower because clustering keys add overhead.

B The query will scan all micro-partitions, no performance change.

C The query will scan fewer micro-partitions, improving performance.

D The query will fail due to clustering key restrictions.

💡 Hint

Clustering keys group data physically by the key, reducing scan scope.

Clustering keys organize data so queries filtering on those keys scan fewer partitions, improving speed.

❓ data_output

intermediate

Result of using incremental model with unique key

What will be the number of rows in the target table after running this incremental model if the source has 1000 rows and 200 rows have changed?

dbt

incremental model with unique_key='id' that updates changed rows only

A 1000 rows

B 1200 rows

C 800 rows

D 200 rows

💡 Hint

Incremental models update existing rows based on unique keys.

The incremental model updates 200 changed rows but keeps total rows at 1000.

🔧 Debug

advanced

Identify the cause of slow query in dbt model

A dbt model runs slowly despite using clustering keys. Which option explains the likely cause?

dbt

select * from orders where order_date between '2023-01-01' and '2023-01-31'; -- clustered on customer_id

A The clustering key is on customer_id, but the filter is on order_date, so clustering is ineffective.

B The model is missing a primary key, causing slow performance.

C The query is slow because clustering keys always slow down queries.

D The query is slow because the table is not partitioned by order_date.

💡 Hint

Clustering helps only when filtering on the clustered columns.

Clustering on customer_id does not speed up queries filtering on order_date, so the query scans many partitions.

🚀 Application

advanced

Choosing partitioning strategy for large fact table

You have a large sales fact table with billions of rows. Which partitioning strategy is best to optimize queries filtering by date and region?

A Do not partition or cluster; rely on full table scans.

B Partition by region column and cluster by date column.

C Cluster by both date and region columns without partitioning.

D Partition by date column and cluster by region column.

💡 Hint

Partitioning works best on low-cardinality columns used in filters.

Partitioning by date reduces data scanned by date filters; clustering by region further optimizes region filters.

🧠 Conceptual

expert

Impact of materialized views on warehouse costs

How do materialized views affect compute costs in a cloud data warehouse?

A Materialized views increase storage costs but do not affect compute costs.

B Materialized views reduce compute costs by precomputing and storing results, reducing query time.

C Materialized views have no impact on compute costs as they are just query shortcuts.

D Materialized views increase compute costs because they require recomputation on every query.

💡 Hint

Think about how precomputed data affects query execution.

Materialized views store precomputed results, so queries run faster and use less compute, lowering costs.

Practice

1. What is the main purpose of using warehouse-specific optimizations in dbt models?

easy

A. To make dbt models run faster and reduce costs by using special warehouse features

B. To change the data schema automatically

C. To create new tables without any configuration

D. To write SQL queries without errors

Solution

1. Step 1: Understand the goal of warehouse-specific optimizations

   These optimizations use special features of the data warehouse to improve performance and cost efficiency.

2. Step 2: Match the purpose with the options

   Only To make dbt models run faster and reduce costs by using special warehouse features mentions making models faster and cheaper using warehouse features, which matches the goal.

3. Final Answer:

   To make dbt models run faster and reduce costs by using special warehouse features -> Option A

4. Quick Check:

   Optimization purpose = faster and cheaper models [OK]

Hint: Focus on speed and cost benefits of warehouse features [OK]

Common Mistakes:

• Confusing optimization with schema changes
• Thinking it automatically fixes SQL errors
• Assuming it creates tables without config

2. Which of the following is the correct way to apply clustering in a dbt model using config()?

easy

A. config(cluster_by = 'column_name')

B. config(clusters = ['column_name'])

C. config(clustering = ['column_name'])

D. config(clustering_columns = 'column_name')

Solution

1. Step 1: Recall the correct config syntax for clustering in dbt

   The correct syntax uses the key clustering with a list of column names.

2. Step 2: Compare options to the correct syntax

   Only config(clustering = ['column_name']) uses clustering = ['column_name'], which matches the correct form.

3. Final Answer:

   config(clustering = ['column_name']) -> Option C

4. Quick Check:

   Correct config key and list format = config(clustering = ['column_name']) [OK]

Hint: Remember clustering uses 'clustering' key with list syntax [OK]

Common Mistakes:

• Using wrong config key names
• Passing string instead of list
• Using plural or incorrect keys

3. Given this dbt model config for incremental loading:

config(
  materialized='incremental',
  unique_key='id'
)

select * from source_table

What happens when you run this model multiple times?

medium

A. No data is loaded because unique_key is missing

B. The entire target table is replaced every time

C. Rows with duplicate 'id' values cause an error

D. Only new rows with unique 'id' values are added to the target table

Solution

1. Step 1: Understand incremental materialization with unique_key

   Incremental models add only new or changed rows based on the unique_key to avoid full reloads.

2. Step 2: Analyze the behavior on multiple runs

   Since unique_key='id', only rows with new 'id' values are inserted, not replacing the whole table.

3. Final Answer:

   Only new rows with unique 'id' values are added to the target table -> Option D

4. Quick Check:

   Incremental + unique_key = add new rows only [OK]

Hint: Incremental with unique_key adds only new unique rows [OK]

Common Mistakes:

• Thinking incremental replaces whole table
• Assuming duplicates cause errors
• Missing unique_key causes no load

4. You wrote this dbt model config:

config(
  materialized='incremental',
  unique_key=['id']
)

select * from source_table

But the model fails to run. What is the likely error?

medium

A. unique_key should be a string, not a list

B. materialized must be set to 'table' for incremental

C. The select statement is missing a where clause

D. config cannot be used with incremental models

Solution

1. Step 1: Check the type of unique_key in config

   In dbt, unique_key must be a string naming the column, not a list.

2. Step 2: Identify the error cause

   Passing unique_key as a list causes a config error, so the model fails.

3. Final Answer:

   unique_key should be a string, not a list -> Option A

4. Quick Check:

   unique_key type must be string [OK]

Hint: unique_key is a single column name string, not a list [OK]

Common Mistakes:

• Passing unique_key as list instead of string
• Confusing materialized types
• Thinking config disallows incremental

5. You want to optimize a large fact table in Snowflake using dbt. Which combination of warehouse-specific optimizations is best to improve query speed and reduce costs?

hard

A. Use full refresh materialization and no clustering

B. Use clustering keys on date columns and incremental materialization with merge strategy

C. Use ephemeral models only without materialization

D. Use incremental materialization without unique_key and no clustering

Solution

1. Step 1: Identify best practices for large Snowflake fact tables

   Clustering keys on date columns improve pruning and query speed; incremental with merge avoids full reloads.

2. Step 2: Evaluate options

   Use clustering keys on date columns and incremental materialization with merge strategy combines clustering and incremental with merge, which is optimal. Others miss clustering or unique_key or use inefficient materialization.

3. Final Answer:

   Use clustering keys on date columns and incremental materialization with merge strategy -> Option B

4. Quick Check:

   Clustering + incremental merge = best for large tables [OK]

Hint: Combine clustering and incremental with merge for big tables [OK]

Common Mistakes:

• Skipping clustering on large tables
• Using full refresh unnecessarily
• Omitting unique_key in incremental