Search code examples
etldata-warehousedimensional-modelingsnowflake-schema

valid_from/valid_to against active_status in dimension table

In order to populate a SCD2 dimension table, a marker to note the lastest active row is always beneficial.

There are two ways I can think of 1) valid_from/valid_to 2) active_status: active/deleted

It is clear that valid_from/valid_to keeps more information, but would that complicate the ETL process a lot?

what are the prons and crons of these two methods?

Solution

  • There are mainly two way to implement SCD2

    1 Keep versioning . 2 Keep start date and end date for a dimension.

    In most of times we use second approach with having a active inactive flag.

    https://en.wikipedia.org/wiki/Slowly_changing_dimension