Fabric Loading Patterns: Full, Incremental, and Dimensional Model Loads

Choose a full load when the dataset is small or the source cannot tell you what changed; choose an incremental load when the source is large and changes can be identified reliably. A full load truncates (or overwrites) the target and rewrites every row on every run — simple, self-healing, and idempotent, but the cost grows linearly with source size and it burns capacity re-processing data that never changed. An incremental load extracts only rows created or modified since the previous run, so runtime stays proportional to the change volume rather than the table size.

The trade-off is complexity. Incremental loads need a way to detect change (a watermark column or Change Data Capture), somewhere to store load state between runs, and a strategy for applying changes to the target (append or merge). They also need a recovery story: if state is lost or logic drifts, you typically fall back to a one-off full reload to re-baseline.

A full load only removes deleted rows if it actually replaces the target. Loading a full snapshot with an insert-and-update MERGE keeps every row the source has dropped, because the merge never visits them — see the section on propagating deletes.

In practice a solution mixes both: full-load the small dimension sources nightly, incrementally load the large fact sources, and keep a documented full-reload path for recovery.

A watermark (high-water-mark) load filters the source on a column whose value only increases when a row is inserted or changed — typically a last-modified timestamp, a ROWVERSION or an ever-increasing ID — and pulls only rows above the value recorded by the previous run. It has two prerequisites: the column must be maintained reliably on every insert and update, and the last watermark must be persisted somewhere (a control table) between runs.

The safe pattern brackets each run with two bounds:

1. Read the old watermark from the control table.
2. Capture the new upper bound from the source — SELECT MAX(modified_at) — before copying.
3. Extract the rows between the two: modified_at > old AND modified_at <= new.
4. Save the new bound only after the load succeeded.

In a Fabric data pipeline that is two Lookup activities (old watermark from the control table, new upper bound from the source), a Copy activity whose source query uses both values, and a Stored procedure activity (against a Warehouse) or Notebook activity that writes the new value. The last activity must hang off the Copy activity's On success dependency. An Upon completion dependency also fires when the copy fails, so the watermark would advance past rows that were never loaded and the next run would skip them for good.

Why the clock and the "last row written" are unsafe watermarks

A copy query reads the rows that exist when it reads them. Rows updated while a long copy is still running get a modified_at later than the moment they were (or were not) read, but earlier than the clock time when the copy finishes. If you save the clock as the new watermark, those rows sit below it and are never extracted. The value of the last row the copy happened to write is no better: copy order, and the order of parallel writes, is not the order of modified_at, so the last row written is usually not the newest row extracted, and a bound taken from it is effectively arbitrary. Take the bound from the source before the copy (or the maximum of the extracted set itself), and filter up to it.

Late-committing transactions

Some applications stamp modified_at when a transaction starts, then commit minutes later. Such a row becomes visible with a value already below the watermark the previous run saved, so a strict > old filter never sees it. The fix is a lookback window: re-read from the old watermark minus a safety interval at least as long as the longest open transaction. The overlap re-delivers rows already loaded, so the apply step must be a keyed MERGE (an upsert), never an append, or every overlapping row is duplicated.

In a notebook the same pattern is a few lines of PySpark:

old_wm = spark.sql(
  "SELECT max_value FROM control.load_watermarks WHERE table_name = 'orders'"
).first()[0]
new_wm = spark.read.table("bronze.orders").agg(max("modified_at")).first()[0]

changed = (spark.read.table("bronze.orders")
  .filter((col("modified_at") > lit(old_wm)) & (col("modified_at") <= lit(new_wm))))
# apply with MERGE, then persist new_wm

A plain watermark also cannot see hard deletes: a row removed at the source simply stops appearing. If deletes matter, you need Change Data Capture, soft-delete flags, or reconciliation against the source's keys.

Fabric gives you two low-code alternatives to hand-building the watermark pipeline: Copy job, which tracks incremental state for you across many tables, and Dataflow Gen2 incremental refresh, which refreshes a single query bucket by bucket.

Copy job incremental copy

