The D365 F&O Extraction Guide

The landscape shifted recently, so start here. Export to Data Lake — the older F&O export that wrote CSV to an ADLS Gen2 account you operated (a separate legacy path from BYOD, which exported entity shapes to your own Azure SQL database) — was retired on November 1, 2024. Microsoft’s current removed-features register (opens in new tab) publishes that retirement date and the remaining hard stop. Microsoft’s transition FAQ (opens in new tab) documents the migration path. There is now a hard end to the runway: the register states the service will be permanently stopped on November 30, 2026. After that it exports no new or changed data (files already exported to your lake remain in place). Microsoft’s FAQ now says no further extensions will be provided. The FAQ describes transition choices through Fabric Link or Synapse Link. For workloads that depend on Export to Data Lake’s near-real-time updates, it recommends Link to Microsoft Fabric with low-latency sync (covered in its own section below). Treat Export to Data Lake as end-of-life on a live countdown: if you inherited a pipeline built on it, it needs migrating before November 30, 2026 — not extending.

As of September 2026, Fabric Link is the durable full-table Delta path. Existing customers can also land F&O tables through Synapse Link for Dataverse into ADLS Gen2, while a separate incremental-update profile can land change data as CSV. F&O full-table sync through Synapse Link is Delta-only; CSV is not available for that same full-table shape.

The Synapse Link Delta Lake (Parquet) option is now deprecated. Microsoft’s Delta Lake export documentation (opens in new tab) says it will be unavailable to new customers beginning October 15, 2026, and existing customers must transition by December 2027, when the option disappears. For F&O, that closes the Synapse Link full-table Delta route: move full-table workloads to Fabric Link, or use the incremental-update CSV profile below when change-data files meet the requirement.

Two mid-2026 changes are worth knowing before you read older vendor posts. In mid-2026 Microsoft rolled out a rewritten low-latency sync engine for Fabric Link (announced June 9; GA staged region by region through late July) — covered in its own section below because migrating an existing link to it is not a toggle. And by early August 2026 the Power Apps entry point was renamed: the “Azure Synapse Link” navigation item became Link data, with Fabric links presented as the recommended path and Synapse Link moved under an “Other Links” section. Read that signal correctly — Fabric is where Microsoft’s investment is going, but Synapse Link is not going away: Microsoft’s transition FAQ (opens in new tab) states that it is not being retired, and that Microsoft intends to support exporting to your own storage “well into the future.”

You enable the specific F&O tables you need in the Synapse Link configuration — this is table-level, so scope it to what you’ll actually query rather than syncing everything — and each lands as a Delta folder in the ADLS Gen2 account you connect. As of September 2026 the sync adds a set of metadata columns you’ll see on every table: Id (a GUID derived from the source RecId), sysrowversion (Microsoft’s documented incremental watermark), IsDelete, SinkCreatedOn and SinkModifiedOn (when the sink wrote the row — equal to SinkCreatedOn on the Delta path, so use it as a tiebreak, not the watermark), versionnumber, the source createdon/modifiedon, tableid, and sysdatastatecode — which flags archived rows: 1 means the row was moved to long-term retention, 0 means live. Archived rows stay in the lake, so any count that doesn’t filter sysdatastatecode = 0 silently mixes archived and live data. Two more land alongside them — createdtransactionid, documented as unused, and modifiedtransactionid, documented as internal — safe to ignore either way. The OptionsetMetadata folder that decodes enums lands from this same sync (see the quirks guide).

Several rename and truncation rules will bite a query written against the AOT field names, so watch for them: source fields named Id become FnO_Id; fields that collide with a reserved word get a trailing underscore (e.g. Level lands as Level_); and memo/long-text fields are truncated at 2,000 characters, so don’t treat a landed memo column as complete.

