Skip to content

Python API

Everything the CLI does is available as plain Python functions. Use the API from notebooks, Airflow or Dagster tasks, Snowflake Python procedures, or your own scripts.

Sessions and access tokens

from lht.user.auth import create_session
from lht.user.salesforce_auth import get_salesforce_access_info

session = create_session(connection_name="my_snowflake")        # snowflake.snowpark.Session
access_info = get_salesforce_access_info("my_salesforce")       # {"access_token": ..., "instance_url": ...}

Pass no name to use the primary connection of each type.

Warehouses

Everywhere below, the first argument named session takes any of three things:

Pass Where it comes from
A Snowpark Session lht.user.auth.create_session()
A Spark session A Databricks notebook's spark, or lht.warehouse.databricks.connect()
An lht.warehouse.Warehouse lht.warehouse.from_session(...), or connect() on Databricks

lht recognises which it has and spells its SQL accordingly, so the reverse-ETL and merge calls are identical on both warehouses:

from lht.warehouse import from_session

warehouse = from_session(spark)        # or from_session(snowpark_session)
warehouse.name                         # 'databricks' | 'snowflake'
warehouse.rows("SELECT 1 AS n")        # [{'n': 1}] -- plain dicts, either way

Sync (Salesforce → warehouse) is Snowflake-only and needs a Snowpark session. See Databricks for what runs there and how to get a session.

Headless credentials

In CI or an orchestrator, read secrets from your secret manager and pass them in directly. Nothing touches a local config directory.

import os
from lht.user.auth import create_session
from lht.user.salesforce_auth import get_salesforce_access_info_from_credentials

session = create_session({
    "account": os.environ["SNOWFLAKE_ACCOUNT"],
    "user": os.environ["SNOWFLAKE_USER"],
    "role": "LHT_ROLE",
    "warehouse": "COMPUTE_WH",
    "database": "SALESFORCE",
    "schema": "RAW",
    "private_key_file": "/run/secrets/snowflake_key.p8",
    "private_key_passphrase": os.environ.get("SNOWFLAKE_KEY_PASSPHRASE"),
})

# Client Credentials flow
access_info = get_salesforce_access_info_from_credentials({
    "auth_flow": "client_credentials",
    "client_id": os.environ["SF_CLIENT_ID"],
    "client_key": os.environ["SF_CLIENT_SECRET"],
    "my_domain": "acme",                      # or "acme--dev.sandbox"
})

# ...or JWT Bearer flow
access_info = get_salesforce_access_info_from_credentials({
    "auth_flow": "jwt_bearer",
    "client_id": os.environ["SF_CLIENT_ID"],
    "username": "integration@acme.com",
    "private_key_pem": os.environ["SF_PRIVATE_KEY"],
    "sandbox": False,
})

Registering credentials under a name, without a file

The headless pattern above works well when a single call site builds the session. When several call sites need the same connection by name — the way connection_name="my_snowflake" works against connections.toml — register it once instead of threading the credentials dict everywhere:

import os
from lht.user.connections import register_connection
from lht.user.auth import create_session

register_connection("prod_snowflake", {
    "connection_type": "snowflake",
    "account": os.environ["SNOWFLAKE_ACCOUNT"],
    "user": os.environ["SNOWFLAKE_USER"],
    "role": "LHT_ROLE",
    "warehouse": "COMPUTE_WH",
    "private_key_file": "/run/secrets/snowflake_key.p8",
})

# Anywhere else in this process, by name -- same as a connections.toml entry:
session = create_session(connection_name="prod_snowflake")

register_connection takes precedence over a same-named entry in connections.toml if one exists, and never touches the file — safe to call even when no connections.toml is present at all. It's process-local and not persisted; call it again in any new process that needs it. Pair it with unregister_connection(name) if a long-running process needs to clear or rotate a registered connection without restarting.

Sync Salesforce → Snowflake

from lht.salesforce.intelligent_sync import sync_sobject_intelligent

result = sync_sobject_intelligent(
    session=session,
    access_info=access_info,
    sobject="Account",
    schema="RAW",
    table="ACCOUNT",
    match_field="ID",            # merge key (default)
    where_clause=None,           # e.g. "IsPersonAccount = false"
    force_full_sync=False,       # True rebuilds the table
    use_stage=False,             # True + stage_name loads through a Snowflake stage
    stage_name=None,
    delete_job=True,             # delete the Bulk API job when done
)

