Rana Nasri Ghazzi

Databricks Performance & Cost Optimization

Oct 18, 2026

Every ETL job, materialized view and pipeline on Databricks should be judged on three things at once: speed, cost, and how well you can see both. Use the sections below while designing, the monitoring sections while testing, and the checklist at the end before every deployment.

Core rules to keep in mind:

• Read less data. Most wins come from scanning fewer files and columns, not from bigger clusters.
• Process only what changed. Incremental beats full refresh almost every time.
• Faster is not always cheaper. Compare runtime and DBUs side by side after every change.
• Measure in development, not after an incident. Record a baseline before you deploy.

1. Data layout foundations

How data sits on storage decides how much of it every query must read, so fix layout before tuning code.

• Use Delta tables, ideally Unity Catalog managed tables. They give file statistics, data skipping and access to Predictive Optimization, which runs OPTIMIZE and VACUUM for you.
• Avoid the small-files problem. Thousands of tiny files slow every read. Run OPTIMIZE on tables written in small batches, or enable auto-optimize:

ALTER TABLE silver.orders SET TBLPROPERTIES (
  'delta.autoOptimize.optimizeWrite' = 'true',
  'delta.autoOptimize.autoCompact'  = 'true'
);

• Prefer Liquid Clustering over partitioning and Z-ORDER for new tables. Cluster by the columns you filter or join on most, and you can change them later without rewriting the table:

CREATE TABLE silver.orders (...) CLUSTER BY (order_date, customer_id);
-- or let Databricks choose:
ALTER TABLE silver.orders CLUSTER BY AUTO;

• Partition only very large tables, on a low-cardinality column such as date, and only when each partition holds at least about 1 GB. Over-partitioning creates small files.
• Keep statistics useful. Data skipping uses stats on the first 32 columns by default, so put filter and join columns early in the schema.
• Clean up old files. Run VACUUM on a schedule (or let Predictive Optimization do it) to cut storage cost.

2. Writing efficient ETL transformations

The fastest transformation is the one that reads and moves the least data, so filter early, select narrowly and load incrementally.

Reading

• Filter as early as possible, on clustered or partitioned columns, so Spark can skip files.
• Select only the columns you need. Never SELECT * in production ETL.
• For files landing in cloud storage, use Auto Loader (cloudFiles) rather than listing directories. Auto Loader relies on cloud-native event notifications, so file discovery reads from an event queue of newly arrived files — the discovery cost stays roughly constant no matter how many files have accumulated historically, and there’s no need to re-list and diff against everything already processed since the queue only ever surfaces what’s new.

Joins and shuffles

• Broadcast small dimension tables.
• Keep Adaptive Query Execution on (default). It fixes skewed joins and right-sizes shuffle partitions at runtime.
• Watch for skew: one task running far longer than the rest in the Spark UI means a hot key. Salt the key or filter out nulls before the join.
• Avoid collect(), toPandas() and count() on large data inside pipelines. Each one triggers a full job.

Code

• Prefer built-in Spark SQL functions over Python UDFs. If you need custom Python, use pandas UDFs, which are vectorized.
• Cache only DataFrames reused several times in the same job, and unpersist them afterwards.
• Don’t repartition() without a reason; it forces a full shuffle.

Incremental loading

• Process only new or changed data: Auto Loader, Structured Streaming with trigger(availableNow=True), or Change Data Feed on source tables.
• For upserts, MERGE with a selective condition, and include the clustering column in the ON clause so fewer files are rewritten:

• Enable deletion vectors (default on newer runtimes) so updates and deletes don’t rewrite whole files.

3. Materialized views and streaming tables

Use a streaming table for append-only ingestion and a materialized view for aggregations and joins whose results must stay correct as sources change.

Keep materialized views cheap:

• Make incremental refresh possible. Serverless compute can refresh many MVs incrementally instead of fully. Enable row tracking and Change Data Feed on source Delta tables, and avoid non-deterministic functions such as current_timestamp() or rand() in the definition.
• Check what actually happened. The pipeline event log shows whether each refresh was incremental or a full recompute. A full recompute on every run is a cost red flag.
• Schedule to match the need. Refresh hourly if the dashboard is read hourly. Use SCHEDULE or triggered pipeline runs rather than continuous refresh by default.

CREATE OR REPLACE MATERIALIZED VIEW gold.daily_sales
SCHEDULE EVERY 1 HOUR
AS SELECT order_date, region, SUM(amount) AS revenue
FROM silver.orders
GROUP BY order_date, region;

• Keep definitions simple. Fewer layers of nested views and fewer joins per MV make incremental refresh more likely. Split complex logic into several MVs.
• Cluster large MVs on the columns dashboards filter by.

4. Declarative pipelines (Lakeflow, formerly DLT)

Declarative pipelines handle orchestration, retries and incremental processing for you, so most of your tuning goes into the layer design and the run mode.

Medallion layers

1. Bronze: raw data as streaming tables via Auto Loader. Keep transforms minimal; add ingestion metadata such as file name and load time.
2. Silver: cleaned, deduplicated, typed data. Use streaming tables, or AUTO CDC (APPLY CHANGES) for change data.
3. Gold: business aggregates as materialized views, shaped for the queries that read them.

Data quality with expectations

Catch bad data at Silver instead of debugging it in Gold:

CREATE OR REFRESH STREAMING TABLE silver.orders (
  CONSTRAINT valid_id     EXPECT (order_id IS NOT NULL) ON VIOLATION DROP ROW,
  CONSTRAINT valid_amount EXPECT (amount >= 0)
) AS SELECT * FROM STREAM(bronze.orders_raw);

