Home Forums SQL Server 2005 Business Intelligence Best Practice for loading DW tables from live source with timestamp/Rowversion column to identify changes RE: Best Practice for loading DW tables from live source with timestamp/Rowversion column to identify changes

  • Essentially you're looking for the best way to find records that have changed in your source system?

    If your source system is a SQL Server 2012 enterprise you could look into implementing change data capture, that is designed for what your trying to do. I'm guessing that's unlikely, so you're going forced to find the changed records yourself, there is no other way round it.

    You could checksum the records or use OR's like you did. To implement the checksum you would have to store the primary key and checksum value for the record in your staging area then compare that list to your extract from source.

    I would also be tempted to consider a full reload of your DW during your ETL which would make the problem go away. That depends on how much transformation you need to do really, you're having to do a full extract from source anyway so it might be an option. You can load all of your data into a second version of the fact table then at the end just rename the table or switch the data in using partition switching.

    Hope this helps.