Dylan's BI Study Notes

My notes about Business Intelligence, Data Warehousing, OLAP, and Master Data Management

Use Last Update Date for Change Capture

It is a very common practice to use the last update date from the OLTP system to capture the changes if your OLTP system provides such information.

You can use the follow SQL to capture changes happened since your last extract:

SELECT  col1, col2, col3
FROM    oltp_tbl
WHERE   oltp_tbl.last_update_date > :last_extract_date;

A good data warehousing solution does not just cover data warehouse design. A good OLTP database design can actually determine your data warehouse performance.

Best Practice: Add the last update date to your OLTP table. Always update the last update when any data in the table is inserted or updated.

Best Practice: Add an index to the last update date column of your OLTP table. This is especially important for those tables which only a few records are inserted or updated between the ETL runs.