Salesforce

Salesforce is a cloud-based CRM platform.

We pull all the underlying data from Salesforce (SFDC) into a SQL-enabled environment to allow you to get a more in-depth analysis of your CRM data.

Features

Supported products

Supported environments

Our two Salesforce connectors, Salesforce and Salesforce Sandbox, support the following environments:

Supported deployment models

We support the SaaS and Hybrid deployment models for the connector.

NOTE: You must have an Enterprise or Business Critical plan to use the Hybrid Deployment model.

Setup guide

Follow our step-by-step Salesforce setup guide to connect Salesforce with your destination using Fivetran connectors.

Sync overview

Formula fields

Salesforce objects often include automatically populated fields called formula fields. Their values are derived from formulas created by your Salesforce administrators. Due to Salesforce limitations, these fields cannot be synced incrementally with reliable integrity. To avoid data integrity issues, Fivetran automatically detects formula fields and excludes them from the tables synced to your destination.

However, if you want to sync your formula fields, Fivetran recommends that you use a transformation-based approach to sync them. For more information, see our Formula Fields documentation.

Global variables

We support the fields that reference the following Global Variables:

• $Api
• $CustomMetadata
• $Label
• $Organization
• $Permission
• $Profile
• $RecordType
• $Setup
• $System.OriginDateTime
• $User
• $UserRole

The following global variables rely on specific tables. You must select the relevant tables on the Schema tab of your connection details page.

The following are user-dependent global variables. We generate their translations dynamically based on the connecting user's details.

• $Label
• $Permission
• $Profile
• $Setup
• $User
• $UserRole

Salesforce Files Private Preview

You can sync files managed in Salesforce Files, which are stored in the ContentVersion. Here are some details about how we sync Salesforce Files:

• We sync only the latest version of each file from the ContentVersion object.
• We prefix each file name with the Id from the ContentVersion record, using the following format: Id__FileName.
• We store files of the SNOTE type as TEXT files with a .txt extension.
• To include metadata and linked entities, we also sync the ContentDocument and ContentDocumentLink objects along with Salesforce Files.

Data storage location

Files

• Files are stored in a named internal stage in the destination schema, with the stage name corresponding to the ContentVersion.
• The connection updates and maintains a Snowflake directory table after each sync.

Metadata

The metadata for each file is maintained in the ContentVersion table. The _fivetran_file_path column stores the relative source path and reference to the respective file’s location in the destination. You can use the following commands to join with the Snowflake directory table:

    SELECT 
        metadata.id,
        metadata.title AS file_name,
        metadata.version_data_url AS source_url,
        metadata.system_modstamp,
        stage_dir.relative_path,
        stage_dir.file_url AS stage_url
    FROM <database>.<schema_name>.CONTENT_VERSION metadata
    LEFT JOIN DIRECTORY(@<database>.<schema_name>.CONTENT_VERSION) stage_dir
    ON metadata._fivetran_file_path = stage_dir.relative_path

Salesforce Files schema

NOTE:

• This feature is only available for connections with Snowflake as the destination.
• File syncing is not supported for connections using Hybrid Deployment.

API usage

The number of API calls that we use varies from company to company. Our Salesforce connector is optimized to use the fewest API calls possible. In practice, customers find that Fivetran is not a significant API consumer.

REST and Bulk API usage

We use both the Salesforce REST API and the Bulk API to sync your Salesforce data. We choose between the Bulk and REST APIs depending on the volume of data we need to sync for a table.

The Bulk API is more useful for high-volume data, so for historical syncs or high-volume incremental syncs, we use the Bulk API. We use the REST API for all other syncs and as a fallback if the Bulk API fails with some error or exceeds a certain time threshold.

Bulk API quota

Each Salesforce account has a rolling 24-hour Bulk API quota. If 90% of the bulk jobs quota has been consumed, to continue the sync we fall back to the REST API.

REST API quota

Each Salesforce account has a rolling 24-hour REST API quota. We automatically postpone by one hour whenever 90% of the Salesforce API call quota has been consumed. This ensures there is always API quota available for other business-critical applications.

Monitoring the API usage

The system table FIVETRAN_API_CALL tracks information about API calls made to sync data into the destination.

Delete capture limitation

History objects

We don't capture deletes of the field tracking history objects because we don't have dedicated endpoints to fetch deleted records for each object. To capture deletes for a specific history object, we can't use the getDeleted() because the call returns all the records deleted during the specified date range for all the history objects.

OpportunityLineItem object

We use the getDeleted() call to capture deletes. We have observed that the call is not reliable for the OpportunityLineItem object. Most Salesforce objects store the deleted records for 15 days; however, for the OpportunityLineItem object, the records are directly deleted. The records are not sent to the recycle bin and cannot be recovered. Due to this limitation, the deleted records are sometimes purged from the OpportunityLineItem object before we capture them.

We must re-import the OpportunityLineItem object to capture deletes. Contact our Support team to enable the re-import of the OpportunityLineItem object.

