Databricks

---
name: databricks
description: "Databricks expert. Deep expertise in lakehouse architecture, Delta Lake, Unity Catalog, Spark SQL, MLflow, Mosaic AI, workflows, and cost optimization. WHEN: \"Databricks\", \"Delta Lake\", \"Unity Catalog\", \"Databricks SQL\", \"Databricks workspace\", \"DBU\", \"Photon\", \"MLflow\", \"Mosaic AI\", \"Delta Sharing\", \"Databricks Workflows\", \"Databricks cluster\", \"serverless compute\", \"Liquid Clustering\"."
license: MIT
---

# Databricks

This skill covers Databricks, the unified analytics platform built on lakehouse architecture. It covers Delta Lake (ACID transactions, time travel, OPTIMIZE, Z-ORDER, Liquid Clustering, change data feed, deletion vectors, UniForm), Unity Catalog (3-level namespace, data lineage, row/column-level security), Databricks SQL (serverless warehouses, query federation), Spark SQL optimization, Photon engine, Mosaic AI (model serving, feature store, vector search, AI functions), MLflow, Workflows (jobs, tasks, orchestration), streaming (Structured Streaming, Delta Live Tables), Databricks Connect, and cost optimization (DBUs, spot instances, cluster policies). Databricks is a fully managed service -- there are no user-installed versions; all workspaces run the latest platform release from Databricks (on AWS, Azure, or GCP).

## How to Approach Tasks

When you receive a request:

1. **Classify** the request:
   - **Architecture/internals** -- Load `references/architecture.md`
   - **Performance diagnostics** -- Load `references/diagnostics.md`
   - **Configuration/operations** -- Load `references/best-practices.md`
   - **Comparison with other databases** -- see the `overview` skill

2. **Determine the context** -- Ask if unclear. Behavior differs significantly depending on whether the user is on AWS, Azure, or GCP; whether they use Unity Catalog or legacy HMS; whether they use classic compute, serverless, or SQL warehouses; and their pricing tier (Standard, Premium, Enterprise).

3. **Analyze** -- Apply Databricks-specific reasoning. Reference the lakehouse model, Delta Lake transaction log, Unity Catalog governance, Spark execution model, Photon engine capabilities, and cost model (DBU pricing per SKU) as relevant.

4. **Recommend** -- Provide actionable guidance with specific Databricks CLI commands, SQL statements, notebook code, REST API calls, or workspace configuration steps.

5. **Verify** -- Suggest validation steps (system tables, Spark UI metrics, DESCRIBE HISTORY, DESCRIBE DETAIL, Unity Catalog information_schema queries, cluster event logs).

## Core Expertise

### Lakehouse Architecture

Databricks pioneered the lakehouse paradigm -- combining the reliability of data warehouses with the scale and flexibility of data lakes:

- **Storage layer:** Cloud object storage (S3, ADLS, GCS) stores all data in open formats (Delta Lake by default, with Parquet, ORC, Avro, CSV, JSON support)
- **Transaction layer:** Delta Lake provides ACID transactions, schema enforcement, and time travel on top of object storage
- **Compute layer:** Decoupled compute (Spark clusters, SQL warehouses, serverless) scales independently from storage
- **Governance layer:** Unity Catalog provides unified access control, auditing, lineage, and discovery across all data assets
- **AI layer:** Mosaic AI integrates model training, serving, feature engineering, and vector search natively

**Key benefit:** One copy of data serves data engineering (ETL), SQL analytics, data science, and AI/ML workloads -- eliminating costly data duplication between lakes and warehouses.

### Delta Lake

Delta Lake is the open-source storage layer that brings reliability to data lakes. It is the default format in Databricks.

**Core capabilities:**
- **ACID transactions:** Every write (INSERT, UPDATE, DELETE, MERGE) is atomic and isolated via the Delta transaction log (`_delta_log/`)
- **Transaction log:** A JSON-based commit log that records every operation. Each commit produces a numbered JSON file; every 10 commits a checkpoint Parquet file is written for fast log replay
- **Schema enforcement:** Writes that do not match the table schema are rejected by default
- **Schema evolution:** Controlled schema changes via `mergeSchema` or `overwriteSchema` options
- **Time travel:** Query any historical version of a table by version number or timestamp

```sql
-- Query a specific version
SELECT * FROM my_catalog.my_schema.events VERSION AS OF 42;

-- Query by timestamp
SELECT * FROM my_catalog.my_schema.events TIMESTAMP AS OF '2026-04-01T00:00:00Z';

-- Restore to a previous version
RESTORE TABLE my_catalog.my_schema.events TO VERSION AS OF 42;
```

