Ingest and Transform Data · Topic 1 of 6
Loading Patterns: Full, Incremental, Dimensional and Streaming
DP-700 loading patterns: full and incremental loads with watermarks and MERGE, slowly changing dimensions, surrogate keys and a streaming load pattern.
- 5 min
- Includes a diagram
- Full loads are simple but costly; incremental loads need a watermark or CDC.
- MERGE applies upserts to Delta tables; CDC is needed to propagate deletes.
- Load dimensions before facts and replace business keys with surrogate keys.
What it is
A loading pattern is the repeatable way data moves from a source into its target. A full load replaces the whole target each run; an incremental load moves only new or changed rows since the last run.
Loads usually pass through layers, often called bronze (raw), silver (cleaned) and gold (modelled for reporting). The gold layer is commonly a dimensional model: fact tables of measurable events and dimension tables that describe them.
How it works
Full and incremental loads
| Pattern | How it works | When to use |
|---|---|---|
| Full load | Truncate and reload, or overwrite the table | Small tables, sources without a change column, first load |
| Incremental by watermark | Read rows where a column such as ModifiedDate is greater than the last value loaded | Sources with a reliable change column |
| Change data capture (CDC) | Read inserts, updates and deletes from the source's change log | Sources that must also propagate deletes |
| Append only | Add new rows; never update | Logs, events, immutable facts |
A watermark is stored after each run, for example in a control table. A pipeline reads it with a Lookup activity, filters the source query with it, and writes the new maximum at the end. The Copy job item has incremental copy built in, so it can track the watermark for you.
Incremental changes are applied to a Delta table with MERGE, which updates matching keys and inserts new ones (an "upsert").
from delta.tables import DeltaTable
from pyspark.sql import functions as F
last = spark.sql("SELECT max(modified_at) AS m FROM silver_orders").first()["m"]
changes = spark.read.table("bronze_orders").where(F.col("modified_at") > F.lit(last))
target = DeltaTable.forName(spark, "silver_orders")
(target.alias("t")
.merge(changes.alias("s"), "t.order_id = s.order_id")
.whenMatchedUpdateAll()
.whenNotMatchedInsertAll()
.execute())Downstream layers can read only what changed in a Delta table by enabling the change data feed (delta.enableChangeDataFeed = true) and reading with option("readChangeFeed", "true").
Prepare data for a dimensional model
| Step | Why |
|---|---|
| Clean and conform in silver | One spelling of each value, correct data types, duplicates removed |
| Generate surrogate keys for dimensions | Integer keys independent of source system keys |
| Load dimensions before facts | Facts need the dimension keys to exist |
| Look up surrogate keys while loading facts | Replace business keys such as CustomerID with CustomerKey |
| Add an unknown member (for example key -1) | Facts whose dimension value is missing still load |
Slowly changing dimensions (SCD) decide what happens when a dimension attribute changes.
| Type | Behaviour | History |
|---|---|---|
| Type 1 | Overwrite the old value | None |
| Type 2 | Close the current row and insert a new row, with ValidFrom, ValidTo and IsCurrent columns | Full |
A Type 2 load compares incoming rows with current rows on the tracked attributes. Changed rows get IsCurrent = 0 and a ValidTo date, and a new version is inserted with a new surrogate key.
A loading pattern for streaming data
Streaming data is loaded continuously and is normally append only. A typical Fabric pattern:
- Eventstream captures events and routes them to an Eventhouse, a lakehouse or both.
- In an Eventhouse, an update policy transforms each new batch from a bronze table into a silver table, and a materialized view keeps aggregates up to date.
- Turning on OneLake availability exposes Eventhouse tables as Delta tables in OneLake for Spark and SQL users.
- Alternatively, Spark structured streaming appends to a lakehouse Delta table, tracking progress in a checkpoint folder.
A worked example
A sales source has an Orders table with a reliable LastModified column and a Customers table whose city changes must be kept for history.
Orders are loaded incrementally: a Lookup reads the watermark, the Copy activity copies rows with a later LastModified, and a notebook merges them into silver. Customers become a Type 2 dimension: each city change closes the old row and adds a new one with a new CustomerKey, so old orders still report against the city at the time of sale.
How the exam asks about this
- "Load only rows changed since the last run." An incremental load with a watermark column.
- "Keep history of a customer's previous addresses." SCD Type 2.
- "Facts arrive with a product code that is not in the dimension yet." Load them against an unknown or inferred member, then fix the key when the product arrives.
What to remember for the exam
- Full loads are simple but costly; incremental loads need a watermark or CDC.
- MERGE applies upserts to Delta tables; CDC is needed to propagate deletes.
- Load dimensions before facts and replace business keys with surrogate keys.
- SCD Type 1 overwrites; Type 2 adds a new row with validity dates and a current flag.
- Streaming loads are append only; update policies and materialized views shape them in an Eventhouse.
An incremental load by watermark never sees rows deleted at the source. If deletes matter, use CDC or a periodic full comparison.
Common mistakes
Updating the watermark before the load succeeds. If the copy fails, the next run skips the missed rows. Write the new watermark as the last step.
Using business keys as the fact table's foreign keys in an SCD Type 2 model. A business key matches several dimension versions; use the surrogate key.
Updating rows in a streaming target. Keep the streaming layer append only and correct data in a later layer.
Check yourself
What does a watermark store in an incremental load?
The highest change value already loaded, such as the latest
ModifiedDate, so the next run reads only later rows.Which SCD type keeps full history of attribute changes?
Type 2, which inserts a new row for each change and closes the previous one with an end date and current flag.
Which Eventhouse feature transforms new rows from a bronze table into a silver table as they arrive?
An update policy on the target table.
Topic 1 of 6 in Ingest and Transform Data
Test yourself on Ingest and Transform Data
The full Microsoft DP-700 question bank has 645 questions, with timed practice exams and an explanation for every answer.
This study guide is independent and is not affiliated with, endorsed by, or sponsored by Microsoft. Vendor names, certification names and exam codes are trademarks of their respective owners and are used for identification only.