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.

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 monotonically increasing column — typically a last-modified timestamp or an ever-increasing ID — pulling only rows above the value recorded by the previous run. The pattern has four steps: read the stored watermark, extract source rows where the tracking column exceeds it, load those rows into the target, then persist the new maximum value for the next run.

In a Fabric data pipeline you implement this with a Lookup activity that reads the stored watermark from a control table, a Copy activity whose source query filters on it, and a final activity (a Stored procedure activity against a Warehouse, or a Notebook activity) that updates the control table. In a notebook the same pattern is a few lines of PySpark:

last_wm = spark.sql(
  "SELECT max_value FROM control.load_watermarks WHERE table_name = 'orders'"
).first()[0]

changed = (spark.read.table("bronze.orders")
  .filter(col("modified_at") > lit(last_wm)))

changed.write.mode("append").saveAsTable("silver.orders_staged")

Two details matter on the exam. First, capture the new watermark from the source data you actually extracted (its MAX(modified_at)), not the current clock time — otherwise rows committed between extraction and bookkeeping are silently skipped. Second, a plain watermark 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 periodic full reconciliation.

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 stream of insert, update, and delete events, each tagged with an operation type. That closes the two gaps a watermark leaves open — deletes are visible, and intermediate changes are captured even if a row changed multiple times between runs. In Fabric you encounter CDC in several forms: mirroring uses source change feeds to keep a OneLake replica current, pipeline copy sources for databases with CDC enabled can extract change sets, and Delta tables expose their own change data feed downstream consumers can read.

Whatever produced the change set, applying it to a Delta table in a Lakehouse is a MERGE — the upsert primitive that matches incoming rows to target rows on a business key and applies the right action per row in one atomic operation:

MERGE INTO silver.orders AS t
USING staged_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 THEN INSERT *

If the change set can contain several versions of the same key (common with CDC), deduplicate it first — keep only the latest change per key — because MERGE fails when multiple source rows match one target row. A run that grades you on this usually hides that detail in the scenario: "the merge intermittently fails when the same order is updated twice" points straight at pre-merge deduplication.

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.
• 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.

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 key that identifies a dimension row, replacing the source system's business (natural) key as the join key from facts. You need them for three reasons: business keys collide when you integrate multiple sources, business keys can be reused or change format, and — decisively — tracking history requires several rows for the same business key, which only a surrogate key can distinguish. In Fabric you generate them with an IDENTITY-style pattern in Warehouse T-SQL or with functions such as row_number() over existing keys (or a key-assignment merge) in a notebook.

Slowly changing dimensions (SCD) define what happens when a dimension attribute changes at the source:

• Type 1 — overwrite. Update the attribute in place. History is lost; every fact, past and future, reports the new value. Correct for fixing errors and for attributes where history has no analytical value.
• Type 2 — add a row. Expire the current row (set its end date, mark it not current) and insert a new row with a new surrogate key, an effective-from date, and a current flag. Facts loaded before the change keep pointing at the old surrogate key, so historical reports remain true to what was known at the time.

A Type 2 load is naturally expressed as a Delta MERGE in two passes — one to expire matched-and-changed current rows, one to insert their replacement rows — driven by comparing incoming attribute values against the current row per business key. The exam's trigger phrase is explicit: "reports must show the customer's region at the time of the sale" means Type 2; "only the latest value matters" means Type 1.

Dimensions load before facts — that is the rule that keeps a star schema consistent. The fact load looks up each incoming business key against the dimension (taking the current row's surrogate key for a Type 2 dimension) and writes the surrogate key into the fact row. If facts loaded first, there would be nothing to look up against and fact rows would carry unresolvable keys.

The rule breaks when a fact arrives referencing 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 (early-arriving-fact) dimension problem, and you have three standard responses:

• Infer the member. Insert a placeholder dimension row containing just the business key (attributes defaulted to "Unknown"), take its new surrogate key, and load the fact now. When the real dimension data arrives, a Type 1 update fills in the attributes. 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 subsequent 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" dimension row. Totals are right immediately, but the fact is never reattributed unless you add a repair step.

Fabric Warehouse foreign keys are informational (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. Corrections are handled by appending compensating events or by deduplicating downstream, not by updating rows in flight.

In Fabric the pattern maps onto the medallion architecture. An Eventstream ingests from a source such as Azure Event Hubs and lands raw events into a bronze layer — an Eventhouse KQL database when the workload is real-time analytics over telemetry, or a Lakehouse Delta table when the destination is the analytical lake. Bronze is append-only and keeps events essentially as received. The silver step — Spark structured streaming in a notebook, or KQL update policies in an Eventhouse — validates, deduplicates, and conforms the events, still writing by 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.

The exam signal to watch for is the mutation model. 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 for streaming. Windowed aggregation and streaming-engine choice belong to the streaming topic proper — here, the tested skill is the shape of the load.

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, copies changed rows to bronze, and a notebook MERGEs them into silver on order_line_id after deduplicating the batch per key. The control table is updated from the extracted data's maximum modified_at.
• 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; facts join on the surrogate key 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?