nerdexam
Microsoft

DP-700 · Question #91

You have a Fabric warehouse named DW1 that contains a Type 2 slowly changing dimension (SCD) dimension table named DimCustomer. DimCustomer contains 100 columns and 20 million rows. The columns are…

The correct answer is A. a hash function to compare the attributes in the source table. To efficiently identify changes in a large Type 2 SCD dimension table with many columns, using a hash function on the relevant attributes in the source data is the most resource-efficient method.

Design and implement data ingestion and transformation

Question

You have a Fabric warehouse named DW1 that contains a Type 2 slowly changing dimension (SCD) dimension table named DimCustomer. DimCustomer contains 100 columns and 20 million rows. The columns are of various data types, including int, varchar, date, and varbinary. You need to identify incoming changes to the table and update the records when there is a change. The solution must minimize resource consumption. What should you use to identify changes to attributes?

Options

  • Aa hash function to compare the attributes in the source table.
  • Ba direct attributes comparison across the attributes in the DimCustomer table.
  • Ca direct attributes comparison for the attributes in the source table.

How the community answered

(38 responses)
  • A
    71% (27)
  • B
    11% (4)
  • C
    18% (7)

Why each option

To efficiently identify changes in a large Type 2 SCD dimension table with many columns, using a hash function on the relevant attributes in the source data is the most resource-efficient method.

Aa hash function to compare the attributes in the source table.Correct

Calculating a hash value across all relevant attributes in the source table allows for a single, fast comparison between the source hash and the target hash to detect changes, which significantly minimizes resource consumption compared to performing direct attribute-by-attribute comparisons across many columns, especially for wide tables.

Ba direct attributes comparison across the attributes in the DimCustomer table.

A direct attributes comparison across 100 columns in the `DimCustomer` table for each row would involve many individual column comparisons, leading to high resource consumption and slow processing, especially with 20 million rows.

Ca direct attributes comparison for the attributes in the source table.

A direct attributes comparison for the attributes in the source table, while part of the change detection process, does not fully address the efficiency of comparing against the target table's existing records for identifying changes.

Concept tested: Efficient SCD change detection with hashing

Source: https://learn.microsoft.com/en-us/azure/architecture/data-analytics-end-to-end/dimension-tables#slowly-changing-dimensions

Topics

#SCD#Data Transformation#Performance Optimization#Hashing

Community Discussion

No community discussion yet for this question.

Sign up to join the discussion
Full DP-700 Practice