Source History & Ingestion Path

The question “which connector should we use?” is usually answered as a procurement question — what is supported, what is managed, what can be stood up this quarter. That ordering produces pipelines that run cleanly and cannot answer the question they were built for.

The right ordering is the opposite. A connector is chosen by two things: how the source represents change, and what history the metrics downstream actually need. Those are properties of the source and of the metric definition, not of the connector catalog, and they are knowable before any tool is evaluated.

The trap worth naming up front, because it is the most expensive one: a managed connector landing a governed streaming table does not give bronze the grain or the history a metric needs. It gives you a reliable, observable, governed copy of what the source holds — which is exactly as much history as the source holds. If the source overwrites a forecast in place, a perfectly configured connector will faithfully replicate the overwriting. The pipeline is not broken. It is complete, and the number is still unbuildable.

This page is the classification layer. It does not restate any reference's extraction mechanics — those live in the eight extraction guides, which every section below links to — and it ships no SQL, for the same reason. What it adds is the posture each source lands in, the change signal you get, and the list of things that will still be missing when the pipeline is green.

Every source on this page lands in exactly one primary posture. Naming it first is what makes the rest of the design decidable, because each posture fails in its own characteristic way.

Change-feed replication. A database log, or a vendor change framework, replays inserts, updates and deletes as discrete operations. This is the only posture that answers the delete question without a second mechanism, and the only one where the sequence of changes — not just the current value — reaches the lakehouse. What it does not do is create history the source never had.

Watermark pulls. A query filtered on a monotonic cursor — a last-modified timestamp, a change number — against a stored high-water mark. Cheap, universally available, and blind in two specific ways: a deleted row simply stops arriving and nothing marks its absence, and any update that does not move the cursor is invisible. Both failures are silent, and both grow with time rather than announcing themselves.

Snapshot-per-run export. Each extract is a full or windowed copy of the source as of the run. Nothing is inferred, which is its virtue; change detection is a diff you compute, which is its cost. The characteristic mistake here is reading the extract as a dated fact — a copy taken every run is a copy, not a balance keyed on the day it describes, and its schedule belongs to the extract tool rather than to the metric.

These three cut the same ground as the three change feeds in the deletes guide, on a different axis: that guide sorts sources by the delete signal they emit, this one by the history that survives the trip. Log CDC and vendor export frameworks both land in change-feed replication here; audit-column watermarks are watermark pulls.

One rule survives all three, and it is the reason posture is worth arguing about before tooling: from events you can always derive a snapshot; from snapshots you can never recover the events. Where budget forces a choice, land the event log.

Given the source condition, this is the connector family to start from — and the evidence to gather before adopting it. The middle column is a starting point, not a conclusion: the third column is what turns it into one.

Two caveats belong beside this table rather than under it. Connector availability and release state are time- and cloud-specific, which is what the dated banner at the top of this page is for — release state belongs in the production risk review, not in a design document written six months earlier. And coverage of enterprise sources, SAP and Oracle above all, is materially narrower than the taxonomy above suggests: those estates often route through partner tooling, files, or custom paths. That is exactly why the SAP and EBS sections below point at their own path families rather than at a row of this matrix.

Run this per source object, before the connector conversation starts. Every question has an answer that is discoverable in the source and none of them requires a tool decision first.

1. What kind of object is it? An immutable event, mutable current state, a parameter, a file snapshot, or a stream. These five behave differently under every posture above, and most arguments about ingestion are really disagreements about which of the five a table is.

2. What identifies a row, and what identifies a change? Two different keys, and a pipeline that conflates them either loses updates or duplicates them.

3. Can updates arrive without the cursor moving? If yes, a watermark pull is already wrong and no amount of scheduling fixes it.

4. How are deletes represented? In band as a flag, in a separate ledger, as a CDC operation, or not at all. The fourth answer is common and is the subject of the deletes guide.

