Skip to content
  • There are no suggestions because the search field is empty.

Databricks dbt - Target environment

Applies to
Platform: Databricks dbt

Prepare a Databricks workspace and a dbt installation to run the dbt project that a Databricks dbt generator creates. Do this once per environment, before the first run. Afterwards dbt debug connects from the artifacts folder to your workspace.

A Databricks dbt project has no deployment scripts and no load control: dbt creates and loads the tables itself. The native Databricks generators use the environment described in Databricks - Target environment with Unity Catalog instead.

Prerequisites

  • A Databricks workspace with Unity Catalog and a catalog for the target tables. The generated materializations read the columns and constraints of the target tables from information_schema of this catalog.
  • The source tables in a catalog and schema of the workspace. To create the source model objects from them, see Discover a Databricks Unity Catalog.
  • An identity for dbt, for example a personal access token of a user or a service principal. It creates schemas and tables in the target catalog and reads the source tables.
  • Python with pip on the machine that runs dbt.
  • With Apply Sensitivity Level Tags on: the tag exists in Unity Catalog. The generator does not create it.

Runtime requirements of the generated statements, for example for unique constraints, are listed in Generators Release - 2.4.0 and Use Unique Constraints (Project).

Grant the privileges

In Unity Catalog the identity that creates a schema, table or view owns it and can run every statement on it. Adding a constraint needs ownership of the table; MODIFY and MANAGE are not enough. Replacing a table or view, and dropping a table in a full refresh, need ownership or MANAGE. Let the dbt identity create the tables and views it loads.

When the dbt identity creates the layer schemas, grant it these privileges. The values in angle brackets are the names of your catalogs, schemas and dbt identity; for a service principal, <identity> is its application ID.

GRANT USE CATALOG, CREATE SCHEMA ON CATALOG <target catalog>
  TO `<identity>`;
GRANT USE CATALOG ON CATALOG <source catalog> TO `<identity>`;
GRANT USE SCHEMA, SELECT ON SCHEMA <source catalog>.<source schema>
  TO `<identity>`;

When an administrator creates the layer schemas <profile schema>_<layer schema> in advance, grant these privileges for each layer schema instead of CREATE SCHEMA, plus the source privileges:

GRANT USE CATALOG ON CATALOG <target catalog> TO `<identity>`;
GRANT USE SCHEMA, CREATE TABLE
  ON SCHEMA <target catalog>.<profile schema>_<layer schema>
  TO `<identity>`;

Tables and views that another identity created stay owned by that identity, for example after you switch from a personal access token to a service principal. Their models then fail when dbt adds a constraint, replaces the object or runs a full refresh. Transfer the ownership to the dbt identity:

ALTER TABLE <table> OWNER TO `<identity>`;
ALTER VIEW <view> OWNER TO `<identity>`;

The dbt identity also needs access to the compute in http_path: CAN USE on a SQL warehouse, or CAN ATTACH TO on an all-purpose cluster (CAN RESTART if dbt has to start it). With Apply Sensitivity Level Tags on, it also needs ASSIGN on the governed tag.

Install dbt

  1. Run pip install dbt-databricks. pip installs the adapter dbt-databricks together with dbt Core.
  2. Run dbt --version. The output lists the version of Core and, under Plugins, the version of databricks.

Verified with dbt Core 1.12.5 and dbt-databricks 1.13.0, on a SQL warehouse and on an all-purpose cluster with Databricks Runtime 18.2 and 19. dbt 2 is not supported with Databricks dbt.

Create the connection profile

  1. Open profiles.yml in the .dbt folder of your user profile, or create it there.
  2. Add a profile with type: databricks and the values below. The Databricks setup page of the dbt documentation lists the other authentication methods.
  3. Enter the profile name as profile in dbt_project.yml of the artifacts, together with the other values of dbt - Placeholder values.

The values of the profile:

  • host: the host name of the workspace, without https://.
  • http_path: the HTTP path of the Databricks compute that runs the SQL, from its connection details. On a cluster with Databricks Runtime 17.3 LTS, the Hub, Link and Mart Dimension models fail with [UNIQUE_CONSTRAINT_DISABLED] Unique constraint feature is disabled. while Use Unique Constraints is on.
  • token: the access token of the identity.
  • catalog: the target catalog.
  • schema: the first part of every target schema. dbt creates the models of a layer in the schema <profile schema>_<layer schema>, for example analytics_rawvault.
  • threads: the number of models that dbt runs at the same time.

Example

A profile dwh for the target catalog dwh_dev. The values in angle brackets are those of your workspace:

dwh:
  target: dev
  outputs:
    dev:
      type: databricks
      host: <workspace host>
      http_path: <http path>
      token: <access token>
      catalog: dwh_dev
      schema: analytics
      threads: 4

With profile: 'dwh' and the layer schema rawvault in dbt_project.yml, dbt writes the Hub common_customer to `dwh_dev`.`analytics_rawvault`.`rdv_hub_common_customer`.

Check the result

This check needs the downloaded artifacts with profile set, see dbt - Placeholder values. Open a terminal in the artifacts folder, the folder that contains dbt_project.yml, and run dbt debug. dbt checks profiles.yml, dbt_project.yml and the connection. The output ends with:

All checks passed!

Failure symptoms and recovery

dbt does not find the profile

Message: Could not find profile named '<profile name>'
Cause: profile in dbt_project.yml names a profile that profiles.yml does not contain. In downloaded artifacts it is <placeholder>.
Fix: Enter the name of your profile as profile in dbt_project.yml and run dbt debug again.

The connection test fails

Message: Connection test: [ERROR], followed by Invalid access token or ENDPOINT_NOT_FOUND: SQL warehouse (<workspace id>, <warehouse id>) does not exist at all in the database
Cause: token in profiles.yml is not valid, or http_path names a SQL warehouse that does not exist.
Fix: Correct the value in profiles.yml and run dbt debug again.

For failures in other steps, see Troubleshoot generation, deployment and load failures.

Related articles