Data Cloud Reference Guide

Data Cloud Reference Guide
Connect Data
Ingest Data
Model Data in Data Cloud
Extract Data
Retrieve Metadata
Query Data Graph Data
Query Customer Profile Information with Profile API
Query Data using Query API V2
Query Data using Query API V1
POST /api/v1/query
Query Services Status Codes
Query Unified Record ID
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 V1

The Query API V1 supports SQL query in ANSI standard. The SQL can be a free form SQL with objects that include data streams, profile or engagement data model objects, and unified data model objects. The Query API V1 supports only synchronous calls. You can use the API to support a variety of use cases that include data extraction, external application integration or interactive querying on the data lake. If you don't want to fetch the entire data like in case of browser-based clients then you can make a call to the API with limit and offset.

A newer version of the Query API is available. We recommend using Query V2 API to take advantage of GET calls in addition to POST calls, subsequent requests, and larger response sizes.

Note

Key Properties

  • The call return limit defaults to the max limit of 49,999 rows returned. Use a done flag to check whether more records exist.
  • There’s no explicit limit to the SQL call length.
  • There’s no explicit limit for the number of nested subqueries.
  • There’s no explicit limit for the number of joins.
  • There’s no explicit limit for the number of filters.
  • There’s no explicit limit for the number of column projections.
  • There’s no explicit limit for any specific data type, such as VARCHAR, DECIMAL, or TIMESTAMP.
  • Pagination supported via limit, offset, and orderby parameters.
    • When done is flagged as true in the response, then there are no more records to query. When the response is false, call orderby with offset param to retrieve the next set of numbers.

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 the 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