Home / Blogs / Uses of Building an SCD Data Warehouse in Astera DW Builder

Table of Content
The Automated, No-Code Data Stack

Learn how Astera Data Stack can simplify and streamline your enterprise’s data management.

Uses of Building an SCD Data Warehouse in Astera DW Builder

November 11th, 2022

Slowly changing dimensions

An SCD Data Warehouse is the crux to tracking changes in data. Change is ever-present in every business. From an HR perspective, employees are promoted and gain new titles, while remuneration and insurance policies are rewritten with each passing year. In Marketing, product lines are renamed, merged, or launched to great fanfare. In Sales, new clients arrive, and key accounts are transferred from one agent to another.

The question is, do your reports account for this constant evolution? Is your BI architecture built to provide both a current and historical view of your data, or do you only get periodic snapshots of each source system?

For someone building a data warehouse, the best way to deal with changing attributes is through slowly changing dimensions. Slowly changing dimensions in a data warehouse store both current and previous information over time. These tables reflect changes as they occur within selected attributes in each dimension. Depending on your requirements, they can overwrite existing entries, or create additional fields to show previous versions of a record.

Astera’s new data warehouse builder (ADWB) provides a complete data model designer that makes it easy to set up your dimension tables and assign different SCD types to selected attributes. Let’s take a look at some of the use cases our product can help you tackle.

Correcting Erroneous Records in an SCD Data Warehouse

Any source system that relies upon manual data entry is prone to human error. From names and addresses to quotes and estimates, there are various records across your source systems that may need to be fixed for accuracy. In such cases, maintaining a previous version of a record in the data warehouse would only add useless complexity to your data. The best solution is to treat entries in these fields as SCD-1 attributes, where current values overwrite the previous data.

scd data warehouse

Sales Data Mart Built Using The DWB Data Model Designer

Let’s say you’ve used DWB to engineer a dimensional model of your sales system, and you’ve noticed some incorrect customer names in the latest batch of updates. Just open up the Customer dimension in the data model designer and set the Customer Name as SCD1 – Update from the dropdown menu.

slowly changing dimension types

Setting SCD Type For CustomerName Attribute

Now, when corrections are made in the source database, the corresponding record will be updated in the data warehouse, as shown below. This is called type 1 dimension in a data warehouse.

Customer Dimension Table Before Correction

scd data warehouse 2

Customer Dimension Table with SCD1 Applied to Customer Name Field

scd data warehouse 3

Comparing Current vs. Past Data in a Dimensional Data Warehouse

One of the enterprise data warehouse’s key functions is supporting long-term strategic analysis, which requires changes within an attribute to be preserved and tracked over time.

Say your company recently merged several district branches and created new regions for its store outlets. If you want to track how sales performance is affected by the reorganization, you could treat the District field as a type 2 slowly changing dimension. This will ensure that changes to store districts are shown as new records within the table.

In DWB, you can apply the SCD2 – Update and Insert option for the District field in the Store dimension table. As changes are made to the source attribute, new versions of the record would be created and inserted into the dimension table, with a unique surrogate key assigned to each one. Based on the type of analysis the company wants to carry out, you can also select the Effective Date and Expiration Date options so that any changes in store regions can be tracked to a specific period.

The table below illustrates how these configurations would affect the output of your dimension table.

Existing Table

scd types

Updated Table – SCD2 (Effective Date and Expiration Date)

Tracking Changes in Status

Certain departments, such as HR, expect frequent changes to records within their systems. For example, an employee’s designation can change multiple times a year. In these cases, companies generally want to be able to maintain the history of data while still being able to quickly query it based on the most current attribute value.
In DWB, you could choose to deal with this situation by setting up the Job Title as an SCD6 field. This means that the table would use elements of SCD1, SCD2, and SCD3 to record changes in this attribute. These elements are as follows:

  • A row is added to track changes in the attribute as they occur (SCD2)
  • An additional column shows the current value for the attribute (SCD3)
  • The current value field will be overwritten to show the updated attribute value (SCD1)

The example below shows how changes to the Job_Title field would appear if SCD6 is implemented.

Current Table

slowly changing dimension in data warhouse

Updated Table – SCD6 (Active Flag, Effective Date, and Expiration Date)

Automate Your Enterprise SCD Data Warehouse

Astera’s metadata-driven Data Warehouse Builder can help you design, build, and deploy your data warehouse project in days. With a full range of dimensional modeling features and a robust ETL platform to fast-track data mapping, loading, and preparation, we give you a single platform with all the tools you need to make your EDW implementation a success.

Get a look at how our product can help you solve your use case today. Click here to get in touch with our technical team.

As the same suggests, slowly changing dimension types (SCD) are utilized to capture the slow changes within a data warehouse over time, rather than on a continuous schedule. The SCD data warehouse helps report historical data and associate dimensional attributes to any given data.

There are six types of slowly changing dimensions (SCD):

  • Type 0 or Fixed Dimension: In this fixed dimension, no changes are allowed as the dimension never changes
  • Type 1 or No History: The records are updated directly with no records of historical values in this type.
  • Type 2 or Row Versioning: New additional records can be created in this type, and the changed records can be tracked with flags and operational data.
  • Type 3 or Previous Value column: New column can be added in this type, and changes can be tracked to a particular attribute.
  • Type 4 or History Table: It uses a historical table to record all the changes while showing the current value in a dimensional table.
  • Type 6 or Hybrid SCD: It combines techniques from SCD (Slowly Changing Dimension) Types 1, 2, and 3 to track change

Some of the common SCD examples and uses cases in data warehouse are:

  • Fixing Erroneous Records
  • Comparing Current Vs. Past Data 
  • Tracking Changes in Status  

Modernizing Unstructured Data Processing With AI
Optimizing Auto Insurance Claims Processing with Astera
Beyond OCR Form Processing: The Comprehensive Guide to Intelligent Form Data Extraction 
Considering Astera For Your Data Management Needs?

Establish code-free connectivity with your enterprise applications, databases, and cloud applications to integrate all your data.

Let’s Connect Now!