Databricks SQL basics: SQL warehouses, the SQL editor, query history and a first dashboard

Databricks SQL is the part of Databricks for analysts and BI tools: plain SQL on Unity Catalog tables, run on SQL warehouses instead of clusters.

#SQL warehouses in one minute

Settings that matter:

• Size (2X-Small ... 4X-Large): bigger = faster for heavy queries, more cost per hour.
• Scaling (min/max clusters): more clusters handle more concurrent users; they do not make one query faster.
• Auto stop: minutes of inactivity before stopping. Short (5-10 min) for serverless.

Tip

Slow single query: try a bigger size. Many people waiting in a queue: raise max clusters. Mixing them up wastes money.

#Write a query

SQL Editor, choose a warehouse and a default catalog/schema:

SELECT date_trunc('week', order_date) AS week,
       country,
       sum(amount) AS revenue,
       count(DISTINCT customer_id) AS customers
FROM sales_prod.gold.orders
WHERE order_date >= dateadd(MONTH, -3, current_date())
GROUP BY ALL
ORDER BY week, revenue DESC;

GROUP BY ALL groups by every non-aggregated column. Save the query with a clear name; saved queries can be shared and reused in dashboards and alerts.

Parameters make one query reusable:

SELECT * FROM sales_prod.gold.orders
WHERE country = :country AND order_date >= :start_date;

#When a query is slow: history and profile

Query History shows every query on the warehouse with duration, rows and status. Open one and choose Query profile:

Quick fixes: filter on the table's clustering or partition columns, select only needed columns, avoid SELECT * in dashboards.

From SQL you can also check recent history in system tables if enabled:

SELECT statement_text, total_duration_ms, read_bytes, produced_rows, executed_by
FROM system.query.history
WHERE start_time > current_timestamp() - INTERVAL 1 DAY
ORDER BY total_duration_ms DESC
LIMIT 20;

#Build a dashboard

Dashboards > Create dashboard (AI/BI dashboards):

1. Data tab: add a dataset from a saved query or a table.
2. Canvas: add a visualization (line, bar, counter, table), pick fields.
3. Add a filter widget bound to a field (country, date range).
4. Publish. Choose whether viewers use your credentials (embedded) or their own; for sensitive data, use their own so Unity Catalog permissions apply.

Schedule it: Schedule > Add schedule to refresh and email a snapshot.

#Alerts on a query

Create an alert on a saved query that returns a number:

SELECT count(*) AS failed_orders
FROM sales_prod.gold.orders
WHERE status = 'payment_failed' AND order_date = current_date();

Alerts > Create: condition failed_orders > 100, schedule every 15 minutes, notify a destination.

#Connecting BI tools

Each warehouse's Connection details tab shows the server hostname and HTTP path for Power BI, Tableau and JDBC/ODBC tools. Authenticate with Entra ID (OAuth) rather than personal access tokens where the tool supports it.

#Cost habits

• Serverless with short auto stop for interactive use.
• Separate warehouses for BI dashboards and ad-hoc analysts, so one cannot slow the other.
• Tag warehouses with team and cost centre.

#Prevent it next time

• Saved, reviewed queries for dashboards instead of ad-hoc SQL.
• Watch Query History weekly for the top 10 slowest queries and fix them.
• Grant analysts SELECT on gold schemas, not on raw data.