Browse cheat sheets · Data Engineering

Technology: Data EngineeringCategory: Storage & PerformanceDifficulty: IntermediateUpdated

Partitioning Strategies

Choose and validate data partitioning strategies that improve pruning, parallelism, maintenance, and cost without creating skew, tiny files, or high-cardinality layouts.

On this page
  1. Problem
  2. Quick answer
  3. Code / commands
  4. 1. Partition by a business date when processing is date-bounded
  5. 2. Partition on the column used in filtering
  6. 3. Avoid partitioning by unique IDs
  7. 4. Avoid partitioning by raw timestamp
  8. 5. Derive a partition date explicitly
  9. 6. Partition by multiple columns only when justified
  10. 7. Think about partition count before writing
  11. 8. Check partition cardinality in PySpark
  12. 9. Check multi-column partition distribution
  13. 10. Measure skew before choosing the key
  14. 11. Do not confuse table partitioning with Spark partitions
  15. 12. Inspect Spark execution partition count
  16. 13. Inspect rows per Spark partition
  17. 14. Repartition by a key when redistribution is actually needed
  18. 15. Repartition without a key to spread data more evenly
  19. 16. Use coalesce when reducing partitions without a full reshuffle
  20. 17. Avoid repartition(1) in production pipelines
  21. 18. Do not use coalesce(1) as a universal “single CSV” solution
  22. 19. Date partitioning fits incremental loads naturally
  23. 20. Partitioning can simplify retention
  24. 21. Do not assume partition deletion is always a metadata-only operation
  25. 22. Use partition filters explicitly
  26. 23. Avoid wrapping partition columns in unnecessary functions
  27. 24. Check partition pruning with explain
  28. 25. Check for filters on the wrong data type
  29. 26. Partitioning does not replace file statistics
  30. 27. Partitioning and clustering solve different problems
  31. 28. Use low/medium-cardinality partition columns
  32. 29. Tenant partitioning can be useful but risky
  33. 30. Date + tenant can explode partition count
  34. 31. Month partitioning may beat day partitioning for small datasets
  35. 32. Hour partitioning may be justified for very high volume
  36. 33. Minute-level partitioning is rarely appropriate
  37. 34. Partition by immutable values when possible
  38. 35. Do not partition by mutable status
  39. 36. Ingestion date and event date serve different purposes
  40. 37. Late-arriving data complicates event-date partitioning
  41. 38. Ingestion-date partitioning simplifies append patterns
  42. 39. Consider dual date columns without dual partitioning
  43. 40. Avoid partitioning by every common filter
  44. 41. Small files can destroy partition benefits
  45. 42. Check file count by partition
  46. 43. Compact small files when the platform supports it
  47. 44. Avoid rewriting all partitions for one day’s change
  48. 45. Partition overwrite must be controlled
  49. 46. Backfills should target explicit partition ranges
  50. 47. Validate row counts by partition after backfill
  51. 48. Reconcile business measures by partition
  52. 49. Partition pruning only helps when the query uses the partition column
  53. 50. Encourage bounded queries
  54. 51. Avoid accidental full scans
  55. 52. Partitioning does not replace column pruning
  56. 53. Partitioning does not replace filtering early
  57. 54. Partitioning does not fix a bad join
  58. 55. Partitioning does not fix source skew automatically
  59. 56. Salt only when skew is real and worth the complexity
  60. 57. Do not salt storage partitions casually
  61. 58. Hash partitioning can distribute workload evenly
  62. 59. Range partitioning helps ordered data
  63. 60. List partitioning fits known categories
  64. 61. Partition strategy should include growth expectations
  65. 62. Account for empty/sparse partitions
  66. 63. Keep partition columns visible in table metadata
  67. 64. Do not make consumers depend on physical directory paths
  68. 65. Partitioning and retention should align where possible
  69. 66. Partitioning can improve failure isolation
  70. 67. Record partition scope in execution logs
  71. 68. Detect missing partitions
  72. 69. Detect duplicate partition loads
  73. 70. Treat partition columns as part of the data contract
  74. 71. Repartitioning an existing large table is a migration
  75. 72. Validate before and after repartitioning
  76. 73. Do not assume more partitions mean more parallelism
  77. 74. Do not assume fewer partitions are always better
  78. 75. Match partitioning to maintenance operations
  79. 76. Match partitioning to security only when the platform enforces it properly
  80. 77. Partitioning may interact with concurrency
  81. 78. Avoid concurrent full-table overwrites
  82. 79. Partition by source system only when it helps operations
  83. 80. Use a repeatable decision checklist
  84. What the result means
  85. What to look for
  86. Queries still scan most data
  87. Too many tiny files
  88. One partition is much larger than others
  89. Backfills are expensive
  90. Daily partitions are nearly empty
  91. Queries frequently filter a high-cardinality column
  92. Production notes
  93. Partitioning is a physical design choice
  94. Partition pruning must be verified
  95. File size and partitioning must be considered together
  96. Partition layout should evolve slowly
  97. Avoid platform-independent magic numbers
  98. Common mistakes
  99. 1. Partitioning by every filter column
  100. 2. Partitioning by unique IDs
  101. 3. Partitioning by raw timestamp
  102. 4. Using repartition(1) to create a single output file
  103. 5. Confusing Spark execution partitions with table partitions
  104. 6. Assuming partitioning fixes skew
  105. 7. Ignoring small files
  106. 8. Rebuilding the full table for partition-scoped changes
  107. 9. Choosing partition columns without query evidence
  108. 10. Treating physical partitions as a security boundary
  109. Next step
  110. Related deep dives

