Implementing Slowly Changing Dimensions in a Fabric Lakehouse: Using PySpark and Delta Lake MERGE to Track Historical Changes Across Medallion Layers

Introduction

Picture this: your finance team runs a customer profitability report and discovers that a customer who recently moved from the "SMB" segment to "Enterprise" is showing revenue attributed to the wrong segment for the past six months. The data is technically correct — it reflects the current state — but nobody tracked when the segment changed. Every historical transaction is now tied to a label that didn't exist when the sale happened. This is the slowly changing dimension problem, and it's one of the most practically important modeling challenges in any data warehouse or lakehouse.

Slowly Changing Dimensions (SCDs) are the mechanism by which you track how descriptive attributes — customer segments, product categories, employee departments, store regions — change over time without losing the historical record. Getting this right is what separates a reporting system that supports genuine business intelligence from one that produces plausible-looking numbers that silently lie about the past.

In this lesson, you'll build a production-quality SCD Type 2 implementation inside a Microsoft Fabric Lakehouse, using PySpark and Delta Lake's MERGE statement to correctly expire old records, insert new versions, and propagate clean history through your medallion architecture. By the end, you'll have a pattern you can adapt to any slowly changing attribute in your own environment.

What you'll learn:

• The difference between SCD Types 1, 2, and 3, and when to use each in a lakehouse context
• How to structure SCD tables as Delta Lake tables with surrogate keys, effective dates, and current-row flags
• How to implement the full SCD Type 2 MERGE logic in PySpark, including detecting changes, expiring old rows, and inserting new versions
• How to handle the Silver-to-Gold promotion of SCD tables within a medallion architecture
• How to maintain query performance on SCD tables using Delta Lake optimization features

---

Prerequisites

You should be comfortable with:

• PySpark DataFrames and basic transformations — if you need a refresher, see Transforming Data with Spark Notebooks in Microsoft Fabric: PySpark for Lakehouse Tables
• Delta Lake MERGE syntax — the patterns in Writing Data from a Spark Notebook to a Fabric Lakehouse Delta Table: Append, Overwrite, and Merge Patterns with PySpark will get you up to speed
• The medallion architecture layering concept — Implementing the Medallion Architecture in Microsoft Fabric: Bronze, Silver, and Gold Layers covers this in full
• Basic familiarity with Fabric Lakehouses and notebooks

---

Understanding the SCD Types: A Practitioner's Breakdown

Before you write a single line of code, you need to make a deliberate design decision: which SCD type is appropriate for each dimension? Getting this wrong is expensive to fix later.

SCD Type 1 — Overwrite: The simplest approach. When an attribute changes, you update the existing row in place. No history is kept. Use this when history genuinely doesn't matter — for example, correcting a misspelled product name, or updating a phone number that's just a contact detail. The risk: any historical analysis that grouping by this attribute will silently use the new value for past events.

SCD Type 2 — Add a new row: When an attribute changes, you close the current record by setting an expiry date and a is_current = false flag, then insert a new row for the new version. This is the gold standard for business-significant attributes like customer segment, product category, or sales territory. It's what we'll implement in depth.

SCD Type 3 — Add a column: You add a "previous value" column alongside the current value. Rarely useful in practice — it only tracks one level of history, and it pollutes your schema as attributes change again and again. Avoid it for anything except very specific use cases.

---

Designing Your SCD Type 2 Table Structure

Let's use a concrete scenario throughout this lesson: a dim_customer dimension tracking customers for a B2B SaaS business. The business cares about:

• customer_segment (SMB, Mid-Market, Enterprise) — Type 2: affects quota attribution and cohort analysis
• account_manager_id — Type 2: affects commission calculations
• billing_email — Type 1: just a contact detail, no historical value
• company_name — Type 1: corrections only

Here's the target schema for the Silver layer SCD table:

from pyspark.sql.types import (
    StructType, StructField, StringType, 
    IntegerType, DateType, BooleanType, TimestampType
)

