OEID Incremental Updates

Published November 27 2012 by Patrick Rafferty
Back to insights

A fairly common approach...

More often than not, when pulling data from a database into OEID, we need to employ incremental updates.   To introduce incremental updates, we need a way to identify which records have been added, updated or deleted since our last load.  This change identification is commonly referred to as change data capture, or CDC.  There is no one way to accomplish CDC and often the best approach is dictated by the mechanisms in place in the source system.  Usually the database we're pulling from isn't leveraging any explicit change data capture (CDC) mechanism.

Note: If you're pulling from text files and new records are being appended, you can look at the incremental reading feature of the UniversalDataReader component (pg. 268). http://docs.oracle.com/cd/E29805_01/integrator.230/DataIntegratorDesigner.pdf.

If you're pulling from a database, and don't have explicit database CDC features enabled, best practices usually dictate you create an "audit" or "execution_history" table to track previous full and incremental loads. This "audit" table simply records the date and time a load started and the date and time it ended, if it ended successfully. You would need to INSERT into this table before calling your incremental load graph in Integrator. Thus, when reading your table (or, better yet, denormalized view), you could issue your SQL SELECT statement with a few other WHERE conditions that leverage a "last_update_date" column in your view like so....

WHERE view_last_update_date >= (SELECT MAX(run_start_date) FROM audit_table WHERE run_status = 'Complete')
AND view_last_update_date < (SELECT MAX(run_start_date) FROM audit_table)


Once this incremental load graph completes, you'd need to update your audit table row with the end datetime of the run and the status="Complete" flag.

Contact us