Problem

Partitioning can make large analytical datasets faster and cheaper to process, but poor partitioning often creates the opposite result.

Common problems include:

  • queries scanning too much data
  • millions of tiny files
  • one hot partition carrying most of the workload
  • partition columns that are too granular
  • daily pipelines rewriting huge portions of a table
  • filters that do not align with partition boundaries
  • high-cardinality layouts that are expensive to list and maintain
  • partitions chosen from habit instead of workload evidence

The goal is to partition around how the data is filtered, written, maintained, and scaled, not simply around whatever columns are available.

Quick answer

Use this decision model:

Queries commonly filter by a stable low/medium-cardinality column
→ consider partitioning on it

Data is naturally processed/rebuilt by date
→ date partitioning is often useful

Very high-cardinality identifier
→ usually do not partition by it

One partition receives most rows
→ repartitioning by that key will likely create skew

Dataset is small enough to scan efficiently
→ do not partition just because the platform supports it

A good first test is:

1. What filters eliminate the most data?
2. What boundaries are used for incremental loads/backfills?
3. How many partitions will exist?
4. How many files will each partition contain?
5. Are partitions balanced enough for parallel processing?

Code / commands

1. Partition by a business date when processing is date-bounded

Example layout:

/orders/order_date=2026-10-01/
/orders/order_date=2026-10-02/
/orders/order_date=2026-10-03/

This is useful when common workloads are:

daily ingestion
daily reconciliation
date-range reporting
date-scoped backfills

A query such as:

WHERE order_date >= '2026-10-01'
  AND order_date <  '2026-10-08'

can allow the engine to skip unrelated date partitions.


2. Partition on the column used in filtering

Good alignment:

partition column
→ order_date

common filter
→ WHERE order_date = '2026-10-04'

Poor alignment:

partition column
→ country

common filter
→ WHERE created_at >= ...

Partitioning only helps pruning when the query can eliminate partitions.


3. Avoid partitioning by unique IDs

Usually poor choices:

order_id
transaction_id
session_id
customer_email
request_id

Why:

high cardinality
→ huge partition count
→ tiny partitions/files
→ metadata overhead
→ expensive directory/table planning

Use those fields as data columns, indexes, clustering keys, or ordering keys where the platform supports such features.


4. Avoid partitioning by raw timestamp

Bad:

event_timestamp=2026-10-04T14:37:21.123456Z

This can create a partition per timestamp value.

Better:

event_date=2026-10-04

or, only when volume and access patterns justify it:

event_hour=2026-10-04-14

Choose the coarsest useful boundary.


5. Derive a partition date explicitly

PySpark:

from pyspark.sql import functions as F

df = df.withColumn(
    "event_date",
    F.to_date("event_time"),
)

