How to Query Databricks from Salesforce Apex (Without Copying a Billion Rows)

The problem

We had a reference dataset in Databricks with 1B+ rows. Users needed to look up a handful of those rows from a Salesforce record page.

The obvious idea — sync it into a Salesforce custom object — falls apart fast:

• Salesforce data storage is priced for business records, not reference datasets
• The source refreshed monthly, so we'd re-load a billion rows every month
• The copy immediately starts drifting from the source

So instead: query Databricks on demand. The user enters a key, we fetch the few matching rows over HTTP, display them, and store a small snapshot on the record.

This post covers exactly how to wire that up — and the four gotchas that aren't in the docs.

Architecture

Lightning Web Component  (user enters a search key)
  → Apex Controller
    → Apex Service
      → Databricks Client ──HTTPS──▶  Databricks SQL Statement Execution API
                            ◀── JSON rows

One important rule up front: all filtering happens in Databricks. Apex has a 6 MB
heap limit, so pulling rows into Salesforce to filter them there fails almost immediately. Push the WHERE clause down.

Why HTTP (and not an SDK)

Databricks ships SDKs for Python, Java, Go, and JS. You can't use any of them from Apex — Salesforce doesn't allow third-party libraries.

That costs you nothing. Every Databricks SDK is a wrapper around the same REST API.
POST /api/2.0/sql/statements is what the SDK sends anyway. You're doing by hand what the SDK does for you.

Step 1 — Collect three values from Databricks

You need exactly three things:

💡 Tip: if someone hands you a JDBC/Python connection string, the warehouse ID is the last segment of the HTTP path:

http_path = "/sql/1.0/warehouses/abc123def456"
                                  ^^^^^^^^^^^^ this is your warehouse ID

For production, use a service principal token, not a personal access token. A PAT expires and dies with the person who created it.

Step 2 — Create the External Credential

In Salesforce Setup → Named Credentials → External Credentials → New:

• Label / Name: Databricks
• Authentication Protocol: Custom

Add a Principal:

• Parameter Name: Sandbox (or Production)
• Identity Type: Named Principal
• Authentication Parameter: Name Token, Value = your Databricks token

Add a Custom Header:

The Token in that formula must match your authentication parameter name exactly, including case.

Step 3 — Create the Named Credential

Setup → Named Credentials → New:

• Label: Databricks Sandbox
• Name: Databricks_Sandbox
• URL: your workspace URL (no path)
• External Credential: Databricks

⚠️ Gotcha #1: Allow Formulas in HTTP Header

Check the "Allow Formulas in HTTP Header" box.

If you don't, Salesforce sends the literal string Bearer {!$Credential...} instead of resolving it, and Databricks returns 403. The error gives you no hint that a checkbox is the cause. This one cost me an hour.

Step 4 — Grant principal access

Create a permission set with External Credential Principal Access for your principal (e.g. Databricks-Sandbox), and assign it to the running user.

Without this, callouts fail even though the credential looks correctly configured.

Step 5 — The Apex client

The API is asynchronous. You POST a statement; if the warehouse is cold, you get back PENDING and a statement_id to poll.

public with sharing class DatabricksClient {
  private static final String STATEMENTS_PATH = '/api/2.0/sql/statements';
  private static final String WAIT_TIMEOUT = '50s';
  private static final Integer MAX_POLLS = 15;

  private final String endpoint;      // 'callout:Databricks_Sandbox'
  private final String warehouseId;
  private final String tableName;

  public DatabricksClient(String endpoint, String warehouseId, String tableName) {
    this.endpoint = endpoint;
    this.warehouseId = warehouseId;
    this.tableName = tableName;
  }

  public List<Map<String, Object>> query(String keyA, String keyB) {
    HttpResponse response = post(buildBody(keyA, keyB));
    Map<String, Object> payload =
      (Map<String, Object>) JSON.deserializeUntyped(response.getBody());

    payload = awaitResult(payload);
    return toRows(payload);
  }

  private String buildBody(String keyA, String keyB) {
    return JSON.serialize(new Map<String, Object>{
      'warehouse_id' => warehouseId,
      'statement' =>
        'SELECT col_a, col_b, amount FROM ' + tableName +
        ' WHERE key_a = :keyA AND key_b = :keyB',
      'parameters' => new List<Object>{
        new Map<String, Object>{ 'name' => 'keyA', 'value' => keyA },
        new Map<String, Object>{ 'name' => 'keyB', 'value' => keyB }
      },
      'wait_timeout' => WAIT_TIMEOUT
    });
  }

  private HttpResponse post(String body) {
    HttpRequest request = new HttpRequest();
    request.setEndpoint(endpoint + STATEMENTS_PATH);
    request.setMethod('POST');
    request.setHeader('Content-Type', 'application/json');
    request.setTimeout(60000);
    request.setBody(body);
    return new Http().send(request);
  }
}

