Definition
- Access-pattern alignment – the partition column must match how queries actually filter the data, because partitioning only pays off through pruning.
- Low cardinality – the column should have few distinct values so each partition directory holds a healthy amount of data.
- High-cardinality failure – partitioning on a column like account_id creates millions of directories, each nearly empty, causing metadata bloat, the small-file problem, and potentially driver OOM during planning.
- Fix – for high-cardinality keys, use bucketing (fixed number of files) or partition on a derived low-cardinality column.
At the National Bank of India, partitioning is like deciding how to label filing drawers. Labelling by branch – a handful of values – gives a few full, useful drawers that auditors actually ask for. But labelling by individual account number gives you millions of drawers each holding one slip, so the records room is overwhelmed with empty folders, the index becomes a book nobody can carry, and the clerk collapses trying to even list them all.
Understanding the Concepts
What are the requirements for effective partitioning?
Effective write-time partitioning requires two things. First, the partition column must match how queries actually filter the data. Second, the column must have low cardinality so each directory holds a healthy amount of data.
from pyspark.sql import SparkSession
import pyspark.sql.functions as F
spark = SparkSession.builder.appName("NBI-Partition-Design").getOrCreate()
# GOOD: partition by branch (low cardinality, frequently filtered)
# Queries like: WHERE branch = 'Mumbai' get pruning
txn = spark.read.parquet("hdfs:///bank/transactions/")
txn.write.partitionBy("branch").parquet("/bank/by-branch/")
# BAD: partition by a column nobody filters on
# Queries never filter by status → no pruning benefit
txn.write.partitionBy("status").parquet("/bank/by-status/")
Why must the partition column align with query access patterns?
Partitioning only helps when queries filter on the partition column, because that is what enables partition pruning. If you partition by branch but every query filters by date, the partitioning does nothing for pruning and you have paid the organizational cost for no benefit.
# Partitioned by branch
df = spark.read.parquet("/bank/by-branch/")
# This query gets pruning: filter matches partition column
df.filter(F.col("branch") == "Mumbai").explain()
# Shows: PartitionFilters: [branch = Mumbai]
# This query does NOT get pruning: filter on non-partition column
df.filter(F.col("amount") > 100000).explain()
# Shows: PushedFilters: [amount > 100000] (no PartitionFilters)
== Physical Plan (branch filter) ==
FileScan parquet PartitionFilters: [branch = Mumbai]
== Physical Plan (amount filter) ==
FileScan parquet PushedFilters: [IsNotNull(amount), GreaterThan(amount,100000)]
Why must the column be low cardinality?
Low cardinality means few distinct values relative to the data size, so each directory holds a healthy amount of data. Ideally each partition is 128 MB to 1 GB, which is the range columnar formats read efficiently. Good choices include branch (~50), country (~200), region (~10), product type (~20), and date at month grain (~12 per year).
# Check cardinality before choosing partition column
for col in ["branch", "product_type", "account_id", "txn_date"]:
count = txn.select(col).distinct().count()
print(f" {col}: {count} distinct values")
branch: 48 distinct values
product_type: 12 distinct values
account_id: 8500000 distinct values
txn_date: 365 distinct values
What goes wrong with high-cardinality partition columns?
When you partition on a high-cardinality column, several things break at once: directory explosion, the small-file problem, metadata bloat, and driver OOM.
# BAD: partitioning by account_id (8.5 million distinct values)
# This creates 8.5 million directories, each with a tiny file
# txn.write.partitionBy("account_id").parquet("/bank/bad-idea/")
# Problem 1: Directory explosion
# /bank/bad-idea/account_id=SAV0000001/part-0.parquet (tiny)
# /bank/bad-idea/account_id=SAV0000002/part-0.parquet (tiny)
# ... 8,500,000 more directories
# Problem 2: Small-file problem
# Each directory holds ~1 KB instead of ~500 MB
# Columnar read optimizations are defeated
# Problem 3: Metadata bloat
# Metastore must track 8.5M partition entries
# Every query's planning phase slows to a crawl
# Problem 4: Driver OOM
# Spark holds partition metadata on the driver
# 8.5M partitions can exhaust the driver heap during planning
How do you fix the high-cardinality problem?
For high-cardinality keys, you do not partition. Instead, use bucketing (fixed number of files via hashing) or partition on a derived low-cardinality column.
# Fix 1: Use bucketing for high-cardinality keys
txn.write.bucketBy(256, "account_id").sortBy("txn_date") \
.saveAsTable("bank.transactions_bucketed")
# Always 256 files, regardless of cardinality
# Fix 2: Partition on a derived low-cardinality column
txn_with_month = txn.withColumn("txn_month", F.date_format("txn_date", "yyyy-MM"))
txn_with_month.write.partitionBy("txn_month").parquet("/bank/by-month/")
# ~12 partitions per year, healthy size each
# Fix 3: Partition by low-cardinality + bucket by high-cardinality
txn.write \
.partitionBy("branch") \
.bucketBy(64, "account_id") \
.sortBy("txn_date") \
.saveAsTable("bank.transactions_optimized")
What is the ideal partition count and size?
The general guideline is to keep the total number of partitions in the thousands at most, not millions. Each partition directory should hold roughly 128 MB to 1 GB.
# Estimating partition sizes
total_data_gb = 500 # 500 GB total
# branch (48 values): 500 GB / 48 ≈ 10.4 GB per partition (too large, add date)
# branch + month (48 * 12 = 576): 500 GB / 576 ≈ 870 MB per partition (good)
# account_id (8.5M values): 500 GB / 8.5M ≈ 60 KB per partition (terrible)
print("branch only:", f"{total_data_gb / 48:.1f} GB/partition")
print("branch + month:", f"{total_data_gb / 576 * 1024:.0f} MB/partition")
print("account_id:", f"{total_data_gb / 8500000 * 1024 * 1024:.0f} KB/partition")
branch only: 10.4 GB/partition
branch + month: 889 MB/partition
account_id: 62 KB/partition
What are common mistakes?
The most common mistakes are partitioning on a high-cardinality column and partitioning on a column nobody filters on.
# WRONG: partition by account_id (millions of tiny directories)
# txn.write.partitionBy("account_id").parquet("/bank/bad/")
# WRONG: partition by a column nobody queries
# txn.write.partitionBy("internal_batch_id").parquet("/bank/useless/")
# WRONG: too many partition levels
# txn.write.partitionBy("country", "branch", "product", "txn_date").parquet(...)
# 200 * 48 * 12 * 365 = 42M combinations → directory explosion
# CORRECT: 1-2 low-cardinality columns matching query patterns
txn.write.partitionBy("branch", "txn_month").parquet("/bank/good/")
How do you verify partition effectiveness?
After writing, check that the partition count is reasonable and each partition holds healthy data. At read time, verify that queries show PartitionFilters in the physical plan.
# Verify partition count
import os
partitions = [d for d in os.listdir("/bank/by-branch/") if "=" in d]
print(f"Partition count: {len(partitions)}")
# Verify pruning in query plan
df = spark.read.parquet("/bank/by-branch/")
df.filter(F.col("branch") == "Mumbai").explain()
# Should show PartitionFilters: [branch = Mumbai]
Partition count: 48
What should a senior engineer know?
A senior engineer designs partitioning based on query access patterns and cardinality analysis before writing data. They combine partitioning (low cardinality, pruning) with bucketing (high cardinality, join optimization) and keep the total directory count manageable.
# Senior decision matrix:
# Column Cardinality Queried? Strategy
# branch ~50 Yes partitionBy
# country ~200 Yes partitionBy
# txn_month ~12/year Yes partitionBy
# account_id ~10M Yes bucketBy
# timestamp ~billions Rarely DO NOT partition
# status ~5 Sometimes partitionBy (if selective)
# Production pattern: partition + bucket + sort
txn.write \
.partitionBy("branch") \
.bucketBy(128, "account_id") \
.sortBy("txn_date") \
.option("maxRecordsPerFile", 1000000) \
.saveAsTable("bank.transactions_production")
Visual Overview
Partition Column Cardinality: LOW CARDINALITY (branch ~50) – GOOD /branch=Mumbai/ ──→ ~10 GB (healthy) /branch=Delhi/ ──→ ~8 GB (healthy) /branch=Chennai/ ──→ ~12 GB (healthy) ... (~50 directories total) HIGH CARDINALITY (account_id ~10M) – BAD /account_id=SAV0000001/ ──→ 60 KB (tiny!) /account_id=SAV0000002/ ──→ 45 KB (tiny!) /account_id=SAV0000003/ ──→ 72 KB (tiny!) ... (8,500,000 directories!) Problems: ├── Directory explosion (millions of dirs) ├── Small-file problem (columnar reads defeated) ├── Metadata bloat (catalog overwhelmed) └── Driver OOM (listing millions of dirs) FIX: Use bucketing for high-cardinality keys bucketBy(256, "account_id") /bucket-000.parquet ──→ ~2 GB (healthy) /bucket-001.parquet ──→ ~2 GB (healthy) ... (always 256 files, regardless of cardinality)
Key Takeaway
Effective partitioning rests on two requirements. First, the partition column must align with how queries actually filter the data, because partitioning only pays off through pruning, and pruning only happens when queries filter on the partition column. If I partition by branch but everyone queries by date, I have paid the cost and gained nothing. Second, the column must be low cardinality so each directory holds a healthy amount of data, ideally a few hundred megabytes up to a gigabyte, which is the range columnar formats read efficiently.
The failure mode is partitioning on a high-cardinality column like account_id or exact timestamp. Several problems hit at once. Directory explosion, because ten million accounts means ten million directories. The small-file problem, because each directory holds almost nothing and tiny files defeat columnar reads. Metadata bloat, because the catalog must track millions of partitions, slowing every query's planning. And worst of all, driver OOM, because Spark holds partition metadata on the driver and millions of partitions exhaust its heap during planning.
I saw exactly this at the National Bank of India when someone partitioned the transactions table by account_id and the next day's job died on the driver with an out-of-memory error. We fixed it by partitioning on branch and month, which are low cardinality and match how reports filter, giving a few thousand healthy directories with real pruning benefits. For account-level lookups we used bucketing by account_id instead.
The way I state it in interviews is: partition on low-cardinality columns you actually filter on, keep the partition count in the thousands rather than the millions, and for high-cardinality keys use bucketing or a derived low-cardinality column.
Remember one sentence: partition by low-cardinality columns you filter on, and use bucketing for high-cardinality keys to avoid directory explosion.
---