The D365 F&O Extraction Guide

The landscape shifted recently, so start here. Export to Data Lake — the older BYOD-style F&O export — was retired March 25, 2025. If you inherited a pipeline built on it, it is on borrowed time and needs migrating, not extending.

As of mid-2026 the two supported paths for landing F&O data are Synapse Link for Dataverse and Fabric Link. Both are Microsoft-managed exports that surface F&O tables as Delta — Synapse Link into a storage account you own (ADLS Gen2), Fabric Link into OneLake. One practical constraint worth knowing up front: for F&O full-table sync the output is Delta only; CSV is not available on that path. That is a change from the old export world, and it is the reason the whole rest of this guide assumes Delta in a lakehouse.

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 mid-2026 the sync adds a fixed set of metadata columns you'll see on every table: Id (a GUID derived from the source RecId), sysrowversion, IsDelete, SinkCreatedOn and SinkModifiedOn (when the sink last wrote the row — your incremental watermark), the source createdon/modifiedon, and tableid. 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.

Two operational notes. Kernel tables such as DATAAREA and USERINFOsync 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 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 mid-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.

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. 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.

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. As of mid-2026 its availability varies by cloud and region, so check the current status 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 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 mid-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 SinkModifiedOn / sysrowversion watermark, so a record updated several times in one window collapses to its final state. Then MERGE into bronze, translating IsDelete = true into a physical DELETEand 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.

-- Incremental upsert from a Synapse Link / Fabric Link staged batch into a
-- bronze Delta table. Dedup on RecId keeping the latest sink watermark, then
-- MERGE — applying soft deletes (IsDelete) as physical deletes in bronze.
MERGE INTO bronze.inventtrans AS tgt
USING (
  SELECT * FROM (
    SELECT *, ROW_NUMBER() OVER (
      PARTITION BY recid ORDER BY sinkmodifiedon DESC, sysrowversion 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 *

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.

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, CustTrans, VendTrans, DirPartyTable, and the warehouse work tables (WHSWorkTableexposes 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.