dim_customer_schema = StructType([
    # Surrogate key — system-generated, never from the source
    StructField("customer_sk", IntegerType(), False),
    
    # Natural key from the source system
    StructField("customer_id", StringType(), False),
    
    # Type 1 attributes (overwritten in place)
    StructField("company_name", StringType(), True),
    StructField("billing_email", StringType(), True),
    
    # Type 2 attributes (new row on change)
    StructField("customer_segment", StringType(), True),
    StructField("account_manager_id", StringType(), True),
    
    # SCD metadata columns
    StructField("effective_start_date", DateType(), False),
    StructField("effective_end_date", DateType(), True),   # NULL = current
    StructField("is_current", BooleanType(), False),
    
    # Audit columns
    StructField("row_created_at", TimestampType(), False),
    StructField("row_updated_at", TimestampType(), False),
    StructField("source_system", StringType(), True),
])

Two design choices here deserve explanation:

Surrogate key vs. natural key: The customer_sk is a system-generated integer that uniquely identifies each version of a customer. The fact table will join on customer_sk, not customer_id. This is what makes historical analysis possible — a transaction from 2022 links to the row where customer_segment = 'SMB', while a 2024 transaction links to the newer row where customer_segment = 'Enterprise'. The customer_id is preserved for traceability but never used as a join key.

NULL effective_end_date for current rows: Some teams use a far-future sentinel date like 9999-12-31. Using NULL is cleaner in Delta Lake because it's unambiguous — there's no magic value to forget to filter on. The is_current flag is slightly redundant but makes queries dramatically more readable: WHERE is_current = true is self-documenting.

---

Building the Bronze Layer: Raw Ingestion

Before you can run SCD logic, you need incoming change data landing in your Bronze layer. In a real pipeline, this might come from a CDC stream, a Dataflow Gen2 pull from a source API, or a scheduled copy from an operational database. For this lesson, we'll simulate a realistic source extract: a full snapshot of the customer table delivered daily as a Parquet file.

The Bronze layer just lands the raw data with minimal transformation — add a load timestamp and partition by load date, nothing else:

from pyspark.sql import SparkSession
from pyspark.sql.functions import current_timestamp, lit, to_date
from delta.tables import DeltaTable

spark = SparkSession.builder.getOrCreate()

# Simulate reading today's customer extract from Bronze Files area
# In production this comes from a Copy Activity or Dataflow Gen2
bronze_raw = spark.read.parquet(
    "Files/bronze/customer_extracts/2024-11-15/customers.parquet"
)

# Add ingestion metadata
bronze_customers = bronze_raw.withColumn(
    "ingestion_timestamp", current_timestamp()
).withColumn(
    "source_file_date", lit("2024-11-15").cast("date")
)

# Write to Bronze Delta table — append only, full history preserved
bronze_customers.write.format("delta") \
    .mode("append") \
    .partitionBy("source_file_date") \
    .saveAsTable("bronze_lakehouse.bronze_customers")

---

The Silver Layer: Implementing SCD Type 2 MERGE

This is where the real work happens. The SCD MERGE operation is more complex than a standard upsert because you're not just updating rows — you're closing old rows and inserting new ones, sometimes both in response to a single changed record.

Step 1: Prepare the Incoming Data

First, read today's snapshot from Bronze and identify what's actually changed:

from pyspark.sql.functions import (
    col, current_timestamp, current_date, 
    lit, sha2, concat_ws, when, coalesce
)

# Read today's extract
today = "2024-11-15"

incoming = spark.sql(f"""
    SELECT DISTINCT
        customer_id,
        company_name,
        billing_email,
        customer_segment,
        account_manager_id,
        source_system
    FROM bronze_lakehouse.bronze_customers
    WHERE source_file_date = '{today}'
""")

# Create a hash of the Type 2 attributes to detect changes efficiently.
# This avoids comparing multiple columns individually in the MERGE condition.
incoming_with_hash = incoming.withColumn(
    "type2_hash",
    sha2(
        concat_ws("||",
            coalesce(col("customer_segment"), lit("__NULL__")),
            coalesce(col("account_manager_id"), lit("__NULL__"))
        ),
        256
    )
)

