Databricks Expert Engineer Skill Skill

Databricks Expert Engineer Skill

This skill provides a comprehensive guide for Databricks development.

1. Databricks CLI Usage

1.1. About warehouse_id

Find and select one Serverless SQL Warehouse for warehouse_id
Note: databricks CLI does not auto-read warehouse_id from config files, so explicitly include it in JSON each time

1.2. Authentication

When auth_type=databricks-cli in profile, run U2M authentication first

databricks auth login --host https://xxx.cloud.databricks.com --profile PROFILE_NAME

Check authentication status
```
databricks auth profiles
```

1.3. Basic Usage

Statements API (Query Execution)

# Execute query
databricks api post /api/2.0/sql/statements --profile "DEFAULT" --json '{
  "warehouse_id": "xxxxxxxxxx",
  "catalog": "catalog_name",
  "schema": "schema_name",
  "statement": "select * from table_name limit 10"
}'

# Get results (statement_id is returned from execution)
databricks api get /api/2.0/sql/statements/{statement_id} --profile "DEFAULT"

Queries API (Query Object Management)

# Create query object (for dashboards, saved queries)
# IMPORTANT: Use warehouse_id, query_text, display_name (NOT data_source_id, query, name)
databricks api post /api/2.0/sql/queries --profile "DEFAULT" --json '{
  "warehouse_id": "xxxxxxxxxx",
  "display_name": "My Query",
  "query_text": "SELECT * FROM table_name LIMIT 10",
  "description": "Optional description"
}'

# List queries
databricks api get /api/2.0/sql/queries --profile "DEFAULT"

# Get query by ID
databricks api get /api/2.0/sql/queries/{query_id} --profile "DEFAULT"

Common Mistakes:

❌ data_source_id → ✅ warehouse_id
❌ query → ✅ query_text
❌ name → ✅ display_name

1.4. Command Tips

Query execution flow
- post executes query -> returns statement_id
- get retrieves results (wait until state is SUCCEEDED)
- For long queries, add sleep and retry
Error handling
- state: CLOSED: Result retrieval was too slow. Get earlier
- state: FAILED: SQL error. Check error_message
- state: RUNNING: Still executing. Wait and retry get
- Timeout: For large data, use limit to verify
Reading results
- data_array: Actual data (2D array)
- schema.columns: Column names and type info
- total_row_count: Total count (shown even with limit)
- state: Query execution state
Parameterized queries

databricks api post /api/2.0/sql/statements --profile "DEFAULT" --json '{
  "warehouse_id": "xxxxxxxxxx",
  "statement": "select * from table where date >= :start_date",
  "parameters": [{"name": "start_date", "value": "2025-01-01", "type": "DATE"}]
}'

2. Well-Architected Lakehouse Framework

Consists of 7 pillars:

2.1. Data and AI Governance

Policies and practices to securely manage data and AI assets. Minimize data copies with unified governance solution.

2.2. Interoperability and Usability

Consistent user experience and seamless integration with external systems.

2.3. Operational Excellence

Processes supporting continuous production operations.

2.4. Security, Privacy, and Compliance

Implement safeguards against threats.

2.5. Reliability

Ensure disaster recovery capabilities.

2.6. Performance Efficiency

Adaptability to workload changes.

2.7. Cost Optimization

Cost management to maximize value delivery.

3. Unity Catalog

3.1. Basic Concepts

"Define once, secure everywhere" approach
Unified access control policies across multiple workspaces
ANSI SQL compliant permission management

3.2. Object Model

3-level namespace: catalog.schema.table

Catalog layer: Data isolation unit (by department, etc.)
Schema layer: Logical group containing tables, views, volumes
Object layer: Tables, views, volumes, functions, models

3.3. Permission Management

Users cannot access data by default
Explicit permission grants required
Permissions inherit from parent to child (catalog -> schema -> table)

-- Check permissions
SHOW GRANTS ON SCHEMA main.default;

-- Grant permissions
GRANT CREATE TABLE ON SCHEMA main.default TO `finance-team`;

-- Revoke permissions
REVOKE CREATE TABLE ON SCHEMA main.default FROM `finance-team`;

