Salesforce to Snowflake sync

A Salesforce → Snowflake sync is usually built as an ETL/ELT pipeline that extracts data from Salesforce, lands it in Snowflake, and then transforms it there.

Common approaches

1. Managed connectors / ETL tools

Best if you want speed and less maintenance.

Examples:

• Fivetran
• Stitch
• Matillion
• Airbyte
• Informatica
• Talend

Typical flow: Salesforce API → connector → Snowflake staging/raw tables → transformations

Pros:

• Fast to set up
• Handles API pagination, retries, schema drift
• Incremental syncs and CDC-like behavior often included

Cons:

• Ongoing license cost
• Less control over exact sync logic

---

2. Custom integration

Best if you need full control or have special requirements.

Typical flow:

• Use Salesforce REST/Bulk API
• Extract objects like Account, Contact, Opportunity, etc.
• Load files to cloud storage or directly into Snowflake
• Use Snowflake COPY INTO or Snowpipe
• Run dbt/Snowflake SQL transformations

Pros:

• Full control
• Can optimize for your business rules
• Lower tool cost if you already have engineering capacity

Cons:

• More engineering and maintenance
• Must handle API limits, retries, deletes, schema changes

---

Recommended architecture

A common and reliable pattern:

1. Extract from Salesforce

   • Use Bulk API 2.0 for large tables
   • Use REST API for smaller or more frequent objects
   • Track SystemModStamp or LastModifiedDate for incremental loads

2. Land raw data

   • Store JSON/CSV in S3, Azure Blob, or GCS
   • Or stage directly into Snowflake

3. Load into Snowflake raw layer

   • Use external/internal stages
   • COPY INTO or Snowpipe

4. Transform in Snowflake

   • Use dbt or SQL models
   • Build cleaned dimensions/facts

5. Handle deletes and updates

   • Soft delete flags or merge logic
   • Periodic full refresh for certain objects if needed

---

Key Salesforce considerations

• API limits: Salesforce has daily and concurrent API limits
• Incremental sync: Use timestamps carefully; SystemModStamp is often better than LastModifiedDate
• Deletes: Need explicit handling; deletes do not always appear in simple incremental queries
• Relationships: Objects like Opportunity, Account, Contact, Lead, Case often require joins
• Schema drift: Custom fields are common and may change over time

---

Key Snowflake considerations

• Use separate layers:
  • RAW for ingested source data
  • STAGING for type casting/standardization
  • MART for business-ready tables
• Prefer MERGE for upserts
• Use streams/tasks if you want near-real-time processing
• Use dbt if you want maintainable transformations and lineage

---

If you want near real-time sync

Use:

• Salesforce change events / platform events if available
• A CDC-capable connector
• Snowpipe for continuous ingestion

Note: truly real-time is harder with Salesforce than with databases because API/event behavior varies by object and license.

---

Example implementation options

Option A: Fivetran + Snowflake

1. Connect Salesforce in Fivetran
2. Select objects
3. Fivetran replicates to Snowflake raw schema
4. dbt transforms raw to models

Good for most teams.

Option B: Custom Python + Snowflake

1. Query Salesforce Bulk API incrementally
2. Write results to parquet/csv
3. Upload to cloud storage
4. COPY INTO Snowflake
5. MERGE into target tables

Good for custom workflows.

---

Best practice checklist

• Use incremental loads
• Preserve raw source data
• Version your transformations
• Track source timestamps and load timestamps
• Plan for deletes and schema changes
• Monitor row counts and freshness
• Secure credentials with secrets manager
• Document object mappings

If you want, I can also give you:

1. a reference architecture diagram,
2. a Python sample for Salesforce → Snowflake, or
3. a tool comparison table.