--- schema_version: "1" id: f75d9dfa66beb91208f2ead629de8a1fc817237d7f17dc329f679ff0752266d8 title: "Query: Retrieve Campaign Engagement Records" collection: developer version: current locale: en-us portal_url: https://docs.salesforce.com/developer/current/en-us/data-cloud/connectapi/guides/query-use-case.md canonical_url: https://developer.salesforce.com/docs/data/connectapi/guide/query-use-case.html source_site: developer.salesforce.com content_type: documentation content_hash: f0436bc8f0c8698a6e9665c8fe642b3537fde82df36ca9e315985505d93c8679 products: - Data 360 Connect REST API - Data 360 Developer Documentation --- # Query: Retrieve Campaign Engagement Records **Business Goal:** Perform a comprehensive audit of data ingestion, verify data integrity, and analyze user activity related to customer interactions. This data examination ensures regulatory compliance and enables reliable, data-driven decisions regarding customer strategy. **Use Case:** A data specialist needs to retrieve all campaign engagement records since January 1, 2025, from the `CampaignEngagement__dll` Data Lake Object (DLO). The total number of records is expected to be very large, for example, 100,000 records. The Query Connect API is designed to make this kind of massive data retrieval manageable and efficient. First, submit the query, then check its status as it processes, and finally retrieve the full results by using pagination. For limit information, see [Data 360 Limits and Guidelines](https://help.salesforce.com/s/articleView?id=data.c360_a_limits_and_guidelines.htm). ![Sequence diagram showing the requests for querying data with the Query Connect API.](https://developer.salesforce.com/docs/media/connectapi/query-seq-diag.png '{"class": "image-md"}') ## Step 1: Submit the query request Use the [create SQL query](https://developer.salesforce.com/docs/data/connectapi/references/spec?meta=createSqlQuery) endpoint to initiate the query execution in Data 360. **Query parameters:** - `dataspace=default`: The data space where the query is to be executed. - `workloadName=connect-engagement-records`: A description of the scenario, task, or application name that the query handles. Set this value to help Salesforce Customer Support assist with debugging issues. ### Request In the body of the request, pass the `sqlParameters` array for parameterized queries. In the query, `:startDate` is a placeholder for the actual date value from the parameters. See [Data 360 SQL Syntax](https://developer.salesforce.com/docs/data/data-cloud-query-guide/references/dc-sql-reference/syntax.html) for more information on SQL statements. ``` POST https://{dne_cdpInstanceUrl}/services/data/v65.0/ssot/query-sql?dataspace=default&workloadName=connect-engagement-records ``` ``` { "sql": "select UnifiedIndividualId, ModifiedDateTime__c, EngagementType__c, CampaignName__c, Channel__c from CampaignEngagement__dll where _DataLakeModifiedDateTime__c >= :startDate orderBy ModifiedDateTime__c ASC", "sqlParameters": [ { "type": "date", "name": "startDate", "value": "2025-01-01" } ] } ``` ### Response The API call immediately returns a `queryId` to track the query's progress, metadata about the result columns, and the first chunk of data (for example, 2000 rows). The `completionStatus` is `Running` because the first chunk is ready, but `rowCount` indicates that more data is available. Query results are subject to results caching, which serves identical queries submitted within a specific timeframe much faster. Cached results have a 24-hour expiration time, ensuring that they remain available for repeated access and optimized performance for frequently run queries. ```json { "returnedRows": 2000, "status": { "chunkCount": 1, "completionStatus": "Running", "expirationTime": "2025-06-05T08:11:00Z", "progress": 0.02, "queryId": "MTAuMjIuMTQ4LjE4MTo3NDg0%2Fe1025a17-0ff7-bcb9-204c-656ec276b188", "rowCount": 100000 }, "metadata": [ { "name": "UnifiedIndividualId", "nullable": false, "type": "varchar" }, { "name": "ModifiedDateTime__c", "nullable": false, "type": "varchar" }, { "name": "EngagementType__c", "nullable": false, "type": "varchar" }, { "name": "CampaignName__c", "nullable": false, "type": "varchar" }, { "name": "Channel__c", "nullable": false, "type": "varchar" } ], "data": [ ["CUST_SYNC_001", "2025-05-01T08:00:00Z", "Email Open", "May Newsletter", "Email"], ["CUST_SYNC_002", "2025-05-01T08:05:00Z", "Website Visit", "Spring Collection", "Web"] ] } ``` ## Step 2: Check the query status Because the initial response indicated that the `rowCount` is 100,000 but only 2,000 rows were returned, the query is still running in the background. You need to periodically check its status using the [get SQL query](https://developer.salesforce.com/docs/data/connectapi/references/spec?meta=getSqlQuery) endpoint. **Query parameters:** - `dataspace=default`: The data space where the query is to be executed. - `workloadName=connect-engagement-records`: A description of the scenario, task, or application name that the query handles. Set this value to help Salesforce Customer Support assist with debugging issues. - `waitTimeMs`: This parameter allows you to perform long polling. The request blocks for up to `waitTimeMs` (for example, 10,000 ms \= 10 seconds) if the status hasn't changed or return immediately if it has. Adjust this number as needed if you encounter Apex resource limit issues. ### Request The client polls every five seconds until the query `completionStatus` is `Finished`. Pass the `queryId` obtained in the previous response as part of the URL for this status check. ``` GET https://{dne_cdpInstanceUrl}/services/data/v65.0/ssot/query-sql/MTAuMjIuMTQ4LjE4MTo3NDg0%2Fe1025a17-0ff7-bcb9-204c-656ec276b188?dataspace=default&workloadName=connect-engagement-records&waitTimeMs=5000 ``` ### Response 1 The `progress` field indicates how much of the query has been processed. ```json { "queryId": "MTAuMjIuMTQ4LjE4MTo3NDg0%2Fe1025a17-0ff7-bcb9-204c-656ec276b188", "completionStatus": "Running", "chunkCount": 5, "rowCount": 100000, "expirationTime": "2025-06-05T08:11:00Z", "progress": 0.5 } ``` ### Response 2 When `completionStatus` is `Finished` (or `progress` is 1.0), all data is ready for retrieval. ```json { "queryId": "MTAuMjIuMTQ4LjE4MTo3NDg0%2Fe1025a17-0ff7-bcb9-204c-656ec276b188", "completionStatus": "Finished", "chunkCount": 50, "rowCount": 100000, "expirationTime": "2025-06-05T08:11:00Z", "progress": 1.0 } ``` ## Step 3: Get additional rows (row-based pagination) After confirming that the initial query is complete, you fetch the remaining 98,000 rows by using the [get SQL query rows](https://developer.salesforce.com/docs/data/connectapi/references/spec?meta=getSqlQueryRows) endpoint. **Query parameters:** - `rowLimit`: The maximum number of rows to return in this chunk (for example, 2000). - `offset`: The row number at which to start retrieving the next chunk of query results. - `omitSchema=true`: This parameter, when set to `true`, tells the request _not_ to return the metadata (`metadata` array) with each subsequent chunk. Omitting the metadata reduces the amount of data returned for faster performance. ### Request Get the next 2000 rows, starting from row 2000 (since you already received the first 2000 rows). ``` GET https://{dne_cdpInstanceUrl}/services/data/v65.0/ssot/query-sql/MTAuMjIuMTQ4LjE4MTo3NDg0%2Fe1025a17-0ff7-bcb9-204c-656ec276b188/rows?rowLimit=2000&offset=2000&omitSchema=true ``` ### Response The response contains the next chunk of data. ```json { "returnedRows": 2000, "data": [ ["CUST_SYNC_003", "2025-05-01T08:10:00Z", "Email Click", "Spring Sale", "Email"] // ... (1999 more rows) ] } ``` ## Step 4: Delete a long-running query (optional) For queries that are no longer needed due to user cancellation or to cancel a long-running query, use the [cancel SQL query](https://developer.salesforce.com/docs/data/connectapi/references/spec?meta=cancelSqlQuery) endpoint.