Loading
About Salesforce Data 360
Table of Contents
Filter by (0)Add
Select Filters

          No results
          No results
          Here are some search tips

          Check the spelling of your keywords.
          Use more general search terms.
          Select fewer filters to broaden your search.

            Search all of Salesforce Help
            Search all of Salesforce Help
            Join Data in a Streaming Transform

            You are here:

            1. Salesforce Help
            2. Docs
            3. Data 360

            Join Data in a Streaming Transform

            Joins enable data augmentation by combining data from two separate streams based on a primary key. In streaming data transformations, `INNER JOIN` and `LEFT JOIN` clauses in SQL statements are used to combine data from two distinct streams. A join expression consists of two objects: one identifies the object that triggers the streaming transform on update, and the other specifies the object used to evaluate the join.

            Required Editions

            Available in: All Editions supported by Data 360. See Data 360 edition availability.
            User Permissions Needed
            To edit a streaming transform

            Permission set:

            • Data Cloud Architect
            • The join condition must include the ON clause. The ON clause has the form column = column
            • Each side of the ON clause can only reference one column from one side of the join, and the other side references the other side of the join.
            • Columns used in the ON clause must have the same data type.
            • Multiple joins can be combined using additional INNER JOIN or LEFT JOIN clauses. Up to 5 objects can be joined in one SQL statement.
            • Any object that can be used as a source object in a streaming transform can be used in a join.
            • The event time field column must be used for non-streaming ‘engagement’ inputs. All other joins must include a primary key from either object in the join.
            1. Create a target DLO or DMO for the data of the joined records.
            2. Create a new streaming transform by clicking New on the Data Transforms tab.
            3. On the Target Data Lake Object or Target Data Model Object page, select the DLO or DMO you created.
            4. In the Expression field, enter a SQL statement to define the join.
              • Use INNER JOIN or LEFT JOIN to specify the tables to join.
              • Use ON to specify the predicate that determines which records from each table should be matched and included in the final result set.
              • Specify the columns that you want to include in the target DLO.
            5. Click Next.
            6. From the Review section, you can select a streaming source object if multiple streaming sources are available.
              When new records are added to the chosen streaming source object, changes in the streaming transform source will cause the transform to run.
            7. Click Save.
            Example
            Example

            This example demonstrates an inner join of two tables: a sessions table and a customer table. The sessions table logs every customer engagement event, recording the customer's ID and the timestamp of their page access. The customer table stores customer information such as age, gender, and ID. Joining this data allows for analysis such as determining the number of customers from a specific state who visited a page on a given date.

            Target DLO

            Create a new DLO by cloning the existing DLO for the sessions table. Include columns that are required for the join from the customers table.

            Columns for the join from the customers table.

            SQL Expression

            SELECT 
sessions__dll.page_visited__c as page_visited__c,
sessions__dll.sesson_id__c as session_id__c,
sessions__dll.timestamp__c as timestamp__c,
sessions__dll.user_id__c as user_id__c,
customers_dll.age__c as age__c,
ustomers_dll.state__c as state__c,
customers_dll.customer_name__c as customer_name__c
FROM session__dll INNER JOIN customer__dll
ON customers__dll.customer_id__c = sessions__dll.user_id__c
            SQL Description
            FROM session__dll INNER JOIN customer__dll The tables to join.
            ON customers__dll.customer_id__c = sessions__dll.user_id__c The condition from which the records are matched.

            page_visited

            session_id

            timestamp

            age

            state

            customer_name

            		 The condition from which the records are matched.

            Results

            If sessions__dll is selected as the stream source object when saving the data stream, the transform is only triggered when new records are added to the sessions__dll streaming object. New customer__dll records don't trigger the streaming transform.

             
            Loading
            Salesforce Help | Article