**OPTIMIZE and file compaction:**
- Delta tables accumulate many small files from streaming or frequent writes
- `OPTIMIZE` compacts small files into larger files (target ~1 GB) for faster reads
- Runs as a separate operation; does not block concurrent reads/writes

```sql
-- Compact all files
OPTIMIZE my_catalog.my_schema.events;

-- Compact with Z-ORDER on specific columns
OPTIMIZE my_catalog.my_schema.events ZORDER BY (user_id, event_type);
```

**Z-ORDER:** Co-locates related data in the same set of files by the specified columns. Dramatically improves data-skipping for queries that filter on those columns. Most effective on high-cardinality columns used frequently in WHERE clauses. Limited to up to 4 columns; diminishing returns beyond 2-3.

**Liquid Clustering:** The next-generation replacement for Z-ORDER and Hive-style partitioning. Specified at table creation with `CLUSTER BY`:

```sql
CREATE TABLE my_catalog.my_schema.events (
  event_id BIGINT,
  event_date DATE,
  user_id STRING,
  event_type STRING,
  payload STRING
)
CLUSTER BY (event_date, user_id);

-- Re-cluster data (incremental -- only rewrites files that need it)
OPTIMIZE my_catalog.my_schema.events;
```

- Automatically organizes data using Hilbert curves for multi-dimensional co-location
- Incremental -- `OPTIMIZE` only rewrites files that need re-clustering, unlike Z-ORDER which rewrites all files
- Clustering columns can be changed after table creation with `ALTER TABLE ... CLUSTER BY`
- No partition boundaries -- eliminates small-file and partition-skew problems inherent in Hive partitioning
- Recommended over Z-ORDER and Hive-style partitioning for new tables

**Deletion vectors:** An optimization that marks rows as deleted in a separate file rather than rewriting data files. Dramatically speeds up DELETE, UPDATE, and MERGE operations on large tables. Enabled by default on new tables. Reads automatically apply deletion vectors to filter out soft-deleted rows.

**Change data feed (CDF):** Records row-level changes (inserts, updates, deletes) in a separate `_change_data/` directory. Enables efficient CDC pipelines downstream:

```sql
-- Enable CDF on a table
ALTER TABLE my_catalog.my_schema.events SET TBLPROPERTIES (delta.enableChangeDataFeed = true);

-- Read changes between versions
SELECT * FROM table_changes('my_catalog.my_schema.events', 5, 10);

-- Read changes by timestamp
SELECT * FROM table_changes('my_catalog.my_schema.events', '2026-04-01', '2026-04-07');
```

**UniForm:** Enables a single Delta table to be read by Iceberg and Hudi clients without data duplication. Databricks automatically generates Iceberg metadata alongside Delta metadata:

```sql
CREATE TABLE my_catalog.my_schema.events (
  event_id BIGINT, event_date DATE, payload STRING
)
TBLPROPERTIES (
  'delta.universalFormat.enabledFormats' = 'iceberg'
);
```

**VACUUM:** Removes data files no longer referenced by the Delta log (old versions beyond retention). Default retention is 7 days. Never set retention below the longest-running query or streaming checkpoint interval:

```sql
-- Dry run to see which files would be deleted
VACUUM my_catalog.my_schema.events DRY RUN;

-- Delete unreferenced files older than default retention
VACUUM my_catalog.my_schema.events;

-- Custom retention (hours)
VACUUM my_catalog.my_schema.events RETAIN 168 HOURS;
```

### Unity Catalog

Unity Catalog is the unified governance layer for all data and AI assets in Databricks.

**3-level namespace:** `catalog.schema.object`
- **Metastore:** Top-level container attached to a Databricks account. One per region.
- **Catalog:** First level of the namespace. Organizes schemas. Analogous to a database in traditional RDBMS.
- **Schema (database):** Second level. Contains tables, views, functions, models, volumes.
- **Object:** Tables, views, materialized views, functions, ML models, volumes, connections.

**Key features:**
- Centralized access control with GRANT/REVOKE using SQL or REST API
- Data lineage tracked automatically at column level across notebooks, jobs, and SQL queries
- External locations: map cloud storage paths to governed Unity Catalog locations
- Managed storage: Unity Catalog manages the physical location of managed tables
- Storage credentials: securely reference cloud provider credentials (IAM roles, service principals)

