Enabling Data Drift Handling

The Azure Synapse SQL destination can automatically compensate for changes in column or table requirements, also known as data drift.

The destination can handle data drift in the following ways:

Create new columns

The destination can create new columns in existing tables when new fields appear in records. For example, if a record suddenly includes a new Address2 field, the destination creates a new Address2 column in the target table.

By default, the destination creates new columns based on the data in the new fields, such as creating an Int column for integer data. You can, however, configure the destination to create all new columns as Varchar.

To enable the automatic creation of new columns, select the Enable Data Drift property on the Table Definition tab. To create all new columns as Varchar, select the Create New Columns as Varchar property.

Create new tables

When data drift handling is enabled, you can also configure the destination to create new tables as needed. For example, say the destination writes data to tables based on the region name in the Region field. When a new SW-3 region shows up in a record, the destination creates a new SW-3 table and writes the record to the new table.

When creating a table, the destination can also create partitions based on the specified column and values. When partitioning a table, you specify the partition column, the values to use as boundaries for the partitions, and whether the specified values are the upper boundary or lower boundary of the partition.

To enable the creation of new tables, first enable data drift on the Table Definition tab, and then select the Auto Create Table property. To partition the table, select the Partition Table property.