Harder to catch is what silently doesn’t export. As of September 2026, Microsoft’s Synapse Link F&O documentation (opens in new tab) states that a derived table exports only its own fields — the base table’s columns are simply absent, and you have to select the base table separately to get them. That lands squarely on the advice below to land DirPartyTable raw: sync DirPerson on its own and the shared party columns you wanted never arrive. Microsoft’s documented remedy is the FastTrack derived-tables script (opens in new tab), which identifies the base tables you must add to the sync and generates the join that reassembles the full shape on your target. The same documentation flags a related trap on older builds: deleted rows go missing from derived tables — the delete markers for both the derived and base tables never arrive in the export, so a party deleted in F&O lives on in your bronze layer forever. The fix ships in 10.0.40 (PU64, platform 7.0.7279.199), 10.0.41 (PU65, 7.0.7367.136), and 10.0.42 (PU66, 7.0.7452.75) or later — if you land derived tables like DirPerson, confirm your build clears that floor before trusting deletes from them.

Whole column classes never land either. TimeZoneID (TZID) and binary fields are never exported. Array-type fields were excluded before 10.0.41 (PU65) and need a full refresh to backfill once you’re on a build that carries them; AOS-authorized fields were excluded before the PU62/PU63 cumulative updates. Each is the same trap: the column exists in the AOT, so it looks available, but it isn’t in your lake. And del_-prefixed, staging, and temporary tables aren’t exportable at all — don’t design a sync around one.

The biggest version-gated failure isn’t a missing column but a whole table: on older builds, a table secured by extensible data security policies might silently not export at all. Microsoft’s fix has the service bypass those policies, and ships in 10.0.37 (PU61, cumulative update 10.0.1725.175), 10.0.38 (PU62, 10.0.1777.135), and 10.0.39 (PU63, 10.0.1860.50) or later — if a selected table lands empty or never arrives, check your build against that floor before debugging the pipeline.

Two setup-time constraints shape the landing zone before any table syncs. F&O tables can’t be added to a Synapse Link profile that already contains Dataverse tables — you create a new profile (after which it can carry both) — and the environment must be a Tier-2 sandbox or higher, or one provisioned from an ERP-based template. F&O tables also require the profile’s Delta lake feature: table selection isn’t even visible without it. That conversion runs on a Spark pool you provision, on a Delta conversion time interval you choose — 5, 15, or 60 minutes for near real-time, or 1440 for daily. It is the guide’s biggest freshness-vs-cost lever: the lower the interval, the more Spark compute you burn, and Microsoft cautions that the interval you request is not the freshness you observe — actual refresh depends on the Spark pool’s size and the volume of incremental changes. Sizing guidance lives in Microsoft’s Delta Lake export documentation (opens in new tab).

Check the Spark runtime as well as the interval. Microsoft marks Synapse runtime Apache Spark 3.4 as retired; new pools require the current runtime, and workflows tied to retired 3.4 pools stop executing. Recovery is to create a new current-version pool and run the documented in-place profile upgrade. Microsoft says the backend switch can take up to 48 hours after an eligible Delta conversion job is triggered; keep the old pool until the new one is confirmed in use.

Check the network path too if your storage account sits behind a firewall. Microsoft is retiring the trusted services firewall exception that lets Azure Synapse Analytics reach storage and Key Vault through a managed identity; the retirement, first set for 1 August 2026, now falls on 1 August 2027. Microsoft’s transition FAQ (opens in new tab) documents the move that keeps Synapse Link connecting: a Synapse workspace with a managed virtual network, a managed private endpoint to the ADLS Gen2 storage account, and “Allow Azure services and resources to access” enabled on both the storage account and the Synapse workspace. Microsoft states that both settings must be on for Synapse Link to connect.

Two operational notes. Kernel tables such as DATAAREA and USERINFO sync only every 24 hours, not near-real-time like the application tables — fine for company and user lookups, but don’t build freshness-sensitive logic on them. And — central to every SQL snippet in this reference — column names land lowercase, which is why all generated boilerplate here uses lowercase identifiers.

Fabric Link (opens in new tab) surfaces the same F&O data as Delta, but lands it in OneLake via Dataverse-managed compute rather than into a storage account you operate. The trade-off, as of September 2026: less infrastructure to run yourself, but the landing lives inside Fabric rather than in an ADLS account you fully control. The metadata columns and lowercase-identifier behavior described above still apply. Once your region’s station has the mid-2026 rollout, new Fabric links run on the low-latency sync engine described in the next section — enablement is per station, so check the link’s low-latency flag rather than assuming it from the calendar.

