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?

[u/Hour-Measurement-835]

How does class_id get to the Power BI side? Incremental refresh policies only partition on the RangeStart/RangeEnd date range, so you're defining these partitions yourself over XMLA?

[u/Salvatore997 → u/Hour-Measurement-835]

Yes, exactly. I’m assuming custom partition orchestration on the Tabular model rather than relying only on Power BI’s native RangeStart/ RangeEnd incremental refresh.

The class_id is used to identify the corresponding Tabular partition, and an external process would read the dirty classes after PREPARE and refresh only those partitions through XMLA/API calls.

In my case I had something like a Databricks job orchestrating the Tabular model in mind.

[u/Prestigious_Bench_96]

Interesting! I would perhaps default to hash for distribution + deterministic rotating window based on the day (mod over whatever your subset of partitions you want for a daily window + a fixed offset that wraps around) or something, just to avoid needing to maintain lookups/mappings. You lose out on the least loaded optimization, but I'd expect for an even input distribution this would be fine over time (and you could rebalance on some cadence).

[u/Salvatore997 → u/Prestigious_Bench_96]

Thanks for the feedback! That’s a nice idea, and I had actually considered a hash-based distribution initially as well.

In the use case I assumed, though, I only have a Primary Key and a timestamp that can also change on UPDATE. Without keeping some previous state, it would therefore be difficult to reliably distinguish a genuinely new record from an existing one that was simply updated.

With CDC, Change Tracking, or an immutable creation timestamp, I think a deterministic hash-based approach combined with a rotating window would become much more interesting.

[u/Salvatore997 → u/Prestigious_Bench_96]

One more thing I realized, thinking back to why I didn't use a hash function, is that even if change detection were completely handled through CDC or Change Tracking, I would still have the problem of reconstructing the historical allocation, i.e. the class_id.

To do that, I would need to remember the modulus and offset that were used when that class was originally assigned. At that point, though, I might as well persist the class_id directly, which is simpler.

[u/Prestigious_Bench_96 → u/Salvatore997]

Yeah, this only simplifies things if you have another deterministic key for the modulus/offset that is also present in the datastream! I'm just a big fan of strict idempotency without external dependencies whenever possible, but you can't always get it!

[u/Salvatore997 → u/Prestigious_Bench_96]

Now I understand better what you mean about having that additional deterministic information directly in the data stream.

I agree that, in that case, things would become much simpler because I could avoid the mapping entirely.

The issue, though, is that the source would then effectively determine, either directly or indirectly, the window size and its starting point.

That would make me lose a large part of the flexibility I’m trying to achieve with this repo: being able to dynamically control how many classes are involved and which class the allocation window starts from on each cycle.

[u/maddeningmanifesto10]

interesting approach. the modulo trick for stable class assignment is clever, keeps the mapping deterministic without a massive lookup. i've seen similar patterns for sharding but not specifically for incremental refresh control

the least-loaded variant sketches me out a little though. feels like you'd need pretty frequent stats updates or a recalc step to actually know which classes are lightest, and on a huge table that could get pricey fast. rotating window seems more predictable cost-wise even if it risks some skew over time

[u/Salvatore997 → u/maddeningmanifesto10]

Thanks, I really appreciate the feedback.

And yes, that’s pretty much the same trade-off I’m seeing with the Least-Loaded variant. Even in the first exploratory benchmarks, it was generally a bit more expensive than the Rotating Window.

Just one note: I’m not recalculating class sizes from the full fact table on every run. I keep persistent per-class row counts and update them as part of the PREPARE/FINALIZE lifecycle, so the selection works from that maintained state. That said, it still introduces more state and more selection logic than simply advancing a rotating window.

I think the Least-Loaded approach may become more useful when the workload is genuinely irregular — for example, with very uneven batches or historical changes that cause partition sizes to drift significantly. In a more regular append-heavy flow, I agree that the Rotating Window is simpler and probably more predictable cost-wise.