Clustering and partitioning in dbt - Interactive Code Practice

Practice - 5 Tasks

1fill in blank

easy

Complete the code to create a clustered table by the column 'customer_id'.

dbt

CREATE TABLE sales_data_clustered CLUSTER BY [1] AS SELECT * FROM sales_data;

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

Acustomer_id

Border_date

Cproduct_id

Dregion

Attempts:

3 left

💡 Hint

Common Mistakes

Choosing a column that is not frequently used in queries.

Using a non-existent column name.

Clustering by 'customer_id' groups data physically by that column, improving query speed on that key.

2fill in blank

medium

Complete the code to partition the table by 'order_year'.

dbt

CREATE TABLE sales_data_partitioned PARTITION BY [1] AS SELECT * FROM sales_data;

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

Aorder_month

Border_year

Ccustomer_id

Dregion

Attempts:

3 left

💡 Hint

Common Mistakes

Partitioning by a column with too many unique values.

Using a column that is not related to time for partitioning.

Partitioning by 'order_year' divides the table into parts by year, making queries on specific years faster.

3fill in blank

hard

Fix the error in the partitioning syntax by filling the blank.

dbt

CREATE TABLE sales_summary PARTITIONED BY ([1]) AS SELECT * FROM sales_data;

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

Aorder_year

Border_year DESC

Corder_year, region

Dorder_year ASC

Attempts:

3 left

💡 Hint

Common Mistakes

Adding sorting directions inside partition clause.

Listing multiple columns separated by commas.

The correct syntax uses 'PARTITIONED BY (column_name)' without ordering or multiple columns.

4fill in blank

hard

Fill both blanks to cluster by 'region' and partition by 'order_month'.

dbt

CREATE TABLE sales_region_month CLUSTER BY [1] PARTITION BY [2] AS SELECT * FROM sales_data;

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

Aregion

Border_month

Ccustomer_id

Dorder_year

Attempts:

3 left

💡 Hint

Common Mistakes

Swapping clustering and partitioning columns.

Using columns not suitable for clustering or partitioning.

Clustering by 'region' groups data physically, and partitioning by 'order_month' divides data by month.

5fill in blank

hard

Fill all three blanks to create a table partitioned by 'order_year', clustered by 'customer_id', and select only orders with amount > 100.

dbt

CREATE TABLE high_value_orders PARTITION BY [1] CLUSTER BY [2] AS SELECT * FROM sales_data WHERE order_amount [3] 100;

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

Aorder_year

Bcustomer_id

C>

D<

Attempts:

3 left

💡 Hint

Common Mistakes

Using wrong comparison operator in WHERE clause.

Mixing up partition and cluster columns.

Partition by 'order_year', cluster by 'customer_id', and filter orders with amount greater than 100.

Practice

1. What is the main purpose of partitioning in dbt models?

easy

A. To create machine learning models

B. To group similar data inside partitions

C. To encrypt data for security

D. To divide data into separate parts based on a column

Solution

1. Step 1: Understand partitioning concept

   Partitioning splits a large table into smaller parts based on a column, like date or region.

2. Step 2: Differentiate from clustering

   Clustering groups similar data inside partitions but does not split the table itself.

3. Final Answer:

   To divide data into separate parts based on a column -> Option D

4. Quick Check:

   Partitioning = dividing data [OK]

Hint: Partitioning splits data by column values [OK]

Common Mistakes:

• Confusing clustering with partitioning
• Thinking partitioning groups data inside partitions
• Assuming partitioning creates ML models

2. Which of the following is the correct syntax to add clustering in a dbt model's config block?

easy

A. cluster_by = ['column_name']

B. clustering = ['column_name']

C. cluster = ['column_name']

D. clusters = ['column_name']

Solution

1. Step 1: Recall dbt clustering syntax

   In dbt, clustering is set using cluster_by = ['column_name'] inside the model config.

2. Step 2: Check other options

   Options like clustering, cluster, or clusters are incorrect keywords.

3. Final Answer:

   cluster_by = ['column_name'] -> Option A

