In this post, I am going to look at automatic partition maintenance by Power BI service for datasets with Incremental Refresh and focus on what happens to the partitions as time goes by. To do this, I am going to set up a couple of sample datasets with different Incremental Refresh (IR) policies with and without the Hybrid option, schedule automatic refreshes from the Power BI Service, and record how their partitions change over time.

If you need to learn about Incremental Refresh itself, I made a short video on this topic here: https://youtu.be/CDJ3s4v7Fsg. Here is the official post from Microsoft: Incremental refresh for datasets and real-time data in Power BI – Power BI | Microsoft Learn.

Background

When you set up Incremental Refresh (IR) (with or without the Hybrid (H) option) from Power BI Desktop and publish the dataset to the Power BI Service, the service will take care of the initial partition creation, and then it maintains the partitions for you over time.

The first refresh from Power BI Service* creates the partitions based on the refresh policy you define in Power BI Desktop. From that point on, with every subsequent refresh, the service will maintain the partitions as time goes by; meaning it will do the following operations as needed and when the opportunity arrives:

• Create new partitions
• Roll the Incremental Refresh window (update what data gets refreshed/pulled into Power BI)
• Merge smaller partitions into bigger ones (3 months into a Quarter)
• Retire old partitions (outside of the archive window)

This automatic partition maintenance is a tremendous help. Of course, you can create custom partitions for more complex scenarios yourself; however, if you can use what Power BI offers out of the box, it will save you a lot of time and headache since you won’t’ have to manage the partitions yourself. Some of you may remember that this had to be done manually or by writing special code when working with Analysis Services (AS) cubes/models.

*Note: The first refresh can also be triggered by using other methods such as doing a ProcessFull from SQL Server Management Studio (SSMS) or by using Tabular Editor to apply the refresh policy followed by a ProcessFull from SSMS; however, keep in mind that at the time of writing this post, neither of the above methods generate the DirectQuery partition if you are using the Hybrid option.

Baseline

I published the following datasets to the service on the dates indicated in the file names.

On Oct 31, I decided to add one more file to the mix:

Each file name shows the Incremental Refresh Policy that was used. “IR” indicates Incremental Refresh was enabled for the dataset. “IRH” indicates that the Hybrid option was enabled as well.

For example, for “Hybrid Tables Demo 7 – Last 5 Years Last 1 Day IRH Published Oct 29.pbix”, the incremental refresh policy was set to archive last 5 years, incrementally refresh 1 day, and the Hybrid option (Direct Query) was enabled for the last day.

Note: Any deployment after the initial deployment will wipe out any automatic partitions created from Power BI Service and the data as well; thus, the deployment was done only once from Power BI Desktop. If you need to make any changes to Power BI datasets after enabling Incremental Refresh for them, you must use other methods such as using the ALM Toolkit to only apply metadata changes.

Starting point on Oct 30, 2022

At this point, each dataset has had at least one refresh from the service. For each dataset, the following shows the Incremental Refresh Policy as defined in Power BI Desktop and the list of partitions as shown by Tabular Editor. I will update the images of partitions as the days roll on. You will notice that on some days I have posted results after a first and a second refresh. This is because as you will see it is the second refresh that merges old smaller partitions into a bigger one and there is a slight difference in behavior for datasets with the Hybrid option enabled.

Last updated Feb 11, 2023

Hybrid Tables Demo 7 – Last 5 Years Last 1 Day IRH Published Oct 29, 2022

Incremental Refresh Policy:

Archive Last 5 years before refresh date, incrementally refresh 1 day before refresh date, get data for the refresh date with Direct Query (Hybrid option)

Partitions:

Oct 30, 2022:

Oct 31, 2022:

A new DirectQuery partition for Oct 31st was created. The partition for Oct 30st was switched from DirectQuery to Import.

Nov 01, 2022:

First refresh: A new DirectQuery partition for Nov 1st was created. The partition for Oct 31st was switched from DirectQuery to Import.

Second refresh: No changes since Power BI chose not to merge days into a month. For some datasets further down in this post, the 2nd refresh will merge partitions.

Nov 02, 2022

First Refresh: A new DirectQuery partition for Nov 02 was created. The partition for Nov 01 was switched from DirectQuery to Import.

Second Refresh: Not captured.

Nov 03, 2022:

First Refresh: A new partition is added for Nov 03. The partition for Nov 02 is switched from DirectQuery to Import. Daily partitions for Oct are merged into a monthly partition for Oct.

Second Refresh: No change.

Nov 04, 2022:

First Refresh: A new partition is added for Nov 04. The partition for Nove 03 is switched from DirectQuery to Import.

Second Refresh: No change.

Nov 05 – Nov 30, 2022

During this time, we expect a new daily partition for each day be added in DirectQuery. There is nothing much more exciting happening as far as partition merges or old partition drops so I will not post daily images.

