Big Objects Implementation Guide

Big Objects
Async SOQL
View Big Object Data in Reports and Dashboards
Running Async SOQL Queries
Async SOQL Use Cases
Supported SOQL Commands
API End-of-Life
PDF

Newer Version Available

This content describes an older version of this product. View Latest

Async SOQL Use Cases

Understand some of the common Async SOQL use cases.

Async SOQL is scheduled for retirement in all Salesforce orgs as of Spring ’23.

Note

Customer 360 Degree and Filtering

In this use case, administrators load various customer engagement data from external sources into Salesforce big objects and then process the data to enrich customer profiles in Salesforce. The goal is to store customer transactions and interactions, such as point-of-sale data, orders, and line items in big objects and then process and correlate that data with your core CRM data. Anchoring customer transactions and interactions with core CRM data provides a richer 360-degree view that translates into an enhanced customer experience.

The following example analyzes the customer data stored in the Rider record of a car-sharing service. The source big object, Rider_Record_b, has a lookup relationship with the Contact object, allowing for an enriched view of the contact’s riding history. You can see that the query includes Rider__r.FirstName, Rider__r.LastName, Rider__r.Email as part of the SELECT clause. This example demonstrates the ability to join big object data (Rider_Record__b) with Contact data (FirstName, LastName, Email) in a single Async SOQL query.

Example URI
1https://yourInstance—api.salesforce.com/services/data/v38.0/async-queries/
Example POST request body
1{
2   "query": "SELECT End_Location_Lat__c, End_Location_Lon__c, End_Time__c,
3                    Start_Location_Lat__c, Start_Location_Lon__c, Start_Time__c,
4                    Car_Type__c, Rider__r.FirstName, Rider__r.LastName, 
5                    Rider__r.Email
6             FROM Rider_Record__b WHERE Star_Rating__c = '5'",
7                       
8   "targetObject": "Rider_Reduced__b",    
9        
10   "targetFieldMap": {"End_Location_Lat__c":"End_Lat__c", 
11                      "End_Location_Lon__c":"End_Long__c",        
12                      "Start_Location_Lat__c": "Start_Lat__c", 
13                      "Start_Location_Lon__c": "Start_Long__c", 
14                      "End_Time__c": "End_Time__c", 
15                      "Start_Time__c": "Start_Time__c",       
16                      "Car_Type__c": "Car_Type__c",        
17                      "Rider__r.FirstName": "First_Name__c",        
18                      "Rider__r.LastName": "Last_Name__c",        
19                      "Rider__r.Email": "Rider_Email__c"   
20                     }                         
21}
Example POST response body
1{
2   "jobId": "08PB000000000NA",
3                                            
4   "message": "",
5                                            
6   "query": "SELECT End_Location_Lat__c, End_Location_Lon__c, End_Time__c,
7                    Start_Location_Lat__c, Start_Location_Lon__c, Start_Time__c,
8                    Car_Type__c, Rider__r.FirstName, Rider__r.LastName, 
9                    Rider__r.Email
10             FROM Rider_Record__b WHERE Star_Rating__c = '5'",  
11                                           
12   "status": "New",  
13                                            
14   "targetFieldMap": {"End_Location_Lat__c":"End_Lat__c", 
15                      "End_Location_Lon__c":"End_Long__c",        
16                      "Start_Location_Lat__c": "Start_Lat__c", 
17                      "Start_Location_Lon__c": "Start_Long__c", 
18                      "End_Time__c": "End_Time__c", 
19                      "Start_Time__c": "Start_Time__c",       
20                      "Car_Type__c": "Car_Type__c",        
21                      "Rider__r.FirstName": "First_Name__c",        
22                      "Rider__r.LastName": "Last_Name__c",        
23                      "Rider__r.Email": "Rider_Email__c"   
24                     },  
25                                                              
26    "targetObject": "Rider_Reduced__b"       
27}

Field Audit Trail

With Field Audit Trail, you can define a policy to retain archived field history data. This feature helps you comply with industry regulations related to audit capability and data retention.

You define a Field Audit Trail policy using the HistoryRetentionPolicy object for each object you want to archive. The field history data for that object is then moved from the History related list into the FieldHistoryArchive object at periodic intervals, as specified by the policy. For more information, see the Field Audit Trail Implementation Guide.

You can use Async SOQL to query archived fields stored in the FieldHistoryArchive object. You can use the WHERE clause to filter the query by specifying comparison expressions for the FieldHistoryType, ParentId, and CreatedDate fields.

If platform encryption is enabled on the org, then AsyncSOQL on FieldHistoryArchive is not supported.

Note

This example queries archived accounts created within the last month.

Example URI
1https://yourInstance.salesforce.com/services/data/v38.0/async-queries/
Example POST request body
1{      
2   "query": "SELECT ParentId, FieldHistoryType, Field, Id, NewValue, OldValue 
3             FROM FieldHistoryArchive WHERE FieldHistoryType = ‘Account’  
4             AND CreatedDate > LAST_MONTH”,
5                                            
6   "targetObject": "ArchivedAccounts__b",
7                                            
8   "targetFieldMap": {"ParentId": "ParentId__c",
9                      "FieldHistoryType": "FieldHistoryType__c",
10                      "Field": "Field__c",
11                      "Id": "Id__c",
12                      "NewValue": "NewValue__c",
13                      "OldValue": "OldValue__c"      
14                     }
15}
Example POST response body
1{
2              "jobId": "07PB000000006PN",
3              "message": "",
4              "query": "SELECT ParentId, FieldHistoryType, Field, Id, NewValue, OldValue
5              FROM FieldHistoryArchive WHERE FieldHistoryType = ‘Account’ AND CreatedDate > LAST_MONTH”,
6              "status": "New",
7              "targetObject": "ArchivedAccounts__b",
8              "targetFieldMap": {"ParentId": "ParentId__c",
9              "targetObject": "Rider_Reduced__b" }
10              }

