Using slowly changing dimension to load staging table

• rs80

  SSCrazy

  Points: 2312

  More actions

  February 8, 2013 at 8:57 am

  #288958

  Can a slowly changing dimension be used to insert/update records into a staging table? I haven't used that transformation before and from what I read it was more used to load dimension tables. Thanks.

• PaulB-TheOneAndOnly

  SSC Guru

  Points: 66772

  More actions

  February 8, 2013 at 9:31 am

  #1586035

  rs80 (2/8/2013)

  ---

  Can a slowly changing dimension be used to insert/update records into a staging table? I haven't used that transformation before and from what I read it was more used to load dimension tables.

  You are correct, a Slowly Changing Dimension is a particular kind of DIMension table where depending on the SCD type history of each particular dimention is kept (or not).

  A Staging table, on the other hand, is by definition a table that gets truncated and re-populated on each ETL cycle therefore SCD does not applies to them.

  Hope this helps.

  _____________________________________
  Pablo (Paul) Berzukov

  Author of Understanding Database Administration available at Amazon and other bookstores.

  Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

• rs80

  SSCrazy

  Points: 2312

  More actions

  February 8, 2013 at 10:17 am

  #1586064

  Thanks Paul

• Phil Parkin

  SSC Guru

  Points: 247172

  More actions

  February 8, 2013 at 10:32 am

  #1586071

  PaulB-TheOneAndOnly (2/8/2013)

  ---

  rs80 (2/8/2013)

  ---

  Can a slowly changing dimension be used to insert/update records into a staging table? I haven't used that transformation before and from what I read it was more used to load dimension tables.

  You are correct, a Slowly Changing Dimension is a particular kind of DIMension table where depending on the SCD type history of each particular dimention is kept (or not).

  A Staging table, on the other hand, is by definition a table that gets truncated and re-populated on each ETL cycle therefore SCD does not applies to them.

  Hope this helps.

  I do not entirely agree with this definition.

  I see no reason why staging tables cannot be updated, rather than truncated/repopulated, as data makes its way elsewhere - whatever is most efficient in the circumstances.

  ---