Then write:

(
    df.write
    .format("delta")
    .mode("append")
    .partitionBy("event_date")
    .saveAsTable("bronze.events")
)

Use this only when date partitioning matches the read/write pattern.


6. Partition by multiple columns only when justified

Example:

event_date
+
region

can be useful if:

queries nearly always filter both
and
each combination has enough data

But this can multiply partition count quickly.

Example:

365 dates
×
50 regions
=
18,250 partitions/year

The arithmetic is simple; whether that is reasonable depends on volume and platform behavior.


7. Think about partition count before writing

Estimate:

number of distinct partition-value combinations

Example:

date
→ 365/year

date + country
→ 365 × number_of_countries

date + customer_id
→ potentially millions

Avoid creating a directory/table layout that scales with an uncontrolled high-cardinality dimension.


8. Check partition cardinality in PySpark

from pyspark.sql import functions as F

df.groupBy("event_date").agg(
    F.count("*").alias("rows")
).orderBy(
    F.desc("rows")
).show(100, truncate=False)

This helps reveal uneven distribution.


9. Check multi-column partition distribution

(
    df.groupBy(
        "event_date",
        "region",
    )
    .agg(
        F.count("*").alias("rows")
    )
    .orderBy(
        F.desc("rows")
    )
    .show(100, truncate=False)
)

Look for combinations with drastically different row counts.


10. Measure skew before choosing the key

Example:

stats = (
    df.groupBy("merchant_id")
    .count()
    .orderBy(F.desc("count"))
)

stats.show(50, truncate=False)

If a few merchants dominate the data, partitioning by merchant_id may create hot partitions.


11. Do not confuse table partitioning with Spark partitions

These are different concepts.

Table/storage partitioning:

event_date=2026-10-04/

Spark execution partitioning:

how rows are distributed across tasks

A table can be partitioned by date while Spark uses many execution partitions inside one date.


12. Inspect Spark execution partition count

df.rdd.getNumPartitions()

This tells you execution partition count for the current DataFrame.

It does not tell you the table’s storage partition layout.


13. Inspect rows per Spark partition

from pyspark.sql import functions as F

partition_rows = (
    df.withColumn(
        "_spark_partition_id",
        F.spark_partition_id(),
    )
    .groupBy("_spark_partition_id")
    .count()
    .orderBy(F.desc("count"))
)

partition_rows.show(100, truncate=False)

Use this to detect execution skew.


14. Repartition by a key when redistribution is actually needed

df2 = df.repartition(
    200,
    "customer_id",
)

Use when a downstream operation benefits from rows with the same key being colocated.

Do not choose 200 blindly; the right count depends on data size and cluster resources.


15. Repartition without a key to spread data more evenly

df2 = df.repartition(200)

This performs a shuffle.

Use when you genuinely need a more even number of execution partitions.

Do not repartition as a default step in every pipeline.


16. Use coalesce when reducing partitions without a full reshuffle

df2 = df.coalesce(20)

Useful after major filtering when the DataFrame has many mostly-empty partitions.

coalesce is typically suited for reducing partitions, not balancing highly skewed data.


17. Avoid repartition(1) in production pipelines

df.repartition(1)

forces all output through one execution partition.

Typical consequences:

single-task bottleneck
poor parallelism
large file risk
slow writes

Use only for intentionally tiny outputs where one file is a real requirement.


18. Do not use coalesce(1) as a universal “single CSV” solution

If an external consumer needs one file, consider a separate export step.

Do not collapse a large distributed pipeline into one task just for naming convenience.


19. Date partitioning fits incremental loads naturally

Example:

load today's data
→ write today's partition

This aligns ingestion and maintenance boundaries.

It also makes controlled backfill easier:

reprocess one date

instead of rewriting the full table.


20. Partitioning can simplify retention

Example:

event_date < retention_cutoff

If data is partitioned by date, retention operations can often target old date partitions more naturally.

Exact delete/drop behavior depends on storage/table technology.


21. Do not assume partition deletion is always a metadata-only operation

In modern table formats, deletes may still involve:

  • transaction log updates
  • file removal markers
  • later vacuum/cleanup
  • concurrency checks

