Warehouse-specific optimizations in dbt - Interactive Code Practice

Practice - 5 Tasks

1fill in blank

easy

Complete the code to set the materialization to table in a dbt model.

dbt

config(materialized = '[1]')

Drag options to blanks, or click blank then click option'

Aincremental

Btable

Cview

Dephemeral

Attempts:

3 left

💡 Hint

Common Mistakes

Using 'view' instead of 'table' creates a view, not a table.

Using 'incremental' requires additional configuration.

Setting materialized = 'table' tells dbt to create a physical table in the warehouse.

2fill in blank

medium

Complete the code to enable clustering on the column 'customer_id' in a Snowflake table.

dbt

config(clustering = ['[1]'])

Drag options to blanks, or click blank then click option'

Aorder_date

Bproduct_id

Ccustomer_id

Dregion

Attempts:

3 left

💡 Hint

Common Mistakes

Choosing a column not used in queries reduces clustering benefits.

Forgetting to specify clustering keys in a list.

Clustering on customer_id helps Snowflake organize data for faster queries on that column.

3fill in blank

hard

Fix the error in the incremental model config to correctly specify the unique key.

dbt

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

Drag options to blanks, or click blank then click option'

Aamount

Bdate

Ccustomer

Did

Attempts:

3 left

💡 Hint

Common Mistakes

Using a non-unique column causes incremental loads to fail.

Leaving unique_key empty or incorrect.

The unique_key should be the column that uniquely identifies rows, often 'id'.

4fill in blank

hard

Fill both blanks to create a Snowflake model with clustering on 'region' and materialized as a table.

dbt

config(materialized='[1]', clustering=['[2]'])

Drag options to blanks, or click blank then click option'

Atable

Bview

Cregion

Dcustomer_id

Attempts:

3 left

💡 Hint

Common Mistakes

Using 'view' materialization with clustering (not supported).

Clustering on a column not used in queries.

This config creates a physical table clustered by the 'region' column for better query performance.

5fill in blank

hard

Fill all three blanks to define an incremental model with unique key 'order_id', materialized as incremental, and clustered by 'order_date'.

dbt

config(materialized='[1]', unique_key='[2]', clustering=['[3]'])

Drag options to blanks, or click blank then click option'

Atable

Bincremental

Corder_id

Dorder_date

Attempts:

3 left

💡 Hint

Common Mistakes

Mixing materialization types incorrectly.

Using wrong columns for unique key or clustering.

This config sets the model as incremental, uses 'order_id' as the unique key, and clusters data by 'order_date' for performance.

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