Two facts usually decide this for an enterprise, and neither is technical. OneLake-linked F&O tables consume Dataverse storage — the replica is billed against your Dataverse capacity, not against cheap lake storage you provision — and the replica stays in the Dataverse environment’s region, so you cannot place it where your analytics estate or your data-residency rules require. Check both before you pick Fabric Link over Synapse Link. You can at least scope the storage bill: as of September 2026 the setup wizard has a table-selection step, and a standalone “Manage tables” surface lets you add or remove tables after setup — only selected tables consume storage.

For authentication, Microsoft recommends Fabric workspace identity (opens in new tab): it avoids secret management and the service-principal misconfiguration Microsoft calls the most common Fabric Link setup failure. Create the identity on a non-personal Fabric workspace and onboard it as an application user in Dataverse before creating the link. Also check tenant scale: Microsoft documents that Link to Fabric can fail when the environment has more than 2,000 active Dataverse tables.

From Databricks you reach OneLake Delta through a OneLake shortcut or an external location, then register it in Unity Catalog like any other Delta source — from there the boilerplate SQL in this reference is identical to the Synapse Link path. One caution for that wiring: don’t hard-code the lakehouse display name in external paths. Microsoft documents the generated name format — <environmentname>_<internalprofile>_<workspace>_<uniquevalue>, where the internal-profile segment appears as CDS2 or CDS3 (a historical marker Microsoft says no longer carries meaning and is likely to be dropped) — as subject to change without notice, and says to reference stable identifiers — the workspace and lakehouse IDs — instead. Which path to choose is usually decided by where the rest of your estate already lives (a Fabric tenant vs. your own ADLS + Databricks), not by the F&O data itself.

On June 9, 2026 Microsoft announced a rewritten Fabric Link sync engine (opens in new tab), with general availability staged region by region from mid-July through late July 2026 — so “when it shipped” depends on your station, not the announcement date. The old pipeline read from the Dataverse database, wrote an intermediate CSV, then converted that to Delta Parquet; the new engine drops the CSV hop and writes directly to Delta Parquet. For F&O tables Microsoft cites throughput of 1M+ records per hour per table — lab figures, but against the roughly 100k–700k/hour the old engine managed, the jump is real. This is Fabric Link only; the Synapse Link → ADLS path is unchanged.

Enablement is automatic, per region station — there is no opt-in. Once your station has it, new Fabric links use the new engine (the link shows a “Low-latency mode” flag in the Link data page). The catch is existing links: an existing Fabric Link profile stays on the old engine until you unlink and relink, and relinking triggers a full initial sync of every configured table. Treat that as a re-onboarding event for your Databricks pipelines — schedule it off-peak and expect the initial load to take a while on large tables — not a settings toggle. Two more migration facts from Microsoft’s Fabric Link documentation (opens in new tab): the relinked profile carries live data only, so if you use long-term retention, retained rows stop surfacing through the Fabric shortcuts until Microsoft completes a promised retained-data migration (retention itself keeps running); and F&O environments must be on a supported build before low-latency sync works — that same documentation maintains the current table of minimum platform and application builds per F&O version (10.0.47, 10.0.48, and 10.0.49 as of September 2026), so check Help & Support → About against it and apply the latest quality update if you’re below the floor.

One schema change needs engineering attention before you relink: the new engine writes timestamp columns as INT64; the INT96 encoding the previous engine used is not supported. For Databricks this is mostly good news — INT64 is the modern Parquet timestamp encoding and Spark reads it natively — but audit anything downstream that special-cased INT96 (custom Parquet readers, type-mapping code, semantic models over the same tables) before the switch, per Microsoft’s own guidance.

