DP-600: Implementing Analytics Solutions Using Microsoft FabricDemo

By Microsoft · Question Mode

QuestionQ2

Prepare data

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?

  • A UPDATE AND INSERT
  • B MERGE
  • C TRUNCATE TABLE and INSERT
  • D CREATE TABLE AS SELECT
Explanation

MERGE lets you insert new dimension rows and update/expire changed ones in a single set-based statement, which is exactly the pattern a Type 2 SCD load needs and avoids the extra table scans and log overhead of running separate UPDATE and INSERT statements. TRUNCATE TABLE AND INSERT would destroy the historical rows an SCD Type 2 is designed to preserve, and CREATE TABLE AS SELECT rebuilds the entire table rather than incrementally merging changes into it.

Community Discussion

No comments yet. Be the first to start the discussion!