Skip to main content
This guide connects canonic to a Databricks workspace from scratch: a service principal that can only read, an access token, a connection, and a first bootstrapped semantic layer with a query. Use a small schema while you follow it, so you do not touch real data by accident.
The Databricks connector needs the databricks extra: pip install 'canonic[databricks]'. Without it, a Databricks connection fails with an install hint instead of a stack trace.

​
What you need

  • A Databricks workspace with Unity Catalog and a SQL warehouse. A serverless warehouse is the cheapest way to start.
  • A catalog with at least one schema of tables or views to read.
  • Permission to create a service principal and grant privileges on the catalog.
A Databricks Free Edition workspace works for trying this out. The connector was tested there with a personal access token of the workspace user, against the default catalog workspace. Such a session runs as the owner of the data and could write, so only the parse guard stands between canonic and a write. Use a service principal with read-only grants for anything beyond a trial.

​
Why a service principal

Databricks has no read-only session flag and no role to switch to. A session runs as the principal that authenticated, so the safety of a connection is what that principal may do. canonic also parses every statement and refuses anything that is not a single SELECT, but the grants are the part Databricks itself enforces. canonic connection test passes with a warning that says as much. Connect as a dedicated service principal and grant it nothing beyond read access. Avoid your personal login for anything that runs unattended.
1

Create the service principal and a token

In the workspace, open Settings, then Identity and access, and add a service principal. Generate a personal access token for it. Copy the token once, Databricks does not show it again.
2

Grant read access only

Run this in a SQL editor as a user who owns the catalog, with your own names:
The service principal also needs Can use on the SQL warehouse. Set that under the warehouse’s permissions.
3

Find the connection details

Open the SQL warehouse and its Connection details tab. You need the Server hostname, for example dbc-1234abcd-5678.cloud.databricks.com, and the HTTP path, for example /sql/1.0/warehouses/abc123def456.
4

Export the token

canonic never stores a secret in canonic.yaml, only a reference to it. Set the token in your shell before you continue:
5

Add the connection

Run canonic setup and pick Databricks, or add the connection directly:
To limit introspection to some schemas, add schemas and tables to the connection’s params in canonic.yaml, see the config reference.
6

Test the connection

A passing test prints a warning that read-only rests on the SQL parse guard. That is expected. It goes away only for engines that enforce it themselves.

​
Naming and types

Unity Catalog identifiers are case-insensitive and stored in lower case. canonic quotes every identifier it emits, with backticks on Databricks, and introspection returns names exactly as Unity Catalog stores them, so sources generated from it work as is. Declared primary and foreign keys are informational in Databricks and optional. canonic reads them when your principal can see them, to help infer joins, and carries on without them when it cannot. ARRAY, MAP, STRUCT, VARIANT and BINARY columns are recorded as json. canonic emits PERCENTILE_CONT as the exact ordered-set aggregate and does not approximate it.

​
The samples catalog

Introspection reads each catalog’s own information_schema. In the built-in samples catalog that view lists the tables of schemas such as tpch but returns columns for only some of them, so canonic finds no usable relations there. Copy the tables you want into a schema of your own catalog, for example with CREATE TABLE workspace.demo.orders AS SELECT * FROM samples.tpch.orders, and point the connection at that catalog.

​
Authoring expr for Databricks

canonic does not transpile the expr strings you write in metrics, dimensions and filters. Write them in Databricks SQL, for example date_trunc('MONTH', created_at) and try_cast(amount AS DECIMAL(18, 2)).

​
Limits

This version authenticates with an access token only. OAuth machine-to-machine authentication is not supported yet. Result sets are capped by row_limit (default 10,000) and every statement runs under statement_timeout_ms (default 30,000).