5. What can be replayed after an outage? Log retention, API history depth, file retention — the horizon past which a gap is permanent rather than recoverable.

6. Which of the four timestamps is which? Source business time, source commit time, connector discovery time, and destination commit time are four different instants, and using the last as if it were the first is the most common way a correct pipeline produces wrong dates.

7. What does the metric need? Transaction history, SCD history, periodic snapshots, or versioned plans. This is the question that makes the other six actionable, and it is answered from the metric definition rather than from the source.

The rule that goes with the list: if these are unanswered, connector selection is premature. Not risky — premature. There is no way to evaluate a connector against requirements nobody has written down.

Four things the metrics in this library need are, in every reference examined, either absent from the source or overwritten by it. None of them is fixed by choosing a better connector. All four are named here so that a design can decide where they get built rather than discovering them at quarter close.

One: the daily balance. ERPs expose current state, movement events, or a sparse period history — never one row per item, per location, per day. CDC records the changes faithfully and does not create the daily row, because the daily row is not a change; it is a restatement of nothing having happened. Every day of delay is a day of history recoverable only by expensive movement replay, which is why the inventory snapshot guide treats it as a scheduled build with a start date rather than a modeling choice.

Two: the forecast vintage. Accuracy by lag needs the forecast as it was stated, frozen at statement time and keyed by the as-of date. That value cannot be recovered afterwards by any means. And connector SCD2 over a current forecast table is not a substitute: it records the changes that were observed, which is not the same object as the official issued plan — a mid-cycle correction and a published forecast look identical in a change feed.

Three: the plan of record. No connector creates plan governance that the source does not have. Which version counts, who declared it, and the window it was in force for is a client convention captured in an authored table — a first-class dimension, not a system attribute inferred from a version id. Where a source does carry a native flag, the authored table becomes a mapping to it; designing for the convention case is the reversible choice.

Four: overwritten parameters. A tolerance is the denominator's rulebook. If the over-delivery tolerance was 10% last quarter and is 5% today, and the source overwrites in place, then recomputing last quarter's fill rate today silently applies today's rule to yesterday's shipments — and the number moves without any transaction changing. The same is true of a reorder point, a safety-stock level, or a lead time.

Two house rules fall out of all four. The first is the posture rule stated above — events give you snapshots and snapshots never give you events. The second is the quieter of the two and does more damage, because nothing about it looks like a failure: a parameter without a date retroactively rewrites history the next time someone recalculates.

History tracking on a managed connector is a real and useful feature, and it is routinely asked to be something it is not. Databricks documents it as disabled by default, with support varying by connector and by table; where it is enabled, prior rows are retained and marked inactive rather than overwritten. Databricks also documents that deleting a source table or a source column does not delete data already landed in the destination, even under SCD Type 1 — the landed copy outlives the source object.

The house controls, which are opinions rather than product behaviour:

Enable it selectively. Only on objects whose changing attributes are analytically meaningful — an attribute nobody reports on historically is storage and confusion.

Keep the natural key.Retain it alongside the connector's own row-validity metadata, so the two identities stay separable.

Add business-effective dates. Wherever the source supplies them, because those are the dates a metric joins on.

Separate deletion from inactivity. A source deletion and a connector-inactive row arrive looking similar and mean different things.

Test late and out-of-order updates deliberately. That is where row closure breaks, and it breaks quietly.

Never SCD2 an event table. Applying it to an append-only event table merely to have more history buys nothing — the events already are the history.

The distinction that governs all six: connector SCD2 is a physical capture method; conformed-dimension SCD2 is a gold modeling decision. One records that a value in a source table changed and when the platform saw it. The other declares which version of a business attribute was in force for a business window, on a dimension that may draw from several sources at once. Where each belongs is the medallion layer contracts guide.

SAP is the reference where the posture is settled and the path is not. Every family this library evaluates delivers change — the question is which one your estate can license and reach, not whether you are getting a change feed.

SAP — the path

