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.
Connector | Priority Fetch Period | Tables synced on a priority-first basis | Backward Sync Duration |
---|---|---|---|
Acculynx | 30 days | JOB and its child tables | 6 hours |
Adjust | 30 days | All tables | 6 hours |
Adobe Analytics | 15 days | All reporting tables | 6 hours |
AdRoll | 30 days | All reporting tables | 6 hours |
Afterpay | 90 days | PAYMENT table and its child tables | 6 hours |
Amazon Ads | 30 days | All reporting tables | 6 hours |
Amazon Selling Partner | 7 days | FINANCE and ORDERS modules | 6 hours |
Amplitude | 15 days | All tables | 6 hours |
Apple Search Ads | 30 days | All reporting tables | 6 hours |
Assembled | 15 days | AGENT_STATE , DAILY_REPORT_METRIC , EVENT_CHANGE , FORECAST , FORECAST_TOTAL , HOURLY_REPORT_METRIC , and REQUIREMENT tables | 6 hours |
BigCommerce | 2 days | CHANNEL , CUSTOMER , ORDERS , PRODUCT , PRICE_LIST , and SUBSCRIBER tables | 6 hours |
Braintree | 2 days | CREDIT_CARD_VERIFICATION table | 6 hours |
Checkout.com | 7 days | BALANCE_REPORT , CUSTOMER , DISPUTE , FINANCIAL_ACTION_REPORT , INSTRUMENT , PAYMENT , PAYMENT_ACTION , PAYOUT_REPORT , and REPORT tables and their child tables | 6 hours |
ChurnZero | 15 days | CHURN_SCORE_CALCULATION and CHURN_SCORE_FACTOR_CALCULATION tables | 6 hours |
Clazar | 15 days | BUYER , CONTRACT , LISTING ,OPPORTUNITY , and PRIVATE_OFFER tables | 6 hours |
Clearpay | 90 days | PAYMENT table and its child tables | 6 hours |
Clockodo | 15 days | WORKTIME table | 6 hours |
Commercetools | 30 days | CART , CUSTOMER , INVENTORY , ORDERS , PAYMENT , and PRODUCT tables and their child tables. | 6 hours |
Constant Contact | 7 days | CONTACT and EMAIL_CAMPAIGN tables and their child tables. | 6 hours |
Criteo | 30 days | All reporting tables | 6 hours |
Datadog | 1 day | AUDIT , CI_VISIBILITY_PIPELINE , CI_VISIBILITY_TEST , EVENT , LOG , RUM_EVENT , and SPAN tables | 6 hours |
Deposco | 30 day | BILLABLE_TRANSACTION , BILLING_INVOICE , PURCHASE_ORDER , SALES_ORDER , and SHIPMENT tables | 6 hours |
Dialpad | 15 days | CALL table | 6 hours |
Donus | 1 day | GRANTS , GRANTS_REPORT , MEMBER , and PAYMENT tables and their child tables | 5 hours |
Duoplane | 7 days | PURCHASE_ORDER , PRODUCT , and SHIPMENT tables | 6 hours |
Eloqua | 7 days | CONTACT_ACTIVITY and custom object tables | 6 hours |
Facebook Pages | 30 days | All page insights tables | 6 hours |
Fourkites | 7 days | SHIPMENT table and its child tables | 6 hours |
Front | 30 days | CONTACT and CONVERSATION tables and their child tables | 6 hours |
Gainsight Product Experience | 7 days | CUSTOM_EVENT , EMAIL_EVENT , ENGAGEMENT_VIEW_EVENT , EVENT_IDENTIFICATION , FEATURE_MATCH_EVENT , FORM_SUBMIT_EVENT , LEAD_EVENT , PAGEVIEW_EVENT , SEGMENT_MATCH_EVENT , SESSION_EVENT , SURVEY_RESPONSE , and USERS tables | 6 hours |
Google Analytics 360 | 15 days | All tables | 6 hours |
Google Analytics 4 | 30 days | Reporting tables | 6 hours |
Google Analytics 4 Export | 1 day | All tables | 6 hours |
Google Display & Video 360 | 30 days | All tables | 6 hours |
Google Search Ads 360 | 15 days | All tables | 6 hours |
Google Search Console | 7 days | Reporting tables | 6 hours |
Healthie | 30 days | APPOINTMENT and FORM_ANSWER_GROUP tables | 6 hours |
Help Scout | 15 days | CUSTOMER_HISTORY and CONVERSATION_HISTORY tables and their child tables | 6 hours |
Hilti On!Track | 60 days | TRANSFER table | 6 hours |
HubSpot | 1 day | CUSTOM_EVENT , EMAIL_EVENT_BOUNCE , EMAIL_EVENT_CLICK , EMAIL_EVENT_DEFERRED , EMAIL_EVENT_DELIVERED , EMAIL_EVENT_DROPPED , EMAIL_EVENT_FORWARD , EMAIL_EVENT_OPEN , EMAIL_EVENT_PRINT , EMAIL_EVENT_SENT , EMAIL_EVENT_SPAM_REPORT , EMAIL_EVENT_STATUS_CHANGE , EMAIL_EVENT_SUPPRESSED , EVENT , FEEDBACK_SUBMISSION , FEEDBACK_SUBMISSION_CONTACT , FEEDBACK_SUBMISSION_PROPERTY_HISTORY , FEEDBACK_SUBMISSION_TICKET , LEAD , LEAD_COMPANY , LEAD_CONTACT and LEAD_PROPERTY_HISTORY tables | 6 hours |
Instagram Business | 29 days | USER_INSIGHTS table | 6 hours |
Iterable | 7 days | EVENT table | 6 hours |
Jibble | 15 days | ACTIVITY ,GROUPS ,SCHEDULE ,TIME_ENTRY , and TIMESHEET tables | 6 hours |
JobNimbus | 30 days | All tables | 6 hours |
Klaviyo | 7 days | EVENT tables | 6 hours |
Liftoff | 30 days | REPORT tables | 6 hours |
LinkedIn Ad Analytics | 30 days | All reporting tables | 6 hours |
Mailjet | 15 days | CONTACT_LIST_SIGNUP , MESSAGE , MESSAGE_INFORMATION ,GEO_STATISTICS ,TOPLINK_CLICKED and CLICK_STAT tables | 6 hours |
Marketo | 0 days | All activity (and dependent) tables and LEAD table | 6 hours |
Maxio Chargify | 7 days | EVENT , INVOICE , PRODUCTS_PRICE_POINT , PRODUCT , SUBSCRIPTION , and SUBSCRIPTION_COMPONENT tables | 6 hours |
Microsoft Advertising | 30 days | All reporting tables | 6 hours |
Mixpanel | 15 days | EVENT table | 6 hours |
Nice | 30 days | AGENT_PERFORMANCE , CONTACT , DISPOSITION , INTERACTION_HISTORY , SKILL_CALL_DATA , SKILL_SUMMARY , SLA_SUMMARY , STATE_HISTORY , WFM_DATA_ADHERENCE , WFM_DATA_CONTACT , WFM_DATA_PERFORMANCE , and WFM_DATA_SCORECARD tables. | 6 hours |
Ometria | 7 days | PROFILE , ORDERS , and UNSUBSCRIBED_CONTACT tables | 6 hours |
On24 | 30 days | ATTENDEE , EVENT , LEAD , PRESENTER , and REGISTRANT tables | 6 hours |
Optimizely | 30 days | DECISION and CONVERSION tables and their child tables | 6 hours |
Ordway | 7 days | INVOICE , JOURNAL , PAYMENTS , REVENUE_SCHEDULE , and USAGE tables and their child tables | 6 hours |
Outbrain | 30 days | All reporting tables | 6 hours |
PagerDuty | 15 days | LOG_ENTRIES tables | 5 hours |
Packiyo | 15 days | ORDERS table | 6 hours |
Pardot | 14 days | EMAIL , VISIT , and VISITOR_ACTIVITY tables | 6 hours |
Pendo | 7 days | ACCOUNT_HISTORY , VISITOR_HISTORY , FEATURE_EVENT , PAGE_EVENT , GUIDE_EVENT , TRACK_TYPE_EVENT , POLL_EVENT , and EVENT tables | 6 hours |
Pinterest Ads | 30 days | All reporting tables | 6 hours |
Quorum | 7 days | BILL table | 6 hours |
Recharge | 15 days | ADDRESS , ADDRESS_DISCOUNT , ADDRESS_SHIPPING_LINE , CHARGE , CHARGE_DISCOUNT , CHARGE_LINE_ITEM , CHARGE_ORDER_ATTRIBUTE , CHARGE_SHIPPING_LINE , CHARGE_TAX_LINE , CHECKOUT , CHECKOUT_LINE_ITEM , CUSTOMER , DISCOUNT , METAFIELD , ONE_TIME_PRODUCT , ORDER , ORDER_LINE_ITEM , PAYMENT_METHOD , PLAN , SUBSCRIPTION , SUBSCRIPTION_HISTORY , and UTM_TAG tables | 6 hours |
Reddit Ads | 30 days | All reporting tables | 6 hours |
Reltio | 7 days | ENTITY , INTERACTION , MATCH , and RELATION tables | 6 hours |
Ricochet360 | 30 days | LEAD table and its child tables | 6 hours |
Rithum | 15 days | ORDERS and PRODUCT tables | 6 hours |
Salesforce Commerce Cloud | 15 days | CAMPAIGN , COUPON_REDEMPTION , CUSTOMER , ORDER , and PRODUCT tables | 6 hours |
Salesforce Marketing Cloud | 1 day | CHAT_INBOUND_MESSAGE_LOG , CHAT_POTENTIAL_UNSUBS , CHAT_TRACKING , EMAIL , EVENT , LIST , LIST_SUBSCRIBER , MOBILE_PUSH_DETAIL_EXTRACT , PUSH_MESSAGE , SEND , SMS_MESSAGE_TRACKING , SUBSCRIBER , and TRIGGERED_SEND tables | 6 hours |
Shopify | 15 days | ABANDONED_CHECKOUT , COLLECTION , CUSTOMER , DRAFT_ORDER , ORDER , PRICE_RULE , PRODUCT , and TENDER_TRANSACTION tables | 6 hours |
Snapchat Ads | 30 days | All reporting tables | 6 hours |
StarRez | 15 days | All tables except BOOKING_TYPE , CUSTOM_FIELD_USAGE_TYPE_ENUM , ENROLLMENT_TYPE_ENUM , ENTRY_STATUS_ENUM , FIELD_DATA_TYPE_ENUM , GENDER_ENUM , GL_POSTING_TYPE_ENUM , GROUP_STATUS_ENUM , GROUP_STATUS_ENUM , NATIONALITY , ROOM_RATE_DURATION_ENUM , ROOM_SPACE_TYPE_ENUM , ROOMSPACE_CLOSED_REASON , and TRANSACTION_TYPE_ENUM tables | 6 hours |
sticky.io | 15 days | CONTACT and ORDERS tables | 6 hours |
Stripe | 30 days | All tables and their child tables except APPLE_PAY_DOMAIN , CHECKOUT_SESSION , CREDIT_NOTE , EARLY_FRAUD_WARNING , PLAN , and SKU tables | 6 hours |
Taboola | 30 days | All reporting tables | 6 hours |
The Movie Database | 12 days | MOVIE, TV_SERIES tables and their child tables | 6 hours |
The Trade Desk | 30 days | All reporting tables | 6 hours |
TikTok Ads | 30 days | All reporting tables | 6 hours |
Twitter Organic | 30 days | ORGANIC_TWEET_REPORT table | 6 hours |
UKG Pro | 30 days | COMPENSATION , DIRECT_DEPOSIT , EMPLOYEE_CHANGE , EMPLOYEE_CONTRACT , EMPLOYEE_GLOBAL_BANK , EMPLOYEE_GLOBAL_LOCALIZATION_ELEMENT , EMPLOYEE_JOB_HISTORY , EMPLOYEE_PAY_DEDUCTION_ELEMENT , EMPLOYEE , and EMPLOYMENT tables | 6 hours |
Xactly | 30 days | All tables except COMMISSION_SUMMARY and QUOTA_SUMMARY tables. | 6 hours |
Yahoo DSP | 30 days | All reporting tables | 6 hours |
Zonka Feedback | 15 days | RESPONSE table and its child tables | 6 hours |
Zendesk | 15 days | TICKET table and its child tables | 6 hours |
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:
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.