Data Engineering Slowly Changing Dimensions (SCDs) 3

Question 1

When loading a Type 2 dimension, a record's tracked attribute changes. What is the correct sequence?

Accepted Answer

Expire the current row, then insert a new row marked current

Answer

You close the existing version (set end_date, is_current=false) and insert a fresh current row.

Question 2

Which technique efficiently detects whether any tracked column changed during a Type 2 load?

Accepted Answer

Comparing a hash of the tracked columns

Answer

Hashing the tracked columns lets a single comparison detect any change quickly.

Question 3

A late-arriving dimension means a fact references a dimension member that:

Accepted Answer

Does not yet exist in the dimension when the fact loads

Answer

Late-arriving dimensions require inserting a placeholder member so the fact can be loaded.

Question 4

How is a high-end_date often represented for the current Type 2 row?

Accepted Answer

A far-future sentinel date like 9999-12-31

Answer

A far-future sentinel keeps BETWEEN range queries simple while marking the row as open.

Question 5

What problem can occur if a non-tracked attribute is mistakenly configured as Type 2?

Accepted Answer

Unnecessary version explosion bloating the dimension

Answer

Treating volatile non-essential columns as Type 2 creates many rows and inflates storage.

Data Engineering Practice Test