How many partitions are too many?

Too many small partitions, for example partitioning by hour instead of by day on a table with light traffic, add metadata overhead and force many small reads instead of a few large ones, slowing queries down instead of speeding them up. The practical rule is to pick the finest granularity that still yields partitions of at least a few hundred MB.

Can I change a table's partitioning scheme after it already exists?

Yes, but it usually means rewriting the entire table under the new scheme, not an instant operation on a table with billions of rows. It should be planned as a real migration, ideally decided at initial design time rather than fixed afterward.

What are partitioning and clustering?

Two techniques that cut the data a query scans, and so its time and cost, in a cloud data warehouse.

Partitioning physically splits a table into separate blocks based on the value of one column, typically a date: each partition holds only the rows for one day or one month, and a query filtering on that column reads only the relevant partitions instead of scanning the whole table (partition pruning). Clustering works one layer deeper: it physically reorders the data based on additional columns, so filters on those columns also avoid reading irrelevant blocks. The two mechanisms are not exposed the same way on every engine: BigQuery offers them as two distinct, combinable choices, Snowflake partitions automatically and leaves the user only the clustering key, Databricks with liquid clustering has merged them into one. Where they coexist they are complementary rather than alternatives: partitioning is chosen on a frequently filtered, low-cardinality column, usually the date, while clustering applies to secondary columns that narrow the search further, such as customer id or product category.

See also: Star schema · A dimensional model with a central fact table linked to dimension tables: the schema underneath most BI dashboards.

Why they matter for cost, not just speed

In cloud-native data warehouses the volume of data read turns into billing by different routes: BigQuery on demand bills the bytes each query processes directly, while Snowflake and Databricks bill compute time, which shrinks when the engine reads less data. On a table with the wrong physical layout the engine ends up reading far more than it needs even for a simple question like "yesterday's sales". With correct partitioning the same query reads only yesterday's partition, a tiny fraction of the total, and clustering adds a second filter inside that fraction.

An enterprise example

Take a transactions table with billions of rows, partitioned by date and clustered by customer. A query asking "this customer's sales in the last month" reads only the blocks of last month's partitions, and inside those blocks jumps straight to the data already ordered by that customer, instead of scanning billions of rows only to discard nearly all of them. The same query, on the same table without this layout, would carry a compute cost orders of magnitude higher for the same result. This is the kind of lever that applies on top of any logical model, whether the table follows a star schema or lives inside a data lakehouse: partitioning and clustering are about how data is physically laid out, not about how it is modeled or where it is stored.

Why it matters for decision makers

A wrong partitioning scheme, or no partitioning at all on a large table queried frequently, can multiply the cost of recurring queries feeding daily dashboards and reports by 10 to 100 times. It is a technical decision with a direct, ongoing economic impact, not a detail to leave to whichever engineer last touched the table. It needs to be made when the table is designed, not fixed after months of runaway cloud bills, and it is one of the first checks a FinOps review of a data platform should run.