Using the SOAP and Bulk API

You can use the SOAP or Bulk API to query existing Salesforce data. When querying existing data, you define the SOQL query and related properties to determine the data returned from Salesforce.

Use the following guidelines when using the SOAP API or the Bulk API without PK Chunking:

SOQL query

When processing existing data with the SOAP API or the Bulk API, use the following query guidelines:

In the WHERE clause, include the offset field and the offset value.
The origin uses an offset field and value to determine the data that is returned. Include both in the WHERE clause of the query.
In the WHERE clause, use the OFFSET constant to represent the offset value.
Use ${OFFSET} to represent the offset value. For example, when you start a pipeline, the following query returns all data from the object where the data in the offset field is greater than the initial offset value:
```
SELECT Id, Name FROM <object> WHERE <offset field> > ${OFFSET}
```
Tip: When the offset values are strings, enclose ${OFFSET} in single quotation marks.
In the ORDER BY clause, include the offset field as the first field.

To avoid returning duplicate data, use the offset field as the first field in the ORDER BY clause.

Note: Using a field that is not the Id field in the ORDER BY clause can slow performance.

When processing existing data with the SOAP API, you can include SOQL aggregate functions in the SELECT statements of SOQL queries. The Bulk API does not support aggregate functions.

The complete SOQL query should use the following syntax:

SELECT <offset field>, <field1>, <field2>, ... FROM <object> WHERE <offset field> > ${OFFSET} ORDER BY <offset field>

If you specify SELECT * FROM <object> in the SOQL query, the origin expands * to all fields in the Salesforce object that are accessible to the configured user. Note that the origin adds components of compound fields to the query, rather than adding the compound fields themselves. For example, the origin adds BillingStreet, BillingCity, etc., rather than adding BillingAddress. Similarly, it adds Location__Latitude__s and Location__Longitude__s rather than Location__c.

When necessary, you can configure the origin to skip validating the query. Skip query validation when you know that the query is valid but it does not match validation requirements. For example, you must disable query validation if you omit the ORDER BY clause. You might omit the ORDER BY clause to improve performance on a large query. To disable query validation, use the Disable Query Validation property on the Advanced tab.

Additional properties

When processing existing data with the SOAP API or the Bulk API, configure the following additional properties on the Query tab:

Offset Field - Typically the Id system field, the offset field should be an indexed field in the record. Default is the Id field.
Initial Offset - First offset value to use when the pipeline starts or after you reset the origin.
Include Deleted Records - An optional property. Determines whether the SOQL query also retrieves deleted records from the Salesforce recycle bin.
The query can retrieve deleted records when the stage uses the Salesforce SOAP API or the Bulk API version 39.0 or later. Earlier versions of the Bulk API do not support retrieving deleted records.