4. Quick Check:

   Correct keyword is cluster_by [OK]

Hint: Use 'cluster_by' keyword exactly in config [OK]

Common Mistakes:

• Using clustering instead of cluster_by
• Misspelling cluster_by keyword
• Using plural clusters instead of cluster_by

3. Given this dbt model config:

config(
  materialized='table',
  partition_by={'field': 'order_date', 'data_type': 'date'},
  cluster_by=['customer_id']
)

What is the effect of this configuration?

medium

A. Data is partitioned by order_date and clustered by customer_id

B. Data is partitioned by customer_id and clustered by order_date

C. Data is clustered by order_date only

D. Data is partitioned by order_date only, no clustering

Solution

1. Step 1: Identify partitioning field

   The config uses partition_by={'field': 'order_date', 'data_type': 'date'}, so partitioning is by order_date.

2. Step 2: Identify clustering field

   The config sets cluster_by=['customer_id'], so clustering is by customer_id.

3. Final Answer:

   Data is partitioned by order_date and clustered by customer_id -> Option A

4. Quick Check:

   Partition = order_date, Clustering = customer_id [OK]

Hint: Partition_by sets partition, cluster_by sets cluster columns [OK]

Common Mistakes:

• Swapping partition and clustering columns
• Ignoring clustering setting
• Assuming clustering replaces partitioning

4. You wrote this dbt model config but get an error:

config(
  materialized='table',
  partition_by='order_date',
  cluster_by=['customer_id']
)

What is the likely cause of the error?

medium

A. Missing comma after cluster_by list

B. partition_by should be a dictionary, not a string

C. materialized cannot be 'table'

D. cluster_by should be a string, not a list

Solution

1. Step 1: Check partition_by syntax

   Partitioning requires a dictionary with 'field' and 'data_type', not just a string.

2. Step 2: Verify cluster_by and materialized

   cluster_by as a list is correct, and materialized='table' is valid.

3. Final Answer:

   partition_by should be a dictionary, not a string -> Option B

4. Quick Check:

   partition_by needs dict, not string [OK]

Hint: partition_by needs dict with field and data_type [OK]

Common Mistakes:

• Using string instead of dict for partition_by
• Changing cluster_by to string incorrectly
• Assuming materialized='table' is invalid

5. You want to optimize query speed on a large sales table by partitioning by sale_year and clustering by region and product_category. Which dbt config is correct?

hard

A. config(materialized='table', partition_by='sale_year', cluster_by=['region', 'product_category'])

B. config(materialized='table', partition_by={'field': 'sale_year'}, cluster_by='region, product_category')

C. config(materialized='table', partition_by={'field': 'sale_year', 'data_type': 'int'}, cluster_by=['region', 'product_category'])

D. config(materialized='table', partition_by={'field': 'sale_year', 'data_type': 'string'}, cluster_by=['region'])

Solution

1. Step 1: Check partition_by format and data type

   Partitioning requires a dictionary with 'field' and 'data_type'. Since sale_year is a number, 'int' is correct.

2. Step 2: Check cluster_by format

   cluster_by accepts a list of columns, so ['region', 'product_category'] is correct.

3. Step 3: Validate other options

   config(materialized='table', partition_by='sale_year', cluster_by=['region', 'product_category']) uses string for partition_by (incorrect). config(materialized='table', partition_by={'field': 'sale_year'}, cluster_by='region, product_category') misses data_type and cluster_by is a string (incorrect). config(materialized='table', partition_by={'field': 'sale_year', 'data_type': 'string'}, cluster_by=['region']) uses wrong data_type and only one clustering column.

4. Final Answer:

   config(materialized='table', partition_by={'field': 'sale_year', 'data_type': 'int'}, cluster_by=['region', 'product_category']) -> Option C

5. Quick Check:

   Partition dict + cluster_by list = correct [OK]

Hint: Partition needs dict with data_type; cluster_by needs list [OK]

Common Mistakes:

• Using string instead of dict for partition_by
• Passing cluster_by as string instead of list
• Wrong data_type for partition column