Salesforce SOQL

Overview

The Salesforce SOQL Snap retrieves object records from Salesforce using a Salesforce Object Query Language (SOQL) query.

See Introduction to SOQL and SOSL for more information.

Note: To use newer Salesforce objects, use the latest API versions where those objects are available.

This is a Read-type Snap.
Works in Ultra Tasks

Supported Accounts

Limitations

For a Salesforce SOQL Snap using the REST API, certain network failures while reading a REST response may result in a socket timeout exception.
When using Primary Key (PK) Chunking mode in this Snap, the output document schemas in preview mode and execution mode may differ. For more information, see PK Chunking.

Snap views


Type	Description	Examples of upstream and downstream Snaps
Input	This Snap has at most one document input view. Min: 0, Max: 1	JSON Generator Mapper
Output	This Snap has exactly one document output view. Min: 1, Max: 1	Mapper JSON Formatter
Learn more about Error handling.

Snap settings

Legend:

Expression icon (): Allows using pipeline parameters to set field values dynamically (if enabled). SnapLogic Expressions are not supported. If disabled, you can provide a static value.
SnapGPT (): Generates SnapLogic Expressions based on natural language using SnapGPT. Learn more.
Suggestion icon (): Populates a list of values dynamically based on your Snap configuration. You can select only one attribute at a time using the icon. Type into the field if it supports a comma-separated list of values.
Upload : Uploads files. Learn more.

Learn more about the icons in the Snap settings dialog.