Feb 11 , 2023

All the 2022 partitions have been merged into one for year 2022. Jan 2023 has its own partition and there are daily partitions for Feb. On Feb 11th, the last two partitions (Feb 10 and 9) have been updated.

If you look at the partitions from SSMS, you will notice that on Feb 11, the data for Feb 11 and 10th was both read. This is to ensure that we have all of the data for Feb 10 if there have been changes since the last refresh on Feb 10 in the middle of the morning.

---

Hybrid Tables Demo 7b – Last 5 Years Last 1 Day IR Published Oct 30, 2022

Incremental Refresh Policy:

Archive Last 5 years before refresh date, incrementally refresh the refresh date

Partitions:

Oct 30, 2022:

Oct 31, 2022:

A new partition for Oct 31st was created.

Nov 01, 2022:

First Refresh: A new partition for Nov 01, 2022, was added but the daily partitions for Oct did not merge into a monthly partition. This will happen in the 2nd refresh.

Second Refresh:

Partitions merged! This time Power BI Service merged the daily partitions for the month of October into one partition for the entire month! Note, how this is different than the merging behavior for the previous dataset with IR with the Hybrid option. In that dataset, even the 2nd Refresh did not result in daily partitions merging into a month partition.

Nov 02, 2022:

First Refresh: A new partition for Nov 2 was added.

Second Refresh: No change.

Nov 03, 2022:

First Refresh: A new partition is added for Nov 03.

Second Refresh: No change.

Nov 03, 2022:

First Refresh: A new partition is added for Nov 04.

Second Refresh: No change.

Nov 05 – Nov 30, 2022

During this time, we expect a new daily partition for each day be added in Import. There is nothing much more exciting happening as far as partition merges or old partition drops so I will not post daily images.

Jan 01, 2023

The automatic refresh was last run on this date and then had been disabled due to inactivity.

If you look at the partitions from SSMS, it is very interesting to look at the time stamps for last processed date. On Jan 1st, 2023, the data for Dec 31, 2022, was read as well. This trend is true for all the previous days as well.

Feb 11, 2023

This dataset’s automatic refresh had stopped so when I triggered a manual refresh, as you can see from SSMS, Power BI created all the new daily partitions for Jan and Feb 2023.

Second Refresh on Feb 11, 2023

As expected, the 2nd refresh merged all of 2022 partitions into one and created one partition for Jan and only refreshed the one for Feb 11th.

---

Hybrid Tables Demo 8 – Last 5 Years Last 1 Month IRH Published Oct 30, 2022

Incremental Refresh Policy:

Archive Last 5 years before refresh date, incrementally refresh 1 month before refresh date, get data for the month that the refresh date falls in with Direct Query (Hybrid option)

Partitions:

Oct 30, 2022:

Oct 31, 2022:

No changes.

Nov 01, 2022:

First Refresh:

A new partition for Nov was created. The partition for Oct changed from DirectQuery to Import.

Second Refresh: No changes.

Nov 02, 2022:

First Refresh: No changes.

Second Refresh: No changes.

Nov 03, 2022:

First Refresh: No change.

Second Refresh: No change.

Nov 04, 2022:

First Refresh: No change.

Second Refresh: No change.

Nov 05 – Nov 30, 2022

During this time, we don’t expect any change so I will not post daily images.

Jan 6, 2023

The automatic refresh was last run on this date and then had been disabled due to inactivity. There is a Direct Query partition for Jan 2023 but the data for 2022 has not been merged yet.

Feb 11, 2023

This was the first manual refresh which activated the automatic refresh again.

Feb 11, 2023 : Second refresh

---

Hybrid Tables Demo 8b – Last 5 Years Last 1 Month IR Published Oct 30, 2022

Incremental Refresh Policy:

Archive Last 5 years before refresh date, incrementally refresh the month that refresh date falls under

Partitions:

Oct 30, 2022:

Oct 31, 2022:

No changes.

Nov 01, 2022:

First Refresh:

A new partition for Nov was created.

Second Refresh: No changes.

Nov 02, 2022:

First Refresh:

Second Refresh: No change.

Nov 03, 2022:

First Refresh: No change.

Second Refresh: No change.

Nov 04, 2022:

First Refresh: No change.

Second Refresh: No change.

Nov 05 – Nov 30, 2022

During this time, we don’t expect any change so I will not post daily images.

Jan 01, 2023

The automatic refresh was last run on this date and then had been disabled due to inactivity.

Feb 11, 2023

This was the first manual refresh which activated the automatic refresh again.

Feb 11, 2023: Second Refresh

Partitions for 2022 merged into one.

---

Hybrid Tables Demo 9 – Last 3 Months Last 1 Month IRH Published Oct 30, 2022

Incremental Refresh Policy:

Archive last 3 months before refresh date, incrementally refresh 1 month before refresh date, get data for the month that the refresh date falls under with Direct Query (Hybrid option)

Partitions:

Oct 30, 2022:

Oct 31, 2022:

No changes.

Nov 01, 2022:

First Refresh:

A new partition for Nov is created in DirectQuery. The partition for Oct is switched from DirectQuery to Import.

The partition for Jun is dropped off!

Second Refresh: No changes.

Nov 02, 2022:

First Refresh: No change.

Second Refresh: No change.

Nov 03, 2022:

First Refresh: No change.

Second Refresh: No change.

Nov 04, 2022:

First Refresh: No change.

Second Refresh: No change.

Nov 05 – Nov 30, 2022

During this time, we don’t expect any change so I will not post daily images.

Jan 10, 2023

The automatic refresh was last run on this date and then had been disabled due to inactivity.

Feb 11, 2023

This was the first manual refresh which activated the automatic refresh again.

Second refresh:

---

Hybrid Tables Demo 9b – Last 3 Months Last 1 Month IR Published Oct 30, 2022

Incremental Refresh Policy:

Archive last 3 months before refresh date, incrementally refresh the month that refresh date falls under

Partitions:

Oct 30, 2022:

Oct 31, 2022:

No changes.

Nov 01, 2022:

First Refresh: A new partition for Nov is created. The partition for Oct is dropped off.

Second Refresh: No changes.

Nov 02, 2022:

First Refresh:

Second Refresh: No change.

Nov 03, 2022:

First Refresh: No change.

Second Refresh: No change.

Nov 04, 2022:

First Refresh: No change.

Second Refresh: No change.

Nov 05 – Nov 30, 2022

During this time, we don’t expect any change so I will not post daily images.

Jan 10, 2023

The automatic refresh was last run on this date and then had been disabled due to inactivity.

Feb 11, 2023

This was the first manual refresh which activated the automatic refresh again.

Second Refresh

---

Hybrid Tables Demo 6 – Last 3 Months Last 2 Days IR Published Oct 31, 2022

Incremental Refresh Policy:

Partitions:

Oct 31, 2022:

Nov 01, 2022:

First Refresh: A new partition for Nov 1st is added. The partition for Oc 31st it changed into Import from DirectQuery.

Second Refresh: No changes.

Nov 02, 2022:

First Refresh: A new partition for Nov 02 is added. The partition for Nov 01 is changed into Import from DirectQuery. The daily partitions for October have not merged into a monthly partition yet.

Second Refresh: No changes.

Nov 03, 2022:

First Refresh: A new partition for Nov 03 is added in DirectQuery. The partition for Nov 02 is switched to Import from DirectQuery. Note that daily partitions for Oct are not merged into one monthly partition yet!

Second Refresh: The daily partitions for Oct are merged into a monthly partition for Oct.

Nov 04, 2022:

First Refresh:

A new partition for Nov 04 is added. The partition for Nov 03 is changed into Import from DirectQuery.

Second Refresh: No change.

Nov 04, 2022:

First Refresh: A new partition was added for Nov 04 in Direct Query. The partition for Nov 03 changed from DirectQuery to Import.

Second Refresh: No change.

Nov 05 – Nov 30, 2022

During this time, we don’t expect any change so I will not post daily images.

Jan 5, 2023

The automatic refresh was last run on this date and then had been disabled due to inactivity.

Feb 11, 2023

This was the first manual refresh which activated the automatic refresh again.

Second Refresh:

---

Hybrid Tables Demo 6b – Last 3 Months Last 2 Days IR Published Oct 31, 2022

Incremental Refresh Policy:

Partitions:

Oct 31, 2022:

Nov 01, 2022:

First Refresh: A new partition for Nov 1st is added.

Second Refresh: No changes.

Nov 02, 2022:

First Refresh: A new partition is added for Nov 02. The daily partitions for October have not merged into a monthly partition yet.

Second Refresh: A new partitions is added for Nov 02. The daily partitions for October are merged into a monthly partition for October. Nothing is dropped off.

Nov 03, 2022:

First Refresh: A new partition is added for Nov 03.

Second Refresh: No change.

Nov 04, 2022:

First Refresh: A new partition is added for Nov 04.

Second Refresh: No change.

Nov 04, 2022:

First Refresh: A new partition was added for Nov 04.

Second Refresh: No change.

Nov 05 – Nov 30, 2022

During this time, we don’t expect any change so I will not post daily images.

Feb 11,2023: This was the first manual refresh which activated the automatic refresh again.

Second Refresh:

---

Last Update to this blog post

I documented how Power BI Service generated new partitions, how it merged existing ones into bigger ones as it got a chance, and how it dropped old partitions off as they rolled outside of the archive window. The last update to this post was on Feb 11, 2023. At this point, we have witnessed all possible scenarios when a new day/month/quarte/year rolls in.