How to Manage Slowly Changing Dimensions in Fabric for Reliable Analytics

You want your analytics to be right, even if your business changes. Dimension data, like customer addresses or product names, can change over time. If you only write over old data, you lose important history. With Slowly Changing Dimensions, especially SCD Type 2, you can track every change.

• SCD Type 2 adds a new row for each change, so you keep both old and new records.

• You can see when and how data changed, which helps with audits and reports.

• Tools like Microsoft Fabric make this easier by letting you add columns like StartDate or EndDate to show changes.

• This clear record helps you answer questions about the past and make better choices.

Key Takeaways

• Slowly Changing Dimensions Type 2 saves every change by making new rows. This way, you always keep important history.

• Add special columns like StartDate, EndDate, and CurrentFlag. These help you know when data changes and which record is the newest.

• Use Microsoft Fabric tools like Dataflows and Notebooks. These tools help you find changes, update records, and keep your data correct.

• Automate your SCD steps with Pipelines. This lets you run steps in order, make fewer mistakes, and keep your data current.

• Check your data often for mistakes like two current records or missing dates. This helps you trust your analytics.

Slowly Changing Dimensions in Fabric

SCD Types Overview

You will find several types of Slowly Changing Dimensions in Microsoft Fabric. Each type handles changes in a different way. Here is a quick overview:

• SCD Type 0: You do not allow any changes. The original values stay the same. This type is simple and does not need extra steps.

• SCD Type 1: You overwrite old values with new ones. This works well when you do not need to keep history.

• SCD Type 2: You keep every change by adding a new row for each update. You use extra columns, like FromDate and ToDate, to track when changes happen. This type is best when you need to see how data changes over time.

• SCD Type 3: You keep only the current and previous values. You add columns for the old value and the date of change.

• SCD Type 4: You split current and historical data into two tables. One table holds the latest values, and another stores all past records.

• Types 5, 6, and 7: These types mix features from other types. They are less common and more complex.

Tip: Most projects in Fabric use Type 1 or Type 2. Type 2 is the top choice when you need to track history.

Why SCD Type 2 Matters

You often need to answer questions about the past. For example, you may want to know how a customer’s address changed or how a product’s price changed over time. SCD Type 2 helps you do this by keeping a full record of every change. When you use SCD Type 2, you add a new row for each update. You also mark the old row as inactive. This way, you never lose old data.

Many businesses choose SCD Type 2 for tasks like tracking customer behavior, following employee promotions, or watching product prices. This method is important in areas like retail and e-commerce. You can see trends, meet rules for keeping records, and understand customer journeys. Unlike other types, SCD Type 2 gives you a complete picture of how your data changes. This makes your analytics more reliable and helps you make better decisions.

Prepare Data Model

Identify Dimension Tables

First, you need to find the right dimension tables for SCD Type 2. Look for tables about people, products, or places, like customers or stores. These tables have details that can change, such as addresses or job titles. Think about if you need to remember these changes. For example, if a salesperson moves to a new region, you might want to see their old sales by region. SCD Type 2 helps you keep every version of a record. This lets you look at trends and check old data. Use SCD Type 2 when you must track changes for important things, like customer contact details. If something changes all the time, or is better as a fact, like daily sales, SCD Type 2 might not be best.

Tip: Pick SCD Type 2 for dimension tables if you must track history for analysis or audits. Always think about how much history you need and how hard it is to manage.

Add Metadata Columns

After you choose your dimension tables, add special columns to track changes. These columns show when a record is good and which one is current.

Here are the main metadata columns you need for SCD Type 2 in Microsoft Fabric:

• EffectiveFromDate and EffectiveToDate: Show when each row is good.

• CurrentFlag (or is_current): Shows if the row is the newest.

• DeletedFlag: Tells if a record is deleted.

• Hash columns: Help you find changes by hashing tracked attributes.

• UpdatedDate: Shows the last time the row changed.

You use these columns to handle updates and keep a clear record of changes. For example, when a customer’s address changes, you set the old row’s end date and current flag. Then you add a new row with the new address and dates. This setup lets you use Fabric tools like Dataflows and Notebooks to help with your SCD Type 2 process.

Implement SCD Type 2

Dataflows for Change Detection

You can use Dataflows in Microsoft Fabric to find changes in your dimension tables. Dataflows help you get, change, and load data with little code. Here is a simple way to set up SCD Type 2 steps using Dataflows:

1. Prepare Your Data
   Load your current dimension table and new source data into your Dataflow. Add a hash column to each set. This hash uses key things, like name and address, to spot changes fast.

2. Detect Changes with Hashes
   Group your dimension table by the hash column. Count how many times each hash shows up. This helps you find which records are the same or different.

