Data Engineering Data Warehouse Modeling 3

Question 1

A Slowly Changing Dimension Type 2 handles attribute changes by:

Accepted Answer

Adding a new row to preserve history

Answer

SCD Type 2 inserts a new dimension row for each change, preserving full historical context.

Question 2

An SCD Type 1 approach manages a changed attribute by:

Accepted Answer

Overwriting the existing value with no history

Answer

SCD Type 1 simply overwrites the old value, keeping no record of prior states.

Question 3

Which columns are typically added to support an SCD Type 2 dimension?

Accepted Answer

Effective date, expiration date, and a current flag

Answer

SCD Type 2 uses effective/expiration dates and a current-record indicator to track row validity periods.

Question 4

An SCD Type 3 dimension tracks change by:

Accepted Answer

Storing a limited 'previous value' column alongside the current value

Answer

SCD Type 3 keeps a previous-value column, allowing comparison between current and one prior state.

Question 5

A rapidly changing attribute (like customer age band) is best handled by splitting it into a:

Accepted Answer

Mini-dimension (junk or demographic dimension)

Answer

Volatile attributes are moved into a mini-dimension to avoid exploding the main dimension's row count.

Data Engineering Practice Test