What are Slowly Changing Dimensions (SCD)?

Updated: Sep 2

SCDs refer to a data warehousing concept where dimensions contain both current and historical data. At a basic level, the dimension will change slowly over time rather than at a scheduled interval — there is no fixed pattern in the frequency of the dimension changes. There are 4 types of SCD we are going to cover Types #1 and #2 in this blog.

Type #1 – The data is replaced, and no history is retained. In the example below we can see there are five records that comprise the Engine table. We find there is a mistake data the Lycoming TIO-540 actually has 700 HP (horse power) instead of the 270. In a Type 1 SCD we would overwrite the data, there is no IsCurrent column to manage. Type #1 is simple and commonly used when you erase all of the data in the warehouse and rebuild daily, what I call nuke and rebuild.

Pro - Simple and easy to manage – Can support very simple ETL – Disaster or Missed imports are easily corrected Con – No History is retained

Type #2 – In a Type #2 the history is retained, we can see this in a new record added #6 and a IsCurrent column is updated.

Pro – History is retained Con – More Complex - disaster or missed imports are difficult to correct – Must manage IsCurrent column

3 views0 comments

Recent Posts

See All

On one of my first forays into consulting I was recruited by a staffing agency recruiter. The end customer wanted to hire be as a production database administrator for SQL Server. The recruiter aske

One of the areas where I have found success as contract DBA is the mundane task of backups, tuning and installation. Most of my customers have automated backup performance monitoring solutions that

I think the DBA role will be more critical in the coming years. I have bet my business and career on this belief. While, databases are becoming more reliable and require less technical maintenance t