3. Assign Surrogate Keys
   Fabric Warehouse does not have identity columns. You must make your own unique keys. Use the biggest key you have and add one for each new record.

4. Find New Records
   Use a left outer join between your source data and the grouped dimension table. If a source record does not match any in the dimension table, it is new. Add columns like StartDate, EndDate (set to null), and IsCurrent (set to true).

5. Find Changed Records
   Use a left anti join to find records in your dimension table that do not match the source data. These are changed records. Mark them as not current by setting IsCurrent to false and EndDate to today’s date.

6. Combine Results
   Put the new and updated records together. Remove the hash column and put your columns in the right order.

7. Add Remaining Records
   Use another left anti join to find records in your dimension table that are not in the new or updated sets. Add these to your final data.

8. Set Up Incremental Refresh
   Use the StartDate column to refresh only new or changed data. This keeps your process quick and smooth.

9. Load Data Back
   Write the final data back to your warehouse. Replace old records and add new ones to keep your history whole.

Tip: Always keep your staging and transformation steps in different Dataflows. This makes your work easier and helps it run better.

Notebooks for SCD Logic

You can also use Notebooks in Microsoft Fabric to handle SCD Type 2 steps. Notebooks let you write code, like PySpark, to control each part of the process. Here is how you can do it:

1. Read Data
   Load your current dimension table and new source data into your Notebook.

2. Identify Record Types
   Use code to compare the two sets. Find:

   • Unchanged records (same in both)

   • New or updated records (in source but not in dimension)

   • Obsolete records (in dimension but not in source)

3. Update Surrogate Keys
   Use window functions to give new keys to new records. Keep the same key for unchanged and obsolete records.

4. Set Metadata Columns
   For new and updated records, set StartDate to today and EndDate to null. For obsolete records, set EndDate to today and IsCurrent to false.

5. Combine All Records
   Put all three sets—new, updated, and unchanged—into one DataFrame.

6. Write Results
   Save the final DataFrame as a Delta table. This keeps your data safe and supports ACID transactions.

Here is a simple PySpark code example for updating records:

from pyspark.sql import functions as F, Window

# Assume df_dim is your dimension table, df_src is your source data

# Find new records
df_new = df_src.join(df_dim, 'business_key', 'left_anti') \
    .withColumn('StartDate', F.current_date()) \
    .withColumn('EndDate', F.lit(None)) \
    .withColumn('IsCurrent', F.lit(True))

# Find changed records
df_changed = df_dim.join(df_src, 'business_key', 'left_anti') \
    .withColumn('EndDate', F.current_date()) \
    .withColumn('IsCurrent', F.lit(False))

# Combine all
df_final = df_dim.unionByName(df_new).unionByName(df_changed)
df_final.write.format("delta").mode("overwrite").save("/path/to/delta/table")

Note: Notebooks give you more control and freedom. You can add checks to make sure only one current record is there for each business key and that dates do not overlap.

Handle New and Changed Records

You need to work with new, changed, and obsolete records carefully to keep your history right. Here is a table that shows what to do for each type:

• Always use surrogate keys to link records. This keeps your fact tables connected and your history safe.

• Never delete old records. Mark them as not current instead.

• Check that only one current record is there for each business key. Make sure EndDate of the old record matches StartDate of the new one. This stops gaps or overlaps in your data.

🛡️ Tip: Check your data often for mistakes. Look for two current records or missing dates. Good data means better analytics.

Now you have a simple way to manage Slowly Changing Dimensions in Microsoft Fabric. By following these steps, you can keep your analytics right and your history complete.

Automate and Validate

Pipelines for Automation

You can use Fabric Data Pipelines to make your Slowly Changing Dimensions process automatic. Pipelines help you control what happens first and next. For example, you can set your pipeline to run the dimension table dataflows before anything else. Then, you can run the fact table dataflows after that. If all steps work, you can refresh your Power BI model. This order keeps your data right and up to date.

Fabric Data Pipelines have many tools to help you:

• Move and change data between sources and your warehouse or lakehouse.

• Load only new or changed records, so you do not repeat work.

• Use loops, conditions, and triggers to run steps only when needed.

• Watch each step to find problems early.

• Break your pipeline into smaller parts. This makes it easier to fix if something goes wrong.

Here is a simple pipeline example for SCD Type 2:

1. Run a Dataflow to find changes in your dimension table.

2. Change and load new or changed records into your warehouse.

3. Refresh your Power BI semantic model if the last steps work.

4. Send an alert if any step does not work.

# Example pipeline steps (pseudocode)
- Run Dataflow: Detect SCD2 changes
- If Success:
    - Load to Warehouse
    - Refresh Power BI Model
- If Failure:
    - Send Alert