Field/Field set	Description
Label `String`	Required. Specify the name for the Snap. You can modify this to be more specific, especially if you have more than one of the same Snap in your pipeline. Default value: Salesforce SOQL Example: Salesforce SOQL
Service Version `String`	Required. Specify the version number associated with the Salesforce service that you want to connect to. Alternatively, click the Suggestion icon to fetch the list of versions and select the desired version. Default value: 52.0 Example: 41.0
Salesforce API `Dropdown list`	Select the Salesforce API mode to `Bulk API` or `REST API`. Note: If you select Bulk API, the Object Type field becomes mandatory. Default value: REST API Example: Bulk API
SOQL Query `String/Expression`	Required. Define the SOQL query statement. You can enter an expression, which is evaluated each time the Snap is executed. This Snap supports the SOQL keyword `FIELDS()`, which can be used to select a pre-defined collection of fields from an object in place of the typical field list. Learn more. To use `FIELDS()` in a SOQL query, the Snap's service version must be set to 51.0 or higher. Note: Using quotes in the WHERE clause Do not use quotes for field names in the WHERE clause. Using quotes results in an error. Use only single quotes for values in the WHERE clause as using double quotes results in an error. The above rules do not apply when you are using SnapLogic expressions, you can use quotes for the field names and values as applicable. Default value: N/A Example: SELECT Id, Name FROM Account WHERE LastModifiedDate > 2020-01-01T00:00:00Z
Batch size `Integer`	Specify the number of records to process in a batch. This refers to the number of records in each batch during the download of large query results. Each batch read requires an API call against Salesforce to retrieve the set of records. This Snap does not honor the selection of the Match Data Type checkbox when the value entered for Batch Size is greater than 50,000. REST API: The batch sizes 200 and 2000 are valid in REST API. According to the SFDC REST API documentation: "There is no guarantee that the requested batch size is the actual batch size. Changes are made as necessary to maximize performance." In fact, if the size of each record in the query result is relatively large (e.g. a few hundred output fields), SFDC may respond in a batch size of 200, And if the size of each record in the query result is relatively small, SFDC may respond in a batch size of 2000 regardless if the Batch size is set to 200 or 2000. Bulk API: Selecting the values 100,000 or 250,000 will make the Bulk API request as PK-chunking with a chunk size of 100,000 or 250,000. Other values 200 or 2000 will have the Snap submit a single regular Bulk API batch job to SFDC. According to the SFDC Bulk API documentation (Number of attempts to query): "15 attempts at 10 minutes each to process the batch. There is also a 2-minute limit on the time to process the query. If more than 15 attempts are made for the query, an error message of 'Tried more than fifteen times' is returned. If the query takes more than 2 minutes to process, a QUERY_TIMEOUT error is returned." Therefore, if the query is expected to take a long time to execute (e.g. the target SObject has more than several hundred thousand records and the number of output fields in a record is more than a few hundreds), it is highly recommended to set this property to 100,000 or 250,000 to avoid timeout errors. Default value: 2000 Example: 2000
Object Type `String`	Specify the name of the Salesforce object or select one from the suggested list. Note: This Snap does not support Net Zero Cloud Salesforce objects. Default value: Account Example: Account
Polling Interval `Integer`	Required. Specify the polling interval in seconds for the Bulk API query execution. At each polling interval, the Snap checks the status of the Bulk API query batch processing. Maximum value: 60 Default value: 5 Example: 5
Polling Timeout `Integer`	Required. Specify the polling timeout in seconds for the Bulk API query batch execution. If the timeout occurs while waiting for the completion of the query batch execution, the Snap throws an exception. Default value: 3000 Example: 300
Include Deleted records `Checkbox`	Select this checkbox to include deleted records in the query result. This feature is supported in REST API version 29.0 or later and Bulk API version 39.0 or later. Default value: Deselected
Pass Through `Checkbox`	Select this checkbox to pass the input document through to the output view under the key 'original'. This property is applicable for REST and Bulk APIs. Default value: Selected
Ignore Empty Result `Checkbox`	Select this checkbox to ignore empty result, so no document will be written to the output view when the operation does not produce any result. If you deselect this checkbox and select Pass Through, the input document will be passed through to the output view. If you do not select both the checkboxes, Ignore Empty Result and Pass Through, the Snap writes an empty output as below: `[{}]` Note: This property does not apply when you run the Snap in `Bulk API` mode. When you run the Snap in `Bulk API` mode and the operation does not produce any result, the Snap does not pass the input document to the output view even if the Pass Through checkbox is selected. Default value: Selected
Bulk Content Type `Dropdown list`	Select the content type for Bulk API: `JSON` or `XML`. Note: The numeric type field values will be read as numbers in JSON content type, and as strings in XML content type, in the output documents. JSON content type for Bulk API is available in Salesforce API version 36.0 or higher. In REST API, the number-type field values will always be read as numbers. If the Bulk API has been selected along with 100,000/250,000 as batch size value, the content-type will always be CSV regardless of the value set in this property. When you configure the Snap in PK Chunking (that is, if the Batch size is set to 100,000 or 250,000), the Snap performs the preview execution in Bulk API, not in PK Chunking. Default value: XML Example: JSON
Escape Single Quotes in Parameter Values `Checkbox`	Select this checkbox to escape single quotes in the parameter values when the SOQL query property is expression-enabled. You must use this feature when the WHERE clause values are parameterized and contain single quotes. For example, `"SELECT Name,Email FROM Contact WHERE Name='" + $Name + "'"`. If you clear the selection, the SOQL query property is evaluated without escaping the single quotes. Default value: Selected
Preview in Bulk API `Checkbox`	Select this checkbox to view the preview in Bulk API. If you select this option and the Salesforce API property is Bulk API, the Snap is executed in Bulk API during the preview. Note: If the query is expected to take long to complete its execution, the preview will also take longer or fail with a timeout error. Users may add "LIMIT 50" to the query to get the preview result fast. If you deselect this checkbox and Salesforce API is Bulk API, the Snap previews in REST API. The data schema of the query results in REST API may be different from the ones in Bulk API, and the data types of some fields are different between REST API and Bulk API. This issue may result in cases where the pipeline previews successfully, but fails in execution. Default value: Deselected
Number of Retries `Integer`	Specify the maximum number of retry attempts in case of a network failure. Note: If the value is larger than zero in PK-Chunking, the Snap overrides user settings and sets Use temp files in PK-Chunking property to true. In Bulk API and PK-Chunking modes, the Snap downloads each batch of data into a temporary local file. When the download is complete, the Snap writes the output documents parsed from the temporary file to avoid the possibility of duplicated output documents in the downstream Pipeline. Ensure that the local drive has sufficient free disk space in the node where the Pipeline executes. All temporary local files are deleted when they are no longer needed. Minimum value: 0 Default value: 1 Example: 3
Retry Interval (seconds) `Integer`	Specify the minimum number of seconds for which the Snap must wait before attempting recovery from a network failure. Minimum value: 0 Default value: 1 Example: 3
Match Data type `Checkbox`	Select this checkbox to match the data types of the Bulk API results with the data types of the REST API. This property applies only when the content type is XML for Bulk API (it does not apply to JSON). If the Bulk content type is XML, Salesforce.com returns all values as strings. If Match data type is selected, the Snap attempts to convert string values to the corresponding data types if the original data type is one of the following: boolean, integer, double, currency, or percent. This property is ignored in REST APIs or when the Bulk Content Type is `JSON`. For Bulk API, Salesforce.com does not return any value for null. This Snap does not honor the selection of the Match Data Type checkbox when the value entered for Batch Size is greater than 50,000. Default value: Deselected
Advanced Properties	Use this field set to set advanced properties. By default, the advanced properties are not required and is an empty table property. Click the add icon to add an advanced property to set a value other than the default value. This field set contains the following fields: Properties Values
Properties `Dropdown list`	Select either of the following properties: Use temp files in PK-Chunking Number of threads in PK-Chunking Validate record count downloaded in Bulk API Default value: N/A
Values `String`	Specify the value for property selected in the Properties field. The default values are: Use temp files in PK-Chunking: false Number of threads in PK-Chunking: 8 Validate record count downloaded in Bulk API: false Default value: N/A Example: true
Snap execution `Dropdown list`	Choose one of the three modes in which the Snap executes. Available options are: `Validate & Execute`. Performs limited execution of the Snap and generates a data preview during pipeline validation. Subsequently, performs full execution of the Snap (unlimited records) during pipeline runtime. `Execute only`. Performs full execution of the Snap during pipeline execution without generating preview data. `Disabled`. Disables the Snap and all Snaps that are downstream from it.

