nerdexam
Microsoft

70-463 · Question #1

You are reviewing the design of a customer dimension table in an existing data warehouse hosted on SQL Azure. The current dimension design does not allow the retention of historical changes to custome

The correct answer is A. Add StartDate and EndDate columns to the customer dimension.. This question tests knowledge of Slowly Changing Dimension Type 2 (SCD2) design, which supports full historical tracking of attribute changes by adding date range columns.

Design and implement a data warehouse

Question

You are reviewing the design of a customer dimension table in an existing data warehouse hosted on SQL Azure. The current dimension design does not allow the retention of historical changes to customer attributes such as Postcode. You need to redesign the dimension to enable the full historical reporting of changes to multiple customer attributes including Postcode. What should you do?

Options

  • AAdd StartDate and EndDate columns to the customer dimension.
  • BAdd an IsCurrent column to the customer dimension.
  • CEnable Snapshot Isolation on the data warehouse.
  • DAdd CurrentValue and PreviousValue columns to the customer dimension.

How the community answered

(18 responses)
  • A
    78% (14)
  • B
    6% (1)
  • C
    11% (2)
  • D
    6% (1)

Why each option

This question tests knowledge of Slowly Changing Dimension Type 2 (SCD2) design, which supports full historical tracking of attribute changes by adding date range columns.

AAdd StartDate and EndDate columns to the customer dimension.Correct

Adding StartDate and EndDate columns implements SCD Type 2, where each change to a customer attribute creates a new row with a date range defining the validity period. This allows full historical reporting by querying which row was active at any point in time, covering multiple attribute changes across all customers.

BAdd an IsCurrent column to the customer dimension.

An IsCurrent flag only marks the latest record but does not store date boundaries, making it impossible to reconstruct attribute values at arbitrary historical points.

CEnable Snapshot Isolation on the data warehouse.

Snapshot Isolation is a database-level transaction isolation setting that prevents dirty reads; it has no effect on dimension history or data model design.

DAdd CurrentValue and PreviousValue columns to the customer dimension.

CurrentValue and PreviousValue columns can only store one level of change history, not the full chain of historical values needed for complete historical reporting.

Concept tested: Slowly Changing Dimension Type 2 design

Source: https://learn.microsoft.com/en-us/azure/architecture/data-guide/relational-data/data-warehousing

Topics

#slowly changing dimensions#SCD Type 2#customer dimension#historical tracking

Community Discussion

No community discussion yet for this question.

Sign up to join the discussion
Full 70-463 Practice