Change-feed replication, in one of the path families the SAP extraction guide lays out: BDC Connect, Datasphere premium outbound, ODP over OData, table-level CDC, and in-system ABAP add-ons. Choosing between them is that guide's subject and is not restated here; what matters on this page is that the table-grain families — Datasphere premium outbound, ODP over OData and table-level CDC — land the movement document rather than a balance. Two qualifications. BDC Connect delivers curated data products, not raw tables at your chosen grain, so what arrives is the shape SAP modeled rather than the transaction-grain movement this page is asking for. And what an ABAP add-on lands depends on the transport it uses, which is a per-vendor question rather than a property of the family.

SAP — what the source keeps

Movement events, in full: MSEG in ECC and MATDOC in S/4HANA are event logs at document-item grain, and they are the richest history any reference on this page ships. Beside them SAP keeps a period stock history — and this is the part that gets over-read. It is written at fiscal-period grain, monthly, and only for periods in which the stock actually moved, so the series is sparse rather than gap-filled. It is not a daily balance and it cannot be treated as one. Current state lives on MARD and MBEW for the current period alone.

SAP — what you must snapshot yourself

The daily balance, from the movement events. And the parameters: the overdelivery and underdelivery tolerances on EKPO and the planning settings on MARC— MRP type, lot sizes, planned delivery time — are overwritten in place with no effective dates, so today's extract holds today's rule and nothing else. Of the four problems, the SAP path solves none on its own; what it does is deliver the events cleanly enough that the daily balance is a reconstruction rather than a guess.

SAP → lakehouse landscape →Table-level CDC & extraction tools →

EWM is the reference where the source deletes its own history as a matter of normal operation, which makes the posture question sharper here than anywhere else on this page.

EWM — the path

Change-feed replication, and the delete operations are not optional. Embedded EWM rides the same S/4HANA path families as the SAP section above; decentralized EWM is a second system with its own database, and the same families are pointed at it. Either way the requirement is a feed that emits deletes (raw table CDC and the open-task problem).

EWM — what the source keeps

The whole task story, split across three tables. Confirming a task removes it from /SCWM/ORDIM_O and writes the confirmed task to /SCWM/ORDIM_C (confirming a warehouse task deletes it); a task cancelled rather than confirmed leaves the open table without reaching the confirmed one, and is generally logged in /SCWM/ORDIM_L. Nothing is lost from the system — but a feed reading the open table on a creation-timestamp watermark can never learn that a row left it, so bronze shows every task ever created as still open. That is not stale data; it is a work-in-progress number that is really a cumulative created count, and it renders plausibly on a dashboard.

EWM — what you must snapshot yourself

The daily open-items count — though not because yesterday's backlog is unrecoverable. The confirmed table carries both CREATED_AT and CONFIRMED_AT, so the open set on any past date can be backfilled: tasks created on or before that date that were either still open or confirmed after it. The daily snapshot is the cheaper and simpler option, and it is the only one that also catches cancellations, which never reach the confirmed table at all and have to be read from the task log. Honouring the delete is what makes bronze correct; the daily count is what makes the aging queue cheap. Of the four problems, the EWM path solves the delete question outright and leaves the daily balance — here, the daily queue — as a lakehouse build.

Confirming a warehouse task deletes it →Raw table CDC and the ORDIM_O problem →

JD Edwards offers a genuinely complete answer and a tempting incomplete one, and the incomplete one is the default most estates reach for because it needs nothing but SQL.

JDE — the path

Change-feed replication: log-based CDC against the Oracle or SQL Server database underneath JDE, which the extraction guide names as the only complete answer. The UPMJ audit-column watermark is the fallback, and its limits are the three this page cares about: it is date-grain, not every program stamps it, and it captures no deletes at all.

JDE — what the source keeps

