Simple Replace or Append

1. Remove All Records from the Target Table

• Action: Delete all existing records from the target table.
• Optional: This step can be made conditional based on a setting or option defined by the designer of the mapping. For example, if a full refresh is not needed, records might not be removed.
• Reason: The goal is to ensure that the target table is empty and prepared to be populated with fresh data from the source.

2. Transform and Insert Source Records

• Action: Load the source records into the target table.
• If the source data is from a remote location, LKMs (Load Knowledge Modules) will have already transformed the data and placed it into staging tables.
• If the source data is local to the target (on the same server), the source datasets can be joined with the pre-transformed data in the staging area.
• Integration Operation: A simple INSERT/SELECT statement is used to load data from the source into the target table.
• The transformation occurs in the target system (e.g., Teradata), leveraging its transformation capabilities during the insert process.

This approach is effective when:

• Data consistency is critical (i.e., no old records should remain in the target table).
• Simpler transformations are required (handled either in the staging area or through database-level transformations during the insertion process).