Configuring Full Sync Orchestration Pipeline
Set up a Matillion orchestration pipeline to perform a full data sync from Salesforce to Snowflake.
Set up an orchestration pipeline in Matillion to perform a full data sync from Salesforce to Snowflake. The pipeline consists of three components: loading source data, running a transformation pipeline, and executing stored procedures.
Pipeline steps
1. Load data from Salesforce. Add a Salesforce Load component and configure the following settings:
◦ Connect: Set the authentication type, Salesforce org type, and login credentials.
◦ Configure: Select Full Load as the load type. Choose Basic mode to build the query through the UI. Select the Salesforce data source object (for example, SVMXC__Installed_Product__c for the Asset object). Define data selection columns and filter conditions.
◦ Destination: Enter the Snowflake table name (for example, Asset_SFDC). Select Replace as the load strategy to overwrite existing data on each pipeline run.
2. Run the transformation pipeline. Add a Run Transformation component to transform Salesforce data into the PTC Orbit format. The transformation pipeline loads data from the raw table, maps fields using the Calculator component, and saves the transformed data to the Snowflake stage table.
In the Calculator component, set the IO_DATA_SOURCE expression value to the UUID of the Salesforce Data Connector (254341a6-c6a4-48bc-b5f8-f57f38eccf0e).
When saving to the stage table using the Table Output component, define the join expression:
"input"."Id" = "target"."IO_EXTERNAL_ID" AND "input"."IO_DATA_SOURCE" = "target"."IO_DATA_CONNECTOR"
 |
For fields with Relationship or Option List data types, map to the corresponding shadow field with the _EXTERNAL_ID suffix. The exception fields IO_DATA_CONNECTOR, IO_PREDB_SYNC_STATUS, and IO_DATA_REVIEW_STATUS are mapped directly.
|
3. Run stored procedures. Add an SQL Script component to validate and group stage records. For Staging Review objects (for example, Asset), execute:
CALL VALIDATE_SYNCED_RECORDS('SVMX_ASSET_DATA_REVIEW_STAGE');
EXECUTE TASK GROUP_ASSET_DATA_REVIEW_STAGE_RECORDS_TASK;
For Pre-DB Sync objects (for example, Account), execute only the validation procedure:
CALL VALIDATE_SYNCED_RECORDS('SVMX_ACCOUNT_PREDB_SYNC_STAGE');
The VALIDATE_SYNCED_RECORDS procedure validates synced records and sets default values for status fields. The GROUP_<OBJECT>_DATA_REVIEW_STAGE_RECORDS_TASK procedure groups synced records for user review.
What To Do Next