Incremental Update

The Incremental Update strategy is used to integrate data into the target table by comparing records in the flow with existing records in the target, based on an update key. Records with the same update key that have different values are updated, while records that don't exist in the target are inserted.

Steps Involved in the Incremental Update Strategy:

1. Drop and Create the Integration Table:
• Drop the integration table ("I$") if it already exists.
• Create a new integration table in the staging area with the same attributes as the target table. This table will also include an IND_UPDATE attribute, which is used to flag records as "I" (insert), "U" (update), or "N" (no change).
2. Transform and Insert Data:
• Data is loaded from source and loading tables into the integration table using a single INSERT/SELECT statement.
• By default, the IND_UPDATE flag is set to "I" for all records.
3. Recycle Rejected Records (Optional):
• If the RECYCLE_ERROR KM option is enabled, previously rejected records are recycled and added to the integration table before further processing.
4. Call the CKM for Flow Control:
• The Check Knowledge Module (CKM) evaluates the data in the integration table against the constraints of the target table.
• Invalid records are moved to an error table ("E$") and removed from the integration table.
5. Set IND_UPDATE to "U" for Matching Records:
• Update the integration table to set the IND_UPDATE flag to "U" for records with the same update key values as the records in the target table but with differing data.
• This is typically done with an UPDATE/SELECT statement.
6. Set IND_UPDATE to "N" for Identical Records:
• Update the integration table to set the IND_UPDATE flag to "N" for records that are already flagged "U" but have exactly the same attribute values as the target records.
• These records do not require any update to the target table.
7. Finalize Flagging in the Integration Table:
• After these updates, the integration table will have records flagged as follows:
• "I": These records should be inserted into the target.
• "U": These records should be used to update the target.
• "N": These records already exist in the target and do not need to be updated or inserted.
8. Update the Target Table with "U" Records:
• Execute an UPDATE statement to update the target table with records from the integration table that are flagged "U".
• It is generally more efficient to execute the update before the insert to minimize the volume of data manipulated.
9. Insert Records with "I" Flag into the Target:
• Execute an INSERT statement to insert records from the integration table that are flagged "I" into the target table.
10. Commit the Transaction:
• Commit the transaction to ensure that all changes (updates and inserts) are applied to the target table.
11. Drop the Temporary Integration Table:
• Drop the temporary integration table ("I$") after all operations are complete.

 

Optimization Techniques

1. Teradata Optimization:
• For Teradata, a left outer join between the flow data and the target table may be used to populate the integration table with the IND_UPDATE attribute already set, improving performance.
2. Oracle Optimization:
• For Oracle, using a MERGE INTO statement might be more efficient than performing separate UPDATE and INSERT operations.

 

Key Concepts in Incremental Update Strategy

1. Update Key:
• The update key is used to identify records in both the source and target. Typically, the primary key is used, but if the primary key is automatically generated (e.g., identity or sequence), the update key should be based on other attributes from the source data.
2. Comparing Nulls:
• When comparing values to decide whether a record needs updating, special handling is required for null values. For accurate comparison, you can use a COALESCE function to treat null values as equal:

o   COALESCE(<target_table>.<AttributeN>, 0) = COALESCE(<loading_table>.<AttributeN>, 0)

3. Attribute-Level Insert/Update Behavior:
• UPDATE statement filters for attributes marked as "Update" in the mapping, excluding the update key.
• INSERT statement filters for attributes marked as "Insert" in the mapping.
4. Transaction Integrity:
• It is crucial that both UPDATE and INSERT operations are executed in the same transaction. If any operation fails, none of the changes should be applied to the target table to maintain data integrity.

 

Best Practices

• Optimize SQL Queries: Depending on the database system (e.g., Oracle or Teradata), optimize queries to ensure the UPDATE and INSERT operations are as efficient as possible.
• Unique Update Key: Always ensure that the update key is unique. If the primary key can't be used, choose other attributes that uniquely identify records.
• Null Value Handling: Ensure that null values are handled correctly in comparisons, as SQL engines can treat nulls differently.