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
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
- v-dineshya
Hi nikolakarakas95 ,
Thanks for the update. We are happy to hear that you have resolved the issue. Thanks for sharing the details here. It will help others with similar problems find it easily.
Thank you.