Module 13: Databricks SQL & Analytics

• Databricks SQL = the SQL-first experience: editor, warehouses, dashboards, alerts, query history
• Same Delta tables and Unity Catalog permissions as notebooks and jobs
• Also reachable from outside: BI tools and drivers connect to a warehouse (topic 11)

Open it: sidebar SQL Warehouses (or Compute → SQL warehouses).

• Free Edition: one warehouse, 2X-Small (fixed)
• A stopped warehouse starts when a query, dashboard or connection needs it
• Sizing rule: if a query spills to disk (see the profile), go one size up. More clusters help concurrency, not a single slow query
• Up to 1,000 queries can wait in the queue

%sql
SELECT current_catalog(), current_schema(), current_user(), version()

• Intelligent Workload Management predicts each query's needs, queues when busy, and scales clusters up and down
• Databricks recommends serverless for most workloads
• Use Pro when serverless isn't available in your region or you need custom networking

QUALIFY filters on a window function:

%sql
-- latest order per customer
SELECT customer_id, order_id, order_ts
FROM orders
QUALIFY row_number() OVER (PARTITION BY customer_id ORDER BY order_ts DESC) = 1

FILTER, PIVOT and MAX_BY:

%sql
SELECT customer_id,
       count(*)                                    AS orders,
       count(*) FILTER (WHERE status = 'cancelled') AS cancelled,
       max_by(order_id, order_ts)                  AS last_order_id
FROM orders GROUP BY customer_id LIMIT 5

%sql
SELECT * FROM (SELECT product_id, status FROM orders)
PIVOT (count(*) FOR status IN ('completed', 'shipped', 'cancelled', 'returned'))
ORDER BY product_id LIMIT 5

Semi-structured and arrays:

%sql
SELECT '{"a": {"b": 5}, "tags": ["x", "y"]}':a.b::int AS b,
       transform(array(1, 2, 3), x -> x * 10)          AS tens,
       filter(array(1, 2, 3, 4), x -> x % 2 = 0)       AS evens

%sql
CREATE OR REPLACE VIEW completed_orders AS
SELECT * FROM orders WHERE status = 'completed';

SELECT count(*) FROM completed_orders

%sql
CREATE MATERIALIZED VIEW revenue_by_category AS
SELECT p.category, round(sum(o.quantity * p.unit_price), 2) AS revenue
FROM orders o JOIN products p USING (product_id)
WHERE o.status IN ('completed', 'shipped')
GROUP BY p.category

%sql
REFRESH MATERIALIZED VIEW revenue_by_category

• A materialized view is managed by a pipeline behind the scenes. It needs Unity Catalog and a serverless (or Pro) warehouse
• Dynamic views and row filters / column masks limit what each user sees (Module 6)
• Metric views define business measures once in Unity Catalog so dashboards, Genie and BI tools all agree on "revenue"

Open it: Query History in the sidebar.

In the profile (Module 12.15): top operators, rows, time, files pruned, bytes read, spill.

• Workflow for a slow query: history → profile → worst operator → fix → rerun → compare
• Queries answered from the result cache have no profile
• Notebook cells on serverless: See performance under the cell
• You see your own queries. Others need CAN MONITOR on the warehouse
• The warehouse monitoring tab shows peak queued queries: above 0 means more capacity is needed

%sql
-- check the plan before running something big
EXPLAIN FORMATTED
SELECT c.country, sum(o.quantity) AS qty
FROM orders o JOIN customers c USING (customer_id)
WHERE o.order_ts >= '2025-03-01'
GROUP BY c.country

• Layout does more than clever SQL: liquid clustering on filter columns, no tiny files (Module 12)
• EXPLAIN doesn't run the query; the profile shows what actually happened
• Statistics keep the optimizer honest: keep predictive optimization on for managed tables

• Query result cache: an identical query on unchanged data returns the stored result
• Disk (data) cache: Delta/Parquet files read often stay on fast local storage
• Results are invalidated when the underlying tables change
• To test real speed, change the query slightly (for example LIMIT) or look at a first run
• Nothing to configure: don't use CACHE TABLE as a habit

1. Dashboards → Create dashboard
2. Data tab → add a dataset with a query:

SELECT date(order_ts) AS order_date, status, count(*) AS orders,
       round(sum(quantity * unit_price), 2) AS revenue
FROM dbx_course.orders JOIN dbx_course.products USING (product_id)
GROUP BY ALL

3. Canvas tab → Add a visualization: line chart of revenue by order_date; bar chart of orders by status; a counter for total revenue
4. Add a filter widget on status
5. Publish; set who can view and which credentials the data uses

• Put business logic in gold tables or views, not inside dashboard SQL
• Fewer, well-named datasets beat one query per chart

• Genie answers questions about your tables in natural language and shows the SQL it wrote
• You set up a Genie agent (earlier called a Genie space) around a few tables

Set it up:

1. Genie → New, pick tables: orders, customers, products
2. Add instructions: "Revenue counts statuses completed and shipped only"
3. Add example SQL for typical questions
4. Test with: "Which category had the most revenue?"

• Answers use the asking user's permissions (Unity Catalog applies)
• Always read the generated SQL for important numbers
• Genie runs on a SQL warehouse

# Python SQL connector (pip install databricks-sql-connector)
from databricks import sql

with sql.connect(server_hostname="<host>", http_path="<http-path>", access_token="<token>") as conn:
    with conn.cursor() as cur:
        cur.execute("SELECT status, count(*) FROM dbx_course.orders GROUP BY status")
        print(cur.fetchall())

• Keep tokens in a secret store, not in code. Prefer OAuth or a service principal in production
• BI tools respect Unity Catalog permissions, row filters and column masks
• Point tools at gold tables, views or metric views

%sql
-- a stable, documented serving view over gold
CREATE OR REPLACE VIEW serving_daily_revenue
COMMENT 'Daily revenue for completed and shipped orders. Owner: data-eng'
AS SELECT order_date, orders, revenue FROM gold_daily_revenue;

GRANT SELECT ON VIEW serving_daily_revenue TO `account users`

• Share outside Databricks with Delta Sharing (Module 6.12)
• Freshness: say how often a table refreshes, and alert if it doesn't