Start with CData Sync Part 3: Creating a Transformation Job

CData Sync provides automated, continuous replication of your enterprise data to any data store. In this article, we show how to create and trigger a transformation job as the last step of an ELT (Extract, Load, Transform) process.

This article requires you to already have replicated data in place in your replication destination. If you haven't already, refer to Parts 1 and 2 of the series before continuing.

Start with CData Sync Part 1: Licensing, Creating Connections, and Creating a Replication Job

Start with CData Sync Part 2: Customizing Replications

Creating a Transformation Job

1. Click on the Transformations tab in the navbar and then select +Add Transformation. Give the transformation a Name and select the transformation Type and Destination. Click Add Transformation.
2. Next, click the Queries tab from the main connection page. Insert the SQL query that will be used to transform the replicated data. In this case, we join the Salesforce tables to collect the total amount for all opportunities, grouped by the billing state for the account associated with each opportunity. Then, click Add.

CREATE OR REPLICATE TABLE "Salesforce_Opp_Totals_By_State" as (
   SELECT Salesforce_Account.BillingState AS State,
      SUM(Salesforce_Opportunity.Amount) AS TotalAmount
   FROM Salesforce_Account
      JOIN Salesforce_Opportunity
         ON Salesforce_Account.Id = Salesforce_Opportunity.AccountId
   GROUP BY Salesforce_Account.BillingState
)

3. Then, navigate back to the Overview tab and click Configure from within the Trigger section. For the Type, select After Job. We choose After Job to ensure the related replication is completed before we attempt to transform the data. Also, you can get more granular by clicking +Add Tasks to include tasks pertinent to the replication job. Click Save.
4. Click Save Changes to save your transformation and triggers.

That's everything you need to know to create transformation tasks and triggers in CData Sync.

Free Trial & More Information

Now that you have seen how to create a transformation job, visit our CData Sync page to read more information about CData Sync and download a free trial. Start consolidating your enterprise data to a cloud data warehouse today! As always, our world-class Support Team is ready to answer any questions you may have.