Post reply

DW - Data Extraction RI issue

  • July 2, 2019 at 11:19 am

    #3659222

    We are taking approach for Extracting data from source to target but have your advice.

    Our approach is:

    1) Drop the RI Constraints

    2) Truncate the Tables

    3) ReCreate the  RI Constraints

    4) Extract data from source to target

    Now confusion is during this process before or during recreating RI constraints if we loose DB connection or something outage happen then we loose to ability to Recreate

    RI Constraints.What your thoughts and advice?

  • July 2, 2019 at 2:59 pm

    #3659346

    Why use the constraints at the destination? Integrity is enforced when the data is created, you only need to duplicate the data once it has been created. Since you are dropping the constraints to make the inserts I assume that the data is either not extremely dynamic or absolute data integrity isn't important within the scale of the intervals between the job runs.

    If your data architecture was designed properly, the only issue you can have is old versions of existing records (which should be good enough for most reporting or analytics) and not have references that are just entirely wrong.

    I'd be extremely leery of dropping and recreating the constraints regularly. If you need to maintain the constraints, I would write the job so it never violates key constraints, copying primary keys first, foreign keys last.

  • July 3, 2019 at 3:12 am

    #3659615

    Thanks Rookie.

    This is our newly build Data warehouse and we are trying to load data from source DBs to destination DB-Staging DW.

    Once it's loaded then we will be loading into STAR Schema tables or some of Data Mart type tables (with some data cleansing) so we can use for reporting purpose. We having confusion as once we drop the RI constraints so we can easily truncate the tables but during recreating constraint if DB having any connectivity issue then the job will fail or get interrupted and we can't restore back All the constraints as we already dropped the constraints. Now some of the constraints get created and we try to load the data then we will be facing RI issue due to constraint is not there.

    Please advise if I am missing something or my approach is wrong?

    Thanks in advance!

     

     

     

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

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