Clustering

How Snowflake stores data

Every table is split into micro-partitions — compressed chunks of rows. For each chunk Snowflake records the min and max of every column. When a query filters on person_id = 123, chunks whose person_id range cannot contain 123 are never read at all. This is partition pruning. It happens automatically, but it is only effective when similar values are stored together: in a table loaded in random order, every chunk spans the whole range of person_ids and nothing can be skipped.

cluster_by tells dbt to sort the data as it builds the table, so values that are queried together are stored together.

The default is usually fine

Without any cluster_by, Snowflake still micro-partitions everything — the data just sits in whatever order it was written. This natural clustering is often adequate: data loaded or built in date order prunes well for date filters; a table built from an ordered upstream inherits much of that ordering; small and medium tables scan quickly regardless. Snowflake manages the partitioning itself and does a reasonable job with no help.

So treat cluster_by as an optimisation, not a default. The reason to add it is a known access pattern on a large table — or a measured problem, a query profile showing scans touching far more partitions than the filter should need. Adding it to every model as boilerplate buys nothing on most of them and obscures the cases where it matters.

The project pattern

You will see this constantly in reporting and published models — it is one line in the same config() block you already know:

{{
    config(
        cluster_by=['person_id'])
}}

select ...

The project conventions:

• Person-level models cluster on person_id — disease registers, vaccination status, dimensions. Most queries against them filter or join on person, so that is the column pruning must work for.
• Dashboard bases cluster on their filter columns — for example the covid/flu dashboard base uses cluster_by=['programme_type', 'campaign_id', 'practice_code', 'person_id'], matching the order users slice the dashboard.
• Event-style tables add the date — cluster_by=['person_id', 'effective_date'].

What makes a good clustering key

The objective in one sentence: cluster by the columns the next consumer will filter or join on. Not what the model groups by internally, not its primary key for its own sake — what the queries reading it will put in their where and on clauses. That means the right key can change as the same data moves down the pipeline, because the consumer changes.

The OLIDS observation pipeline is a worked example:

• Upstream, cluster by clinical code. The concept-mapped observation data is clustered by SNOMED concept (for example stg_olids_concept_map uses cluster_by=['source_concept_id']), because the next step — building int_ models — filters to specific types of observation: blood pressure readings, HbA1c results, diagnosis codes. Those code filters prune well against code-ordered data.
• Downstream, cluster by person. Once an int_ model has extracted its observations, its consumers stop filtering by code — registers and demographics join and filter by patient. So the int_ outputs switch to cluster_by=['person_id', 'clinical_effective_date'], and everything built on them joins efficiently.

Same data, two different keys — each chosen for the queries that come next. Three practical rules follow:

1. Ask who reads this model and what they filter or join on. If you cannot answer, you are not ready to choose a key.
2. Order matters: put the coarser, most-filtered column first. A handful of columns is the ceiling — more dilutes the benefit.
3. Small tables don't need it. A 50,000-row lookup fits in a few micro-partitions; there is nothing to prune. Clustering pays off on large person-level and event tables.
4. Cardinality matters at both extremes. A two-value flag barely narrows anything; a unique timestamp scatters grouping. Mid-cardinality columns — person, code, practice, date — sit in the useful range. When the natural column is too fine-grained, Snowflake's advice is to cluster on an expression that coarsens it — a timestamp cast to a date, for example — keeping the ordering while giving the partitions something to group by.

Because our tables are rebuilt by dbt rather than continuously loaded, cluster_by mostly costs nothing extra: dbt sorts the data as it builds the table, so each nightly rebuild comes out freshly clustered.

How to tell it is working

Clustering is measurable, not a matter of faith. Two checks, both in Snowflake:

• Query Profile. Run a representative filtered query and open its profile in Snowsight. The TableScan node shows partitions scanned against partitions total — a well-clustered table scans a small fraction; scanning nearly all of them means the key is not helping that query.
• system$clustering_information. Pass it a table and a candidate column list and it reports overlap depth — how jumbled the data is with respect to that key — letting you evaluate a key before committing to it.

If a model is large, clustered, and its consumers still scan most partitions, the key does not match how the table is actually queried — change the key, not the queries.