Use the table format’s supported operations.


22. Use partition filters explicitly

Good:

df = (
    spark.read
    .table("silver.orders")
    .filter(
        (F.col("order_date") >= F.lit("2026-10-01")) &
        (F.col("order_date") <  F.lit("2026-11-01"))
    )
)

This gives the optimizer a direct partition predicate.


23. Avoid wrapping partition columns in unnecessary functions

Potentially less pruning-friendly pattern:

WHERE YEAR(order_date) = 2026

More direct:

WHERE order_date >= '2026-01-01'
  AND order_date <  '2027-01-01'

Direct predicates are generally easier for optimizers to reason about.

Verify actual pruning with the execution plan.


24. Check partition pruning with explain

PySpark:

filtered = (
    spark.read
    .table("silver.orders")
    .filter("order_date = '2026-10-04'")
)

filtered.explain("formatted")

Inspect whether partition filters are applied.

Do not assume pruning occurred just because the table is partitioned.


25. Check for filters on the wrong data type

If the partition column is a date but the client passes malformed strings, pruning and correctness may suffer.

Use compatible types.

Example:

from pyspark.sql import functions as F

filtered = df.filter(
    F.col("order_date") == F.to_date(
        F.lit("2026-10-04")
    )
)

26. Partitioning does not replace file statistics

Columnar formats and table engines may also use:

  • min/max statistics
  • data skipping
  • clustering
  • ordering

A non-partition column can still be query-efficient if the physical layout supports it.

Do not turn every filter column into a partition column.


27. Partitioning and clustering solve different problems

Partitioning:

coarse directory/table subdivision

Clustering/order/data layout:

organizes data within/across files to improve skipping

If a column is frequently filtered but too high-cardinality for partitioning, clustering may be a better fit where supported.


28. Use low/medium-cardinality partition columns

Often practical candidates include:

date
month
region
source_system
tenant_group
environment

only if they align with filtering and data volume.

The correct choice is workload-specific.


29. Tenant partitioning can be useful but risky

Partition by tenant only when:

  • tenant count is bounded enough
  • query isolation matters
  • per-tenant volume is meaningful

Risks:

thousands/millions of tenants
→ too many partitions

one huge tenant
→ severe skew

30. Date + tenant can explode partition count

Example:

10,000 tenants
×
365 days
=
3.65 million combinations/year

Even if many are empty, the design is a warning sign.

Prefer coarser grouping or a different layout strategy.


31. Month partitioning may beat day partitioning for small datasets

If daily volume is tiny, daily partitions may create too many small files.

Monthly:

2026-10

can be better when:

  • queries usually span months
  • daily pruning is not needed
  • daily volume is low

Choose granularity based on data volume and query patterns.


32. Hour partitioning may be justified for very high volume

Example:

event_date=2026-10-04/
event_hour=14/

Use only when:

  • each hour still contains substantial data
  • queries/backfills are often hour-scoped
  • daily partitions are too large to manage efficiently

Do not use hour partitioning as a default for event data.


33. Minute-level partitioning is rarely appropriate

Unless data volume and access patterns are extreme, minute partitions usually create excessive metadata and file fragmentation.

Prefer coarser partitioning plus file-level statistics.


34. Partition by immutable values when possible

If partition key changes:

region changes
customer classification changes

the row may need to move between partitions.

Date-of-event or ingestion-date keys are often simpler because they are stable.


35. Do not partition by mutable status

Poor candidate:

status = pending / complete / cancelled

Rows frequently move between statuses.

That creates file rewrites and maintenance overhead.

Use it as a normal column or clustering dimension instead.


36. Ingestion date and event date serve different purposes

event_date:

when business event occurred

ingestion_date:

when platform received it

Partition choice depends on:

  • query pattern
  • replay pattern
  • late-arriving data
  • retention

Do not treat them as interchangeable.


37. Late-arriving data complicates event-date partitioning

Example:

event_date = 2026-10-01
arrives on 2026-10-04

If partitioned by event_date, the pipeline must write to an older partition.

This is often fine, but design for:

  • concurrency
  • backfill overlap
  • partition rewrite/update cost

38. Ingestion-date partitioning simplifies append patterns

