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__cssot__LastName__cssot__Id__cKQ_Id__c
PhyllisCotton00QHu00003W7JIAMA3CRM
JohnBond003Hu00003SELnCIAXCRM
MikeBraund00QHu00003W7JICMA3CRM

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__cind_id__c
18608b9f028a77452a4c434ec97ea96bd
2864d017d1c4b3fe454cc5b8eab6420bc
1903c9f06924bcb170bf3c896ead0656d