Clustering and partitioning in dbt

Introduction

Clustering and partitioning help organize data into groups that share similar features. This makes it easier to find patterns and make decisions.

Grouping customers by buying habits to target marketing.

Organizing documents by topic for faster search.

Segmenting images by colors or shapes for analysis.

Dividing sensor data into normal and abnormal groups.

Splitting data into parts to speed up queries.

Syntax

dbt

partition_by:
  field: <column_name>
  data_type: <type>
cluster_by: [<column_name1>, <column_name2>, ...]

partition_by divides data into separate parts based on a column.

cluster_by groups data within partitions to improve query speed.

Examples

This partitions data by the order_date column, creating one partition per date.

dbt

partition_by:
  field: order_date
  data_type: date

This clusters data by customer_id, grouping similar customers together inside partitions.

dbt

cluster_by: [customer_id]

This partitions data by region and clusters by product_category within each region.

dbt

partition_by:
  field: region
  data_type: string
cluster_by: [product_category]

Sample Program

This dbt model creates a table that groups sales by product and year. It partitions data by sales_year to separate years, and clusters by product_id to group similar products together. This helps queries run faster when filtering by year or product.

dbt

models:
  - name: sales_summary
    description: "Summary of sales partitioned by year and clustered by product"
    config:
      materialized: table
      partition_by:
        field: sales_year
        data_type: int
      cluster_by: [product_id]
    sql: |
      select
        product_id,
        sales_year,
        sum(amount) as total_sales
      from sales
      group by product_id, sales_year

Important Notes

Partitioning splits data into chunks, making it faster to scan only needed parts.

Clustering sorts data inside partitions to speed up filtering and aggregation.

Use partitioning on columns with many unique values like dates or regions.

Summary

Partitioning divides data into separate parts based on a column.

Clustering groups similar data inside partitions for faster queries.

Both help organize data to improve performance and analysis.