Automated Schema Migrations
Fivetran Automated Schema Migrations is 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 will now have a migration window, which is typically 90 days. During the window, you can control the timing of when such schema changes take effect.
An automated schema migration may include schema changes of the following types:
| 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. |
| Deprecated table | Deprecating a table. |
| Deprecated column | Deprecating 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. |
We highlight schema change types differently in the changelog table to help you identify potentially breaking schema changes:
Red - We highlight in red breaking schema changes, such as column and table renaming, primary key changes, and type changes.
Orange - We highlight in orange potentially breaking schema changes, such as column and table deprecations.
Green - We highlight in 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 migration approach | Phase 1 | Phase 2 |
|---|---|---|
| Addition (a new column or table has been added) | We add a new table or column and it starts receiving data. | n/a |
| Rename (a column or table has been renamed) | We add a table or column with the new name and it starts receiving data. | The column or table with the old name stops receiving data. |
| Deprecation (a column or table will no longer receive data) | n/a | A column or table stops receiving data. |
| Table rebuild (primary key changes or data type changes) | We create an updated table with an incoming suffix. It starts receiving data. | The new table replaces the existing one. Its incoming suffix is removed. The old table receives a backup suffix. |
NOTE: Re-syncs are always free if they are a part of a schema migration.
Phase 1
Phase 1 applies non-breaking schema changes as soon as the migration window opens. During phase 1, we do the following:
- Open a schema migration window. Typically, the migration window is 90 days, although it may be shorter in some cases (e.g., for connectors in Private Preview or Beta).
- Apply all Phase 1 schema changes (see the Schema Migration Phases table) as soon as the migration window opens. Phase 1 includes non-breaking changes and changes needed to prepare for the planned breaking schema changes.
- Send an email notification to you once the migration window opens.
- Display upgrade alerts and banner notifications in your Fivetran dashboard.
- Publish an in-depth release note with a table that explains how the schema is changing.
- Publish an updated ERD. The previous version of the ERD will still be available until the end of the migration window.
- Enable you to create a test connector to help you adjust your data queries.
IMPORTANT: Fivetran applies Phase 1 updates automatically at the beginning of the schema migration window and notifies you about the changes.
Phase 2
During Phase 2, we apply all potentially breaking changes. You can trigger Phase 2 manually, or we will automatically apply the changes once the migration window closes.
IMPORTANT: During the migration window, you can trigger Phase 2 updates manually in the Fivetran dashboard. Once triggered, the migration takes effect during the next sync.
Schema migration approaches
To implement the changes, schema migrations follow one or a combination of the following approaches:
| Schema migration approach | Description |
|---|---|
| Addition | Used to add new tables and columns. Additions do not introduce breaking schema changes. |
| Rename (2-phase) | Used for renaming tables and columns to ensure old data is preserved. Fivetran does not delete the old data in the destination. Once the new schema is applied, we stop adding data into the old table or column. The new table or column with the updated name starts receiving data. |
| Deprecation | Used when a column or table is to no longer receive updates. When the new schema is applied, we stop adding new data into the deprecated table or column. |
| Table rebuild (2-phase) | Used for changes to the primary key of a table and/or narrowing changes to the data type of a column. With this approach, you may see a temporary table in your destination or notice a table is removed for a brief time before it is rebuilt during a re-sync. |
Addition
The addition approach involves adding new tables and columns. We automatically apply these changes and no schema migration windows are required as additions do not affect your downstream queries.
NOTE: The changes we introduce with additions may be applied at the same time as changes that require a migration window. In such cases, the additions will apply as soon as the window opens.
Rename (2-phase)
Rename is a 2-phase schema migration approach for renaming tables and columns. Phase 1 is completed automatically when the migration window opens. During Phase 1, we add the column or table with the new name. During the window, we populate both the old and the new table or column. At the end of the migration window, we stop adding new data into the old table or column. The new tables and columns may not have complete data until the end of Phase 2 if a re-sync is needed.
See the Schema Migration Phases table for more details.
Deprecation
The deprecation approach is used when a column or table is to no longer receive updates. When the migration window opens, nothing changes in the schema. During the window, we continue to populate the to-be deprecated table or column. At the end of the window, when the new schema is applied, we stop adding new data into the table or column.
Table rebuild (2-phase)
Table rebuild is a 2-phase schema migration approach. We use it for changes to the primary key of a table and/or changes to the data type of a column. Phase 1 is completed automatically as soon as the schema migration window opens. During Phase 1, we create a completely new table similar to an existing one. At the end of the migration window, the new table replaces the old one. The new table may not have complete data until the end of Phase 2 if a re-sync is needed.
See the Schema Migration Phases table for more details.
Recommendations
If you receive a notification that a connector's schema is changing, there are several actions that you can take during a migration window to prepare for the new schema.
Review the associated changelog and ERD. Consider whether the breaking changes affect your downstream queries, reports, or models.
If necessary, update your workflows, scripts, and queries to prepare for the schema migration.
Set up a test connector to verify that your downstream queries work with the new schema.
Manually trigger the update in the Fivetran dashboard or prepare for an automatic update at the end of the migration window.