Question regarding staging tables and fact tables for a Data Warehouse in a sql server 2012 database

  • So I have a staging customer table that holds combined customer information for several different databases. All our customer information across different platforms is combined and standardized in this staging table. Based on this data - we normalize it and then either update the customer information in our Fact table if that customer already exists - or we insert a new customer record if that customer doesn't exist.

    Our current Fact table has 10+ million records in it. Our staging customer table has millions in it as well. I know these numbers aren't exactly big - but I am also planning for the future as well. So - my question is are there any 3rd party tools on the market that can help with the update and the inserts? Or is my only option scripting it all out in either a stored procedure or an SSIS package. I looked at Red gate's SQL compare - but it looks like it works better if you run it ad hoc. I am looking for something to create and then schedule it so it runs automatically.

    Any suggestions is greatly appreciated.

  • I would prefer a separate staging table for each of the different versions of customer.

    Then I would look ate combined table with the conformed customer dimension.

    I would use merge to get the final table.

    Not sure if the above is ok for your volumes. You would really need to add more detail.

    E

    😎

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

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