In incremental copy mode, a Copy job's first run copies everything; every later run moves only new or changed rows. Change is detected either from a watermark column (such as ROWVERSION, datetime, date, integer, or a string interpreted as a datetime) or from CDC when it is enabled on the source — and only CDC carries deletes. The job stores the state of the last successful run itself, so there is no control table to build; a failed run does not move that state, and the next run resumes from the end of the last successful one. You choose how rows are written: append (the default), merge on a key column (insert or update; with CDC, delete too), overwrite, or SCD Type 2 (a preview option that works with CDC replication and turns source deletes into soft deletes). One Copy job can cover many tables, which makes it the least-effort choice when dozens of tables each have a reliable change column.

Dataflow Gen2 incremental refresh

Incremental refresh is configured per query in a Dataflow Gen2. You pick a Date, DateTime or DateTimeZone column to filter on, how far back to extract, and a bucket size (day, month and so on). On each refresh the dataflow checks the maximum value of a change-detection column per bucket; a bucket whose maximum changed is extracted in full and replaced in the destination (its old rows deleted, then the fresh rows inserted), while unchanged buckets are skipped. Replace is the only update method — there is no row-level merge or append. Destinations that support it directly are Lakehouse, Warehouse and Azure SQL Database, and a query can have at most 50 buckets, the whole dataflow 150.

Change Data Capture (CDC) is change detection done by the source system itself: instead of you inferring changes from a timestamp column, the source's transaction log yields a record of every insert, update and delete, each tagged with an operation type and a sequence number (for SQL Server, a log sequence number, LSN). That closes the gaps a watermark leaves open — deletes are visible, and every intermediate change is captured. In Fabric you meet CDC as mirroring (which keeps a OneLake replica current from the source's change feed), as Copy job CDC-based incremental copy, and as Delta change data feed (CDF).

Be precise about CDF: it records the changes made to that Delta table — the inserts, updates and deletes your own writes performed — so that downstream layers (silver to gold) can process them incrementally. It knows nothing about the upstream source. If a row is hard-deleted in the source database and your load never deletes it from the Delta table, CDF has nothing to report.

Whatever produced the change set, applying it to a table is a MERGE — the upsert statement that matches incoming rows to target rows on a business key and applies the right action per row in one atomic operation. MERGE exists both in Spark SQL for Lakehouse Delta tables and in T-SQL in a Fabric Warehouse. The example below is Spark SQL: the UPDATE SET * and INSERT * shorthands are Delta Lake syntax, and the Warehouse T-SQL version lists the columns explicitly.

MERGE INTO silver.orders AS t
USING latest_changes AS s
  ON t.order_id = s.order_id
WHEN MATCHED AND s.op = 'D' THEN DELETE
WHEN MATCHED THEN UPDATE SET *
WHEN NOT MATCHED AND s.op <> 'D' THEN INSERT *

Deduplicate to the latest change per key first

A change log holds one row per change, not one row per key. An order updated twice since the last run appears twice, both rows match the same target row, and MERGE fails ("multiple source rows matched"). Reduce the change set to the last change per key, ordered by the source's sequence number (LSN, or commit timestamp if that is all you have) — and keep that last row even when it is a delete, so the delete is applied. dropDuplicates(["order_id"]) is not a fix: it keeps an arbitrary row, which may be the older version.

w = Window.partitionBy("order_id").orderBy(col("lsn").desc())
latest = changes.withColumn("rn", row_number().over(w)).filter("rn = 1")

A load reflects source deletes only if something tells it which rows disappeared. Which mechanism you use depends on what the source gives you.