If all new arrivals land under:

ingestion_date = today

writes are naturally append-oriented.

But business queries filtering by event date may scan more data.

Choose based on dominant workload.


39. Consider dual date columns without dual partitioning

You can store:

event_date
ingestion_date

but partition by only one.

The other still remains queryable and can benefit from file statistics/clustering.


40. Avoid partitioning by every common filter

If users filter by:

date
country
merchant
product
channel
status

you should not create a six-column partition layout.

Partitioning is coarse-grained.

Use other optimization techniques for additional dimensions.


41. Small files can destroy partition benefits

Example:

event_date=2026-10-04/
  25,000 files

Even though pruning works, query planning/open-file overhead can remain high.

Partitioning and file sizing must be designed together.


42. Check file count by partition

Exact methods vary by platform.

Conceptually, collect:

partition value
file count
total bytes
average file size

Look for:

many tiny files
one giant file
high imbalance

43. Compact small files when the platform supports it

For Delta Lake, a maintenance operation may be:

OPTIMIZE silver.orders;

Platform support and behavior vary.

Do not schedule aggressive compaction blindly; measure whether small files are the real problem.


44. Avoid rewriting all partitions for one day’s change

Bad pattern:

read full table
union new data
overwrite full table

Prefer targeted append/MERGE/partition replacement when table semantics allow it.


45. Partition overwrite must be controlled

If replacing one partition, ensure the write scope cannot accidentally replace unrelated data.

Use supported dynamic partition overwrite / predicate-based replacement / transactional table operations according to your platform.

Do not rely on fragile path deletion scripts.


46. Backfills should target explicit partition ranges

Example:

2026-09-01
through
2026-09-07

Record the exact range in job metadata.

Do not run an unrestricted “rebuild recent data” process without a deterministic boundary.


47. Validate row counts by partition after backfill

Example:

(
    df.groupBy("event_date")
    .agg(F.count("*").alias("rows"))
    .orderBy("event_date")
    .show(100, truncate=False)
)

Compare against expected source volumes.


48. Reconcile business measures by partition

For financial/order data, row count alone is not enough.

Example:

(
    df.groupBy("order_date")
    .agg(
        F.count("*").alias("rows"),
        F.sum("amount").alias("amount"),
    )
    .orderBy("order_date")
)

This helps catch duplicate/missing data after partition-scoped writes.


49. Partition pruning only helps when the query uses the partition column

If most queries are:

WHERE customer_id = 123

but the table is partitioned only by:

order_date

then date partitioning may not reduce scan unless a date filter is also supplied.

Understand actual query behavior.


50. Encourage bounded queries

For large fact tables, require consumers to supply a useful date boundary when practical.

Example API/report contract:

start_date
end_date

This allows the storage layer to prune data intentionally.


51. Avoid accidental full scans

Bad:

SELECT *
FROM huge_fact_table;

Better:

SELECT
    order_id,
    order_date,
    amount
FROM huge_fact_table
WHERE order_date >= '2026-10-01'
  AND order_date <  '2026-11-01';

Partitioning cannot protect you from every unbounded query.


52. Partitioning does not replace column pruning

Even within selected partitions, reading unnecessary columns costs I/O.

Use both:

partition pruning
+
column pruning

53. Partitioning does not replace filtering early

Apply high-value filters before:

  • joins
  • aggregations
  • shuffles

when semantics allow.

Storage partitioning helps input scan, not every later stage.


54. Partitioning does not fix a bad join

If a join multiplies rows unexpectedly, partitioning may only make the wrong result faster.

Validate:

join keys
cardinality
output grain

first.


55. Partitioning does not fix source skew automatically

If one key owns most records:

merchant_id = 1
→ 60% of rows

partitioning by that key can concentrate work.

Measure distribution first.


56. Salt only when skew is real and worth the complexity

For extreme join/aggregation skew, salting may spread one hot key across several derived keys.

Example concept:

merchant_id=1
→ 1_0, 1_1, 1_2, ...

This adds complexity and often requires a second aggregation step.

Use only after confirming skew in actual execution.


57. Do not salt storage partitions casually

Salting is primarily an execution/join technique.