Closed sales lines are not deleted — they move. At sales update, lines leave F4211 for F42119, which looks like a delete in one table and an insert in the other. That is a relocation, not a vintage: the row that arrives in F42119is the line's final state, not a series of the states it passed through. The state history exists beside it rather than inside it: F42199, the sales order detail ledger, writes a history row every time an F4211 line changes, keyed by update date and time — so a line's revisions are source-side history, in a different file from the one the line ends up in. Neither of the two detail tables alone is “all sales,” and a watermark pull on either one cannot reconcile the move.

JDE — what you must snapshot yourself

The forecast vintage. F3460 holds forecast quantities keyed by forecast type, and the file is overwritten as the forecast is revised — so lag history is a lakehouse capture, made before anyone asks for accuracy by lag. Of the four problems, log CDC solves the delete question; the daily balance, the forecast vintage and the parameters all remain builds.

Log-based CDC paths →Poor-man's CDC: audit columns →

Dynamics 365 F&O is the closest any reference on this page comes to a solved change feed — deletes arrive in band, and some masters carry real effective dating. It still leaves three of the four problems open.

D365 — the path

Change-feed replication through the managed export feeds — Synapse Link and Fabric Link. Deletes do not remove rows: the row reappears carrying IsDelete = true, which the load translates into a physical delete (incremental & soft deletes). One operating limit belongs in the design rather than the runbook: the sink purges those deleted rows after a fixed window, so a pipeline that lags past it misses the delete signal entirely.

D365 — what the source keeps

Genuine date-effective history on some masters — a business key with several ValidFrom/ValidTo rows, one per version in force. The documented cases are employee positions, prices and exchange rates (date-effective tables), which is a narrower list than “masters carry history” suggests. Silver inherits that instead of inventing it, which is the one place on this page where source-side history is the real thing. Two preconditions, though, and the quirks guide states both: the platform update that exports expired rows has to be present, and a full refresh has to have been run, because the fix is not retroactive.

D365 — what you must snapshot yourself

Everything about the plan. InventSum is aggregated current state and InventTrans is the movement ledger, so the daily balance is the usual build. ReqTrans is regenerated by each planning run and stamped with a plan version, so filter to one plan version before aggregating or every regeneration double-counts. That version is a RecId and each regeneration gets a distinct one; what is reused is the plan name, so the id rather than the name is the vintage key — and the id exists only while that version is retained, which is why the cycle history is a dated capture rather than a query. ForecastSalesis keyed by forecast model and edited in place, so a model holds today's numbers under a name it has always had. Of the four problems, this path solves deletes and inherits date-effective history exactly where the source keeps it — not on the planning and tolerance parameters, whose per-module item settings are overwritten in place — so the daily balance, the forecast vintage, the plan of record and the parameter dimension are all yours.

Incremental & soft deletes →Date-effective tables →

M3 is the reference whose platform keeps the most history and gives you the least of what a metric needs — a distinction worth being precise about, because the two are easy to confuse.

M3 — the path

Change-feed replication, by one of two routes the deployment decides. Multi-tenant CloudSuite M3 gives no direct database access, so the Infor Data Lake is the path; on-prem or single-tenant M3 BE does give it, and replicating the database directly — commonly off a mirrored reporting copy — is the usual answer there. The Data Lake route is the one with platform semantics worth stating: publication is opt-in per table, and a published table streams its database events, so every update lands as a new variation rather than overwriting the previous one, with a delete indicator beside it (incremental & deletes). Two consequences the extraction guide is explicit about: history from before a table was subscribed does not arrive on its own, and what Compass returns depends on the object's query processing mode — analytical mode does the variation handling for you, transactional mode returns every ingested version, and the mode is set per object.

M3 — what the source keeps

Variation history per record. This is connector-side history in the exact sense of the section above: it records that a value changed and when the platform saw it change. It is not business SCD2, because nothing in a variation says which business-effective window the value was in force for, and the ordinary transaction tables — MITTRA for movements, MITLOC for balance by location — carry their own semantics underneath it.

M3 — what you must snapshot yourself

