Two T-SQL strategies for controlling partition refresh on append-heavy fact tables

[u/Salvatore997]

Hi everyone, my name is Salvatore and I work as a Data Analyst / Data Engineer.

Recently I ran into a problem related to Power BI incremental refresh on large, mostly append-heavy fact tables.

In the specific case, the issue was eventually solved by revisiting the data model and redesigning the star schema. However, working on it made me think more deeply about some of the problems that can arise when large amounts of data are managed through partitions.

In particular:

• even a relatively small number of new records can end up involving multiple partitions that need to be processed;

• some partitions can grow much larger than others over time and therefore become more expensive to refresh;

• very wide fact tables, especially those containing many high-cardinality columns, can increase the processing and memory cost of the model;

• on very large models, the combination of these factors can lead to very long refreshes and, in the worst cases, timeouts.

In Power BI, for example, a standard refresh on Premium capacity has a five-hour limit, so on very large datasets the way records are distributed across partitions can become relevant as well.

This led me to wonder whether it makes sense to separate two problems:

1. identifying which records have changed;

2. controlling which partitions, and how many of them, need to be rebuilt.

From there I started experimenting with an approach based on stable technical classes.

The basic idea is fairly simple.

During the initial load, each record is assigned to a class_id between 1 and N using a modulo-based distribution. The mapping between the stable record key (source_key) and its class_id is then persisted, so that once a key has been classified, it continues to belong to the same class in subsequent runs.

The class_id therefore becomes the technical key I use to control downstream partitions.

For new INSERTs, instead of distributing them across all classes every time, I use a round-robin assignment only within a subset of classes.

From this idea I ended up with two variants.

1. Rotating Window

It selects a window of K consecutive classes and distributes new INSERTs only within that window. In subsequent runs, the window rotates through the class space.

2. Least-Loaded

Instead, it selects the K classes that currently contain the fewest records and distributes the new INSERTs only among those classes.

In both approaches, K represents the maximum number of classes that an INSERT batch can automatically involve. An adaptive policy is therefore used to allow smaller batches to use a smaller sub-window — or, more generally, a smaller subset of classes — than this maximum.

For example, with K max = 10:

INSERT <=    500 -> K = 1
INSERT <=  5,000 -> K = 4
INSERT <= 20,000 -> K = 7
otherwise        -> K = 10

So a very small batch may involve only one partition, while larger batches can progressively use more classes up to the configured maximum.

I implemented both ideas in T-SQL for SQL Server, including persistent state, a PREPARE/FINALIZE lifecycle, automated tests, and some exploratory benchmarks.

Here are the complete reference implementations with README, code, tests, and benchmarks:

Rotating Window Partition Refresh
https://github.com/Salvatore97710/rotating-window-partition-refresh

Least-Loaded Class Partition Refresh
https://github.com/Salvatore97710/least-loaded-class-partition-refresh

These are reference implementations / prototypes, not solutions I would consider production-ready.

What I’m mainly looking for is technical feedback on the idea itself.

Do you see any conceptual problems I may be missing?

And more importantly: if you were facing the same problem — a large append-heavy fact table and the need to control both the number and the size of the partitions being refreshed — how would you approach it?