• The source is a full snapshot. A MERGE that only has WHEN MATCHED THEN UPDATE and WHEN NOT MATCHED THEN INSERT never visits rows missing from the snapshot, so rows the source no longer contains stay in the target forever. Add WHEN NOT MATCHED BY SOURCE THEN DELETE, which acts on target rows with no source match. It is supported in Fabric Warehouse T-SQL and in Delta Lake. Use it only when the batch is the complete dataset: on an incremental batch it would delete every row that simply did not change.
• The source soft-deletes (sets IsDeleted = 1 and bumps modified_at). The watermark picks the flagged rows up, so handle them in the merge: WHEN MATCHED AND s.IsDeleted = 1 THEN DELETE removes rows loaded earlier, and the insert clause must exclude flagged rows (WHEN NOT MATCHED AND s.IsDeleted = 0 THEN INSERT) because a row the target never received has nothing to delete and must not be inserted either.
• The source has CDC. Apply the delete operations from the deduplicated change set, as in the previous section.
• No CDC, no flag, too big to reload. Reconcile keys: copy just the source's key column (narrow and cheap even for large tables), anti-join the target against it to find keys that no longer exist, and delete those rows. Run it on whatever schedule the business tolerates for stale deletes.

A load is idempotent when running it twice with the same input leaves the target exactly as running it once — so a retried pipeline, a re-delivered file or an overlapping lookback window cannot corrupt the data. Design every load for it, because retries happen.

The last row matters when rows have no unique key — for example web-server log lines, where two identical requests in the same second are genuinely two hits. A key-based MERGE would collapse genuine rows, and in-batch deduplication cannot stop cross-run duplicates. If each run loads exactly one slice (say one hour of logs, tagged LogHour), make the run replace that slice: in a Lakehouse, write with Delta's replaceWhere option (.mode("overwrite").option("replaceWhere", "LogHour = '2026-10-01T09'")); in a Warehouse, delete that slice and insert the batch inside one transaction.

Preparing data for a dimensional model means reshaping source tables into a star schema: a central fact table of numeric, additive measurements surrounded by dimension tables of descriptive attributes. Facts answer "how much, how many" (order amount, quantity, duration); dimensions answer "who, what, where, when" (customer, product, store, date). Each fact row carries foreign keys to its dimensions, and analytical queries join the fact to a handful of dimensions and aggregate.

The preparation work happens in the silver-to-gold transformation, whether you run it in a notebook or with T-SQL in a Warehouse:

• Split entities. A denormalized source extract with customer and product columns repeated on every order line becomes one fact table plus distinct customer and product dimensions.
• Conform dimensions. If sales and returns both reference products, they must share a single product dimension so results reconcile across facts.
• Flatten hierarchies into the dimension. Product → subcategory → category becomes columns of one product dimension. The alternatives are a snowflake (separate subcategory and category tables chained by keys), which adds joins and relationships for no analytical gain, and one big table (attributes copied onto every fact row), which bloats the fact, repeats attribute changes across millions of rows and cannot be shared across facts. A flattened star dimension is the target for Power BI semantic models.
• Declare grain. Fix exactly what one fact row represents — one order line, one order, one daily snapshot — before writing any load logic; every measure and key must be consistent with that grain.
• Build a date dimension. A generated calendar table with one row per day supports time intelligence and consistent fiscal attributes.

Grain decides what happens to data recorded at another level. A header-level measure such as a single discount applied to a whole invoice does not fit an invoice-line fact as is: repeating it on every line overstates every total, and putting it on only the first line makes it impossible to analyse by product. Allocate it to the lines by a stated rule (proportional to line amount, quantity or weight) so it becomes a line-grain measure that sums correctly by any dimension — or, if nobody needs it below invoice level, keep it in an invoice-grain fact. Data at a genuinely different grain, such as quarterly sales targets by region, gets its own fact table joined to the shared (conformed) date and geography dimensions at the coarser level; it is never squeezed into the sales fact.

In Fabric the gold star schema typically lives as Delta tables in a Lakehouse or tables in a Warehouse, and a semantic model in Direct Lake mode sits on top for Power BI. The cleaner the star schema, the simpler and faster that semantic model is — dimensional preparation is as much a modeling task as a loading task.

A surrogate key is a warehouse-generated integer that identifies a dimension row and replaces the source's business (natural) key as the join key from facts. You need one for three reasons: business keys collide when you integrate several sources, business keys get reused or change format, and — decisively — tracking history requires several rows for the same business key, which only a per-row key can distinguish. Surrogate keys are a modeling choice, not a platform requirement: Fabric Warehouse lets you declare a primary key on whichever column identifies the row, including a business key.

