Get Help

Test Databricks through ODBC

The ODBC route to Databricks: the Simba Spark ODBC driver on the machine, a token or a service principal, and Odbc@1. Keep it when your organisation standardises on the driver or an existing DSN; otherwise the native Databricks@1 provider is the recommended way — nothing to install, and a three-key change to move.

What you connect to

Any compute that can run SQL over your tables, through the same driver and the same connection string — only two values differ:

Compute Where to copy Server hostname and HTTP path
SQL warehouse — classic or serverless; the right choice for tests SQL → SQL Warehouses → the warehouse → Connection details tab
All-purpose cluster Compute → the cluster → Configuration → Advanced options → JDBC/ODBC tab

A warehouse that is stopped starts when CAT connects (and your run waits for it); a cluster must be running. Unity Catalog tables are addressed with three parts, catalog.schema.table; the driver’s Schema and Catalog keys, or a USE CATALOG at the start, set the default.

Prerequisites

  • The Databricks ODBC driver (Simba Spark ODBC Driver), 64-bit, on the machine where CAT runs — download, install, nothing to configure. On Linux and macOS the driver needs unixODBC as well.
  • A way to authenticate: a personal access token (User settings → Developer → Access tokens → Generate new token) for your own runs; for unattended runs a service principal with an OAuth secret, which the driver supports directly (below). Either way, the secret goes into an environment variable — never into the project file.

Add the data source

CAT Studio: Data sources → New data source, technology ODBC, paste the connection string (the dialog’s expert view takes it as is). In YAML, the DSN-less form — everything in the string, nothing to maintain on the machine beyond the driver, and the values that change come from environment variables:

Data sources:
- Name: lakehouse
  Provider: Odbc@1
  Connection string: >
    Driver=Simba Spark ODBC Driver;
    Host=%DATABRICKS_HOST%;
    Port=443;
    HTTPPath=%DATABRICKS_HTTP_PATH%;
    ThriftTransport=2;
    SSL=1;
    AuthMech=3;
    UID=token;
    PWD=%DATABRICKS_TOKEN%;

Data sources:
- Name: lakehouse
  Provider: Odbc@1
  Connection string: >
    Driver=Simba Spark ODBC Driver;
    Host=%DATABRICKS_HOST%;
    Port=443;
    HTTPPath=%DATABRICKS_HTTP_PATH%;
    ThriftTransport=2;
    SSL=1;
    AuthMech=11;
    Auth_Flow=1;
    Auth_Client_ID=%DATABRICKS_CLIENT_ID%;
    Auth_Client_Secret=%DATABRICKS_CLIENT_SECRET%;

The service principal’s application id and OAuth secret come from the workspace’s Identity and access → Service principals; the keys are the driver’s own — see Authentication settings for the Databricks ODBC driver.

When a DSN is how your organization does ODBC: create a 64-bit User DSN with the Simba Spark driver — host, port 443, HTTP path (under HTTP Options), SSL Options → Enable SSL, mechanism User Name and Password with user name token and the token as password — and point CAT at it:

Data sources:
- Name: lakehouse
  Provider: Odbc@1
  Connection string: DSN=LAKEHOUSE

A DSN holds the token on the machine and has to be edited when it expires — the reason the string form is the default here.

Only Odbc@1 and the driver are CAT’s; the keys are the driver’s and are documented by Databricks.

Write the tests

Name
Gold customers table is loaded
Suite
Smoke tests
Data source
lakehouse
Query
SELECT * FROM gold.dim.customer LIMIT 10
Expectation
set is not empty
Name
Silver orders equal the source system
Suite
Source vs lakehouse
First data source
erp
First query
SELECT order_id, amount FROM dbo.Orders WHERE status = ‘closed’ ORDER BY order_id
Second data source
lakehouse
Second query
SELECT order_id, amount FROM silver.sales.orders WHERE status = ‘closed’ ORDER BY order_id
Expectation
sets match
Key
order_id
Tests:
- Name: Gold customers table is loaded
  Suite: Smoke tests
  Data source: lakehouse
  Query: SELECT * FROM gold.dim.customer LIMIT 10
  Expectation: set is not empty

- Name: Silver orders equal the source system
  Suite: Source vs lakehouse
  First data source: erp
  First query: SELECT order_id, amount FROM dbo.Orders WHERE status = 'closed' ORDER BY order_id
  Second data source: lakehouse
  Second query: SELECT order_id, amount FROM silver.sales.orders WHERE status = 'closed' ORDER BY order_id
  Expectation: sets match
  Key: order_id

The tests that pay off on a lakehouse are the ones across its layers and across systems — exactly what a notebook inside the platform cannot see: the source system against bronze, silver against gold, gold against the Power BI model built on it (Test Power BI — both sides in one test). Both sides of a sets match ordered, or Sort data: true.

Generate tests from Unity Catalog

Unity Catalog’s information_schema is a ready-made metadata source: one template — “every table in silver has rows”, “every table has a _loaded_at column” — plus a metadata query over system.information_schema.tables or …columns gives you one test per table, refreshed on every open. See Generate tests from metadata.

Move to the native provider

The queries, the tests and the environment variables stay. Change the provider and three keys of the connection string, and the driver can be uninstalled:

ODBC (Odbc@1) Native (Databricks@1)
Host=… adbc.spark.host=…
HTTPPath=… adbc.spark.path=…
AuthMech=3; UID=token; PWD=… adbc.spark.auth_type=token; adbc.spark.token=…
AuthMech=11; Auth_Flow=1; Auth_Client_ID=…; Auth_Client_Secret=… adbc.spark.auth_type=oauth; adbc.databricks.oauth.grant_type=client_credentials; adbc.databricks.oauth.client_id=…; adbc.databricks.oauth.client_secret=…
Driver=…; Port=443; ThriftTransport=2; SSL=1 nothing — the native driver needs none of them 
Data sources:
- Name: lakehouse
  Provider: Databricks@1
  Technology: Databricks
  Connection string: >
    adbc.spark.host=%DATABRICKS_HOST%;
    adbc.spark.path=%DATABRICKS_HTTP_PATH%;
    adbc.spark.auth_type=token;
    adbc.spark.token=%DATABRICKS_TOKEN%;

In CAT Studio, pick technology Databricks for the data source and fill the guided form with the same values. Values arrive as the same .NET types either way — a sets match that passed through ODBC passes through the native provider; see Databricks@1 for the types table.

← Test Databricks
CAT in Databricks notebooks →