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