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