Skip to content

Databricks Setup Checklist

This checklist sets up a DataOps.live project to run against Databricks, with the Databricks orchestrator and the Databricks MATE orchestrator. The pipeline creates the project's catalogs and schemas. Everything else in Step 2 must exist before the first pipeline runs.

Setup checklist

  1. Set up the DataOps.live project and runner.
  2. Set up Databricks: the service principal, its OAuth secret, the SQL warehouse and the grants.
  3. Set up cloud storage access, if the project reads or writes files in cloud storage.
  4. Populate the vault with the service principal's credentials.
  5. Lay out the project and run the first pipeline.

Step 1 - Set up the DataOps.live project and runner

Create the project and install a runner as in Step 1 and Step 4 of the Snowflake setup checklist. The runner must be able to reach the workspace over HTTPS.

In pipelines/includes/config/variables.yml:

  • DATAOPS_PREFIX starts every catalog name: the catalog for an environment is <DATAOPS_PREFIX>_<environment>, for example DATAOPS_PROD.
  • DATAOPS_BRANCH_NAME_PROD must name your production branch. Projects created from the template project set it to main.

DATAOPS_BRANCH_NAME_PROD

When DATAOPS_BRANCH_NAME_PROD is unset, the reference project treats only a branch named master as production. On a main branch, production jobs still run, but they write to the feature-branch catalog <DATAOPS_PREFIX>_FB_MAIN, without any error.

Step 2 - Set up Databricks

The workspace must use Unity Catalog. The pipeline can't create the following:

Prerequisite Who creates it Detail
A service principal, added to the workspace Account admin, in the account console The identity every job runs as
An OAuth secret for the service principal, with the scopes sql and unity-catalog Account admin all-apis is not necessary. See Authentication
A SQL warehouse Workspace admin Its HTTP path, /sql/1.0/warehouses/<warehouse-id>, goes in the vault
CAN USE on the warehouse, for the service principal Workspace admin Granted on the warehouse's Permissions
CREATE CATALOG on the metastore, for the service principal Metastore admin So that Set Up Databricks can create the project's catalogs
Managed storage at the metastore level Account admin Needed because Set Up Databricks creates catalogs without a managed location. Workspaces enabled for Unity Catalog automatically have none
A lower STATEMENT_TIMEOUT Workspace admin See Limit how long a statement can run

Grant privileges to the service principal by its client id, also called its application id, not by its display name:

Allow the service principal to create catalogs
GRANT CREATE CATALOG ON METASTORE TO `<client-id>`;

Let the pipeline create the catalogs and schemas. Unity Catalog objects are owned by whoever creates them. A catalog that you create yourself, as a user, can't be written to by the service principal unless you grant it the privileges.

Authentication

Both orchestrators authenticate with OAuth machine-to-machine, exchanging the service principal's client id and secret for a token with only the sql and unity-catalog scopes at the start of each job or dbt command. Personal access tokens aren't used.

Limit how long a statement can run

When a job is cancelled or times out, its running statement isn't cancelled. The statement keeps running on the warehouse until the warehouse's STATEMENT_TIMEOUT, which defaults to two days. This applies to SQL jobs and to dbt's queries.

Lower it to the longest a pipeline statement should run, in the workspace's SQL configuration parameters or on the pipeline's warehouse; Databricks' STATEMENT_TIMEOUT page describes both. The Databricks orchestrator waits at most an hour for a statement, and each dbt command must finish within an hour, so STATEMENT_TIMEOUT 7200, two hours, leaves a margin.

Step 3 - Set up cloud storage access

Skip this step unless the project's SQL reads or writes files in cloud storage. Databricks reaches the storage through a Unity Catalog external location and its storage credential, which the pipeline can't create. Create them with your cloud provider's steps in the Databricks documentation. On Amazon S3, follow Create a storage credential and external location for S3 manually, and after you create the storage credential, update the IAM role's trust policy with the external id Databricks shows for it.

