Custom queries

Note: The first release of this feature specifically benefits users of Salesforce as a data source using Connect to bring data into their system.

Data source custom query provides you with the flexibility and control of specifying a custom SOQL (Salesforce Object Query Language) statement to define the columns and rows of Salesforce data to ingest into your system.

Using data source custom query

Note: The pre-requisites required are currently only the same as using Salesforce as a data source.

To use data source custom query:

1. Follow the same steps that you would to set up a data source, i.e.

2. Go to Data Sources.

   

3. Click + New Data Source.

   

4. Select your New Data Source.

   

5. Enter your Data Source Name and click Create.

   

6. Create a + New Credential, or select an existing one.

   

7. If creating a new data source, populate the relevant data.

   

8. From the tables menu, you can now select Custom Query.

   

9. You can now enter your Custom Query in the text box.

   

See below technical notes and example queries for further information on next steps with data source custom queries.

Technical notes

Note: Custom queries require knowledge of SOQL syntax. This requires a requisite level of knowledge on custom queries and should not be attempted without that knowledge level.

Our existing implementation doesn’t include a mechanism to build your queries. We therefore recommend developing and testing your queries before entering them into the query text box.

Recommended tools:

• Salesforce Developer Console Query Editor

• How to open the developer console in your Salesforce environment

• Salesforce extensions for Visual Studio Code

Example queries

Here are some examples of the custom queries you might use:

Retrieving data from the contact table

SELECT 
Salutation,LastName,    FirstName,    Name,    MailingStreet,    MailingCity,    MailingState,
MailingPostalCode
FROM Contact

Retrieving data from the Contact and Account tables (Account is a parent table to the Contact)

SELECT Id, Salutation, FirstName, LastName, Email,
MailingStreet, MailingCity, MailingState, MailingPostalCode,
Account.Name, Account.Type, Account.Industry, Account.BillingStreet,
Account.BillingCity, Account.BillingState
FROM Contact

Retrieving filtered data from the Contact and Account tables (Account is a parent table to the Contact)

SELECT Id, Salutation, FirstName, LastName, Email,
MailingStreet, MailingCity, MailingState, MailingPostalCode,
Account.Name, Account.Type, Account.Industry, Account.BillingStreet,
Account.BillingCity, Account.BillingState
FROM Contact WHERE IsDeleted = false AND Account.BillingState = 'CA'

SOQL Limitations

We’re using SOQL delivered via the Salesforce bulk API. This imposes some limitations on the types of query that you can run.

Bulk API 2.0 doesn’t support SOQL queries that include any of these items:

• GROUP BY, OFFSET, or TYPEOF clauses.

• Aggregate Functions such as: COUNT()

• Date functions in GROUP BY clauses. (Date functions in WHERE clauses are supported)

• Compound address fields or compound geolocation fields (Instead, query the individual components of compound fields).

• Parent-to-child relationship queries (Child-to-parent relationship queries are supported)

  • It's possible to refer to and use columns from parent tables (up to five levels above) but not from child tables.

Source: https://developer.salesforce.com/docs/atlas.en-us.api_asynch.meta/api_asynch/queries.htm