**Security model:**
```sql
-- Grant usage on catalog
GRANT USAGE ON CATALOG analytics TO `data-engineers`;

-- Grant access to schema
GRANT USAGE ON SCHEMA analytics.production TO `data-engineers`;
GRANT SELECT ON SCHEMA analytics.production TO `data-analysts`;

-- Table-level grants
GRANT SELECT, MODIFY ON TABLE analytics.production.orders TO `etl-service`;

-- Row-level security via row filters
ALTER TABLE analytics.production.orders SET ROW FILTER security_db.region_filter ON (region);

-- Column-level masking
ALTER TABLE analytics.production.customers SET COLUMN MASK security_db.email_mask ON (email);
```

**Row filters and column masks** are SQL UDFs that dynamically filter rows or mask column values based on the querying user's identity or group membership:

```sql
-- Create a row filter function
CREATE FUNCTION security_db.region_filter(region_val STRING)
RETURN IF(IS_ACCOUNT_GROUP_MEMBER('global-admins'), true, region_val = current_user_region());

-- Create a column mask function
CREATE FUNCTION security_db.email_mask(email_val STRING)
RETURN IF(IS_ACCOUNT_GROUP_MEMBER('pii-readers'), email_val, '***@***.***');
```

**Delta Sharing:** Open protocol for secure data sharing across organizations without copying data. Recipients can be external (non-Databricks) consumers using any Delta Sharing client:

```sql
-- Create a share
CREATE SHARE customer_share;
ALTER SHARE customer_share ADD TABLE analytics.production.customers;

-- Create a recipient
CREATE RECIPIENT partner_org USING ID 'partner-account-id';

-- Grant access
GRANT SELECT ON SHARE customer_share TO RECIPIENT partner_org;
```

### Databricks SQL

Databricks SQL provides a SQL-native analytics experience with serverless compute:

- **SQL warehouses:** Dedicated compute endpoints optimized for SQL queries. Three types:
  - **Serverless:** Instant start, auto-scaling, managed by Databricks. Lowest operational overhead.
  - **Pro:** User-managed, supports Photon, query federation, predictive optimization.
  - **Classic:** Legacy type, basic SQL execution.
- **Query federation:** Query external databases (PostgreSQL, MySQL, SQL Server, Redshift, Snowflake, BigQuery, Salesforce) directly from Databricks SQL using foreign catalogs registered in Unity Catalog
- **Serverless compute:** Databricks manages the infrastructure; clusters start in seconds; charges are per-DBU consumed
- **Dashboards:** Built-in visualization and dashboarding with SQL-based widgets and AI-assisted dashboard creation
- **Alerts:** SQL-based alerting on query results with configurable thresholds and notification destinations

### Spark SQL Optimization

Databricks runs an optimized distribution of Apache Spark with significant enhancements:

- **Adaptive Query Execution (AQE):** Dynamically adjusts query plans at runtime based on actual data statistics. Auto-coalesces shuffle partitions, converts sort-merge joins to broadcast hash joins, optimizes skew joins.
- **Predicate pushdown:** Filters pushed down to the storage layer; Delta Lake data-skipping leverages column-level min/max statistics to skip entire files
- **Dynamic file pruning:** At join time, prune files from the probe side based on values from the build side
- **Cost-based optimizer (CBO):** Uses table and column statistics for optimal join ordering and strategy selection

```sql
-- Collect statistics for CBO
ANALYZE TABLE my_catalog.my_schema.orders COMPUTE STATISTICS FOR ALL COLUMNS;

-- View query execution plan
EXPLAIN EXTENDED SELECT * FROM my_catalog.my_schema.orders WHERE region = 'US';
```

### Photon Engine

Photon is a vectorized query engine written in C++ that replaces the Spark JVM execution engine for supported operations:

- Runs natively on Delta Lake Parquet files
- 2-10x faster than standard Spark for scan-heavy, aggregation, and join workloads
- Vectorized execution processes data in columnar batches (like a columnar DBMS)
- Enabled per-cluster or per-SQL-warehouse; automatically falls back to Spark JVM for unsupported operations
- Serverless SQL warehouses always use Photon
- Best gains on: large scans, aggregations, joins, string operations, and data ingestion

### Mosaic AI

Mosaic AI is Databricks' integrated AI/ML platform:

**Model serving:**
- Deploy models (MLflow, custom Python, foundation models) as real-time REST endpoints
- Serverless GPU inference with auto-scaling from zero
- Support for foundation model APIs (DBRX, Llama, Mixtral, and external models via AI Gateway)
- Provisioned throughput for predictable latency at scale

**Feature store:**
- Feature tables are Unity Catalog tables with primary key and timestamp metadata
- Automatic feature lookup at training and inference time
- Online store integration (Cosmos DB, DynamoDB) for low-latency serving
- Feature engineering with declarative feature functions

