Schema drift handling

Database Ingestion and Replication can be configured to automatically detect some source schema changes and handle these changes on the target. This process is referred to as schema drift.

Database Ingestion and Replication can detect the following types of source schema changes:

•Add column

•Modify column

•Drop column

•Rename column

When you define a task, on the Let's Go page of the database ingestion and replication task wizard, you can configure how the supported types of schema changes are handled. For example, you can configure schema drift options to ignore the changes, replicate them, or stop the job or subtask when a schema change occurs. For more information, see Finalize the task definition. Note that dIfferent types of schema changes might have different default settings, depending on the target type.

Schema drift options are supported for the following source - target combinations and load types:

Source	Load Type	Target
Db2 for i	Incremental Combined initial and incremental	Amazon Redshift, Amazon S3, Databricks, Google BigQuery, Google Cloud Storage, Kafka (incremental loads only), Microsoft Azure Data Lake Storage, Microsoft Azure Synapse Analytics, Microsoft Fabric Data Warehouse, Microsoft Fabric Lakehouse, Microsoft Fabric OneLake, Open Table, Oracle, Oracle Cloud Object Storage, PostgreSQL, Salesforce Data 360, Snowflake, and SQL Server
Db2 for LUW	Incremental Combined initial and incremental	Snowflake
Db2 for z/OS, except Db2 11	Incremental Combined initial and incremental	Amazon Redshift, Amazon S3, Databricks, Google BigQuery, Google Cloud Storage, Kafka (incremental loads only), Microsoft Azure Data Lake Storage, Microsoft Azure Synapse Analytics, Microsoft Fabric Data Warehouse, Microsoft Fabric Lakehouse, Microsoft Fabric OneLake, Open Table, Oracle, Oracle Cloud Object Storage, Salesforce Data 360, Snowflake, and SQL Server
Microsoft SQL Server	Incremental Combined initial and incremental	Amazon Redshift, Amazon S3, Databricks, Google BigQuery, Google Cloud Storage, Kafka (incremental loads only), Microsoft Azure Data Lake Storage, Microsoft Azure Synapse Analytics, Microsoft Fabric Data Warehouse, Microsoft Fabric Lakehouse, Microsoft Fabric OneLake, Open Table, Oracle, Oracle Cloud Object Storage, PostgreSQL, Salesforce Data 360, Snowflake, and SQL Server
Oracle	Incremental Combined initial and incremental	Amazon Redshift, Amazon S3, Databricks, Google BigQuery, Google Cloud Storage, Kafka (incremental loads only), Microsoft Azure Data Lake Storage, Microsoft Azure Synapse Analytics, Microsoft Fabric Data Warehouse, Microsoft Fabric Lakehouse, Microsoft Fabric OneLake, Open Table, Oracle, Oracle Cloud Object Storage, PostgreSQL, Salesforce Data 360, Snowflake, and SQL Server
PostgreSQL	Incremental Combined initial and incremental	Incremental loads: Amazon Redshift, Amazon S3, Databricks, Google BigQuery, Google Cloud Storage, Kafka (incremental loads only), Microsoft Azure Data Lake Storage, Microsoft Azure Synapse Analytics, Microsoft Fabric Data Warehouse, Microsoft Fabric Lakehouse, Microsoft Fabric OneLake, Open Table, Oracle, Oracle Cloud Object Storage, PostgreSQL, Salesforce Data 360, and Snowflake Combined initial and incremental loads: Microsoft Fabric Data Warehouse, Microsoft Fabric Lakehouse, Open Table, Oracle, PostgreSQL, Salesforce Data 360, and Snowflake

Note:

•If you try to replicate a type of schema change that is not supported on the target, the database ingestion and replication job ends with an error.

•Database Ingestion and Replication does not replicate source changes that add, remove, or modify primary key or unique key constraints. If these types of changes occur on the source, you must resynchronize the target tables.

•If you configured schema drift options to stop the job when Database Ingestion and Replication detects a schema change, you can use the Resume With Options command to resume the job with an override schema drift option.

•Database Ingestion and Replication detects a schema change in a source table only after DML operations occur on the altered source table. If multiple schema changes occur without intervening DML operations, Database Ingestion and Replication detects all of the schema changes at one time, when a DML operation occurs. To ensure that Database Ingestion and Replication detects all of the supported schema changes correctly, Informatica recommends that you apply schema changes to source tables one by one, each followed by at least one DML change.

•For source types that allow selecting individual columns in the task configuration, column selection rules take precedence over schema drift. If your column selection rules exclude a column from replication, the job does not add, modify, drop, or rename that column in the target, and does not apply the schema change to it. Schema drift also does not modify your column selection rules. For example, if your column selection rules include only specific columns and you add a new column, the job does not replicate the new column even if you enable the Add Column schema drift option. The added column does not change your rules, so it stays excluded.

•For tasks that have Oracle or SQL Server sources, if you set the Add Column option to Replicate, Database Ingestion and Replication assumes that any new column is added at the end of the table definition. If a new column appears in the middle of a table definition, Database Ingestion and Replication might treat the change as if the column has been dropped and added again. In this situation, the following alert might report that the same column has been both dropped and added:

Column <column_name2> has been added. Column <column_name2> has been dropped.

•For tasks that have Db2 for i sources, if you set the Add Column option to Replicate and then add a column with a default value to a source table, Database Ingestion and Replication adds the default value to the newly added table rows to the target. However, existing rows on the target are not updated to reflect the default values. To get the default value populated to the existing target rows, perform another initial load to re-materialize the target.

•For tasks that have Db2 for z/OS sources, if you set the Add Column option to Replicate and an Add Column operation is captured, you should run the Db2 REORG TABLESPACE utility or Db2 LOAD utility with a RELOAD on the source table to make sure the Db2 page set's row data contains the new column values or null indicators.

•Database ingestion and replication tasks that have Microsoft Azure Synapse Analytics targets cannot replicate rename operations on source columns. The Replicate option is not available.

•Database ingestion and replication tasks that have Microsoft Fabric Data Warehouse, Open Table, and Salesforce Data 360 targets cannot replicate drop and rename operations on source columns. The Replicate option is not available for these operations.

•Database ingestion and replication tasks that have Microsoft Fabric Lakehouse targets cannot replicate drop and rename operations on source columns. The Replicate option is not available for these operations. These tasks also cannot replicate modify operations on source columns.

• Database ingestion and replication tasks that have Snowflake targets support modify operations on source columns with the following limitations:

- Snowflake targets cannot modify the scale of NUMBER columns.
- Snowflake targets do not support changing the data type of an existing column to a different data type.

•Database ingestion and replication tasks that have Google BigQuery targets cannot replicate rename or modify operations on source columns. The schema drift options for these operations are not available.

•In jobs that load data with the Apache Iceberg Open Table format to Amazon S3 targets, the schema drift options for add, modify, drop, and rename columns are set to Replicate by default. To prevent the task from failing, ensure to set the schema drift options to Ignore so that schema changes are bypassed during processing.