Designing and applying a partitioning scheme

Partitioning in Azure Databricks (via Delta Lake) organizes data into subdirectories based on column values, enabling partition pruning so queries scan only relevant files instead of the full table. Choosing the right partitioning strategy balances query performance against the risk of creating too many small files, which can degrade performance and increase metadata overhead.

1 · Learn the must-know

• 1Use partitionBy() when writing a DataFrame or CREATE TABLE ... PARTITIONED BY in SQL to physically separate data into folders by the chosen column(s).
• 2Partition columns should have low-to-moderate cardinality (e.g., date, region, category); high-cardinality columns (like a unique ID or timestamp with seconds) create too many small partitions and hurt performance.
• 3As a general guideline, avoid partitioning tables smaller than about 1TB, and ensure each partition holds at least ~1GB of data to prevent the small-file problem.
• 4Partition pruning lets Spark skip reading irrelevant partitions when a query filters on the partition column, significantly reducing I/O.
• 5For high-cardinality columns or multi-dimensional filtering needs, Z-Ordering (via OPTIMIZE ... ZORDER BY) or Delta Lake Liquid Clustering are preferred over traditional partitioning since they co-locate related data without creating excessive directories.
• 6Regularly run OPTIMIZE to compact small files within partitions, and consider VACUUM to clean up obsolete files, since heavy partitioning combined with frequent small writes accelerates file fragmentation.

2 · Check your understanding