Forum Discussion
Mirrored Database - Delta Table Materialization Time
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
Super UserHi 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 helping
Hi 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!
v-dineshyaCommunity Support
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
- v-dineshya
Hi nikolakarakas95 ,
We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet. And, if you have any further query do let us know.
Regards,
Dinesh