Load Salesforce data into BigQuery

You can load data from your Salesforce Sales Account to BigQuery using theBigQuery Data Transfer Service for Salesforce connector. With theBigQuery Data Transfer Service, you can schedule recurring transfer jobs thatadd your latest data from your Salesforce Sales Account toBigQuery.

Limitations

Salesforce data transfers are subject to the following limitations:

• The Salesforce connector only supports transfers from aSalesforce Sales Account.
• The Salesforce connector only supports fields included in Salesforce BulkAPI V1 version 64.0. Some fields that were included in previous versions of the SalesforceBulk API might not be supported. For more information about these changes to the Salesforceconnector, seeSalesforce Bulk API.
• The Salesforce connector uses Salesforce Bulk API V1 to connectto the Salesforce endpoint to retrieve data.
  • The Salesforce connector only supports the SalesforceBulk API V1 to connect to the Salesforce instance, and only supports thetransfer of entities which are supported by the Salesforce Bulk API. For moreinformation about what entities are supported, see'Entity is not supported by the Bulk API' error.
• The Salesforce connector does not support the transfer ofthe following objects that have binary fields.
  • Attachment
  • ContentVersion
  • Document
  • StaticResource
  • Scontrol
  • EmailCapture
  • MailMergeTemplate
• The minimum interval time between recurring data transfers is 15 minutes. Thedefault interval for a recurring transfer is 24 hours.
• Due to Salesforce processing limits, scheduling too many datatransfers at a time can lead to delays or failures. We recommend that you limitSalesforce data transfers to the following:
  • Have no more than 10 assets per transfer configuration.
  • Across your different transfer configurations, have no more than 10simultaneous transfer runs at a time.
• A single transfer configuration can only support one data transfer run at a giventime. In the case where a second data transfer is scheduled to run before thefirst transfer is completed, then only the first data transfer completes whileany other data transfers that overlap with the first transfer is skipped.
  • To avoid skipped transfers within a single transfer configuration, werecommend that you increase the duration of time between large datatransfers by configuring theRepeat frequency.
• If you are using network attachments with your data transfer, you mustset up a public network address translation (NAT) with a static IPaddress. For more information, seeSetup IP allowlist for Salesforce transfers.
• If your configured network attachment and virtual machine (VM) instance arelocated in different regions, there might be cross-region data movement whenyou transfer data from Salesforce.

Incremental transfer limitations

• You can only chooseTIMESTAMP columns as watermark columns.
• Incremental ingestion is only supported for assets with valid watermark columns.
• Values in a watermark column must be monotonically increasing.
• Incremental transfers cannot sync delete operations in the source table.
• A single transfer configuration can only support either incremental or fullingestion.
• You cannot update objects in theasset list after the first incrementalingestion run.
• You cannot change the write mode in a transfer configuration after the firstincremental ingestion run.
• You cannot change the watermark column or the primary key after the firstincremental ingestion run.

Data ingestion options

The following sections provide more information on the data ingestion optionswhen you set up a Salesforce data transfer.

Full or incremental transfers

You can specify how data is loaded into BigQuery by selectingeither theFull orIncremental writepreference in the transfer configuration when youset up aSalesforce transfer. Incremental transfersare supported inpreview.

Alternatively, you can selectIncremental(Preview) to onlytransfer data that was changed since the last data transfer, instead of loadingthe entire dataset with each data transfer. If you selectIncremental foryour data transfer, you must specify either theAppend orUpsert writemodes to define how data is written to BigQuery during anincremental data transfer. The following sections describe the available writemodes.

Append write mode

TheAppend write mode only inserts new rows to your destination table. This optionstrictly appends transferred data without checking for existing records, sothis mode can potentially cause data duplication in the destination table.

When you select theAppend mode, you must select a watermark column. Awatermark column is required for the Salesforce connector to track changesin the source table.

Upsert write mode

TheUpsert write mode either updates a row or inserts a new row in yourdestination table by checking for a primary key. You can specify a primary keyto let the Salesforce connector determine what changes are needed to keepyour destination table up-to-date with your source table. If the specifiedprimary key is present in the destination BigQuery table during a data transfer, then the Salesforceconnector updates that row with new data from the source table. If a primary keyis not present during a data transfer, then the Salesforce connectorinserts a new row.

When you select theUpsert mode, you must select a watermark column and aprimary key:

• A watermark column is required for the Salesforce connector to track changes in the source table.
  • Select a watermark column that updates every time a row is modified. We recommend using theSystemModstamp orLastModifiedDate column.

• The primary key can be one or more columns on your table that are requiredfor the Salesforce connector to determine if itneeds to insert or update a row.
  • Select columns that contain non-null values that are unique across allrows of the table. We recommend columns that include system-generatedidentifiers, unique reference codes (for example, auto-incrementing IDs), orimmutable time-based sequence IDs.
  • To prevent potential data loss or data corruption, the primary key columnsthat you select must have unique values. If you have doubts about theuniqueness of your chosen primary key column, then we recommend that youuse theAppend write mode instead.

Before you begin

The following sections describe the steps that you need to take before youcreate a Salesforce data transfer.

Create a Salesforce Connected App

You mustcreate a Salesforce Connected Appwith the following required configurations:

