How to create dynamic partitions in Delta tables on the Lakehouse

João Barros 09 de October de 2026 6 min read

This tutorial shows how to create dynamic partitions in a Delta table on the Lakehouse to improve data read and write performance. Correctly partitioning reduces I/O costs, speeds up queries and avoids small files when writing in parallel. In addition, well-defined partitions make cleanup and retention operations easier and help the engine apply partition pruning, which can reduce the volume read by 10x to 100x in common scenarios.

Prerequisites

Account with access to Microsoft Fabric and a Lakehouse created.
Permissions to create/alter Delta tables in the Lakehouse.
Sample files (CSV/JSON) uploaded to a Lakehouse or OneLake path.
Basic knowledge of SQL and PySpark.

Step 1: Choose the right partition column

Why: a good partition column balances the number of files and the selectivity of queries. Avoid columns with high cardinality (e.g., a unique ID with millions of values) — this creates many small partitions and increases metadata overhead. Also do not use columns with very low cardinality (e.g., a field that always has the same value), because there is no benefit. For temporal data, using a hierarchy like year/month[/day] is a common practice. For example:

Dates: year/month — good balance for years with ~12 months and hundreds of thousands to millions of records per month.
High cardinality: user_id with 10M users — do not use as a partition.
Low cardinality: country with 5-10 values — not enough for significant I/O reduction.

Rule of thumb: each partition should contain at least tens of MB to hundreds of MB (e.g., 100–500 MB) to be efficient with Delta files. If your partitions consistently fall below ~64 MB, consider reducing partition granularity.

Step 2: Create an unpartitioned Delta table (optional, to import initial data)

It's useful to start unpartitioned to validate the schema and then convert to partitioned when you have real data load. This helps verify types and nulls with real data before defining final partitions. Example in SQL on the SQL Analytics endpoint or a notebook:

CREATE TABLE IF NOT EXISTS my_db.raw_events_delta
USING DELTA
LOCATION 'lakehouse:////raw/events'
AS SELECT * FROM csv.`/paths/events/*.csv`;

After importing a few million rows, inspect samples and observe the temporal distribution to decide whether to use year/month/day.

Step 3: Create a partitioned Delta table with the final schema

Create the table with PARTITIONED BY. Here we use year and month extracted from the event_timestamp column. This creates an organized folder structure, for example /year=2024/month=06/, which facilitates pruning and data management.

CREATE TABLE IF NOT EXISTS my_db.events_delta
(
  event_id STRING,
  event_timestamp TIMESTAMP,
  user_id STRING,
  event_type STRING,
  payload STRING
)
USING DELTA
PARTITIONED BY (year, month)
LOCATION 'lakehouse:////tables/events';

Note: choosing PARTITIONED BY avoids the need to create separate physical columns for year and month if you prefer to extract them in the SELECT, but having explicit columns makes queries and indexes easier.

Step 4: Write data using dynamic partitions with PySpark

When writing, compute year and month and use write.partitionBy to create dynamic partitions. This ensures files are written to the correct folder according to the event date. Minimal PySpark example:

from pyspark.sql.functions import year, month, to_timestamp

df = spark.read.csv('/mnt/lake/events/*.csv', header=True)
df = df.withColumn('event_timestamp', to_timestamp(df.event_timestamp))
df = df.withColumn('year', year(df.event_timestamp))
df = df.withColumn('month', month(df.event_timestamp))

(df.write
  .format('delta')
  .mode('append')
  .partitionBy('year','month')
  .save('lakehouse:////tables/events'))

Concrete example: if you have 10M records per month and a cluster with 200 executors, estimate final files per partition and adjust repartition to target ~128 MB per file.

Step 5: Avoid small files when writing in parallel

Common mistake: each executor writes a few MB, creating hundreds of small files per partition, which degrades performance. Solutions:

Increase file size with coalesce/repartition before writing: for example, if you target ~128 MB files and have 1 TB of data, estimate 8000 files and use repartition(8000).
Repartition by a logical partition key (e.g., concat year-month) to group data for the same partition before the write.
Run OPTIMIZE / compaction after ingestion to combine small files (when supported in the environment).

# Repartition by approximate partition before writing
from pyspark.sql.functions import concat_ws

# Create a partition_key column to repartition
df = df.withColumn('partition_key', concat_ws('-', df.year.cast('string'), df.month.cast('string')))
# Repartition by partition_key to reduce files
df = df.repartition('partition_key')

(df.drop('partition_key')
  .write
  .format('delta')
  .mode('append')
  .partitionBy('year','month')
  .save('lakehouse:////tables/events'))

Step 6: Update schema or add partitions

If you add new columns, use ALTER TABLE to avoid rewriting the entire table. To detect new external folders/partitions, some environments support commands like MSCK REPAIR; others offer SHOW PARTITIONS. Examples:

ALTER TABLE my_db.events_delta ADD COLUMNS (device STRING);
-- List partitions
SHOW PARTITIONS my_db.events_delta;

If you need to migrate from unpartitioned to partitioned with millions of rows, perform a controlled batch write to avoid I/O spikes.

Verify the result

Confirm that partitions exist and that queries perform partition pruning. Run:

-- See physical partitions
SHOW PARTITIONS my_db.events_delta;

-- Partition pruning test (inspect plans)
EXPLAIN SELECT * FROM my_db.events_delta WHERE year = 2024 AND month = 6;

-- Count by partition
SELECT year, month, COUNT(*) FROM my_db.events_delta GROUP BY year, month ORDER BY year, month;

If EXPLAIN shows that only the paths year=2024/month=6 are read, pruning is working. Monitor query times and I/O: ideally you will see significant reduction when the filter matches partitions.

Conclusion

Dynamic partitions in Delta tables on the Lakehouse improve performance and cost when well chosen. Start with a conservative granularity (year/month), monitor data distribution and average file size and adjust repartition at write time. Next steps: automate partitioning in ingestion pipelines, use OPTIMIZE and ZORDER (when supported) to improve internal sorting and reduce read latency, and create alerts to detect growth of small files. Practical tip: aim for ~128–256 MB files per partition and review the strategy quarterly as data grows.