result is a dict:

{
    "sobject": "Account",
    "target_table": "RAW.ACCOUNT",
    "sync_method": "bulk_api_incremental",   # or bulk_api_full, bulk_api_stage_*
    "estimated_records": 1500,
    "actual_records": 1487,
    "sync_duration_seconds": 45.2,
    "last_modified_date": Timestamp("2026-01-15 10:30:00"),
    "sync_timestamp": Timestamp("2026-01-16 14:20:00"),
    "success": True,
    "error": None,
}

To sync several objects in a loop:

for sobject in ["Account", "Contact", "Opportunity", "Case"]:
    r = sync_sobject_intelligent(session, access_info, sobject, schema="RAW", table=sobject.upper())
    print(f"{sobject}: {r['actual_records']} rows via {r['sync_method']}")

Reverse ETL: warehouse → Salesforce

Works on Snowflake and Databricks. session here is whichever warehouse you opened above — the call does not change.

from lht.salesforce import retl

# Column names must be Salesforce field API names.
retl.upsert(session, access_info, sobject="Account",
            query="SELECT External_Id__c, Rating, Industry FROM ANALYTICS.ACCOUNT_SCORES",
            field="External_Id__c", batch_size=25000, clear_nulls=False)

retl.update(session, access_info, sobject="Contact",
            query="SELECT Id, Title FROM STAGE.CONTACT_FIXES", clear_nulls=True)

retl.insert(session, access_info, sobject="Task",
            query="SELECT WhoId, Subject, ActivityDate FROM STAGE.NEW_TASKS")

retl.delete(session, access_info, sobject="Lead",
            query="SELECT Id FROM STAGE.LEADS_TO_DELETE")

clear_nulls=True sends NULL as #N/A, which clears the field in Salesforce. Without it, a NULL is sent as an empty cell, which Salesforce treats as "leave unchanged".

upsert returns a summary (total_records, records_processed, total_batches, successful_batches, failed_batches, batch_results), or None when the query returned no rows. The other three return the Bulk API's job-creation response.

Logging per-record outcomes

from lht.salesforce.results_bapi import process_bulk_api_results

result = retl.upsert(session, access_info, "Account", query, "External_Id__c")
for batch in result["batch_results"]:
    if batch.get("job_id"):
        process_bulk_api_results(session, access_info, batch["job_id"],
                                 match_field="External_Id__c",
                                 schema="LOGS")        # main.lht_logs on Databricks

That writes JOB_INFO, SUCCESS and FAILURE in the given schema, creating them on first use with the right column types for the backend. Passing match_field is what records which source record produced which Salesforce Id. It is best-effort: a logging failure never fails the load, and never raises.

Because it never raises, check what it returns: {"job_id", "success_count", "failure_count", "job_state", "success"}, plus "error" when success is False. success is True only if everything was saved. When it isn't, error names each thing that couldn't be (one short line per problem, without Spark's stack trace), so a log that wasn't written can't pass for one that was.

outcome = process_bulk_api_results(session, access_info, job_id,
                                   match_field="External_Id__c", schema="LOGS")
if not outcome["success"]:
    print(outcome["error"])

Every row in SUCCESS is a record Salesforce accepted, so its SUCCESS column is TRUE; CREATED says whether it was created or updated.

schema defaults to a bare LOGS, which resolves against whatever database or catalog the session points at. On Unity Catalog, qualify it.

The job history (RETL_HISTORY) is written by the retl.* call itself, on every run, and takes its own log_schema= (retl.upsert(..., log_schema="main.lht_logs"); the same keyword on update, insert and delete). Pass the same value to both to keep every log table together.

Merge records

from lht.salesforce.merge import merge

summary = merge(session, access_info, sobject="Account",
                query="SELECT MasterId, LoserId FROM DEDUPE.ACCOUNT_PAIRS",
                dry_run=True)

The merge happens inside Salesforce over SOAP, so this works on either warehouse — all the warehouse does is answer the query.

See Merging records for the validation rules.

Bulk API 2.0 jobs

from lht.salesforce import jobs

jobs.list_bulk_api_jobs(access_info)
jobs.get_bulk_api_job(access_info, "750xx000000abcDAAQ")
jobs.get_ingest_job_results(access_info, "750xx000000abcDAAQ")   # successful / failed / unprocessed CSVs
jobs.delete_bulk_api_job(access_info, "750xx000000abcDAAQ")