Data Clustering

Snowflake · Storage & Optimization

Theory

Clustering describes how well rows with similar values sit together in the same micro-partitions. Data loaded in date order is naturally well clustered on date, but after many loads and updates the value ranges of partitions can overlap, and pruning gets worse. A clustering key, set with CLUSTER BY (order_date, region), tells Snowflake which columns to keep co-located.

Automatic Clustering is a serverless background service that reorganises partitions when the table drifts, and it consumes credits. SYSTEM$CLUSTERING_INFORMATION shows metrics like average clustering depth, where lower means less overlap and better pruning.

Why we use it

On very large tables, poor clustering means filters cannot skip partitions, so queries scan far more data than they return. A good clustering key restores pruning and can cut scan time dramatically.

When to use it

Consider a clustering key for multi-terabyte tables that are queried often with filters on the same columns, and where Query Profile shows poor pruning. Skip it for small tables or tables that change constantly, where reclustering cost outweighs the benefit.

What a Clustering Key Does

Tells Snowflake which columns to use when co-locating similar rows together across micro-partitions, sharpening the metadata-based pruning that micro-partitions already provide.

When It Actually Matters

Mainly relevant on very large, multi-terabyte tables with a clear, frequently-filtered column like a date or customer_id — small tables rarely benefit enough to justify it.

Automatic Reclustering

Snowflake reclusters automatically in the background as new data arrives and skews the physical layout, so you don't need to manually rebuild the clustering over time.

How it works

  1. Large table with slow filtered queries
  2. Check pruning in Query Profile and SYSTEM$CLUSTERING_INFORMATION
  3. Is the table very large and filtered on the same columns? Yes: Choose a key: low to medium cardinality filter columns. No: Do not cluster; tune elsewhere.
  4. ALTER TABLE ... CLUSTER BY (key columns)
  5. Automatic Clustering reorganises partitions in the background
  6. Monitor clustering depth and credit usage

Example

ALTER TABLE orders
  CLUSTER BY (order_date, region);

Key points

Snowflake topics

Snowflake Fundamentals

Warehouses & Performance

Staging & SnowSQL

Tables & Data Handling

Cloud Integrations

Storage & Optimization

Security & Governance

Views, Pipelines & Automation

Data Types & SQL Fundamentals

Semi-Structured Data Deep Dive

Advanced Features

Data Sharing & Collaboration

Cost Management & Monitoring

Databases, Schemas & Objects

Performance Deep Dive

Advanced Security & Governance

Stored Procedures & UDFs

Ecosystem & Integrations

SnowPro Certification Prep

T
Tech Town
Log InSign Up Free
Home SQL Python DSA Power BI Snowflake Databricks Spark Gen AI Practice Problems Contest Blog

Mastering Snowflake

The modern data cloud, from architecture to SnowPro certification. Click any topic in the sidebar to open it right here.

{{ sectionCount }}
Sections
{{ topicCount }}
Topics
Free
Forever
Hands-on practice Practice Snowflake SQL Sharpen the SQL you use in Snowflake: 150 query problems in a live editor with instant checking. Start practising →
Section {{ sec.num }}

{{ sec.label }}

Snowflake  /  {{ current.sectionLabel }}  /  {{ current.name }}

{{ current.name }}

Theory

{{ current.def }}

Why we use it

{{ rich.why }}

When to use it

{{ rich.when }}

{{ sec.h }}

{{ sec.body }}

{{ sec.body }}

Diagram

{{ rich.dg.caption }}

{{ tb.title }}
{{ c.v }}
{{ rich.dg.op }}
➜
{{ rich.dg.output.title }}
{{ c.v }}
{{ nd.label }}
{{ nd.sub }}
{{ nd.label }}
{{ nd.sub }}
{{ rich.dg.left.title }}
{{ it }}
{{ rich.dg.right.title }}
{{ it }}
On screen — where to click

{{ rich.ui.title }}

{{ u.text }}
  1. {{ st.n }}{{ st.text }}
{{ rich.ui.title }}
{{ u.text }}
Flowchart — how it works
{{ nd.text }}
{{ nd.text }}
◇ {{ nd.text }}
Yes → {{ nd.yes }}
No → {{ nd.no }}
Syntax
{{ current.syntax }}
Example
{{ current.example }}
Want to run this yourself? Unlike the SQL and Python courses, Snowflake needs a real account — there's no in-browser engine for it. Click Copy above, then paste it into a free Snowflake trial (30 days, no card required) and run it in Snowsight.
Key Points — click to check off as you learn
  • {{ pt.mark }} {{ pt.text }}

Share this topic