Applying an audit history of all source table DML changes to target tables
You can configure database ingestion and replication incremental load and combined initial and incremental load tasks to write an audit trail of every DML change operation made on the source tables to the target. A row for each DML change on a source table is written to the generated target table along with the audit columns you select. The audit columns contain metadata about the change, such as the DML operation type, time, owner, transaction ID, generated ascending sequence number, and before image.
Note:
The Audit apply mode is available for tasks that have Amazon Redshift, Databricks, Google BigQuery, Oracle, PostgreSQL, Snowflake, or SQL Server targets.
When you define the task, select Audit in the Apply Mode field on the Target page. The Apply Mode field is available for new or undeployed tasks.
To specify the audit metadata columns to add, select one or more of the check boxes under Advanced on the Target page:
•Add Last Replicated Time. Adds a column that records the timestamp at which a record was inserted or last updated in the target table. For initial loads, all loaded records have the same timestamp, except for Snowflake targets that use the Superpipe option where the seconds or minutes might vary slightly. For incremental loads and combined initial and incremental loads, the column records the timestamp of the last DML operation that was applied to the target.
Note:
This option is available for Amazon Redshift, Databricks, Google BigQuery, Microsoft Azure Synapse Analytics, Oracle, Snowflake, and SQL Server targets only.
•Add Operation <metadata_type>. Adds columns that contain metadata for change operations, such as the DML operation type, time, owner, transaction ID, and generated ascending sequence number. The columns are populated when data is loaded to the target tables.
•Add Before Images. Adds _OLD columns that contain before-image data for Updates. You can compare the old and new column values in the tables.
•Prefix for Metadata Columns. Add a prefix to the names of the added audit columns to differentiate them from other table columns. Default is INFA_.
These fields are optional. Only the Add Operation Type check box is selected by default to add a column that shows the DML operation type: I (insert), D (delete), or U (update).
The first time you run a job associated with the task, the job generates the target tables with the selected audit metadata columns. Ensure that no constraints other than indexes exist on the target tables.
For targets that support Soft Deletes mode, for each delete operation processed as a soft delete, the operation type of "D" appears in the INFA_OPERATION_TYPE column and values are written to any other metadata columns that you selected. However, for update and insert operations, the INFA_OPERATION_TYPE column and all other selected metadata columns are NULL.
Example for Audit mode
For example, assume the following DML change operations occur on a source table in the order shown:
Insert into tableA pkey = 1 Update tableA where pkey=1 Update tableA where pkey=1 Delete from tableA where pkey = 1
All of the following rows appear in the target table, providing an audit trail of all of the source DML changes:
In this example, the only audit column selected is opType.
Note that when the task's apply mode is Standard, none of these rows appears on the target table because the last DML operation is a Delete, which supersedes the prior changes.
Note:
If a combined initial and incremental load job captures an incremental insert change record during the initial unload phase, the job manufactures a delete for the same row to remove any duplicate that might have been obtained from the initial unload. This manufactured activity will be reflected in audit apply mode.