Forum Discussion
Delta Table Maintenance for Mirrored DB Tables
Hello,
We have a Lakehouse set up with some regular delta tables as well as shortcuts to tables in a mirrored DB (using CDC from on-prem SQL server). I know there are delta table maintenance tasks which should be performed to help optimize read/write operations and remove old data (OPTIMIZE/VORDER/VACUUM/PURGE). I am able to successfully run these delta table maintenance tasks from within the Lakehouse UI and from within a notebook (see code below), but only for the regular (i.e. non-mirrored) tables in the Lakehouse.
try:
df = spark.sql("OPTIMIZE [LAKEHOUSE_NAME].[SCHEMA_NAME].[TABLE_NAME]")
except Exception as e:
print(f"Error running OPTIMIZE: {e}")
m = df.toPandas().at[0,'metrics']
print("OPTIMIZE metrics:")
for key, value in m.items():
print(f" {key}: {value}")
When running the maintenance tasks for the mirrored tables from the Lakehouse UI, I get a permission-related error:
Job failed during run time with state=[dead]. TSG:An operation with ADLS Gen2 has failed. This is typically due to a permissions issue. 1. Please ensure that for all ADLS Gen2 resources referenced in the Spark job, that the user running the code has RBAC roles "Storage Blob Data Contributor" on storage accounts the job is expected to read and write from. 2. Check the logs for this Spark application. Inspect the logs for the ADLS Gen2 storage account name that is experiencing this issue.When running the optimize command from a pyspark notebook, it doesn't return errors but it doesn't actually perform any compaction. I've run it using [LAKEHOUSE_NAME].[SCHEMA_NAME].[TABLE_NAME] as well as [MIRROR_NAME].[SCHEMA_NAME].[TABLE_NAME] and get the same metrics:
numFilesAdded: 0
numFilesRemoved: 0
...
totalConsideredFiles: 1
totalFilesSkipped: 1I haven't tried using the Lakehouse Maintenance pipeline activity as it doesn't support maintenance on Lakehouses with schemas enabled (which our Lakehouse does).
It would make sense if the parquet files for mirrored DB are read-only to maintain the mirrored data. If this is the case and table maintenance can't be performed by users, is it automatically handled by the system? Or is table maintenance for mirrored tables not supported at all?
Hi JuliaMeneley ,
Short answer:
I would not treat mirrored tables the same way as regular Lakehouse Delta tables for manual maintenance.For regular Lakehouse Delta tables, Fabric supports maintenance operations such as OPTIMIZE, V-Order and VACUUM.
However, for mirrored tables, I would treat the mirrored output as Fabric-managed unless Microsoft confirms otherwise.
Microsoft documentation for Lakehouse table maintenance:
https://learn.microsoft.com/en-us/fabric/data-engineering/lakehouse-table-maintenanceWhy this likely happens:
Mirroring is a managed replication feature. Microsoft describes Mirroring as continuously replicating data into OneLake and automatically keeping the data in sync. That makes the mirrored table different from a normal Delta table that your Lakehouse owns and writes to directly.Microsoft documentation on Mirroring:
https://learn.microsoft.com/en-us/fabric/mirroring/overviewAlso, if you are accessing the mirrored table through a Lakehouse shortcut, the shortcut is just a reference to data without copying it. So the table appears in the Lakehouse, but the underlying files are not physically owned by that Lakehouse.
Microsoft documentation on Lakehouse shortcuts:
https://learn.microsoft.com/en-us/fabric/data-engineering/lakehouse-shortcutsMaintenance commands such as OPTIMIZE and VACUUM are not just read operations. OPTIMIZE compacts/reorganizes Delta files, and VACUUM removes obsolete files that are no longer referenced by the Delta log. So they require write/delete access to the underlying Delta storage.
Your result also seems consistent with this:
numFilesAdded: 0 numFilesRemoved: 0 totalFilesSkipped: 1
That suggests Spark did not compact or rewrite the mirrored table files.
What I would do:
If you need full control over table maintenance, I would create a downstream curated Delta table:Mirrored table → read from it → write to your own Delta table in the Lakehouse → run OPTIMIZE / VORDER / VACUUM on that curated table
Practical rule:
- Regular Lakehouse Delta tables: maintain them yourself.
- Mirrored tables / shortcuts: treat them as Fabric-managed or source-owned.
- If you need custom optimization: create a curated Delta copy and maintain that.
Best regards,
Solutions Architect · Microsoft Fabric Specialist · Parchitect💡Did my response help you? Clicking Kudos is a small gesture that goes a long way, it encourages contributors and helps the community thrive!
✔️Did I answer your question? Please mark my post as a Solution, it helps others find the answer faster.
3 Replies
Hi JuliaMeneley ,
Short answer:
I would not treat mirrored tables the same way as regular Lakehouse Delta tables for manual maintenance.For regular Lakehouse Delta tables, Fabric supports maintenance operations such as OPTIMIZE, V-Order and VACUUM.
However, for mirrored tables, I would treat the mirrored output as Fabric-managed unless Microsoft confirms otherwise.
Microsoft documentation for Lakehouse table maintenance:
https://learn.microsoft.com/en-us/fabric/data-engineering/lakehouse-table-maintenanceWhy this likely happens:
Mirroring is a managed replication feature. Microsoft describes Mirroring as continuously replicating data into OneLake and automatically keeping the data in sync. That makes the mirrored table different from a normal Delta table that your Lakehouse owns and writes to directly.Microsoft documentation on Mirroring:
https://learn.microsoft.com/en-us/fabric/mirroring/overviewAlso, if you are accessing the mirrored table through a Lakehouse shortcut, the shortcut is just a reference to data without copying it. So the table appears in the Lakehouse, but the underlying files are not physically owned by that Lakehouse.
Microsoft documentation on Lakehouse shortcuts:
https://learn.microsoft.com/en-us/fabric/data-engineering/lakehouse-shortcutsMaintenance commands such as OPTIMIZE and VACUUM are not just read operations. OPTIMIZE compacts/reorganizes Delta files, and VACUUM removes obsolete files that are no longer referenced by the Delta log. So they require write/delete access to the underlying Delta storage.
Your result also seems consistent with this:
numFilesAdded: 0 numFilesRemoved: 0 totalFilesSkipped: 1
That suggests Spark did not compact or rewrite the mirrored table files.
What I would do:
If you need full control over table maintenance, I would create a downstream curated Delta table:Mirrored table → read from it → write to your own Delta table in the Lakehouse → run OPTIMIZE / VORDER / VACUUM on that curated table
Practical rule:
- Regular Lakehouse Delta tables: maintain them yourself.
- Mirrored tables / shortcuts: treat them as Fabric-managed or source-owned.
- If you need custom optimization: create a curated Delta copy and maintain that.
Best regards,
Solutions Architect · Microsoft Fabric Specialist · Parchitect💡Did my response help you? Clicking Kudos is a small gesture that goes a long way, it encourages contributors and helps the community thrive!
✔️Did I answer your question? Please mark my post as a Solution, it helps others find the answer faster.Hi JuliaMeneley I would agree with Parchitect (don't treat mirrored delta table the same as normal delta table in lakehouse, if use curated delta delta table i.e. copy it over to your lakehouse physically) and would like to add further.
I would suggest you also focus on the source system health (on prem SQL server) and optimise it by ensuring clean indexes and optimizes SQL queries (and etc) as the mirrored tables will reflects upstream structure, you may read optimization techniques from this good blog:
MS SQL Server Performance Optimization: Best Practices & Tips
Appreciate if you can 'Kudos' and/or 'Accept as Solution' if this answered your query.
Parchitect Thanks for the explanation.
