SfSkills Domains Docs GitHub

Home / integration / data-cloud-query-api

data-cloud-query-api

Use this skill when querying unified profile data, calculated insights, or Data Lake Objects from Data Cloud using ANSI SQL via the Query V2 or Query Connect APIs. Triggers on: SQL queries against Data Cloud, querying. NOT for SOQL against standard objects, Data Cloud segment filtering in the UI, or vector search — use data/data-cloud-data-streams.

Open the skill folder on GitHub

Data Cloud Query API

This skill activates when a practitioner needs to query unified profile data, calculated insights, or Data Lake Objects in Data Cloud programmatically using the Query V2 or Query Connect API. It covers authentication, SQL syntax requirements, pagination patterns, and the key differences between Query V2 and Query Connect.

Before Starting

Gather this context before working on anything in this domain:

Core Concepts

Authentication Is Separate from Standard Salesforce OAuth

Data Cloud uses a distinct token endpoint. After obtaining a standard Salesforce access token, you exchange it for a Data Cloud-scoped token:

POST https://<sf-instance>/services/a360/token
Content-Type: application/x-www-form-urlencoded
grant_type=urn:salesforce:grant-type:connected:app&subject_token=<sf_access_token>&subject_token_type=urn:ietf:params:oauth:token-type:access_token

The response contains access_token, instance_url, and critically dcInstanceUrl. Use dcInstanceUrl as the base for all subsequent Query API calls. Using the standard instance_url causes 404 errors.

Query V2 — Synchronous Cursor-Paginated SQL

Query V2 (POST /api/v2/query) executes ANSI-standard SQL (not SOQL) against unified profile objects (DMOs), calculated insights, and Data Lake Objects. It returns up to a platform-defined row limit per batch, plus a nextBatchId for pagination. Practitioners must issue a follow-up GET to /api/v2/query/{nextBatchId} to retrieve subsequent pages. The entire fetch operation must complete within 1 hour from query submission and no single inter-batch gap may exceed 3 minutes, or the cursor is invalidated.

Query Connect API — Row-Count-Unlimited Long-Running Queries

The Query Connect API is the successor pattern for large-scale result sets and supersedes both the V1 and V2 Query APIs. It removes the per-batch row-count ceiling entirely (no limit on the number of rows retrieved) and extends result availability to 24 hours, so it suits nightly exports and data pipeline integrations where Query V2's 1-hour window is insufficient. Execution runs on Salesforce's Hyper SQL engine, and identical repeated queries are served from a result cache that also expires after 24 hours.

Unlike Query V2's opaque nextBatchId cursor, Query Connect uses row-based (offset/limit) pagination — you retrieve chunks by passing rowLimit and offset query parameters against a stored queryId, so a slow consumer can re-request a page without the 3-minute cursor-expiry risk. It also supports parameterized queries. The endpoint family (rooted at dcInstanceUrl) is:

Method + pathPurpose
POST /ssot/query-sqlSubmit a query, returns queryId
GET /ssot/query-sql/{queryId}Check query status
GET /ssot/query-sql/{queryId}/rows?rowLimit=&offset=Retrieve a paginated page of rows
DELETE /ssot/query-sql/{queryId}Cancel a running query

Official docs do not label the Query Connect API as GA, Beta, or Pilot — treat it as the current, documented surface rather than asserting a maturity Salesforce has not stated.

ANSI SQL, Not SOQL

Data Cloud Query API uses ANSI SQL with Data Cloud-specific object names (e.g., ssot__Individual__dlm for the unified Individual DMO). SOQL syntax (e.g., SELECT Id FROM Contact WHERE ...) is invalid. Calculated Insight aliases are queried by their API name. You cannot use SOQL operators like IN :idList — only standard SQL IN (...) literals are supported.

Common Patterns

Pattern 1: Authenticate and Execute a Simple Profile Query

When to use: Querying unified individual profiles for a specific segment or identity match.

How it works:

import requests

# Step 1: Get standard SF token
sf_token = get_salesforce_token()

# Step 2: Exchange for Data Cloud token
dc_token_resp = requests.post(
    f"{sf_instance}/services/a360/token",
    data={
        "grant_type": "urn:salesforce:grant-type:connected:app",
        "subject_token": sf_token,
        "subject_token_type": "urn:ietf:params:oauth:token-type:access_token"
    }
)
dc = dc_token_resp.json()
dc_base = dc["dcInstanceUrl"]
dc_access_token = dc["access_token"]

