Reverse ETL: warehouse → Salesforce¶
lht retl runs a SELECT against your warehouse and writes the rows to a Salesforce object through a Bulk API 2.0 ingest job. Use it to push lead scores, enrichment, cleaned addresses, account hierarchies, or any model output back to where your sales team works.
lht retl upsert --sobject Account --match-field External_Id__c \
--sql "SELECT External_Id__c, Rating, AnnualRevenue FROM ANALYTICS.ACCOUNT_SCORES"
Snowflake is the default. Add --databricks [PROFILE] to read from Databricks instead; everything on this page applies to both. In a notebook, use the Python API — see Databricks.
Operations¶
| Operation | Required columns | Notes |
|---|---|---|
upsert |
The --match-field column (an External ID field, or Id) plus the fields to set |
Creates records whose match value isn't found. A blank match value always creates a record. |
update |
Id plus the fields to set |
|
insert |
Fields to set | |
delete |
Id |
Soft delete: records go to the Recycle Bin |
Column names in the query must be Salesforce field API names (Industry, My_Field__c). Snowflake folds unquoted names to uppercase and Spark SQL keeps your aliases as written; Salesforce matches field names case-insensitively either way, so SELECT industry works on both.
NULL handling¶
By default a NULL becomes an empty CSV cell, which Salesforce treats as "don't change this field". To blank a field, pass --clear-nulls: lht then sends #N/A, which clears it. The match field itself is never turned into #N/A.
Batching¶
upsert reads the query once and sends it in batches of --batch-size rows (default 25,000). Each batch is its own Bulk API ingest job.
Results and logging¶
lht get-job-results JOB_IDdownloads the successful, failed and unprocessed CSVs for any ingest job.--log-resultsrecords job metadata and per-record outcomes in aLOGSschema in the warehouse (RETL_HISTORY,JOB_INFO,SUCCESS,FAILURE), created on first use — as Snowflake tables or Delta tables, depending on the warehouse. Pass--match-fieldand each row records which source record produced which Salesforce Id. Logging is best-effort: a logging failure never aborts the load.--log-schemaputs those tables somewhere other than a bareLOGS, and covers all of them: the job history (RETL_HISTORY, written on every run) as well as the per-record tables. Worth setting on Databricks, where an unqualified name resolves against whichever catalog the session points at:--log-schema main.lht_logs. The schema is created on first use if it does not exist.
Tips¶
- Keep reverse-ETL queries deterministic, and include only the rows that actually changed. That saves API usage and avoids noisy field history. It also keeps memory down: the query is read once and held while it is cut into batches, because re-reading it per batch without an
ORDER BYcould send a row twice or skip it. - Try new jobs in a sandbox first. Salesforce validation rules, triggers and flows all run on Bulk API writes.
- Use
--sql-filefor anything longer than a line, and keep those files in version control next to your dbt models.