Study guide
6.2 Streaming & Batch Data Transforms: Formula Fields, SQL Transforms & Data Prep Recipes
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
6.2 Streaming & Batch Data Transforms: Formula Fields, SQL Transforms & Data Prep Recipes
Key Takeaways Explain This Topic AI
Ingestion-time Formula Fields execute row-by-row during Data Stream ingestion before data lands in the DLO, making them ideal for string cleanup, normalization, date parsing, and generating composite primary keys with zero post-ingestion compute overhead.
Modifying or adding a Formula Field on an active Data Stream does NOT recalculate historical records already written to the DLO; recalculating historical data requires initiating an explicit full data stream refresh.
Batch Data Transforms (BDTs) execute scheduled Spark SQL transformations within Data Cloud's compute engine, allowing multi-table DLO joins, window functions (e.g., ROW_NUMBER), and aggregations that output directly to derived DLOs.
Streaming Data Transforms (SDTs) process events in near-real-time as micro-batches arrive on streaming DLOs, performing row-level filtering and parsing before writing to target DLOs or DMOs, but do NOT support multi-table historical joins.
Architects must choose the proper transformation tier based on the processing decision matrix: use Ingestion Formulas for row-level formatting and keys, BDTs for multi-DLO lakehouse restructuring, and Calculated Insights (CIs) for multidimensional metric cubes that power Segmentation and Activation.
Last updated: September 2026
Streaming & Batch Data Transforms: Formula Fields, SQL Transforms & Data Prep Recipes
Enterprise customer data rarely arrives in a pristine, analytics-ready state. Inbound records from transactional mainframes, legacy ERPs, mobile clickstreams, and marketing databases often contain fragmented strings, inconsistent phone formatting, unparsed date structures, and unjoined relational keys. Salesforce Data Cloud provides a multi-tier transformation architecture designed to clean, enrich, and reshape data at various points along the ingestion and modeling lifecycle.
A certified Data Cloud consultant must master when and where to apply Ingestion-Time Formula Fields , Batch Data Transforms (Spark SQL) , Streaming Data Transforms , and Data Prep Recipes , ensuring pipelines optimize both computational efficiency and platform credit consumption.
The Multi-Tier Transformation Pipeline
Transformation in Data Cloud does not follow a single monolithic ETL step. Instead, processing occurs across distinct architectural checkpoints: 1. Ingestion Boundary (Per-Row) ──► Ingestion Formula Fields (on Data Stream)
- │ (Normalizes PII, generates composite PKs)
- ▼
- 2. Lakehouse Physical Storage ──► Data Lake Objects (DLOs)
- │
- ├──► Streaming Data Transforms (Real-Time Micro-Batch)
- └──► Batch Data Transforms / Recipes (Spark SQL Multi-DLO)
- ▼
- 3. Lakehouse Staging Tier ──► Derived Data Lake Objects (Derived DLOs)
- │
- ▼ (Semantic Mapping Canvas)
- 4. Semantic Data Model Layer ──► Data Model Objects (DMOs)
- │
- ▼ (Multidimensional Metrics)
- 5. Analytical Insights Layer ──► Calculated Insights (CIs)
- │
- ▼
- 6. Audience Execution Layer ──► Segmentation & Activation Targets
- Ingestion-Time Transformations: Formula Fields on Data Streams
Ingestion-Time Formula Fields are evaluated inline, record-by-record, as raw data streams into Data Cloud, before the records are committed to the physical Data Lake Object (DLO). Because formula fields evaluate during the ingest pipeline, they incur zero additional post-ingestion query overhead or background compute job scheduling.
Core Functional Capabilities
Data Cloud provides a rich library of transformation functions categorized into four primary domains:
1. String Manipulation & Hygiene
CONCAT(text1, text2, ...) : Merges multiple string fields. Indispensable for composite primary key generation (e.g., CONCAT(source.Org_Code__c, "_", source.Account_Number__c) ).
TRIM(text) : Removes leading and trailing whitespace from incoming strings.
UPPER(text) / LOWER(text) : Standardizes casing across text identifiers and email fields to ensure reliable deterministic matching.
SUBSTITUTE(text, old_text, new_text) : Replaces specific character sequences, frequently used to strip non-numeric characters from phone numbers.
LEFT(text, num) / RIGHT(text, num) / MID(text, start, length) : Extracts substrings from fixed-width source codes.
2. Date and Time Parsing
DATE(year, month, day) : Constructs a valid calendar Date from separate numeric components.
DATETIME(year, month, day, hour, minute, second) : Constructs a fully qualified DateTime timestamp.
EXTRACT(part, datetime) : Extracts specific components (e.g., year, month, day of week) from an existing timestamp.
3. Mathematical & Numerical Operations
NUMBER(text) : Converts string representations of numeric values into true computational numbers.
COALESCE(val1, val2, ...) : Evaluates arguments in order and returns the first non-null value, preventing null-pointer exceptions in calculations.
Basic arithmetic operators ( + , - , * , / ) for unit price extensions or tax calculations.
4. Conditional CASE and IF Logic
IF(logical_test, value_if_true, value_if_false) : Evaluates a boolean expression and branches accordingly.
CASE(expression, val1, result1, val2, result2, default_result) : Evaluates multi-way conditional branching.
Real-World Formula Field Implementation Patterns /* Pattern 1: Generating a Composite Primary Key with Source System Isolation */
CONCAT(source.DataSource__c, "_", source.Region_Code__c, "_", source.Customer_ID__c)
- /* Pattern 2: Normalizing Phone Numbers (Stripping formatting artifacts) */
- SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(source.Raw_Phone__c, "(", ""), ")", ""), "-", ""), " ", "")
- /* Pattern 3: Normalizing Email for Exact Match Identity Resolution */
- LOWER(TRIM(source.Email_Address__c))
- /* Pattern 4: Conditional Loyalty Tier Classification via CASE */
- CASE(
- source.Lifetime_Points__c >= 100000, "Platinum",
- source.Lifetime_Points__c >= 50000, "Gold",
- source.Lifetime_Points__c >= 10000, "Silver",
- "Bronze"
- )
- Formula Limitations vs. Standard Salesforce Core Formulas
Consultants frequently assume that Data Cloud formula fields support the full syntax of Salesforce Core CRM formula fields. This is a common exam trap. Key differences include:
No Cross-Object Relationship Traversal : Data Cloud formulas CANNOT traverse object relationships (e.g., Contact.Account.BillingCity is strictly impossible). A formula can only access fields present within that specific row of the incoming data stream.
No CRM Runtime Functions : Functions such as PRIORVALUE() , ISCHANGED() , VLOOKUP() , and REGEX() are not supported in Data Cloud stream formulas.
No Global Variable Access : System variables like $User , $Profile , or custom settings cannot be referenced.
Batch Data Transforms (BDTs): SQL-Based Lakehouse Transformations
While formula fields handle single-row hygiene during ingestion, enterprise data engineering frequently requires combining multiple tables, restructuring deeply nested hierarchies, or performing windowed ranking across historical data. For these complex requirements, Data Cloud provides Batch Data Transforms (BDTs) .
Architecture and Execution Engine
Batch Data Transforms run within Data Cloud's high-performance Apache Spark SQL compute layer. They execute on a scheduled batch frequency (e.g., daily, hourly, or upon manual trigger) and operate on existing Data Lake Objects.
Input : One or more Data Lake Objects (DLOs).
Compute : Distributed Spark SQL executing joins, unions, aggregations, and windowing.
Output : Writes the resulting transformed dataset to a new Derived Data Lake Object (Derived DLO) .
Key SQL Capabilities in BDTs
BDTs support standard ANSI Spark SQL syntax, unlocking capabilities impossible in ingestion formulas:
Multi-Table Joins : Execute INNER JOIN , LEFT OUTER JOIN , RIGHT OUTER JOIN , and FULL OUTER JOIN across disparate DLOs.
Window Functions : Execute analytical windowing, such as ROW_NUMBER() OVER (PARTITION BY Customer_ID ORDER BY Transaction_Date DESC) to isolate a customer's most recent order.
Aggregations & Grouping : Perform GROUP BY , SUM , AVG , COUNT , and HAVING operations across large historical datasets.
Common Table Expressions (CTEs) : Structure modular queries using WITH cte_name AS (...) blocks.
Practical BDT Example: Denormalizing Order Headers and Line Items
Consider a retail scenario where Order Header data ( Order_Header_DLO ) and Order Line Item data ( Order_Line_DLO ) must be joined, enriched with store metadata ( Store_DLO ), and aggregated into an Order_Summary_DLO before mapping to the canonical SalesOrder DMO: WITH RankedItems AS (
- SELECT
- header.Order_ID__c AS OrderID,
- header.Customer_ID__c AS CustomerID,
- header.Order_Date__c AS OrderDate,
- store.Store_Name__c AS StoreName,
- lines.Product_SKU__c AS TopItemSKU,
- lines.Item_Price__c AS TopItemPrice,
- ROW_NUMBER() OVER (
- PARTITION BY header.Order_ID__c
- ORDER BY lines.Item_Price__c DESC
- ) as ItemRank
- FROM Order_Header_DLO__dll header
- LEFT JOIN Order_Line_DLO__dll lines
- ON header.Order_ID__c = lines.Order_ID__c
- LEFT JOIN Store_DLO__dll store
- ON header.Store_ID__c = store.Store_ID__c
- )
- SELECT
- OrderID,
- CustomerID,
- OrderDate,
- StoreName,
- TopItemSKU,
- TopItemPrice
- FROM RankedItems
- WHERE ItemRank = 1
- Streaming Data Transforms (SDTs): Real-Time Event Processing
For event-driven architectures that demand sub-minute responsiveness (e.g., real-time location triggers, abandoned cart notifications, IoT telemetry warnings), Batch Data Transforms are too slow. Data Cloud solves this via Streaming Data Transforms (SDTs) .
Mechanics and Latency
Streaming Data Transforms operate continuously on incoming streaming data streams (such as those fed by the Data Cloud Web/Mobile SDK, Ingestion API, or Amazon Kinesis / Apache Kafka connectors). As micro-batches of event data enter the pipeline, the SDT engine evaluates rules and writes the transformed output directly to a target DLO or canonical DMO with near-zero latency (under 2 minutes).
Key Architectural Constraints of SDTs
Because Streaming Data Transforms must maintain ultra-low latency across massive data velocities, they have strict architectural boundaries:
Single Streaming Source Only : An SDT can only read from a single streaming Data Lake Object. It does NOT support multi-table joins against large historical or batch DLOs.
Stateless / Micro-Batch Operations : Designed for row-level parsing (e.g., parsing a raw JSON payload string into discrete structured columns), event type classification, string sanitation, and conditional filtering (e.g., discarding system heartbeat pings).
Target Constraints : Can output to a target DLO or directly to a Data Model Object (DMO).
Data Prep Recipes vs. Batch SQL Transforms
Data Cloud provides two distinct modalities for building batch transformations: Data Prep Recipes and Batch Data Transforms (SQL Transforms) . Both execute on the underlying Spark compute infrastructure, but they cater to different practitioner personas and complexity profiles.
- Architectural Attribute Data Prep Recipes Batch Data Transforms (SQL Transforms)
- Primary User Persona Business Analysts, Low-Code Admins Data Engineers, SQL Developers
- Interface Modality Declarative visual node-graph canvas (drag-and-drop) Code-centric SQL script editor
Transformation Nodes Join, Append, Aggregate, Filter, Bucket, Flatten ANSI Spark SQL ( SELECT , JOIN , GROUP BY , OVER )
Version Control & CI/CD Metadata XML export via SFDX / DevOps Center Native SQL query text, easily versioned in Git repos
Complex Windowing Support Basic ranking and lagging via UI formulas Full native windowing ( ROW_NUMBER , DENSE_RANK , LEAD , LAG )
- Output Target Derived Data Lake Object (DLO) Derived Data Lake Object (DLO)
- Execution Engine Spark-based data prep pipeline Spark SQL distributed compute engine
- Performance Best Practices & The Transformation Decision Matrix
A paramount requirement on the Data Cloud Consultant exam is determining which transformation layer to deploy for a given business requirement. Utilizing the wrong tool causes bloated storage, inflated processing costs, pipeline failures, or stale segmentation data.
The Transformation Architecture Decision Matrix
Business Requirement Recommended Transformation Layer Architectural Rationale
Standardize email casing ( LOWER(TRIM()) ) and generate composite Primary Keys Ingestion Formula Field Evaluated inline at ingestion; incurs zero background compute scheduling or post-ingestion processing cost.
Real-time parsing of incoming mobile JSON clickstream payloads Streaming Data Transform Near-real-time micro-batch processing; immediately cleanses and structures event data for real-time activation.
Join Order Headers with Line Items and Store Lookups across multiple DLOs Batch Data Transform (SQL) Multi-table relational joins across large lakehouse datasets require distributed Spark SQL batch execution.
Calculate customer 30-day rolling spend, average order value (AOV), or lifetime value Calculated Insight (CI) Never use BDTs for dynamic customer metrics! Calculated Insights build optimized multidimensional aggregate cubes on DMOs, natively accessible in Segmentation and Activation.
Visual column bucketing and conditional filtering by non-technical analysts Data Prep Recipe Declarative visual canvas allows low-code operators to build derived staging DLOs without writing SQL. ┌────────────────────────────────────────────────────────────────────────────┐
- │ TRANSFORMATION DECISION HEURISTIC │
- ├───────────────────────────────────┬────────────────────────────────────────┤
- │ Is it single-row formatting/PK? │ ──► Ingestion Formula Field │
- │ Is it real-time stream filtering? │ ──► Streaming Data Transform │
- │ Does it join multiple DLO tables? │ ──► Batch Data Transform / Recipe │
- │ Is it an analytical metric/KPI? │ ──► Calculated Insight (on DMOs) │
- └───────────────────────────────────┴────────────────────────────────────────┘
- Critical Exam Traps & Consultant Pitfalls
Warning
The "Retroactive Formula" Trap An active Data Stream has been ingesting customer records for six months, accumulating 10 million rows in its DLO. The client realizes that the source phone numbers contain formatting dashes and spaces. The consultant adds an ingestion formula field: SUBSTITUTE(SUBSTITUTE(Phone, "-", ""), " ", "") to the data stream and deploys it.
The Reality : The formula field evaluates ONLY on incoming new or updated records arriving after deployment. The 10 million historical records in the DLO remain completely un-transformed!
Consultant Mandate : To recalculate or populate an ingestion formula across historical records, the consultant must initiate an explicit Full Refresh or re-ingest the historical dataset.
Caution
The "Metric in a BDT" Anti-Pattern A team attempts to calculate a customer's "Total Lifetime Spend" by writing a Batch Data Transform that groups order rows by Customer_ID , sums Order_Amount , and writes to a derived DLO mapped to a custom DMO.
Why this fails the exam : BDTs output static lakehouse tables that require custom DMO modeling and scheduled batch re-runs. Data Cloud provides Calculated Insights (CIs) specifically for multidimensional metric aggregations. Calculated Insights integrate natively with the Segment Canvas, update incrementally, and do not consume unnecessary lakehouse storage partitions.