The hash trick is important. Instead of writing AND (target.customer_segment != source.customer_segment OR target.account_manager_id != source.account_manager_id) in every MERGE condition, you compute a single hash of all Type 2 attributes. If the hash changes, something Type-2-significant changed. This also handles NULLs gracefully — note the coalesce to replace NULLs with a sentinel string before hashing.

Step 2: Generate Surrogate Keys for New Records

Surrogate key generation in a distributed environment requires care. A common pattern in Delta Lake lakehouses is to find the current maximum key and increment from there:

from pyspark.sql.functions import monotonically_increasing_id, row_number
from pyspark.sql.window import Window

# Find the max existing surrogate key
try:
    max_sk_row = spark.sql(
        "SELECT COALESCE(MAX(customer_sk), 0) as max_sk FROM silver_lakehouse.dim_customer"
    )
    max_sk = max_sk_row.collect()[0]["max_sk"]
except Exception:
    # Table doesn't exist yet — first load
    max_sk = 0

# Assign surrogate keys to genuinely new customer_ids
# (IDs not yet in the dimension at all)
existing_ids = spark.sql(
    "SELECT DISTINCT customer_id FROM silver_lakehouse.dim_customer"
    if max_sk > 0 else "SELECT '' as customer_id WHERE 1=0"
)

new_customers = incoming_with_hash.join(
    existing_ids, on="customer_id", how="left_anti"
)

# Window function to assign sequential keys
window_spec = Window.orderBy("customer_id")

new_customers_with_sk = new_customers.withColumn(
    "customer_sk",
    (max_sk + row_number().over(window_spec)).cast("integer")
)

Step 3: The Core MERGE Logic

Here's the full SCD Type 2 MERGE. Read it carefully — this is the heart of the pattern:

from delta.tables import DeltaTable

# Load the target Delta table
dim_customer_delta = DeltaTable.forName(spark, "silver_lakehouse.dim_customer")

# Prepare the source dataframe — both returning customers (potential updates)
# and brand new customers
source_df = incoming_with_hash.withColumn(
    "effective_start_date", current_date()
).withColumn(
    "effective_end_date", lit(None).cast("date")
).withColumn(
    "is_current", lit(True)
).withColumn(
    "row_created_at", current_timestamp()
).withColumn(
    "row_updated_at", current_timestamp()
)

# -----------------------------------------------------------------------
# PHASE 1: Expire rows where Type 2 attributes have changed
# We UPDATE the existing current row to set effective_end_date and is_current=False
# -----------------------------------------------------------------------
(
    dim_customer_delta.alias("target")
    .merge(
        source_df.alias("source"),
        condition="""
            target.customer_id = source.customer_id
            AND target.is_current = true
        """
    )
    # Type 2 change detected: hash is different → expire the old row
    .whenMatchedUpdate(
        condition="""
            target.type2_hash != source.type2_hash
        """,
        set={
            "is_current": "false",
            "effective_end_date": "date_sub(source.effective_start_date, 1)",
            "row_updated_at": "current_timestamp()"
        }
    )
    # Type 1 change only: hash is same but Type 1 attrs differ → overwrite in place
    .whenMatchedUpdate(
        condition="""
            target.type2_hash = source.type2_hash
            AND (
                target.company_name != source.company_name
                OR target.billing_email != source.billing_email
            )
        """,
        set={
            "company_name": "source.company_name",
            "billing_email": "source.billing_email",
            "row_updated_at": "current_timestamp()"
        }
    )
    .execute()
)

# -----------------------------------------------------------------------
# PHASE 2: Insert new rows for changed records and brand-new customers
# We need to insert a new current row wherever a Type 2 change happened
# OR where the customer_id is completely new
# -----------------------------------------------------------------------

# Find customers who had a Type 2 change — their old row was just expired,
# now we need to insert the new version
changed_customers = (
    source_df.alias("source")
    .join(
        dim_customer_delta.toDF().filter(col("is_current") == False)
            .filter(col("effective_end_date") == current_date() - 1)
            .select("customer_id")
            .alias("expired"),
        on="customer_id",
        how="inner"
    )
)