Then, on any cloud, grant the service principal access to the files on the external location:

The project's SQL Privileges on the external location
Reads files READ FILES
Writes files with INSERT OVERWRITE DIRECTORY READ FILES and WRITE FILES. The statement reads its target before writing, so WRITE FILES alone is refused
Grant file access to the service principal
GRANT READ FILES, WRITE FILES ON EXTERNAL LOCATION `<external-location>` TO `<client-id>`;

Step 4 - Populate the vault

Both the Databricks orchestrator and the Databricks MATE orchestrator read their credentials from the DATABRICKS section of the DataOps Vault:

DATABRICKS:
  HOST: dbc-xxxxxxxx-xxxx.cloud.databricks.com
  TRANSFORM:
    HTTP_PATH: /sql/1.0/warehouses/<warehouse-id>
    CLIENT_ID: <client-id>
    CLIENT_SECRET: <oauth-secret>
    THREADS: 8
  CONNECTION:
    HTTP_PATH: /sql/1.0/warehouses/<warehouse-id>
    CLIENT_ID: <client-id>
    CLIENT_SECRET: <oauth-secret>
Key Read by
HOST Both orchestrators. The workspace hostname, without https://
TRANSFORM.* The Databricks MATE orchestrator, for the dbt profile and its token. THREADS is dbt's thread count
CONNECTION.* The Databricks orchestrator, for any connection variable a job leaves unset

One service principal

TRANSFORM and CONNECTION must hold the same service principal. Unity Catalog objects are owned by the identity that creates them. With two service principals, the dbt job that creates a table in a schema the Set Up Databricks job created fails with PERMISSION_DENIED.

One way to fill the section is from masked CI/CD variables, through the project's vault-content/vault.template.yml. The Initialise Pipeline job renders it into the vault when DATAOPS_VAULT_CONTENT is set, which projects created from the template project already do. Without a secrets manager, also set SECRETS_MANAGER: NONE:

pipelines/includes/config/variables.yml
variables:
  ...
  DATAOPS_VAULT_CONTENT: $CI_PROJECT_DIR/vault-content/vault.yml
  SECRETS_MANAGER: NONE # only without a secrets manager

Projects created from the template project start with a SNOWFLAKE section in vault.template.yml. In a project that uses only Databricks, replace it with the DATABRICKS section below. Also remove or update the template project's other Snowflake files under dataops/. By default, jobs render every .template. file there before they run, even ones they don't use, and fail when a file reads a vault key that doesn't exist.

vault-content/vault.template.yml
DATABRICKS:
  HOST: "{{ env.VAULT_DATABRICKS_HOST }}"
  TRANSFORM:
    HTTP_PATH: "{{ env.VAULT_DATABRICKS_HTTP_PATH }}"
    CLIENT_ID: "{{ env.VAULT_DATABRICKS_CLIENT_ID }}"
    CLIENT_SECRET: "{{ env.VAULT_DATABRICKS_CLIENT_SECRET }}"
    THREADS: 8
  CONNECTION:
    HTTP_PATH: "{{ env.VAULT_DATABRICKS_HTTP_PATH }}"
    CLIENT_ID: "{{ env.VAULT_DATABRICKS_CLIENT_ID }}"
    CLIENT_SECRET: "{{ env.VAULT_DATABRICKS_CLIENT_SECRET }}"

Don't make the CI/CD variables protected, or feature-branch pipelines can't read them. Don't name them after a variable the orchestrators read, such as DATAOPS_DATABRICKS_HTTP_PATH: a project CI/CD variable overrides the same variable in every job, and the orchestrator then never reads the vault for it. An unset variable renders as an empty string, and the first job that needs the value fails, naming the vault key.

Step 5 - Lay out the project

A project created from the template project has most of these files already. Edit them as the table says, and create dataops/databricks/, which is new. Remove the template project's Snowflake pipelines, sole-ci.yml and mate-ci.yml.

