Data Warehousing
  • Data Warehousing
  • Readme
  • Fundamentals
    • Terms to Know
    • Jobs
    • Skills needed for DW developer
    • Application Tiers
    • Operational Database
    • What is a Data Warehouse
      • Typical Data Architecture
      • Problem Statement
      • Features of Data Warehouse
      • Need for Data Warehouse
      • Current State of the Art
    • Activities of Data Science
    • Types of Data
    • Data Storage Systems
    • Data Warehouse 1980 - Current
    • Data Warehouse vs Data Mart
    • Data Warehouse Architecture
      • Top-Down Approach
      • Bottom-Up Approach
    • Data Warehouse Characteristic
      • Subject Oriented
      • Integrated
      • Time Variant
      • Non Volatile
    • Tools
    • Cloud vs On-Premise
    • Steps to design a Data Warehouse
      • Gather Requirements
      • Environment
      • Data Modeling
      • Choosing ETL / ELT Solution
      • Online Analytic Processing
      • Front End
      • Query Optimization
    • Dataset Examples
    • Thoughts on some data
  • RDBMS
    • Data Model
      • Entity Relationship Model
      • Attributes
      • Keys
      • Transaction
      • ACID
    • Online vs Batch
    • DSL vs GPL
    • Connect to Elvis
    • SQL Concepts
      • Basic Select - 1
      • Basic Select - 2
      • UNION Operators
      • Wild Cards & Distinct
      • Group By & Having
      • Sub Queries
      • Derived Tables
      • Views
    • Practice using SQLBolt
  • Cloud
    • Overview
    • Types of Cloud Services
    • Challenges of Cloud Computing
    • AWS
      • AWS Global Infrastructure
      • EC2
      • S3
      • IAM
    • Terraform
  • Spark - Databricks
    • Storage Formats
    • File Formats
    • Medallion Architecture
    • Delta
  • Data Warehousing Concepts
    • Dimensional Modelling
      • Star Schema
      • Galaxy Schema
      • Snowflake Schema
      • Starflake Schema
      • Star vs Snowflake
      • GRAIN
      • Multi-Fact Star Schema
      • Vertabelo Tool
    • Dimension - Fact
    • Sample Excercise
    • Keys
      • Why Surrogate Keys are Important
    • More Examples
    • Master Data Management
    • Steps of Dimensional Modeling
    • Types of Dimensions
      • Date Dimension Table
      • Degenerate Dimension
      • Junk Dimension
      • Static Dimension
      • Conformed Dimensions
      • Slowly Changing Dimensions
        • SCD - Type 0
        • SCD - Type 1
        • SCD - Type 2
        • SCD - Type 3
        • SCD - Type 4
        • SCD - Type 6
        • SCD - Type 5 - Fun Fact
      • Role Playing Dimension
      • Conformed vs Role Playing
      • Shrunken Dimension
      • Swappable Dimension
      • Step Dimension
    • Types of Facts
      • Factless Fact Table
      • Transaction Fact
      • Periodic Fact
      • Accumulating Snapshot Fact Table
      • Transaction vs Periodic vs Accumulating
      • Additive, Semi-Additive, Non-Additive
      • Periodic Snapshot vs Additive
      • Conformed Fact
    • Sample Data Architecture Diagram
    • Data Pipeline Models
    • New DW Concepts
Powered by GitBook
On this page
  1. Data Warehousing Concepts
  2. Types of Dimensions

Slowly Changing Dimensions

Slowly Changing Dimensions (SCDs) is a concept in data warehousing and business intelligence that deals with managing changes in dimension data over time.

Data Warehousing has

- Time Variant (with history data)
- Non Volatile (no changes)

But in reality

- The customer may change his address.
- Store move to a new location.
- An employee joins a new company.

Dimension data refers to the descriptive attributes of an entity, such as customer, product, or location. These attributes provide context for analysis and reporting in a data warehouse.

However, dimension data can change over time, and it's essential to maintain a historical record of these changes for accurate reporting and analysis.

There are several types of SCDs, each with different strategies for managing changes in dimension data:

Type 1 - Overwrite: In this approach, when changes occur, the existing values are overwritten with the new values. No historical data is preserved.

Type 2 - Add a New Row: This method maintains a complete history of changes by adding a new row with the updated attribute values in the dimension table. Each row has a start and end date (or a start date and a flag indicating the current record) to show the period during which the attribute values were valid.

Type 3 - Add a New Column: This approach maintains a limited history of changes by adding new columns to the dimension table to store the previous values of changed attributes. It is useful when tracking a small number of changes but can become unwieldy with many changes.

Type 4 - Add a History Table: In this method, a separate history table stores the changes in the dimension attributes. The primary dimension table contains the current attribute values, while the history table stores historical data.

Type 6 - Hybrid: This combines Type 1, Type 2, and Type 3 approaches. It allows for the selective preservation of history for specific attributes and can be used to overwrite certain attribute values.

Choosing the appropriate SCD type depends on the specific requirements of the data warehouse, the importance of historical data for analysis, and the performance implications of each approach.

PreviousConformed DimensionsNextSCD - Type 0

Last updated 2 years ago