Group Discounts available for 3+ students and Corporate Clients

SCD(Slow changing Dimension) in Data Stage

SCD(Slow changing Dimension)

EX:- Suppose we have an customer Table, we have some fields which are frequently, ofliny,  slowly, Rarely, Rapidly changed




Different types of loadings


  1. Initial
  2.  incremental


  • Initial data (source) :-

C ID           CN      Add

11               A           HYD                      stored in Achieve Data base or OLTP or Source

22               B           SEC

33               C          DEL


The information is should from 2000 to 29th dec 2010

This data is stored in Target  7 Ware House  7 Before Data

  • Source (After data) :-

CID          CN                    Add   7value column

11              A                      HYD  7Copy

22              B                   GNT   7Edit

33               E                   VZD   7insert


  • Refresh the target data with source data based on type 1, type 2, type 3
  • Differences :-

Compare Before Data with After Data

Extract                                  Extract

Target                                    Source

Data                                        Data


  • If there are any changes in key column7Insert
  • If there are any changes in value column7 edit


Output :-




Table 1 :-

EX:  Before Data(Target)

CID           CN               Add

11             A                  HYD

22             B                 GNT

33             C                 DEL

32             D                 VZD


After Data: (Source)


CID       CN             Add

11           A          ASM 7 Edit

22          B            GNT 7 copy

44          G            PUNE 7 insert

32          E            Cal 7edit


  • Type 1 will not maintain historical data, maintains only current data .
Get through the interview bar with our selected interview questions for DataStage enthusiasts

Output :-

CID           CN              Add

11               A               ASM

22              B              GNT

33              C               DEL

44             G              PUNE

32              E               CAL


  • Before Data will not Maintain history


Type 1 implementation will always  happen in ODS




Dimensional  Tables7 CUST id,   C NAME, C ADD , Gender

Fact Tables 7 C id, BAL, AREA, Trane Type , Data 7 Maintained History


Type 2  :-  (Maintain History)


After Data :- (Source)                    Before  Data :- (Target)


CID   CN    ADD                                    CID   CN      ADD

11    A         HYD                                     11    A         HYD

22   B            GNT                                   22     B         SEC

44    E            PUN                                   33    C           DEL


Output :-



11    A         HYD

22     B        GNT

33      C       DEL


22           B               GNT

44             E                PUN


  • Here the key column is repeating, so we insert an new constraint
  • Surrogate key

For indepth understanding of DataStage click on

“At TekSlate, we are trying to create high quality tutorials and articles, if you think any information is incorrect or want to add anything to the article, please feel free to get in touch with us at, we will update the article in 24 hours.”

0 Responses on SCD(Slow changing Dimension) in Data Stage"

Leave a Message

Your email address will not be published. Required fields are marked *


Please Enter Your Details and Query.
Three + 6