Loading Type 2 SCD in Fabric Data Warehouse

DP-600 Last updated

Transform data

Short answer

A You must use UPDATE AND INSERT to load a Type 2 SCD in Fabric because MERGE is unsupported.

Question

You have a Fabric tenant that contains a data warehouse. You need to load rows into a large Type 2 slowly changing dimension (SCD). The solution must minimize resource usage. Which T-SQL statement should you use?

  1. UPDATE AND INSERT Correct Answer
  2. MERGE
  3. TRUNCATE TABLE and INSERT
  4. CREATE TABLE AS SELECT

Key Takeaways

The question tests knowledge of Fabric data warehouse T-SQL limitations, specifically that MERGE is unsupported, making UPDATE and INSERT the required method for Type 2 SCDs.

Loading rows into a Type 2 slowly changing dimension in a Microsoft Fabric data warehouse requires specific T-SQL statements because MERGE is unsupported. This page establishes that using separate UPDATE and INSERT statements is the correct approach to minimize resource usage.

Common mistake: Choosing MERGE (B) because it is the standard T-SQL method for SCDs in traditional SQL Server, but it is currently unsupported in Fabric.

Expert Analysis

Why the Answer Is Correct

Option A (UPDATE AND INSERT) is correct because Microsoft Fabric data warehouse currently does not support the T-SQL MERGE statement. To implement a Type 2 slowly changing dimension, which requires updating existing records (e.g., setting an end date) and inserting new records, separate UPDATE and INSERT statements are the supported workaround in Fabric.

Why the Other Options Are Wrong

Option B (MERGE) is the traditional go-to statement for SCDs in SQL Server, but it is explicitly listed as unsupported in the Fabric data warehouse T-SQL surface area. Option C (TRUNCATE TABLE and INSERT) would delete all existing data, destroying the historical tracking required by a Type 2 SCD. Option D (CREATE TABLE AS SELECT) creates a new table rather than updating or inserting into an existing one, making it unsuitable for ongoing SCD Type 2 processing.

Community Comment Notes

Multiple commenters pointed out that MERGE is not currently supported in Fabric, such as AdventureChick who stated "MERGE is not currently supported in Fabric". This limitation forces the use of UPDATE and INSERT instead of the typical MERGE statement, as notsqlbot also noted by saying "Normally I would say its MERGE. However MERGE is not currently supported by Fabric".

Official Reference

https://learn.microsoft.com/en-us/fabric/data-warehouse/tsql-surface-area#limitations

Exam Strategy

Always check the T-SQL surface area limitations for Microsoft Fabric data warehouse before answering query-related questions. Standard SQL Server features like MERGE may be unsupported, requiring alternative logic like separate UPDATE and INSERT statements.

Community Votes

A
62%
B
38%

62% of anonymous learners picked answer A. Votes are pick records left by other test-takers — they are not the verified answer.

Discussion 4 contributions

Compiled from public community threads and reader submissions, lightly edited for clarity. Handles and vote tallies are shown as recorded.

From public community threads 4

  1. AdventureChick 👍 4 Answered A
    MERGE is not currently supported in Fabric: https://learn.microsoft.com/en-us/fabric/data-warehouse/tsql-surface-area#limitations so it must be UPDATE and INSERT C – TRUNCATE TABLE AND INSERT would eliminate history (which is the whole point of SCD Type 2) D – CREATE TABLE AS SELECT – this can be used for an initial load if the table doesn’t exist, but it won’t work for an SCD Type 2 because it would eliminate retention of historical changes.
  2. notsqlbot 👍 4 Answered A
    Normally I would say its MERGE. However MERGE is not currently supported by Fabric (I think its due to be GA Q1 2025),so the answer needs to be UPDATE and INSERT.
  3. MultiCloudIronMan 👍 3 Answered B
    Its a merge with merge you can define what happens after the merge
  4. Pegooli 👍 2 Answered B
    Merg allow you to do : -Insert new records for changes. -Update existing records to mark them as historical. -Maintain the history of changes efficiently.

Reader comments & corrections

No reader corrections yet — spotted an error or have a note? Share it below.

Log in to comment, report an error, or add a note about this question.

Submitted for moderation before publishing. Keep it helpful and respectful.

Fabric Deployment Pipeline Folder Structure Behavior

S-Grade

Accessing OneLake Shortcuts in Fabric Notebooks

S-Grade

Direct Lake Fallback to DirectQuery with Row-Level Security

S-Grade

Practice All DP-600 Questions

Access 115 questions with complete answers and detailed explanations.

View Full DP-600 Practice Test →

← Back to DP-600 Study Guide