File What to set
pipelines/includes/config/stages.yml Add Databricks Setup after Snowflake Setup. A project's stages.yml replaces the reference project's list, so without it GitLab rejects the pipeline, because the Set Up Databricks job's stage isn't defined
pipelines/includes/config/variables.yml DATAOPS_PREFIX and DATAOPS_BRANCH_NAME_PROD, as in Step 1. DATAOPS_VAULT_CONTENT and SECRETS_MANAGER, as in Step 4
vault-content/vault.template.yml The DATABRICKS section, as in Step 4
dataops/databricks/ The catalogs and schemas the Set Up Databricks job creates
dataops/modelling/ The dbt project
dataops/profiles/profiles.template.yml The dbt profile. Projects created from the template project have a Snowflake profile here, which you must replace or extend
pipelines/includes/local_includes/ The project's jobs

A pipeline file for a project that uses only Databricks includes the Set Up Databricks job from the reference project, and the project's own jobs:

full-ci.yml
include:
  - /pipelines/includes/bootstrap.yml

  ## Creates the catalogs and schemas in dataops/databricks
  - project: reference-template-projects/dataops-template/dataops-reference
    ref: 5-stable
    file: /pipelines/includes/default/databricks_lifecycle.yml

  ## MATE jobs that extend .databricks_modelling_and_transformation_base
  - /pipelines/includes/local_includes/mate_jobs/test_all_sources.yml
  - /pipelines/includes/local_includes/mate_jobs/build_all_models.yml
  - /pipelines/includes/local_includes/mate_jobs/test_all_models.yml

Don't include the reference project's Generate Docs job: the Databricks MATE orchestrator doesn't support TRANSFORM_ACTION: DOCS.

Then run full-ci.yml on the production branch. Set Up Databricks creates the production catalog if it doesn't exist, and the MATE jobs build the models in it.

A feature branch's catalog starts empty, so in this pipeline its source tests run against no tables. Either point the dbt sources at a fixed catalog with database:, or load or seed the data in the pipeline before the source tests.

Differences from Snowflake

Snowflake Databricks
A feature branch's database is a zero-copy clone of production A feature branch gets a new, empty catalog. A feature-branch pipeline has no data until it loads or seeds its own
SOLE manages databases, schemas, roles, warehouses, grants and more Set Up Databricks creates catalogs and schemas only. The warehouse, the service principal and every grant are set up by hand
SOLE can tear down a feature branch's objects Nothing is dropped. Drop a finished feature branch's catalog from a job, as the service principal
A user with a password or key pair, and a role for each vault section OAuth M2M, with one service principal for every job
A script of several statements runs as written Several statements must be one BEGIN ... END block, with ; after every statement. Only the last result is returned
COPY INTO a location can return the list of files it wrote INSERT OVERWRITE DIRECTORY deletes the files at its path first and returns nothing. Its JSON writer drops null keys unless OPTIONS ('ignoreNullFields' = 'false') is set. See the example
MATE runs dbt 1.7 by default, with every TRANSFORM_ACTION dbt 1.12, with RUN, TEST, SEED and COMPILE. See the differences
Unquoted names are stored in upper case Unity Catalog stores names in lower case: the catalog DATAOPS_PROD appears as dataops_prod

Troubleshooting

A missing OAuth scope and a missing Unity Catalog privilege both read as "not allowed", but they fail in different places and are fixed in different places:

Where it fails What the log shows Fix
Getting the token, before any SQL runs access_denied and Scopes '<scope>' are not assigned to the client An account admin adds the scope to the service principal's OAuth secret. Or the job requests a scope it doesn't need
Getting the token invalid_client The client id or secret in the vault is wrong
A statement, after the token is granted PERMISSION_DENIED: User does not have <privilege> on <object>, or INSUFFICIENT_PERMISSIONS The object's owner or a metastore admin grants the privilege to the service principal