Creating random physical storage partitions can make querying and maintenance harder.


58. Hash partitioning can distribute workload evenly

Conceptually:

hash(customer_id) % N

can produce more balanced buckets.

Useful in some storage/processing systems.

But it usually reduces intuitive pruning for date/range queries.

Choose based on engine and workload.


59. Range partitioning helps ordered data

Example:

customer_id 1–1,000,000
customer_id 1,000,001–2,000,000

Range partitioning can align with ordered access patterns.

Be careful when new data concentrates at one end of the range.


60. List partitioning fits known categories

Example:

region = US
region = EU
region = APAC

Useful when categories are stable and balanced enough.

If one category dominates, parallelism can suffer.


61. Partition strategy should include growth expectations

Ask:

How many partitions today?
How many in 1 year?
How many in 5 years?

A design that looks fine at 100 tenants may fail at 100,000.


62. Account for empty/sparse partitions

A theoretical partition count can be much larger than populated partitions.

Still, sparse high-cardinality layouts are often a warning sign because metadata and directory structure can grow unpredictably.


63. Keep partition columns visible in table metadata

Users should be able to discover:

what table is partitioned by

without inspecting storage paths manually.

Use catalog/table metadata and documentation.


64. Do not make consumers depend on physical directory paths

A consumer should query:

table + partition predicate

not construct internal storage paths unless the contract explicitly requires path-level access.

This keeps the table layout changeable.


65. Partitioning and retention should align where possible

If policy is:

retain 13 months

and the table is partitioned by month/date, cleanup can be easier to reason about.

But use table-format-supported deletion/retention semantics.


66. Partitioning can improve failure isolation

A bad write limited to:

event_date=2026-10-04

is easier to repair than a full-table overwrite.

This is one reason date-bounded incremental designs are operationally attractive.


67. Record partition scope in execution logs

Useful job metadata:

job_id
partition_start
partition_end
partition_column
row_count
status
code_version

This improves replay and incident response.


68. Detect missing partitions

If one partition is expected per business day, monitor for gaps.

Conceptually:

expected dates
MINUS
actual dates

A successful pipeline run does not prove every expected partition arrived.


69. Detect duplicate partition loads

Store batch/load metadata so you can identify:

same source batch
written twice

Partitioning alone does not prevent duplicates.


70. Treat partition columns as part of the data contract

Changing from:

event_date

to:

ingestion_month

can affect:

  • query plans
  • retention
  • backfills
  • downstream filters
  • job logic

Do not change partition layout casually on large production tables.


71. Repartitioning an existing large table is a migration

Typical migration concerns:

full rewrite cost
concurrent readers
cutover
validation
rollback
storage duplication

Plan it like a data migration.


72. Validate before and after repartitioning

Compare:

row count
business totals
key uniqueness
date coverage
schema
query performance
file count
storage size

Do not judge success by query speed alone.


73. Do not assume more partitions mean more parallelism

Too many tiny partitions can increase:

  • scheduling overhead
  • metadata work
  • file-open cost

Parallelism has diminishing returns.


74. Do not assume fewer partitions are always better

One giant partition can create:

  • long-running tasks
  • poor parallelism
  • large rewrites
  • hot spots

Balance is the goal.


75. Match partitioning to maintenance operations

If operations frequently do:

rebuild one date
delete one month
reconcile one region

partitioning that aligns with those scopes can simplify maintenance.


76. Match partitioning to security only when the platform enforces it properly

Do not assume physical partition separation equals access control.

Security should be enforced by:

  • table permissions
  • row-level security
  • tenant-aware authorization
  • platform policy

Partition layout is not a security boundary by itself.


77. Partitioning may interact with concurrency

Two jobs writing different partitions may conflict less than two jobs rewriting the same partition, but exact behavior depends on the table format and engine.

For Delta, use Delta-aware writes and handle concurrency according to platform semantics.


78. Avoid concurrent full-table overwrites

Even if jobs target logically separate data, a full-table overwrite is a broad operation.

Prefer narrowly scoped writes.


79. Partition by source system only when it helps operations

Example:

source_system = crm
source_system = billing
source_system = web

This can help source-specific reprocessing.

