With Fabric Mirroring, Microsoft is promoting a nice and appealing story for operational reporting build entirely in Fabric where you can have almost live data in reports.
The pitch is genuinely attractive. Your source database is mirrored into Fabric continuously, a semantic model sits directly on the mirrored data in Direct Lake mode, and your report shows near real-time data. The only meaningful delay is mirroring itself, which can be just a few minutes. No scheduled refresh, no import, no copy — it looks like operational reporting solved.
Operational Reporting Reality
The problem is demo architecture. In about 99% of cases mirrored data arrives in the shape we have in the source system, and that shape is almost never ready for reporting. You will need to do at least couple of minimal transformation before a semantic model is usable.
Examples:
- We have a datetime column, but you need a proper Date dimension to do anything serious with time intelligence. So we need to cast column to date.
- Developers of applications often like to use one JSON column and store details so they do not need to change table schema
- Multi column keys
There is as far as I know the only way you can do simple transformations on fly add Lakehouse in between Mirrored DB and Semantic Model.
So in practice you change the architecture to this:
In this version:
- You reference the mirrored data with shortcuts into a Lakehouse.
- You do your transformation in SQL views and functions over those shortcuts.
- You build the semantic model in Direct Query mode against the SQL analytics endpoint.
This trades some speed for flexibility — Direct Query is slower than Direct Lake — but it should still meet a low-latency requirement on operational data, and now you have a place to do simple transformation.
That works well… until you hit the SQL endpoint lag.
The trap: SQL endpoint lag
The SQL analytics endpoint is a separate layer that maintains its own catalog and cache over the Delta tables in OneLake. Keeping it in sync with the underlying data is a background process with real latency, so data can land in the lakehouse while the endpoint still returns stale (or missing) rows.
Two details make it worse than a fixed delay. The endpoint is serverless, so after a period of inactivity it spins down and the background refresh stops — the classic symptom is the first query of the morning returning yesterday’s data. And the refresh can simply stall: no error, no warning, just stale results. (Dennes Torres documented this behavior in detail, and the long-running “SQL endpoint lags behind lakehouse” thread on the Fabric community shows how widely it is felt.)
In a Direct Query model feeding a live report, this lag is visible directly to your users — which is exactly what the architecture was supposed to prevent.
Automated refresh from notebook is fine for batch pipelines where you can fire a refresh when ingestion finishes. However, it falls apart for continuous mirroring: there is no natural “end of ingestion” to trigger from.
New metadata sync (preview)
In May 2026 Microsoft announced a new metadata sync for the SQL analytics endpoint, currently in preview. It rearchitects the SQL Endpoint refresh process rather than tweaking the old job, and it targets exactly this freshness problem.
The key change for live reporting: alongside a periodic background refresh, it adds an on-demand refresh on read — when a query arrives and the data is not fresh, it refreshes then and there. The stated goal is data visible within seconds of landing. Under the hood it uses an external-tables approach to parse Delta logs and decouples schema-change detection from data-change detection.
Practical notes:
- It is opt-in at the workspace level (Workspace settings ? Warehouse settings) and applies only to newly created endpoints — existing ones are not retrofitted.
- You get a new DMV, dm_db_external_tables_log_status, exposing each table’s last-update time and the Delta log/checkpoint versions processed — so you can measure lag instead of guessing.
- You can refresh a single table for data-only changes:
EXEC sys.sp_dw_refresh_ext_table ‘dbo.publicholidays’;
So once again it’s available only on endpoints created after enabling the new sync!
Does it help? Measurably, yes
As it applies only to newly created Lakehouse it’s simple to do a test:
I tested two warehouses 1st created before enabling this feature in workspace. Second after enabling. The difference was visible: the lag was substantially shorter and far more predictable than the old background process. I hope this goes to GA soon and shal be by default enabled for all SQL Endpoints as SQL Lag is kind of embarrassing for fabric when explaining this to customers.
Takeaways
- Real reporting on top of mirrored data needs transformation which pushes you to a lakehouse + SQL endpoint + Direct Query design.
- That design exposes you to SQL endpoint lag — unpredictable, sometimes silent, and noticeable by users.
- The new metadata sync (preview, May 2026) adds on-demand-on-read refresh and measurably reduces and stabilizes the lag for this scenario. Opt-in, new endpoints only, test before you rely on it.
References:
- Dennes Torres, “SQL Endpoint Secrets you need to know” (Redgate Simple Talk)
- “SQL endpoint lags behind lakehouse” thread on the Microsoft Fabric Community;
- Microsoft Learn, “SQL analytics endpoint metadata sync.”
The post Fabric for Operational Reporting & SQL Endpoint Trap appeared first on Joyful Craftsmen.