PK Chunking

PK chunking splits bulk queries on very large tables into chunks based on the record IDs, or primary keys, of the queried records. Each chunk is processed as a separate batch that counts toward your daily batch limit. PK chunking is supported for the following objects: Account, Campaign, CampaignMember, Case, Contact, Lead, LoginHistory, Opportunity, Task, User, and custom objects.

PK chunking works by adding record ID boundaries to the query with a WHERE clause, limiting the query results to a smaller chunk of the total results. The remaining results are fetched with additional queries that contain successive boundaries. The number of records within the ID boundaries of each chunk is referred to as the chunk size. The first query retrieves records between a specified starting ID and the starting ID plus the chunk size, the next query retrieves the next chunk of records, and so on. Since Salesforce.com appends a WHERE clause to the query in the PK Chunking mode, if SOQL Query has LIMIT clause in it, the Snap will submit a regular bulk query job without PK Chunking. See PK Chunking Header for more details.

If 100,000 or 250,000 is selected for Batch size, the value is used as a chunk size in the PK Chunking. Note the chunk size applies to the number of records in the queried table rather than the number of records in the query result. For example, if the queried table has 10 million records and 250,000 is selected for Batch size, 40 batches are created to execute the bulk query job. One additional control batch is also created and it does not process any record. The status of the submitted bulk job and its batches can be monitored by logging onto your Salesforce.com account in a web browser and going to Setup > Administration Setup > Monitoring > Bulk Data Load Jobs.

PK Chunking requires Service version 28.0 or later. Therefore, if 100,000 or 250,000 is selected for Batch size and Service version is older than 28.0, the Snap submits a regular bulk query job without PK Chunking.

The output document schemas in preview mode (validation) and execution mode for this Snap may differ when using Primary Key (PK) Chunking mode. This is because the Snap intentionally generates the output preview using regular Bulk API instead of PK Chunking, to reduce the costs involved in the PK Chunking operation. Salesforce recommends use of PK Chunking if the target Salesforce object (table) is relatively large (for example, more than few 100,000 records).

CSV files are generated as a result of PK Chunking Bulk API request in Salesforce.com. The Snap downloads them into temporary files in local disk of the instance where the pipeline is executed. Therefore, users should make sure sufficient free disk space is available for all CSV files. These temporary files are removed when the CSV parsing is completed.

Troubleshooting

None.