> For the complete documentation index, see [llms.txt](https://v2.dataos.info/llms.txt). Markdown versions of documentation pages are available by appending `.md` to page URLs; this page is available as [Markdown](https://v2.dataos.info/references/engine-guide/databricks.md).

# Databricks

Databricks is an Apache Spark-based analytics platform with managed clusters, a SQL engine, Unity Catalog, and Delta Lake. Vulcan runs against Databricks to manage transformations using Unity Catalog and Delta tables. This page is the full manual: connection and authentication, Unity Catalog grants, the external state store Databricks requires, how each model kind materialises, operational guardrails, and the failure modes you hit in real projects.

Use the [Connect engine → Databricks](https://v2.dataos.info/build/productize/connect-engine/databricks) Build page for the quick connection steps.

{% hint style="info" %}
Engine adapter type: `databricks`. Model dialect: `databricks`. Execution vehicle: SQL warehouse via `http_path` (not an all-purpose cluster by default). Tested Vulcan image: `tmdcio/vulcan-databricks:0.228.1.23` on the Draco 1.38 series.
{% endhint %}

## When to use Databricks

Choose Databricks when your data lands in Unity Catalog as Delta tables and you want to transform and serve it with a SQL warehouse. The SQL warehouse plus Unity Catalog path is GA. The Python / Databricks Connect path is partial: default execution is SQL warehouse, so validate Python model support against your specific image before using it in production.

### Supported model kinds

`FULL`, `VIEW`, `INCREMENTAL_BY_TIME_RANGE`, `INCREMENTAL_BY_UNIQUE_KEY`, `INCREMENTAL_BY_PARTITION`, and `SCD_TYPE_2`.

{% hint style="warning" %}
Vulcan state (plans, snapshots, intervals) cannot live in the SQL warehouse. Databricks requires an external Postgres `state_connection`. A running statestore is mandatory for both local development and production.
{% endhint %}

## Connection options

Set these under `gateways.<name>.connection`.

| Option            | Description                                                                                                        | Type   | Required |
| ----------------- | ------------------------------------------------------------------------------------------------------------------ | ------ | -------- |
| `type`            | Engine type name. Must be `databricks`.                                                                            | string | Yes      |
| `server_hostname` | The workspace hostname (for example, `adb-xxxxx.azuredatabricks.net` or `dbc-xxxxxxxx-xxxx.cloud.databricks.com`). | string | Yes      |
| `http_path`       | The HTTP path to the SQL warehouse or cluster. Find it under SQL Warehouses → your warehouse → Connection Details. | string | Yes      |
| `access_token`    | Personal access token (PAT) or service principal token.                                                            | string | Yes      |
| `catalog`         | The Unity Catalog name to use as the default catalog.                                                              | string | Yes      |

### Authentication methods

* Personal access token (PAT): set `access_token`. Generate one under User Settings → Developer → Access tokens → Generate new token.
* Service principal token: set `access_token` to the service principal token.

{% hint style="warning" %}
The PAT's workspace must match `server_hostname`. A cross-workspace PAT fails silently at auth. Never commit the token; inject it with `access_token: {{ env_var('DATABRICKS_ACCESS_TOKEN') }}`.
{% endhint %}

## Permissions and grants

Three roles are required, each with a distinct scope.

| Role                | Who holds it                        | Purpose                                                                         |
| ------------------- | ----------------------------------- | ------------------------------------------------------------------------------- |
| Admin role          | Databricks workspace admin          | Creates SQL warehouses, Unity Catalog (UC) catalogs, and grants UC permissions. |
| Vulcan service role | The principal whose PAT Vulcan uses | Runs models, creates and modifies tables and schemas in the target UC catalog.  |
| Consumer role       | BI users, endpoint consumers        | Read-only access to Data Product tables via Vulcan endpoints.                   |

Run minimum grants as the workspace admin in the SQL editor, on the same warehouse as `http_path`:

```sql
-- Target catalog (for Vulcan materialization)
GRANT USE CATALOG ON CATALOG <your_catalog> TO `<principal>`;
GRANT CREATE SCHEMA ON CATALOG <your_catalog> TO `<principal>`;
GRANT CREATE TABLE ON CATALOG <your_catalog> TO `<principal>`;
GRANT CREATE VIEW ON CATALOG <your_catalog> TO `<principal>`;

-- Source data (e.g. Unity Catalog sample data)
GRANT USE CATALOG ON CATALOG samples TO `<principal>`;
GRANT USE SCHEMA ON SCHEMA samples.tpch TO `<principal>`;
GRANT SELECT ON SCHEMA samples.tpch TO `<principal>`;
```

Consumer role (read-only):

```sql
GRANT USE CATALOG ON CATALOG <your_catalog> TO `<consumer>`;
GRANT USE SCHEMA ON SCHEMA <your_catalog>.<schema> TO `<consumer>`;
GRANT SELECT ON SCHEMA <your_catalog>.<schema> TO `<consumer>`;
```

Verify grants in the SQL editor (same warehouse as `http_path`):

```sql
SELECT current_user() AS user, current_catalog() AS catalog;
SHOW SCHEMAS IN <your_catalog>;
SHOW TABLES IN samples.tpch;
```

{% hint style="warning" %}
The UC catalog referenced in `connection.catalog` must exist before `vulcan apply`. Vulcan creates schemas and tables inside it; it cannot create the catalog itself. A missing catalog raises `NO_SUCH_CATALOG_EXCEPTION`.
{% endhint %}

### DataOS permissions

Your DataOS operator must provision `roles:id:data-dev` (or equivalent), workspace access, `depot:rw:<databricks-depot-name>`, `depot:r:<databricks-depot-name>`, and Git repository access. Run `dataos-ctl get depot` to confirm the Databricks depot appears before you start.

## Example gateway config

Minimum local connection. Note the required `state_connection` and `state_schema`:

```yaml
gateways:
  default:
    connection:
      type: databricks
      server_hostname: <workspace-host>          # e.g. dbc-xxxxxxxx-xxxx.cloud.databricks.com
      http_path: /sql/1.0/warehouses/<id>        # from SQL Warehouse → Connection details
      access_token: "{{ env_var('DATABRICKS_ACCESS_TOKEN', '') }}"
      catalog: "{{ env_var('DATABRICKS_CATALOG', 'main') }}"
    state_connection:
      type: postgres
      host: "{{ env_var('STATESTORE_HOST', 'localhost') }}"
      port: "{{ env_var('STATESTORE_PORT', '5432') }}"
      user: "{{ env_var('STATESTORE_USER', 'vulcan') }}"
      password: "{{ env_var('STATESTORE_PASSWORD', 'vulcan') }}"
      database: "{{ env_var('STATESTORE_DATABASE', 'statestore') }}"
    state_schema: <project_name>

default_gateway: default
model_defaults:
  dialect: databricks
```

Isolate multiple projects on the same Postgres instance with different `state_schema` values. For local development, run Postgres in Docker; in production, a DataOS-managed Postgres is used.

## Local development

Databricks itself runs in the cloud; what you run locally is the Postgres statestore Vulcan needs.

```bash
docker run -d --name vulcan-state \
  -e POSTGRES_USER=vulcan -e POSTGRES_PASSWORD=vulcan \
  -e POSTGRES_DB=statestore -p 5432:5432 postgres:15

export DATABRICKS_ACCESS_TOKEN=dapi<your-token>
export DATABRICKS_CATALOG=<your-catalog>

vulcan migrate        # initializes Vulcan state in Postgres
vulcan plan           # dry run against Unity Catalog; should succeed with no errors
```

{% hint style="info" %}
If the CLI runs inside Docker and cannot reach Postgres, set `STATESTORE_HOST=host.docker.internal` and confirm the port mapping.
{% endhint %}

## Production deployment

Production uses a depot instead of a direct connection. Apply resources in this order:

```
Databricks PAT instance secret → Databricks depot → Git secret → config.yaml → Vulcan resource
```

| Manifest                              | Purpose                                                                       |
| ------------------------------------- | ----------------------------------------------------------------------------- |
| `instance-secret-databricks-pat.yaml` | Stores the Databricks PAT.                                                    |
| `depot-databricks.yaml`               | Registers workspace URL, HTTP path, UC catalog; binds purposes to the secret. |
| `secret-git-sync.yaml`                | Repo credentials for git-sync.                                                |
| `config.yaml`                         | Gateway points at the depot.                                                  |
| `domain-resource.yaml`                | Workflow plus API; `engine: databricks`, depot, repo.                         |

Depot purposes:

| Purpose | Who uses it            | What it allows                                                                       |
| ------- | ---------------------- | ------------------------------------------------------------------------------------ |
| `rw`    | Vulcan workflow        | Read source tables, create and write model output, manage schemas in the UC catalog. |
| `scan`  | Metadata scanner       | Read UC `information_schema` to populate the catalog and lineage.                    |
| `query` | Consumer direct access | Read-only access to Data Product tables.                                             |

{% hint style="warning" %}
A misspelled depot name passes `apply` but fails at every run with `Depot not resolvable`. Verify `dataos://<depot-name>?purpose=rw` matches the depot manifest's `name` exactly.
{% endhint %}

## Materialisation behaviour per model kind

SQL models compile to Databricks SQL and execute on the configured SQL warehouse over Delta tables in Unity Catalog.

| Model kind                  | Databricks operation                                                                         |
| --------------------------- | -------------------------------------------------------------------------------------------- |
| `FULL`                      | `INSERT OVERWRITE` (replaces table contents; Delta, UC-managed).                             |
| `VIEW`                      | `CREATE OR REPLACE VIEW` (staging / intermediate layer).                                     |
| `INCREMENTAL_BY_TIME_RANGE` | `INSERT OVERWRITE` by time-column partition (overwrites the whole partition for the window). |
| `INCREMENTAL_BY_UNIQUE_KEY` | `MERGE` on the unique key (ACID upsert via Delta Lake).                                      |
| `INCREMENTAL_BY_PARTITION`  | `REPLACE WHERE` by partitioning key (Delta partition-scoped overwrite).                      |

A typical layering pattern: declare Bronze/source tables in `external_models.yaml`, alias raw columns to business names in `VIEW` staging models, build gold dimensions as `FULL`, and build the gold fact as `INCREMENTAL_BY_TIME_RANGE` partitioned on the date column.

```sql
MODEL (
  name marts.fct_sales,
  kind INCREMENTAL_BY_TIME_RANGE (
    time_column order_date
  ),
  grain (ORDER_LINE_ID)
);
SELECT ...
FROM   intermediate.int_order_items
WHERE  order_date BETWEEN @start_date AND @end_date;
```

### Identifier casing

{% hint style="warning" %}
Match semantic model identifiers to the physical column casing of the model they reference. Databricks sample and raw tables use lowercase column names (`c_custkey`); alias them to UPPERCASE in staging, then use UPPERCASE everywhere in semantic models, filters, joins, DQ SQL, and metric expressions. Lowercase causes `column not found`.
{% endhint %}

```sql
SELECT c_custkey AS CUSTOMER_ID, c_name AS CUSTOMER_NAME FROM samples.tpch.customer
```

```yaml
kind: semantic
name: ORDERS
model: marts.fct_sales
dimensions:
  includes:
    - ORDER_LINE_ID
    - ORDER_DATE
    - CUSTOMER_ID
measures:
  - name: TOTAL_SALES
    type: sum
    sql: "{ORDERS.TOTAL_REVENUE}"
```

### Cross-catalog external models and lifecycle DDL

`vulcan create_external_models` may not discover tables outside the configured catalog (for example, `samples.tpch`). Declare them in `external_models.yaml`, or the linter throws `Table not found` and lineage stops at the model boundary.

```yaml
- name: samples.tpch.customer
  columns:
    c_custkey: BIGINT
    c_name:    VARCHAR(25)
    c_mktsegment: VARCHAR(10)
```

Wrap any DDL in `pre_statements` / `post_statements` that should run only on real execution, or `vulcan plan` executes it:

```sql
@IF(@runtime_stage = 'evaluating',
  OPTIMIZE <catalog>.<schema>.<table> ZORDER BY (order_date)
);
```

To make forward-only structural changes, set the Delta column-mapping property on the model, otherwise `ALTER` operations such as dropping a column fail with `DELTA_UNSUPPORTED_DROP_COLUMN`:

```
physical_properties (
  'delta.columnMapping.mode' = 'name'
)
```

## Endpoints

Every Data Product on Databricks gets REST, GraphQL, and MySQL-wire endpoints. All push down to the same SQL warehouse, so latency is identical across protocols; the endpoint layer adds roughly 1.5 to 3 seconds (validate in your stack, since warehouse tier affects it). Result sets up to about 50,000 rows are buffered in API memory; keep `api.limit.memory` at 3 GiB and never below 1.5 GiB.

## Metadata scanning and catalog

DataOS scans Unity Catalog `information_schema` (real-time structural metadata) and Databricks system tables (query history and lineage; lag varies by workspace tier, with serverless system tables near real time and classic lagging minutes to hours). Use a read-only scanner principal separate from the Vulcan service role:

```sql
GRANT USE CATALOG ON CATALOG <your_catalog> TO `<scanner_principal>`;
GRANT USE SCHEMA ON ALL SCHEMAS IN CATALOG <your_catalog> TO `<scanner_principal>`;
GRANT SELECT ON ALL TABLES IN CATALOG <your_catalog> TO `<scanner_principal>`;
GRANT USE CATALOG ON CATALOG system TO `<scanner_principal>`;
GRANT SELECT ON ALL TABLES IN CATALOG system TO `<scanner_principal>`;
```

Lineage is computed from parsed view definitions, `MERGE` / `INSERT INTO SELECT` / `CTAS` statements, and Vulcan's own SQL parse. The scanner runs every 6 to 12 hours (configurable).

## Engine-native feature support

| Databricks feature               | Vulcan pattern                                                                             |
| -------------------------------- | ------------------------------------------------------------------------------------------ |
| SQL warehouse                    | `connection.http_path` plus PAT (primary execution path).                                  |
| UC catalog (existing)            | `connection.catalog`; Vulcan creates schemas inside it.                                    |
| Managed Delta table              | kind `FULL` and incrementals.                                                              |
| Delta table properties / Z-order | `ALTER TABLE … SET TBLPROPERTIES` or `OPTIMIZE … ZORDER BY` in guarded `post_statements`.  |
| SQL UDF (inline)                 | `CREATE OR REPLACE FUNCTION` in `pre_statements`.                                          |
| Identity column                  | `GENERATED ALWAYS AS IDENTITY` in model DDL.                                               |
| UC row filters / column masks    | Enforced at query time by UC; test queries with the PAT principal's effective permissions. |

Boundaries (provision or manage out of band): SQL warehouse create/resize/auto-stop config, all-purpose cluster as default execution, Delta Live Tables and Streaming tables, UC catalog creation, Delta Sharing, and MLflow / Feature Store. Land results of those flows as Delta tables that Vulcan reads.

## Operational boundaries

### Compute sizing

Use a small SQL warehouse with 10-minute auto-stop for dev, right-size to partition touch volume for daily incrementals, scale up temporarily for first backfills (revert after), and use a dedicated or higher-tier warehouse for the semantic API. Always enable auto-stop on dev warehouses (Workspace UI → SQL Warehouses → Edit → Auto Stop: 10 min).

### Concurrency and API replicas

Keep `concurrent_tasks` below what the warehouse can admit without queue timeouts; start at 2 for daily incrementals. The API is stateless: add replicas (1 to 2 single team, 3 to 5 multi-team, 5 or more high volume) before raising warehouse concurrency. Keep `api.limit.memory` at 3 GiB.

### Scheduling and latency floor

Schedule after upstream lands, set `timezone: UTC`, set `endOn` 1 to 2 years out, and use `concurrencyPolicy: Forbid`. The latency floor: SQL warehouse cold start after auto-stop adds 2 to 10 seconds (tier dependent); the semantic API adds about 1.5 to 3 seconds.

### Cost guardrails

Databricks bills SQL warehouse DBUs. Enable auto-stop, set a query timeout, tag queries for attribution, and use `VACUUM` to reclaim deleted Delta files.

```sql
SET spark.sql.session.timeoutMs = 3600000;       -- 1 hour, in pre_statements
SET spark.sql.query.tag = 'vulcan:<project>:<model>';
```

## Performance reference

Latency is warehouse-tier and data-volume shaped, so validate on a known dataset before publishing SLOs. Measure warehouse queue time (concurrency saturation), plan/run duration per model, semantic query wall time, rows touched per incremental window (the primary DBU driver), and cold-start overhead. When concurrency degrades, fix in order: remove cross-join-shaped queries, add API replicas, raise warehouse cluster count, split build and query warehouses, then increase warehouse size last.

## Failure modes and troubleshooting

| Symptom                                               | Likely cause                                          | Fix                                                                     |
| ----------------------------------------------------- | ----------------------------------------------------- | ----------------------------------------------------------------------- |
| `NO_SUCH_CATALOG_EXCEPTION`                           | Catalog missing or wrong name                         | Create the UC catalog; fix `DATABRICKS_CATALOG` / `connection.catalog`. |
| `connection refused` to Postgres statestore           | Infra not running or wrong host/port                  | Start Postgres; use `host.docker.internal:5432` from inside Docker.     |
| Auth / 403 on warehouse                               | Expired PAT or wrong workspace URL                    | New PAT; ensure `server_hostname` matches the PAT's workspace.          |
| `Table samples.tpch.customer not found`               | Missing UC grant or not declared external             | Grant `SELECT` on `samples.tpch`; add to `external_models.yaml`.        |
| Plan succeeds but Catalog Explorer shows empty schema | Different SQL warehouse used in UI versus `http_path` | Use the same warehouse in the SQL editor and `config.yaml`.             |
| Semantic `column not found`                           | Casing mismatch                                       | Use UPPERCASE identifiers in semantic models.                           |
| `Depot not resolvable` on DataOS                      | Depot name mismatch                                   | Match `dataos://<depot-name>?purpose=rw` to the depot `name`.           |
| Duplicate apply appears skipped                       | Postgres state believes the model is current          | Create a new `state_schema`, run `vulcan migrate`, re-apply (dev only). |
| `http_path invalid` / `warehouse not found`           | Warehouse stopped or deleted                          | Recreate; update `http_path`.                                           |
| `vulcan plan` runs DDL                                | Lifecycle DDL not guarded                             | Wrap in `@IF(@runtime_stage = 'evaluating', …)`.                        |
| Incremental reprocesses all history                   | Missing/wrong `time_column`                           | Verify `kind INCREMENTAL_BY_TIME_RANGE (time_column <col>)`.            |
| Endpoint OOM on large result                          | `api.memory.limit` below 1.5 GiB                      | Raise to 3 GiB.                                                         |
| First query after idle 2 to 10 seconds slower         | Cold start after auto-stop                            | Expected; document in the SLO or pin a warming query.                   |

### Health-check queries

```sql
SELECT current_user() AS user, current_catalog() AS catalog, current_schema() AS schema;
SHOW SCHEMAS IN <your_catalog>;
SELECT table_catalog, table_schema, table_name, table_type
FROM   <your_catalog>.information_schema.tables
WHERE  table_schema NOT LIKE 'information_schema'
ORDER  BY table_schema, table_name
LIMIT  50;
```

### Recovery procedures

| Situation                               | Procedure                                                                              |
| --------------------------------------- | -------------------------------------------------------------------------------------- |
| Incremental run failed mid-window       | Re-run; `INCREMENTAL_BY_TIME_RANGE` re-processes the window, so re-runs are safe.      |
| Table dropped manually                  | `vulcan plan` detects and recreates; or `vulcan run --rebuild <model>`.                |
| Wrong UC catalog applied                | Fix `DATABRICKS_CATALOG`, create a new `state_schema`, run `vulcan migrate`, re-apply. |
| PAT leaked or expired                   | Revoke, issue a new token, update `.env` / DataOS instance secret.                     |
| Workflow halted because `endOn` expired | Update `endOn` and re-apply.                                                           |

## Related

* [Connect engine → Databricks](https://v2.dataos.info/build/productize/connect-engine/databricks) for the quick connection steps.
* [Engine guide overview](/references/engine-guide/engine-guide.md) for the cross-engine rules.


---

# Agent Instructions
This documentation is published with GitBook. GitBook is the documentation platform designed so that both humans and AI agents can read, navigate, and reason over technical content effectively. Learn more at gitbook.com.

## Querying This Documentation
If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question.

Perform an HTTP GET request on the current page URL with the `ask` query parameter, and the optional `goal` query parameter:

```
GET https://v2.dataos.info/references/engine-guide/databricks.md?ask=<question>&goal=<endgoal>
```

`ask` is the immediate question: it should be specific, self-contained, and written in natural language.
`goal` is optional and describes the broader end goal you are ultimately trying to accomplish on behalf of the user. GitBook uses it to tailor the answer towards what is most useful for that goal.

The response will contain a direct answer to the question and relevant excerpts and sources from the documentation.

Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.