Partition Skew

Example recent partitions 2025-10-11, 2025-10-12 are 6 times the size of history partition 2001-10-12.

Day Partition   | OrderID | Customer ID
----------------+---------+-------------
2001-10-12      | 01      | 
----------------+---------+-------------
2001-10-13      | 02      | 
2001-10-13      | 03      | 
----------------+---------+-------------
2001-10-14      | 04      | 
2001-10-14      | 05      | 
----------------+---------+-------------
...             |         | 
----------------+---------+-------------
2025-10-10      | 06      | 
2025-10-10      | 07      | 
2025-10-10      | 08      | 
----------------+---------+-------------
2025-10-11      | 09      | 
2025-10-11      | 10      | 
2025-10-11      | 11      | 
2025-10-11      | 12      | 
2025-10-11      | 13      | 
2025-10-11      | 14      | 
----------------+---------+-------------
2025-10-12      | 15      | 
2025-10-12      | 16      | 
2025-10-12      | 17      | 
2025-10-12      | 18      | 
2025-10-12      | 19      | 
2025-10-12      | 20      | 

Partition Salting for BigQuery/Databricks

1. Use if STRING partition is not supported, Day is DATE datatype.
2. Add Salt column
   • Salt = OrderID%3 IF Day >= '2025-10-10'
   • PARTITION BY Day CLUSTER/ZORDER BY Salt
3. Recent 2025 partitions have 3 Salt values. Rows scanned drop from 6 to 2.
4. Queries still filter on Day to match all data transparently.

Day Partition   | OrderID | Customer ID
----------------+---------+-------------
2001-10-12      | 01      | 
----------------+---------+-------------
2001-10-13      | 02      | 
2001-10-13      | 03      | 
----------------+---------+-------------
2001-10-14      | 04      | 
2001-10-14      | 05      | 
----------------+---------+-------------
...             |         | 
----------------+---------+-------------


2025-10-10      | 06      | 
2025-10-10      | 07      | 
2025-10-10      | 08      | 
----------------+---------+-------------


2025-10-11      | 09      | 
2025-10-11      | 10      | 
2025-10-11      | 11      | 
2025-10-11      | 12      | 
2025-10-11      | 13      | 
2025-10-11      | 14      | 
----------------+---------+-------------


2025-10-12      | 15      | 
2025-10-12      | 16      | 
2025-10-12      | 17      | 
2025-10-12      | 18      | 
2025-10-12      | 19      | 
2025-10-12      | 20      | 

Day Partition | Salt | OrderID | CustomerID
--------------+------+---------+-------------
2001-10-12    | NULL | 01      | 
--------------+------+---------+-------------
2001-10-13    | NULL | 02      | 
2001-10-13    | NULL | 03      | 
--------------+------+---------+-------------
2001-10-14    | NULL | 04      | 
2001-10-14    | NULL | 05      | 
--------------+------+---------+-------------
...           |      | 
--------------+------+---------+-------------
2025-10-10    | 0    | 06      | 
··············+······+·········+·············
2025-10-10    | 1    | 07      | 
··············+······+·········+·············
2025-10-10    | 2    | 08      | 
--------------+------+---------+-------------
2025-10-11    | 0    | 09      | 
2025-10-11    | 0    | 12      | 
··············+······+·········+·············
2025-10-11    | 1    | 10      | 
2025-10-11    | 1    | 13      | 
··············+······+·········+·············
2025-10-11    | 2    | 11      | 
2025-10-11    | 2    | 14      | 
--------------+------+---------+-------------
2025-10-12    | 0    | 15      | 
2025-10-12    | 0    | 18      | 
··············+······+·········+·············
2025-10-12    | 1    | 16      | 
2025-10-12    | 1    | 19      | 
··············+······+·········+·············
2025-10-12    | 2    | 17      | 
2025-10-12    | 2    | 20      | 

Databricks-only Partition Salting

1. String partition is supported, Day is STRING datatype.
2. Recent 2025 have 3 partition values for each day. Partition size drops from 6 to 2.
3. Queries use WHERE day LIKE '2025-10-12%' to match all partitions transparently.

Day Partition   | OrderID | Customer ID
----------------+---------+-------------
2001-10-12      | 01      | 
----------------+---------+-------------
2001-10-13      | 02      | 
2001-10-13      | 03      | 
----------------+---------+-------------
2001-10-14      | 04      | 
2001-10-14      | 05      | 
----------------+---------+-------------
...             |         | 
2025-10-10_0    | 06      | 
----------------+---------+-------------
2025-10-10_1    | 07      | 
----------------+---------+-------------
2025-10-10_2    | 08      | 
----------------+---------+-------------
2025-10-11_0    | 09      | 
2025-10-11_0    | 12      | 
----------------+---------+-------------
2025-10-11_1    | 10      | 
2025-10-11_1    | 13      | 
----------------+---------+-------------
2025-10-11_2    | 11      | 
2025-10-11_2    | 14      | 
----------------+---------+-------------
2025-10-12_0    | 15      | 
2025-10-12_0    | 18      | 
----------------+---------+-------------
2025-10-12_1    | 16      | 
2025-10-12_1    | 19      | 
----------------+---------+-------------
2025-10-12_2    | 17      | 
2025-10-12_2    | 20      | 

Last modified on 2026-03-01