3.4. Best Practices

Managed tables/volumes recommended (Delta Lake format, full lifecycle management)
Catalog isolation across workspaces possible
Independent managed storage location per catalog recommended

4. Data Engineering

4.1. Lakeflow Solution

Unifies data ingestion, transformation, and orchestration.

Lakeflow Connect: Simplifies data ingestion
Lakeflow Spark Declarative Pipelines (SDP): Declarative pipeline framework
Lakeflow Jobs: Workflow automation

4.2. Delta Lake

Parquet data files with file-based transaction log
ACID transactions
Time travel functionality
Optimizations: liquid clustering, data skipping, file layout optimization, vacuum

4.3. Lakeflow Jobs

Task types:

Notebook tasks
Pipeline tasks
Python script tasks

Triggers:

Time-based (e.g., daily at 2 AM)
Event-based (on new data arrival)

Limits:

Workspace: Max 2000 concurrent task executions
Saved jobs: Max 12000
Tasks per job: Max 1000

5. Machine Learning Infrastructure

5.1. MLflow

Core tool for experiment tracking and model management
Dedicated features for GenAI

5.2. Feature Store

Feature management system
Automatic data pipelines and feature discovery

5.3. Model Serving

Deploy custom models and LLMs as REST endpoints
Auto-scaling and GPU support

6. Security

6.1. Authentication and Access Control

SSO configuration
Multi-factor authentication
Access control lists

6.2. Network Security

Private connectivity
Serverless egress control
Firewall settings
VPC management

6.3. Data Encryption

Encryption at rest and in transit
Customer-managed keys
Inter-cluster communication encryption
Automatic credential masking

7. SQL Warehouse

7.1. Serverless SQL Warehouse Benefits

Instant and elastic compute
Auto-scaling
Minimal management (Databricks handles capacity)
Low total cost of ownership

8. Schema Discovery and Validation

8.1. Pre-Query Validation Rule

YOU MUST: Run DESCRIBE before executing SELECT on unfamiliar tables
YOU MUST: Verify exact column names and case before writing queries

-- Check table columns first
DESCRIBE TABLE catalog.schema.table_name;

-- Then write your query using verified column names
SELECT column_name FROM catalog.schema.table_name;

8.2. Schema Discovery Commands

-- Basic column info
DESCRIBE TABLE catalog.schema.table_name;

-- Extended info (types, nullability, comments)
DESCRIBE EXTENDED catalog.schema.table_name;

-- List tables in schema
SHOW TABLES IN catalog.schema;

-- Table properties and metadata
DESCRIBE DETAIL catalog.schema.table_name;

8.3. Common Gotchas

| Issue | Cause | Prevention | | ------------------- | ------------------------------ | ----------------------------- | | Column name case | Databricks preserves case | Use DESCRIBE before query | | Data type mismatch | Implicit conversion fails | Check column types explicitly | | NULL handling | Unexpected NULL in aggregation | Use COALESCE or filter NULLs | | Timestamp precision | TIMESTAMP vs TIMESTAMP_NTZ | Verify type before comparison |

8.4. Knowledge Accumulation

When encountering schema-related issues, update this skill with:

Universal patterns (case sensitivity, type coercion rules)
Common column naming conventions in Unity Catalog
Databricks-specific SQL behaviors

NOTE: Do not include project-specific table names or business logic. Keep entries generalizable across environments.

9. VARIANT Type and JSON Operations

9.1. VARIANT Type (Runtime 15.3+)

Benefits:

10-30x faster than JSON strings
Schema evolution without manual updates
No predefined schema required

Basic Usage:

-- Create table with VARIANT
CREATE TABLE events (
  id BIGINT,
  data VARIANT
);

-- Insert JSON data
INSERT INTO events VALUES
  (1, parse_json('{"name":"太郎","age":25}')),
  (2, parse_json('{"name":"花子","age":30,"new_field":"value"}'));

-- Query with colon notation
SELECT
  data:name::STRING AS name,
  data:age::INT AS age,
  data:new_field::STRING AS new_field  -- Auto-recognized
FROM events;

9.2. JSON Access Patterns

Colon Notation (Recommended):