The effective dates. The planning parameters on MITBAL — the item/warehouse balance and policy record — carry no effective dating, so the variation stream tells you the value moved on the day the platform ingested it, which is not the day the business changed the rule. Of the four problems, this path solves deletes and gives you a usable input to the parameter dimension; it does not build it, and the daily balance and both plan problems remain builds.

Compass (Data Lake SQL) →Incremental & deletes →

Oracle EBS is the reference with the widest gap between the two paths available to it, and the one where the missing history is total rather than partial.

EBS — the path

Change-feed replication through log-based CDC — GoldenGate reads the redo logs of the database underneath EBS (CDC paths & batch pulls). The batch fallback is a watermark on LAST_UPDATE_DATE, and it fails in three documented ways: batch programs bulk-stamp rows, so “changed since yesterday” includes rows with no business change; the column is not reliably indexed on transaction tables; and a hard delete never stamps anything (WHO columns). Purge programs do physically remove rows, so this is not theoretical.

EBS — what the source keeps

The movement ledger and nothing else historical. MTL_MATERIAL_TRANSACTIONS is the event log, and MTL_ONHAND_QUANTITIES_DETAIL is current-state receipt slices — one row per receipt, consumed in FIFO order — so on-hand at any grain is a sum over those slices as they stand today. There is no historical on-hand quantity object at all — the nearest thing, the costing period close summary, sits at period × subinventory and carries value without quantity, tied to the general ledger close rather than to an operational balance. This is the strongest form of the daily-balance problem on this page: not a sparse history to interpolate, but nothing to interpolate from.

EBS — what you must snapshot yourself

The daily balance, entirely, from the transaction ledger against a trusted baseline. And both plan surfaces: MSC_PLANS is the plan registry and its output is regenerated per run, while MRP_FORECAST_DATESis edited in place — its current quantity is post-consumption, so even today's row is not the forecast as stated. Of the four problems, EBS solves none without a build.

CDC paths & batch pulls →WHO columns: audit stamps on every table →

Fusion is one of the two references on this page whose primary posture is snapshot-per-run, and the only one where the run belongs to an extract tool rather than to a refresh schedule you set — which plants a trap the others don't have.

Fusion — the path

Snapshot-per-run export. BICC extracts land as files per run: the first run of a store is a full extract, and later runs use the store's incremental key columns against a stored last-extract date (BICC mechanics). Two consequences are structural rather than incidental. The prune window deliberately re-delivers rows near the last extract date, so the load has to be an idempotent upsert deduped to the latest change per key — not an append. And incremental extracts are delete-blind: a purged row simply stops arriving, and the delivered answer is the active primary-key extract, anti-joined against your copy (incremental & deletes).

Fusion — what the source keeps

The same shape as EBS underneath — an event ledger in INV_MATERIAL_TXNS and current-state FIFO receipt slices in INV_ONHAND_QUANTITIES_DETAIL — plus date-effective windows on some objects, which are real history where they exist. Master data such as EGP_SYSTEM_ITEMS_B repeats per inventory organization, so the grain a snapshot arrives at is the grain BICC was configured to extract, not the grain a metric asked for.

Fusion — what you must snapshot yourself

The trap, stated plainly: the extract being a snapshot does not make it your daily balance. Its schedule is BICC's schedule and its grain is the store's grain, and neither is guaranteed to be daily or to be item × location. A file that arrives every run is a copy of the source as of that run, which is a different object from a dated balance fact keyed on the day it describes. Of the four problems, this path gives you a delete mechanism if you run the key extract, and leaves the rest.

BICC mechanics: offerings, stores, increments →Incremental & deletes →

NetSuite inverts the usual assumption. Incremental extraction is the exception here, not the default, and the arithmetic is stark: seven of the seventy records in this catalog carry a modification stamp.

NetSuite — the path

