Features

Capture deletes

Fivetran captures deletes whenever we can detect them so that you can run analyses on data that may no longer exist in your source system. Some sources provide us with direct information about deletes. When sources dont provide us with direct information about deletes, we try to infer them.

In the soft delete mode, when you delete data in the source, Fivetran soft-deletes it in the destination. In the destination table, we add an extra column named _fivetran_deleted and set its value to TRUE for the rows deleted in the source. The exact mechanism by which we capture deletes varies by connector type.

Databases

We can detect and capture deletes for most databases because we perform log-based replication (and logs contain deletes) for most databases. The major exception is PostgreSQL XMIN based replication, which is non-log-based. PostgreSQL WAL replication, however, does capture deletes.

Applications

Some application APIs provide dedicated endpoints that return deletes, and we capture deletes for those applications. However, most application APIs dont return deletes as changes, so we cannot detect and therefore dont capture those deletes. That means there may be data in your destination from those applications which has been deleted in the source.

Inferred deletes through re-syncs

If Fivetran re-syncs an entire table, we assume that any record that wasnt updated must have been deleted, and mark it _fivetran_deleted = TRUE in the destination. In the case of connectors where we identified that deletes would provide analytical value and re-syncing doesn't take long, we periodically re-sync the entire connection and use this data to infer deletes.

We re-sync in the evenings and on weekends to minimize performance impact. The drawback of this method is that all deletes from a particular period are rolled together -- we dont know exactly when data was deleted, only that it was deleted in the interval between updates. However, if a source doesnt provide us direct information about deletes, inferred deletes are the next best thing.

History mode

To retain table history in the destination for selected tables, Fivetran uses the type 2 slowly changing dimension format. You can use this historical data to analyze how your data changed over time.

We use the following approaches to retain historical data:

• For connectors where Fivetran defines the schema, we track history for a predefined set of tables. These tables are different for each connector, and we select them based on the analytical value of their historical data. For these connectors, you cannot choose which tables track history.

• For connectors where Fivetran just replicates the schema, you can select which tables track history. In these connectors, you can switch any supported table to history mode to track its history.

Custom data

As part of complete schema replication, Fivetran replicates custom data whenever it exists and is accessible. Not all sources that have custom data expose it in a way we can access.

Custom data includes custom objects, tables, and fields that you have configured in the source system to better suit your business needs. Custom objects are specific to your source, for example, custom Salesforce objects that match your business process. Custom tables are database tables that allow you to store information unique to your organization. Standard tables can also have custom fields, which are specific to an organization.

There is no special action you need to take to make sure we replicate your custom data. It will happen automatically for the systems that enable it.

Data blocking

Data blocking lets you omit certain tables or columns from replicating to your destination.

Data blocking avoids exposing sensitive information, like Personally Identifiable Information (PII), in your destination. It also saves you time and storage space, because Fivetran only syncs relevant data to your destination.

Read our detailed documentation about data blocking.

Column hashing

Column hashing anonymizes sensitive data in your destination without sacrificing its analytical value. Column hashing is only available for some of our connectors. If this feature is available for your connector, you will see the option in your Schema tab. For connectors that support the feature, you can select which column(s) within a table to hash.

Column hashing avoids exposing sensitive information, like PII, in your destination. Because hashing anonymizes your PII, this feature makes you compliant with the General Data Protection Regulation (GDPR).

Read our detailed documentation about column hashing.

Re-sync

Sometimes the data in your destination and your source get out of sync and you need to overwrite all or some of the existing data in your destination to make it consistent with the source. Depending on the connector, you can re-sync all or part of your data from your source to your destination.

Full re-sync

A full re-sync completely overwrites the data in your destination for the currently selected tables with new data from your source and soft-deletes the old data. In the Fivetran dashboard, you can re-sync your connection by clicking Resync all historical data under Setup Sync Settings.

A full re-sync affects every table currently selected in the connections schema. To re-sync only specific tables, use the per-table Re-sync buttons on the Schema tab.

Typical uses:

• Repairing schema drift or data mismatches in the destination
• Recovering from an unrecoverable incremental cursor gap
• Repopulating the destination after widening the Historical Sync Time Frame

Considerations:

• Depending on source limits and data volume, a full re-sync may take several hours or even days to complete.
• Incremental updates are paused while the re-sync runs.
• The operation does not alter physical destination tables table schemas remain intact.
• The connections configuration, schedule, and operational history are not affected.

Table re-sync

Table re-sync lets you overwrite the data in a specific table, so you can fix data integrity issues in selected tables without re-syncing the entire connection. Just like a full re-sync, re-syncing a table pauses incremental updates until the sync completes. However, table re-sync is much faster since single tables tend to have far less data.

API configurable

The Fivetran REST API provides several endpoints that let you perform key management actions which were previously available only through the dashboard.

• The User Management API lets you list, invite, edit, and delete your users.
• The Group Management API lets you understand the groups and connections within those groups.
• The Connection Management API lets you create, edit, and manage a subset of your connections.

Read our detailed documentation about the Fivetran REST API.

Priority-first sync

Priority-first syncs fetch your most recent data first so that it's quickly ready for you to use.

During your initial sync, Fivetran updates your destination with your most recent data. How many days of recent data we fetch and which tables we sync on a priority-first basis vary by connector type.

In each subsequent sync, we continue to update your most recent data first as part of a forward sync. We then fetch your historical data in small increments as part of a backward sync. The backward sync duration varies by connector type.

The forward and backward syncs are two different steps. After we complete the forward sync, we reschedule the connection sync with a notification on the Fivetran dashboard. The backward sync occurs immediately after we complete the forward sync. This allows us to push the data fetched during the forward sync into the destination, before initiating the backward sync.

Priority-first syncs are shown in the Sync History chart on the Connection Status page of your Fivetran dashboard.

Priority-first sync is supported for the following connectors:

Syncing empty tables and columns

Fivetran can create empty tables and columns in your destination for the following connectors:

• BigCommerce
• CockroachDB
• Connector SDK
• Google Ads
• Microsoft Dynamics 365 CRM
• Microsoft Dynamics 365 Finance and Operations
• MySQL
• NetSuite SuiteAnalytics
• Oracle
• PostgreSQL
• QuickBooks
• Salesforce
• Salesloft
• SAP ERP on HANA
• ServiceNow
• SQL Server
• Stripe
• The Trade Desk
• Zoho CRM
• Zuora

Syncing empty tables and columns ensures that any templated SQL queries in your destination that reference these objects continue to work. An empty table has no rows, and an empty column has no data.

Syncing empty columns in file connectors

Fivetran creates empty columns and columns with null values in your destination. Fivetran initially creates these columns with a STRING data type. However, in subsequent syncs, if Fivetran detects non-null values in these columns, it updates the data type to match the inferred type and recreates the columns in your destination.

Supported connectors

Fivetran supports syncing empty columns for the following file connectors when using BigQuery, Databricks, and Snowflake as destinations:

• Amazon S3
• Azure Blob Storage
• Box
• Dropbox
• Email
• FTP
• Google Cloud Storage
• S3-Compatible Storage
• SFTP
• SharePoint

Fivetran creates a destination table only when the file contains at least one row with data available for sync.

For more information, see our Files documentation.

Private networking

Private networking allows Fivetran to sync data from your source, write data to your destination, or both without exposing traffic to the public internet.

Fivetran supports the following private networking services:

• AWS PrivateLink
• Azure Private Link
• Google Cloud Private Service Connect

Regional Failover

The Regional Failover feature enables you to ensure the availability of your data, connections, and services in the event of a disaster or outage in the cloud region hosting the Fivetran services. Use this feature to failover your connections and services to a secondary region when the primary region experiences a failure or becomes unavailable.

Read our detailed documentation about regional failover.

Fivetran data models

Fivetran offers pre-built, data build tool (dbt) Core -compatible data models for our most popular connectors. Our data models standardize your data and generate tables that you can link to your BI and visualization tools.

You must have a dbt project to use our models. Each data model contains instructions on how to add the model to your dbt project. You do not need to enable Transformations for dbt Core to use our models, but we recommend it.

To see our available data models and supported destinations, read our data model documentation.

Row filtering

Row filtering enables you to sync only rows that meet specific criteria. To enable row filtering for a table, you select the relevant table and column and specify the filter value and comparison operator. You can use one filter per table.

We apply row filtering to initial syncs, re-syncs, and incremental syncs. In incremental syncs, the filters are applied starting from the next sync after the filters are created.

To learn more, read our detailed row filtering documentation.

Type locking

Type locking lets you lock the data types of your connection's columns in the destination. Once type locking is enabled for a connection, during every sync, Fivetran determines if the data type of incoming data differs from the locked type. Fivetran then converts values of the changed types to the locked types, preventing unexpected type changes that could disrupt your data workflows.

See our Type Locking documentation to learn more.

Custom data type mapping Private Preview

Custom data type mapping lets you control which data type a column uses in the destination. By default, Fivetran automatically maps source data types to destination types. With custom data type mapping, you can override this default on a per-column basis and specify a target data type for individual columns.

See our Custom Data Type Mapping documentation to learn more.

External Secret Managers Private Preview

The External Secret Managers feature allows you to securely store and manage your connection and destination credentials in your own external secret management service rather than in Fivetran.

To learn more, read our detailed External Secret Managers documentation.

Schema migration

Schema migration is the process of managing changes to a connectors schema to maintain data flow and integrity in downstream systems. Schema migration helps accommodate source data changes or rectify issues while preserving the existing data in the destination.

We consider the following schema changes in a schema migration:

For more information, see how we handle schema migrations in your destination.

Unstructured file replication Beta

Fivetran enables you to sync unstructured files, regardless of the file format, to the supported destinations with integrated object storage capabilities. You can manage and store both unstructured and structured data from a unified storage location.

To learn more, read our detailed unstructured file replication documentation.