Databricks SQL & SQL Warehouses

-- Create a SQL Warehouse using the SQL Warehouses API
-- POST /api/2.0/sql/warehouses

-- Python equivalent:
import requests

config = {
    "name": "analytics-warehouse",
    "cluster_size": "Medium",     # 2X-Small to 4X-Large
    "min_num_clusters": 1,
    "max_num_clusters": 3,       # Auto-scale clusters for concurrency
    "auto_stop_mins": 15,
    "warehouse_type": "PRO",     # PRO, CLASSIC, or leave empty for serverless
    "enable_serverless_compute": False,
    "spot_instance_policy": "COST_OPTIMIZED",
    "tags": {
        "custom_tags": [{"key": "team", "value": "analytics"}]
    }
}

response = requests.post(
    f"{DATABRICKS_HOST}/api/2.0/sql/warehouses",
    headers=headers,
    json=config
)

-- Revenue Dashboard: Daily revenue trend
SELECT
    date_trunc('day', order_date) AS day,
    SUM(total_amount) AS daily_revenue,
    COUNT(DISTINCT customer_id) AS unique_customers,
    AVG(total_amount) AS avg_order_value
FROM prod_catalog.sales.orders
WHERE order_date >= current_date() - INTERVAL 30 DAYS
GROUP BY 1
ORDER BY 1;

-- Alert Query: Detect revenue drops
SELECT
    CASE
        WHEN today_revenue < yesterday_revenue * 0.8
        THEN 'ALERT: Revenue dropped 20%+'
        ELSE 'OK'
    END AS status,
    today_revenue,
    yesterday_revenue
FROM (
    SELECT
        SUM(CASE WHEN order_date = current_date() THEN total_amount END) AS today_revenue,
        SUM(CASE WHEN order_date = current_date() - 1 THEN total_amount END) AS yesterday_revenue
    FROM prod_catalog.sales.orders
);

-- Top Products with Window Functions
SELECT
    product_name,
    category,
    total_sold,
    RANK() OVER (PARTITION BY category ORDER BY total_sold DESC) AS category_rank
FROM (
    SELECT product_name, category, SUM(quantity) AS total_sold
    FROM prod_catalog.sales.order_items
    GROUP BY product_name, category
)
WHERE category_rank <= 5;

-- 1. Use Delta Lake statistics for pruning
ANALYZE TABLE prod_catalog.sales.orders COMPUTE STATISTICS FOR ALL COLUMNS;

-- 2. Optimize file layout with Z-ORDER
OPTIMIZE prod_catalog.sales.orders
ZORDER BY (customer_id, order_date);

-- 3. Use result caching -- repeated queries return instantly
-- Result cache is ON by default for SQL Warehouses
SET use_cached_result = true;

-- 4. Partition large tables by date
CREATE TABLE prod_catalog.sales.events_partitioned
USING delta
PARTITIONED BY (event_date)
AS SELECT * FROM prod_catalog.sales.events;

-- 5. Use EXPLAIN to analyze query plans
EXPLAIN FORMATTED
SELECT customer_id, SUM(amount)
FROM prod_catalog.sales.orders
WHERE order_date >= '2024-01-01'
GROUP BY customer_id;

-- 6. Materialized Views for repeated complex queries
CREATE MATERIALIZED VIEW prod_catalog.sales.daily_summary
AS SELECT
    date_trunc('day', order_date) AS day,
    SUM(total_amount) AS revenue,
    COUNT(*) AS order_count
FROM prod_catalog.sales.orders
GROUP BY 1;