# Find brand-new customer_ids (no row exists at all)
# These already have surrogate keys assigned from Step 2 above
brand_new = new_customers_with_sk.select(source_df.columns + ["customer_sk"])

# Combine: changed customers (need new current row) + brand new customers
# For changed customers, we need to assign new surrogate keys too
changed_with_sk = changed_customers.withColumn(
    "customer_sk",
    (max_sk + row_number().over(Window.orderBy("customer_id")) + 
     new_customers_with_sk.count()).cast("integer")
)

rows_to_insert = changed_with_sk.union(brand_new)

# Insert all new/changed rows as current records
(
    dim_customer_delta.alias("target")
    .merge(
        rows_to_insert.alias("source"),
        condition="""
            target.customer_id = source.customer_id
            AND target.is_current = true
        """
    )
    .whenNotMatchedInsert(
        values={
            "customer_sk": "source.customer_sk",
            "customer_id": "source.customer_id",
            "company_name": "source.company_name",
            "billing_email": "source.billing_email",
            "customer_segment": "source.customer_segment",
            "account_manager_id": "source.account_manager_id",
            "type2_hash": "source.type2_hash",
            "effective_start_date": "source.effective_start_date",
            "effective_end_date": "null",
            "is_current": "true",
            "row_created_at": "current_timestamp()",
            "row_updated_at": "current_timestamp()",
            "source_system": "source.source_system"
        }
    )
    .execute()
)

Let's walk through what this is doing:

Phase 1 runs a MERGE that only touches existing current rows. For any customer in today's extract whose Type 2 hash differs from what's in the table, it expires that row: sets is_current = false and effective_end_date = yesterday. The date_sub(source.effective_start_date, 1) calculation closes the row one day before the new version becomes effective, so there are no gaps or overlaps in the timeline. It also handles Type 1 updates in the same pass — no new row, just an in-place column update.

Phase 2 inserts new rows. It does this in two parts: customers whose old row was just expired (we find them by looking for rows we just set effective_end_date to yesterday), and genuinely new customers who had no row at all. Both sets get inserted as is_current = true with a NULL effective_end_date.

Step 4: Handle the Initial Load

The code above assumes the table already exists. For the very first load, you need to create and populate the table:

def initial_load_dim_customer(spark, source_df: "DataFrame") -> None:
    """
    Run only once to bootstrap the dim_customer table.
    Assigns surrogate keys and sets all rows as current.
    """
    window_spec = Window.orderBy("customer_id")
    
    initial_df = source_df.withColumn(
        "customer_sk", row_number().over(window_spec).cast("integer")
    ).withColumn(
        "effective_start_date", lit("2000-01-01").cast("date")  # Beginning of time
    ).withColumn(
        "effective_end_date", lit(None).cast("date")
    ).withColumn(
        "is_current", lit(True)
    ).withColumn(
        "row_created_at", current_timestamp()
    ).withColumn(
        "row_updated_at", current_timestamp()
    ).withColumn(
        "type2_hash",
        sha2(concat_ws("||",
            coalesce(col("customer_segment"), lit("__NULL__")),
            coalesce(col("account_manager_id"), lit("__NULL__"))
        ), 256)
    )
    
    initial_df.write.format("delta") \
        .mode("overwrite") \
        .saveAsTable("silver_lakehouse.dim_customer")
    
    print(f"Initial load complete. {initial_df.count()} customers loaded.")

The effective_start_date of 2000-01-01 is a common convention for "we don't know when this was true from, but it was true at the beginning of our data history." Document this convention in your data catalog — future analysts will thank you.

---

Propagating SCD History to the Gold Layer

Your Silver dim_customer table is now a full SCD Type 2 history table. But how you expose it in Gold depends on who's consuming it.

Current-State View for Operational Reports

Most dashboard consumers only want the current customer attributes — they're not doing point-in-time analysis:

# Gold layer: current dimension view
# This is what your Power BI Direct Lake model joins to for standard reports