Run mode and compute

• Triggered runs on a schedule and shuts down after. Use it unless you truly need minute-level latency; it is much cheaper.
• Continuous keeps compute running all the time. Reserve it for real low-latency needs.
• Serverless pipelines remove cluster tuning and enable incremental MV refresh. Use them where available.
• Use development mode while building (reuses the cluster, no retries) and production mode for scheduled runs.
• Avoid full refreshes in production unless the logic changed; they reprocess all history.

5. Compute and cost control

Most wasted spend comes from the wrong compute type and idle clusters, not from slow code.

Settings to check on every cluster or job:

• Autoscaling with a sensible min and max workers. Start small and raise the max only if the Spark UI shows waiting tasks.
• Auto-termination on every all-purpose cluster.
• Photon on for SQL and DataFrame-heavy work. It usually costs more per DBU but finishes faster, so compare total cost per run.
• Spot instances for workers on fault-tolerant batch jobs; keep the driver on-demand.
• Right-sized nodes. Memory-optimized for large joins and aggregations, compute-optimized for CPU-heavy parsing.
• Cluster policies to stop oversized clusters being created.
• Tags (team, project, environment) on every cluster and job so costs can be split in billing.

See who and what is spending, from the billing system table:

SELECT usage_metadata.job_id,
       SUM(usage_quantity) AS dbus
FROM system.billing.usage
WHERE usage_date >= current_date() - INTERVAL 30 DAYS
GROUP BY 1
ORDER BY dbus DESC
LIMIT 20;

6. Monitoring during development

Problems are cheapest to fix before deployment, so check the plan, run on realistic data and record a baseline for every job.

Before running: read the plan

EXPLAIN FORMATTED SELECT ... ;

Look for full scans with no pushed filters, SortMergeJoin where a broadcast would do, and Cartesian products.

After running: Spark UI (jobs and notebooks)

SQL warehouses: Query Profile

Open Query History, pick the query and view its profile. Check which operator takes the most time, rows and bytes read, and files pruned. Low pruning means filters aren’t using clustering or stats.

Test like production

• Use data volumes close to production, or a representative sample. Small test data hides skew and shuffle problems.
• Run twice: once cold and once warm, so caching doesn’t mislead you.

Record a baseline for every job

Write down runtime, DBUs per run, rows processed and data scanned. You’ll compare against these numbers after every change and in production.

7. Monitoring in production

In production, watch for slow drift as data grows, using system tables and alerts rather than waiting for complaints.

• Job duration trends from system.lakeflow.job_run_timeline. A job whose runtime grows week over week usually needs OPTIMIZE, better clustering or an incremental rewrite.
• Cost per job from system.billing.usage joined to job IDs. Flag any job whose DBUs jump more than about 20–30% from its baseline.
• Slow SQL from system.query.history: sort by duration and bytes read to find the worst dashboard queries.
• Pipeline health from the pipeline event log: failed expectations, full recomputes of MVs, and backlog on streaming tables.
• Data growth: track table sizes; a table that doubles may need its clustering keys or cluster size revisited.

Example: jobs that got slower this week versus last week:

WITH runs AS (
  SELECT job_id,
         date_trunc('week', period_start_time) AS wk,
         AVG(unix_timestamp(period_end_time) - unix_timestamp(period_start_time)) AS avg_sec
  FROM system.lakeflow.job_run_timeline
  WHERE period_start_time >= current_date() - INTERVAL 14 DAYS
  GROUP BY 1, 2
)
SELECT cur.job_id, prev.avg_sec AS last_week_sec, cur.avg_sec AS this_week_sec
FROM runs cur JOIN runs prev
  ON cur.job_id = prev.job_id AND cur.wk = prev.wk + INTERVAL 7 DAYS
WHERE cur.avg_sec > prev.avg_sec * 1.3
ORDER BY cur.avg_sec DESC;

Set up alerts:

• Job duration and failure notifications in each job’s settings (email or Slack).
• Databricks SQL alerts on queries like the one above, run daily.
• Budget alerts in the account console for overall spend.

System table names and columns change between releases, so confirm them in your workspace before relying on these queries.

8. Pre-deployment checklist

Run through this before promoting any ETL job, materialized view or pipeline to production.

Data layout

• Target tables are Delta (Unity Catalog managed where possible)
• Clustering or partitioning matches the main filter and join columns
• Auto-optimize or Predictive Optimization is on; VACUUM is scheduled

Code

• No SELECT *, no unnecessary collect(), count() or toPandas()
• Filters applied early; small tables broadcast
• Built-in functions used instead of Python UDFs
• Loads are incremental (Auto Loader, streaming, CDF or selective MERGE)

Materialized views and pipelines

• Streaming tables for ingestion, MVs for aggregates
• MV definitions are deterministic; event log confirms incremental refresh
• Triggered mode unless low latency is truly required
• Expectations defined on Silver tables
• Refresh schedule matches how often results are read

Compute and cost

• Job cluster or serverless, not all-purpose
• Autoscaling limits and auto-termination set
• Photon and spot workers evaluated against cost per run
• Cluster and job tagged with team, project and environment

Monitoring

• EXPLAIN and Spark UI or Query Profile reviewed on production-sized data
• Baseline recorded: runtime, DBUs, rows processed, data scanned
• Failure and duration alerts configured
• Job included in the weekly runtime and cost regression query