NOTE: This also applies to the opportunity child objects, OpportunitySplit and OpportunityLineItemSchedule.

Re-sync scenarios

Deleted records are retained in the Recycle Bin for 15 days. After 15 days, deleted items are purged from the Recycle Bin, and we can't use the getDeleted() to fetch them.

We show a re-sync table warning to capture deletes if:

• We are unable to capture deleted records of a specific table for more than 15 days.
• We receive exceptions like TooMuchDataException when fetching deleted records.

(Optional) AWS PrivateLink

IMPORTANT: You must have a Business Critical plan to use AWS PrivateLink.

If you select PrivateLink as a connection method, Fivetran uses AWS PrivateLink to move your data securely between Salesforce and your destination. You must have a Salesforce Private Connect license to use AWS PrivateLink.

To set up AWS PrivateLink for your Salesforce source, follow the instructions in the Salesforce setup guide.

Incremental syncs

We sync most tables using incremental syncs. To run incremental syncs, Fivetran requires reliable timestamp fields. Timestamp fields allow us to detect which rows have changed since the last sync and sync only new or modified data. In Salesforce, we use one of the following timestamp fields to track when data was last updated in the source:

• SystemModStamp
• LastModifiedDate
• CreatedDate
• LoginTime

If none of these fields are available in a table, then we re-import the table.

NOTE: Salesforce does not update the incremental column SystemModstamp/LastModified for objects when there is an update in derived fields. Salesforce derived field values are never actually stored nor modified. As a result, your incremental syncs will not include updated data from these fields. To learn more about derived fields, see Salesforce's documentation.

Re-import tables

We re-import tables for the relevant Salesforce objects that have the replicateable property set to FALSE.

We also re-import the AccountTeamMember and OpportunityTeamMember tables because Salesforce does not update the SystemModstamp column for these tables if you change their access settings by modifying the organization-wide sharing defaults.

The re-import frequency depends on the time it takes for a table to re-sync.

Schema information

Fivetran follows a one-to-one mapping with all objects in Salesforce. If you can't find something that you're looking for, your Salesforce user may have limited access to the information. Fivetran tries to sync all possible objects, except for formula fields, which must be replicated using transformations to ensure integrity.

Schema

This schema diagram illustrates how objects relate to one another. It is not a complete Entity Relationship Diagram. It shows relationships for some central entities and workflows. Many standard and all custom entities that are delivered by Salesforce connections are omitted for brevity.

We also offer you information about the connection in the fivetran_query table.

System tables

FIVETRAN_API_CALL

The FIVETRAN_API_CALL table tracks the information about all the API calls Fivetran makes to sync data into the destination.

FIVETRAN_DEPENDENT_PICKLIST_RELATION

The FIVETRAN_DEPENDENT_PICKLIST_RELATION table stores the dependent picklist relations.

FIVETRAN_FORMULA

The FIVETRAN_FORMULA table stores the formulas defined in Salesforce, which we use to translate formula fields.

FIVETRAN_FORMULA_FAILURE_REASON

The FIVETRAN_FORMULA_FAILURE_REASON table stores the information about why Fivetran couldn't translate a particular formula field.

FIVETRAN_FORMULA_MODEL

The FIVETRAN_FORMULA_MODEL table stores SQL view models for the objects with translated formula fields.

FIVETRAN_PICKLIST_FIELD

The FIVETRAN_PICKLIST_FIELD table stores information about all the picklist fields.

FIVETRAN_PICKLIST_FIELD_VALUE

The FIVETRAN_PICKLIST_FIELD_VALUE table stores possible values for the picklist fields.

FIVETRAN_QUERY

The FIVETRAN_QUERY table records the details for every Salesforce Object Query Language (SOQL) query Fivetran makes.

Unsupported data types and object types

Our Salesforce connector does not support the following data types and object types:

• base64 type (exception: VersionData (base64 field) of ContentVersion object, which we sync in Salesforce Files)
• complexvalue type
• Deprecated and hidden objects
• Objects that don't support LIMIT
• BigObject, or objects whose names end with __b
• Non-queryable objects
• Objects that can't be accessed using Salesforce Object Query Language (SOQL) queries or Salesforce APIs
• Object whose names end with __x or __hd
• The AccountUserTerritory2View object due to sync performance issues

Compound fields

Although Fivetran does not sync compound fields as is, we sync all fields of a compound field in a Salesforce object as the corresponding individual fields in their respective tables corresponding to the object. For example, the Address compound field may appear as BillingAddress in the User object in Salesforce. In that case, the subfields of the Address compound field are synced to the USER table in your destination as individual columns that have the billing_ prefix (for example, billing_latitude, billing_postal_code, etc.). You can reconstruct the compound field downstream using these individual fields.

Type conversion

We convert empty strings ("") to null objects.

Syncing empty tables and columns

Fivetran can sync empty tables and columns for your Salesforce connection. For more information, see our Features documentation.