Domain 2 of 3 · Chapter 1 of 3

Design and implement loading patterns

Unlock the complete study guide.

Bundled into the existing Implementing Data Engineering Solutions Using Microsoft Fabric premium course — no separate purchase.

14-day money-back guarantee — no questions asked.

Included in this chapter:

  • The three decisions behind every load
  • Full loads: replace, stage, then publish
  • Incremental loads and their change signals
  • Preparing data for a dimensional model
  • Fact table types and measure additivity
  • Slowly changing dimensions in the load
  • Loading patterns for streaming data
  • Exam patterns and the traps that decide them

The three decisions across four Fabric loading engines

Decision axisCopy jobPipeline copy activity with a watermark tableDataflow Gen2 incremental refreshSpark Structured Streaming
Copy modeFull copy, or incremental copy driven by a watermark column or source CDC; resettable per job or per table. Only the CDC form detects deletes.Full, or incremental with bounds you compute yourself from lookup activities. Deletes only if the source query exposes them.Incremental over time buckets, after an initial extraction horizon set by Extract data from the past. Buckets are replaced, never diffed, so deletes are not detected.Continuous micro-batches; the run-level full-versus-incremental choice does not apply. Deletes only if delete events are in the stream.
Update method at the destinationAppend by default, or overwrite, merge on a key, or SCD Type 2 with effective dating.Whatever the destination and your own T-SQL implement, from plain insert to a MERGE statement.Replace, and only replace, applied to each changed bucket.Delta append via outputMode, or a merge you write explicitly in the notebook or job.
Progress stateHeld by the service from the last successful run; a failed run leaves it unchanged.A watermark row you store in a table and update from a stored procedure after the copy succeeds.The maximum value of the change-detection column, compared per bucket against the previous refresh.The checkpoint location written to lake storage alongside the Delta output.

Decision tree

Continuous event feed? Yes Streaming load pattern checkpoint holds the progress Spark job definition, with retries No Usable change signal? watermark, CDC, or file time No Full copy with overwrite no change state to keep rebuilds the whole target Yes Must deletes reach the target? Yes Incremental copy with CDC change data capture feed merge or SCD Type 2 No Can arriving rows update keys? Yes Incremental copy with merge upsert on the business key one current row per key No Incremental copy with append each batch added as new rows never updates a prior row
Cheat sheet

Cheat sheet

  • Use a full load when replacing the complete dataset is acceptable
  • Complete the initial snapshot before applying incremental changes
  • Match destination replacement behavior to full-load semantics
  • Publish a full refresh only after the replacement data is ready
  • Never truncate and reload a generated-surrogate-key dimension
  • Choose a monotonic change column for watermark loads
  • Bound each incremental extraction with old and new watermarks
  • Advance the saved watermark only after the batch succeeds
  • Merge changed rows when an increment can update existing keys
  • Use file modification metadata for new-or-changed file ingestion
  • Use Copy job for a guided full-then-incremental load
  • Configure Dataflow incremental refresh around replaceable time buckets
  • Declare a fact table grain before loading measures
  • Load dimensions before resolving fact foreign keys
  • Use surrogate dimension keys to decouple the model from source keys
  • Map unresolved dimensions to a predefined unknown member
  • Load atomic facts to preserve valid aggregation paths
  • Separate analytic context from measurements in a star schema
  • Match the fact table type to how measurements evolve
  • Classify measure additivity before choosing an aggregation
  • Share conformed dimensions across related stars
  • Validate dimensional referential integrity in ETL
  • Create an inferred member for a valid late-arriving dimension
  • Soft-delete warehouse records when history must remain valid
  • Use SCD Type 1 when prior attribute values need not be retained
  • Use SCD Type 2 to preserve attribute history
  • Track Type 2 currency with validity metadata
  • Resolve historical facts to the dimension version valid at event time
  • Compare tracked attributes before creating a new dimension version
  • Use SCD Type 3 only for limited prior-value history
  • Checkpoint stateful Spark streams in durable lake storage
  • Make downstream processing tolerant of at-least-once delivery
  • Fan out a raw archive and curated stream from one ingestion path
  • Run production Spark streams as Spark job definitions with retries

Unlock with Premium — includes all practice exams and the complete study guide.

Also tested in

References

  1. What is Copy job in Data Factory for Microsoft Fabric?
  2. Incremental copy in Copy job
  3. Load tables in a dimensional model in Microsoft Fabric Warehouse
  4. Incrementally load data from Data Warehouse to Lakehouse
  5. Incremental refresh in Dataflow Gen2
  6. Modeling fact tables in Microsoft Fabric Warehouse
  7. Primary, foreign, and unique keys in Microsoft Fabric Warehouse
  8. Modeling dimension tables in Microsoft Fabric Warehouse
  9. Data streaming into a lakehouse with Spark
  10. Microsoft Fabric eventstreams overview
  11. Process events using a SQL operator in eventstreams