The customer was in Canberra. The report still said Sydney. All we needed to do was correct January to March and leave the later months alone.
That ordinary request was at the heart of my earlier post on Materialized Lake Views. Fabric handled new rows arriving nicely, but correcting existing data could send the view back through a full rebuild. A small business correction had become a much larger processing job.
Microsoft’s preview for incremental updates and deletes gave me a reason to return to the same customer. Perhaps the engine could finally fix the affected sales and leave the rest alone.
This time, the correction worked. Fixing those sales also used more compute than rebuilding the view.
The Same Customer, a Different Refresh
I built a test with about a million sales and 10,000 customers. The view used each sale’s date to find the customer’s city at that time. A move later in the year should not quietly change where an earlier sale belongs.
For one customer, I corrected the history to Canberra for January–March and Sydney for April–June. I left the sales records alone.
After the refresh, all 27 affected sales were reported under Canberra. April’s sales stayed under Sydney. A fresh calculation across the whole dataset agreed with the saved result, and Fabric’s run records confirmed that the refresh was incremental.
That was what I wanted from the preview: fix the customer’s history and let this view carry the correction into the reports. I did not need a separate job rewriting those sales first.
If I had stopped at the incremental status, this could have been a very cheerful follow-up. But my earlier post had argued for making corrections smaller. I wanted to know what making this one smaller had actually saved.
Finding Those Sales Still Meant Reading the Year
Across six comparisons, full refresh finished sooner every time. Incremental’s median refresh time was about 27% longer, and its median compute consumption was about 35% higher.
The outputs were correct in both cases. The incremental result changed only 27 sales. So why was maintaining it taking more work?
The execution plans exposed the missing step in my reasoning. Fabric’s change feed could tell it which customer records had changed. It could not hand over a list of all the older sales whose reported city was now wrong. Fabric still had to find them.
In the plans I could inspect, that search read all 12 months of sales. Incremental then read three months of the existing view to apply the correction. Those 27 changed results were the outcome of a much larger search.
Both the sales table and the view were already partitioned by month. Partitioning was helping: the lookup in the existing view stopped at January–March. Finding the affected sales still involved reading the whole year.
I had been looking at how little changed in the answer. The engine also had to do the work of finding that answer. The plans made that work visible, although they could not account for every extra second or unit of compute.
Then I Left the Customer History Alone
At that point, I was wondering why I would bother with incremental at all. Maintaining 27 sales had produced the right result, but a full rebuild had been quicker and used less compute.
So I changed the experiment. This time, customer history stayed fixed and 100 new sales arrived in May.
Now Fabric already had the sales it needed to process. It could take those new rows, look up the customer’s city on each sale date, and keep the earlier matches. The customer history still had to be read, but the lookup no longer started with a year of sales.
That was substantial repeated work to avoid. Full refresh resolved the customer history for more than a million sales. Incremental resolved it for the 100 new ones.
This time, incremental finished sooner in all six comparisons. Its median refresh time was about 13% shorter. A modest win, but one I could connect to a clear difference in the work being done.
The customer history mattered in both experiments. What changed was where the work started. Correcting that history meant discovering its effect on old sales. Adding sales supplied the rows that needed processing directly.
The Monthly Total Kept Me Honest
The other tests stopped me from turning that result into a blanket recommendation. Simply loading new sales without the customer history lookup showed little timing benefit. Correcting a few existing sales was slower incrementally.
The monthly summary was particularly revealing. Only one monthly total needed updating, but the incremental plan recalculated May’s old and new totals, then updated that one result. Partitioning successfully limited those reads to May.
It still ran more slowly than a full refresh. Across the six comparisons, incremental also used 17.5% more combined compute. Changing one total had not made calculating the replacement cheap.
Even the successful new-sales case needed some perspective. Adding up its six refreshes, incremental used only 1% less compute overall. One run used enough extra compute to offset much of the saving from the others. It was consistently quicker in these tests, with little net compute saving.
That left me with a more useful distinction than the number of rows changed: how much repeated work could the refresh avoid, and what work did it add to maintain the result?
What Those Tiny Files Actually Told Me
Back in the customer history correction test, incremental created about 15 KB of new result files. Full refresh created about 143 MB. Those are strikingly different numbers for two refreshes producing the same answer.
I also had to be careful about what I was counting. Delta can keep an existing data file and record which rows should no longer be used. That file can appear again in the log. Adding its whole size to the total for new data would count an existing file as new.
I changed the notebook to retain file paths and separate new result files from reused ones. The 15 KB versus 143 MB comparison counts those new result files. It leaves out files tracking changes and deletions, temporary writes, and the work of reading the sources.
That is useful evidence about the saved result. It does not measure every read and write the refresh performed.
Compute came from a separate measurement: Fabric’s Capacity Metrics report, matched to the individual refreshes. Those figures cover MLV refresh compute. Test setup, source changes, validation and separate storage transaction charges sit outside that comparison.
Where This Leaves My Earlier Advice
In my earlier post, I argued for reducing how much history a correction needs to change. I still would. The preview now lets this view carry a correction through incrementally, which removes an important limitation from that earlier experiment.
What I would look at more closely now is the work left behind. Knowing that one customer changed is useful. Being able to find their affected sales cheaply is another part of the problem.
For new sales being matched to unchanged customer history, I would give incremental a serious look. It avoided useful work here. For historical corrections and monthly summaries, I would keep full refresh in the comparison until the measured time and compute gave me a reason to choose otherwise.
I started this follow-up hoping that fixing 27 sales would finally be a small job for Fabric. The customer is in the right city now. My assumption about what that small correction should cost did not survive the test.
Methodology and Scope
Each workload used six measured incremental/full pairs, with correctness and the actual refresh strategy checked independently. Warm-ups, setup and baseline refreshes were excluded from the comparisons. The full results and metric definitions are below.
Metrics measured and what they mean
The Capacity Metrics export attributes MLV refresh compute to the parent Lakehouse item. The exported operation name is Materialized Lake Views Run; Microsoft classifies this as a background operation.
| Item type | Operation type | Metric type | What it measures |
|---|---|---|---|
| Lakehouse | Materialized Lake Views Run Background | Compute consumption: Total CU (s) | Reported CU seconds for each matched MLV operation. Tables compare mode medians across six measured refreshes per mode. Combined CU is the sum across those six runs. |
| Lakehouse | Materialized Lake Views Run Background | Operation duration: Duration (s) | The interval reported by Capacity Metrics for the operation. Its boundaries differ from scheduler service duration and notebook waiting time. |
| Lakehouse | Materialized Lake Views Run Background | Throttling: Throttling (s) | The export’s reported throttling counter. All matched records reported zero; this does not establish that the capacity had no other workload pressure. |
Attribution: Each exported OperationId was matched exactly to a measured Fabric MLV job instance ID. The dimension-correction baseline has 12 final matches. The fact-change follow-up has 48 final matches out of 48 measured refreshes: all 24 pairs. Only final Billable, Stopped records enter CU comparisons. The operation ID is an attribution key, rather than a consumption metric.
Which CU field: The comparison uses Total CU (s). Timepoint CU (s) is the share assigned to the selected timepoint after smoothing and was not used as the run’s total consumption.
Additional experiment measurements
These measurements come from the scheduler, notebook, SQL profiles and Delta logs. Their operation labels describe the experiment’s work; they are not additional Capacity Metrics billing categories.
| Item / evidence source | Operation | Metric type | What it measures |
|---|---|---|---|
| MLV refresh / scheduler | Refresh | Service duration (seconds) | Scheduler-recorded start-to-end service interval. This is the duration used in the results tables; it is not isolated Spark CPU time. Percentage changes compare the two mode medians; paired wins and paired median savings are reported separately. |
| Notebook | SDK refresh request | Elapsed time (seconds) | Time from requesting refresh to SDK return, including client waiting. Retained as a diagnostic because submission order affected it. |
| MLV output Delta table | MERGE / full overwrite | New result Parquet bytes | Sizes of distinct newly added normal result Parquet paths. Excludes reused paths, CDC files, deletion-vector storage, temporary writes and source reads. |
| MLV output Delta table | MERGE / full overwrite | Result rows maintained | Source CDF, Delta operations, normal result-file records and an independent query establish the business delta: 27 corrected sales, 100 appended sales or one summary group, depending on the case. CDC-related operation counters are not counted as extra business facts. |
| Fact source / existing MLV output | Spark SQL scans | Files, partitions and rows | Read scope reported by identified scan operators in the available SQL profiles. Reused graph nodes are not summed as separate physical reads. File-size footprints are not a measurement of physical bytes fetched from OneLake. |
| MLV result | Refresh and validation | Observed policy / correctness | The actual refresh strategy and comparison against an independent full-query result. A completed refresh alone does not verify either. |
CU scope: The MLV compute comparison excludes controller notebook runs, source changes, validation, setup and baseline refreshes, and separate OneLake transaction operations.
Field definitions: Capacity Metrics timepoint operations and Fabric Spark operation classifications.
Experiment setup and full results
The fixture contained 1,000,100 sales, 10,000 customers and 20,000 physical SCD Type 2 dimension rows. Sales and result views were partitioned by month. Append trials produced 1,000,200 sales; update trials retained 1,000,100. The data was synthetic.
Customer history correction: six pairs
One dimension row was updated and one inserted to split the customer history. The fact source stayed unchanged, with no new commits or CDF records. The view restated 27 sales correctly. Both views received fresh full baselines before each measured correction. Mode order and physical view roles were balanced; first-mode order was grouped rather than interleaved.
| Metric | Incremental | Forced full |
|---|---|---|
| Median scheduler service duration | 47.59 s | 37.41 s |
| Median reported MLV compute | 721.99 CU s | 536.89 CU s |
| Combined reported MLV compute | 4,230.17 CU s | 3,383.42 CU s |
| New normal result Parquet file bytes | 15,210 | 143,476,703 |
| New normal result files | 3 | 12 |
Incremental had a 27.2% longer service-duration median and a 34.5% higher CU median. Combined CU was 25.0% higher. Full refresh was faster in all six pairs; incremental used more CU in five of six. All 12 outputs were correct, with verified refresh policies and final Capacity Metrics matches.
Available SQL profiles cover five incremental and six full refreshes. All showed a fact scan across 14 files and all 12 monthly partitions. Incremental also scanned three output partitions containing 270,000 rows. The changed dimension data was broadcast into the fact join; no customer/date/month restriction narrowed the fact scan in these plans. The scan evidence does not attribute every second or CU to an operator.
Fact changes with customer history held fixed: six pairs per case
Each pair restored the same source snapshot, fully refreshed both outputs, then applied one shared mutation. Source CDF contained exactly 100 inserts, or 27 update preimages and 27 postimages. Customer history was unchanged. First-mode order alternated, and both physical views received incremental mode three times. All 48 measured refreshes were correct: 24 verified incremental and 24 verified full, with no fallbacks. Final CU records cover all 24 pairs.
| Workload | Incremental median service time | Full median service time | Incremental faster |
|---|---|---|---|
| Add 100 sales: direct detail view | 31.84 s | 31.78 s | 3/6 |
| Update 27 sales: direct detail view | 37.97 s | 32.35 s | 0/6 |
| Add 100 sales: customer history lookup | 32.16 s | 36.88 s | 6/6 |
| Update 27 sales: monthly summary | 42.71 s | 31.92 s | 0/6 |
| Workload | Incremental median CU s | Full median CU s | Combined CU change | Incremental uses less CU |
|---|---|---|---|---|
| Direct detail: new sales | 570.44 | 609.46 | −6.6% | 3/6 |
| Direct detail: corrected sales | 582.81 | 587.07 | −0.7% | 3/6 |
| New sales + customer history | 538.34 | 590.59 | −1.0% | 4/6 |
| Monthly summary correction | 721.21 | 652.40 | +17.5% | 1/6 |
The customer history lookup had a 12.8% lower service-duration median. The median within-pair saving was 2.37 seconds; the difference between the two mode medians was 4.71 seconds. Its CU median was 8.8% lower, but combined compute was 3,568.76 versus 3,605.33 CU seconds: only 1.0% lower. The summary had a 33.8% longer service-duration median, a 10.5% higher CU median, and combined compute of 4,612.25 versus 3,923.86 CU seconds.
The append lookup read the 100 new fact rows through CdcAddFileIndex and joined them to the unchanged 20,000-row dimension, scanned across eight files. Full refresh scanned all 12 fact partitions. All six SQL profiles per mode are available for this case. Repeated graph nodes may reuse scans and are not counted as independent physical reads.
The monthly summary plan read two fact snapshots restricted to May, approximately 80,100 logical rows each, before merging one changed output group. Scan counters showed 80,100 and 80,127 physical positions, with deletion filtering explaining the difference. The full result contained 12 groups. The follow-up has 42 usable measured SQL profiles out of 48 refreshes.
Query definition and limits
The customer history lookup used an INNER JOIN and a half-open validity interval:
FROM facts f
INNER JOIN customer_history d
ON f.customer_id = d.customer_id
AND f.sale_date >= d.valid_from
AND f.sale_date < d.valid_toA join to a dimension surrogate key already stored in the sale has different restatement semantics: it does not automatically reinterpret sale dates when customer validity ranges change. Earlier eligibility tests used incremental refresh for this INNER JOIN correction, while a LEFT JOIN used full refresh. Microsoft documents full refresh when the right side of a LEFT JOIN changes. Join semantics must follow the business requirement.
CDF was enabled on all sources. Detail and customer history views declared a unique sale key; the summary declared its grouping keys. The preview deletion-vector configuration was enabled before view creation. Fabric trusts the declared uniqueness, so keys were checked independently.
New result-file bytes count distinct newly added normal Parquet paths. Summing Delta add.size alone can include reused paths whose deletion-vector metadata changed. This does not prove that their Parquet data was rewritten. The Delta protocol defines the underlying file actions. CDC files, deletion-vector storage, temporary writes and source reads are excluded from the reported new-file metric.
Timing uses scheduler-recorded service intervals. SDK return time and Capacity Metrics operation duration have different boundaries; none is isolated Spark CPU time. Whole-refresh Spark task accounting remained incomplete. Exact matches from Fabric job IDs to final Capacity Metrics operation IDs independently establish the reported MLV compute figures.
These comparisons started from fresh baselines before each pair. They do not measure file-layout changes after a long stream of incremental refreshes. Capacity SKU and concurrent workload conditions were not recorded. The correction and append experiments show different plans under their respective conditions; they do not isolate the changing source as the sole cause of a cross-run timing difference. These are descriptive results for this fixture, not statistical significance claims or a benchmark against Azure SQL.