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.