Expiring rows in a SCD

View previous topic View next topic Go down

Expiring rows in a SCD

Post  gbritton on Tue Dec 17, 2013 1:42 pm

Toy example for a real problem:

In the ETL i'm writing, I have a source table "foo". It is eventually loaded to dim.foo in the DW. "foo" has just two columns: "bar" and "fum". "bar" is my source business key.

Now, one of the changes that can happen in the source system is that a row in "foo" can be deleted, indicating that the data in column "fum" no longer applies to the key in column "bar". To detect this condition, I run a query that joins the new source data and my current dim table to find the now-absent rows. (There is no transaction data available to me to tell me that this has happened.) I will then mark these rows in my dim table as no longer active or relevant.

Question: Is this a reasonable approach? Is there a better way??

gbritton

Posts : 6
Join date : 2013-11-18

View user profile

Back to top Go down

Re: Expiring rows in a SCD

Post  ngalemmo on Tue Dec 17, 2013 1:53 pm

In a lot of cases there isn't a requirement to do this, but if you need to, what you describe is a reasonable way to do it. You never want to physically delete rows from a dimension.
avatar
ngalemmo

Posts : 3000
Join date : 2009-05-15
Location : Los Angeles

View user profile http://aginity.com

Back to top Go down

View previous topic View next topic Back to top

- Similar topics

 
Permissions in this forum:
You cannot reply to topics in this forum