Get Help

Test Databricks

CAT tests Delta tables through the Databricks ODBC driver, whatever compute serves them — an all-purpose cluster, a SQL warehouse, serverless. One driver install, a token, a connection string, and the tests that pay off: source against bronze, silver against gold, the lakehouse against Power BI.

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 — download, 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:

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 

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.

← DAX for SQL people
CAT in Databricks notebooks →