spark.sql("""
    CREATE OR REPLACE TABLE gold_lakehouse.dim_customer_current
    USING DELTA
    AS
    SELECT
        customer_sk,
        customer_id,
        company_name,
        billing_email,
        customer_segment,
        account_manager_id,
        effective_start_date,
        source_system
    FROM silver_lakehouse.dim_customer
    WHERE is_current = true
""")

This is a simple, clean table for the 90% use case. Refresh it after every SCD MERGE run.

Point-in-Time Join for Analytical Queries

For revenue attribution, cohort analysis, or any scenario where you need "what was true at the time of the transaction," you need a point-in-time join in your fact table processing:

# Gold layer: fact table enriched with customer attributes AS OF transaction date
# This is the query pattern that makes SCD Type 2 worth the effort

fact_revenue_enriched = spark.sql("""
    SELECT
        f.transaction_id,
        f.transaction_date,
        f.revenue_amount,
        f.product_id,
        
        -- Customer attributes AS OF the transaction date
        c.customer_id,
        c.company_name,
        c.customer_segment,      -- This will be 'SMB' for old transactions
        c.account_manager_id,    -- Commission goes to the right person
        
        -- The surrogate key ties this fact to the exact dimension version
        c.customer_sk
        
    FROM gold_lakehouse.fact_revenue f
    
    -- Join to the version of the customer that was current at transaction time
    JOIN silver_lakehouse.dim_customer c
        ON f.customer_id = c.customer_id
        AND f.transaction_date >= c.effective_start_date
        AND (f.transaction_date <= c.effective_end_date 
             OR c.effective_end_date IS NULL)
""")

fact_revenue_enriched.write.format("delta") \
    .mode("overwrite") \
    .option("overwriteSchema", "true") \
    .saveAsTable("gold_lakehouse.fact_revenue_enriched")

This join condition — transaction_date BETWEEN effective_start_date AND effective_end_date — is the definitive SCD Type 2 lookup. It retrieves exactly the version of the customer record that was active when the transaction occurred.

---

Wrapping the Whole Thing in a Pipeline

Individual notebook cells are fine for development, but production SCD processing needs to be orchestrated reliably. The recommended pattern in Fabric is:

1. Copy Activity (or Dataflow Gen2) pulls the customer extract to Bronze
2. Notebook Activity runs the SCD MERGE logic against Silver
3. Notebook Activity refreshes the Gold current-state table
4. Optional: another Notebook Activity rebuilds the enriched fact table

You can chain these into a Fabric Data Pipeline where each activity runs in sequence, with failure alerts configured so you know immediately if the SCD MERGE fails before the fact refresh runs. For a full treatment of pipeline orchestration patterns including activity chaining and notifications, see Orchestrating Loads with Fabric Data Pipelines: Copy Activities, Parameters, and Schedules.

A critical point about idempotency: your SCD MERGE notebook should be safe to run twice on the same day without corrupting data. The Phase 1 MERGE only expires rows where the hash changes — if you run it again on the same source data, no hashes change, so no rows get expired a second time. The Phase 2 insert uses whenNotMatchedInsert, which only fires if there's no current row — if there's already a current row from the first run, the insert is skipped. This is the correct behavior.

---

Handling Schema Evolution

Real source systems add columns over time. If your source starts sending a new field — say, industry_vertical — you need to decide whether it's a Type 1 or Type 2 attribute and extend your schema accordingly. Delta Lake handles this gracefully with schema evolution, but you need to update your hash computation and MERGE logic simultaneously.

The Handling Schema Evolution in Fabric Lakehouse Delta Tables article covers the mechanics in detail. The SCD-specific consideration is: when you add a new Type 2 attribute to the hash, every existing row's hash becomes stale. The next time you run the MERGE, every customer will appear to have changed, and you'll generate a new row for every customer in your dimension.

The fix: when adding a new Type 2 attribute, run a one-time backfill that updates the type2_hash on all current rows to include the new attribute's value, before running the MERGE for the first time with the new hash logic. This way, only customers where industry_vertical genuinely changes going forward will trigger new rows.

---

Hands-On Exercise

