Incremental Update FAQS

1. What is the Incremental Update strategy?
• The Incremental Update strategy is used to update existing records in the target table based on a comparison with incoming records. If the data in the source differs from the target (based on the update key), the record is updated. If the record does not exist in the target, it is inserted.
2. How does the update key work in the Incremental Update strategy?
• The update key is used to identify records in both the source and target tables. Typically, the primary key is used as the update key, but if the primary key is automatically generated (e.g., an identity column), a different attribute must be used as the update key.
3. What is the purpose of the integration table (I$) in this strategy?
• The integration table ("I$") is used to stage the data before it is inserted or updated in the target table. It holds transformed data and includes an IND_UPDATE attribute to flag records as "I" (insert), "U" (update), or "N" (no action).
4. How are records flagged for insert or update?
• Records in the integration table are flagged based on the comparison of the update key and attribute values between the source and target.
• "I": Records to be inserted.
• "U": Records to be updated.
• "N": Records that do not need to be updated or inserted.
5. What happens if the RECYCLE_ERROR KM option is enabled?
• If the RECYCLE_ERROR option is enabled, rejected records from previous executions are recycled and added to the integration table before proceeding with flow control and updates. This ensures that previously failed records can be reprocessed.
6. How are errors handled in the Incremental Update strategy?
• Errors are handled using the Check Knowledge Module (CKM), which evaluates constraints on the integration table data. Invalid records are moved to an error table ("E$"), and only valid records are used for insert or update operations.
7. How are null values handled when comparing records?
• To ensure null values are treated as equal, a COALESCE function is used during comparisons. This ensures that a null in the source matches a null in the target, as SQL engines generally do not consider nulls equal.
8. What is the difference between the UPDATE and INSERT operations?
• The UPDATE statement applies changes to existing records flagged as "U", while the INSERT statement adds new records flagged as "I" into the target table.
9. Can the Incremental Update strategy be used for dimension tables?
• Yes, the Incremental Update strategy is commonly used for dimension tables, especially when there is no need to track the history of changes and only the most current data needs to be maintained.
10. What are some optimizations for the Incremental Update strategy?
• For databases like Teradata, a left outer join can be used between the flow data and the target table to populate the integration table efficiently.
• For Oracle, using a MERGE INTO statement may be more efficient than performing separate UPDATE and INSERT operations.
11. How are update and insert operations handled in the same transaction?
• Both UPDATE and INSERT operations should be executed within the same transaction. This ensures atomicity — either both operations succeed, or neither does, preserving data consistency and integrity.
12. Can the strategy be optimized based on the database system?
• Yes, depending on the underlying database, optimizations can be made. For instance, Oracle may benefit from using MERGE INTO, while Teradata may benefit from a left outer join for the flow data comparison.
13. What should I use as an update key if the primary key cannot be used?
• If the primary key cannot be used (e.g., when it’s generated automatically), use a combination of source attributes that can uniquely identify the record. This could be a composite key or a combination of business keys that uniquely identify the record.