Post reply

SCD Insert/Updates In Staging And Live Database??

  • May 20, 2017 at 3:59 pm

    #321610

    Hi All

    So we are a 24/7 operation so for my new/first DW I have a STAGING DB for the data/ETL and a LIVE database for the Fact and Dim tables - happy days!
    I have an SCD which is normalised so pretty much the same as the original tables (lets CategoryID and a CategoryName ) and at the moment I am thinking
    that if the Categories are changed/created in the source DB I need to check for these changes and update my Staging data and then check changes in Staging
    and update my Live SCD with the changes..

    I'm happy do that if that is standard practice or I am also happy to bypass Staging DB and just update my Live SCDs from the source DB?

    Any advice greatly appreciated!

    Thanks
    Mattie

  • May 21, 2017 at 6:57 am

    #1943474

    MattieG72 - Saturday, May 20, 2017 3:59 PM

    Hi All

    So we are a 24/7 operation so for my new/first DW I have a STAGING DB for the data/ETL and a LIVE database for the Fact and Dim tables - happy days!
    I have an SCD which is normalised so pretty much the same as the original tables (lets CategoryID and a CategoryName ) and at the moment I am thinking
    that if the Categories are changed/created in the source DB I need to check for these changes and update my Staging data and then check changes in Staging
    and update my Live SCD with the changes..

    I'm happy do that if that is standard practice or I am also happy to bypass Staging DB and just update my Live SCDs from the source DB?

    Any advice greatly appreciated!

    Thanks
    Mattie

    For simplicity and consistency, I'd recommend keeping exactly the same process for all source DB changes.
    If your current process is source to staging to DW, do it that way.

    The absence of evidence is not evidence of absence
    - Martin Rees
    The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
    - Phil Parkin

  • May 21, 2017 at 8:07 am

    #1943479

    Phil Parkin - Sunday, May 21, 2017 6:57 AM

    For simplicity and consistency, I'd recommend keeping exactly the same process for all source DB changes.
    If your current process is source to staging to DW, do it that way.

    I agree with Phil 100%.  If you have a standard process for loading and it's implemented in many places, to have a one-off throws people who will have to work on it later.  Also, since this is your first assignment on it, you don't want to ignore the standards and go off in a different direction.  I'm not saying that writing a new process is necessarily a bad thing, but wait until you know the standard, understand the nuances of it and understand any problems before you fix it.

    Tally Tables - Performance Personified
    String Splitting with True Performance
    Best practices on how to ask questions

  • May 21, 2017 at 1:52 pm

    #1943484

    Thanks all, sound advice all round 🙂

    Mattie

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply