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 don’t 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 don’t return deletes as changes, so we cannot detect and therefore don’t 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 wasn’t updated must have been deleted, and mark it _fivetran_deleted = TRUE in the destination. In the case of connectors where re-syncing doesn't take long, and we have identified deletes would provide analytical value, we periodically re-sync the entire connector 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 don’t know exactly when data was deleted, only that it was deleted in the interval between updates. However, if a source doesn’t 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 with new data from your source. While it’s a powerful option for fixing a destination out of sync with its source, a full re-sync also pauses incremental updates. A full re-sync can take hours to days for some connectors, either because of the amount of data or because of limits imposed by the source. You can initiate a full re-sync from your connector details page.

Fivetran can re-sync data from any source, but we have disabled the option to run a full re-sync for Marketo because re-syncing it takes an extremely long time. If you want to run a full re-sync for Marketo, contact our support team.

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 connector. 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 connectors within those groups.
• The Connector Management API lets you create, edit, and manage a subset of your connectors.

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 connector 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 Connector Status page of your Fivetran dashboard.

Priority-first sync is supported for the following connectors:

IMPORTANT: Connectors that support priority-first sync activate the connector's free trial period as soon as a priority-first sync is initiated.

Syncing empty tables and columns

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

• BigCommerce
• CockroachDB Private Preview
• Connector SDK Beta
• 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.

NOTE: An empty schema is a schema that doesn't contain any tables. A schema that contains empty tables is not an empty schema. Fivetran doesn't sync empty schemas.

Private networking

IMPORTANT: You must have a Business Critical plan to use 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 Beta

Regional Failover Private Preview

IMPORTANT:

• You must have a Business Critical plan to use the Regional Failover feature.
• The Hybrid Deployment model does not support this feature.

The Regional Failover feature enables you to ensure the availability of your data, connectors, and services in the event of a disaster or outage in the cloud region hosting the Fivetran services. Use this feature to failover your connectors 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 for initial sync and re-syncs. For incremental syncs, we do not apply row filtering.

Fivetran supports row filtering for the following connectors:

• Amazon Aurora MySQL
• Amazon Aurora PostgreSQL
• Amazon Aurora Serverless V2
• Amazon RDS for MariaDB
• Amazon RDS for MySQL
• Amazon RDS for PostgreSQL
• Amazon RDS for SQL Server
• Azure Database for MariaDB
• Azure Database for MySQL
• Azure Database for PostgreSQL
• Azure SQL Database
• Azure SQL Managed Instance
• Generic MariaDB
• Generic MySQL
• Generic PostgreSQL
• Generic SQL Server
• Google Cloud SQL for MySQL
• Google Cloud SQl for PostgreSQL
• Google Cloud SQL for SQL Server
• Heroku PostgreSQL

To learn more, read our detailed row filtering documentation.

* dbt Core is a trademark of dbt Labs, Inc. All rights therein are reserved to dbt Labs, Inc. Fivetran Transformations is not a product or service of or endorsed by dbt Labs, Inc.