Skip to content
RelentlessData Engineering

Case Study

End-to-end reverse ETL: Snowflake to Salesforce (lightweight, config-driven)

A lightweight, config-driven reverse ETL pipeline from Snowflake to Salesforce that saved about $10,000 a year in tooling fees, cut sync latency by roughly 90% via a direct bulk path, and gave engineering clear logs and auditability.

Client
A B2B SaaS company syncing curated Snowflake marts into Salesforce
Sector
B2B SaaS
Duration
Delivered as a single focused build

Stack

  • Python
  • Snowflake
  • Snowpark
  • Salesforce REST API
  • Salesforce Bulk API
  • YAML
  • Docker
  • GitLab CI
  • AWS Secrets Manager
  • $10,000/yearsaved in managed reverse ETL tooling fees, for these syncs (indicative)
  • ~90% lowersync latency, via the direct Snowflake-to-Salesforce bulk path (indicative)
  • 1 YAML fileto add a new sync, with no application change and a reviewable Git diff (indicative)
  • Full audit traillogging, failed-record summaries and email alerts sent from Snowflake (indicative)

Business context and problem

Why change?

  • A managed reverse ETL platform was more tool than these syncs needed, and was priced accordingly.
  • Latency and control: third-party connectors added delay and limited tuning.
  • Ownership: the client needed a simple, auditable pipeline under engineering control.

Success criteria

Lower cost and latency, predictable transforms, clear logs and auditability, and fast iteration with minimal code.

Requirements and constraints

  • Config over code: add or adjust syncs via YAML, not app changes.
  • Simple transforms: mostly renames and mappings, predictable and auditable.
  • Bulk-first: prefer the Salesforce Bulk API for higher volumes.
  • Guardrails: avoid overwriting populated fields where not intended.
  • Fault isolation: one bad sync or record must not halt the others.
  • Mixed operation modes: insert, update and upsert are available per config.

Add a new sync by dropping in a YAML file, with no code changes.

Solution overview

  • Python app: run_reverse_etl.py discovers YAML configs and executes syncs independently.
  • Extract: Snowflake through a Snowpark session, with compute pushed to the warehouse.
  • Transform: lightweight field mapping and conditional logic.
  • Load: the Salesforce REST or Bulk API, chosen per config.
  • Config-driven: each sync is defined in configs/*.yaml.
  • Selective queries: Snowflake SQL returns only rows that require syncing.

Architecture and data flow

Data flow for the reverse ETL pipelineFive stages read left to right. Stage one, trigger: a GitLab CI schedule, or a manual run. Stage two, runner and secrets: a GitLab runner on AWS pulls the image and runs it, and AWS Secrets Manager supplies the Snowflake and Salesforce credentials to the same container. Stage three, the sync app: a Docker container running run_reverse_etl.py against a directory of YAML sync configs. Stage four, source and target: the container queries Snowflake through a Snowpark session, so the compute is pushed to the warehouse, and Snowflake returns only the rows that need syncing; the container then loads those rows into Salesforce over the REST or Bulk API, chosen per config. Stage five, observability: Snowflake sends the email alerts that report each run and its failed records.01TRIGGER02RUNNER ANDSECRETS03SYNC APP04SOURCE ANDTARGET05OBSERVABILITYGitLab CIschedule or manualAWS SecretsManagerwarehouse and CRMGitLab runneron AWS, pulls imageDocker containerrun_reverse_etl.pyplus YAML configsSnowflakeSnowpark extract,only rows to syncSalesforceREST or Bulk APIEmail alertssent from Snowflake
DiagramScrolls sideways on a narrow screen

Reverse ETL, trigger to target. Five stages, left to right. A GitLab CI schedule or a manual run starts a GitLab runner on AWS, which pulls the image and runs a Docker container holding the sync app and its YAML configs. AWS Secrets Manager supplies the Snowflake and Salesforce credentials to that same container. The container queries Snowflake through a Snowpark session, so the compute is pushed to the warehouse, and Snowflake returns only the rows that need syncing; those rows are then loaded into Salesforce over the REST or Bulk API, chosen per config. Snowflake sends the email alerts that report each run.

Flow (high-level)

  1. The runner starts the container with the app, its configs and the secrets from AWS Secrets Manager.
  2. The app discovers the YAML configs, and each sync runs in isolation.
  3. The extract executes in Snowflake, and queries are scoped to only the rows that require syncing.
  4. The transform maps and renames fields and applies guardrails such as set_if_empty.
  5. The load upserts, updates or inserts via REST or Bulk, and results are summarised per config.
  6. Observability: Snowflake sends the email alerts.

Configuration-driven design

  • Add a new sync by dropping in a YAML file, with no code changes. Iteration is faster, and each change is a clear Git diff to review.
  • Consistent runtime: the same runner, logging and guardrails across all syncs.

Notes:

  • set_if_empty prevents overwriting populated fields.
  • The operation is set per config, which allows mixed modes.

Deployment and operations

  • Runner: a GitLab runner on AWS.
  • Image: pulled from Amazon ECR or the GitLab container registry.
  • Secrets: AWS Secrets Manager (Snowflake and Salesforce credentials, among others).

Runtime

  • Triggering: CI schedules, manual runs or both.
  • Batching: the Bulk API by default for volume, and REST for per-record sets.
  • Selective loads: Snowflake SQL returns only rows needing sync.

Results and impact

Before

A managed reverse ETL connector, with delay added and tuning limited.

After

A direct Snowflake-to-Salesforce bulk path, under engineering control.

ChartIndicative

Sync latency, before and after. How long a curated mart took to reach Salesforce. Before, a managed reverse ETL connector sat between the two systems and added its own scheduling and queueing. After, the container queries Snowflake directly and loads over the Bulk API, so the only delay left is the work itself.

Indicative: comparative shape only. The bars carry no axis and no values, because the underlying figures are directional rather than audited.

Removing the managed reverse ETL licence for these syncs took roughly $10,000 a year of tooling fees out of the stack, and the direct bulk path cut latency by about 90%. Operationally the change is larger than either figure: the pipeline is report-only with clear summaries, alerts arrive by email from Snowflake, and every sync definition is a file in Git with a review history.

Trade-offs and alternatives

  • Managed reverse ETL: pre-built connectors, but higher cost and less control.
  • Heavier custom build: more features (data quality, lineage, retries), but higher maintenance.
  • This pattern: the best ROI for simple, selective syncs where control matters.