Which Slowly Changing Dimension type preserves historical records by creating new rows for changes?

Enhance your skills with the CompTIA Data+ Certification Test. Engage with flashcards, tackle challenging multiple choice questions, complete with hints and explanations. Get yourself exam-ready now!

Multiple Choice

Which Slowly Changing Dimension type preserves historical records by creating new rows for changes?

To preserve a complete history of changes in a dimension, you version the records instead of updating them in place. Type 2 Slowly Changing Dimension does this by inserting a new row every time a relevant attribute changes, giving the new row its own surrogate key and marking the previous version as no longer current (often with a start_date and end_date or a current flag). This approach keeps every historical state and lets you query what the dimension looked like at any point in time by selecting the row with the appropriate date range or current flag. Other methods overwrite data, store only a partial history in the same row, or use a separate history table, so they don’t provide the full, row-by-row history that Type 2 does.

To preserve a complete history of changes in a dimension, you version the records instead of updating them in place. Type 2 Slowly Changing Dimension does this by inserting a new row every time a relevant attribute changes, giving the new row its own surrogate key and marking the previous version as no longer current (often with a start_date and end_date or a current flag). This approach keeps every historical state and lets you query what the dimension looked like at any point in time by selecting the row with the appropriate date range or current flag. Other methods overwrite data, store only a partial history in the same row, or use a separate history table, so they don’t provide the full, row-by-row history that Type 2 does.

More practice questions