Targets

Fabric Open Mirroring

Mirror captured tables into Microsoft Fabric by writing Open Mirroring Parquet files to an open mirrored database's landing zone.

A Fabric Open Mirroring target writes the captured tables as Parquet files into the landing zone of an open mirrored database in Microsoft Fabric, and Fabric ingests them into Delta tables in OneLake. The target is called Fabric Mirror in the wizard. For the SQL database in Fabric, which SQL Flow writes through its SQL endpoint instead, see SQL database in Fabric.

Requirements

Requirement	Details
Mirrored database	An open mirrored database in Fabric, whose source type is `GenericMirror`. Another kind of item, or a mirrored database that Fabric replicates itself, is refused.
Mirroring status	`Running`. Test connection offers Start mirroring when mirroring is `Initialized` or `Stopped`.
Landing zone URL	Copied from the mirrored database's Home page in Fabric: `https://onelake.dfs.fabric.microsoft.com/<workspace ID>/<mirrored database ID>/Files/LandingZone`. It must use the IDs, not the names, so renaming either in Fabric never breaks the pipeline.
Identity	A Microsoft Entra service principal: its tenant ID, client ID and client secret
Primary keys	Every captured table needs a primary key on the source. A table without one stops the pipeline at startup.
Network	The agent that applies the pipeline reaches `onelake.dfs.fabric.microsoft.com` and `api.fabric.microsoft.com` over HTTPS.
Sources	Every source and capture mode.

Permissions

The service principal needs:

The workspace role Contributor, or higher, on the mirrored database's workspace.
The Fabric tenant setting "Service principals can use Fabric APIs" enabled for it. A Fabric administrator sets it in the Admin portal's tenant settings, under Developer settings, where it is titled "Service principals can call Fabric public APIs".

Test connection checks that the URL names an open mirrored database, that mirroring is running, that the service principal can write to the landing zone, and that the selected tables' folders are free or already belong to this pipeline. It stops at the first check that fails, with a red alert that names it. When mirroring is Initialized or Stopped, the alert carries a Start mirroring button, which starts mirroring and runs the test again. SQL Flow never starts mirroring unless you click it.

What SQL Flow creates on the target

SQL Flow writes files into the landing zone. It doesn't create the mirrored database or change its settings.

Path	Contents
`<table>/`	One folder per table in the source's default schema (`dbo` or `public`)
`<schema>.schema/<table>/`	One folder per table in any other schema
Files in each table folder	`_metadata.json` (the table's key columns), `_sqlflow_owner.json` (marks the folder as owned by this pipeline), and Parquet data files numbered `00000000000000000001.parquet`, `…2.parquet` and so on

SQL Flow also writes _partnerEvents.json once at the landing zone root. Inserts and updates are written as Open Mirroring upserts and deletes as deletes, so Fabric's tables converge on the source even after a batch is replayed.

On the agent, a work directory under the agent's data directory holds the files waiting to be published. Keep the agent's data directory across restarts.

Options

Field	Option key	Values	Default
Target	`target`	OneLake (default) (`onelake`), Local folder (`local`)	`onelake`
Landing zone URL	`landingZoneUrl`	The URL from the mirrored database's Home page	Required for OneLake
Microsoft Entra tenant ID	`tenantId`	The service principal's tenant GUID	Required by Test connection
Client ID	`clientId`	The service principal's application (client) GUID	Required by Test connection
Password	The connection's password or secret reference	The service principal's client secret	Required for OneLake
Local path	`localPath`	A folder on the agent host; shown with Local folder	Required for a local folder
Work directory	`workDir`	A folder on the agent host; shown with Local folder	A folder for the pipeline under the agent's data directory

With Local folder, the agent writes the same landing zone layout to a folder on its own host instead of OneLake, and the connection carries no credential.

These options are set with the sqlflow CLI (--opt <key>=<value>):

Option key	What it does	Default
`strictUpdate`	`true` writes an `UPDATE` as an Open Mirroring update (`__rowMarker__` `1`) instead of an upsert (`4`)	`false`
`publishIntervalMs`	How often change files are published, in milliseconds. A change can wait up to this long before it is published.	`5000`
`publishMaxRows`	Most rows in one change file	`100000`
`publishMaxBytes`	Most bytes in one change file	64 MiB
`maxValueBytes`	Largest string or binary value a file may carry; a larger value stops the pipeline	`16000000`
`tableStatusPollSeconds`	How often the agent asks Fabric for each table's mirroring status; `0` turns it off	`60`
`tableStatusFailAfter`	Consecutive checks that report a table in error before the pipeline stops	`2`
`restartTables`, `restartEpoch`	Re-seed the listed tables after you delete their landing zone folders. See Re-seed one table.	None

Re-seed one table

Published files are never rewritten. To write one table again from scratch, for example after Fabric reports it in error, restart that table's folder:

Pause the pipeline.
In OneLake, delete the table's landing zone folder: <table>/, or <schema>.schema/<table>/ for a table outside the default schema.
Wait until the table disappears from the mirrored database in Fabric.
Add restartTables and a new restartEpoch to the pipeline's target. restartTables lists tracked tables as you selected them, separated by commas; restartEpoch is any value you haven't used for this table before. A pipeline keeps its own copy of its target connection, so update the saved connection, then copy it to the pipeline, giving the client secret again. Replace <connection>, <pipeline>, <schema.table>, <id> and <NAME>.
```
sqlflow connection edit <connection> --opt restartTables=<schema.table> --opt restartEpoch=<id>
sqlflow pipeline set-connection <pipeline> --which target --from <connection> --password-env <NAME>
```
Start the pipeline. The table's next files go into the new folder.
Resync the table, so its existing rows are written to the new folder.

Each restartEpoch value restarts a table once; later starts with the same value leave the table alone. If the folder still holds files, the start stops with restart '<id>' of table '<table>' needs its landing folder '<folder>' deleted first, but it still holds <files>. Delete the folder, wait until the table disappears from the mirrored database, then start again; nothing was reset.

Limitations

Every captured table needs a primary key on the source.
A TRUNCATE captured from the source stops the pipeline: Open Mirroring has no __rowMarker__ that expresses a TRUNCATE, so a truncate cannot be forwarded to a Fabric mirrored table. Apply the truncate out of band.
Reload isn't available: Pipeline <id>: Reload isn't available for fabricmirror targets, because published events and files can't be withdrawn. Create a new pipeline to publish a fresh snapshot. To write single tables again, re-seed them instead.
One pipeline owns each table folder. A folder that another pipeline or another producer writes to is refused.
Values are converted to Open Mirroring's Parquet types, and some are refused rather than changed:
- Decimals are written as DECIMAL(38,18); a value outside that range stops the pipeline.
- A datetimeoffset loses its offset and is written as a UTC time.
- A string or binary value larger than maxValueBytes stops the pipeline.
The lag SQL Flow shows ends when a file lands in the landing zone. The time Fabric takes to ingest the file isn't included.
A pipeline's target can't be pointed at a different mirrored database. Create a new pipeline for the other mirrored database.

If this was useful,
our newsletter will be too.

Get monthly insights on data engineering, AI, and building critical infrastructure - direct from the Spectral Core team and CEO Damir Bulic.