Can Fivetran Preserve Custom Table Properties When Recreating a Destination Table?

Question

If a source schema change requires Fivetran to recreate a Databricks destination table, can Fivetran preserve custom table properties such as change data feed (CDF)?

Environment

Destination: Databricks

Answer

No, when a source schema change requires us to recreate the destination table using CREATE TABLE AS SELECT (CTAS), we create a new table object. Custom properties applied to the original table, including CDF, aren't automatically carried over. If you enable CDF on the new table, its history begins from that point. We can't preserve the original table's CDF history.

We use CTAS for schema changes that can't be applied through type widening. To reduce the likelihood of table recreation, consider the following options:

1. Block automatic schema changes in Fivetran.

   i. Go to the relevant connection page.

   ii. Select the Schema tab.

   iii. Click Schema change settings.

   iv. In the Schema new data handling drop-down menu, select Block all new data. This prevents new schemas, tables, and columns from being applied automatically. For more information, see Define Schema Change Handling settings.

   v. Click Save.

2. Use custom data type mapping, where supported, to reduce destination type changes that may require table recreation.

3. Use type locking to keep destination column types stable and prevent automatic type changes that may require table recreation.