All number fields returned from a SOQL query of archived objects are in standard notation, not scientific notation, as in the number fields in the entity history of standard objects.

Note

Real-Time Event Monitoring

Real-Time Event Monitoring enables you to track who is accessing confidential and sensitive data in your Salesforce org. You can view information about individual events or track trends in events to swiftly identify unusual behavior and safeguard your company’s data. These features are useful for compliance with regulatory and audit requirements.

With Real-Time Events, you can monitor data accessed through API calls, report executions, and list views. The corresponding event objects are called ApiEvent, ReportEvent, and ListViewEvent. Querying these events covers many common scenarios because more than 50% of SOQL queries occur using the SOAP, REST, or Bulk APIs. Key information about each query—such as the username, user ID, rows processed, queried entities, and source IP address—is stored in the event objects. You can then run SOQL queries on the event objects to find out details of user activity in your org.

For more information, see Real-Time Event Monitoring.

Async SOQL is scheduled for retirement in all Salesforce orgs as of Spring ’23.

Note

Let’s say you’ve created a custom object called Patent__c that contains sensitive patent information. You want to know when users query this object using any API. Use the following Async SOQL query on the ApiEvent object to determine when Patent__c was last accessed, who accessed it, and what part of it was accessed. The WHERE clause uses the QueriedEntities field to narrow the results to just API queries of the Patent__c object.
Example URI
1https://yourInstance.salesforce.com/services/data/v48.0/async-queries/
Example POST request body
1{
2  "query": "SELECT EventDate, EventIdentifier, QueriedEntities, SourceIp, Username, UserAgent FROM ApiEvent 
3            WHERE QueriedEntities LIKE '%Patent__c%'",
4  "targetObject": "ApiTarget__c",
5  "targetFieldMap": {
6    "EventDate": "EventDate__c",
7    "EventIdentifier": "EventIdentifier__c",
8    "QueriedEntities": "QueriedEntities__c",
9    "SourceIp": "IPAddress__c",
10    "Username": "User__c",
11    "UserAgent": "UserAgent__c"
12  }
13}
Example POST response body
1{
2  "jobId" : "08PB00000066JRfMAM",
3  "message" : "",
4  "operation" : "INSERT",
5  "query" : "SELECT EventDate, EventIdentifier, QueriedEntities, SourceIp, Username, UserAgent FROM ApiEvent 
6             WHERE QueriedEntities LIKE '%Patent__c%'",
7  "status" : "Complete",
8  "targetExternalIdField" : "",
9  "targetFieldMap" : {
10    "EventDate" : "EventDate__c",
11    "SourceIp" : "IPAddress__c",
12    "EventIdentifier" : "EventIdentifier__c",
13    "QueriedEntities" : "QueriedEntities__c",
14    "Username" : "User__c",
15    "UserAgent" : "UserAgent__c"
16  },
17  "targetObject" : "ApiTarget__c",
18  "targetValueMap" : { }
19}

All number fields returned from a SOQL query of archived objects are in standard notation, not scientific notation, as in the number fields in the entity history of standard objects.

Note

If you ask this question on a repeated basis for audit purposes, you can automate the query using a cURL script.
1curl -H "Content-Type: application/json" -X POST -d
2'{"query": "SELECT EventDate, EventIdentifier, QueriedEntities, SourceIp, Username, UserAgent FROM ApiEvent WHERE QueriedEntities LIKE '%Patent__c%'",
3  "targetObject": "ApiTarget__c", 
4  "targetFieldMap": {"EventDate": "EventDate__c","EventIdentifier": "EventIdentifier__c","QueriedEntities": "QueriedEntities__c","SourceIp": "IPAddress__c","Username": "User__c","UserAgent": "UserAgent__c"}}' 
5  "https://yourInstance.salesforce.com/services/data/v48.0/async-queries/" -H 
6  "Authorization: Bearer 00D30000000V88A!ARYAQCZOCeABy29c3dNxRVtv433znH15gLWhLOUv7DVu.
7 uAGFhW9WMtGXCul6q.4xVQymfh4Cjxw4APbazT8bnIfxlRvUjDg"

Another event monitoring use case is to identify all users who accessed a sensitive field, such as Social Security Number or Email. For example, you can use the following Async SOQL query to determine the users who saw social security numbers.

Example URI
1https://yourInstance.salesforce.com/services/data/v48.0/async-queries/
Example POST request body
1{        
2   "query": "SELECT Query, Username, EventDate, SourceIp FROM ApiEvent 
3             WHERE Query LIKE '%SSN__c%'",
4   "targetObject": "QueryEvents__c",
5   "targetFieldMap": {
6      "Query":"QueryString__c", 
7      "Username":"User__c", 
8      "EventDate":"EventDate__c", 
9      "SourceIp" : "IPAddress__c"
10   }
11}
Example POST response body
1{
2   "jobId": "08PB000000001RS",
3   "message": "",
4   "query": "SELECT Query, Username, EventDate, SourceIp FROM ApiEvent 
5             WHERE Query LIKE '%SSN__c%'",
6   "status": "Complete",
7   "targetFieldMap": {"Query":"QueryString__c", "Username":"User__c", 
8                     "EventDate":"EventDate__c", "SourceIp" : "IPAddress__c"
9                     },
10   "targetObject": "QueryEvents__c"
11}