Databricks SQL (DBSQL) - Advanced Features
Quick Reference
| Feature |
Key Syntax |
Since |
Reference |
| SQL Scripting |
BEGIN...END, DECLARE, IF/WHILE/FOR |
DBR 16.3+ |
references/sql-scripting.md |
| Stored Procedures |
CREATE PROCEDURE, CALL |
DBR 17.0+ |
references/sql-scripting.md |
| Recursive CTEs |
WITH RECURSIVE |
DBR 17.0+ |
references/sql-scripting.md |
| Transactions |
BEGIN ATOMIC...END |
GA |
references/sql-scripting.md |
| Materialized Views |
CREATE MATERIALIZED VIEW |
Pro/Serverless |
references/materialized-views-pipes.md |
| Temp Tables |
CREATE TEMPORARY TABLE |
All |
references/materialized-views-pipes.md |
| Pipe Syntax |
|> operator |
DBR 16.1+ |
references/materialized-views-pipes.md |
| Geospatial (H3) |
h3_longlatash3(), h3_polyfillash3() |
DBR 11.2+ |
references/geospatial-collations.md |
| Geospatial (ST) |
ST_Point(), ST_Contains(), 80+ funcs |
DBR 16.0+ |
references/geospatial-collations.md |
| Collations |
COLLATE, UTF8_LCASE, locale-aware |
DBR 16.1+ |
references/geospatial-collations.md |
| AI Functions |
ai_query(), ai_classify(), 11+ funcs |
DBR 15.1+ |
references/ai-functions.md |
| http_request |
http_request(conn, ...) |
Pro/Serverless |
references/ai-functions.md |
| remote_query |
SELECT * FROM remote_query(...) |
Pro/Serverless |
references/ai-functions.md |
| read_files |
SELECT * FROM read_files(...) |
All |
references/ai-functions.md |
| Data Modeling |
Star schema, Liquid Clustering |
All |
references/best-practices.md |
Common Patterns
SQL Scripting - Procedural ETL
BEGIN
DECLARE v_count INT;
DECLARE v_status STRING DEFAULT 'pending';
SET v_count = (SELECT COUNT(*) FROM catalog.schema.raw_orders WHERE status = 'new');
IF v_count > 0 THEN
INSERT INTO catalog.schema.processed_orders
SELECT *, current_timestamp() AS processed_at
FROM catalog.schema.raw_orders
WHERE status = 'new';
SET v_status = 'completed';
ELSE
SET v_status = 'skipped';
END IF;
SELECT v_status AS result, v_count AS rows_processed;
END
Stored Procedure with Error Handling
CREATE OR REPLACE PROCEDURE catalog.schema.upsert_customers(
IN p_source STRING,
OUT p_rows_affected INT
)
LANGUAGE SQL
SQL SECURITY INVOKER
BEGIN
DECLARE EXIT HANDLER FOR SQLEXCEPTION
BEGIN
SET p_rows_affected = -1;
SIGNAL SQLSTATE '45000'
SET MESSAGE_TEXT = concat('Upsert failed for source: ', p_source);
END;
MERGE INTO catalog.schema.dim_customer AS t
USING (SELECT * FROM identifier(p_source)) AS s
ON t.customer_id = s.customer_id
WHEN MATCHED THEN UPDATE SET *
WHEN NOT MATCHED THEN INSERT *;
SET p_rows_affected = (SELECT COUNT(*) FROM identifier(p_source));
END;
-- Invoke:
CALL catalog.schema.upsert_customers('catalog.schema.staging_customers', ?);
Materialized View with Scheduled Refresh
CREATE OR REPLACE MATERIALIZED VIEW catalog.schema.daily_revenue
CLUSTER BY (order_date)
SCHEDULE EVERY 1 HOUR
COMMENT 'Hourly-refreshed daily revenue by region'
AS SELECT
order_date,
region,
SUM(amount) AS total_revenue,
COUNT(DISTINCT customer_id) AS unique_customers
FROM catalog.schema.fact_orders
JOIN catalog.schema.dim_store USING (store_id)
GROUP BY order_date, region;
Pipe Syntax - Readable Transformations
-- Traditional SQL rewritten with pipe syntax
FROM catalog.schema.fact_orders
|> WHERE order_date >= current_date() - INTERVAL 30 DAYS
|> AGGREGATE SUM(amount) AS total, COUNT(*) AS cnt GROUP BY region, product_category
|> WHERE total > 10000
|> ORDER BY total DESC
|> LIMIT 20;
AI Functions - Enrich Data with LLMs
-- Classify support tickets
SELECT
ticket_id,
description,
ai_classify(description, ARRAY('billing', 'technical', 'account', 'feature_request')) AS category,
ai_analyze_sentiment(description) AS sentiment
FROM catalog.schema.support_tickets
LIMIT 100;
-- Extract entities from text
SELECT
doc_id,
ai_extract(content, ARRAY('person_name', 'company', 'dollar_amount')) AS entities
FROM catalog.schema.contracts;
-- General-purpose AI query with structured output
SELECT ai_query(
'databricks-meta-llama-3-3-70b-instruct',
concat('Summarize this customer feedback in JSON with keys: topic, sentiment, action_items. Feedback: ', feedback),
returnType => 'STRUCT<topic STRING, sentiment STRING, action_items ARRAY<STRING>>'
) AS analysis
FROM catalog.schema.customer_feedback
LIMIT 50;
Geospatial - Proximity Search with H3
-- Find stores within 5km of each customer using H3 indexing
WITH customer_h3 AS (
SELECT *, h3_longlatash3(longitude, latitude, 7) AS h3_cell
FROM catalog.schema.customers
),
store_h3 AS (
SELECT *, h3_longlatash3(longitude, latitude, 7) AS h3_cell
FROM catalog.schema.stores
)
SELECT
c.customer_id,
s.store_id,
ST_Distance(
ST_Point(c.longitude, c.latitude),
ST_Point(s.longitude, s.latitude)
) AS distance_m
FROM customer_h3 c
JOIN store_h3 s ON h3_ischildof(c.h3_cell, h3_toparent(s.h3_cell, 5))
WHERE ST_Distance(
ST_Point(c.longitude, c.latitude),
ST_Point(s.longitude, s.latitude)
) < 5000;
Collation - Case-Insensitive Search
-- Create table with case-insensitive collation
CREATE TABLE catalog.schema.products (
product_id BIGINT GENERATED ALWAYS AS IDENTITY,
name STRING COLLATE UTF8_LCASE,
category STRING COLLATE UTF8_LCASE,
price DECIMAL(10, 2)
);
-- Queries automatically case-insensitive (no LOWER() needed)
SELECT * FROM catalog.schema.products
WHERE name = 'MacBook Pro'; -- matches 'macbook pro', 'MACBOOK PRO', etc.
http_request - Call External APIs
-- Set up connection first (one-time)
CREATE CONNECTION my_api_conn
TYPE HTTP
OPTIONS (host 'https://api.example.com', bearer_token secret('scope', 'token'));
-- Call API from SQL
SELECT
order_id,
http_request(
conn => 'my_api_conn',
method => 'POST',
path => '/v1/validate',
json => to_json(named_struct('order_id', order_id, 'amount', amount))
).text AS api_response
FROM catalog.schema.orders
WHERE needs_validation = true;
read_files - Ingest Raw Files
-- Read JSON files from a Volume with schema hints
SELECT *
FROM read_files(
'/Volumes/catalog/schema/raw/events/',
format => 'json',
schemaHints => 'event_id STRING, timestamp TIMESTAMP, payload MAP<STRING, STRING>',
pathGlobFilter => '*.json',
recursiveFileLookup => true
);
-- Read CSV with options
SELECT *
FROM read_files(
'/Volumes/catalog/schema/raw/sales/',
format => 'csv',
header => true,
delimiter => '|',
dateFormat => 'yyyy-MM-dd',
schema => 'sale_id INT, sale_date DATE, amount DECIMAL(10,2), store STRING'
);
Recursive CTE - Hierarchy Traversal
WITH RECURSIVE org_chart AS (
-- Anchor: top-level managers
SELECT employee_id, name, manager_id, 0 AS depth, ARRAY(name) AS path
FROM catalog.schema.employees
WHERE manager_id IS NULL
UNION ALL
-- Recursive: direct reports
SELECT e.employee_id, e.name, e.manager_id, o.depth + 1, array_append(o.path, e.name)
FROM catalog.schema.employees e
JOIN org_chart o ON e.manager_id = o.employee_id
WHERE o.depth < 10 -- safety limit
)
SELECT * FROM org_chart ORDER BY depth, name;
remote_query - Federated Queries
-- Query PostgreSQL via Lakehouse Federation
SELECT *
FROM remote_query(
'my_postgres_connection',
database => 'my_database',
query => 'SELECT customer_id, email, created_at FROM customers WHERE active = true'
);
Reference Files
Load these for detailed syntax, full parameter lists, and advanced patterns:
| File |
Contents |
When to Read |
| references/sql-scripting.md |
SQL Scripting, Stored Procedures, Recursive CTEs, Transactions |
User needs procedural SQL, error handling, loops, dynamic SQL |
| references/materialized-views-pipes.md |
Materialized Views, Temp Tables/Views, Pipe Syntax |
User needs MVs, refresh scheduling, temp objects, pipe operator |
| references/geospatial-collations.md |
39 H3 functions, 80+ ST functions, Collation types and hierarchy |
User needs spatial analysis, H3 indexing, case/accent handling |
| references/ai-functions.md |
13 AI functions, http_request, remote_query, read_files (all options) |
User needs AI enrichment, API calls, federation, file ingestion |
| references/best-practices.md |
Data modeling, performance, Liquid Clustering, anti-patterns |
User needs architecture guidance, optimization, or modeling advice |
Key Guidelines
- Always use Serverless SQL warehouses for AI functions, MVs, and http_request
- Use
LIMIT during development with AI functions to control costs
- Prefer Liquid Clustering over partitioning for new tables (1-4 keys max)
- Use
CLUSTER BY AUTO when unsure about clustering keys
- Star schema in Gold layer for BI; OBT acceptable in Silver
- Define PK/FK constraints on dimensional models for query optimization
- Use
COLLATE UTF8_LCASE for user-facing string columns that need case-insensitive search
- Test SQL via CLI (
databricks experimental aitools tools query) or notebooks before deploying. If --warehouse is rejected on your CLI version, set DATABRICKS_WAREHOUSE_ID in the environment instead.
1---2name: databricks-dbsql3description: >-4 Databricks SQL (DBSQL) advanced features and SQL warehouse capabilities.5 This skill MUST be invoked when the user mentions: "DBSQL", "Databricks SQL",6 "SQL warehouse", "SQL scripting", "stored procedure", "CALL procedure",7 "materialized view", "CREATE MATERIALIZED VIEW", "pipe syntax", "|>",8 "geospatial", "H3", "ST_", "spatial SQL", "collation", "COLLATE",9 "ai_query", "ai_classify", "ai_extract", "ai_gen", "AI function",10 "http_request", "remote_query", "read_files", "Lakehouse Federation",11 "recursive CTE", "WITH RECURSIVE", "multi-statement transaction",12 "temp table", "temporary view", "pipe operator".13 SHOULD also invoke when the user asks about SQL best practices, data modeling14 patterns, or advanced SQL features on Databricks.15compatibility: Requires databricks CLI (>= v1.0.0)16metadata:17 source: https://skillmd.com/skills/stanfish06/databricks-dbsql18 version: "0.1.0"19parent: databricks-core20---2122# Databricks SQL (DBSQL) - Advanced Features2324## Quick Reference2526| Feature | Key Syntax | Since | Reference |27|---------|-----------|-------|-----------|28| SQL Scripting | `BEGIN...END`, `DECLARE`, `IF/WHILE/FOR` | DBR 16.3+ | [references/sql-scripting.md](references/sql-scripting.md) |29| Stored Procedures | `CREATE PROCEDURE`, `CALL` | DBR 17.0+ | [references/sql-scripting.md](references/sql-scripting.md) |30| Recursive CTEs | `WITH RECURSIVE` | DBR 17.0+ | [references/sql-scripting.md](references/sql-scripting.md) |31| Transactions | `BEGIN ATOMIC...END` | GA | [references/sql-scripting.md](references/sql-scripting.md) |32| Materialized Views | `CREATE MATERIALIZED VIEW` | Pro/Serverless | [references/materialized-views-pipes.md](references/materialized-views-pipes.md) |33| Temp Tables | `CREATE TEMPORARY TABLE` | All | [references/materialized-views-pipes.md](references/materialized-views-pipes.md) |34| Pipe Syntax | `\|>` operator | DBR 16.1+ | [references/materialized-views-pipes.md](references/materialized-views-pipes.md) |35| Geospatial (H3) | `h3_longlatash3()`, `h3_polyfillash3()` | DBR 11.2+ | [references/geospatial-collations.md](references/geospatial-collations.md) |36| Geospatial (ST) | `ST_Point()`, `ST_Contains()`, 80+ funcs | DBR 16.0+ | [references/geospatial-collations.md](references/geospatial-collations.md) |37| Collations | `COLLATE`, `UTF8_LCASE`, locale-aware | DBR 16.1+ | [references/geospatial-collations.md](references/geospatial-collations.md) |38| AI Functions | `ai_query()`, `ai_classify()`, 11+ funcs | DBR 15.1+ | [references/ai-functions.md](references/ai-functions.md) |39| http_request | `http_request(conn, ...)` | Pro/Serverless | [references/ai-functions.md](references/ai-functions.md) |40| remote_query | `SELECT * FROM remote_query(...)` | Pro/Serverless | [references/ai-functions.md](references/ai-functions.md) |41| read_files | `SELECT * FROM read_files(...)` | All | [references/ai-functions.md](references/ai-functions.md) |42| Data Modeling | Star schema, Liquid Clustering | All | [references/best-practices.md](references/best-practices.md) |4344---4546## Common Patterns4748### SQL Scripting - Procedural ETL4950```sql51BEGIN52 DECLARE v_count INT;53 DECLARE v_status STRING DEFAULT 'pending';5455 SET v_count = (SELECT COUNT(*) FROM catalog.schema.raw_orders WHERE status = 'new');5657 IF v_count > 0 THEN58 INSERT INTO catalog.schema.processed_orders59 SELECT *, current_timestamp() AS processed_at60 FROM catalog.schema.raw_orders61 WHERE status = 'new';6263 SET v_status = 'completed';64 ELSE65 SET v_status = 'skipped';66 END IF;6768 SELECT v_status AS result, v_count AS rows_processed;69END70```7172### Stored Procedure with Error Handling7374```sql75CREATE OR REPLACE PROCEDURE catalog.schema.upsert_customers(76 IN p_source STRING,77 OUT p_rows_affected INT78)79LANGUAGE SQL80SQL SECURITY INVOKER81BEGIN82 DECLARE EXIT HANDLER FOR SQLEXCEPTION83 BEGIN84 SET p_rows_affected = -1;85 SIGNAL SQLSTATE '45000'86 SET MESSAGE_TEXT = concat('Upsert failed for source: ', p_source);87 END;8889 MERGE INTO catalog.schema.dim_customer AS t90 USING (SELECT * FROM identifier(p_source)) AS s91 ON t.customer_id = s.customer_id92 WHEN MATCHED THEN UPDATE SET *93 WHEN NOT MATCHED THEN INSERT *;9495 SET p_rows_affected = (SELECT COUNT(*) FROM identifier(p_source));96END;9798-- Invoke:99CALL catalog.schema.upsert_customers('catalog.schema.staging_customers', ?);100```101102### Materialized View with Scheduled Refresh103104```sql105CREATE OR REPLACE MATERIALIZED VIEW catalog.schema.daily_revenue106 CLUSTER BY (order_date)107 SCHEDULE EVERY 1 HOUR108 COMMENT 'Hourly-refreshed daily revenue by region'109AS SELECT110 order_date,111 region,112 SUM(amount) AS total_revenue,113 COUNT(DISTINCT customer_id) AS unique_customers114FROM catalog.schema.fact_orders115JOIN catalog.schema.dim_store USING (store_id)116GROUP BY order_date, region;117```118119### Pipe Syntax - Readable Transformations120121```sql122-- Traditional SQL rewritten with pipe syntax123FROM catalog.schema.fact_orders124 |> WHERE order_date >= current_date() - INTERVAL 30 DAYS125 |> AGGREGATE SUM(amount) AS total, COUNT(*) AS cnt GROUP BY region, product_category126 |> WHERE total > 10000127 |> ORDER BY total DESC128 |> LIMIT 20;129```130131### AI Functions - Enrich Data with LLMs132133```sql134-- Classify support tickets135SELECT136 ticket_id,137 description,138 ai_classify(description, ARRAY('billing', 'technical', 'account', 'feature_request')) AS category,139 ai_analyze_sentiment(description) AS sentiment140FROM catalog.schema.support_tickets141LIMIT 100;142143-- Extract entities from text144SELECT145 doc_id,146 ai_extract(content, ARRAY('person_name', 'company', 'dollar_amount')) AS entities147FROM catalog.schema.contracts;148149-- General-purpose AI query with structured output150SELECT ai_query(151 'databricks-meta-llama-3-3-70b-instruct',152 concat('Summarize this customer feedback in JSON with keys: topic, sentiment, action_items. Feedback: ', feedback),153 returnType => 'STRUCT<topic STRING, sentiment STRING, action_items ARRAY<STRING>>'154) AS analysis155FROM catalog.schema.customer_feedback156LIMIT 50;157```158159### Geospatial - Proximity Search with H3160161```sql162-- Find stores within 5km of each customer using H3 indexing163WITH customer_h3 AS (164 SELECT *, h3_longlatash3(longitude, latitude, 7) AS h3_cell165 FROM catalog.schema.customers166),167store_h3 AS (168 SELECT *, h3_longlatash3(longitude, latitude, 7) AS h3_cell169 FROM catalog.schema.stores170)171SELECT172 c.customer_id,173 s.store_id,174 ST_Distance(175 ST_Point(c.longitude, c.latitude),176 ST_Point(s.longitude, s.latitude)177 ) AS distance_m178FROM customer_h3 c179JOIN store_h3 s ON h3_ischildof(c.h3_cell, h3_toparent(s.h3_cell, 5))180WHERE ST_Distance(181 ST_Point(c.longitude, c.latitude),182 ST_Point(s.longitude, s.latitude)183) < 5000;184```185186### Collation - Case-Insensitive Search187188```sql189-- Create table with case-insensitive collation190CREATE TABLE catalog.schema.products (191 product_id BIGINT GENERATED ALWAYS AS IDENTITY,192 name STRING COLLATE UTF8_LCASE,193 category STRING COLLATE UTF8_LCASE,194 price DECIMAL(10, 2)195);196197-- Queries automatically case-insensitive (no LOWER() needed)198SELECT * FROM catalog.schema.products199WHERE name = 'MacBook Pro'; -- matches 'macbook pro', 'MACBOOK PRO', etc.200```201202### http_request - Call External APIs203204```sql205-- Set up connection first (one-time)206CREATE CONNECTION my_api_conn207 TYPE HTTP208 OPTIONS (host 'https://api.example.com', bearer_token secret('scope', 'token'));209210-- Call API from SQL211SELECT212 order_id,213 http_request(214 conn => 'my_api_conn',215 method => 'POST',216 path => '/v1/validate',217 json => to_json(named_struct('order_id', order_id, 'amount', amount))218 ).text AS api_response219FROM catalog.schema.orders220WHERE needs_validation = true;221```222223### read_files - Ingest Raw Files224225```sql226-- Read JSON files from a Volume with schema hints227SELECT *228FROM read_files(229 '/Volumes/catalog/schema/raw/events/',230 format => 'json',231 schemaHints => 'event_id STRING, timestamp TIMESTAMP, payload MAP<STRING, STRING>',232 pathGlobFilter => '*.json',233 recursiveFileLookup => true234);235236-- Read CSV with options237SELECT *238FROM read_files(239 '/Volumes/catalog/schema/raw/sales/',240 format => 'csv',241 header => true,242 delimiter => '|',243 dateFormat => 'yyyy-MM-dd',244 schema => 'sale_id INT, sale_date DATE, amount DECIMAL(10,2), store STRING'245);246```247248### Recursive CTE - Hierarchy Traversal249250```sql251WITH RECURSIVE org_chart AS (252 -- Anchor: top-level managers253 SELECT employee_id, name, manager_id, 0 AS depth, ARRAY(name) AS path254 FROM catalog.schema.employees255 WHERE manager_id IS NULL256257 UNION ALL258259 -- Recursive: direct reports260 SELECT e.employee_id, e.name, e.manager_id, o.depth + 1, array_append(o.path, e.name)261 FROM catalog.schema.employees e262 JOIN org_chart o ON e.manager_id = o.employee_id263 WHERE o.depth < 10 -- safety limit264)265SELECT * FROM org_chart ORDER BY depth, name;266```267268### remote_query - Federated Queries269270```sql271-- Query PostgreSQL via Lakehouse Federation272SELECT *273FROM remote_query(274 'my_postgres_connection',275 database => 'my_database',276 query => 'SELECT customer_id, email, created_at FROM customers WHERE active = true'277);278```279280---281282## Reference Files283284Load these for detailed syntax, full parameter lists, and advanced patterns:285286| File | Contents | When to Read |287|------|----------|--------------|288| [references/sql-scripting.md](references/sql-scripting.md) | SQL Scripting, Stored Procedures, Recursive CTEs, Transactions | User needs procedural SQL, error handling, loops, dynamic SQL |289| [references/materialized-views-pipes.md](references/materialized-views-pipes.md) | Materialized Views, Temp Tables/Views, Pipe Syntax | User needs MVs, refresh scheduling, temp objects, pipe operator |290| [references/geospatial-collations.md](references/geospatial-collations.md) | 39 H3 functions, 80+ ST functions, Collation types and hierarchy | User needs spatial analysis, H3 indexing, case/accent handling |291| [references/ai-functions.md](references/ai-functions.md) | 13 AI functions, http_request, remote_query, read_files (all options) | User needs AI enrichment, API calls, federation, file ingestion |292| [references/best-practices.md](references/best-practices.md) | Data modeling, performance, Liquid Clustering, anti-patterns | User needs architecture guidance, optimization, or modeling advice |293294---295296## Key Guidelines297298- **Always use Serverless SQL warehouses** for AI functions, MVs, and http_request299- **Use `LIMIT` during development** with AI functions to control costs300- **Prefer Liquid Clustering over partitioning** for new tables (1-4 keys max)301- **Use `CLUSTER BY AUTO`** when unsure about clustering keys302- **Star schema in Gold layer** for BI; OBT acceptable in Silver303- **Define PK/FK constraints** on dimensional models for query optimization304- **Use `COLLATE UTF8_LCASE`** for user-facing string columns that need case-insensitive search305- **Test SQL via CLI** (`databricks experimental aitools tools query`) or notebooks before deploying. If `--warehouse` is rejected on your CLI version, set `DATABRICKS_WAREHOUSE_ID` in the environment instead.