Keys in a Fabric Warehouse

• Constraints are declared NOT ENFORCED. Primary keys (which must be NONCLUSTERED), unique constraints and foreign keys are metadata only, e.g. ALTER TABLE dbo.DimCustomer ADD CONSTRAINT PK_DimCustomer PRIMARY KEY NONCLUSTERED (CustomerKey) NOT ENFORCED;. The engine does not reject duplicates or orphans, so the load must guarantee them.
• IDENTITY is BIGINT only, with no seed or increment: CustomerKey BIGINT IDENTITY. The values are unique but not sequential and can have gaps, because the warehouse allocates them in parallel. Specifying INT or IDENTITY(1,1) fails. Never rely on key order to mean insert order.
• In a notebook, assign keys yourself — for example row_number() over the new rows, added to the dimension's current maximum key.

Slowly changing dimension types

The slowly changing dimension (SCD) type answers one business question: when an attribute changes at the source, does the old value still matter?

A single dimension can mix types per attribute. The rule for a Type 1 attribute in a Type 2 dimension: update it on every version row of that business key, not just the current one — otherwise reports by that attribute split the same member across old and new values.

The trigger phrases are explicit: "region at the time of the sale" means Type 2; "only the latest value matters" or "the old value was wrong" means Type 1.

A Type 2 dimension needs two things per row: a surrogate key per version and effective dates (ValidFrom, ValidTo, with an open or far-future ValidTo on the current version). An IsCurrent flag is an optional convenience for filtering — the current version is already the one whose end date is open. The business key stays a plain attribute; it cannot be the primary key any more, since several rows share it.

The load compares incoming attributes with each business key's current row. For changed keys it expires the current row (sets ValidTo, clears IsCurrent) and inserts a new version with a new surrogate key; new keys are simply inserted.

The single-MERGE trap

A MERGE with WHEN MATCHED AND attributes differ THEN UPDATE (expire) and WHEN NOT MATCHED THEN INSERT expires changed rows but never inserts their new versions: each source row takes exactly one branch, and a changed key always matches. Two fixes work:

• Stage changed rows twice. Union the changes with a second copy of the changed rows whose merge key is NULL. The real-key copy matches and expires the old row; the NULL-key copy cannot match anything, so it takes the insert branch. This is the documented Delta Lake pattern for SCD Type 2 in one statement.
• Run a separate insert after the MERGE (or an UPDATE then an INSERT in one Warehouse transaction).

Dimensional history vs time travel

Fabric Warehouse time travel (OPTION (FOR TIMESTAMP AS OF '…') on a query) and Delta time travel read a whole table as it was at a past point, within a limited retention period. That is a recovery and audit tool, not dimensional history: it cannot tell a fact which customer version applied on its own date, and it expires. Use Type 2 rows when reports need attributes as of each fact's date.

Dimensions load before facts — that keeps a star schema consistent. The fact load looks up each incoming business key in the dimension and writes the resulting surrogate key into the fact row; if facts loaded first there would be nothing to look up against.

Against a Type 2 dimension the lookup must use the business key and the fact's own date: pick the version where ValidFrom <= fact date < ValidTo. Looking up only the current row (IsCurrent = 1) works for today's facts but attaches a late-arriving fact — a sale from three weeks ago loaded today — to the customer's current region rather than the region at the time of the sale.

SELECT f.*, d.CustomerKey
FROM staged_sales f
JOIN dim.Customer d
  ON d.CustomerID = f.CustomerID
 AND f.OrderDate >= d.ValidFrom AND f.OrderDate < d.ValidTo

The rule breaks when a fact references a dimension member that does not exist yet — a sale for a product created minutes ago that the nightly product load has not seen. This is the late-arriving dimension member (early-arriving fact) problem, with three standard responses:

• Infer the member. Insert a placeholder row containing just the business key (attributes defaulted to "Unknown", an IsInferred flag set), take its new surrogate key, and load the fact now. When the real attributes arrive, update that placeholder row in place and clear the flag. Do not create a new Type 2 version: the facts already loaded point at the placeholder's key, and the arrival of real data is a completion, not a change over time. This is the usual production answer because no data is delayed or dropped.
• Park the fact. Divert unmatched fact rows to a quarantine table and retry them on later runs. Nothing incorrect enters the model, but measures are understated until the retry succeeds.
• Map to a permanent Unknown member. Point the fact at a reserved "Unknown" row. Totals are right immediately, but the fact is never reattributed unless you add a repair step.

Fabric Warehouse foreign keys are declared NOT ENFORCED, and Lakehouse Delta tables have no foreign keys at all — so referential integrity in a Fabric star schema is entirely the loading pattern's job. The lookup-and-infer logic is the constraint.

A streaming loading pattern differs from batch in one fundamental way: it is append-only. Events arrive continuously, each is an immutable statement of something that happened, and the load appends them as they come — there is no truncate-and-reload and no per-run watermark bookkeeping, because the stream's own offset tracking plays that role. A correction is itself an event: the source sends a new event for the same key, both are kept, and readers resolve the latest one downstream (for example by event time). Nothing is updated in place at the landing edge.

Choosing the landing store

An Eventstream ingests from a source such as Azure Event Hubs and lands raw events into a bronze layer. Pick the destination by what consumes it:

• Eventhouse (KQL database) when people query recent telemetry ad hoc and need data within seconds of arrival — it is built for high-rate ingestion and fast queries over time-series events.
• Lakehouse Delta table when the events feed analytical lake workloads (Spark, SQL, Direct Lake). The Eventstream Lakehouse destination buffers events and writes them in batches, so freshness is minutes, and it avoids writing a flood of tiny files.

Deduplicating at-least-once events

Most event sources deliver at least once, so duplicates are normal and are removed downstream, keeping the raw table intact. In an Eventhouse the right tool is a materialized view that keeps one row per event key:

.create materialized-view EventsDedup on table RawEvents
{
  RawEvents | summarize take_any(*) by EventId
}

The view is maintained in the background across everything ingested, so queries read an already-deduplicated result. A KQL update policy cannot do this job: it runs on each newly ingested batch and sees only that batch, so a duplicate that arrives in a later batch slips through. Update policies are for per-batch transformation (parsing, reshaping) into a silver table. On the Lakehouse side, Spark structured streaming does the equivalent silver step, still appending micro-batches.

Only at gold does the pattern converge with batch again: aggregating events into dimensional summaries can involve MERGE-style updates to serving tables, typically on a short schedule rather than per event. If a scenario says data "arrives continuously" and asks for a landing design, the answer appends to bronze without transformation; any option that updates rows in place at ingestion, or reloads the landing table, is wrong. Windowed aggregation and engine choice belong to the streaming-transformation topic.

Put the patterns together. A retailer runs Fabric with a medallion Lakehouse: a transactional database holds 400 million order lines with a reliable modified_at column, a product catalog of a few thousand rows changes rarely, customer records change often and the business wants history of region moves, and point-of-sale telemetry streams through Azure Event Hubs.

The design that DP-700 expects:

• Order lines — incremental. Far too large to reload nightly, and modified_at gives a usable watermark. A pipeline looks up the stored watermark and the source's current maximum modified_at, copies the rows between them to bronze, and a notebook MERGEs them into silver on order_line_id after keeping the latest row per key. The new watermark is saved on the Copy activity's success path, so a failed run is simply repeated — the merge makes that safe.
• Product catalog — full load. Thousands of rows: a truncate-and-reload every night is cheaper than maintaining incremental machinery, and it self-heals from any drift.
• Customer dimension — incremental with SCD Type 2. The history requirement forces Type 2: changed customers get their current row expired and a new surrogate-keyed row inserted; the fact load resolves each sale's surrogate key by customer ID and sale date, so old sales keep their old region.
• Point-of-sale stream — append-only. An Eventstream lands raw events in bronze; structured streaming conforms them into silver; a scheduled job aggregates into gold. No merges at the landing edge.
• Load order. Dimensions before facts, with inferred members covering products that sell before the catalog load has seen them.

Every choice traces back to three questions the exam keeps asking: how big is it, can you detect change, and does history matter?