Always use bound parameters

Notice the parameters array. Never concatenate user input into the SQL string:

// ❌ SQL injection
'... WHERE key_a = \'' + userInput + '\''

// ✅ bound parameter
'parameters' => new List<Object>{
  new Map<String, Object>{ 'name' => 'keyA', 'value' => userInput }
}

Parsing the response

Databricks returns columns and rows separately. Build a name→index map from the manifest so column order changes can't silently break you:

private Map<String, Integer> columnIndex(Map<String, Object> payload) {
  Map<String, Integer> index = new Map<String, Integer>();
  Map<String, Object> manifest = (Map<String, Object>) payload.get('manifest');
  Map<String, Object> schema = (Map<String, Object>) manifest.get('schema');
  List<Object> columns = (List<Object>) schema.get('columns');

  for (Integer i = 0; i < columns.size(); i++) {
    Map<String, Object> column = (Map<String, Object>) columns[i];
    index.put((String) column.get('name'), i);
  }
  return index;
}

Rows then live in result.data_array as arrays of strings — everything comes back as
a string, so cast explicitly (Decimal.valueOf, Date.valueOf).

The gotchas nobody tells you

⚠️ Gotcha #2: wait_timeout maxes out at 50 seconds

The valid range is 5–50 seconds (default 10). Use 50s to minimise polling.

⚠️ Gotcha #3: Apex has no sleep() — so 50s is your real ceiling

This is the subtle one. Apex cannot pause between polls. Your polling loop fires all its requests back-to-back in a couple of seconds and gives up — it can't actually wait for a warehouse to finish starting.

So the only place Salesforce can genuinely wait is Databricks holding the initial POST open, capped at 50s. Apex's own callout budget (120s per callout) is irrelevant here.

If a query doesn't finish in 50 seconds, your user gets an error. Which leads to…

⚠️ Gotcha #4: cluster your table, or point lookups will time out

Querying a few rows from a billion-row Delta table only works if Databricks can skip the irrelevant files. That's what clustering does:

ALTER TABLE my_catalog.my_schema.my_table
  CLUSTER BY (key_a, key_b);

OPTIMIZE my_catalog.my_schema.my_table;

ALTER TABLE only records the rule — OPTIMIZE does the actual reorganisation. Run only the first and nothing gets faster.

Delta stores min/max stats per file, so a clustered lookup reads a few MB instead of the whole table — and stays roughly flat whether the table holds 1M or 1B rows.

Two follow-ups:

• Re-run OPTIMIZE after each bulk load, or new unclustered rows degrade lookups over time. Better: enable Predictive Optimization.
• Don't partition on a high-cardinality column. ~41k distinct values = ~41k tiny fragments, and per-file overhead makes reads slower.

Cold starts are real

A serverless SQL warehouse that has idled out takes 10–30 seconds to wake. Options:

• Set auto-stop to 15–30 min so normal traffic keeps it warm
• Tell users the first search of the day may be slower

Result size limits

Salesforce is ~4× tighter than Databricks, so you'll hit the heap limit long before the API limit. Heap use also multiplies as you deserialize. Keep result sets small — which is the whole point of pushing filters down.

Testing

Inject an HTTP sender so tests never make real callouts:

@IsTest
private static void query_parsesResponse() {
  Test.setMock(HttpCalloutMock.class, new MyMock());

  Test.startTest();
  List<Map<String, Object>> rows = newClient().query('a', 'b');
  Test.stopTest();

  Assert.areEqual(2, rows.size());
}

Worth covering explicitly:

• A SUCCEEDED response parses correctly
• A PENDING response triggers a poll
• A non-200 throws
• A FAILED statement state throws (note: HTTP 200 but the query failed — these are different failure modes and deserve different exception types)

Wrapping up

The integration itself is ~150 lines of Apex. The hard parts were never the code — they were:

1. The "Allow Formulas in HTTP Header" checkbox
2. Apex having no sleep(), making 50s the real ceiling
3. Clustering being mandatory, not an optimisation
4. Remembering the 6 MB heap, not the 25 MiB API limit, is what binds

If you keep filtering in Databricks and your table clustered, point lookups land in 1–2 seconds and stay there as the table grows.

Have you built something similar? I'd be curious whether you went direct like this or put middleware in between — drop a comment.