How do partitioning and clustering differ in a cloud warehouse, and how do you choose them?

Partitioning splits a table into physically separate chunks, usually by date, so a filtered query prunes whole partitions and scans less data. Clustering sorts data within storage by chosen columns so the engine can skip blocks on those predicates. Partition on the column that appears in almost every WHERE clause, typically event date; cluster on the high cardinality columns people filter or join on next.

Partitioning is coarse and physical: split by day, and a query filtered to last week reads seven partitions instead of the whole table. Clustering is fine grained ordering inside the data so the engine can skip blocks based on the min and max values it stores per block. My rule is to partition on the thing that is in nearly every query, which is almost always an event or ingestion date, then cluster on the next one or two predicates, like customer id or country. The failure I have cleaned up more than once is over partitioning. Someone partitioned by customer id, ended up with forty thousand partitions of a few kilobytes each, and query planning alone got slower than the scan it was trying to avoid. The other habit I keep is verifying rather than trusting. On BigQuery I check bytes processed before and after with a dry run, because if the estimate does not drop, the pruning is not happening, usually because the filter wraps the partition column in a function.