Data Cloud Developer Guide

Data Cloud Developer Guide
Connect Data
Ingest Data
Model Data
Extract Data
Retrieve Metadata
Query Customer Profile Information with Profile API
Query Data using Query API V2
POST /api/v2/query
GET /api/v2/query/{nextBatchId}
Query Data using Query API V1
Query Services Status Codes
Query Source Record IDs
Query Calculated Insights
CDP Python Connector
API Limits for Profile, Query, and Calculated Insights
Webhook Data Action Targets in Data Cloud
PDF

Query Data using Query API V2

Query API V2 supports SQL query in ANSI standard. The results return as an array of records. The expected input when calling this API is free form SQL. The input objects include data stream, profile and engagement data model objects, and unified data model objects. You can use Query API V2 to support a variety of use cases, which includes large volume data reads, external application integration, or interactive on demand querying on the data lake.

We recommend using Query API V1 for smaller extract queries. You can also use the API when you want to fetch a limited set of data by specifying offsets and limits.

Note

Key Properties

  • Cursor pagination is supported.
  • GET and POST API calls are supported.
  • The POST endpoint is used to make the first request. The POST endpoint returns data along with batchId for next batch using cursor pagination.
  • The GET endpoint is used for subsequent requests for the same query.
  • The GET endpoint requires a batchId as a parameter.
  • Pagination is forward cursor only, which means the same batchId couldn’t be used more than one time.
  • As subsequent GET calls need nextBatchId value from last call, hence the query execution is done serially.
  • There’s no explicit limit on the number of nested subqueries.
  • There’s no explicit limit on the number of joins.
  • There’s no explicit limit on the number of filters.
  • There’s no explicit limit on the number of column projections.
  • There’s no explicit limit on the data types.
  • In the response a done boolean flag is exposed. If the flag is set to true, then there are no more records to be queried. If the flag is set to false, then the next page can be retrieved using the next batchId.
  • Response data size is limited to a maximum of 8 MB.
  • The next batch calls are allowed for up to one hour.
  • When using aggregate functions, the results computed are of number types instead of string types.

The response contains a queryId. The ID remains the same irrespective of POST and GET calls.

Note

Connected App Setup

Your orgs must be provisioned with Data Cloud licenses and the users must be assigned to appropriate roles for having full access to objects in the Data Cloud. Refer to User Roles and Permission Sets in Data Cloud before setting up the Connected App.

Important

Set Up a Connected App to discover new insights about your customers.

Acquire and Exchange Your Access Token for Data Cloud

Refer to Getting Started page to acquire the token.

Key Qualifiers

We recommend that you include key qualifier fields in all table joins for queries submitted through the Query API V2. Make sure to use the COALESCE() function to get a null-safe join. When key qualifiers aren’t configured on data lake object fields, the value for the key qualifier field is null. In such cases, use the COALESCE() function to achieve desired results. This sample query covers the usage of COALESCE() function.
1SELECT * FROM ssot__ContactPointEmail__dlm email 
2LEFT JOIN ssot__Individual__dlm individual 
3ON email.ssot__PartyId__c = individual.ssot__Id__c 
4AND 
5COALESCE(email.KQ_PartyId__c, '') = COALESCE(individual.KQ_Id__c, '') 
6limit 10

Call Reference