Study guide
11.1 Calculated Insights (CI) Architecture, SQL/Builder Syntax & Dimensions vs. Metrics
Salesforce Certified Data 360 Consultant formerly Data Cloud Consultant
Track your progress as you read
Finished sections get a check mark.
1 Chapter 1: Solution Overview & Enterprise Value
1.1 Enterprise Customer 360 & Data Cloud Business Value 1.2 Architectural Foundation: Zero-Copy, Hyperforce, Lakehouse & Data Pipeline 1.3 Platform Terminology, Core Entities & Data Lifecycle Stages 1.4 Data 360's Foundational Role in Predictive & Generative AI
2 Chapter 2: Ethics, Privacy & Governance Fundamentals
2.1 Regulatory Compliance, Data Privacy & Ethics in Customer Data 2.2 Consent Management & Preference Enforcement in Data Cloud 2.3 Data Retention, Deletion, and Privacy Right-to-be-Forgotten (RTBF)
3 Chapter 3: Security, Permissions & Tenant Architecture
3.1 Standard Permission Sets, User Roles & Functional Access 3.2 Data Spaces Architecture, Resource Allocation & Tenant Isolation
4 Chapter 4: Administration, Monitoring & Lifecycle Management
4.1 Data Kits & Package Automation for Cross-Org Deployment 4.2 Auditing, Status Monitoring, Error Logs & Data Explorer 4.3 Sandboxes, Change Management & Metadata Deployment
5 Chapter 5: Ingestion Connectors & Source Integration
5.1 Standard Bundles & Salesforce CRM Connectors (Sales/Service/Marketing) 5.2 Cloud Storage (S3, GCS, Azure Blob) & Streaming Ingestion (Web/Mobile SDK, Ingestion API) 5.3 Zero-Copy Data Federation & Snowflake/BigQuery/Databricks Sharing
6 Chapter 6: Data Lake Objects (DLO) & Data Transformations
6.1 DLO Configuration, Primary Keys, Event Time & Data Stream Categories (Profile, Engagement, Other) 6.2 Streaming & Batch Data Transforms: Formula Fields, SQL Transforms & Data Prep Recipes
7 Chapter 7: Data Model Objects (DMO) & Canonical Harmonization
7.1 Customer 360 Canonical Data Model & Subject Areas 7.2 DLO-to-DMO Mapping Patterns, Cardinality & Custom DMO Design 7.3 Data Validation, Schema Maintenance & Field Mapping Best Practices
8 Chapter 8: Identity Resolution Architecture & Match Rules
8.1 Identity Resolution Concepts, Consolidation Rate & Graph Modeling 8.2 Match Rules & Match Criteria: Exact Match vs. Fuzzy Matching (Deterministic & Probabilistic) 8.3 Multi-Party Identification, Anonymous-to-Known Transitions & Contact Point Matching
9 Chapter 9: Reconciliation Rules & Unified Profile Governance
9.1 Reconciliation Rules: Most Frequent, Source Priority & Last Updated 9.2 Unified Profile Schema (Individual & Contact Points) & Resolution Troubleshooting
10 Chapter 10: Segment Architecture & Audience Design
10.1 Segmentation Canvas, Segment Types (Standard UI & Flow) & Query Processing 10.2 Direct vs. Related Attributes, Cardinality & 1:N Filtering Traps 10.3 Segment Schedule, Refresh Frequencies & Exclusion Logic 10.4 Segment Membership Analysis, Population Drift & Sizing Validation
11 Chapter 11: Calculated & Streaming Insights
11.1 Calculated Insights (CI) Architecture, SQL/Builder Syntax & Dimensions vs. Metrics 11.2 Streaming Insights (SI) Architecture, Windowing (Tumbling/Sliding) & Real-Time Signals 11.3 CI vs. SI Architectural Decision Matrix & Performance Optimization
12 Chapter 12: Data Enhancements, Sharing & Analysis
12.1 Referencing Data 360 Data from Other Systems: Enrichments, Related Lists, Data Graphs & APIs 12.2 Reports, Dashboards & Tableau Analysis on Data 360 Data 12.3 Applying Predictive & Generative AI Tooling to Customer Scenarios
13 Chapter 13: Activation Targets & Outbound Activation
13.1 Activation Targets: Marketing Cloud, External Platforms, Cloud Storage & Data Spaces 13.2 Activation Configuration: Audience Mapping, Related Attributes & Payload Optimization 13.3 Activation Schedules, Publishing Cadence & Incremental vs. Full Refresh 13.4 Activation Troubleshooting: Error Logs, Rejection Reasons & Delivery Failures
14 Chapter 14: Real-Time Data Actions & Flow Automation
14.1 Data Action Targets & Webhook/Platform Event Architecture 14.2 Data Cloud-Triggered Flows & Real-Time Business Orchestration 14.3 End-to-End Enterprise Scenario Walkthrough: Ingestion to Closed-Loop Action
11.1 Calculated Insights (CI) Architecture, SQL/Builder Syntax & Dimensions vs. Metrics
Key Takeaways Explain This Topic AI
Calculated Insights (CIs) pre-aggregate complex multidimensional metrics across massive historical datasets in Data Cloud, materializing results into insight objects (__cio) to eliminate runtime query latency during audience segmentation and activation.
Every Calculated Insight must define at least one Dimension (grouping attribute) and at least one Metric (aggregated measure using SUM, COUNT, AVG, MIN, or MAX); queries without a GROUP BY dimension fail validation.
CIs can be built declaratively using the Visual Insights Builder for standard relationship paths or programmatically using Data Cloud ANSI SQL Builder for multi-hop joins, CASE WHEN conditionals, and complex analytical logic.
Single-dimension CIs group solely by Unified Individual ID, creating 1:1 profile attributes that appear directly on the Segment Canvas, whereas multi-dimensional CIs group by additional attributes (such as Product Category) and behave as 1:N related attributes.
Calculated Insights can be activated as direct or related attributes to external platforms (Marketing Cloud, Amazon S3, Snowflake) and surfaced natively in CRM Lightning pages and Service Console via Data Cloud Query API or FlexCards.
Last updated: September 2026
Calculated Insights (CI) Architecture, SQL/Builder Syntax & Dimensions vs. Metrics
In an enterprise Customer Data Platform, customer data is continuously ingested across diverse engagement channels—e-commerce transactions, point-of-sale logs, digital web interactions, mobile app telemetry, and customer service tickets. Over months and years, these tables accumulate millions or billions of rows. When a marketing specialist or data analyst attempts to create an audience segment such as "Customers who spent more than $2,500 across digital channels in the last 12 months with at least three purchases in the Luxury Goods category," evaluating this complex aggregation at segmentation query time across raw transactional tables would lead to prohibitive query latencies, resource contention, and potential query timeouts.
Salesforce Data Cloud addresses this fundamental challenge through Calculated Insights (CIs) . Calculated Insights enable data architects and consultants to define, schedule, and pre-aggregate complex multidimensional metrics across harmonized historical Data Model Objects (DMOs). The resulting computations are materialized into specialized insight objects ( __cio ), making high-value customer aggregations instantly queryable by the Segment Canvas, Activation engines, and downstream business intelligence tools.
Core Architecture: Dimensions vs. Metrics
At the architectural heart of every Calculated Insight is the strict separation between Dimensions and Metrics (Measures). Understanding this distinction is vital for designing performant data models and answering certification exam scenarios. ┌────────────────────────────────────────────────────────────────────────┐
- │ CALCULATED INSIGHT DEFINITION │
- │ │
- │ SELECT │
- │ UnifiedIndividual__dlm.Id__c AS CustomerId__c, ◄──┐ │
- │ ProductCategory__dlm.Name__c AS Category__c, ◄──┼─── Dimensions (GROUP BY)
- │ SUM(SalesOrder__dlm.GrandTotalAmount__c) AS TotalSpend__c,◄─┼─── Metric (Aggregation)
- │ COUNT(SalesOrder__dlm.Id__c) AS OrderCount__c ◄──┘ │
- │ FROM ... │
- │ GROUP BY CustomerId__c, Category__c │
- └────────────────────────────────────────────────────────────────────────┘
- 1. Dimensions (Grouping Attributes)
Definition : Dimensions are qualitative, categorical, temporal, or identity attributes used to segment, filter, and partition data into distinct aggregation buckets. They correspond directly to the SQL GROUP BY clause.
Mandatory Requirement : Every Calculated Insight must contain at least one dimension. A query that attempts to calculate global metrics (e.g., SELECT SUM(Amount__c) FROM SalesOrder__dlm ) without a GROUP BY dimension will fail platform validation.
Primary Key Formulation : In Data Cloud, the primary key of the materialized Calculated Insight object ( __cio ) is automatically formed by the composite combination of all defined dimensions.
Common Examples :
- Unified Customer Anchor: UnifiedIndividual__dlm.Id__c
- Source System Customer ID: Individual__dlm.Id__c
- Product Hierarchy: ProductCategory__dlm.Name__c , GoodsProduct__dlm.ProductCode__c
- Temporal Granularity: DATE_TRUNC('month', SalesOrder__dlm.OrderedDate__c)
- Geographic/Channel Attributes: SalesOrder__dlm.StoreLocationId__c , SalesOrder__dlm.SalesChannel__c
- 2. Metrics (Aggregated Calculations)
Definition : Metrics are quantitative, numerical, or countable calculations evaluated across the records belonging to each dimensional grouping bucket.
Aggregate Functions : Metrics must utilize standard aggregate mathematical functions:
SUM(expression) : Calculates total values (e.g., Lifetime Purchase Amount).
COUNT(expression) / COUNT(DISTINCT expression) : Calculates transaction frequencies or unique product views.
AVG(expression) : Calculates mean values (e.g., Average Order Value - AOV).
MIN(expression) / MAX(expression) : Identifies boundaries (e.g., First Order Date, Most Recent Order Date, Maximum Transaction Value).
Field Naming & Suffixes : Every metric must be assigned an explicit SQL alias ending in __c (e.g., LifetimeSpend__c , TotalTransactions__c ).
No Nested Aggregations : The Data Cloud SQL engine forbids nested aggregate functions. An expression such as AVG(SUM(OrderAmount__c)) is syntactically invalid.
Conditional Aggregation ( CASE WHEN ) : Metrics frequently leverage CASE WHEN logic to compute conditional indicators within a single query pass: SUM(
- CASE
- WHEN SalesOrder__dlm.OrderStatus__c = 'Completed'
- THEN SalesOrder__dlm.GrandTotalAmount__c
- ELSE 0
- END
- ) AS CompletedRevenue__c,
- COUNT(
- CASE
- WHEN SalesOrder__dlm.OrderStatus__c = 'Returned'
- THEN SalesOrder__dlm.Id__c
- ELSE NULL
- END
- ) AS ReturnedOrderCount__c
- Dimensions vs. Metrics Technical Comparison
- Architectural Attribute Dimension Metric
- Role in Query Categorizes and groups records Computes numerical summaries per group
SQL Equivalent Specified in SELECT and mandatory GROUP BY Specified in SELECT with aggregate functions ( SUM , AVG , etc.)
Data Types Text, Date, DateTime, Boolean Number, Decimal, Integer, Date/DateTime (for MIN / MAX )
Cardinality Rule Determines row volume of the output insight table Evaluated per row of the output insight table
- Minimum Required Exactly 1 or more (Mandatory) Exactly 1 or more (Mandatory)
- Role in Primary Key Combined composite forms the Insight PK Stored as payload values in the Insight row
- Building CIs: Visual Insights Builder vs. SQL Builder
Salesforce Data Cloud provides two distinct authoring interfaces for creating Calculated Insights: the declarative Visual Insights Builder and the programmatic SQL Builder .
Visual Insights Builder (Declarative No-Code)
The Visual Insights Builder is an intuitive, drag-and-drop graphical canvas designed for business analysts and marketing practitioners who need standard aggregates without writing raw SQL.
Automated Relationship Traversal : When an analyst selects a root DMO (e.g., UnifiedIndividual__dlm ), the canvas displays valid related DMOs based on pre-established relationships in the Customer 360 Data Model.
Canvas Nodes : Users configure data sources, filter criteria, join steps, group-by dimensions, and aggregation measures visually.
Best Suited For :
Standard single-object aggregations (e.g., Total Spend per Customer from SalesOrder__dlm ).
Simple 1-hop relationship aggregations.
Rapid prototyping of standard customer metrics without syntax debugging.
ANSI SQL Builder (Programmatic Code-First)
The SQL Builder gives enterprise architects and data engineers the full power of Data Cloud's ANSI SQL engine. It is required for advanced analytical modeling.
Complex Multi-Hop Joins : Enables joining across 3, 4, or more DMOs along with bridge tables (e.g., traversing from UnifiedIndividual__dlm through UnifiedLinkIndividual__dlm to SalesOrder__dlm and SalesOrderProduct__dlm ).
Subqueries & Common Table Expressions (CTEs) : Allows staging intermediate aggregates before computing final metrics.
Conditional & String Expressions : Supports complex CASE WHEN , COALESCE , DATE_TRUNC , and string formatting functions.
Direct Code Maintenance : SQL definitions can be exported, version-controlled, packaged into Data Kits, and deployed across orgs.
Joining DMOs Inside SQL CIs: Join Rules & Unified Link Mechanics
When authoring Calculated Insights in SQL, understanding how to link operational transaction data to unified customer profiles is the single most critical exam objective. In Data Cloud, transactions are ingested and mapped to standard engagement DMOs (such as SalesOrder__dlm ) using source system identifiers (such as a CRM ContactId or e-commerce CustomerId ). However, segmentation almost always runs against unified customer profiles ( UnifiedIndividual__dlm ).
To bridge this divide, Calculated Insights must traverse the Unified Link table ( UnifiedLinkIndividual__dlm ), which maps source records to their resolved unified identity.
- The Unified Individual Join Architecture ┌────────────────────────────┐ ┌────────────────────────────┐
- │ SalesOrder__dlm │ │ UnifiedLinkIndividual__dlm │
- ├────────────────────────────┤ ├────────────────────────────┤
- │ Id__c (PK) │ │ SourceRecordId__c (FK) │◄───┐
- │ SoldToPartyId__c (FK) │──────►│ UnifiedRecordId__c (FK) │ │ Join Path
- │ GrandTotalAmount__c │ └─────────────┬──────────────┘ │
- │ OrderedDate__c │ │ │
- └────────────────────────────┘ ▼ │
- ┌────────────────────────────┐ │
- │ UnifiedIndividual__dlm │ │
- ├────────────────────────────┤ │
- │ Id__c (PK) │◄───┘
- │ FirstName__c │
- │ LastName__c │
- └────────────────────────────┘
- Production ANSI SQL Pattern: Customer Lifetime Spend SELECT
- UnifiedIndividual__dlm.Id__c AS UnifiedIndividualId__c,
- SUM(SalesOrder__dlm.GrandTotalAmount__c) AS LifetimeSpend__c,
- COUNT(SalesOrder__dlm.Id__c) AS LifetimeOrderCount__c,
- AVG(SalesOrder__dlm.GrandTotalAmount__c) AS AverageOrderValue__c,
- MAX(SalesOrder__dlm.OrderedDate__c) AS LastPurchaseDate__c
- FROM
- SalesOrder__dlm
- JOIN
- UnifiedLinkIndividual__dlm
- ON SalesOrder__dlm.SoldToPartyId__c = UnifiedLinkIndividual__dlm.SourceRecordId__c
- JOIN
- UnifiedIndividual__dlm
- ON UnifiedLinkIndividual__dlm.UnifiedRecordId__c = UnifiedIndividual__dlm.Id__c
- WHERE
- SalesOrder__dlm.OrderStatus__c != 'Cancelled'
- GROUP BY
- UnifiedIndividual__dlm.Id__c
Important
Join Best Practice: Group by Unified Record ID When creating CIs intended for segmentation on the UnifiedIndividual__dlm entity, you must group by UnifiedIndividual__dlm.Id__c . If you mistakenly group by the source Individual__dlm.Id__c or SalesOrder__dlm.SoldToPartyId__c , the Segment Canvas will not recognize the insight as a direct attribute on the unified customer, forcing complex and inefficient related-attribute queries.
Multi-Dimensional vs. Single-Dimension Calculated Insights
Calculated Insights fall into two architectural topologies based on their dimensionality: Single-Dimension and Multi-Dimensional .
Single-Dimension Calculated Insights
Granularity : Grouped exclusively by a single entity key (typically UnifiedIndividual__dlm.Id__c or Account__dlm.Id__c ).
Cardinality : Exactly 1 row per unified profile (1:1 relationship with UnifiedIndividual__dlm ).
Segment Canvas Behavior : Exposed as Direct Attributes on the Unified Individual profile. A marketer can drag LifetimeSpend__c directly onto the canvas and apply a simple filter (e.g., LifetimeSpend__c >= 1000 ).
Use Cases : Overall customer lifetime value (LTV), total loyalty points accrued, customer tenure in days, total support cases opened.
Multi-Dimensional Calculated Insights
Granularity : Grouped by an entity key AND one or more additional categorical or temporal dimensions (e.g., UnifiedIndividual__dlm.Id__c + ProductCategory__dlm.Name__c ).
Cardinality : Potentially multiple rows per unified profile (1:N relationship with UnifiedIndividual__dlm ). A single customer will have one row for "Electronics", one for "Apparel", and one for "Home & Garden".
Segment Canvas Behavior : Exposed as Related Attributes . When building segment criteria, the marketer must drag the insight onto the canvas and specify paired criteria (e.g., Category__c = 'Apparel' AND CategorySpend__c >= 500 ).
Use Cases : Category-level spend affinity, quarterly spend rollups, engagement frequency by marketing channel (Email vs. SMS vs. Push).
Architectural Comparison: Single vs. Multi-Dimensional CIs
- Feature Single-Dimension CI Multi-Dimensional CI
- SQL GROUP BY GROUP BY UnifiedIndividual__dlm.Id__c GROUP BY UnifiedIndividual__dlm.Id__c, Category__c
- Row Count ≤ Total Unified Individuals ≤ (Unified Individuals × Unique Categories)
- Relationship to Profile 1:1 (Direct Profile Attribute) 1:N (Related Attribute)
Segment Canvas UX Single slider / operator (e.g., Spend > 500 ) Paired conditions container (e.g., Category = X AND Spend > 500 )
- Activation Payload Directly mapped as a profile field Mapped as a nested JSON / related attribute list
- Storage Footprint Low to moderate Moderate to high (scales with dimension cardinality)
- How CIs are Exposed Downstream
Calculated Insights are not merely analytical queries; they are active data assets integrated across the Salesforce ecosystem:
Audience Segmentation Canvas :
Materialized insight fields appear directly in the left-hand attribute palette under the "Calculated Insights" tab.
Segments execute lightning-fast lookups against the pre-aggregated insight table rather than scanning gigabytes of underlying transactional logs.
Activation to Downstream Targets :
CI metrics can be appended directly to activation payloads heading to Marketing Cloud, Google Ads, Meta, Amazon S3, or SFTP.
Example: Activating ChurnRiskScore__c and PreferredCategory__c to Marketing Cloud Journey Builder to dynamically trigger tailored win-back journeys.
CRM & Lightning Experience Enrichment :
Via the Data Cloud Query API, FlexCards, or Copy Field Enrichment, CI metrics can be projected directly onto Sales Cloud or Service Cloud Lightning record pages.
A customer service agent viewing a Contact record in Service Console can see real-time pre-computed metrics: Customer Lifetime Value, 30-Day Return Count, and Product Satisfaction Index without leaving their console.
Business Intelligence (Tableau & CRM Analytics) :
Tableau and CRM Analytics connect directly to Data Cloud Calculated Insight objects via native connectors, enabling executive dashboards without requiring separate data warehouse transformation pipelines.