10.1 Partitioning in Microsoft Fabric
Partitioning means physically organizing a large dataset into smaller groups based on the values of one or more columns.
The main reason we use partitioning is:
Instead of scanning the entire dataset, the query can read only the partitions it needs.
This can significantly improve performance for large datasets.
1. Simple example
Imagine a Sales table containing 1 billion rows from 2022–2026.
Without partitioning:
Sales Table ────────────────────
2022 data
2023 data
2024 data
2025 data
2026 data
Total = 1 Billion Rows
You run:
SELECT * FROM Sales WHERE SalesYear = 2026;
Without effective data skipping/partitioning, the engine may need to examine much more data than the 2026 portion.
But if the data is partitioned by year:
Sales │ ├── SalesYear=2022 │ ├── SalesYear=2023 │ ├── SalesYear=2024 │ ├── SalesYear=2025 │ └── SalesYear=2026
The engine can target:
SalesYear=2026
↓
Read required files
↓
Return results
This is called partition pruning.
2. Think of partitioning like a filing cabinet
Imagine you have 1 million documents.
Without organization
Big Box │ ├── January documents ├── March documents ├── December documents ├── February documents ├── July documents └── ...
Finding December documents requires searching a huge collection.
Partitioned
Documents │ ├── January ├── February ├── March │
...
└── December
Now:
"Give me December documents."
You go directly to:
December
That's the basic idea behind partitioning.
3. Partitioning in a Fabric Lakehouse
In Microsoft Fabric, partitioning is particularly important when working with Lakehouse / Delta tables and Spark.
Suppose:
Lakehouse │ ▼ Tables │ ▼
FactSales
The table contains:
SalesID
CustomerID
ProductID
SalesDate
Country
Amount
You could partition the underlying Delta data by something like:
Year
or:
Year + Month
depending on data volume and query patterns.
4. Physical organization
Suppose you partition by:
Year
Conceptually, the underlying storage might look like:
FactSales/
│
├── Year=2024/
│ ├── part-00001.parquet
│ ├── part-00002.parquet
│ └── part-00003.parquet
│
├── Year=2025/
│ ├── part-00001.parquet
│ ├── part-00002.parquet
│ └── part-00003.parquet
│
└── Year=2026/
├── part-00001.parquet
├── part-00002.parquet
└── part-00003.parquet
Notice something important:
Partition ≠ File
A partition can contain multiple Parquet files.
5. Query without partition pruning
Imagine:
2024 → 200 GB
2025 → 250 GB
2026 → 300 GB
Total:
750 GB
Query:
SELECT SUM(Amount) FROM FactSales WHERE Year = 2026;
If the engine had to scan everything:
2024 ─┐ 2025 ├── Scan 750 GB 2026 ─┘
That's expensive.
6. Query with partition pruning
With useful partitioning:
WHERE Year = 2026
│
▼
Partition Pruning
│
├── Year=2024 ✕
├── Year=2025 ✕
│
└── Year=2026 ✓
Only the relevant partition needs to be read.
Conceptually:
750 GB total
↓
Query Year=2026
↓
Read ~300 GB
That can reduce:
I/O
↓
Data scanned
↓
Processing
↓
Query time
7. Partitioning by Date
Date/time columns are common partitioning candidates because analytical queries often filter by time.
Suppose your data spans 10 years.
You could partition by:
Year
Result:
FactSales │ ├── Year=2017 ├── Year=2018 ├── Year=2019 │
...
├── Year=2025 └── Year=2026
Queries like:
WHERE Year = 2026
can benefit.
8. Year + Month partitioning
What if each year contains enormous amounts of data?
For example:
2026 = 10 TB
A single yearly partition may still be too large.
You might use:
Year +
Month
Result:
FactSales
│
└── Year=2026
│
├── Month=01
├── Month=02
├── Month=03
├── Month=04
│
...
└── Month=12
Now a query for July 2026 can target:
Year=2026
Month=07
instead of reading the whole year.
9. Example
Query:
SELECT
ProductID,
SUM(Amount)
FROM FactSales
WHERE Year = 2026
AND Month = 7
GROUP BY ProductID;
The engine can potentially skip:
2024 ✕
2025 ✕
2026 ├── January ✕ ├── February ✕ ├── March ✕ ├── April ✕ ├── May ✕ ├── June ✕ ├── July ✓ ├── August ✕
...
This is partition pruning.
10. Choosing a partition column
This is where partition design becomes important.
A good partition column is often:
Frequently used in query filters.
Reasonably distributed.
Low-to-moderate cardinality.
Able to create partitions large enough to avoid excessive small files.
Typical candidates:
Year
Month
Date
Region
Country
But the correct choice depends on the data and workload.
11. Bad partition column: CustomerID
Imagine:
100 million customers
and you partition by:
CustomerID
You could potentially create an enormous number of partitions:
CustomerID=1
CustomerID=2
CustomerID=3
...
CustomerID=100000000
That's usually a terrible partitioning strategy.
This is called:
High-cardinality partitioning
12. What is cardinality?
Cardinality means the number of distinct values in a column.
Examples:
Gender ------
Distinct values ≈ 2–few
LOW CARDINALITY
Country -------
Distinct values ≈ hundreds
MODERATE CARDINALITY
CustomerID ----------
Distinct values = millions
HIGH CARDINALITY
Partitioning on extremely high-cardinality columns can create too many tiny partitions/files.
13. Over-partitioning
Suppose you partition by:
Year
Month
Day
Hour
Minute
You might end up with:
Year=2026
└── Month=08
└── Day=03
└── Hour=10
└── Minute=01
Then:
Minute=01 → 1 tiny file
Minute=02 → 1 tiny file
Minute=03 → 1 tiny file
...
Now you have potentially thousands or millions of tiny files.
This can hurt performance.
14. The Small Files Problem
This is one of the most important topics in Fabric/Spark performance.
Bad:
Dataset = 100 GB
100,000 files
×
~1 MB each
Spark has to manage huge numbers of files/tasks.
There is overhead for:
File discovery
Task scheduling
Metadata
Opening files
Closing files
Processing
So:
More partitions does NOT automatically mean better performance.
15. Partitioning and File Size are connected
Notice your syllabus:
10.1 Partitioning
10.2 File Sizes
These topics are intentionally related.
Good design:
Large Dataset
↓
Useful Partitioning
↓
Reasonably sized files
↓
Efficient Spark processing
Bad design:
Large Dataset
↓
Too many partitions
↓
Tiny files
↓
Too many Spark tasks
↓
Poor performance
16. Spark partitioning
There is another concept you need to distinguish:
Spark execution/dataframe partitions
versus:
Storage/table partitions
They are related but not exactly the same thing.
Table/storage partitioning
FactSales/ ├── Year=2025/ └── Year=2026/
This affects physical data organization.
Spark partitions
When Spark processes data:
Dataset
↓
Partition 1 → Executor
Partition 2 → Executor
Partition 3 → Executor
Partition 4 → Executor
These affect parallel processing.
This distinction becomes important in your 10.3 Spark Optimization topic.
17. Creating a partitioned Delta table with Spark
For example, suppose your DataFrame is:
df
You can write:
df.write \
.format("delta") \
.mode("overwrite") \
.partitionBy("Year") \
.saveAsTable("FactSales")
Conceptually:
DataFrame
↓
partitionBy("Year")
↓
Delta Table │ ├── Year=2024 ├── Year=2025 └── Year=2026
18. Partitioning by Year and Month
You could also use:
df.write \
.format("delta") \
.mode("overwrite") \
.partitionBy("Year", "Month") \
.saveAsTable("FactSales")
Result:
FactSales
│
├── Year=2025
│ ├── Month=01
│ ├── Month=02
│ └── ...
│
└── Year=2026
├── Month=01
├── Month=02
└── ...
But don't automatically choose multiple columns. The decision should be driven by data volume and query patterns.
19. repartition() in Spark
Now we move to Spark partitions, not table partitioning.
Suppose:
df.rdd.getNumPartitions()
returns:
4
You can redistribute the DataFrame:
df2 = df.repartition(20)
Now Spark can process the data using approximately:
20 partitions
Conceptually:
Original
[P1] [P2] [P3] [P4]
repartition(20)
[P1] [P2] [P3] ... [P20]
This can increase parallelism, but it involves a shuffle.
20. repartition() by column
You can also write:
df2 = df.repartition("Region")
Spark redistributes rows according to the specified expression/column.
Conceptually:
Data │ ▼ Shuffle │ ├── Partition ├── Partition ├── Partition └── ...
This may be useful before certain joins, aggregations, or writes, but it should be used deliberately because shuffling is expensive.
21. coalesce()
If you have too many Spark partitions, you may want fewer.
For example:
df2 = df.coalesce(10)
Conceptually:
100 partitions
↓
coalesce(10)
↓
10 partitions
A useful beginner distinction:
repartition()
↓
Redistributes data
Can increase/decrease partitions
Usually causes shuffle
coalesce()
↓
Usually reduces partitions
Often avoids a full shuffle
22. Partitioning and parallelism
Spark is a distributed processing engine.
Suppose:
1 TB Dataset
If effectively processed as one giant partition:
1 TB
↓
One Task
↓
Slow
If appropriately divided:
1 TB │ ├── P1 ├── P2 ├── P3 ├── P4 ├── ... └── Pn
Spark can process multiple partitions in parallel across available executors/cores.
P1 → Executor 1
P2 → Executor 2
P3 → Executor 3
P4 → Executor 4
That's one of the fundamental ideas behind Spark performance.
23. But more partitions aren't always better
Suppose you create:
1,000,000 partitions
for a 10 GB dataset.
Now Spark has huge scheduling/metadata overhead.
Think:
Too Few Partitions
↓
Poor Parallelism
Too Many Partitions
↓
Scheduling + File Overhead
Balanced Partitions
↓
Better Performance
Optimization is about finding an appropriate balance.
24. Data Skew
Another partition-related problem is data skew.
Suppose sales distribution is:
India → 800 GB
USA → 100 GB
UK → 50 GB
Germany → 50 GB
If work is distributed poorly by Country, one worker may receive:
India → 800 GB
while others receive much less.
Executor 1 → 800 GB ← overloaded
Executor 2 → 100 GB
Executor 3 → 50 GB
Executor 4 → 50 GB
Most executors finish quickly, but one keeps working.
This is data skew.
25. Balanced partitioning
Ideal distribution looks more like:
Executor 1 → 250 GB
Executor 2 → 250 GB
Executor 3 → 250 GB
Executor 4 → 250 GB
Balanced work generally improves parallel processing.
You'll encounter this again when learning:
Spark Optimization
26. Partition Pruning
This term is very important for interviews.
Partition pruning means:
The query engine skips partitions that cannot contain the requested data.
Example:
WHERE Year = 2026
Storage:
Year=2023 ✕
Year=2024 ✕
Year=2025 ✕
Year=2026 ✓
Only the required partition is targeted.
This reduces:
Data scanned
↓
I/O
↓
CPU work
↓
Query duration
27. Partition elimination vs full scan
Full scan
Query
↓
Scan
↓
2023
2024
2025
2026
↓
Filter
↓
Result
Partition pruning
Query
WHERE Year=2026
↓
Partition metadata
↓
Skip 2023
Skip 2024
Skip 2025
↓
Read 2026
↓
Result
That's the performance benefit you're trying to achieve.
28. Real-world example
Imagine an IoT table:
SensorEvents
100 TB
Data:
Timestamp
SensorID
Temperature
Location
Status
Most queries are:
Last 7 days
Last 30 days
Yesterday
Current month
A time-based partition strategy may make sense because queries frequently filter on time.
SensorEvents │ ├── Date=2026-08-01 ├── Date=2026-08-02 ├── Date=2026-08-03 └── ...
Then:
WHERE Date = '2026-08-03'
can target the relevant date partition.
However, whether daily, monthly, or another strategy is best depends on how much data arrives each day.
29. When should you partition?
Partitioning becomes most useful when:
✓ Dataset is large
✓ Queries frequently filter on the partition column
✓ Partitions remain reasonably large
✓ Data distribution is appropriate
It may be unnecessary or harmful when:
✕ Dataset is small
✕ Partition column has extremely high cardinality
✕ Each partition contains tiny amounts of data
✕ Queries rarely filter on the partition column
30. Partitioning in Medallion Architecture
Suppose your Fabric architecture is:
Source
↓
Bronze
↓
Silver
↓
Gold
You may use different strategies depending on each layer.
For example:
BRONZE
Raw Events
↓
Date-based organization
SILVER
Cleaned Events
↓
Date/Business-oriented strategy
GOLD
Aggregated Data
↓
Often much smaller
↓
May require less partitioning
Don't assume every table at every layer needs identical partitioning.
31. Common mistakes
Avoid blindly doing these:
Partition every table
or:
Partition by every filter column
or:
Partition by CustomerID
with millions of customers
or:
Create millions of tiny files
or:
Use Year/Month/Day/Hour/Minute
without checking data volume
The best strategy depends on:
Data size + data distribution + query pattern + file sizes
32. Interview question — What is Partitioning?
A strong answer:
Partitioning is a technique for organizing a large dataset into smaller logical/physical groups based on one or more columns. In Fabric Lakehouse and Delta scenarios, good partitioning can improve query performance through partition pruning, where the engine reads only relevant partitions instead of scanning unnecessary data.
33. Interview question — What is Partition Pruning?
Partition pruning is an optimization where the query engine skips partitions that don't match the query filter. For example, if a Delta table is partitioned by year and the query filters Year = 2026, the engine can skip partitions for previous years.
34. Interview question — What makes a good partition column?
A strong answer:
A good partition column is frequently used in query filters, has suitable cardinality and distribution, and produces partitions large enough to avoid the small-files problem. Date-related columns such as year or month are common candidates for large time-based datasets, but the design should depend on the workload.
35. Interview question — Why not partition by CustomerID?
CustomerID usually has very high cardinality. Partitioning by millions of customer IDs could create millions of small partitions and files, increasing metadata and task-management overhead and potentially reducing performance instead of improving it.
36. Interview question — repartition() vs coalesce()
For Spark:
repartition() redistributes data across partitions and typically causes a shuffle; it can increase or decrease the partition count. coalesce() is commonly used to reduce the number of partitions and can often do so without a full shuffle.
37. Quick revision
PARTITIONING ================================
Purpose:
Split large datasets
into manageable groups
Example:
FactSales │ ├── Year=2024 ├── Year=2025 └── Year=2026
Query:
WHERE Year = 2026
Partition Pruning:
2024 ✕
2025 ✕
2026 ✓
Benefits: --------
Less data scanned
Less I/O
Faster queries
Better manageability
Risks: ------
Too many partitions
Small files
High-cardinality columns
Data skew
One diagram to remember
10 TB FACT_SALES
│
▼
PARTITION
│
┌────────────┼────────────┐
▼ ▼ ▼
Year=2024 Year=2025 Year=2026
3 TB 3 TB 4 TB
│ │ │
✕ ✕ ✓
│
WHERE Year=2026
│
▼
Read required data
│
▼
Faster Query
The key takeaway
Remember this chain:
Large Table → Partition → Query Filter → Partition Pruning → Less Data Scanned → Better Performance
And one important warning:
Partitioning is not "the more partitions, the better."
Good partitioning means choosing a partition strategy that matches your data volume and query patterns.
↑ Back to top