Build the following in your own Fabric Lakehouse environment. You'll need a Bronze and Silver lakehouse — if you haven't set these up yet, Building Your First Lakehouse in Microsoft Fabric will walk you through the setup.

The scenario: A retail company has a dim_store dimension. Stores change their region assignment occasionally when territory boundaries are redrawn, and sometimes change their store format (Express, Standard, Flagship). Region and format are Type 2 (historically significant for sales analysis). The store manager name is Type 1 (contact detail only).

Your tasks:

1. Create the initial load. Use this synthetic data to bootstrap silver_lakehouse.dim_store:

from pyspark.sql import Row
from datetime import date

initial_data = [
    Row(store_id="S001", store_name="Portland Downtown", manager_name="Alice Reyes",
        region="Pacific Northwest", store_format="Flagship"),
    Row(store_id="S002", store_name="Seattle Capitol Hill", manager_name="Ben Ochoa",
        region="Pacific Northwest", store_format="Standard"),
    Row(store_id="S003", store_name="Boise Towne Square", manager_name="Cara Ngo",
        region="Mountain West", store_format="Standard"),
    Row(store_id="S004", store_name="Denver LoDo", manager_name="Derek Walsh",
        region="Mountain West", store_format="Express"),
]

initial_df = spark.createDataFrame(initial_data)

2. Run the initial load using the initial_load_dim_customer pattern above, adapted for stores.

3. Simulate an incoming change batch and run the full SCD MERGE. Use this as your "next day's extract":

# Day 2 extract: 
# - S002 moves to a new region (Type 2 change → new row)
# - S004 upgrades to Standard format (Type 2 change → new row)
# - S003 has a new manager (Type 1 change → overwrite)
# - S001 is unchanged
# - S005 is a brand-new store

day2_data = [
    Row(store_id="S001", store_name="Portland Downtown", manager_name="Alice Reyes",
        region="Pacific Northwest", store_format="Flagship"),
    Row(store_id="S002", store_name="Seattle Capitol Hill", manager_name="Ben Ochoa",
        region="Oregon Coastal",    store_format="Standard"),    # region changed
    Row(store_id="S003", store_name="Boise Towne Square", manager_name="Jordan Kim",  # manager changed
        region="Mountain West", store_format="Standard"),
    Row(store_id="S004", store_name="Denver LoDo", manager_name="Derek Walsh",
        region="Mountain West", store_format="Standard"),       # format changed
    Row(store_id="S005", store_name="Salt Lake City Main", manager_name="Priya Patel",
        region="Mountain West", store_format="Express"),        # new store
]

4. Verify your results. After running the MERGE, query dim_store and confirm:

• S001 has exactly 1 row, is_current = true
• S002 has 2 rows: one expired with region = 'Pacific Northwest', one current with region = 'Oregon Coastal'
• S003 has 1 row (Type 1 update) with manager_name = 'Jordan Kim'
• S004 has 2 rows: one expired, one current with store_format = 'Standard'
• S005 has 1 row, is_current = true, with a new surrogate key

5. Stretch goal: Write the point-in-time join query that would answer "What was the region and format of each store on Day 1 (the initial load date)?" against a fictional fact_sales table.

---

Common Mistakes & Troubleshooting

Mistake 1: Running MERGE without filtering to current rows

If your MERGE condition is just target.customer_id = source.customer_id without AND target.is_current = true, your MERGE will match against expired rows too. This causes chaos: expired rows get updated with current values, and your history is destroyed.

Fix: Always include AND target.is_current = true in the MERGE join condition for SCD Type 2 tables.

Mistake 2: Treating surrogate key generation as trivially safe

Using MAX(customer_sk) + 1 is safe only if your MERGE is single-threaded (one notebook running at a time). If you ever parallelize across multiple notebooks, you'll get duplicate surrogate keys.

Fix: Use a dedicated surrogate key table with Delta Lake's optimistic concurrency, or switch to UUIDs. In Fabric, you can also use a SQL IDENTITY column if you're routing through the Warehouse endpoint.

Mistake 3: Hashing NULL values inconsistently