The new engine also brings an optional Change Data Feed (opens in new tab) capability (documented August 2026): with low-latency sync active, Fabric Link can preserve roughly 24 hours of row-level change history via Delta Lake’s Change Data Feed (opens in new tab) (the underlying open-source mechanism, documented independently of Microsoft), enabling time-travel and change-based reads directly against the synced tables. It is opt-in per environment through the Power Platform admin center — useful if your incremental pipeline wants real change rows instead of diffing snapshots. Two cautions from the same documentation: the ~24-hour window means it complements, not replaces, the watermark-based MERGE pattern below, and Microsoft warns CDF is memory- and compute-intensive and can raise overall sync latency even in low-latency mode — weigh that tradeoff before enabling it.

If downstream work reads the SQL analytics endpoint, Microsoft also offers a new endpoint metadata-sync path (opens in new tab) to reduce end-to-end latency. It is explicitly preview, requires a new workspace connection, and is not intended for production use while in preview.

The manual path is the same either way: point a Unity Catalog external location at the ADLS Gen2 container (or the OneLake path), register the landed Delta folders, and expose them as a bronze schema — the raw, source-aligned layer you build silver and gold on top of. The <catalog>.<schema> placeholders in every boilerplate snippet on this site are meant to point at exactly that bronze layer.

There is also a managed alternative: Databricks Lakeflow Connect for Dynamics 365, a first-party connector built on top of Synapse Link. It lands F&O data incrementally under Unity Catalog governance without you wiring the external location and MERGE logic by hand. Release status and feature coverage evolve quickly, so check the connector’s current documentation (opens in new tab) for your cloud before you design around it rather than assuming it’s there. Where it is available, it removes most of the plumbing in the next section; where it isn’t, the manual MERGE below is the fallback. The managed connector reads Synapse Link exports in ADLS Gen2; it does not consume Fabric Link. Its Parquet ingestion path is Beta as of September 2026, so check the format-specific setup (opens in new tab) before choosing it. The Delta/Parquet retirement above also makes this managed Parquet path time-limited for new designs; the connector can ingest Synapse Link CSV, but that is a different source profile and landing shape. As of September 2026 the connector documentation publishes a cap of 250 tables per pipeline, SCD type 2 support, and column renames arriving as a new column plus a deleted column rather than a rename. The sharp edge is that automated schema evolution does not cover data-type changes — a type change on the source breaks the pipeline rather than flowing through it.

The dedicated connector limitations (opens in new tab) add the recovery rules: deleting a source table does not remove the destination table; selecting a new column does not backfill history unless you run a full refresh; missing or expired Synapse export files can also force a full refresh. For SCD type 2, run more frequently than once every 24 hours because Parquet checkpoint compaction can make intermediate versions unavailable. F&O virtual-entity schema changes require a Dataverse refresh, propagation, and then a full refresh. Attachment metadata lands, but file contents must be fetched separately.

The sync is incremental, and — this is the part that trips up a first pipeline — deletes don’t remove rows, they arrive as soft deletes: the row reappears in the batch with IsDelete = true. Ignore that flag and deleted records live on in bronze forever, inflating every count and sum built on top. As of September 2026 the sink itself purges these deleted Delta rows after 28 days, which sets a hard operating limit — if your pipeline lags more than 28 days it can miss the delete signal entirely, so bronze must stay current.

The safe load is two steps. First dedup each incoming batch on RecId, keeping the row with the latest sysrowversion — Microsoft’s documented watermark field — with SinkModifiedOn as tiebreak, so a record updated several times in one window collapses to its final state. Then MERGE into bronze, translating IsDelete = true into a physical DELETE and everything else into an upsert — so bronze reflects the true current state of F&O and downstream counts stay correct. The skeleton below does both.

That watermark is not universal table metadata. Microsoft’s row-version change-tracking documentation (opens in new tab) says a table must have Allow Row Version Change Tracking enabled to receive the SysRowVersion system field. Entity change tracking then adds that source to the entity view and records entity deletions separately; unsupported entity shapes and mutable filters can fail validation or prevent deletions from being tracked correctly. That entity-side deletion record has a much shorter life than the 28-day Delta purge above: Microsoft’s deletion-tracking cleanup (opens in new tab) removes tracked entity deletions after a 10-day retention period, and changes for finance and operations entities are returned only if the last token is within 10 days, not the 90 days Dataverse tables get. The two limits sit on different paths: the 28-day purge governs Synapse Link Delta table data, while the 10-day window (currently Microsoft’s default, enforced by a configurable cleanup batch job) governs entity change tracking. Size your pipeline’s maximum lag to the path you actually use.

