(Applies to dbt v2.0 and later)

# Connect Databricks to dbt v2

Local development

You can configure the Databricks adapter by running `dbt init` in your CLI or manually providing the `profiles.yml` file with the fields configured for your authentication type.

The Databricks adapter for dbt v2 supports the following [authentication methods](#supported-authentication-types):

* Personal access token (for individual users)
* Service Principal token (for service users)
* OAuth
* OAuth token federation using OIDC

## Warehouse permissions for dbt v2

The Databricks user or service principal that dbt v2 uses must have privileges on the catalog and schemas where models run, plus access required for metadata queries. Requirements depend on whether you use Unity Catalog or the legacy Hive Metastore.

### Required Databricks objects

Before connecting, these objects must exist or be accessible:

| Object                        | Purpose                                 |
| ----------------------------- | --------------------------------------- |
| **Workspace**                 | Your Databricks workspace URL (host)    |
| **SQL warehouse or cluster**  | Compute resource (using `http_path`)    |
| **Catalog**                   | Unity Catalog or Hive Metastore catalog |
| **Schema**                    | Target schema within the catalog        |
| **User or service principal** | Identity for authentication             |

### Unity Catalog

Required access for the Unity Catalog:

| Permission      | Object          | Purpose                              |
| --------------- | --------------- | ------------------------------------ |
| `USE CATALOG`   | Catalog         | Access the catalog                   |
| `USE SCHEMA`    | Schema          | Access schemas                       |
| `SELECT`        | Tables or views | Read existing data and sources       |
| `CREATE TABLE`  | Schema          | Create models materialized as tables |
| `CREATE VIEW`   | Schema          | Create models materialized as views  |
| `MODIFY`        | Tables          | Insert, update, and delete data      |
| `CREATE SCHEMA` | Catalog         | Auto-create schemas (if needed)      |

### Hive Metastore

Required access for the legacy Hive Metastore:

| Permission | Object   | Purpose                 |
| ---------- | -------- | ----------------------- |
| `USAGE`    | Database | Access the database     |
| `SELECT`   | Tables   | Read data               |
| `CREATE`   | Database | Create tables and views |
| `MODIFY`   | Tables   | Modify data             |

### Metadata operations

The following are required for fundamental dbt features:

| Query type                   | SQL used                                             | Required permission        |
| ---------------------------- | ---------------------------------------------------- | -------------------------- |
| Get table schema             | `DESCRIBE TABLE` or `DESCRIBE TABLE EXTENDED`        | SELECT on table            |
| Get table schema (DBR 16.2+) | `DESCRIBE TABLE EXTENDED ... AS JSON`                | SELECT on table            |
| List relations               | Query `INFORMATION_SCHEMA.TABLES`                    | USE CATALOG and USE SCHEMA |
| Source freshness             | Query `INFORMATION_SCHEMA.TABLES` for `last_altered` | USE CATALOG                |
| Get view definition          | Query `SYSTEM.INFORMATION_SCHEMA.VIEWS`              | Access to system catalog   |
| Create schemas               | `CREATE SCHEMA IF NOT EXISTS`                        | CREATE SCHEMA on catalog   |

### Python models

Optional permissions for environments using Python models

| Permission           | Object                 | Purpose                     |
| -------------------- | ---------------------- | --------------------------- |
| Workspace API access | `/api/2.0/workspace/*` | Create notebook directories |
| Notebook import      | Workspace              | Import Python notebooks     |
| Job execution        | Cluster or warehouse   | Run Python models           |

## Databricks configuration details

The dbt v2 `dbt-databricks` adapter is the only supported connection method for Databricks.

`dbt-databricks` can connect to Databricks SQL Warehouses. These warehouses are the recommended way to get started with Databricks.

Refer to the [Databricks docs](https://docs.databricks.com/dev-tools/dbt.html#) for more info on how to obtain the credentials for configuring your profile.

## Configure dbt v2

Executing `dbt init` in your CLI will prompt for the following fields:

* **Host:** Databricks instance hostname (excluding the `http` or `https` prefix)
* **HTTP Path:** Path to your SQL server or cluster
* **Schema:** The development/staging/deployment schema for the project
* **Catalog (Optional):** The Databricks catalog containing your schemas and tables

Alternatively, you can manually create the `profiles.yml` file and configure the fields. See examples in [authentication](#supported-authentication-types) section for formatting. If there is an existing `profiles.yml` file, you are given the option to retain the existing fields or overwrite them.

Next, select your authentication method. Follow the on-screen prompts to provide the required information.

## Supported authentication types

### Personal access token

Enter your personal access token (PAT) for the Databricks environment. For more information about obtaining a PAT, refer to the [Databricks documentation](https://docs.databricks.com/aws/en/dev-tools/auth/pat). This is considered a legacy feature by Databricks and OAuth is recommended over PATs.

#### Example personal access token configuration

profiles.yml

```yaml
default:
  target: dev
  outputs:
    dev:
      type: databricks
      database: TRANSFORMING
      schema: JANE_SMITH
      host: YOUR.HOST.COM
      http_path: YOUR/PATH/HERE
      token: ABC123
      auth_type: databricks_cli
      threads: 16
```

### Service Principal token

Enter your Service Principal token for the Databricks environment. For more information about obtaining a Service Principal token, refer to the [Databricks documentation](https://docs.databricks.com/aws/en/admin/users-groups/service-principals).

#### Example Service Principal token configuration

profiles.yml

```yaml
default:
  target: dev
  outputs:
    dev:
      type: databricks
      database: TRANSFORMING
      schema: JANE_SMITH
      host: YOUR.HOST.COM
      http_path: YOUR/PATH/HERE
      token: ABC123
      auth_type: databricks_cli
      threads: 16
```

### OAuth (Recommended)

Selecting the OAuth option will create a connection to your Databricks environment and open a web browser so you can complete the authentication. Users will be prompted to re-authenticate with each new dbt session they initiate.

#### Example OAuth configuration

profiles.yml

```yaml
default:
  target: dev
  outputs:
    dev:
      type: databricks
      database: TRANSFORMING
      schema: JANE_SMITH
      host: YOUR.HOST.COM
      http_path: YOUR/PATH/HERE
      auth_type: oauth
      threads: 16
```

### OAuth token federation

To use OAuth token federation, you may provide an OIDC token either in an environment variable or a file.

#### Example OIDC configuration using an environment variable

To provide the OIDC token using an environment variable, set `auth_type: env-oidc` in `profiles.yml`, and provide the OIDC token in `DATABRICKS_OIDC_TOKEN`.

profiles.yml

```yaml
  target: dev
  outputs:
    dev:
      type: databricks
      database: TRANSFORMING
      schema: JANE_SMITH
      host: YOUR.HOST.COM
      http_path: YOUR/PATH/HERE
      auth_type: env-oidc
      client_id: CLIENT_ID
      threads: 16
```

#### Example OIDC configuration using a token file

To provide the OIDC token using a token file, set `auth_type: file-oidc` in `profiles.yml` and provide the file path in `oidc_token_filepath`.

You can instead leave `oidc_token_filepath` out of `profiles.yml` and set the path in the `DATABRICKS_OIDC_TOKEN_FILE` or `DATABRICKS_OIDC_TOKEN_FILEPATH` environment variable. dbt uses the environment variable only when `oidc_token_filepath` isn't in `profiles.yml`. When both are set, dbt only uses the `profiles.yml` setting and never falls back to the environment variable. We recommend setting the path in `profiles.yml`.

profiles.yml

```yaml
default:
  target: dev
  outputs:
    dev:
      type: databricks
      database: TRANSFORMING
      schema: JANE_SMITH
      host: YOUR.HOST.COM
      http_path: YOUR/PATH/HERE
      auth_type: file-oidc
      client_id: CLIENT_ID
      oidc_token_filepath: PATH/TO/TOKEN/FILE
      threads: 16
```

## Optional connection settings

Add these optional fields to your Databricks output in `profiles.yml` to control connection timeouts and retries.

| Profile field     | Description                                                                                                                                                                                                                                                   | Default |
| ----------------- | ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | ------- |
| `connect_timeout` | How many seconds dbt waits for a connection to Databricks before giving up. Enter a number greater than `0`, or dbt returns an error. For example, `600` becomes 600s (10 mins). Raise this if your jobs start while your SQL warehouse is still starting up. | `200`   |
| `connect_retries` | The number of times dbt should retry the connection to Databricks after the first attempt fails. A retry runs only when Databricks marks the error as retryable, or when `retry_all` is `true`.                                                               | `1`     |
| `retry_all`       | Set to `true` to retry every connection error. Set to `false` to retry only errors Databricks marks as retryable.                                                                                                                                             | `false` |

These settings apply only to connecting. They don't retry queries that fail after dbt has connected.

#### Example

The following example sets `connect_timeout` to `600` (10 minutes) so dbt can wait for a SQL warehouse that is still starting.

profiles.yml

```yaml
outputs:
  dev:
    type: databricks
    # ...your other connection fields
    connect_timeout: 600
```

## More information

Find Databricks-specific configuration information in the [Databricks adapter reference guide](../../../reference/resource-configs/databricks-configs.md).
