Backup the Target Table Before Loading

1. Define Backup Table Naming Convention

• The backup tables should be named similarly to the original data tables but with a "_BCK" suffix.
• Example: target_table becomes target_table_BCK.

2. Identify the Data Warehousing Process

• Understand the current data loading process, specifically the Incremental Update IKM used for loading the target datastore.
• The backup should happen before any data is modified in the target table.

3. Create a Single Task for Backup Operation

• Instead of creating separate mappings for each target table, utilize a single SQL-based approach.
• Implement a CREATE AS SELECT statement that creates and populates the backup table with the existing data before modifying the target table.

4. Modify the Incremental Update IKM

• Locate the Incremental Update IKM or any other IKM used for populating the data warehouse.
• Add tasks before the existing tasks that modify the target table (e.g., Before the Update Existing Rows and Insert New Rows tasks).

5. Add Backup Task in IKM

• Implement a SQL task in the IKM to perform the backup action before the data is modified.
• The SQL might look like this:

o   CREATE TABLE target_table_BCK AS SELECT * FROM target_table;

• This will create a new backup table target_table_BCK that mirrors the contents of the original target_table.

6. Ensure Backup is Performed Automatically

• Ensure that the backup task runs automatically each time the data warehouse loads data, without requiring manual intervention.
• The backup task should be added directly in the IKM’s workflow, so it runs as part of the data load process.

7. Test the Backup Process

• Verify that the backup is correctly created before any changes are made to the target table.
• Check that the data in the backup table is identical to the source data in the target table at the time of backup.

8. Handle Rollback or Restoration

• Ensure that in case of a failure during the data loading, the data in the backup table (target_table_BCK) can be restored to the original target table.
• Example SQL to restore:

o   INSERT INTO target_table SELECT * FROM target_table_BCK;

9. Monitor and Maintain Backup Process

• Monitor the backup table to ensure it is being generated properly and that the space for backups is managed.
• If there are frequent schema changes, ensure that the backup process remains aligned with these changes.

By following these steps, the data warehouse will automatically back up the target table before the data load process begins, reducing the need for manual intervention and ensuring that recovery is easier in case of an issue.