Databricks SQL (DBSQL) advanced features and SQL warehouse capabilities. This skill MUST be invoked when the user mentions: "DBSQL", "Databricks SQL", "SQL warehouse", "SQL scripting", "stored procedure", "CALL procedure", "materialized view", "CREATE MATERIALIZED VIEW", "pipe syntax", "|>", "geospatial", "H3", "ST_", "spatial SQL", "collation", "COLLATE", "ai_query", "ai_classify", "ai_extract", "ai_gen", "AI function", "http_request", "remote_query", "read_files", "Lakehouse Federation", "recursive CTE", "WITH RECURSIVE", "multi-statement transaction", "temp table", "temporary view", "pipe operator". SHOULD also invoke when the user asks about SQL best practices, data modeling patterns, or advanced SQL features on Databricks.
Databricks SQL (DBSQL) advanced features and SQL warehouse capabilities. This skill MUST be invoked when the user mentions: "DBSQL", "Databricks SQL", "SQL warehouse", "SQL scripting", "stored procedure", "CALL procedure", "materialized view", "CREATE MATERIALIZED VIEW", "pipe syntax", "|>", "geospatial", "H3", "ST_", "spatial SQL", "collation", "COLLATE", "ai_query", "ai_classify", "ai_extract", "ai_gen", "AI function", "http_request", "remote_query", "read_files", "Lakehouse Federation", "recursive CTE", "WITH RECURSIVE", "multi-statement transaction", "temp table", "temporary view", "pipe operator". SHOULD also invoke when the user asks about SQL best practices, data modeling patterns, or advanced SQL features on Databricks.
BEGINDECLARE v_count INT;
DECLARE v_status STRING DEFAULT'pending';
SET v_count = (SELECTCOUNT(*) FROM catalog.schema.raw_orders WHERE status ='new');
IF v_count >0THENINSERT INTO catalog.schema.processed_orders
SELECT*, current_timestamp() AS processed_at
FROM catalog.schema.raw_orders
WHERE status ='new';
SET v_status ='completed';
ELSESET v_status ='skipped';
END IF;
SELECT v_status ASresult, v_count AS rows_processed;
END
Stored Procedure with Error Handling
CREATEOR REPLACE PROCEDURE catalog.schema.upsert_customers(
IN p_source STRING,
OUT p_rows_affected INT
)
LANGUAGESQLSQL SECURITY INVOKER
BEGINDECLARE EXIT HANDLER FORSQLEXCEPTIONBEGINSET p_rows_affected =-1;
SIGNAL SQLSTATE'45000'SET MESSAGE_TEXT = concat('Upsert failed for source: ', p_source);
END;
MERGEINTO catalog.schema.dim_customer AS t
USING (SELECT*FROM identifier(p_source)) AS s
ON t.customer_id = s.customer_id
WHEN MATCHED THENUPDATESET*WHENNOT MATCHED THENINSERT*;
SET p_rows_affected = (SELECTCOUNT(*) FROM identifier(p_source));
END;
-- Invoke:CALL catalog.schema.upsert_customers('catalog.schema.staging_customers', ?);
Materialized View with Scheduled Refresh
CREATEOR REPLACE MATERIALIZED VIEW catalog.schema.daily_revenue
CLUSTER BY (order_date)
SCHEDULE EVERY1HOUR
COMMENT 'Hourly-refreshed daily revenue by region'ASSELECT
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)
GROUPBY order_date, region;
Pipe Syntax - Readable Transformations
-- Traditional SQL rewritten with pipe syntaxFROM catalog.schema.fact_orders
|>WHERE order_date >=current_date() -INTERVAL30 DAYS
|> AGGREGATE SUM(amount) AS total, COUNT(*) AS cnt GROUPBY region, product_category
|>WHERE total >10000|>ORDERBY total DESC|> LIMIT 20;
AI Functions - Enrich Data with LLMs
-- Classify support ticketsSELECT
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 textSELECT
doc_id,
ai_extract(content, ARRAY('person_name', 'company', 'dollar_amount')) AS entities
FROM catalog.schema.contracts;
-- General-purpose AI query with structured outputSELECT 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 indexingWITH 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 collationCREATE TABLE catalog.schema.products (
product_id BIGINT GENERATED ALWAYS ASIDENTITY,
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 SQLSELECT
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 hintsSELECT*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 optionsSELECT*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
WITHRECURSIVE org_chart AS (
-- Anchor: top-level managersSELECT employee_id, name, manager_id, 0AS depth, ARRAY(name) AS path
FROM catalog.schema.employees
WHERE manager_id ISNULLUNIONALL-- Recursive: direct reportsSELECT 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 ORDERBY depth, name;
remote_query - Federated Queries
-- Query PostgreSQL via Lakehouse FederationSELECT*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:
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.
