Slowly changing dimensions

Introduction

First things first, we need to recall what dimension is in the context of databases and warehouses. A table consists of rows and columns. Rows represent individual records or data points, while columns represent specific attributes or characteristics of those records.

Dimensions refer to the number of columns in a table. A table with customer information might have several dimensions, such as customer ID, name, address, and phone number.

SCD refers to Slowly Changing Dimensions. These are dimensions that change over time, but not in a regular or predictable way. Changes could occur due to updates, corrections, or new information becoming available.

There are a number of ways to handle dimension management, and we are about to discuss them in the next series of posts.

Lots of thanks to my fellow Aleksandr Laperdin for the inspirational post on this.

1. Add New Row, Natural and Surrogate Keys

• A natural key (also known as a business key or domain key) is a type of unique key in a database that has meaning outside the database itself and is based on real-world observation.
• A surrogate key (or synthetic key, pseudokey, or factless key) is, in turn, not derived from application data.

So, the second way is as follows: it tracks historical data by creating multiple records for a given natural key in the dimensional tables with separate surrogate keys and/or different version numbers. Thus, we have the ability to store a potentially unlimited number of versions.

Example with version numbers:

+-----+------+----+-----+
  Key | Code | St | Ver 
+-----+------+----+-----+
  123 | ABC  | CA | 0   
+-----+------+----+-----+
  124 | ABC  | IL | 1   
+-----+------+----+-----+

Example with ’effective date’ columns (date1 < date2 < date3):

+-----+-----+----+-----+-----+
  Key | ... | St |Start|End  
+-----+-----+----+-----+-----+
  123 | ... | CA |date1|date2
+-----+-----+----+-----+-----+
  124 | ... | IL |date3|NULL 
+-----+-----+----+-----+-----+

Note that a standardized surrogate high date (e.g. 9999-12-31) may instead be used as an end date, so that the field can be included in an index and so that null-value substitution is not required when querying.

In some database software, using an artificial high date value could cause performance issues that using a null value would prevent.

2. Add a New Attribute

This method tracks changes using separate columns and preserves a limited history. It is limited to the number of columns designated for storing historical data. In the following example, an additional column has been added to the table to record the supplier’s original state; only the previous history is stored:

+-----+------+-------+------+
  Key | Code | Prev  | Cur  
+-----+------+-------+------+
  123 | ABC  | CA    | IL   
+-----+------+-------+------+

3. Add History Table

This method is usually referred to as using “history tables”, where one table keeps the current data, and an additional table is used to keep a record of some or all changes.

For the example below, the original table name is supplier and the history table is supplier_history.

supplier:

+-----+------+-------+
  Key | Code | State 
+-----+------+-------+
  124 | ABC  | IL    
+-----+------+-------+

supplier_history:

+-----+------+----+------+
  Key | Code | ST | Date 
+-----+------+----+------+
  123 | ABC  | CA | 2023 
+-----+------+----+------+
  124 | ABC  | IL | 2024 
+-----+------+----+------+

4. Combined Approach

This method combines the approaches of some previous types. It’s also named “Unpredictable Changes with Single-Version Overlay”.

+---+---+----+-----+----+------+
 Key|Cur|Prev|Start|End |Latest
+---+---+----+-----+----+------+
 123|CA |CA  |2023 |2024| Y    
+---+---+----+-----+----+------+

When change happens, we add a new record this way:

+---+---+----+-----+----+------+
 Key|Cur|Prev|Start|End |Latest
+---+---+----+-----+----+------+
 123|IL |CA  |2023 |2024| N    
+---+---+----+-----+----+------+
 123|IL |IL  |2024 |NULL| Y    
+---+---+----+-----+----+------+

Heads up, you have to add surrogate key column as well.

5. Temporal Tables

As we saw above, managing SCD can be tricky because you need to track the changes while maintaining historical data for analysis.

This is where temporal tables come in. They allow you to store the entire history of changes for each dimension member, providing a complete picture of how the dimension has evolved over time.

SQL Server, Oracle, IBM DB2 and MariaDB support temporal tables out-of-box.

For Postgres, temporal tables are available through extensions:

• https://clarkdave.net/2015/02/historical-records-with-postgresql-and-temporal-tables-and-sql-2011/
• https://github.com/arkhipov/temporal_tables

Functionality often includes:

• Stores history. Each row in a temporal table has additional columns capturing its start and end validity periods. This enables access to any version of the data at a specific point in time.

• Automatic versioning. When you modify data in a temporal table, the old version is automatically saved as a new historical record, preserving the complete change history.

• Point-in-time queries. You can write queries to specifically target any past moment or timeframe, analyzing how data looked at that particular time.

• Audit trails. Temporal tables simplify auditing changes made to data, providing a valuable log for compliance and security purposes.