# Step 3: Execute query
headers = {"Authorization": f"Bearer {dc_access_token}", "Content-Type": "application/json"}
query_resp = requests.post(
    f"{dc_base}/api/v2/query",
    headers=headers,
    json={"sql": "SELECT Id__c, FirstName__c, Email__c FROM ssot__Individual__dlm LIMIT 1000"}
)
result = query_resp.json()

Why not use SOQL: SOQL cannot reach unified DMO objects. The Data Cloud object model lives outside the standard Salesforce data layer.

Pattern 2: Full Pagination of Large Result Sets

When to use: When the result set exceeds a single batch and nextBatchId is returned.

How it works:

import time

rows = []
data = result

while True:
    rows.extend(data.get("data", []))
    next_id = data.get("nextBatchId")
    if not next_id:
        break
    # Must fetch next page within 3 minutes
    data = requests.get(
        f"{dc_base}/api/v2/query/{next_id}",
        headers=headers
    ).json()

print(f"Total rows: {len(rows)}")

Why not ignore nextBatchId: Ignoring it silently truncates results. There is no automatic merge — every page must be explicitly fetched.

Decision Guidance

SituationRecommended ApproachReason
Results fit in a few thousand rows, need results nowQuery V2 synchronousSimplest path, immediate results
Large export, >1 hour to paginate, nightly pipelineQuery Connect API24-hour result window, no row-count limit, offset/limit pagination immune to cursor expiry
Same query repeated within a day (e.g. scheduled refresh)Query Connect APIIdentical queries hit the 24-hour result cache
Semantic/vector similarity searchdata-cloud-vector-search-dev skillQuery API only supports SQL predicates, not vector search
Filtering on SOQL-style Salesforce IDsUse SQL IN literalsSOQL bind variables not supported in Data Cloud SQL
Querying a calculated insightUse CI API name in FROM clauseCIs are queryable as table-like objects by their API name
  1. Confirm the org has Data Cloud provisioned and the connected app has the required Data Cloud OAuth scope (cdp_api) — missing scope causes token exchange to fail with 400.
  2. Obtain a standard Salesforce access token via your preferred OAuth flow (Client Credentials, JWT Bearer, or User-Agent).
  3. Exchange it for a Data Cloud-specific token via /services/a360/token and capture dcInstanceUrl — never use the standard instance_url for Data Cloud API calls.
  4. Identify the target objects (DMO API names, DLO names, or Calculated Insight API names) using Data Cloud Setup > Data Model Explorer or the Schema API.
  5. Write ANSI SQL targeting the correct object names — test with LIMIT 10 first to validate schema and token before executing full queries.
  6. Implement pagination by checking for nextBatchId in every response and issuing follow-up GET requests within the 3-minute inter-batch window.
  7. For long-running exports (>1 hour), migrate to the Query Connect API and adjust your retry/polling logic accordingly.

Review Checklist

Salesforce-Specific Gotchas

  1. Token Endpoint Confusion — Using /services/oauth2/token directly will NOT return a Data Cloud token. You must first get a standard SF token, then exchange it at /services/a360/token. Skipping the exchange causes all Query API calls to return 401 with no clear error about which endpoint was wrong.
  2. dcInstanceUrl vs instance_url — Even after successful token exchange, using the standard instance_url for Data Cloud API calls returns 404. The dcInstanceUrl field may point to a different subdomain. Always extract and store dcInstanceUrl explicitly.
  3. Cursor Expiry Silently Fails — If you wait more than 3 minutes between pagination requests, the nextBatchId becomes invalid and the follow-up GET returns 404 or an error. There is no automatic warning. Design your pagination loop to minimize latency between requests.
  4. ANSI SQL Object Names Are Case-Sensitive — DMO API names in SQL must match exactly, including underscores and suffixes (e.g., ssot__Individual__dlm). Querying Individual without namespace prefix returns an error, not an empty result.
  5. Calculated Insights Must Be Published — A Calculated Insight is only queryable after it has been successfully published and its last batch run has completed. Querying an unpublished or failed CI returns an empty result with no error.
  6. Don't Port Query V2 Pagination Logic to Query Connect — Query V2 and Query Connect use different pagination models. Query V2 chains an opaque nextBatchId cursor with a 3-minute expiry; Query Connect pages a stored queryId by rowLimit/offset and holds results for 24 hours. Reusing a nextBatchId loop against Query Connect (or vice versa) will not paginate correctly — rewrite the paging logic when you migrate rather than assuming the batch cursor carries over.

Output Artifacts

ArtifactDescription
Data Cloud SQL queryANSI-SQL statement targeting DMO, DLO, or Calculated Insight API names
Pagination driverPython/Node loop that follows nextBatchId until no further pages remain
Token exchange helperFunction that obtains Data Cloud-scoped access token and dcInstanceUrl

Package files