Automation in Fabric means you do not have to do each step by hand. This lowers mistakes and keeps your history safe. Tools like DataflowGen2 and Azure Data Factory can find changes, mark old records as inactive, and add new records for you. This makes your SCD Type 2 process faster and more trustworthy.

💡 Tip: Use monitoring tools in Fabric to watch your pipelines. You can set alerts for failures and check logs to see what happened. This helps you fix problems fast.

Validate Historical Data

You must check your data to make sure your history is right. Good checks keep your analytics trustworthy. Here are some ways to check your SCD Type 2 tables:

• Add metadata columns like timestamps, source system IDs, and reasons for change. These help you track every update.

• Use Change Data Capture (CDC) to find and use only changed records. This keeps your history correct and up to date.

• Always close the old record’s time when you add a new one. Set the EndDate of the old row to match the StartDate of the new row.

• Keep audit trails and data lineage. This helps you follow rules and see where data came from.

• Use tools like Debezium or Apache Hudi if you need to check more data and automate checks.

You should also do these things to keep your data clean:

1. Watch for duplicate records. Make sure you do not add the same record twice in one batch or across batches.

2. Do not add a new record if a current valid record already exists for the same business key.

3. Use timestamps or source info to tell apart records that look the same.

4. Check for strange changes, like records that change too often or have bad data.

5. Add rules and checks during loading to stop bad data from getting in.

Microsoft Fabric gives you tools to help with checks. You can use audit logs and activity monitoring to track every step. Data Activator lets you set up alerts for special cases. You can also use Power Automate to send messages if something fails. For deeper checks, you can run Python scripts to scan your Delta tables and log any problems.

🛡️ Note: Always check that you have only one current record for each business key. Make sure your dates do not overlap. Good checks keep your Slowly Changing Dimensions process strong and your analytics reliable.

Best Practices

Tool Selection

You have several tools in Microsoft Fabric to manage Slowly Changing Dimensions. Each tool works best for different needs.

• Dataflow Gen2 gives you a low-code way to handle SCD Type 2. You can track changes by adding new records and marking old ones as inactive. This tool works well for simple cases, like tracking changes in student data or customer addresses.

• Notebooks let you write code for more complex tasks. You can use PySpark to handle tricky updates or mix Type 1 and Type 2 changes in the same table. Notebooks give you the most control, but you need to know how to code.

• Pipelines help you schedule and automate your SCD process. You can run Dataflows and Notebooks in order, making sure each step happens at the right time.

💡 Tip: Choose your tool based on your team’s skills and the job’s complexity. For simple SCD Type 2, start with Dataflows. For advanced needs, use Notebooks and Pipelines together.

A hybrid approach often works best. You can keep history for some columns (Type 2) and overwrite others (Type 1). This method uses surrogate keys and works well in Fabric with Notebooks.

Common Pitfalls

You can avoid many problems by following a few key rules:

• Do not rely on Power Query alone. It cannot track history. Always use a data warehouse and set up regular ETL jobs.

• Do not skip surrogate keys. These keys help you link records and keep your data clean.

• Do not forget to set start and end dates for each record. This keeps your history clear.

• Do not ignore deleted or changed records. Always mark them with end dates or flags.

• Do not try to build everything at once. Start with the most important tables and add more over time.

🛡️ Note: Plan your data warehouse as a long-term project. Focus on quality and accuracy. Expand step by step, and always check your data for mistakes.

---

You can handle Slowly Changing Dimensions in Fabric by using simple steps. First, find your dimension tables. Next, add metadata columns to them. Then, use Dataflows or Notebooks to spot changes. Pipelines help you automate these steps. SCD Type 2 lets you save every version of your data. This helps you see trends and make smart choices. You get reliable analytics because you always know the real history. Try out different Fabric tools to see what fits your team. Keep learning about new features to get even better.

FAQ

What is the best way to start with SCD Type 2 in Fabric?

You should begin by picking one dimension table. Add the needed metadata columns. Use Dataflows for simple changes. Try Notebooks if you want more control. Test your process before using it on all tables.

How do you know if a record has changed?

You can use a hash column to compare key fields. If the hash value changes, the record has changed. Dataflows and Notebooks both help you spot these changes quickly.

Can you mix SCD Type 1 and Type 2 in the same table?

Yes, you can. Track some columns with Type 2 to keep history. Use Type 1 for others to overwrite values. Notebooks in Fabric let you set up this hybrid approach.

What should you do if you find duplicate current records?

You must fix this right away. Only one current record should exist for each business key. Use validation steps in your pipeline to catch and correct duplicates.

Do you need to update fact tables when dimension records change?

Yes. Fact tables should link to the correct version of each dimension record. Use surrogate keys to keep this link strong. This ensures your reports show the right history.