If customer_segment is NULL in the source on day 1 and then becomes 'SMB' on day 2, that's a real Type 2 change and should generate a new row. But if your hash doesn't handle NULLs consistently — for example, if sha2(concat_ws("||", null, "other")) produces different results than expected — you'll miss this change.

Fix: Always coalesce(col("attribute"), lit("__NULL__")) before including in the hash. Never allow raw NULLs into the hash input.

Mistake 4: Forgetting to partition by is_current

Queries like WHERE is_current = true AND customer_id = 'C1234' will be slow if Delta Lake has to scan all historical rows to find the current one.

Fix: Write your Delta table with partitionBy("is_current"). With most dimensions heavily weighted toward is_current = false (historical rows), this partition split may actually hurt if you're only ever reading current rows. Test with and without — for large dimensions, Z-order on customer_id within the is_current = true partition is often more effective than partitioning.

Mistake 5: VACUUM deleting rows you need for time travel

Delta Lake's VACUUM removes old file versions to reclaim storage. If you've set a short retention window (the default is 7 days), you'll lose the ability to recover from a botched MERGE.

Fix: Don't reduce VACUUM retention below 7 days, and always test your MERGE logic on a small subset before running in production. Delta Lake's RESTORE command (or time travel queries) is your recovery mechanism — keep it available.

---

Performance Implications and When to Use This Approach

SCD Type 2 has real costs that you should weigh consciously:

• Storage: Every attribute change creates a new row. A customer dimension with 500k customers and moderate churn can accumulate millions of rows over a few years.
• Join complexity: Point-in-time joins are range joins — they don't benefit from simple hash joins. They're inherently more expensive than single-key joins.
• MERGE latency: A full-table MERGE on millions of rows takes time. For very large dimensions, consider running incremental MERGEs — only process customer_ids that appear in a change log, not the full snapshot.

SCD Type 2 is the right choice when:

• Historical attribute values directly affect financial or operational calculations
• You're building a data warehouse for regulated industries (financial services, healthcare) where point-in-time auditability is required
• Analysts legitimately need to ask "what did we think was true then?" rather than just "what is true now?"

It's not the right choice when:

• Your data consumers only ever want current state (use Type 1 and save the complexity)
• The changing attribute has low cardinality and changes are very frequent — consider a separate "events" table instead

---

Summary & Next Steps

You've now built a production-quality SCD Type 2 implementation in a Fabric Lakehouse. Let's recap the key patterns:

• Design deliberately: Classify each attribute as Type 1 or Type 2 before writing any code. Mix them within a single table using a hash for Type 2 detection.
• Two-phase MERGE: Expire old rows first (update), then insert new current rows. The two operations cannot be collapsed into one Delta MERGE call for the same key.
• Surrogate keys: Always join fact tables to dimension tables via surrogate key, never natural key. This is what makes point-in-time queries work.
• Idempotency: Your MERGE logic should be safe to re-run. Use it as a design constraint, not an afterthought.
• Gold layer exposure: Separate current-state views (for most reports) from point-in-time joins (for analytical accuracy).

Where to go next:

• Explore Delta Lake change data feed (CDF) as an alternative to snapshot-based SCD processing — CDF can give you row-level change events directly from the Delta log, which makes Phase 2 inserts much cheaper on large tables
• Wire your SCD notebook into a full pipeline with alerting — Scheduling and Automating Fabric Data Pipeline Runs with Activity-Level Retries, Alerts, and Email Notifications covers the production orchestration patterns
• Optimize your SCD table's query performance for the Power BI Direct Lake scenario using Z-order and V-Order — Optimizing Delta Table Performance in a Fabric Lakehouse has everything you need
• Consider whether incremental ingestion with watermarks, rather than daily full snapshots, would reduce your processing costs — Incrementally Loading Data into a Fabric Lakehouse with Watermarks and Pipeline Lookup Activities covers that pattern end to end

The investment in getting SCD right pays back every time an analyst can confidently answer "what was the customer's segment when we made that sale?" without hedging. That's the kind of trust that makes a data platform genuinely valuable.