**Vector search:**
- Managed vector index on Delta tables for similarity search
- Supports embedding generation via model serving endpoints
- Auto-sync from source Delta table to vector index
- Query via REST API or Python SDK

```sql
-- Create a vector search index (via Python SDK)
-- from databricks.vector_search.client import VectorSearchClient
-- client = VectorSearchClient()
-- index = client.create_delta_sync_index(
--   endpoint_name="my-vs-endpoint",
--   source_table_name="catalog.schema.documents",
--   index_name="catalog.schema.doc_index",
--   primary_key="doc_id",
--   embedding_source_column="text",
--   embedding_model_endpoint_name="embedding-model"
-- )
```

**AI functions:** SQL functions that call LLMs directly from SQL queries:

```sql
-- Classify text using AI
SELECT ai_classify(review_text, ARRAY('positive', 'negative', 'neutral')) AS sentiment
FROM my_catalog.my_schema.reviews;

-- Generate text
SELECT ai_generate(CONCAT('Summarize: ', article_text)) AS summary
FROM my_catalog.my_schema.articles;

-- Extract structured data
SELECT ai_extract(description, ARRAY('product_name', 'price', 'category')) AS extracted
FROM my_catalog.my_schema.listings;
```

### MLflow

MLflow is the open-source ML lifecycle platform deeply integrated into Databricks:

- **Tracking:** Log parameters, metrics, artifacts, and models for every experiment run
- **Model Registry in Unity Catalog:** Register, version, stage, and govern models as Unity Catalog objects
- **Model serving:** Deploy registered models to serving endpoints with one click
- **Experiment management:** Organize runs into experiments; compare runs with built-in UI

```python
import mlflow
mlflow.set_experiment("/Users/user@company.com/my-experiment")

with mlflow.start_run():
    mlflow.log_param("learning_rate", 0.01)
    mlflow.log_metric("rmse", 0.85)
    mlflow.sklearn.log_model(model, "model",
        registered_model_name="catalog.schema.my_model")
```

### Workflows (Jobs)

Databricks Workflows orchestrate multi-task data pipelines:

- **Jobs:** Named collections of tasks with schedule, triggers, and alerting
- **Tasks:** Individual units of work -- notebooks, Python scripts, SQL queries, dbt tasks, Delta Live Tables pipelines, JAR tasks, or Spark Submit
- **Task dependencies:** DAG-based orchestration with conditional branching (IF/ELSE, FOR EACH)
- **Triggers:** Schedule (cron), file arrival, continuous, or manual
- **Compute:** Each task can use a dedicated job cluster, shared job cluster, or serverless compute
- **Parameters:** Pass parameters between tasks; dynamic value references with `{{task_name.values.output}}`
- **Repair and retry:** Automatically retry failed tasks; repair and re-run only failed tasks in a completed run

### Streaming

**Structured Streaming:** Apache Spark's stream processing engine, deeply integrated with Delta Lake:

```python
# Read from Kafka, write to Delta
(spark.readStream
  .format("kafka")
  .option("kafka.bootstrap.servers", "broker:9092")
  .option("subscribe", "events")
  .load()
  .selectExpr("CAST(value AS STRING) as json_data")
  .writeStream
  .format("delta")
  .outputMode("append")
  .option("checkpointLocation", "/checkpoints/events")
  .trigger(availableNow=True)  # or processingTime="10 seconds"
  .toTable("my_catalog.my_schema.raw_events"))
```

**Delta Live Tables (DLT):** Declarative ETL framework for building reliable streaming and batch pipelines:

```python
import dlt

@dlt.table(comment="Raw events ingested from Kafka")
def raw_events():
    return (spark.readStream
        .format("kafka")
        .option("kafka.bootstrap.servers", "broker:9092")
        .option("subscribe", "events")
        .load())

@dlt.table(comment="Cleaned events with quality constraints")
@dlt.expect_or_drop("valid_event_type", "event_type IS NOT NULL")
@dlt.expect_or_fail("valid_timestamp", "event_ts > '2020-01-01'")
def cleaned_events():
    return dlt.read_stream("raw_events").selectExpr(
        "CAST(value AS STRING) as payload",
        "CAST(key AS STRING) as event_key",
        "timestamp as event_ts"
    )
```

- Automatic dependency resolution between tables
- Data quality expectations (`expect`, `expect_or_drop`, `expect_or_fail`)
- Automatic schema inference and evolution
- Enhanced autoscaling for streaming workloads
- Materialized views and streaming tables as first-class objects in Unity Catalog

### Databricks Connect

Databricks Connect allows running Spark code from local IDEs (VS Code, IntelliJ, PyCharm) against Databricks clusters:

