What is table partitioning and clustering, and when should you use each?
Two ways to lay out a large table so a query reads only the files it needs: partitioning splits it by a column such as the date, clustering keeps similar values in the same files.
intermediate · Also called Table partitioning, Partition pruning, Liquid clustering, Clustering key, Z-ordering
Partitioning and clustering are 2 ways to lay out a large table so that a query reads only part of it. Partitioning splits the table into separate parts by the value of one column, usually a date, so a query for last Tuesday opens only Tuesday's part. Clustering keeps rows with similar values of chosen columns in the same files, so a query that filters on them can skip the files that cannot match. Both work by skipping, and skipping is what makes a query on a big table fast and, in a warehouse that bills by data scanned, cheap.
How a query skips data
A table on a lakehouse or in a warehouse is stored as many files or blocks. Each keeps a little metadata, such as the smallest and largest value of every column in it. Delta Lake writes these per file; Snowflake keeps them for each micro-partition; BigQuery keeps them for each block. When a query asks for order_date = '2026-10-13', the engine reads the metadata first and skips every file whose dates cannot include that day. BigQuery calls this pruning, and it bills only the blocks it scans.
Skipping only works when the values a query asks for sit together. If every file holds orders from every day, every file's range covers the 13th, and nothing can be skipped. Partitioning and clustering are 2 ways of putting the values together.
Partitioning: fixed parts
A partitioned table keeps each value of the partition column apart: one part per day, per month or per country. A filter on that column goes straight to its parts. Partitions also make some jobs simple: load one day without touching the others, delete a whole month at once, or let BigQuery drop partitions older than a set age. On a partitioned BigQuery table, a dry run can tell you the cost of a query before it runs.
The cost is that the parts are fixed. Partition by a column with many values, such as a customer id or a timestamp, and you get thousands of tiny parts, each with its own small files and metadata, and queries slow down. Databricks advises at least 1 GB in each partition and no partitioning at all under 1 TB; Google suggests clustering instead when partitions would hold under about 10 GB. Choose badly and the fix is rewriting the whole table.
Clustering: values kept together
Clustering sorts rows by a few columns as they are written or later, so similar values share files, with no fixed boundaries. It works for columns with many values, such as a customer id, and for filters on more than one column. BigQuery takes up to 4 clustering columns and reclusters new data in the background; the first column matters most. Snowflake splits every table into micro-partitions of 50 to 500 MB by itself, and a clustering key keeps them in order. On Databricks, the docs recommend liquid clustering (CLUSTER BY) over both partitioning and the older Z-ordering; its keys change with one ALTER TABLE, and CLUSTER BY AUTO picks them from the queries that run.
Which to use
Start with neither and let the defaults work: Databricks clusters unpartitioned Delta tables by the time data arrives, and Snowflake makes micro-partitions on its own. When queries filter on a column and still read too much, cluster by it. Partition when you need what partitions alone give, such as deleting old days or loading one day at a time, and only by a column with few values and plenty of data in each. A table read by a gold layerMedallion ArchitectureOrganising a lakehouse in 3 layers of rising quality: bronze for raw data, silver for cleaned and joined data, and gold for tables shaped for reports and AI. dashboard by date and region is a typical case for clustering on both.
Why it matters for AI
An assistant that writes SQL against a warehouseData WarehouseA database built for analysis: cleaned, structured data from many systems, kept in one place and shaped for fast reports and dashboards. writes the filters a person would, and the layout decides whether each of those queries reads a day of data or 3 years of it. The same goes for the pipelines that build features and retrieval tables. On BigQuery's on-demand price, a query that cannot skip is billed for every byte it reads.
Sources