Configuring the Maintenance Data Pipeline
Deploy and configure the Matillion ELT pipeline that extracts preventive maintenance data from Salesforce, loads it into Snowflake staging tables, and transforms it into the PTC Orbit Canonical Data Model.
The PM Data Sync Framework (PMDF) is a modular ELT pipeline that extracts preventive maintenance data from Salesforce and merges it into Snowflake staging tables for downstream consumption by PTC Orbit. A single master pipeline orchestrates four stages in sequence:
• Stage 1: Maintenance Plans and Plan Templates
• Stage 2: Schedule Rules (Schedule Definitions and Schedule Templates)
• Stage 3: Schedules
• Stage 4: Children (Coverage, Work History, Applicable Products)
Each stage extracts data from Salesforce using the modular-salesforce-input-v2 connector, loads raw records into Snowflake staging tables, and transforms them into the target DEV_*_PREDB_SYNC_STAGE tables. The pipeline supports three load modes: AUTO (default; detects configuration changes and selects FULL or INCREMENTAL automatically), FULL (complete data refresh), and INCREMENTAL (delta sync based on LastModifiedDate).
|
Prerequisites
|
|---|
|
• Implementer-level access to PTC Orbit and the Matillion environment.
• Salesforce authentication configured. For setup instructions, see Configuring Salesforce Authentication.
• ServiceMax Core PM Plans configured with the required PM objects: PM Plan, PM Plan Template, PM Coverage, PM Schedule, PM Schedule Definition, PM Schedule Template, PM History, and PM Applicable Product.
|
Configure the Max Platform
1. In the Max platform, set the Data Ingestion Mode to Direct Sync for each target object in the Max Object configuration.
2. Click Synchronize to create the required Direct Sync staging tables in Snowflake.
 |
Without this step, the Direct Sync tables do not exist in Snowflake. Pipeline validation steps fail, and all data load runs produce errors. Complete this step before the first pipeline execution.
|
Import and Configure the Pipeline
1. Obtain the PM pipeline package from PTC and import it into your Matillion project.
After import, the pipeline jobs appear in the Matillion project explorer under SFDC DataSync OOTB Pipelines. The package includes orchestration pipelines (Master Pipeline, Stages 1 through 4, Error Handler, Success Handler), transformation pipelines, and an automated test suite.
2. Configure the Salesforce connection by setting the following Matillion environment variables under Project > Environments > Environment Variables:
|
Variable
|
Type
|
Description
|
|---|---|---|
|
sf_username
|
TEXT (SHARED)
|
Salesforce API username for all connectors
|
|
sf_security_token
|
TEXT (SHARED)
|
Salesforce security token for the API user
|
|
sf_is_sandbox
|
TEXT (SHARED)
|
Set to Yes for sandbox or No for production
|
Create the following entries in the Matillion Password Manager:
◦ sf_password: Salesforce API password
3. Configure SMTP settings for pipeline email notifications by creating these Matillion environment variables:
|
Variable
|
Description
|
|---|---|
|
v_notification_email
|
Recipient email address for success and failure notifications
|
|
v_sender_email
|
From address on outgoing emails
|
|
v_smtp_host
|
SMTP server hostname
|
|
v_smtp_port
|
SMTP server port (for example, 587 for StartTLS)
|
|
v_smtp_username
|
SMTP authentication username
|
Create a smtp_password entry in the Matillion Password Manager for the SMTP authentication password.
 |
If SMTP credentials are not available, you can disable the email-sending components in the PM Error Handler and PM Success Handler pipelines. The core data sync logic runs without email notifications.
|
4. Confirm that the Matillion environment's default Snowflake connection (warehouse, database, and schema) points to the correct target. All pipeline components use [Environment Default] for Snowflake connectivity; no connection values are hardcoded in the pipelines.
5. Verify the IO_DATA_SOURCE UUID in the transformation pipeline Calculator components. Every transformation pipeline sets this value to 254341a6-c6a4-48bc-b5f8-f57f38eccf0e.
This UUID must match the Data Connector record ID in your target ServiceMax environment. If it does not match, update the Calculator expression in each transformation pipeline with the correct UUID.
|
|
An incorrect IO_DATA_SOURCE value causes records to fail matching during merge operations, which leads to duplicate or orphaned data.
|
6. Review the p_enable_target_cleanup variable on Stage 1. The default value is Yes, which enables cascading deactivation of target records for plans that no longer match any PM Process configuration.
If your environment has PM Plans managed outside this pipeline, set the value to No to prevent unintended deactivation.
Run and Validate the Pipeline
1. Open the 7 - Salesforce PM Module Master Pipeline in Matillion and set p_load_mode to FULL for the initial data load.
Click Run. The Master Pipeline executes initialization, Stages 1 through 4, and the Success Handler in sequence. If any stage fails, the Error Handler logs the failure and sends an alert email.
|
|
After the initial FULL load, subsequent runs should use the default AUTO mode. AUTO mode detects whether PM Process configurations have changed and selects FULL or INCREMENTAL automatically.
|
2. Verify pipeline execution by checking the PIPELINE_EXECUTION_LOG table in Snowflake. This table records per-stage status, record counts, timestamps, and duration for every run.
Run the following query to review the most recent execution:
SELECT * FROM PIPELINE_EXECUTION_LOG ORDER BY CREATED_AT DESC LIMIT 50;
Check the PIPELINE_ERROR_LOG table for any recorded errors. Both tables are created automatically by the Pipeline_Initialization component on the first run.
3. Set up a schedule on the 7 - Salesforce PM Module Master Pipeline for ongoing syncs using the Matillion scheduler.
Leave p_load_mode at its default value of AUTO for scheduled runs. The pipeline detects configuration changes automatically and selects the appropriate load mode. Use FULL or INCREMENTAL overrides only for recovery or testing scenarios.
What to Do Next
After the pipeline runs, preventive maintenance data from Salesforce flows into the PTC Orbit Canonical Data Model. PM Plans, Plan Templates, Coverage, Schedules, Schedule Rules, Work History, and Applicable Product records populate the target tables in Snowflake, where they support demand forecasting and maintenance performance analytics.