Mass Ingestion

Back Next

Snowflake Cloud Data Warehouse target properties

When you define a

database ingestion

task, you must enter some properties for your Snowflake Cloud Data Warehouse target on the

Target

page of the task wizard. The properties vary slightly by load type.

The following table describes the Snowflake target properties that appear under

Target

Property	Description
Apply Mode	For incremental load and combined initial and incremental load jobs with Snowflake targets, indicates how source DML changes, including inserts, updates, and deletes, are applied to the target. Options are: Standard . Accumulate the changes in a single apply cycle and intelligently merge them into fewer SQL statements before applying them to the target. For example, if an update followed by a delete occurs on the source row, no row is applied to the target. If multiple updates occur on the same column or field, only the last update is applied to the target. If multiple updates occur on different columns or fields, the updates are merged into a single update record before being applied to the target. Soft Delete . Apply source delete operations to the target as soft deletes. A soft delete marks the deleted row as deleted without actually removing it from the database. For example, a delete on the source results in a change record on the target with "D" displayed in the INFA_OPERATION_TYPE column. If an update followed by a delete occurs on the source, two records are written to the target both with "D" displayed in the INFA_OPERATION_TYPE column. Consider using soft deletes if you have a long-running business process that needs the soft-deleted data to finish processing, to restore data after an accidental delete operation, or to track deleted values for audit purposes. Audit . For Snowflake targets only, ingest change data into an audit table on the target system by using insert operations, instead of merging and applying the changes to the target database. Consider using audit tables if you want to perform computations or other downstream processing on the data before applying it to the target database or if you want to examine the changes. You can add metadata columns for SQL change operations to the audit table by setting options under the Advanced section. Default is Standard.
Target Creation	The only available option is Create Target Tables , which generates the target tables based on the source tables. After the target table is created, Mass Ingestion Databases intelligently handles the target tables on subsequent job runs. Mass Ingestion Databases might truncate or re-create the target tables depending on the specific circumstances.
Schema	Select the target schema in which Mass Ingestion Databases creates the target tables.
Stage	The name of internal staging area that holds the data read from the source before the data is written to the target tables. This name must not include spaces. If the staging area does not exist, it will be automatically created.

Under

Advanced

, you can enter the following advanced target properties if you set

Apply Mode

Soft Deletes

Audit

to add metadata columns for each delete operation or each DML change recorded in the audit table:

Field	Description
Add Operation Type	Select this check box to add a metadata column that records the source SQL operation type in the output that the job propagates to the target database or inserts into the audit table on the target system. For incremental loads and combined initial and incremental loads, the job writes "I" for insert, "U" for update, or "D" for delete. For initial loads, the job always writes "I" for insert. By default, this check box is selected. You cannot deselect it if you are using soft deletes.
Add Operation Time	Select this check box to add a metadata column that records the source SQL operation timestamp in the output that the job propagates to the target database or inserts into the audit table on the target system. By default, this check box is not selected.
Add Operation Owner	Select this check box to add a metadata column that records the owner of the source SQL operation in the output that the job propagates to the target database or inserts into the audit table on the target system. By default, this check box is not selected. This property is not available for jobs that have a PostgreSQL source.
Add Operation Transaction Id	Select this check box to add a metadata column that records the transaction ID of the source transaction with the SQL operation that the job propagates to the target database. By default, this check box is not selected.
Add Operation Sequence	Select this check box to add a metadata column that records a generated, ascending sequence number for each change operation that the job inserts into the audit table on the target system. The sequence number reflects the change stream position of the operation. By default, this check box is not selected.
Add Before Images	Select this check box to add _OLD columns with UNDO "before image" data in the output that the job inserts into the audit table on the target system. You can then compare the old and current values for each data column. For a delete operation, the current value will be null. This field is available only when Apply Mode is set to Audit . By default, this check box is not selected.
Add Columns Prefix	Select this check box to add a prefix to the names of the added metadata columns to easily identify them and to prevent conflicts with the names of existing columns. The default value is INFA_.

Rename Saved Search

Table of Contents

Mass Ingestion

Mass Ingestion

Snowflake Cloud Data Warehouse target properties

Snowflake Cloud Data Warehouse target properties