The Infor M3 Extraction Guide

Start with which M3 you have, because it decides everything. Multi-tenant CloudSuite M3 has no direct database access — you cannot point a JDBC driver or a replication tool at the operational database. The supported extraction path is the Infor Data Lake (part of Infor Data Fabric): M3 publishes its data there continuously, close to real time, and that is where your pipelines read from.

On-premises and single-tenant M3 BE installations are the other world: direct database access is available, and replicating the operational database to a mirrored reporting copy is a common, well-worn pattern. If that's your landscape, your extraction problem is ordinary database replication and most of this guide's Data Lake mechanics don't apply — though everything in the quirks guide still does.

M3 publishes table-shaped data objects to the Data Lake — the objects keep the plain table names, so MITBAL lands as MITBAL, with the same prefixed columns this reference documents. There is no renamed export schema to map back; the catalog pages here describe what you'll actually see.

Each landed record carries metadata properties alongside the business columns: identifierpaths, variationpath, deleteindicator, and archiveindicator. The variation properties are how the Data Lake represents change over time — every update to a record produces a new variation rather than overwriting the old one — and they matter enormously for correct analytics (see incremental & deletes and the quirks guide).

Compassis the Data Lake's SQL query platform — Athena-based, with a JDBC driver — so you can query landed M3 objects with ordinary SQL from tools and pipelines. By default a Compass query returns the current, non-deleted state of each record: the variation handling is done for you.

When you need history, deletes, or archived rows, you opt in with the table function: infor.include('<table>', 'ACTIVE, DELETED, ARCHIVED', 'AllVariations|HighestVariation') — choosing which record states to include and whether you want every variation or only the highest. That makes Compass both the convenient current-state read and the escape hatch for change-history extracts.

As of mid-2026 there is no first-party connector that lands Infor Data Lake / M3 data in Databricks. Infor's first-party streaming destination is Snowflake, via Data Fabric Stream Pipelines, and Infor's Databricks integration to date is Infor Nexus network data shared over Delta Sharing (announced June 2025) — supply-chain network datasets, not M3 ERP tables. So landing M3 in Databricks means wiring one of these paths, ranked by how often they're the right answer:

(a) Scheduled Compass JDBC pulls into bronze Delta tables — the simplest to operate and the usual starting point. (b) Data Lake APIs or Stream Pipelines — Infor's named streaming path — into cloud object storage, then Auto Loader into bronze — more moving parts, better for near-real-time needs. (c) Partner connectors — third-party tools that package the same plumbing; evaluate against your estate rather than assuming coverage.

Whichever path you take, expose the landed objects as a bronze schema under Unity Catalog — the raw, source-aligned layer you build silver and gold on. The <catalog>.<schema> placeholders in every boilerplate snippet on this site are meant to point at exactly that bronze layer.

You have two watermark options, and the hierarchy is: for landed Data Lake objects the variation metadata is authoritative — dedup on the primary key keeping the highest variation, and honor the delete indicator so removed records actually leave bronze. In-table LMDT + CHNO is the fallback — the change date and change number every M3 table carries (see the quirks guide) — for loads that don't carry the metadata columns. The audit columns can't signal deletes, which is the main reason the variation metadata wins when you have it.

The safe load is two steps: dedup each incoming batch to one row per primary key (highest variation), then MERGE into bronze — translating the delete indicator into a physical DELETE and everything else into an upsert. The skeleton below does both.

-- Incremental upsert from a staged Data Lake batch into a bronze Delta table.
-- Watermark hierarchy: for landed Data Lake objects the variation metadata is
-- authoritative; in-table LMDT + CHNO is the fallback when you land without
-- the metadata columns. Dedup on the primary key keeping the highest
-- variation, then MERGE — honoring the delete indicator.
MERGE INTO <catalog>.<schema>.MITBAL AS tgt
USING (
  SELECT * FROM (
    SELECT *, ROW_NUMBER() OVER (
      PARTITION BY MBCONO, MBWHLO, MBITNO
      ORDER BY variationpath DESC
    ) AS rn
    FROM <catalog>.<staging_schema>.MITBAL
  ) WHERE rn = 1
) AS src
ON  tgt.MBCONO = src.MBCONO
AND tgt.MBWHLO = src.MBWHLO
AND tgt.MBITNO = src.MBITNO
WHEN MATCHED AND src.deleteindicator = true  THEN DELETE
WHEN MATCHED AND src.deleteindicator = false THEN UPDATE SET *
WHEN NOT MATCHED AND src.deleteindicator = false THEN INSERT *

M3 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. MI API programs — the programs axis of this reference — are the transactional interface: business-validated reads and writes, scoped to what one call needs.

The split is by workload. For analytics at scale, land the raw tables — APIs paginate, throttle, and reshape, and rebuilding a fact table through them is slow and lossy. For operational integration— writes, small lookups, anything that must respect M3's business logic — call the MI APIs; writing to landed tables is never an option, and re-implementing M3's validation in SQL is a trap.

That is why this reference documents both: the tables you land for analytics, and the programs (interactive and MI API, cross-linked) that tell you where a screen's data actually lives and which API reaches the same records. Each program page lists its backing tables with SQL that reads them directly.