Data Transformation: ETL Topics

• Surrogate Keys

• • Protect yourself from source system changes.
  • Needed since Dimensions of TYPE II (see appendix) will have a different natural key.
  • Example:
  • • Surrogate Id 1 / Customer Id : 100 / State: WA
    • Surrogate Id 2 / Customer Id : 100 / State: CA

• History

• • EFFECTIVE and EXPIRATION dates
  • Example:
  • • Surrogate Id 1 / Customer Id: 100 / Eff 01-Jan-2006 / Exp 31-Mar-2006
    • Surrogate Id 2 / Customer Id: 100 / Eff 31-Mar-2006 / NULL
    • Accurate reports the sale two years ago and last week.