need to load inserts before updates before deletes into target...data is all in one source

Question

So, we're currently trying to achieve the following:

SourceTableA is a change data capture table, it will contain for a natural key, I, U, D, I then U, I then D, I then U then D, or U then D. Primary key for this table is the natural key + action (I, U, or D).

TargetTableA is a type-2 scd table with surrogate key generated by a sequence generator.

The main problem we're having is processing an update on a record which hasn't been inserted yet (surrogate key only exists in mapping and not in table), but came in the same pipeline.

We HAVE to process all records from SourceTableA in batch.

We can't use 3 different source qualifiers as I, U, D pipelines because of complex lookup logic.

We can't use dynamic cache to maintain a store of the generated surrogate keys because we can't control how Oracle will process the ins/upd/deletes. It was actually working, until we found out it was trying to update before inserting the referenced record.

I'm at wits end here.

Ex scenario of what should happen:

Insert record comes in, key is generated for this record, say 100. It is inserted with active_flag = 'Y' and end_date is 'open'. Next, an update record comes in for the same natural key, a key is generated, 101, and the record with the new data is inserted with active_flag = 'Y'. Previously 'inserted' row 100 is deactivated to active_flag = 'N' and end_date = (update_row).end_date - 1 second.

Thanks!

Answer 1

Dynamic lookup on target will fix this issue when source reads two records of same natural key since dynamic lookup will update the cache and then the target

Answer 2

In Update Strategy set property to DD_Update for both Insert and also Update. IN session level,mapping properties check only update else insert.

Hope it works!!