
Databricks makes it easy to write SQL that works. It’s harder to write SQL that stays fast and affordable as tables grow from millions to billions of rows, that a teammate can pick up without a walkthrough, and that doesn’t open a security hole. The good news is that most of the wins come from a short list of habits. This post pulls together the ones that matter most, drawing on Databricks’ own documentation alongside community and practitioner write-ups.
A note on versions: several recommendations below (liquid clustering, predictive optimization, managed tables, materialized views) assume Unity Catalog and a recent Databricks Runtime or SQL warehouse. Databricks changes its defaults regularly, so treat this as a solid starting point and check the release notes for your workspace.
Here is how the ten practices fit together. Performance starts with how tables are laid out, continues through how the query is written, and ends with measuring before you tune.
flowchart LR
A["Layout: name and design tables (practices 1, 6)"] --> B["Query: read less, join smart, merge smart (practices 2 to 5, 7)"]
B --> C["Reuse: precompute and parameterize (practices 8, 9)"]
C --> D["Verify: profile before tuning (practice 10)"]
D -.->|feedback| A1. Use the three-level namespace and name things consistently
Unity Catalog organizes data as catalog.schema.object. In shared code, jobs and dashboards, always reference tables by their full three-part name. Relying on whatever default catalog and schema happens to be set in a notebook is a classic reason code works in dev and breaks in prod.
SELECT order_id, order_date, amount
FROM prod_sales.silver.orders
WHERE order_date >= date_sub(current_date(), 7);A convention many teams use, shown below, is one catalog per environment with schemas organized by medallion layer or source system. The exact pattern matters less than picking one early. Renaming after dashboards and pipelines depend on it is painful.
flowchart TD
M["Metastore"] --> C1["dev_sales (catalog)"]
M --> C2["prod_sales (catalog)"]
C2 --> S1["bronze (schema)"]
C2 --> S2["silver (schema)"]
C2 --> S3["gold (schema)"]
S2 --> T["orders (table)"]
T --> N["Full name: prod_sales.silver.orders"]2. Select only the columns you need
Replace SELECT * with an explicit column list. Delta tables are stored in a columnar format, so reading fewer columns means reading less data. Explicit columns also protect you when someone adds a column upstream and make the query easier to review. Keep SELECT * for quick exploration, and pair it with a LIMIT.
3. Filter early, and write filters the engine can use
The cheapest row is the one you never read. A few habits help:
Filter before you join. Cut rows down in a CTE or subquery first. The optimizer often pushes filters down for you, but being explicit helps readers and helps when the plan gets complicated.
Filter on raw columns. Delta data skipping uses per-file statistics (minimum and maximum values, null counts and record counts), so a range filter on a plain column can skip whole files. Wrapping the column in a function usually prevents that. Statistics are collected on the first 32 columns by default, although predictive optimization can add frequently filtered columns on managed tables, so a filter on a column without statistics won’t skip anything.
-- Harder to skip files
WHERE year(order_date) = 2026
-- Easier
WHERE order_date >= '2026-01-01' AND order_date < '2027-01-01'Be careful with
LIKEandNOT IN. Practitioner guides flag both as expensive. A pattern that starts with a wildcard generally can’t benefit from data skipping, so prefer exact or prefix matches where you can. For “rows in A with no match in B”,NOT EXISTS(or an anti-join) is usually the better choice, and it avoids the surprising NULL behavior ofNOT IN.
4. Structure queries with CTEs, and know their limits
Common table expressions turn a wall of nested subqueries into a readable top-to-bottom story. Give each CTE one job and a descriptive name, and use comments to explain the why, not the what. They’re also the recommended alternative to repeating the same subquery or joining the same set of tables several times.
WITH recent_orders AS (
SELECT order_id, customer_id, amount
FROM prod_sales.silver.orders
WHERE order_date >= date_sub(current_date(), 30)
),
customer_totals AS (
SELECT customer_id, SUM(amount) AS total_amount
FROM recent_orders
GROUP BY customer_id
)
SELECT c.customer_name, t.total_amount
FROM customer_totals AS t
JOIN prod_sales.gold.dim_customer AS c USING (customer_id)
ORDER BY t.total_amount DESC
LIMIT 100;The caveat: a CTE is a readability tool, not a cache. In open-source Spark, deterministic CTEs are inlined, so a CTE referenced three times can be planned and executed three times, unless the optimizer happens to find identical work to reuse. Check the plan (see practice 10) rather than assuming. If an expensive intermediate result is reused heavily, persist it (a Delta table or a materialized view, see practice 8).
If you run SQL from Python, one practitioner coding standard suggests assigning the statement to a variable instead of burying it inline in spark.sql(...). It makes the query easier to log, test and parameterize.
5. Join with intent
Joins are where most slow queries live. Some guidelines:
Join on columns with matching data types. Practitioner guides warn that mismatched types force implicit conversions that can block optimizations.
Broadcast small tables. When one side is small, such as a dimension table, a broadcast hint avoids shuffling the big side.
SELECT /*+ BROADCAST(d) */ f.order_id, d.region_name, f.amount
FROM prod_sales.silver.orders AS f
JOIN prod_sales.gold.dim_region AS d
ON f.region_id = d.region_id
WHERE f.order_date >= '2026-01-01';Let adaptive query execution (AQE) work. AQE re-optimizes while the query runs: it can switch a sort-merge join to a broadcast join, coalesce shuffle partitions and handle skewed joins. Databricks notes that manual skew hints are no longer required when AQE and its skew-join setting are on, which is the default. The docs also point out that a static broadcast hint can still beat AQE’s dynamic choice when you know your data well, because AQE may only switch after both sides have already been shuffled.
Don’t join the same table repeatedly. If you’re joining one table several times just to apply different conditions, conditional aggregation with
CASEis usually simpler and cheaper:
SELECT customer_id,
SUM(CASE WHEN status = 'completed' THEN amount END) AS completed_amount,
SUM(CASE WHEN status = 'returned' THEN amount END) AS returned_amount
FROM prod_sales.silver.orders
GROUP BY customer_id;When a join is slow, this is a reasonable order of questions to ask:
flowchart TD
A["Join is slow or expensive"] --> B{"Is one side small?"}
B -->|"Yes"| C["Add a BROADCAST hint on the small side"]
B -->|"No"| D{"Do the last few tasks in a stage run far longer than the rest?"}
D -->|"Yes"| E["Likely skew: confirm in the query profile and lean on AQE skew handling"]
D -->|"No"| F["Revisit practices 2 and 3, and check join key types"]
C --> G["Re-check the query profile"]
E --> G
F --> G6. Design tables for the way you query them
Often the biggest lever isn’t in the query at all. Databricks’ current guidance:
Use liquid clustering instead of partitioning and Z-ordering. It’s the recommended default for new tables. Cluster by the columns you filter on most, then filter on those columns in your queries. Liquid clustering is not compatible with partitioning or
ZORDERon the same table, so choose one layout strategy rather than stacking them.Use Unity Catalog managed tables with predictive optimization. Predictive optimization runs
OPTIMIZE,VACUUMandANALYZEfor you on managed tables, which keeps file sizes healthy and statistics fresh for the cost-based optimizer.Stick with Delta. Most Databricks Runtime optimizations require Delta Lake, the default table format, so creating tables in another format is a common beginner mistake.
CREATE TABLE prod_sales.silver.orders (
order_id BIGINT,
customer_id BIGINT,
region_id INT,
order_date DATE,
amount DECIMAL(18, 2)
)
CLUSTER BY (order_date, region_id);
-- Access patterns changed? Redefine the keys without rewriting the table.
ALTER TABLE prod_sales.silver.orders CLUSTER BY (customer_id, order_date);
-- Apply the new keys to data that was already written (costs I/O and time).
OPTIMIZE prod_sales.silver.orders FULL;A few things to remember:
Clustering happens through
OPTIMIZE(and on write, once a transaction is large enough). If you don’t use predictive optimization, schedule regularOPTIMIZEruns, because settingCLUSTER BYalone doesn’t cluster data that’s already there.When you change keys, later
OPTIMIZEruns and writes use the new keys, but existing data isn’t rewritten. RunOPTIMIZE ... FULLto rewrite it, which can take hours on a large table.You can specify up to four clustering keys, and they must be columns that have statistics collected.
Databricks recommends automatic liquid clustering (the
CLUSTER BY AUTOclause) to choose keys from your query patterns. It applies to Unity Catalog managed tables, relies on predictive optimization, and changes keys only when the predicted data-skipping savings outweigh the clustering cost.To move an existing partitioned Delta table to liquid clustering, Databricks Runtime 18.1 and above supports
ALTER TABLE ... REPLACE PARTITIONED BY WITH CLUSTER BY.
flowchart TD
A["Choosing a table layout"] --> B{"New table?"}
B -->|"Yes"| C["Create a managed Delta table with CLUSTER BY"]
B -->|"No"| D{"Partitioned or Z-ordered today?"}
D -->|"Yes"| E["Consider converting to liquid clustering"]
D -->|"No"| F["Enable it on an unpartitioned table with ALTER TABLE CLUSTER BY"]
C --> G{"Managed table with predictive optimization?"}
E --> G
F --> G
G -->|"Yes"| H["Use CLUSTER BY AUTO"]
G -->|"No"| I["Pick up to four frequently filtered columns"]
H --> J["Predictive optimization selects keys and clusters data"]
I --> K["Schedule regular OPTIMIZE runs"]7. Make MERGE cheap
By default, MERGE searches the entire target table for matches. A few ways to cut that cost:
Reduce the search space by adding known constraints to the match condition. This also reduces the chance of conflicts with concurrent operations.
Control shuffle partitions, since merge shuffles data multiple times and
spark.sql.shuffle.partitionsgoverns the task count.Enable optimized writes on partitioned tables, where merge can otherwise produce lots of small files.
Compact small files. If the data sits in many small files, reading it to find matches gets slow.
OPTIMIZE, or predictive optimization, fixes that.
Some help is already automatic. Low Shuffle Merge, on by default, processes unmodified rows without a shuffle and preserves their layout. Deletion vectors, which Predictive I/O uses on Photon-enabled compute, let updates, deletes and merges avoid rewriting whole files, though for Delta tables you have to enable them explicitly.
MERGE INTO prod_sales.silver.orders AS t
USING staging.orders_updates AS s
ON t.order_id = s.order_id
AND t.order_date >= current_date() - INTERVAL 3 DAYS -- known constraint: the batch only holds recent orders
WHEN MATCHED THEN UPDATE SET *
WHEN NOT MATCHED THEN INSERT *;A word of caution: only add a constraint you know holds for every source row. This example assumes the batch contains only orders from the last three days. If an older row slips in, it can’t match its existing record and will be inserted as a duplicate.
flowchart LR
subgraph NoPrune["Match on order_id only"]
S1["Source batch"] --> T1["Scan the whole target table"] --> R1["Rewrite many files"]
end
subgraph Prune["Match on order_id plus a known constraint"]
S2["Source batch"] --> T2["Skip files that cannot match"] --> R2["Rewrite only the files touched"]
end8. Precompute the expensive work, and be careful with caching
If the same heavy aggregation feeds a dashboard that refreshes all day, compute it once, not on every refresh. Databricks’ BI guidance recommends materialized views and persisted tables to pre-aggregate the most common and resource-intensive queries, and generated columns for frequently calculated fields so they aren’t recomputed at query time.
CREATE MATERIALIZED VIEW prod_sales.gold.daily_revenue AS
SELECT order_date, region_id, SUM(amount) AS revenue
FROM prod_sales.silver.orders
GROUP BY order_date, region_id;
-- Refresh on demand (refreshes can also be scheduled)
REFRESH MATERIALIZED VIEW prod_sales.gold.daily_revenue;Materialized views in Databricks SQL are Unity Catalog managed tables backed by a serverless pipeline, so you need a Unity Catalog-enabled pro or serverless SQL warehouse. Refreshes on serverless compute attempt an incremental refresh and fall back to a full recompute when the query doesn’t support it, so it’s worth testing your query with EXPLAIN CREATE MATERIALIZED VIEW.
On caching, advice varies. Some community guides recommend caching frequently accessed data, but Databricks’ own Delta Lake best-practices page advises against Spark caching on Delta tables. You lose data skipping from any extra filters applied on top of the cached data, and cached results may not reflect changes if the table is accessed under a different identifier. Databricks also applies disk caching automatically on supported instance types, which speeds up repeated reads of the same data files with no code changes.
9. Parameterize your queries; never concatenate them
Pasting values into SQL strings with f-strings or string concatenation invites SQL injection and makes queries harder to reuse. Databricks supports parameter markers, named (:region) or unnamed (?), that keep values separate from the statement’s structure.
# Risky: string concatenation
spark.sql(f'SELECT order_id, amount FROM prod_sales.silver.orders WHERE region_id = {user_input}')
# Safer: named parameter markers
df = spark.sql(
'SELECT order_id, amount FROM prod_sales.silver.orders '
'WHERE region_id = :region AND order_date >= :start',
args={'region': 7, 'start': '2026-01-01'},
)sequenceDiagram
participant U as User input
participant A as Python job
participant E as SQL engine
U->>A: region = 7 OR 1=1
alt String concatenation
A->>E: One SQL string with the input pasted in
E-->>A: Input is parsed as SQL, so injection is possible
else Parameter markers
A->>E: Statement with a named marker, plus the value as a separate argument
E-->>A: Input is bound as a typed value only
endValues aren’t the only thing that varies. For table, column or schema names, use the IDENTIFIER clause, which provides injection-safe parameterization of object names. Following the docs’ pattern, pass a qualified name as a single parameter with backticks:
spark.sql(
'SELECT * FROM IDENTIFIER(:tbl) LIMIT 10',
args={'tbl': '`prod_sales`.`silver`.`orders`'},
)In notebooks and jobs, pass environment-specific values (catalog names, dates) as widgets or job parameters, so the same code runs unchanged in dev, test and prod.
Two version notes. Before Databricks Runtime 18.0, parameter markers can’t be used in DDL such as view definitions, SQL functions, and DEFAULT or generated-column definitions, with IDENTIFIER as the exception for object names. From 18.0 they work wherever a literal of the same type is allowed. Also, before 18.0 you can’t qualify the IDENTIFIER clause or use it as a qualifier (for example myschema.IDENTIFIER(...)), which is why the example above passes the whole name in one parameter.
10. Measure first, then tune
Optimization by intuition is how teams end up with a bigger cluster and the same slow query.
Find the bottleneck before changing anything. In Databricks SQL, read the query profile and performance insights, which are ranked by estimated effect, flag things like small files and data skew, and show which optimizations were already applied. For notebooks and jobs, use the Spark UI.
Don’t make compute your first response. A larger cluster can make an inefficient query finish faster while also making it more expensive.
Use
EXPLAINto inspect the plan for a large query before you run it.Stay current and clear out legacy settings. Use a recent runtime and Photon-enabled compute, since some optimizations require them. Databricks also recommends removing old explicit Delta configurations when you upgrade, because legacy settings can block newer optimizations and defaults.
flowchart TD
A["Query is slow or costly"] --> B["Open the query profile (SQL) or the Spark UI (notebooks and jobs)"]
B --> C{"What dominates?"}
C -->|"Too much data scanned"| D["Revisit practices 2, 3 and 6: fewer columns, selective filters, table layout"]
C -->|"Skew in a join or aggregation"| E["Check AQE behavior, then consider hints"]
C -->|"Many small files"| F["Run OPTIMIZE or enable predictive optimization"]
C -->|"Same heavy work repeated"| G["Precompute with a materialized view (practice 8)"]
D --> H["Re-run and compare"]
E --> H
F --> H
G --> H
H -->|"Improved"| J["Document the fix and move on"]
H -->|"Not enough"| B
H -->|"Query is efficient but still too slow"| I["Scale compute"]Bonus: treat SQL like code
The practices above are about performance. These keep a team sane:
Modularize notebooks. Break work into logical cells instead of one giant cell, so each step can be tested and debugged on its own.
Document in markdown cells (
%md) rather than scattering long comments through code.Automate. Schedule recurring work with Lakeflow Jobs (formerly Databricks Workflows) instead of running scripts by hand.
Set table ownership deliberately. Assign owners (ideally a group, not one person) so tables don’t become orphaned when someone changes teams.
Quick checklist
Full three-part table names everywhere
Explicit columns, not
SELECT *Filters on raw columns, ideally on clustering keys
CTEs for structure, but not assumed to be cached
Broadcast hints for small tables; trust AQE for skew
Liquid clustering, managed tables, predictive optimization
MERGE with a pruning condition that is safe for every source row
Materialized views for repeated heavy aggregations
Parameter markers and
IDENTIFIER, never string concatenationQuery profile before tuning, and compute last
Sources and further reading
Databricks: Optimization recommendations
Databricks: Best practices: Delta Lake
Databricks: Use liquid clustering for tables
Databricks: Data skipping
Databricks: Skew join optimization
Databricks: Adaptive query execution
Databricks: Parameter markers
Databricks: IDENTIFIER clause
Databricks: Materialized views in Databricks SQL
Databricks: Incremental refresh for materialized views
Databricks: BI serving: data preparation
Databricks: Query performance insights
Databricks: Renamed and deprecated product names
Apache Spark JIRA: CTE referenced multiple times and reuse (SPARK-56685)
Databricks Community: Databricks Performance Optimization: What Changed, What Still Matters
Databricks Community: Best practices for writing efficient SQL queries in Databricks
Databricks Community: How to Structure Unity Catalog Like a Pro
Prophecy: A Guide to Query Optimization Techniques for Databricks SQL
Perficient: PySpark Coding Standards & Best Practices
Dev Genius: 11 Common Databricks Mistakes Beginners Make
Comments(0)
Join the discussion by logging into your account.
No comments yet. Be the first to comment!