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…

The correct answer is B. MERGE. MERGE is the optimal T-SQL statement for loading a Type 2 slowly changing dimension (SCD). A Type 2 SCD preserves full history by expiring old rows (UPDATE the end-date/flag) and inserting new rows for changed records in a single, atomic operation. MERGE accomplishes both…

Submitted by layla.eg· Apr 18, 2026Prepare data

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?

Options

AUPDATE AND INSERT
BMERGE
CTRUNCATE TABLE and INSERT
DCREATE TABLE AS SELECT

How the community answered

(39 responses)

A
3% (1)
B
79% (31)
C
10% (4)
D
8% (3)

Explanation

MERGE is the optimal T-SQL statement for loading a Type 2 slowly changing dimension (SCD). A Type 2 SCD preserves full history by expiring old rows (UPDATE the end-date/flag) and inserting new rows for changed records in a single, atomic operation. MERGE accomplishes both actions in one pass against the target table, minimizing I/O and locking compared to running separate UPDATE and INSERT statements. TRUNCATE TABLE + INSERT destroys all historical rows, violating the Type 2 requirement. CREATE TABLE AS SELECT rewrites the entire table and does not support incremental change detection. Separate UPDATE and INSERT statements require multiple table scans and are less resource-efficient than a single MERGE.

Topics

#Slowly Changing Dimensions (SCD)#MERGE statement#Data Warehousing#T-SQL optimization

Community Discussion

No community discussion yet for this question.

Full DP-600 Practice