Create Streaming Data Transforms
Use a streaming data transform to clean ingested data. The source and target data lake objects (DLO) must be different.
Required Editions
| Available in: Lightning Experience in Enterprise and Unlimited Editions that have Consumer Goods Cloud enabled |
-
On the Data Transforms tab, click New.
If your org already used the maximum number of transforms, the New button isn’t available.
- To create a data transform object from an installed data kit, select Choose From Data Kits.
- Select the data transform object from the CG Data Kit, and click Next.
- Enter the data transform name and select the target data lake object as per the table.
- In Expression, enter an SQL query to specify the records to be processed by the streaming data transform and to define how to map the transformed output to the target DLO.
-
To verify your SQL statement’s syntax, click Check Syntax.
If a data transform contains syntax errors or validation errors, such as incorrect API names for the source DLO and target DLOs, you can’t save it. These errors appear on the left side.
-
Save the transform.
Streaming data transforms are activated immediately when you save them. You can’t change a streaming data transform after you save it. To change a streaming data transform, you must delete it and then create another one.
-
Similarly, create the other data lake streaming data transforms.
Retail Execution Base
Data Transform Name Description Target DLO Query Order Billing Address Data Transform Transform Order's Billing Address into Order_Billing_Address_dlo DLO Order_Billing_Address_dlo SELECT Order__dll.Billing_Address_Id__c as Address_Id__c, Order__dll.BillingCity__c as BillingCity__c, Order__dll.BillingCountry__c as BillingCountry__c, Order__dll.BillingLatitude__c as BillingLatitude__c, Order__dll.BillingLongitude__c as BillingLongitude__c, Order__dll.BillingState__c as BillingState__c, Order__dll.BillingStreet__c as BillingStreet__c, Order__dll.BillingPostalCode__c as BillingPostalCode__c FROM Order__dll Order Shipping Address Data Transform Transform Order's Shipping Address into Order_Shipping_Address_dlo DLO Order_Shipping_Address_dlo SELECT Order__dll.Id__c as Address_Id__c, Order__dll.ShippingCity__c as ShippingCity__c, Order__dll.ShippingCountry__c as ShippingCountry__c, Order__dll.ShippingLatitude__c as ShippingLatitude__c, Order__dll.ShippingLongitude__c as ShippingLongitude__c, Order__dll.ShippingState__c as ShippingState__c, Order__dll.ShippingStreet__c as ShippingStreet__c, Order__dll.ShippingPostalCode__c as ShippingPostalCode__c FROM Order__dll Account Billing Address Data Transform Transform Account's Billing Address into Account_Billing_Address_dlo DLO Account_Billing_Address_dlo SELECT Account__dll.Billing_Address_Id__c as Address_Id__c, Account__dll.BillingCity__c as BillingCity__c, Account__dll.BillingCountry__c as BillingCountry__c, Account__dll.BillingLatitude__c as BillingLatitude__c, Account__dll.BillingLongitude__c as BillingLongitude__c, Account__dll.BillingState__c as BillingState__c, Account__dll.BillingStreet__c as BillingStreet__c, Account__dll.BillingPostalCode__c as BillingPostalCode__c FROM Account__dll Account Shipping Address Data Transform Transform Account's Shipping Address into Account_Shipping_Address_dlo DLO Account_Shipping_Address_dlo SELECT Account__dll.Shipping_Address_Id__c as Address_Id__c,Account__dll.ShippingCity__c as ShippingCity__c,Account__dll.ShippingCountry__c as ShippingCountry__c,Account__dll.ShippingLatitude__c as ShippingLatitude__c,Account__dll.ShippingLongitude__c as ShippingLongitude__c,Account__dll.ShippingState__c as ShippingState__c,Account__dll.ShippingStreet__c as ShippingStreet__c,Account__dll.ShippingPostalCode__c as ShippingPostalCode__c FROM Account__dll Retail Execution Enhanced
Data Transform Name Description Target DLO Query Order Billing Address Data Transform Transform Order's Billing Address into Order_Billing_Address_dlo DLO Order_Billing_Address_dlo SELECT Order__dll.Billing_Address_Id__c as Address_Id__c, Order__dll.BillingCity__c as BillingCity__c, Order__dll.BillingCountry__c as BillingCountry__c, Order__dll.BillingLatitude__c as BillingLatitude__c, Order__dll.BillingLongitude__c as BillingLongitude__c, Order__dll.BillingState__c as BillingState__c, Order__dll.BillingStreet__c as BillingStreet__c, Order__dll.BillingPostalCode__c as BillingPostalCode__c FROM Order__dll Order Shipping Address Data Transform Transform Order's Shipping Address into Order_Shipping_Address_dlo DLO Order_Shipping_Address_dlo SELECT Order__dll.Id__c as Address_Id__c, Order__dll.ShippingCity__c as ShippingCity__c, Order__dll.ShippingCountry__c as ShippingCountry__c, Order__dll.ShippingLatitude__c as ShippingLatitude__c, Order__dll.ShippingLongitude__c as ShippingLongitude__c, Order__dll.ShippingState__c as ShippingState__c, Order__dll.ShippingStreet__c as ShippingStreet__c, Order__dll.ShippingPostalCode__c as ShippingPostalCode__c FROM Order__dll Account Billing Address Data Transform Transform Account's Billing Address into Account_Billing_Address_dlo DLO Account_Billing_Address_dlo SELECT Account__dll.Billing_Address_Id__c as Address_Id__c, Account__dll.BillingCity__c as BillingCity__c, Account__dll.BillingCountry__c as BillingCountry__c, Account__dll.BillingLatitude__c as BillingLatitude__c, Account__dll.BillingLongitude__c as BillingLongitude__c, Account__dll.BillingState__c as BillingState__c, Account__dll.BillingStreet__c as BillingStreet__c, Account__dll.BillingPostalCode__c as BillingPostalCode__c FROM Account__dll Account Shipping Address Data Transform Transform Account's Shipping Address into Account_Shipping_Address_dlo DLO Account_Shipping_Address_dlo SELECT Account__dll.Shipping_Address_Id__c as Address_Id__c,Account__dll.ShippingCity__c as ShippingCity__c,Account__dll.ShippingCountry__c as ShippingCountry__c,Account__dll.ShippingLatitude__c as ShippingLatitude__c,Account__dll.ShippingLongitude__c as ShippingLongitude__c,Account__dll.ShippingState__c as ShippingState__c,Account__dll.ShippingStreet__c as ShippingStreet__c,Account__dll.ShippingPostalCode__c as ShippingPostalCode__c FROM Account__dll Bill To Role Data Transform Transform Account Extension's Bill To Role into Bill_To_Role_dlo DLO Bill_To_Role_dlo SELECT cgcloud_Account_Extension_c__dll.Bill_To_Role_Id__c as Account_Role_Id__c, cgcloud_Account_Extension_c__dll.cgcloud_Account_c__c as Account__c, "Bill To Role" as Account_Role_Type__c, cgcloud_Account_Extension_c__dll.cgcloud_Bill_To_Role_Valid_From_c__c as Effective_Start_Date__c, cgcloud_Account_Extension_c__dll.cgcloud_Bill_To_Role_Valid_Thru_c__c as Effective_End_Date__c FROM cgcloud_Account_Extension_c__dll Broker Role Data Transform Transform Account Extension's Broker Role into Broker_Role_dlo DLO Broker_Role_dlo SELECT cgcloud_Account_Extension_c__dll.Broker_Role_Id__c as Account_Role_Id__c, cgcloud_Account_Extension_c__dll.cgcloud_Account_c__c as Account__c, "Broker Role" as Account_Role_Type__c, cgcloud_Account_Extension_c__dll.cgcloud_Broker_Role_Valid_From_c__c as Effective_Start_Date__c, cgcloud_Account_Extension_c__dll.cgcloud_Broker_Role_Valid_Thru_c__c as Effective_End_Date__c FROM cgcloud_Account_Extension_c__dll Customer Role Data Transform Transform Account Extension's Customer Role into Customer_Role_dlo DLO Customer_Role_dlo SELECT cgcloud_Account_Extension_c__dll.Customer_Role_Id__c as Account_Role_Id__c, cgcloud_Account_Extension_c__dll.cgcloud_Account_c__c as Account__c, "Customer Role" as Account_Role_Type__c, cgcloud_Account_Extension_c__dll.cgcloud_Customer_Role_Valid_From_c__c as Effective_Start_Date__c, cgcloud_Account_Extension_c__dll.cgcloud_Customer_Role_Valid_Thru_c__c as Effective_End_Date__c FROM cgcloud_Account_Extension_c__dll Delivery Role Data Transform Transform Account Extension's Delivery Role into Delivery_Role_dlo DLO Delivery_Role_dlo SELECT cgcloud_Account_Extension_c__dll.Delivery_Role_Id__c as Account_Role_Id__c, cgcloud_Account_Extension_c__dll.cgcloud_Account_c__c as Account__c, "Delivery Role" as Account_Role_Type__c, cgcloud_Account_Extension_c__dll.cgcloud_Delivery_Role_Valid_From_c__c as Effective_Start_Date__c, cgcloud_Account_Extension_c__dll.cgcloud_Delivery_Role_Valid_Thru_c__c as Effective_End_Date__c FROM cgcloud_Account_Extension_c__dll Fund Role Data Transform Transform Account Extension's Fund Role into Fund_Role_dlo DLO Fund_Role_dlo SELECT cgcloud_Account_Extension_c__dll.Fund_Role_Id__c as Account_Role_Id__c, cgcloud_Account_Extension_c__dll.cgcloud_Account_c__c as Account__c, "Fund Role" as Account_Role_Type__c, cgcloud_Account_Extension_c__dll.cgcloud_Fund_Role_Valid_From_c__c as Effective_Start_Date__c, cgcloud_Account_Extension_c__dll.cgcloud_Fund_Role_Valid_Thru_c__c as Effective_End_Date__c FROM cgcloud_Account_Extension_c__dll POS Template Data Transform cgcloud__POS_POS_Template__c Batch Promotion Account Data Transform Promotion_Account_dlo Batch Store Role Data Transform Transform Account Extension's Store Role into Store_Role_dlo DLO Store_Role_dlo SELECT cgcloud_Account_Extension_c__dll.Store_Role_Id__c as Account_Role_Id__c, cgcloud_Account_Extension_c__dll.cgcloud_Account_c__c as Account__c, "Store Role" as Account_Role_Type__c, cgcloud_Account_Extension_c__dll.cgcloud_Store_Role_Valid_From_c__c as Effective_Start_Date__c, cgcloud_Account_Extension_c__dll.cgcloud_Store_Role_Valid_Thru_c__c as Effective_End_Date__c FROM cgcloud_Account_Extension_c__dll Tactic Type Data Transform Tactic_Type_dlo Batch Visit Job Data Type Data Transformation Visit_Job_dlo Batch Trade Promotion Management
Data Transform Name Description Target DLO Query Order Billing Address Data Transform Transform Order's Billing Address into Order_Billing_Address_dlo DLO Order_Billing_Address_dlo SELECT Order__dll.Billing_Address_Id__c as Address_Id__c, Order__dll.BillingCity__c as BillingCity__c, Order__dll.BillingCountry__c as BillingCountry__c, Order__dll.BillingLatitude__c as BillingLatitude__c, Order__dll.BillingLongitude__c as BillingLongitude__c, Order__dll.BillingState__c as BillingState__c, Order__dll.BillingStreet__c as BillingStreet__c, Order__dll.BillingPostalCode__c as BillingPostalCode__c FROM Order__dll Order Shipping Address Data Transform Transform Order's Shipping Address into Order_Shipping_Address_dlo DLO Order_Shipping_Address_dlo SELECT Order__dll.Id__c as Address_Id__c, Order__dll.ShippingCity__c as ShippingCity__c, Order__dll.ShippingCountry__c as ShippingCountry__c, Order__dll.ShippingLatitude__c as ShippingLatitude__c, Order__dll.ShippingLongitude__c as ShippingLongitude__c, Order__dll.ShippingState__c as ShippingState__c, Order__dll.ShippingStreet__c as ShippingStreet__c, Order__dll.ShippingPostalCode__c as ShippingPostalCode__c FROM Order__dll Account Billing Address Data Transform Transform Account's Billing Address into Account_Billing_Address_dlo DLO Account_Billing_Address_dlo SELECT Account__dll.Billing_Address_Id__c as Address_Id__c, Account__dll.BillingCity__c as BillingCity__c, Account__dll.BillingCountry__c as BillingCountry__c, Account__dll.BillingLatitude__c as BillingLatitude__c, Account__dll.BillingLongitude__c as BillingLongitude__c, Account__dll.BillingState__c as BillingState__c, Account__dll.BillingStreet__c as BillingStreet__c, Account__dll.BillingPostalCode__c as BillingPostalCode__c FROM Account__dll Account Shipping Address Data Transform Transform Account's Shipping Address into Account_Shipping_Address_dlo DLO Account_Shipping_Address_dlo SELECT Account__dll.Shipping_Address_Id__c as Address_Id__c,Account__dll.ShippingCity__c as ShippingCity__c,Account__dll.ShippingCountry__c as ShippingCountry__c,Account__dll.ShippingLatitude__c as ShippingLatitude__c,Account__dll.ShippingLongitude__c as ShippingLongitude__c,Account__dll.ShippingState__c as ShippingState__c,Account__dll.ShippingStreet__c as ShippingStreet__c,Account__dll.ShippingPostalCode__c as ShippingPostalCode__c FROM Account__dll Bill To Role Data Transform Transform Account Extension's Bill To Role into Bill_To_Role_dlo DLO Bill_To_Role_dlo SELECT cgcloud_Account_Extension_c__dll.Bill_To_Role_Id__c as Account_Role_Id__c, cgcloud_Account_Extension_c__dll.cgcloud_Account_c__c as Account__c, "Bill To Role" as Account_Role_Type__c, cgcloud_Account_Extension_c__dll.cgcloud_Bill_To_Role_Valid_From_c__c as Effective_Start_Date__c, cgcloud_Account_Extension_c__dll.cgcloud_Bill_To_Role_Valid_Thru_c__c as Effective_End_Date__c FROM cgcloud_Account_Extension_c__dll Broker Role Data Transform Transform Account Extension's Broker Role into Broker_Role_dlo DLO Broker_Role_dlo SELECT cgcloud_Account_Extension_c__dll.Broker_Role_Id__c as Account_Role_Id__c, cgcloud_Account_Extension_c__dll.cgcloud_Account_c__c as Account__c, "Broker Role" as Account_Role_Type__c, cgcloud_Account_Extension_c__dll.cgcloud_Broker_Role_Valid_From_c__c as Effective_Start_Date__c, cgcloud_Account_Extension_c__dll.cgcloud_Broker_Role_Valid_Thru_c__c as Effective_End_Date__c FROM cgcloud_Account_Extension_c__dll Customer Role Data Transform Transform Account Extension's Customer Role into Customer_Role_dlo DLO Customer_Role_dlo SELECT cgcloud_Account_Extension_c__dll.Customer_Role_Id__c as Account_Role_Id__c, cgcloud_Account_Extension_c__dll.cgcloud_Account_c__c as Account__c, "Customer Role" as Account_Role_Type__c, cgcloud_Account_Extension_c__dll.cgcloud_Customer_Role_Valid_From_c__c as Effective_Start_Date__c, cgcloud_Account_Extension_c__dll.cgcloud_Customer_Role_Valid_Thru_c__c as Effective_End_Date__c FROM cgcloud_Account_Extension_c__dll Delivery Role Data Transform Transform Account Extension's Delivery Role into Delivery_Role_dlo DLO Delivery_Role_dlo SELECT cgcloud_Account_Extension_c__dll.Delivery_Role_Id__c as Account_Role_Id__c, cgcloud_Account_Extension_c__dll.cgcloud_Account_c__c as Account__c, "Delivery Role" as Account_Role_Type__c, cgcloud_Account_Extension_c__dll.cgcloud_Delivery_Role_Valid_From_c__c as Effective_Start_Date__c, cgcloud_Account_Extension_c__dll.cgcloud_Delivery_Role_Valid_Thru_c__c as Effective_End_Date__c FROM cgcloud_Account_Extension_c__dll Fund Role Data Transform Transform Account Extension's Fund Role into Fund_Role_dlo DLO Fund_Role_dlo SELECT cgcloud_Account_Extension_c__dll.Fund_Role_Id__c as Account_Role_Id__c, cgcloud_Account_Extension_c__dll.cgcloud_Account_c__c as Account__c, "Fund Role" as Account_Role_Type__c, cgcloud_Account_Extension_c__dll.cgcloud_Fund_Role_Valid_From_c__c as Effective_Start_Date__c, cgcloud_Account_Extension_c__dll.cgcloud_Fund_Role_Valid_Thru_c__c as Effective_End_Date__c FROM cgcloud_Account_Extension_c__dll Store Role Data Transform Transform Account Extension's Store Role into Store_Role_dlo DLO Store_Role_dlo SELECT cgcloud_Account_Extension_c__dll.Store_Role_Id__c as Account_Role_Id__c, cgcloud_Account_Extension_c__dll.cgcloud_Account_c__c as Account__c, "Store Role" as Account_Role_Type__c, cgcloud_Account_Extension_c__dll.cgcloud_Store_Role_Valid_From_c__c as Effective_Start_Date__c, cgcloud_Account_Extension_c__dll.cgcloud_Store_Role_Valid_Thru_c__c as Effective_End_Date__c FROM cgcloud_Account_Extension_c__dll Tactic Type Data Transform Tactic_Type_dlo Batch Promotion Account Data Transform Promotion_Account_dlo Batch
Did this article solve your issue?
Let us know so we can improve!
