14 February 2025

🏭🧊🗒️Microsoft Fabric: Partitions in Lakehouses [Notes]

 Disclaimer: This is work in progress intended to consolidate information from various sources for learning purposes. For the latest information please consult the documentation (see the links below)! 

Last updated: 14-Feb-2024

[Microsoft Fabric] Partitions

  • {def} a data organization technique used to split a large dataset into smaller, more manageable nonoverlapping subsets (aka partitions, shards
    • a pattition is defined based on one or more fields
    • each partition contains a subset of the data
    • each partitions can be stored and processed independently
  • {goal} improve performance, scalability, and manageability of large data tables
  • {benefit} allows to split large tables into smaller, manageable partitions based on specific criteria [2]
    • e.g., date ranges, regions, categories, entities
  • {benefit} allows to improve queries' performance as they can target specific partitions [2]
    • reduces the amount of data scanned [2]
    • improves queries' performance [2]
  • {benefit} allows for more efficient data loading [2]
  • {benefit} facilitates the management of big tables [2]
    • maintenance tasks can be performed on individual partitions  [2]
    • obsolete data partitions can be removed with no overhead, adding new partitions on a need basis [2]
  • applies to 
    • backups
    • indexing
    • allows optimizing query performance for specific subsets of data
    • statistics
  • performance can be affected by
    • the choice of partition columns for a delta table [1]
    • the number and size of partitions of the partition column [1]
    • a column with high cardinality (mostly or entirely made of unique values) results in a large number of partitions [1]
      • ⇐ negatively impacts performance of the metadata discovery scan for changes [1]
      • {recommendation} if the cardinality of a column is high, choose another column for partitioning [1]
    • the size of each partition can also affect performance
      • {recommendation} use a column that would result in a partition of at least (or close to) 1 GB [1]
      • {recommendation} follow the best practices for delta tables maintenance [1]
      • a large volume of small-sized parquet files increases the time it takes to sync the changes [1]
        • ⇒ leads to large number of parquet files in a delta table due to [1]
          • over-partitioning
            • partitions with high number of unique values [1]
            • {recommendation} choose a partition column that 
              • doesn't have a high cardinality [1]
              • results in individual partition size of at least 1 GB [1]
          • many small files
            • batch and streaming data ingestion rates might result in small files
              • depends on the frequency and size of changes being written to a lakehouse [1]
            • {recommendation} implement regular lakehouse table maintenance [1] 

Previous Post <<||>> Next Post

References:
[1] Microsoft Learn (2024) Microsoft Fabric: SQL analytics endpoint performance considerations [link]
[2] Kenneth A Omorodion (2024) Partitioning Data in Microsoft Fabric to Improve Performance
written [link]
[3] Microsoft Learn (2024) Microsoft Fabric: Loading Fabric Lakehouse Tables with partitions [link]
[4] 

Resources
[R1] Microsoft Learn (2024) Microsoft Fabric: Load data to Lakehouse using partition in a Data pipeline [link]

No comments:

Related Posts Plugin for WordPress, Blogger...

About Me

My photo
Koeln, NRW, Germany
IT Professional with more than 24 years experience in IT in the area of full life-cycle of Web/Desktop/Database Applications Development, Software Engineering, Consultancy, Data Management, Data Quality, Data Migrations, Reporting, ERP implementations & support, Team/Project/IT Management, etc.