Communities
A collaborative platform to connect and grow with like-minded Informaticans across the globe
Product Communities
Connect and collaborate with Informatica experts and champions
Discussions
Have a question? Start a Discussion and get immediate answers you are looking for
User Groups
Customer-organized groups that meet online and in-person. Join today to network, share ideas, and get tips on how to get the most out of Informatica
Get Started
Community Guidelines
Knowledge Center
Troubleshooting documents, product guides, how to videos, best practices, and more
Knowledge Base
One-stop self-service portal for solutions, FAQs, Whitepapers, How Tos, Videos, and more
Support TV
Video channel for step-by-step instructions to use our products, best practices, troubleshooting tips, and much more
Documentation
Information library of the latest product documents
Velocity (Best Practices)
Best practices and use cases from the Implementation team
Learn
Rich resources to help you leverage full capabilities of our products
Trainings
Role-based training programs for the best ROI
Certifications
Get certified on Informatica products. Free, Foundation, or Professional
Product Learning Paths
Free and unlimited modules based on your expertise level and journey
Resources
Library of content to help you leverage the best of Informatica products
Tech Tuesdays Webinars
Most popular webinars on product architecture, best practices, and more
Product Availability Matrix
Product Availability Matrix statements of Informatica products
SupportFlash
Monthly support newsletter
Support Documents
Informatica Support Guide and Statements, Quick Start Guides, and Cloud Product Description Schedule
Product Lifecycle
End of Life statements of Informatica products
Ideas
Events
Change Request Tracking
Marketplace
English
  • Data Ingestion and Replication
  • All Products

Table of Contents

Search

  1. Preface
  2. Database Ingestion and Replication

Database Ingestion and Replication

Database Ingestion and Replication

Back Next

Schema drift handling

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
Schedule and Runtime Options
 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 Configuring schedule and runtime options. 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 OneLake, Oracle, Oracle Cloud Object Storage, PostgreSQL, 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 OneLake, Oracle, Oracle Cloud Object Storage, 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 OneLake, Oracle, Oracle Cloud Object Storage, PostgreSQL, 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 OneLake, Oracle, Oracle Cloud Object Storage, PostgreSQL, Snowflake, and SQL Server
PostgreSQL
Incremental
Combined initial and incremental
Incremental loads: Amazon Redshift, Amazon S3, Databricks, Google BigQuery, Google Cloud Storage, Kafka, Microsoft Azure Data Lake Storage, Microsoft Azure Synapse Analytics, Microsoft Fabric OneLake, Oracle, Oracle Cloud Object Storage, PostgreSQL, and Snowflake
Combined initial and incremental loads: Oracle, PostgreSQL, and Snowflake
  • 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.
  • If you set the
    Add Column
     option to
    Replicate
     for tasks that have an Oracle or SQL Server source,
    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.
  • 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 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.
  • If you set the
    Add Column
     option to
    Replicate
     and then add a column with a default value to a Db2 for i 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.
Back to Top
1 COMMENTS
Back Next
We’d like to hear from you!
Alessio Giordani - January 23, 2025

Hello documentation team,

can you please update this page including MS Fabric Onelake as supported target for the schema drift as described here?

https://docs.informatica.com/integration-cloud/data-ingestion-and-replication/current-version/database-ingestion-and-replication/database-ingestion-and-replication/configuring-a-database-ingestion-and-replication-task/configuring-schedule-and-runtime-options.html#GUID-C9E517A1-F63A-45BE-800B-BBD773288197

Thanks and regards,

Alessio

Informatica Documentation Team - January 23, 2025

Microsoft Fabric OneLake has been added as a target to this topic in the Feb 2025 doc.