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
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,
Nikola
Community SupportHi 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
- v-dineshya
Hi nikolakarakas95 ,
Use Lakehouse as a staging area (the “escape hatch”), it will fix the issue.
Please follow below steps.
1. Ingest raw Parquet --> Lakehouse.
2. Use COPY INTO Mirrored Database.
3. Define your own keys on the Lakehouse side.
4. Rebuild whenever you want.
5. Never reload 700M rows from SAP againI hope this information helps. Please do let us know if you have any further queries.
Regards,
Dinesh
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