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 each reference’s extraction guide, which this page 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.

Shorthand on this page: CDC is change data capture; SCD2 is slowly changing dimension, type 2.

Every source 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 Oracle estates are read against their own path families — see the SAP extraction guide and the EBS extraction guide — rather than against 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 in place 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.

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.