DP-700: mastering partitioning and sorting in Fabric tables

I will teach how to plan and implement partitioning and sorting in Microsoft Fabric tables (OneLake/Delta tables) — a useful skill for the DP-700 and critical in practice for query performance and storage efficiency.

What you need to know

Partitioning a table means dividing the data into logical segments (for example, by date, region) so that read and maintenance operations process only a subset of the data. Sorting (sort order) organizes files/rows within partitions according to key columns, which speeds up join operations, range scans and compression. In Fabric, this applies to Delta-formatted tables inside OneLake and affects both query time in Power BI/SQL and processing cost.

Simple example: you have a transactions table with millions of rows. If partitions are by year and month, historical queries that only consult one month avoid scanning the entire history. If within each partition the data is sorted by customer_id, a join by customer will be much more efficient.

How it works

Key concepts:

• Partition column: chosen to segment the data; ideally low cardinality per partition and matching query patterns (e.g.: date, region).
• Granularity: by year/month/day; higher granularity means more files/partitions and metadata overhead.
• Sorting (sort): columns used to order files within the partition; improves sequential reads and compression.
• File pruning: mechanism that avoids reading irrelevant files when appropriate partitioning/metadata exists.
• Compaction/OPTIMIZE: operation to combine many small files into larger files, maintaining sort order and reducing read costs.

In practice

Practical steps to implement partitioning and sorting in a Delta table in Fabric (generic SQL/Notebook example):

1. Choose columns for partitioning and sorting.

   Rule of thumb: partition by a column frequently used in filters (e.g.: event_date), sort by a column used in joins/sorts (e.g.: user_id).

2. Create the table with partitioning and sorting (example in T-SQL/Delta DDL):

   CREATE TABLE sales_delta (
     sale_id BIGINT,
     event_date DATE,
     customer_id BIGINT,
     amount DECIMAL(10,2)
   )
   USING DELTA
   PARTITIONED BY (YEAR(event_date), MONTH(event_date))
   -- Optional: properties for sort/optimize (varies by engine)
   

   Note: the exact syntax of PARTITIONED BY and sorting options may vary depending on the environment (Spark notebook, SQL endpoint). In Fabric, you often use commands in Spark notebooks or the options of Data Factory/ingestion.

3. Ingestion: write data preserving the partition. Spark example:

   df.write
     .format("delta")
     .mode("append")
     .partitionBy("year","month")
     .save("/onedrive/OneLake/.../sales_delta")
   

4. Optimization: combine files and apply sorting (generic example):

   -- In Spark/Delta
   OPTIMIZE delta.`/onedrive/OneLake/.../sales_delta`
   WHERE year = 2026 AND month = 06
   ZORDER BY (customer_id)
   

   The OPTIMIZE command (or equivalent) will compact files in the specific partition and apply physical ordering (ZORDER is a common technique to improve data locality across multiple columns).

5. Validate: test queries with and without partition filters to confirm reduced file scans (check execution plan statistics or metrics usage).

Common mistakes

• Choosing high-cardinality columns for partitioning (for example, transaction_id) — creates too many small partitions, increasing overhead.
• Excessive partitioning (too fine granularity) — causes many small files and worsens performance due to metadata and ineffective pruning.
• Not performing compaction/OPTIMIZE regularly after streaming or micro-batch ingestions — leads to many small files and penalizes reads.

How to practice

Practice these tasks in a test environment in Fabric/OneLake: create a Delta table, import real (or generated) data and experiment with different partitioning and sorting strategies, measuring the impact on queries. For DP-700 exam preparation, use the official Microsoft free Practice Assessment to evaluate your knowledge and consult the official study guide (both free). These sources help align practice with the skills measured without using protected material.

In summary

• Partitioning reduces the volume of data read when filtering by partition values; choose columns that match query patterns.
• Sorting within partitions (ZORDER/ORDER) improves joins and range scans and favors compression.
• Avoid overly fine partitions and high-cardinality columns as partition keys.
• Run compaction/OPTIMIZE after ingestions to reduce small files and improve performance.