Schema Migration

Schema migration is the process of managing changes to a connector’s schema to maintain data flow and integrity in downstream systems. Fivetran's schema migration process helps accommodate source data changes or rectify issues while preserving the existing data in the destination. Schema migration facilitates the modification of a connector’s destination schemas.

We have designed the schema migration process to be as seamless as possible, minimizing the need for user intervention. We consider the following schema changes in a schema migration:

New table or column
Rename table or column
Drop (Discontinue) table or column
Data type changes of columns
Primary key and foreign key changes

Automated schema migrations

Automated schema migrations are a simple, consistent way for you to plan, prepare for, and control schema changes that may impact your downstream data queries. Schema changes to application connectors that can introduce breaking changes have an update window. During the update window, you can control the timing of when such schema changes take effect.

IMPORTANT: We support automated schema migrations for a subset of our application connectors and are gradually rolling out support for most of them, where applicable.

Schema change types

We consider the following types of schema changes in a schema migration:

Schema change type	Description
New table	Adding a new table.
New column	Adding a new column in a table.
Rename table	Renaming a table.
Rename column	Renaming a column.
Discontinued table	Discontinuing a table.
Discontinued column	Discontinuing a column.
Type change	Changing a column’s data type.
Primary key change	Changing the primary key columns of a table.
Foreign key relationship change	Changing the foreign key relationships of a table.

Schema change types help you identify potentially breaking schema changes:

Red - Breaking schema changes, such as column and table renaming, primary key changes, and data type changes.
Yellow - Potentially breaking schema changes, such as column and table discontinuations.
Green - Non-breaking schema changes, such as the addition of new columns and tables.

Schema migration phases

Whenever we update an application connector's schema with potentially breaking changes, we do it in two phases. Schema migrations use one or a combination of the following approaches:

Schema migration approach	Description	Phase 1	Phase 2	Notes
Addition	Used to add new tables and columns. Additions do not introduce breaking or potentially breaking schema changes.	We add a new table or column and it starts receiving data.	n/a	We automatically apply these changes and no schema update windows are required as additions may not affect your downstream queries. The changes we introduce with additions may be applied at the same time as changes that require an update window. In such cases, the additions will apply as soon as the window opens.
Rename (2-phase)	Used to rename tables and columns. Renames introduce breaking schema changes. Fivetran does not delete the old data in the destination. When the new schema is applied, we stop adding data to the old table or column.	We rename a table or column.	The table or column with the new name starts receiving data.	The column or table with the old name stops receiving data.
Discontinuation	Used when a column or table will no longer receive updates. Discontinuations introduce potentially breaking schema changes. Fivetran does not delete the data in the destination. When the new schema is applied, we stop adding new data to the discontinued table or column.	n/a	The column or table stops receiving data.	When the update window opens, nothing changes in the schema. During the window, we continue to populate the to-be-discontinued column or table. At the end of the window, when the new schema is applied, we stop adding new data to the column or table.
Table rebuild (2-phase) (primary key changes or data type changes)	Used for changes to the primary key of a table or narrowing changes to the data type of a column. Table rebuilds introduce breaking schema changes. When the new schema is applied, you may observe a temporary table or a table removal before it is rebuilt.	We create an updated table with an incoming suffix. The table starts receiving data.	The new table replaces the existing one. We remove the incoming suffix. The old table receives a backup suffix.	Phase 1 is completed automatically as soon as the schema update window opens. During Phase 1, we create a completely new table similar to an existing one. At the end of the update window, the new table replaces the old one. The new table may not have complete data until the end of Phase 2. For more information, read our Phase 1 and Phase 2 documentation.

Phase 1

Fivetran applies Phase 1 updates automatically at the beginning of the schema update window and notifies you about the changes. During Phase 1, we do the following:

Open a schema update window. Typically, the update window is 90 days, although it may be shorter in some cases (for example, for connectors in Private Preview or Beta).
Apply all Phase 1 schema changes as soon as the update window opens. Phase 1 includes non-breaking changes and changes needed to prepare for the planned breaking schema changes.
Send an email notification to users once the update window opens.
Display upgrade alerts and banner notifications in your Fivetran dashboard.
Publish an in-depth release note that explains how the schema is changing.
Publish an updated ERD.

Phase 2

During Phase 2, Fivetran applies all the potentially breaking changes. During the update window, you can trigger Phase 2 manually in the Fivetran dashboard, or we will automatically apply the changes once the update window closes. The migration takes effect during the next sync.

Table rebuild (2-phase) illustration

The following image illustrates our table rebuild approach:

table rebuild approach

Schema migration recommendations

If you receive a notification that your connector schema is changing, you must do the following before Fivetran applies the new schema:

Review the associated release note and ERD. Consider whether the breaking changes affect your downstream queries, reports, or models.
If needed, update your workflows, scripts, and queries to prepare for the schema migration.
(Optional) Set up a test connection to verify that your downstream queries work with the new schema.
Trigger the update in the Fivetran dashboard or prepare for an automatic update at the end of the update window.

FAQ

Do schema migrations contribute to increased connector usage?
Re-syncs are always free if they are a part of a schema migration.