-- Object fields
json_data:name
json_data:metadata.status

-- Array elements
json_data:tags[0]
json_data:tags[1]

-- Wildcards (all elements)
json_data:tags[*]  -- Returns array

-- Nested arrays
json_data:basket[*][0]  -- First element of each sub-array
json_data:basket[0][*]  -- All elements of first array

get_json_object() Function:

-- Basic usage
get_json_object(json_data, '$.name')
get_json_object(json_data, '$.tags[0]')
get_json_object(json_data, '$.metadata.status')

-- Limitation: Path must be STRING literal (no variables)

json_object_keys() Function:

-- Get all keys as array
SELECT json_object_keys(json_data) FROM table_name;
-- Result: ["name", "age", "tags", "metadata"]

-- Access by index (order not guaranteed)
SELECT
  json_object_keys(json_data)[0] AS first_key,
  get_json_object(json_data, '$.' || json_object_keys(json_data)[0]) AS first_value
FROM table_name;

Important Notes:

Object field order is NOT guaranteed in JSON
Array order IS guaranteed
Colon notation supports type casting: json_data:age::INT
Wildcards [*] only work with colon notation (not get_json_object)

10. Dashboard API (Lakeview)

10.1. Important Changes (2026)

Legacy Dashboard API: Deprecated (access disabled 2026-01-12)
Migration deadline: 2026-03-02
New API: Lakeview API (/api/2.0/lakeview/dashboards)

10.2. Dashboard Visualization Limitations

AI/BI Dashboard (Lakeview):

❌ No custom HTML/JavaScript
❌ No client-side JSON parsing
✅ 20+ predefined visualization types
✅ Query parameters for interactivity

Recommendation: Parse JSON in SQL (server-side) before visualization

11. dbt Integration Patterns

11.1. Auto-Schema Evolution with Jinja

Macro for Dynamic JSON Expansion:

-- macros/get_json_keys.sql
{% macro get_json_keys(table_ref, json_column) %}
  {% set query %}
    SELECT DISTINCT key
    FROM {{ table_ref }},
    LATERAL variant_explode({{ json_column }})
    ORDER BY key
  {% endset %}

  {% if execute %}
    {% set results = run_query(query) %}
    {% set keys = results.columns[0].values() %}
    {{ return(keys) }}
  {% else %}
    {{ return([]) }}
  {% endif %}
{% endmacro %}

dbt Model:

-- models/staging/stg_events.sql
{% set json_keys = get_json_keys(source('bronze', 'events'), 'json_data') %}

SELECT
  id,
  {% for key in json_keys %}
  json_data:{{ key }}::STRING AS {{ key | lower }}
  {%- if not loop.last %},{% endif %}
  {% endfor %}
FROM {{ source('bronze', 'events') }}

Benefits:

New JSON fields auto-detected on dbt run
No manual model updates required
Works with VARIANT or JSON string columns

11.2. Recommended Architecture

Source → Bronze (VARIANT) → Silver (dbt expand) → Gold (business logic)

Bronze: VARIANT型でRaw JSON保存
Silver: dbt Jinjaマクロで必要なフィールドを展開
Gold: ビジネスロジック、アグリゲーション

12. Reference Links

Official docs: https://docs.databricks.com/
Unity Catalog: https://docs.databricks.com/en/data-governance/unity-catalog/
Lakeflow Jobs: https://docs.databricks.com/en/jobs/
MLflow: https://docs.databricks.com/en/mlflow/
Delta Lake: https://docs.databricks.com/en/delta/
Security: https://docs.databricks.com/en/security/
VARIANT Type: https://docs.databricks.com/aws/en/semi-structured/variant.html
JSON Operations: https://docs.databricks.com/aws/en/semi-structured/json.html
Colon Notation: https://docs.databricks.com/aws/en/sql/language-manual/functions/colonsign.html
Queries API: https://docs.databricks.com/api/workspace/queries
Lakeview Dashboards: https://docs.databricks.com/aws/en/dashboards/
dbt Documentation: https://docs.getdbt.com/

Agent Skills: Databricks Expert Engineer Skill

Install this agent skill to your local

Skill Files