Still landing data the old way? You’re paying too much for it.
Open Mirroring in Microsoft Fabric is faster, simpler and free as in free beer.
In this deep-dive, learn how to build and tune open-mirroring pipelines with Python, measure real-world performance, and see why this cost-free pattern should be your default for data landing.
If You’re Not Using Open Mirroring Yet, You’re Doing It the Hard Way Jon Christian Halvorsen Track: Data Integration Level 300 Who am I (and why this talk exists) Jon Christian Halvorsen Data Platform Architect, Twoday • Build Microsoft Fabric data platforms for SMB customers in Norway • Focus: Ingestion, architecture, operational reliability • The pattern comes from real pipelines, not labs Most of this room is paying for orchestration, not for data Typical nightly delta runs: • ~1 minute file generation • As always, depends on source latency • Additional time for mirroring engine runtime • No manual merge logic • Some sources: Auto discovery of schemas and keys • Ingestion compute cost: $0 What surprised me building this • Open mirroring is not a connector – it’s a storage protocol • The contract is extremely strict • Abstractions break when file lifecycle changes Open Mirroring is a Storage Contract You produce the files, fabric does the rest You Control This Source System SAP / M3 / API Storage Contract Fabric Controls This Fabric notebook Python Producer Mirroring Engine OneLake Landing Zone
Write delta Compute: $0 Delta table Delta table Delta table This is scheduled batch, not CDC - and that is a deliberate choice • Most source systems are batch anyway ERP and LOB systems often change hourly or nightly, not every second. • CDC Requires always-on compute Streaming needs always-on infrastructure. Batch runs only when needed. • Open Mirroring makes batch cheap and fast You write files on schedule. The mirroring engine handles the merge at $0 compute. Get these three things wrong before the first file and you never fully recover
Folder path • Files/LandingZone/.schema//
Schema must never drift • Column name + order + types must match
Key columns define merge behaviour • Declared in _metadata.json Most sources clear four of five criteria The one gap is usually deletes Criteria SAP OData S/4HANA Infor M3 Compass D365 F&O / BC (Odata) File-based Blob / ADLS Keys from metadata ✓ auto EntityType Key ✗ static config ✓ auto EntityType Key ✗ static config Reliable watermark ✗ static Config per table ✓ server-side compasstimestamp ✓ always SystemModifiedAt ✓ blob LastModified Delete tracking ✗ none poll only ✓ boolean flag in row ✗ none poll only ✗ none poll only Paging built-in ✓ nextLink /$skip fallback ✓ async job CSV pages ✓ nextLink OData v4 ✓ file-by-file LastModified Schema auto-gen ✓ $metadata full EDM types ✓ type metadata in response ✓ $metadata full EDM types ✗ define once from first file ✓ Clean fit ✗ Limitation — manageable When the API has a metadata endpoint, schema and keys come for free Examples: SAP S/4HANA OData v2/v4, Dynamics 365 Business Central, D365 F&O The point: $metadata does the work • One function call per entity — schema + create_table + page loop + watermark all inside • First run fetches $metadata and caches to disk. Every run after loads from JSON — no HTTP call. • delta_column in entity config triggers watermark delta loading automatically When there is no metadata, you implement the same contract manually Examples: Infor M3 Compass, Salesforce Bulk API 2.0 The point: same outcome, you do more legwork — submit, poll, page • M3: keys are static config, no metadata API - specify key_cols in TABLES list • Watermark: max(infor_compasstimestamp) - same high-watermark pattern When there is no API at all, blob LastModified is your watermark Examples: Azure Blob, ADLS — utility meters, IoT exports, nightly file drops, legacy extracts The point: source does not need an API at all • Late corrections handled for free: meter estimated → actual arrives later → rowMarker=4 (Upsert) overwrites. Zero extra logic. • Keys are static config — define once from the first file you parse • Pattern applies to any system exporting to storage: healthcare, construction, energy, legacy mainframes Demo Time Three categories of failure — and none of them surface an error message Nothing loads at all • Wrong folder path — must be exactly LandingZone/.schema// • Missing or malformed _metadata.json — table is invisible to the mirroring engine • File named incorrectly — sequential name in a LastUpdateTime table (or vice versa) • Fix: validate folder + _metadata.json exist before first upload; check Monitoring/tables.json Data lands but looks wrong • Schema mismatch: column name case, column order, or type differs from the stored schema • pandas in the pipeline: None→NaN, int64→float64, bool→object — silent type corruption • Decimal128, timestamp[us,UTC], bool — must be built explicitly in Arrow, not inferred • Fix: Arrow all the way from source record to pq.write_table() — zero pandas anywhere in the chain Permissions and identity • FabricTokenCredential token expires mid-run — refresh every 55 min or token call will fail silently • Fix: memoize with 5-min pre-expiry buffer (already in OpenMirroringClient.get_access_token) Change data fails silently; scale constraints fail loudly but too late Change data goes wrong • Duplicates / unexpected upserts — keyColumns wrong or missing in _metadata.json • Deletes don’t delete — rowMarker=2 requires correct key match; wrong keys = silently ignored • rowMarker=0 (Insert) on re-runs creates duplicates — always default to 4 (Upsert) • Fix: verify keyColumns match actual primary key before first load; always ship rowMarker=4 Scale and operational drift • Too many small files — ingestion overhead dominates; target ~10k rows per Parquet file • Schema evolution: adding a column requires drop and recreate — no incremental alter • SequentialFileName counter breaks if Fabric reorganises files — GUID files have no counter to break • Throttling limits: ~1 TB/day change rate, 500 tables per mirrored database The rule: treat Open Mirroring like an API contract. Write _metadata once, lock the schema, never manually touch the folder. Real numbers from two live production pipelines SAP S/4HANA — 25 OData entities • Initial full load: 37 entities | ~3 M rows | 12 minutes | Schema auto-generated from $metadata • Delta runs: 58 seconds for parquet file dumping, 2,5 minute for mirroring engine sync Infor M3 — 3 largest tables via Compass async SQL API • Initial full load: Not interesting, limited by Compass API • Delta runs: 39 seconds for parquet file dumping, 2 minute for mirroring engine sync No Spark. No Dataflow Gen2. No data pipelines. No orchestration compute. OneLake initial file landing operation is the only cost. OneLake storage free (up to a cap) Why we implemented a direct storage client SDK Limitations • Service principal only — incompatible with notebookutils.getToken(); cannot use Fabric workspace identity What i learned from Raki Rahman’s implementation • Direct azure.storage.filedatalake calls — no SDK abstraction layer, no SDK bugs • Sequential filenames only — the counter Fabric now corrupts during its own processed-file cleanup • GUID filenames (LastUpdateTimeFileDetection) — no sequential counter, nothing to corrupt • Re-initialises _metadata.json on every call — destructive in a running pipeline • Token memoized with 5-min pre-expiry buffer — runs as long as your pipeline needs • No token refresh — long loads fail silently when the token expires mid-run What the engine can actually do: 1.2 billion rows/minute on F2, 30–60 sec lag to Delta • Stress-tested by Raki Rahman (Microsoft SQL Server Telemetry Team) — empirical, not theoretical • File size sweet spot: below 1.25M rows/file — latency degrades above this threshold • Stress-test on appending data, not on merging, that’s why we see longer lag locally. • rakirahman.me/fabric-open-mirorring-stress This pattern is ready to ship — here is how to start • Default to Open Mirroring for new ingestion pipelines • Use LastUpdateTimeFileDetection + GUID filenames — simpler, no counter to break • Generate schema from source metadata on first run, cache to JSON forever after • Add watermarks before you need them — retrofitting delta into a full-load pipeline is painful • Separate source libs from the mirroring lib — keep the core clean, copy the source lib per source Benchmark + stress test: rakirahman.me/fabric-open-mirorring-stress How was the session? Complete Session Surveys in for your chance to WIN PRIZES!