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_schemaof 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
- Run
pip install dbt-databricks. pip installs the adapter dbt-databricks together with dbt Core. - Run
dbt --version. The output lists the version of Core and, under Plugins, the version ofdatabricks.
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
- Open
profiles.ymlin the.dbtfolder of your user profile, or create it there. - Add a profile with
type: databricksand the values below. The Databricks setup page of the dbt documentation lists the other authentication methods. - Enter the profile name as
profileindbt_project.ymlof the artifacts, together with the other values of dbt - Placeholder values.
The values of the profile:
host: the host name of the workspace, withouthttps://.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 exampleanalytics_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
- Next: Generate artifacts, Download generated artifacts, dbt - Placeholder values, dbt - Deployment
- Reference: Databricks dbt Data Vault, Use Unique Constraints (Project), Apply Sensitivity Level Tags (Project), Databricks dbt Data Mart, Databricks dbt Data Vault and Mart
- See also: dbt - Artifacts, Discover a Databricks Unity Catalog, Databricks - Target environment with Unity Catalog, Generators Release - 2.4.0