Partitioning vs Bucketing vs Liquid Clustering, Explained

How files are laid out decides how much data a query must read. A query for one day of one country's orders can read a few megabytes or scan the whole table, depending on how the table is partitioned, clustered and compacted. In a design interview, saying how you'll lay out a big table is what turns "we'll store it in S3" into a credible answer, and it drives cost directly.

Partitioning

Partitioning splits a table into folders by the value of one or more columns. A filter on that column lets the engine skip every other folder (partition pruning).

s3://lake/silver/events/event_date=2026-03-14/part-0001.parquet
s3://lake/silver/events/event_date=2026-03-14/part-0002.parquet
s3://lake/silver/events/event_date=2026-03-15/part-0001.parquet
-- WHERE event_date = '2026-03-15' reads one folder, not the whole table

• Default: partition by event date. Time-series and event data is almost always filtered by date, so this one choice prunes most reads.
• Add a second key only if most queries filter on it, e.g. if 80% of queries filter by country or region: event_date / region.
• Avoid high-cardinality partition keys (user_id, order_id): millions of tiny folders and files make everything slower. Partitions should hold at least hundreds of MB to a few GB each.
• The catch is rigidity. Partition columns are fixed up front. If queries shift from filtering by date to filtering by region, the partitioning stops helping, and changing it means rewriting the whole dataset.

Bucketing

Bucketing hashes a column (often a join key such as customer_id) into a fixed number of buckets, so rows with the same key always land in the same file. Two tables bucketed the same way on the join key can be joined without a shuffle, and equality filters touch one bucket. It suits high-cardinality join keys that are bad partition columns, but the bucket count is fixed and both tables must agree on it. See Spark architecture for shuffles and join strategies.

Clustering and data skipping

Columnar files store min/max statistics per file and row group. If rows are sorted or clustered so similar values sit together, those statistics let the engine skip most files for a filter (data skipping), even on columns you didn't partition by. See columnar file formats.

• Sorting / Z-ordering: rewrite files sorted by one column, or Z-ordered across several (e.g. OPTIMIZE events ZORDER BY (region, product_id)) so multi-column filters still skip files.
• Liquid clustering (Delta Lake on Databricks; Iceberg offers similar sort orders and hidden partitioning): instead of fixed physical partitions, you declare clustering columns and the engine incrementally reorganizes data around them. You can cluster on event_date and region together, and if query patterns change you just change the clustering columns, with no full rewrite. It removes the up-front rigidity of partitioning.

-- Delta Lake: liquid clustering instead of fixed partitions
CREATE TABLE silver.events (event_id STRING, user_id STRING, region STRING, event_type STRING, event_ts TIMESTAMP, event_date DATE)
CLUSTER BY (event_date, region);

ALTER TABLE silver.events CLUSTER BY (event_date, event_type);   -- query patterns changed: no rewrite needed
OPTIMIZE silver.events;                                          -- incrementally clusters new data

File sizes and compaction

Streaming and frequent small batches create the small file problem: thousands of KB-sized files whose open/close overhead dominates query time. Aim for files of roughly 128 MB–1 GB, and run compaction regularly (OPTIMIZE in Delta, rewrite_data_files in Iceberg) to merge small files. Write-side settings (auto-optimize, target file size, fewer output partitions in Spark) reduce how many small files are created in the first place.

Choosing a strategy

Layout is also a cost lever: on pay-per-scan engines (Athena, BigQuery), pruning and skipping translate directly into lower bills.