Building a Cost-Aware, Closed-Loop ETL and Reverse ETL Pipeline for Salesforce and BigQuery

2026-07-09 · Source: Data Engineering on Medium · Field: Technology & Digital — Software Development & Engineering, Cloud Computing & IT Infrastructure, Data Science & Analytics · Depth: Intermediate, long

Summary

A cost-aware, closed-loop ETL and Reverse ETL pipeline for Salesforce and BigQuery is presented, designed to mitigate high Salesforce storage costs and enhance analytical capabilities. This automated solution, built entirely on Google Cloud's serverless primitives including Cloud Scheduler, Cloud Workflows, Cloud Run, ephemeral Cloud Data Fusion (BASIC tier), BigQuery, and Dataform, moves historical Salesforce data to BigQuery and writes aggregated insights back. It specifically resolves issues like idle infrastructure costs by creating and deleting Data Fusion instances per run, silent data loss through explicit deletion tracking, and the limitations of one-way data flow. The pipeline uses the "salesforce-plugins" artifact (v1.7.0) for ingestion and Google Secret Manager for secure credential management, targeting cost-sensitive organizations where a daily data refresh is acceptable.

Key takeaway

For Data Engineers managing Salesforce data archives and analytics, this pipeline offers a cost-effective, reliable solution. If your organization prioritizes cost-efficiency over sub-minute data freshness, you should consider this ephemeral, incremental architecture. It ensures data integrity by tracking deletions and delivers bidirectional insights by writing aggregated metrics back to Salesforce, keeping your CRM lean and analytics robust without persistent infrastructure costs.

Key insights

The pipeline optimizes Salesforce data management by archiving to BigQuery and reverse ETL, using ephemeral Google Cloud services.

Principles

• Ephemeral compute minimizes idle costs.
• Incremental processing reduces compute load.
• Explicit deletion tracking prevents data drift.

Method

The pipeline orchestrates Cloud Data Fusion for incremental Salesforce ingestion and deletion tracking, Dataform for SQL-native transformations, and Cloud Run for reverse ETL, all scheduled by Cloud Scheduler and managed by Cloud Workflows.

In practice

• Deploy Data Fusion instances ephemerally.
• Use "salesforce-plugins" v1.7.0 for Salesforce ingestion.
• Store Salesforce credentials in Google Secret Manager.

Topics

• Salesforce ETL
• Reverse ETL
• BigQuery
• Cloud Data Fusion
• Google Cloud Workflows
• Dataform

Code references

• data-integrations/salesforce
• caugusto/salesforce-cdf
• data-integrations/salesforce

Best for: Data Engineer, Software Engineer, IT Professional

Related on AIssential

• Building ETL Pipelines in Databricks | Data Engineering in Databricks — Databricks ETL pipelines transform raw data into production-ready insights using Medallion Architecture and AI-assisted coding.
• PipelineIQ: Forward‑Looking Sales Intelligence That Drives Action — Prescriptive AI can transform messy CRM data into immediate, actionable sales guidance, prioritizing action over traditional forecasting.
• Scaling for MHHS: how Octopus Energy achieved a 50x cost reduction in margin data engineering — Re-architecting data pipelines for granular processing and incremental updates drastically reduces costs and improves data freshness.
• How We Reduced a Large Spark ETL Job Runtime by 80%: A Practical Databricks Optimization Guide — Systematic Spark ETL optimization can drastically reduce runtime and costs without changing business logic or cluster size.
• Scaling Graph Analytics Without ETL: Inside PuppyGraph’s Architecture — PuppyGraph enables direct graph querying on existing lakehouse data, bypassing ETL for scalable, real-time analytics.
• PIPELINE — Cloud economics make ELT superior to traditional ETL for modern data pipelines.

Open in AIssential →

Editorial summary, takeaway, and curation by AIssential. Original article published by Data Engineering on Medium.