Skip to main content
Grupos* Data 360 *Pregunta de Juan Pablo de la Fuente
Juan Pablo de la Fuente (Angry Beavers Hydraulics) ha preguntado en * Data 360 *
4 may, 15:59

Streaming Data Transform: Why does a new Source record delete an existing record in the Target DLO if it fails the WHERE clause?

Hi everyone,

 

I’ve encountered a specific behavior in

Salesforce Data Cloud Streaming Data Transforms (SDT) that isn't explicitly detailed in the official documentation regarding record persistence. I would like to understand the underlying logic or if this is the intended "Streaming SQL" behavior for Data Cloud.

 

The Scenario:

  • Source DLO (sessionEvent): Category: Engagement. Primary Key: UUID (unique for every single event).
  • Fields: cookie__c (Business Key) and type__c (Event type).
  • Streaming Data Transform Logic: SQL
SELECT 
    cookie__c AS id__c, -- Target Primary Key
    cookie__c AS cookie__c
FROM sessionEvent
WHERE type__c = 'login'

 

Target DLO (anonymousProfiles): Category: Profile. Primary Key: id__c (mapped from cookie__c).

The Observed Behavior:

  1. Event A arrives: { id__c: 'uuid-1', cookie__c: 'cookie001', type__c: 'login' }.
    • Result: A record with ID cookie001 is correctly created in the Target DLO.
  2. Event B arrives: { id__c: 'uuid-2', cookie__c: 'cookie001', type__c: 'navigation' }.
    • Result: The record cookie001 is automatically deleted from the Target DLO.

The Mystery: Even though the Source DLO has unique UUIDs as Primary Keys (meaning Event B is NOT an update of Event A but a new record), the Streaming engine seems to be performing a "Retraction".

It appears that when a new event for the same "Business Key" (cookie__c) enters the stream but fails the WHERE clause, the engine interprets this as a state change for that ID and issues a Tombstone/Delete

command to the Target DLO to maintain the "integrity" of the SQL definition. 

 

Questions:

  1. Is this behavior documented? Does the SDT engine treat the Target PK as a stateful materialized view that "retracts" records if the latest event for a business key doesn't meet the criteria?
  2. Is there a way to make the SDT "Append-only" for the target DLO without using composite keys (e.g., cookie + type) to prevent deletions?
  3. Does the engine keep an internal state of business keys even if the Source DLO is categorized as Engagement?

I've already solved this using a composite key in the target, but I want to understand the "why" behind this automatic deletion to design better data architectures in the future.

 

Has anyone else observed this "Silent Deletion" or "Retraction" logic?

1 respuesta
  1. Kundan Kumar Jha (Infosys)
    3 sept, 16:31

    Hi Juan Pablo, 

     

    Great diagnosis — and yes, this is the actual (if under-documented) mechanics of Streaming Data Transforms. 

     

    Answering your three questions: 

     

    1. Is this documented? Not explicitly with this framing, but it follows from how SDT works under the hood: it behaves like a continuously materialized view keyed on the target Primary Key, not a simple append/insert stream. The engine treats your target PK (id__c/cookie__c) as "the current state for this key, per the transform's SQL definition." When a new source event arrives for that same key and no longer satisfies the WHERE clause, the engine's correct answer to "what does the view look like now for this key?" is "nothing" — so it retracts/deletes the previously materialized row. This is standard streaming materialized-view semantics (the same pattern you'd see in Spark Structured Streaming or ksqlDB), not a bug or hidden special case. 

     

    2. Can you make it append-only without a composite key? Not with a single business-key PK feeding a filtered SELECT — the moment your target PK is purely the business key, the transform is inherently a stateful "latest matching state per key" view, and any later event for that key that fails the filter will retract it. Your composite key fix (cookie + type) is exactly right, because it changes the semantic from "current login state per cookie" to "did this cookie ever have a login event" — each (cookie, type) pair becomes its own independent, append-safe row that no other event can invalidate. 

     

    3. Does it track state even though the source is Engagement-categorized? Yes — the Engagement categorization on the source DLO governs retention/volume handling on the source side, but it doesn't change the transform engine's need to maintain per-target-key state to know whether to insert, no-op, or retract on each new source event. 

     

    So: not a "silent deletion" bug — it's the expected behavior of a stateful streaming view keyed on a business key with a filter, and composite keys are the documented-by-implication way to avoid it. 

     

    Reference:

    https://help.salesforce.com/s/articleView?id=data.c360_a_streaming_transform_considerations.htm&language=en_US&type=5

     

     

    Good instinct isolating this via the composite key — that's the standard pattern for exactly this scenario.

Escribir una respuesta...
0/9000