Salesforce external table gem

This gem runs in .

Overview

The Salesforce gem enables you to read data from Salesforce directly into your Prophecy pipelines. The gem supports both SOQL (Salesforce Object Query Language) for querying standard Salesforce objects and SAQL (Salesforce Analytics Query Language) for accessing analytical datasets.

Create a Salesforce gem

To create a Salesforce Source or Target gem in your pipeline:

Add a Source or Target gem to the pipeline

Open your pipeline in the Studio.
Click on Source/Target in the canvas.
Select Source or Target from the dropdown.
Click on the gem to open the configuration.

Select Salesforce format

In the Type tab, select Salesforce. Then, click Next.

Set location details

In the Location tab, set your connection details and query configuration. To learn more, jump to Source location and Target location.

Set table properties

In the Properties tab, set the table properties. To learn more, jump to Source properties and Target properties.

Preview data (Source only)

In the Preview tab, load a sample of the data and verify that it looks correct.

Source configuration

Use these settings to configure a Salesforce Source gem for reading data.

Source location

Parameter	Description
Format type	Table format for the source. For Salesforce tables, set to `salesforce`.
Select or create connection	Select or create a new Salesforce connection in the Prophecy fabric you will use.
Query Mode	Specify the table you would like to read using a query. Learn more in Query modes.

Query modes

You have three options for retrieving data from Salesforce.

SAQL

Use a SAQL query to access CRM Analytics datasets (formerly known as Wave Analytics). For example:

q = load "Account"; q = foreach q generate 'Id' as 'Id', 'Name' as 'Name'; q = limit q 5;

SOQL

Use a SOQL query to read structured data from Salesforce objects such as Account or Contact tables. For example:

SELECT Id, Name, Industry, CreatedDate FROM Account WHERE CreatedDate >= LAST_N_DAYS:30

When using the FIELDS(ALL) keyword, the response is limited to 200 rows per call.

Prophecy does not support SOQL parent-to-child relationship queries that use nested subqueries. Query parent and child objects separately, then join the datasets in Prophecy.For example, the following query is not supported:

SELECT Id,
       Name,
       (SELECT FirstName, LastName FROM Contacts)
FROM Account

Salesforce Objects

Retrieve all records from a specific Salesforce object by name, without writing a query. Use this option when the object exceeds the default 200 row limit of FIELDS(ALL).

Source properties

The following properties are available for the Salesforce Source gem. These properties only apply to tables retrieved with the SOQL or Salesforce Object query mode.

Property	Description
Enable bulk query	Enable to run the query as a batch job in the background for better performance on large datasets.
Retrieve deleted and archived records	Enable to include soft-deleted and archived records in the returned table.

Target configuration

Use these settings to configure a Salesforce Target gem for writing data.

The Salesforce Target gem only supports writing to Salesforce Objects. You cannot write to CRM Analytics datasets.

Target location

Use the following parameters to define the write location.

Parameter	Description
Format type	Table format for the source. For Salesforce tables, set to `salesforce`.
Select or create connection	Select or create a new Salesforce connection in the Prophecy fabric you will use.
Object Name	Name of the Salesforce Object to write to. If the object doesn’t exist, it will be created automatically.

Target properties

The following properties are available for the Salesforce Target gem.

Property	Description	Default
Description	Text description of the target table. Use this field to document the purpose or content of the data being written.	None
Write Mode	Defines how records are written to Salesforce. Upsert: Update existing records if a match is found; otherwise, insert new records. Insert: Add new records without modifying existing ones. Update: Update existing records if a match is found. Delete: Remove existing records if a match is found.	Upsert
External ID Field	Specifies the Salesforce field used as the unique key when performing Upsert or Update operations. The field must be defined as an External ID in Salesforce.	None
Use Bulk API	Enable to run write operations using the Salesforce Bulk API.	Disabled

Overview

Sources and targets

Data processing

Reporting

Advanced

Overview

Create a Salesforce gem

Source configuration

Source location

Query modes

SAQL

SOQL

Salesforce Objects

Source properties

Target configuration

Target location

Target properties

​Overview

​Create a Salesforce gem

​Source configuration

​Source location

​Query modes

​SAQL

​SOQL

​Salesforce Objects

​Source properties

​Target configuration

​Target location

​Target properties

Overview

Create a Salesforce gem

Source configuration

Source location

Query modes

SAQL

SOQL

Salesforce Objects

Source properties

Target configuration

Target location

Target properties