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
  3. Slowly Changing Dimensions

SCD - Type 0

Type 0 - Retain Original: This approach ignores changes and retains the original values for the dimension attributes. No history is kept for changes.

The Product dimension table might include the following columns:

  1. Product_ID

  2. Product_Name

  3. Category

  4. Price

Now, imagine that the store decides to re-categorize one of its products. For instance, they may change the "Smartphone" product from the "Electronics" category to the "Mobile Devices" category.

Using a Type 0 Slowly Changing Dimension approach, the store will retain the original value for the product category, ignoring the change. In this case, the Product dimension table would still show the "Smartphone" product in the "Electronics" category, even though it has been re-categorized to "Mobile Devices." This approach means no history is kept for changes, and the original values are always preserved.

The Product dimension table would look like this:

Product_ID
Product_Name
Category
Price

1

Smartphone

Electronics

1000

Use Cases:

  1. When historical data is not relevant or necessary for analysis, and only the original values are needed.

  2. For dimensions with attributes that are fixed and don't change over time, such as unique identifiers or codes.

  3. In cases where the data warehouse is only required to support reporting and analysis on the current state of the business and not the historical trends.

Advantages:

  1. Simplicity: SCD Type 0 is the simplest approach, as it doesn't require any additional mechanisms to handle changes in dimension attributes.

  2. Space Efficiency: Since there is no need to store historical data or multiple versions of records, the dimension tables will be smaller and require less storage space.

  3. Performance: As there are no additional rows or columns for historical data, the querying and processing of the data warehouse will generally be faster.

Disadvantages:

  1. Lack of Historical Data: SCD Type 0 does not store any historical data, which means it cannot support reporting and analysis that requires tracking changes over time. This can be a significant limitation for businesses that need to analyze trends, understand the impact of changes, or perform other historical analyses.

  2. Inaccurate Analysis: Since the dimension table only contains the original values, any changes that have occurred over time are not reflected. This may lead to incorrect analysis results or conclusions based on outdated information.

  3. Inability to Track Changes: With SCD Type 0, it is impossible to determine when or why changes occurred, as there is no record of any changes in the dimension data.

PreviousSlowly Changing DimensionsNextSCD - Type 1

Last updated 2 years ago