Momentum One Academy · article
SCD Type 2 and CDC Patterns in Microsoft Fabric
A customer changes segment, a product moves category, or an employee transfers region. The operational system now shows the new value, but a business report still needs to answer a historical question: which segment, category, or region was valid when the transaction occurred? Overwriting the dimension gives an accurate current state and an inaccurate past.
That is the problem SCD Type 2 solves. It expires the old dimension version and inserts a new version, preserving the attribute values and the effective period. Change Data Capture (CDC) is related but different: it is a way to discover inserted, updated, and deleted source rows. Change Data Feed (CDF) is the Delta Lake mechanism for recording row-level changes in a Delta table. In a production design, capture tells us what changed; SCD Type 2 decides how that change becomes durable business history. 1 2 4 6
Start with the business question, not the tool
At Momentum One, we first classify each attribute in a dimension. If only the latest value matters, use Type 1 behavior and overwrite it. If a report must describe the past accurately, make that attribute Type 2. A customer email address may be Type 1, while a sales territory used for historical performance may be Type 2. Do not make every column historical by default: a rapidly changing attribute can create a large number of versions and make a dimension difficult to use. Microsoft’s warehouse guidance explicitly recommends reserving Type 2 for attributes that require historical perspective. 2
The dimension contract should be agreed before the pipeline is built. It normally includes a source or natural key, a warehouse surrogate key, the tracked attributes, a validity start and end, and a current-row flag. Audit fields such as load ID, created time, last-modified time, and process name make reruns and investigations possible. The natural key identifies the business member across versions; the surrogate key identifies one version and is the key that facts should store. 1 2
For example, customer C-123 may have one row for California from 2025-01-01 through 2026-02-20 and a second row for New York from 2026-02-20 onward. Both rows share CustomerID = C-123, but they have different surrogate keys. The first row is no longer current; the second row is current. A fact load must resolve the version appropriate to the fact’s business date, not simply join to whichever row happens to be current today.
The Fabric building blocks
A practical Fabric solution usually combines several experiences rather than treating “CDC” as a complete architecture.
Fabric Warehouse is a strong serving layer when the target is a governed star schema and the team wants a SQL-oriented loading process. Microsoft’s dimensional-model guidance recommends staging when it improves restartability, reduces source impact, or makes transformations easier. The documented sequence is to stage data, process dimensions, process facts, and then perform dependent post-processing such as a semantic-model refresh. Dimensions must be processed before facts so that the required members and surrogate keys exist. 3
Fabric Lakehouse and Delta Lake are a strong landing and transformation layer when Spark processing, medallion layers, or a shared table format are important. Fabric uses Delta Lake as the default format for Lakehouse tables, with transactional consistency and interoperability across Fabric tools. 7 A common production shape is raw or bronze data, a normalized silver table, and a curated history table. A Warehouse can then publish a dimensional model for SQL consumers, or the Lakehouse tables can remain the analytical source when that is the better fit.
Data Factory Dataflow Gen2 provides a low-code SCD2 route. Microsoft’s tutorial compares source and dimension data, uses selected fields to detect changes, prepares new and expired records, and then either hands a staging result to a stored procedure or notebook or rebuilds the destination through Dataflow Gen2. Hashing can make comparison practical, but the hash must be computed from a deliberate list of tracked fields with consistent null and type handling. 1
Copy Job CDC is a managed data-movement option when the source connector and destination combination meet the requirements. CDC-based incremental copy can replicate inserts, updates, and deletes; a watermark approach is a fallback when CDC is unavailable and a reliable incremental column exists. Copy Job SCD Type 2 adds validity and current-row columns and treats source deletes as soft deletes, but Microsoft currently labels SCD Type 2 in Copy Job as preview. Connector support and destination rules therefore belong in the design review, not in a footnote. 4
Three architecture choices
1. Warehouse-first, controlled SCD2
Use this when the principal deliverable is a Warehouse dimensional model. Land a source snapshot or change set in a staging schema. Match on the natural key and compare only the tracked attributes. For a new member, insert a current row. For a changed member, expire the current row and insert a new row using the same change boundary. For a source deletion, keep the historical rows and mark the latest member as deleted or inactive according to the model’s reporting contract.
This approach gives the team explicit control over effective dates, surrogate keys, mixed Type 1 and Type 2 attributes, soft-delete policy, and fact lookups. It also creates more code and more operational responsibility. A production implementation should make the expire-and-insert unit rerunnable and should prevent two current rows for one natural key. Separate staging from the published dimension so that a failed transformation does not leave half-processed business data visible.
Never solve a difficult incremental load by truncating and recreating a generated-key SCD2 dimension. Microsoft warns that doing so can invalidate fact references and make historical versions impossible to reproduce safely. 3
2. Lakehouse-first, CDF-driven processing
Use this when changes naturally land in Delta tables or when Spark is the team’s best transformation environment. Enable CDF before relying on it for production deltas. CDF records changes only after it is enabled; it does not reconstruct changes from before that point. It exposes change type, commit version, and commit timestamp, while updates can include both pre-image and post-image rows. 6
A notebook or Spark job can read a bounded version window, normalize events, and apply the SCD2 rules to a curated Delta history table. Persist the last successfully processed table version with the run ID. Prefer bounded reads for batch processing so a rerun has a defined input window. If CDF retention is shortened by maintenance, a consumer that has fallen too far behind may need a controlled re-seed from a full snapshot.
The following Spark SQL example shows the important boundary. The version numbers are illustrative; production code should use the versions recorded by the pipeline’s control table.
-- Run before the first production change you need to consume.
ALTER TABLE silver.customer
SET TBLPROPERTIES (delta.enableChangeDataFeed = true);
-- Read one closed Delta version window in a Fabric notebook.
SELECT customer_id, segment, _change_type, _commit_version, _commit_timestamp
FROM table_changes('silver.customer', 120, 125)
WHERE _change_type IN ('insert', 'update_postimage', 'delete');
Treat update_postimage as the candidate new state and do not load both update images as two business changes. Join the candidate to the current dimension version, compare tracked fields, expire the old version when the comparison differs, and insert the candidate as current. Map a delete to the agreed soft-delete behavior. If several changes for one natural key occur in the same window, order them by the Delta commit metadata or the source’s authoritative change sequence before creating versions. CDF supplies events; it does not choose your business-effective timestamp or your dimension-key policy. 6
3. Managed Copy Job CDC and SCD2
Use this for a straightforward replication or history-tracking requirement where the preview status and connector matrix are acceptable. The first run is a full snapshot and later runs consume changes. For Azure SQL Database, Microsoft’s tutorial requires CDC to be enabled on the database and tables, recommends supports_net_changes, and warns that CDC log retention must exceed the interval between scheduled runs. 5
This route reduces custom code, but it does not remove design work. Confirm that every selected table is supported, decide whether the destination schema is automatically created or pre-existing, and test the generated validity columns against downstream expectations. The current CDC Copy Job documentation also notes that mixing CDC-enabled and non-CDC tables in one job causes all tables to be treated as watermark-based. It further documents that Lakehouse CDF enablement is not automatically detected. 4 For a production rollout, separate jobs or explicit validation may be safer than assuming the UI’s default is the intended capture mode.
A production loading sequence
First, establish the capture boundary. Record the source CDC window, watermark, or Delta table version before transformation. Do not advance that checkpoint until the destination write and validation succeed. For a Lakehouse CDF pipeline, a checkpoint location helps streaming state survive restarts; for a batch Warehouse process, a control table with run ID, lower bound, upper bound, and status serves the same operational purpose. 6
Second, normalize the change set. Standardize key types, trim or otherwise define string comparison rules, handle nulls deliberately, and deduplicate events when the source can emit multiple updates for one key. A hash is useful as a comparison aid, but it is not a substitute for a stable natural key or for a documented collision and null policy.
Third, process dimensions before facts. Match each candidate to the current version by natural key. Unchanged candidates do nothing. New candidates receive a new surrogate key and an open-ended current period. Changed candidates close the current period and create a new current version. Deleted members remain available for historical joins; represent deletion with a soft-delete flag or an equivalent inactive state unless the business has explicitly approved physical removal.
Fourth, load facts using the correct dimension key. If the fact is event-dated, resolve the dimension row whose validity period contains that event time. If the fact represents the present state, use the current version. Keep an explicit unknown or inferred-member strategy for facts that arrive before dimension details; do not silently drop the fact because a lookup failed. Fabric Warehouse foreign keys are not enforced, so the pipeline must test referential integrity itself. 3
Finally, validate before publishing. Check that each natural key has at most one current row, that validity windows do not overlap, that the new version begins at the old version’s boundary, and that inserted, expired, deleted, and unchanged counts reconcile to the input change set. Log the run ID, window, row counts, final counts, errors, and downstream refresh status. These checks turn “the pipeline succeeded” into evidence that the model remains trustworthy. 3
Trade-offs and failure modes
The most common failure is confusing current-state replication with history. Copy Job Merge or a Type 1 design may be perfectly correct for an operational mirror, but it cannot answer what the dimension looked like last month. Conversely, Type 2 everywhere produces unnecessary versions and harder report semantics. Make the decision at attribute level and document it.
A second failure is losing the source change window. CDC logs and Delta change files are retained resources, not an unlimited audit archive. If a scheduled Copy Job runs after the source CDC retention window, the missing changes may no longer be available. If a CDF consumer falls behind and maintenance removes older change files, the pipeline needs a planned full-resnapshot procedure. 5 6
A third failure is a broken time model. Mixing source event time, ingestion time, and processing time makes “as of” queries inconsistent. Choose which timestamp controls validity, preserve the other timestamps for audit, and define timezone handling. Use the same boundary to expire the old row and start the new row; otherwise a transaction can fall into a gap or two versions can appear valid at once.
Other practical failures include hashing the wrong columns, treating a missing source row as a delete when the extract was only a filtered subset, inserting both CDF update images, loading facts before the dimension, and re-running an expire step without an idempotency guard. These are modeling and orchestration defects, not problems that a different Fabric item will solve automatically.
Operational checklist
Before production, verify the following:
- The natural key, surrogate key, tracked attributes, Type 1 attributes, validity timestamp semantics, current flag, and soft-delete rule are documented.
- The selected CDC, watermark, or CDF mechanism is supported for every source and destination combination, and preview features have an owner and rollback plan.
- The initial full load and the incremental boundary have been tested together; the first post-baseline change is not missing or duplicated.
- The control record advances only after the destination write and reconciliation checks succeed.
- CDF is enabled before the first required change, and Delta maintenance and CDC log retention exceed the maximum expected processing delay. 5 6
- The load enforces one current row per natural key, non-overlapping validity windows, stable surrogate keys, and fact-to-dimension integrity.
- Source schema changes, null changes, duplicate keys, out-of-order events, filtered extracts, and deletes have explicit test cases.
- Monitoring captures status, duration, rows read and written, insert/update/delete counts where available, lower and upper bounds, run IDs, errors, and downstream refresh outcome. 8
- A runbook explains how to pause consumers, re-seed from a full snapshot, repair a bad window, and resume without creating duplicate versions.
Module 7 workshop connection
In Momentum One Academy’s Module 7 workshop, use one small customer or employee dimension to make the distinction observable. Start with a baseline, change one Type 2 attribute, correct one Type 1 attribute, add a member, and delete a member. Capture the source change set, inspect the Delta change events if the Lakehouse route is used, and then publish the Warehouse dimension. Learners should be able to show the expired row, the new current row, the soft-deleted member, and the fact lookup that preserves the historical grouping.
The workshop’s production test is not merely “did the notebook run?” It is “can a second run with the same input leave the dimension unchanged, can a failed run resume from a known boundary, and can a report explain the state at a chosen date?” That is the discipline that turns a demo SCD2 pattern into an operable Fabric solution.
Ready to apply the pattern to your own architecture? Continue with the Momentum One Academy Fabric Production Workshop.
References
Plan your workshop