But if every query spans all sources and each source is small, it may add unnecessary fragmentation.


80. Use a repeatable decision checklist

Before partitioning a table, answer:

1. What are the dominant filters?
2. What are the incremental/backfill boundaries?
3. How many distinct partition values exist now and later?
4. Is data evenly distributed?
5. Will each partition contain enough data?
6. Will writes frequently modify old partitions?
7. Can file statistics/clustering solve the problem instead?
8. Is the table large enough to benefit from partitioning at all?

If those questions do not produce a clear answer, avoid premature partitioning.

What the result means

Strategy Best fit Main risk
No partitioning Small/moderate tables, broad scans Less pruning on very large data
Date Time-bounded analytics and incremental loads Too many tiny partitions if volume is low
Month Lower-volume time-series data Less precise pruning
Hour Very high-volume time-series data Partition explosion
Region/category Stable bounded categories Uneven/skewed categories
Tenant Strong tenant-scoped workloads High cardinality / hot tenants
Date + dimension Very large, consistently filtered workloads Multiplicative partition count
Hash/bucket Balanced key distribution Weak human/range pruning
Range Ordered/range access Hot end ranges / rebalancing

The most important tradeoff is:

better pruning
vs
more partition/file metadata

What to look for

Queries still scan most data

Check:

  • predicate uses partition column
  • data types match
  • functions hide partition predicate
  • filters are applied early
  • engine actually reports partition pruning

Too many tiny files

Check:

  • partition granularity
  • write frequency
  • one-file-per-task behavior
  • need for compaction

One partition is much larger than others

Check skew in:

tenant
region
merchant
date

A single hot partition can dominate runtime.

Backfills are expensive

Check whether partition boundaries align with backfill scope.

If not, the table may be rewriting/scanning too much data.

Daily partitions are nearly empty

Use coarser partitioning such as month, or no partitioning.

Queries frequently filter a high-cardinality column

Consider clustering/data skipping/indexing rather than physical partitioning.

Production notes

Partitioning is a physical design choice

It affects:

  • read cost
  • write cost
  • maintenance
  • metadata
  • concurrency
  • file layout

Treat it like schema/index design, not decoration.

Partition pruning must be verified

Use execution plans and scan metrics.

Do not assume the optimizer used your layout.

File size and partitioning must be considered together

A good partition key with terrible file fragmentation is still a poor design.

Partition layout should evolve slowly

Changing partition strategy on a large table usually requires a rewrite/migration.

Avoid platform-independent magic numbers

There is no universal ideal:

partition count
file size
rows per partition

Measure using your engine, storage, and workload.

Common mistakes

1. Partitioning by every filter column

This creates an explosion of partition combinations.

2. Partitioning by unique IDs

High-cardinality keys create tiny partitions and metadata overhead.

3. Partitioning by raw timestamp

Timestamp cardinality is usually too high.

4. Using repartition(1) to create a single output file

This destroys distributed parallelism.

5. Confusing Spark execution partitions with table partitions

They solve different problems.

6. Assuming partitioning fixes skew

A skewed partition key can make skew worse.

7. Ignoring small files

Partition pruning can still be slow when every partition contains thousands of tiny files.

8. Rebuilding the full table for partition-scoped changes

Use targeted writes when possible.

9. Choosing partition columns without query evidence

A partition that is never filtered is often wasted complexity.

10. Treating physical partitions as a security boundary

Use proper authorization/security controls.

Next step

Use observed workload behavior to choose the next action.

  • Large time-series table with date filters → consider date partitioning.
  • Daily partitions are tiny → move to coarser month partitions or no partitioning.
  • One key dominates the data → avoid partitioning by that key; investigate skew handling.
  • Queries filter high-cardinality columns → evaluate clustering/data skipping/indexing instead.
  • Deep scans continue despite partitioning → inspect pruning in the execution plan.
  • Millions of tiny files exist → address write pattern and compaction strategy.
  • Backfills rewrite too much data → align partition boundaries with recovery scope.
  • Need to change an existing partition scheme → treat it as a controlled table migration.
  • Unsure whether to partition → measure table size, filter patterns, cardinality, skew, and maintenance boundaries first.

← All cheat sheets