Snapshot-per-run export, in the form of a scheduled full refresh over the single analytics door: sixty-three of the seventy records carry no modification stamp, so re-reading them whole is the design rather than a fallback (incremental loads & the full-refresh list). Watermark pulls are the secondary path, available on the seven stamped records, and where a watermark exists it catches inserts and updates only.

NetSuite — what the source keeps

Documents, on one spine. transaction carries every document type, and it is one of the seven records with a modification stamp. deletedrecord is the deletion ledger — it names the record type, the name, and who deleted it and when, but exposes no internal id, so it is a detection surface rather than a key set, and it records deleted records rather than deleted lines (delete blindness at line grain). Remove one line from a surviving order and nothing is written there at all.

NetSuite — what you must snapshot yourself

The costing outputs and the balances on inventoryitemlocations are overwritten in place, and so are the policy fields beside them — the reorder point, the safety stock, the lead times — so the daily balance and the parameter history are both builds. The plan is the interesting case: itemdemandplan is a storedplan row rather than a calculation regenerated per run, which makes NetSuite the one reference here whose demand plan persists as its own record. It carries the plan's method and buckets, not the date the plan was issued — so the vintage is still yours to capture, and the record has no modification stamp, so it is a full-refresh table.

Incremental loads, deletes, and the full-refresh list →Delete blindness at line grain →

Nothing new here — every cell restates a section above. Read the last two columns together: the third is what arrives, and the fourth is the work that is still outstanding once it has.

Six of the eight land in the change-feed posture and two in snapshot-per-run — and the last column is nearly identical across all of them, which is the point of the page.

These are cheap, they are runnable against a non-production source in an afternoon, and each one corresponds to a failure that is silent in production.

1. Insert, update, delete. One keyed row through all three operations, and confirm all three arrive.

2. Tied cursor values. Two updates carrying an identical cursor value — which one wins, and is the answer stable across reruns.

3. A late update. A change whose business timestamp is older than rows already landed.

4. A schema change. A source column added, a column renamed, and a column dropped.

5. An outage past retention. Longer than the log or API retention window — what does recovery actually do.

6. A full refresh after a partial snapshot. Issued once an initial snapshot has failed part-way through.

7. A replay. Whether re-running the load produces duplicate facts downstream.

8. SCD2 closure. Row closure and current-row uniqueness, asserted rather than assumed.

9. A malformed payload. Where it lands, and whether anyone hears about it.

10. Counts and checksums. Source-to-destination, by time window — the only one of the ten that keeps working after go-live.

The rule: run them for each connector × source combination. Product-family documentation cannot prove source-specific correctness, and a connector that passed all ten against one source has demonstrated nothing about the next one. The checks that keep running afterwards — the ones that catch a bad load before a dashboard does — are the data quality checklist.

The decision, per source object, is three steps. Answer the seven questions. Then pick the posture the source's change semantics allow — not the one you would prefer. Then name which of the four problems remain. That third list is the deliverable, and it is not a set of ingestion settings: it is a set of silver-layer builds with owners and start dates.

The daily balance table. Item × location × day, scheduled, started before anyone asks for it — the flows are ground truth and the stock is their integral. The patterns are in inventory snapshot patterns.

The delete interpretation. What a missing row means in this source, and the mechanism that detects it — per reference, in ERP deletes & change tracking.

The effective-dated parameter dimension, and the forecast and plan vintage facts. Tolerances, reorder points and lead times as type-2 rows with a lakehouse-assigned effective-from date; the forecast keyed on target period and as-of date; the plan version governance table. Which layer owns each is the medallion layer contracts guide.

And the division of labour that holds across all of them: bronze keeps the connector metadata and the replay evidence; silver constructs the history. Bronze preserves source identifiers, extraction timestamps, and rescued or unparsed content, and does no business deduplication, because deduplication destroys the evidence a replay is checked against. Silver normalizes keys and units and time zones, interprets the change feed and the deletes, deduplicates by change identity, and builds the four things above. A pipeline that is green and a metric that is computable are two different achievements, and only the second one is the job.