Write a Simple Query
First, let's start with a simple query. Say you want to view all individual customer records. You write a query to select all fields from the ssot__Individual__dlm DMO.
The SELECT clause uses the asterisk (*) wildcard character to get all fields from ssot__Individual__dlm.
You can specify a subset of the fields to retrieve by listing the field names in the SELECT clause. In the FROM clause, you can specify the DLOs, DMOs, and calculated insights that you want to retrieve.
For example, this query retrieves only the first name, last name, the individual’s ID, and the key qualifier (KQ_Id__c).
This query output shows sample results.
ssot__FirstName__c | ssot__LastName__c | ssot__Id__c | KQ_Id__c |
|---|---|---|---|
Phyllis | Cotton | 00QHu00003W7JIAMA3 | CRM |
John | Bond | 003Hu00003SELnCIAX | CRM |
Mike | Braund | 00QHu00003W7JICMA3 | CRM |
What’s the key qualifier (KQ_Id__c)? Data ingested from different data sources can result in records having duplicate IDs. In our example query, the ssot__Id__c column could contain duplicates. The key qualifier is an additional column that, when combined with the record ID, uniquely identifies each record. Together, the key ssot__Id__c and the key qualifier KQ_Id__c form the unique, fully qualified key. To ensure the correctness of joins in Data Cloud, perform all joins on both the Id and the key qualifier columns. The fully qualified key doesn’t apply to unified DMOs, such as UnifiedIndividual_dlm. For more details, see Data Interpretation with Fully Qualified Keys in _Salesforce Help.
To retrieve a calculated insight with its measure field and dimension field, specify the calculated insight’s API namd in the FROM clause.
For example, say we have a calculated insight that computes the number of contact point addresses for each unified individual. The insight has a measure of count1__c and a dimension of ind_id__c, which is the ID of the Individual DMO. The query results for this calculated insight are similar to this output.
count1__c | ind_id__c |
|---|---|
1 | 8608b9f028a77452a4c434ec97ea96bd |
2 | 864d017d1c4b3fe454cc5b8eab6420bc |
1 | 903c9f06924bcb170bf3c896ead0656d |