• Configure the Basic Informationin the Connected App. TheConnected App Name andContact Emailfields are required for a Salesforce transfer.
• Enable OAuth Settingswith the following configurations:
  • Select theEnable OAuth Settings checkbox.
  • In theCallback URL field, enter the following:
    • For a production environment, enterhttps://login.salesforce.com/services/oauth2/token.
    • For a sandbox environment, enterhttps://test.salesforce.com/services/oauth2/token.
  • Verify that theIssue JSON Web Token(JWT)-based access tokens for named userscheckbox isn't selected.
• In theSelected OAuth Scopes section, selectManage user data via APIs (api).
• Clear theRequired Proof Key for Code Exchange (PKCE) Extension for Supported Authorization Flowscheckbox.
• Select theEnable Client Credentials Flow, then clickOK on thenotice that appears.

Once you have configured the Connected App with the required configurations,clickSave. You are redirected to the detail page of your newly createdConnected App.

Once you have created the Connected App, you must also configure the clientcredentials flow by doing the following:

1. ClickSetup.
2. In the search bar, search forConnected Apps.
3. ClickManage Apps>Connected Apps. If you are usingthe Salesforce Lightning Experience, clickManage Connected Apps.
4. On the Connected App that you have created, clickEdit.
5. TheApp details page appears. In theClient Credentials Flowsection, enter your username in theRun As field. You can use thefinder tool in this field to ensure that you have selected the correctuser.
6. ClickSave.

Required Salesforce information

You must have the following Salesforce information when creating aSalesforce data transfer:

To obtain yourmyDomain,clientID, andclientSecret values,select one of the following options:

Setup IP allowlist for Salesforce transfers

You must configure your Google Cloud environment and your Salesforceaccount to add specific IP addresses to the allowlist for data transfers.This ensures that Salesforce only accepts connections from a trusted,static IP address. This step is required if you are using network attachmentswith your data transfers.

To do so, you must first set up and configure your Google Cloud network to usea static IP address:

1. Set up a public network address translation(NAT) with a static IPaddress within your Virtual Private Cloud network. The CloudNAT must be configuredwithin the same region as the destination dataset for this data transfer.
2. Set up a network attachmentwithin the same VPC network. This resource is used by theBigQuery Data Transfer Service to access private services.

Next, you mustconfigure the trusted IP ranges inSalesforce.When adding the range of the IP address, use the static IP address from yourGoogle Cloud public NAT for both the beginning and ending IP addresses for theIP range.

Once you have set up the IP ranges, you can now specify the static IP when youset up your transfer configuration by selecting your network attachment in theNetwork attachment field.

BigQuery prerequisites

• Verify that you have completed all actions required toenable the BigQuery Data Transfer Service.
• Create a BigQuery datasetto store your data.
• If you intend to set up transfer run notifications for Pub/Sub,ensure that you have thepubsub.topics.setIamPolicy Identity and Access Management (IAM) permission. Pub/Subpermissions are not required if you only set up email notifications. For moreinformation, seeBigQuery Data Transfer Service run notifications.

Required BigQuery roles

To get the permissions that you need to create a BigQuery Data Transfer Service data transfer, ask your administrator to grant you theBigQuery Admin (roles/bigquery.admin) IAM role on your project. For more information about granting roles, seeManage access to projects, folders, and organizations.

This predefined role contains the permissions required to create a BigQuery Data Transfer Service data transfer. To see the exact permissions that are required, expand theRequired permissions section:

You might also be able to get these permissions withcustom roles or otherpredefined roles.

For more information, seeGrantbigquery.admin access.

Set up a Salesforce data transfer

Add Salesforce data into BigQuery by setting up atransfer configuration using one of the following options:

bqmk--transfer_config--project_id=PROJECT_ID--data_source=DATA_SOURCE--display_name=NAME--target_dataset=DATASET--params='PARAMETERS'

"primaryKeys":[['Id'],['master_label','type']],"assets":["Account","CaseHistory"],

bqmk--transfer_config--target_dataset=mydataset--data_source=salesforce--display_name='My Transfer'--params='{"assets": ["Account", "CaseHistory"]        "connector.authentication.oauth.clientId": "1234567890",        "connector.authentication.oauth.clientSecret":"ABC12345",        "connector.authentication.oauth.myDomain":"MyDomainName",        "connector.authentication.username":"user1@force.com",        "connector.authentication.password":"abcdef1234",        "ingestionType":"incremental",        "writeMode":"WRITE_MODE_UPSERT",        "watermarkColumns":["SystemModstamp","CreatedDate"]        "primaryKeys":[['Id'], ['master_label','type']]}'

To manually run a data transfer outside of your regular schedule, you can startabackfill run.

Data type mapping

The following table maps Salesforce data types to thecorresponding BigQuery data types:

Pricing

For pricing information about Salesforce transfers, seeData Transfer Service pricing.

Troubleshoot transfer setup

If you are having issues setting up your data transfer, seeSalesforce transfer issues.

What's next

• For an overview of the BigQuery Data Transfer Service, seeIntroduction to BigQuery Data Transfer Service.
• For information on using transfers including getting information about atransfer configuration, listing transfer configurations, and viewing atransfer's run history, seeWorking with transfers.
• Learn how toload data with cross-cloud operations.