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, but their payload properties depend on the published object. This reference preserves M3’s prefixed physical catalog. Before running its generated SQL, inspect the object schema and Data Catalog metadata, then either use a prefixed physical source or normalize landed properties to these prefixed names.

One assumption to check early: publication is opt-in per table. M3 does not push its whole schema to the Data Lake — an administrator selects tables for publishing through M3’s Data Lake Publisher (opens in new tab) (under the M3-DLSubscriptionAdmin role), which creates the Event Hub subscriptions that stream those tables’ database events to Data Fabric. History from before a table was subscribed doesn’t arrive on its own — it comes through a separate initial-load step. So a table this reference documents may simply not be in your lake yet, and back-loading it is a distinct task, not a wait.

Data Catalog configuration identifies payload paths for record identity and variation, plus the paths and values that signal delete and archive state. These keys are not guaranteed payload columns. Resolve them per object and expose authored normalized aliases before using the examples. Variation is 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).

Compass is 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. Whether Compass does the variation handling for you now depends on the object’s query processing mode (opens in new tab). In analytical mode a Compass query returns the current, non-deleted and non-archived state of each record (ACTIVE records at their highest variation) — the variation handling is done for you. But in transactional mode (opens in new tab) no record-level deduplication is applied — every ingested version of a record comes back as its own row, and any dedup has to happen in your query or downstream. The mode is set per data object on Compass’s Mode Configuration panel (opens in new tab), and the switch’s off state is transactional — so check your objects’ modes before relying on Compass current-state semantics. Either way, the dedup-before-analytics MERGE pattern in incremental & deletes keeps bronze correct.

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. Note that these record-state controls are a separate axis from the query processing mode above: infor.include(...) chooses which record states and variations you ask for, while the mode governs whether the conversion deduplicates at all. That makes Compass both the convenient current-state read (in analytical mode) 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 path, Data Fabric Stream Pipelines (opens in new tab), delivers to supported database destinations — Snowflake, PostgreSQL and SQL Server on Infor’s enumerated list, not Databricks — and Infor’s Databricks integration to date is Infor Nexus network data shared over Delta Sharing (opens in new tab) (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 into cloud object storage, then Auto Loader into bronze — more moving parts, better for near-real-time needs. Stream Pipelines is a separate route to Infor’s documented database destinations, including SQL Server and PostgreSQL; it is not the object-storage landing step. Two things to know before architecting around Stream Pipelines: it is an add-on license to Data Fabric (opens in new tab) (a separate SKU to obtain, not a feature to switch on), and per the Data Lake Publisher documentation an active pipeline routes published data directly to its destination before it is stored in the Data Lake — a route ahead of the lake, not a read from it. (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. An archive indicator is retained as source state unless your separately chosen retention policy says otherwise. The skeleton below does both.

-- Incremental upsert from a staged Data Lake batch into a bronze Delta table.
-- Resolve each object's Data Catalog paths and indicator values first, then
-- alias the payload properties to variation_number and is_deleted; retain any
-- normalized archive marker as payload for a separately chosen retention policy.
-- Watermark hierarchy: normalized variation metadata is authoritative; in-table
-- LMDT + CHNO is the fallback when metadata is unavailable. Dedup on the
-- primary key keeping the highest variation, then honor physical deletes.
MERGE INTO <catalog>.<schema>.MITBAL AS tgt
USING (
  SELECT * FROM (
    SELECT *, ROW_NUMBER() OVER (
      PARTITION BY MBCONO, MBWHLO, MBITNO
      ORDER BY variation_number 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.is_deleted = true THEN DELETE
WHEN MATCHED AND COALESCE(src.is_deleted, false) = false THEN UPDATE SET *
WHEN NOT MATCHED AND COALESCE(src.is_deleted, false) = 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.