-- Incremental upsert from a Synapse Link / Fabric Link staged batch into a
-- bronze Delta table. Dedup on RecId keeping the latest sysrowversion (the
-- documented watermark; SinkModifiedOn ties within a Delta conversion), then
-- MERGE — applying soft deletes (IsDelete) as physical deletes in bronze.
-- Delta-path batches only: on the incremental-update (CSV) profile, deleted
-- rows carry only Id with an empty body — see the note below the skeleton.
MERGE INTO bronze.inventtrans AS tgt
USING (
  SELECT * FROM (
    SELECT *, ROW_NUMBER() OVER (
      PARTITION BY recid ORDER BY sysrowversion DESC, sinkmodifiedon DESC
    ) AS rn
    FROM staging.inventtrans
  ) WHERE rn = 1
) AS src
ON tgt.recid = src.recid
WHEN MATCHED AND src.isdelete = true  THEN DELETE
WHEN MATCHED AND src.isdelete = false THEN UPDATE SET *
WHEN NOT MATCHED AND src.isdelete = false THEN INSERT *

One landing shape this guide hasn’t named yet changes that skeleton: the incremental-update (CSV) profile. Microsoft’s Synapse Link F&O documentation (opens in new tab) describes it as a separate Synapse Link profile that lands the same F&O tables as CSV files in time-stamped folders — no Synapse workspace or Spark pool required — for feeding your own downstream warehouse. The critical difference for the load logic: on that path a deleted row arrives carrying only its Id (plus IsDelete = true) with the body of the record empty. The MERGE skeleton above is written for the Delta path, where a soft delete becomes a physical DELETE before any column is read — that part survives — but don’t reuse the pattern of trusting a delete row’s other columns anywhere else in a CSV-profile pipeline: an empty-bodied delete row that reaches an UPDATE SET * or a downstream join blanks real values. On the CSV path, treat delete rows as an Id-only signal and look up any fields you need from the last non-deleted version of the record.

F&O exposes its data on two axes, and you’ll use both. Raw tables are the physical schema — transaction grain, every column, the shapes the quirks guide describes. Data entities are export-shaped views: pre-joined, business-named, convenient. Where a good entity exists, landing it saves you the joins.

Landing an entity also has operational gates that landing a table doesn’t. Microsoft’s Synapse Link documentation (opens in new tab) documents three: an F&O entity must first be enabled as a Dataverse virtual entity, then have row-version change tracking turned on, before it can be selected for sync at all — and change tracking can’t be enabled for every entity: enabling it can fail validation with “chosen entity doesn’t pass the validation rules,” which means some entities simply cannot be landed. Entities that do land arrive under the mserp_ prefix — look for mserp_salesorderheaderv2entity in your lake, not salesorderheaderv2entity — the same class of naming trap as the lowercase column names covered earlier. If the entity you wanted fails validation, fall back to the raw-table route: every entity page in this reference generates the SQL that rebuilds the entity’s shape from its landed tables — Microsoft’s own documented fallback for this scenario is the FastTrack EntityUtil solution (opens in new tab), which builds the entity shape as views over those same landed tables.

The catch is that entities don’t cover everything. Several core supply-chain objects have no standard export entity and can only be reached by landing the raw table: InventTrans, InventSum (only aggregate on-hand entities exist, not the full detail), InventDim, ReqTrans, DirPartyTable, the AR/AP open-item ledgers (CustTrans and VendTrans), and the warehouse work tables (WHSWorkTable exposes only a lines entity). These are exactly the transaction- and dimension-grain objects analytics needs most, so raw-table landing isn’t optional — it’s the backbone of any real F&O extract.

That is why this reference documents both axes: land raw tables for the grain entities can’t give you, and lean on entities where they exist to skip the joins. When you go the raw-table route, each entity page here includes SQL that rebuilds the entity from its landed tables — so you can reproduce the convenient shape yourself and validate it against what the entity export would have returned.