- Thin client -- code runs locally, Spark execution happens on the cluster
- Supports PySpark DataFrame API, Spark SQL, and MLlib
- Works with serverless compute and classic clusters
- No data is pulled to the local machine unless explicitly collected

### Cost Optimization

**DBU (Databricks Unit):** The unit of processing capability per hour billed by Databricks:

| Compute Type | Approximate DBU/hour | Best For |
|---|---|---|
| Jobs Compute (classic) | 0.10-0.40 per VM core | Batch ETL, scheduled jobs |
| All-Purpose Compute (classic) | 0.22-0.65 per VM core | Interactive development, ad hoc analysis |
| SQL Serverless | Per-query DBU | SQL analytics, dashboards |
| Jobs Serverless | Per-task DBU | Automated pipelines |
| Model Serving | Per-token / per-request | Real-time inference |

**Cost optimization strategies:**
1. **Use serverless compute** -- Eliminates idle cluster costs; pay only for queries/tasks executed
2. **Job clusters over all-purpose clusters** -- Job clusters are significantly cheaper per DBU and auto-terminate
3. **Spot instances (cloud provider)** -- Up to 60-90% savings for fault-tolerant batch workloads; configure via cluster policies
4. **Cluster policies** -- Restrict instance types, max workers, auto-termination, and spot ratio to control costs organizationally
5. **Auto-termination** -- Set aggressive auto-termination (10-30 minutes) on interactive clusters
6. **Photon** -- While Photon DBU rate is higher, the wall-clock speedup often results in lower total cost
7. **Right-size clusters** -- Monitor cluster utilization via Ganglia/Spark UI; reduce over-provisioned workers
8. **Predictive optimization** -- Auto-runs OPTIMIZE and VACUUM on managed tables, reducing manual maintenance and improving query performance
9. **System tables for cost monitoring** -- Query `system.billing.usage` to track DBU consumption by workspace, cluster, user, and SKU
10. **Delta Lake file management** -- Regular OPTIMIZE and VACUUM keeps file count and size optimal, reducing I/O costs

## Common Troubleshooting Patterns

### Slow Queries

1. Check execution plan with `EXPLAIN EXTENDED` -- look for full table scans, missing data-skipping, suboptimal join strategies
2. Check Delta table health: `DESCRIBE DETAIL` for file count and size; `DESCRIBE HISTORY` for recent operations
3. Run `OPTIMIZE` if file count is high or average file size is small (<100 MB)
4. Enable Liquid Clustering or add Z-ORDER on frequently filtered columns
5. Check if Photon is enabled -- significant speedup for scan and aggregation workloads
6. Collect table statistics with `ANALYZE TABLE ... COMPUTE STATISTICS` for CBO
7. Check AQE settings and shuffle partition count in Spark UI

### Cluster OOM (Out of Memory)

1. Check Spark UI -- look for skewed partitions in shuffle stages (one task processing much more data)
2. Check for `collect()`, `toPandas()`, or broadcast of large tables pulling data to the driver
3. Solutions: increase driver/worker memory, repartition skewed data, use salting for skew joins, avoid driver-side collection, increase `spark.sql.shuffle.partitions`
4. For Delta tables: run `OPTIMIZE` to reduce file count and ensure even file sizes
5. Monitor with Ganglia metrics on the cluster -- check memory utilization trends

### Streaming Lag

1. Check Structured Streaming query progress -- `query.lastProgress` shows `inputRowsPerSecond` vs `processedRowsPerSecond`
2. If processing rate < input rate: increase cluster size, reduce trigger interval, optimize transformations
3. Check for skew in Kafka partitions or Delta change feed
4. Use `trigger(availableNow=True)` for catch-up processing in batch mode
5. For DLT: check pipeline event log for backlog and data quality expectation violations

### Unity Catalog Permission Errors

1. Check the full privilege chain: metastore > catalog > schema > object. User needs `USAGE` on every level
2. Check group membership: `SELECT * FROM system.information_schema.catalog_privileges WHERE grantee = 'user@company.com'`
3. Verify external location permissions for external tables
4. Check storage credential access for the underlying cloud storage
5. Use `SHOW GRANTS ON <object>` to see effective permissions

### Cost Spikes

1. Query `system.billing.usage` to identify DBU consumption by SKU, workspace, and cluster
2. Look for long-running interactive clusters with low utilization (high idle DBU burn)
3. Check for jobs running on all-purpose compute instead of job compute
4. Check for missing auto-termination on interactive clusters
5. Identify large or frequently running jobs that could benefit from serverless or spot instances
6. Solutions: enforce cluster policies, migrate to serverless, set auto-termination, use spot instances