Seamless Salesforce leads and opportunities to PostgreSQL with backfill and incremental ETL Synchronization

Workflow overview

Why this workflow matters

Helpful for business development and pipeline building. Relevant for managed services and support workflows.

Salesforce Leads & Opportunities to PostgreSQL (Backfill & Incremental Sync ETL) This workflow extracts Lead and Opportunity data from Salesforce, transforms and normalizes the data, and loads it into PostgreSQL as a structured data bank for reporting and analytics. It is designed for scalable data ingestion and supports both historical backfill and incremental sync in a single workflow. Use Case This workflow is suitable when you need to: Centralize Salesforce data into a database for reporting Build a data warehouse for BI tools (Looker Studio, Metabase, etc.) Track lead-to-opportunity lifecycle Merge multiple Salesforce objects into a unified dataset Maintain a clean and normalized CRM data layer Two Input Modes 1. Historical Backfill (Manual Trigger) Run once to populate historical data. Set start_date and end_date in the "Set Historical Date Range" node Data is split into 7-day batches Each batch is processed sequentially to reduce API load 2. Incremental Sync (Schedule Trigger) Runs automatically (e.g. daily). Date range is generated dynamically using ISO datetime Typically pulls data from yesterday until today No manual input required Batch Processing Date ranges are processed in weekly batches. This helps: Prevent large API requests Reduce timeout risk Improve stability during backfill Keep memory usage efficient Core Workflow Logic 1. Data Extraction Fetch Lead records from Salesforce Fetch Opportunity records from Salesforce Filter using CreatedDate (since_datetime and until_datetime) 2. Phone-Based Routing Records are split into two paths: Records without phone: Skip normalization Still included in final dataset Records with phone: Processed for normalization Used for merging This ensures no data is lost even if phone is missing. 3. Phone Normalization (+62) Phone numbers are standardized into: +62XXXXXXXXXX Steps: Remove spaces and symbols Remove all non-digit characters Convert 0xxxx → 62xxxx Ensure no duplicated prefix (e.g. 6262) Add "+" prefix This uses Indonesia's International Direct Dialing (IDD) code: +62 4. Opportunity De-duplication Duplicate opportunities are removed Based on normalized phone key This ensures clean merging and avoids duplicate enrichment. 5. Lead–Opportunity Merge Merge is done using normalized phone fields: body.nomorlead body.nomoroppty Behavior: Lead is the primary dataset Opportunity enriches lead Records without phone: Still preserved Not removed 6. Data Standardization All records are transformed into a unified schema: Source_Object SF_Id CreatedDate CreatedById Name Phone Clean_Phone Email LeadSource Status StageName OwnerId AccountId Amount 7. Upsert to PostgreSQL Uses UPSERT (insert or update) Matching key: sf_id Behavior: New data → insert Existing data → update Ensures: No duplicate records Idempotent execution Data Flow Summary Salesforce (Lead + Opportunity) → Date Filtering → Batch Processing (weekly) → Phone Routing → Phone Normalization (+62) → Opportunity Deduplication → Lead–Opportunity Merge → Data Standardization → PostgreSQL (Upsert) Setup Requirements Before using this workflow, prepare the following: 1. Salesforce Salesforce OAuth2 credential Access to: Lead object Opportunity object Ensure API access is enabled 2. PostgreSQL Active PostgreSQL database Credentials configured in AlekSystem Table created (see schema below) 3. AlekSystem Environment AlekSystem instance (cloud or self-hosted) Salesforce node configured PostgreSQL node configured 4. Date Configuration (Backfill) Set start_date and end_date manually in: "Set Historical Date Range" node 5. Schedule Configuration (Incremental) Configure Schedule Trigger Recommended: Daily execution Off-peak hours Minimal PostgreSQL Table Schema CREATE TABLE AlekSystem_salesforce_data ( sf_id TEXT PRIMARY KEY, Source_Object TEXT, CreatedDate TIMESTAMP, CreatedById TEXT, Name TEXT, Phone TEXT, Clean_Phone TEXT, Email TEXT, LeadSource TEXT, Status TEXT, StageName TEXT, OwnerId TEXT, AccountId TEXT, Amount NUMERIC, synced_at TIMESTAMP DEFAULT NOW() ); Important Notes sf_id is used as the unique key for upsert Clean_Phone is recommended for indexing if used in analytics Data consistency depends on phone normalization quality Schema must be updated manually if additional fields are added Known Limitations Phone-based matching may fail if: Phone numbers are inconsistent Phone is missing in both Lead and Opportunity No deduplication for Leads (only Opportunities handled) No retry logic for API failures (can be added) Recommended Improvements Add index on Clean_Phone for faster queries Add logging table for monitoring ETL runs Add retry and error handling nodes Extend support for: Contact Account Campaign data Summary This workflow provides a reliable and scalable way to: Extract Salesforce data Normalize and merge datasets Store structured data in PostgreSQL Enable analytics and reporting pipelines It is best suited for teams building a lightweight data warehouse layer on top of Salesforce.

Best fit

Need another direction?