Fivetran Uses Teleport Sync Instead of Time Travel Sync for Dynamic Tables

Issue

CHANGE_TRACKING is enabled on Snowflake dynamic tables, but Fivetran uses the Fivetran Teleport Sync incremental sync method instead of Time Travel Sync.

Environment

Connector: Snowflake

Resolution

To resolve this issue, ensure the affected dynamic tables use the INCREMENTAL refresh mode, then re-sync them:

1. In Snowflake, run the following query to check the table's refresh mode:

   SHOW DYNAMIC TABLES LIKE '<dynamic_table_name>';
   

   Check the refresh_mode and refresh_mode_reason columns. If refresh_mode is FULL, refresh_mode_reason explains why Snowflake can't use incremental refresh.
2. Update the table definition to remove unsupported, non-deterministic functions, such as CURRENT_TIMESTAMP and SYSDATE. This allows Snowflake to use incremental refresh.
3. Ensure CHANGE_TRACKING = TRUE is enabled on the underlying base tables.
4. In Fivetran, go to your Snowflake connection page.
5. Select the Schema tab.
6. Find the affected tables, then click Re-sync.
7. In the confirmation pop-up window, click Start re-sync.

Once the dynamic table uses the INCREMENTAL refresh mode, Fivetran automatically transitions from Fivetran Teleport Sync to Time Travel Sync.

For more information, see the Snowflake documentation on supported non-deterministic functions.

Cause

This issue occurs when dynamic tables contain non-deterministic functions, such as CURRENT_TIMESTAMP or SYSDATE, that prevent Snowflake from using incremental refresh. Snowflake therefore uses FULL refresh mode, which causes every row to appear changed after each refresh.

When this happens, we can't retrieve incremental change data, and we fall back to Fivetran Teleport Sync even when CHANGE_TRACKING is enabled.