Databricks Module 8: SQL Warehouses, Performance, Cost, and a First Look at MLflow

Step 1: Serve analysts with Databricks SQL

A SQL warehouse is compute tuned for SQL queries and BI tools. Analysts write queries in the SQL editor, save them, build dashboards, and connect tools such as Power BI and Tableau. Serverless SQL warehouses start in seconds and stop automatically when idle. Size the warehouse to the workload, and set an auto-stop timeout so you do not pay for idle time.

SELECT status, count(*) AS orders, sum(amount) AS revenue
FROM lab.sales.orders_silver
GROUP BY status
ORDER BY revenue DESC;

Step 2: Tune performance with the biggest levers

Store data in Delta with sensible clustering on the columns you filter by, avoid many small files (OPTIMIZE or automatic maintenance), filter early and select only needed columns, broadcast small tables in joins, and avoid unnecessary shuffles and wide transformations. Photon, the vectorized engine, speeds up many SQL and DataFrame workloads on supported compute. Read the query profile or Spark UI to find the slow stage before changing anything.

-- cluster a large table by the columns you filter on
ALTER TABLE lab.sales.events CLUSTER BY (event_date, user_id);
OPTIMIZE lab.sales.events;

Step 3: Control cost

Costs come from Databricks Units for compute plus your cloud provider's charges. Use job or serverless compute for scheduled work instead of always-on clusters, set auto-termination on interactive clusters, use cluster policies to cap size, schedule heavy jobs sensibly, tag compute for cost reporting, and use system tables and budgets to see where spend goes. Check current pricing, because it differs by cloud, region, and compute type.

Step 4: Track a model with MLflow

MLflow records experiments: the parameters, metrics, and model files of each training run, so results are reproducible and comparable. On Databricks it is built in, and autologging captures most details for common libraries.

import mlflow
from sklearn.datasets import load_diabetes
from sklearn.ensemble import RandomForestRegressor
from sklearn.model_selection import train_test_split
from sklearn.metrics import mean_absolute_error

X, y = load_diabetes(return_X_y=True)
X_train, X_test, y_train, y_test = train_test_split(X, y, random_state=42)

with mlflow.start_run(run_name="rf-baseline"):
    model = RandomForestRegressor(n_estimators=100, random_state=42).fit(X_train, y_train)
    mae = mean_absolute_error(y_test, model.predict(X_test))
    mlflow.log_param("n_estimators", 100)
    mlflow.log_metric("mae", mae)
    mlflow.sklearn.log_model(model, "model")

Step 5: Finish with a capstone and clean up

Capstone: ingest files with Auto Loader into a bronze table, build silver and gold layers in a pipeline with at least two expectations, schedule it as a job with an alert, grant an analyst group read access to the gold table, and build a small dashboard on it. Then clean up: terminate compute, delete the job and pipeline schedules, and drop practice tables and volumes so nothing keeps costing money.

Capstone checklist
[ ] Auto Loader ingestion into a bronze table with metadata columns
[ ] Silver table with at least two expectations
[ ] Gold materialized view or table for reporting
[ ] Job with schedule, retry, timeout, and failure alert
[ ] Permissions granted to a group, not individuals
[ ] A dashboard on the gold table
[ ] Practice resources deleted, compute terminated

-- cleanup
DROP SCHEMA IF EXISTS lab.sales CASCADE;
DROP CATALOG IF EXISTS lab CASCADE;