---
title: "Test Databricks"
description: "Test data in Databricks — all-purpose clusters, SQL warehouses, serverless — through the Simba ODBC driver: driver, token, connection string, first test, tests generated from Unity Catalog"
url: "https://docs.justcat.it/how-to-guides/data-platforms/test-databricks/"
---
# Test Databricks


## 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 `Schema`/`Catalog` keys of the driver, 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 — <a href="https://www.databricks.com/spark/odbc-drivers-download" target="_blank">download</a>, install, nothing to configure.
* 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, and the values that change come from environment variables:


**Personal access token**


```yaml
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%;
```


**Service principal (OAuth M2M)**


```yaml
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 <a href="https://docs.databricks.com/aws/en/integrations/odbc/authentication" target="_blank">Authentication settings for the Databricks ODBC driver</a>.


**DSN**


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:

```yaml
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


**Properties**



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





**YAML**


```yaml
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](https://docs.justcat.it/how-to-guides/data-platforms/power-bi/test-power-bi/ "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](https://docs.justcat.it/reference/tests/templates/ "Templates") — "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](https://docs.justcat.it/how-to-guides/organize-and-run-tests/generate-tests-from-metadata/ "Generate tests from metadata").

## Related

* [CAT in Databricks notebooks](https://docs.justcat.it/how-to-guides/data-platforms/cat-in-databricks-notebooks/ "CAT in Databricks notebooks") — when you want to run CAT *inside* Databricks, and why usually not.
* [Odbc@1](https://docs.justcat.it/reference/data-sources/providers/odbc-1/ "Odbc@1") — the provider.
* [Work with secrets](https://docs.justcat.it/how-to-guides/organize-and-run-tests/work-with-secrets/ "Work with secrets") — the token and the secret.