6.2 Streaming & Batch Data Transforms: Formula Fields, SQL Transforms & Data Prep Recipes

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:

1. Multi-Table Joins: Execute INNER JOIN, LEFT OUTER JOIN, RIGHT OUTER JOIN, and FULL OUTER JOIN across disparate DLOs.
2. 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.
3. Aggregations & Grouping: Perform GROUP BY, SUM, AVG, COUNT, and HAVING operations across large historical datasets.
4. 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.

---

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

┌────────────────────────────────────────────────────────────────────────────┐
│                     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.