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.