Forum Discussion
Mirrored Database - Delta Table Materialization Time
Hi everyone,
I am using a 3rd party tool to load data from SAP into Microsoft Fabric. The destination in Fabric is a Mirrored Database, and the parquet files land in the Mirrored Database Landing Zone.
The table in question has 700 million rows and 50 columns. It is loaded in 8 parallel batches in approximately 1 hour, with each batch producing a single parquet file using GZIP compression at around 500 MB per file. (the compression type cannot be changed)
The concern arises during delta table materialization, which takes an additional 4 hours. When I increased the number of parallel batches to 24 (3x more), the materialization time only decreased by 1 hour, while introducing additional overhead in batch maintenance.
My assumption was that a larger number of smaller files with SNAPPY compression would significantly reduce the materialization time, since SNAPPY decompresses approximately 5 times faster than GZIP, but unfortunately that did not turn out to be the case. I ran additional tests loading 30 million rows using the following configurations:
1. 1 large parquet file – GZIP compression (~140 MB)
2. 1 large parquet file – SNAPPY compression (~180 MB)
3. 5 smaller parquet files – GZIP compression (3–15 MB each)
4. 5 smaller parquet files – SNAPPY compression (5–30 MB each)
5. 50 smaller parquet files – SNAPPY compression (1–5 MB each)
However, I did not observe any significant difference in delta table materialization time — all test cases completed in approximately 7 minutes.
Is a ~4 hour materialization time expected for a delta table of this size (700 million rows, 50 columns, 6 key columns)? If not, I would greatly appreciate any advice you may have on this topic.
Best regards,
Nikola
Hi, v-dineshya
this would still require manipulating _metadata.json, as that is the crucial file for mirroring to work. The keys need to exist in the Landing Zone regardless, as they are required for incremental loads and CDC is a must. COPY INTO would simply be a different way of dumping parquet files into the Landing Zone, but that has not been our issue anyway.
Not sure if I am reading you correctly, but if the suggestion is to perform the entire operation in the Lakehouse and simply dump data into the Mirrored Database, then having a Mirrored Database just for the sake of it loses its purpose entirely. What I genuinely appreciate about it is the way it handles deletions, combined with the fully automated UPSERT operation — which eliminates the need for an additional layer to manage that logic. On top of that, with CDF now in Public Preview, the complexity traditionally associated with the bronze layer becomes largely irrelevant.
For everyone else facing the same issue — the core problem is not the keys themselves, but the __rowMarker__ value in the landing parquet files. The issue with the software we use is that it always sets __rowMarker__ to 4 (Upsert). Unfortunately, there is currently no way to set __rowMarker__ to 0 (Insert), which would be the appropriate value for the initial load. This is documented in the Microsoft Documentation under Format Requirements here:
In our case, the simplest workaround is to remove the keys from _metadata.json before inital load and adding the keys after the inital load — this way we force the mirroring engine to perform inserts, since there are no keys available for merging. (I will also accept this as a solution.)
Best regards,
Nikola
10 Replies
Hi nikolakarakas95,
What you are seeing is consistent with how mirrored databases behave at this scale.Why compression and file size did not help
Delta table materialization is not driven only by file I/O. During materialization, Fabric:
-
Scans all parquet files
-
Validates and aligns schema
-
Writes Delta transaction logs
-
Reorganizes data into its internal optimized layout
At ~700M rows and 50 columns, this becomes compute and metadata heavy. Because of that:
-
SNAPPY vs GZIP shows little difference
-
Few large files vs many small files shows little difference
-
Increasing parallel batches gives diminishing returns
Your test results line up with this behavior.
Is ~4 hours expected?
For:
-
700M rows
-
50 columns
-
multi-batch ingestion into a mirrored database
Yes, this is within a reasonable range today. Especially when the system needs to consolidate multiple Parquet batches into a single Delta structure.
What actually drives materialization time
These factors matter more than compression:
-
Total volume processed in one cycle
-
Number of partitions created internally
-
Table width and key complexity
-
Delta write and optimization overhead
What you can try
1. Move to incremental loads
This gives the biggest impact.-
Load only new or changed data
-
Avoid large full refresh cycles
2. Keep file sizes in a balanced range
-
Aim for ~100 MB to 1 GB per file
-
Avoid too many small files
You are already close to this, so no major gains are expected here.
3. Reduce table width where possible
-
Drop unused columns before landing
-
Narrow tables materialize faster
4. Avoid over-parallelization
-
More batches add overhead
-
Gains are not linear
Your 8 vs 24 batch test already shows this.
5. Consider Lakehouse for heavy ingestion scenarios
If you need more control:
-
Land directly into Lakehouse Delta tables
-
Manage partitioning and optimization yourself
Mirrored databases favor simplicity over ingestion tuning.
Summary
-
Your observations are valid
-
Compression and file size are secondary
-
Materialization is compute and metadata bound
-
~4 hours for this workload is not unusual
Thank you!
Proud to be a Super User!
📩 Need more help?
✔️ Don’t forget to Accept as Solution if this guidance worked for you.
💛 Your Like motivates me to keep helpingHi MJParikh ,
Thank you for the comprehensive reply!
Unfortunately, this is what I was afraid of — that we've exhausted all possible options. Maybe a few things I forgot to mention:
- We are using F64 capacity
- The scenario I described with 700 million rows is only for the initial load — the daily load is exclusively incremental
- The 50 columns we are currently loading is already the most trimmed-down version of the table. The original table in SAP has 550 columns. In case a user requests an additional column with full history, we will have to run the entire initial load again (although there are a few other implementation approaches for that, which again only adds overhead to the process).
We would like to avoid a standard load into the Lakehouse for now, but if that turns out to be the only option with a significant gain, we might go down that path.
Thanks again!
Hi nikolakarakas95 ,
Thank you for reaching out to the Microsoft Community Forum.
Use Lakehouse only for initial loads and history rebuilds. This solves: Large initial load time, Future requests for additional historical columns, Repeatability, Full rehydration without touching SAP,
Better ingestion performance tuning. And you can Keep using Mirrored DB for everything else. Keep incremental ingestion exactly as-is. Avoid daily Lakehouse overhead.I hope this information helps. Please do let us know if you have any further queries.
Regards,
Dinesh
-
Hi v-dineshya ,
Thank you for your response. However, an initial load into a Lakehouse followed by CDC into a Mirrored Database is something that would not work in our case, as CDC can also delete and update records. This would then have to be handled with a third table into which the data would be merged, which again introduces data copying and additional effort.
One thing I came up with that speeds up the delta table materialization by 60% is to remove the keys from _metadata.json during the initial load. Namely, the Mirrored Database always operates in upsert mode and uses keys to merge data. When the keys are empty, the mirroring engine switches to append mode. Of course, after the initial load, the keys need to be restored to _metadata.json if we want upsert behavior.
Best regards,
NikolaHi nikolakarakas95 ,
Modifying _metadata.json is not officially supported and may break mirroring/Delta/OneLake features. Load without keys. After initial sync, add keys using SQL.
I hope this information helps. Please do let us know if you have any further queries.
Regards,
Dinesh
Hi v-dineshya ,
unfortunately, we are not able not to specify keys in the 3rd party software.
Also, I'm not sure what you meant by "add keys using SQL"? We are loading Parquet files into the landing zone, and _metadata.json is the only place where we can do something with keys. Where exactly would you run the SQL, and what would it look like?
Best regards,
Nikola
