Partitioning and Bucketing Strategies

• Partitioning splits a dataset into separate physical folders based on a column’s value (like date or region), so a query can skip entire folders it doesn’t need.
• Bucketing goes a level deeper as it hashes rows within each partition into a fixed number of files based on another column, so a query can jump straight to the one file that has what it’s looking for.

Used together, they’re one of the single biggest levers for cutting both query time and cost on large datasets.

Why It Matters

Query engines that scan data in cloud storage — Amazon Athena, Spark, BigQuery these largely charge based on how much data they have to read, not how much data actually matches your query. A poorly laid-out dataset forces the engine to scan everything just to find a handful of relevant rows.

Partitioning and bucketing are the two primary techniques for avoiding that waste. Get the layout right, and a query that would have scanned terabytes might only touch a few megabytes. Get it wrong or skip it entirely and every query on a growing dataset gets slower and more expensive over time, often without anyone realizing why until the monthly cloud bill shows up.

If you’re a tech lead / an architect working with large datasets in S3, a data lake, or a warehouse, this is one of the highest leverage, lowest-effort optimizations available and one of the most commonly skipped by teams still new to the space.

Partitioning

Partitioning physically splits a dataset into separate folders or the warehouse equivalent based on the value of one or more columns — most commonly a date.

A partitioned layout in S3 looks like this:

s3://my-bucket/orders/
  year=2026/month=09/day=24/  <- one file (or more) here
  year=2026/month=09/day=25/
  year=2026/month=09/day=26/

When a query filters WHERE year=2026 AND month=9 AND day=26, the engine only reads the one matching folder and this is called partition pruning, and it’s the entire reason partitioning exists.

Choosing a partition key — the core tradeoff:

• Too coarse : e.g., partitioning a huge table by year only. each partition is still enormous, and pruning barely helps.
• Too fine : e.g., partitioning by minute, or by a very high-cardinality column. you end up with an explosion of tiny partitions and tiny files, which creates its own performance problem like metadata overhead, slow listing operations which are often called the “small files problem.”
• Look for the right match: pick a column that matches how the data is actually queried. date is extremely common because most analytical queries filter by a time range and that produces a reasonable number of partitions, each with a healthy amount of data.

AWS Athena Example (ref)

• When a dataset is partitioned, you can create a partitioned table in Athena.
• A partitioned table is a table that has partition keys. 
• When you use a CREATE TABLE statement, you specify the partition keys in the PARTITIONED BY (column_name data_type) clause.

CREATE EXTERNAL TABLE [IF NOT EXISTS]
 [db_name.]table_name [(col_name data_type [COMMENT col_comment] [, ...] )]
 [COMMENT table_comment]
 [PARTITIONED BY (col_name data_type [COMMENT col_comment], ...)]
 [CLUSTERED BY (col_name, col_name, ...) INTO num_buckets BUCKETS]
 [ROW FORMAT row_format]
 [STORED AS file_format] 
 [WITH SERDEPROPERTIES (...)]
 [LOCATION 's3://amzn-s3-demo-bucket/[folder]/']
 [TBLPROPERTIES ( ['has_encrypted_data'='true | false',] ['encryption_option'='SSE_S3 | SSE_KMS | CSE_KMS',] ['kms_key'='aws_kms_key_arn',] ['classification'='aws_glue_classification',] property_name=property_value [, ...] ) ]

-- Partitioned AND bucketed table, using Athena's documented CLUSTERED BY syntax
CREATE EXTERNAL TABLE orders (
    order_date     STRING,
    order_id       STRING,
    customer_id    STRING,
    amount         DOUBLE
)
PARTITIONED BY (order_date STRING)
STORED AS PARQUET
LOCATION 's3://my-bucket/orders/'
TBLPROPERTIES (
  'bucketing_format' = 'spark'
);

Bucketing

• Bucketing also called as clustering which goes a level deeper.
• Within each partition, rows are hashed by the value of another column into a fixed number of files “buckets.”
• Every row with the same value for that column always lands in the same bucket, deterministically.
• As Amazon Athena’s own documentation puts it: bucketing is a way to organize records into categories, where records are distributed as evenly as possible so each bucket holds roughly the same amount of data and in practice, each bucket is simply a file, with a hash function determining which file a given record belongs in.
• If a table is partitioned by date and bucketed by customer_id, a query filtering on both a date range and a specific customer only needs to read one file per partition rather than scanning every file in that day’s partition.

AWS Athena Example

-- Partitioned AND bucketed table, using Athena's documented CLUSTERED BY syntax
CREATE EXTERNAL TABLE orders (
    order_date     STRING,
    order_id       STRING,
    customer_id    STRING,
    amount         DOUBLE
)
PARTITIONED BY (order_date STRING)
CLUSTERED BY (customer_id) INTO 8 BUCKETS
STORED AS PARQUET
LOCATION 's3://my-bucket/orders/'
TBLPROPERTIES (
  'bucketing_format' = 'spark'
);

A query that benefits from both

SELECT SUM(amount)
FROM orders
WHERE order_date BETWEEN '2026-09-20' AND '2026-09-26'
  AND customer_id = '12345';
-- If this table is partitioned by order_date and bucketed by customer_id,
-- Athena calculates the exact bucket for this prders and reads
-- at most one file per matching partition — not the whole partition.

• When you use CREATE TABLE AS (CTAS), the partitions that are created on Amazon S3 are automatically added to the table.
• Athena’s CTAS (Create Table As Select) statement. In plain terms: it runs a query, and instead of just returning the results to you, it saves those results as a brand-new physical table — and the WITH (…) clause is where you control how that new table gets stored.

CREATE TABLE orders_optimized
WITH (
    format             = 'PARQUET',
    external_location  = 's3://my-bucket/orders_optimized/',
    partitioned_by      = ARRAY['order_date'],
    bucketed_by         = ARRAY['customer_id'],
    bucket_count        = 8
) AS
SELECT order_id, customer_id, amount, order_date
FROM orders_raw;

• Without WITH (…), a bare CTAS still works, but you get Athena’s defaults and whatever format your source table already uses, no partitioning, no bucketing, output dropped wherever your query results location is configured.

CREATE TABLE orders_copy AS SELECT * FROM orders_raw

Related Tools

• Amazon Athena – supports both Hive-style and Spark-style bucketing
• Apache Spark – df.write.partitionBy(…) and df.write.bucketBy(n, “column”) for creating partitioned/bucketed datasets
• AWS Glue Data Catalog – stores partition metadata that Athena and other query engines rely on
• Delta Lake / Apache Iceberg / Apache Hudi — modern table formats that handle partitioning more flexibly than raw Hive-style folders

Where This Fits in the Bigger Picture

Partitioning and bucketing decisions are made at the storage stage, but they’re really a bet on how the data will be queried later and which is why they connect directly to Data Architecture decisions as much as engineering ones:

The practical rule before choosing a partition key, ask how the table will actually be queried and not what feels like the “natural” way to organize the data.

Real-World Scenarios