Why is my Salesforce data not landing correctly in Snowflake?

There are a few common reasons Salesforce data doesn’t “land” correctly in Snowflake, and the right fix depends on where it’s failing: extraction, transformation, or load.

Most common causes

1. API limits or extraction failures in Salesforce

   • Salesforce has API/request limits.
   • Large objects or frequent syncs can cause partial extracts or missing rows.
   • Incremental syncs based on LastModifiedDate can miss records if the watermark logic is wrong.

2. Data type mismatches

   • Salesforce fields may not map cleanly to Snowflake types.
   • Common issues:
     • long text fields truncated
     • number/decimal precision loss
     • date vs timestamp timezone shifts
     • boolean/null handling differences

3. Schema drift

   • New Salesforce fields are added or renamed.
   • If your pipeline expects a fixed schema, loads can fail or silently drop columns.

4. Flattening of nested/complex Salesforce structures

   • Objects like Owner, RecordType, child relationships, or multi-select picklists can be transformed incorrectly.
   • JSON arrays/objects may need explicit flattening before loading.

5. Bad incremental load logic

   • Duplicate rows can happen if:
     • the same window is reprocessed
     • deduplication keys are wrong
     • soft-deleted records aren’t handled
   • Missing rows can happen if:
     • the sync watermark is advanced too early
     • late-arriving updates occur after extraction

6. Warehouse-side load issues

   • Snowflake COPY INTO may reject rows because of:
     • file format mismatch
     • invalid UTF-8 / special characters
     • incorrect delimiters or quoting
     • oversized records
   • Rows may be loaded but appear wrong due to VARIANT parsing or column order mismatches.

7. Timezone and timestamp handling

   • Salesforce timestamps are often UTC.
   • If transformed incorrectly, dates may shift by hours or appear in the wrong day.

8. Permission or object visibility issues

   • The integration user may not have access to all fields/objects in Salesforce.
   • Missing field-level security can make data look incomplete.

---

How to narrow it down quickly

Check these in order:

• Salesforce source

  • Is the record present in the API query?
  • Are there API limit errors or pagination issues?
  • Are deleted/updated records being captured?

• Pipeline logs

  • Any rejected rows?
  • Any mapping/type conversion warnings?
  • Is the job completing successfully but loading fewer rows?

• Snowflake

  • Compare row counts between source extract and target table.
  • Inspect load history:
    • COPY_HISTORY
    • LOAD_HISTORY
  • Check for rejected files/rows and error messages.

• Data comparison

  • Pick a few known records and trace them end-to-end.
  • Verify primary key, timestamps, and field values at each stage.

---

Typical “root cause” patterns

• Rows missing entirely → API/filter/watermark issue
• Rows duplicated → incremental/dedup logic issue
• Fields blank or incomplete → permission/schema drift/mapping issue
• Values altered → type conversion/timezone/encoding issue
• Load failures → Snowflake file format or parse issue

---

Best next step

If you want, I can help you diagnose it more precisely. Send me:

• the integration tool you’re using
• whether the problem is missing rows, duplicates, wrong field values, or load failures
• a sample Salesforce record and its Snowflake result
• any error message or load log output

And I’ll help pinpoint the likely cause.