--- title: "Writing SQL in Databricks: 10 Best Practices for Faster, Cheaper, Safer Queries" author: "Pradeep Kumar" published: "2026-11-18T13:54:30.468Z" updated: "2026-11-18T13:54:30.468Z" url: "https://zyvop.com/writing-sql-in-databricks-10-best-practices-for-faster-cheaper-safer-queries-s9df6" canonical: "https://zyvop.com/writing-sql-in-databricks-10-best-practices-for-faster-cheaper-safer-queries-s9df6" tags: ["Data Engineering","Databricks","SQL","Big Data","query-optimization"] --- # Writing SQL in Databricks: 10 Best Practices for Faster, Cheaper, Safer Queries 10 practical Databricks SQL best practices to improve query performance, reduce compute costs, strengthen security, and write maintainable SQL. ![Cover image]() 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. ```mermaid 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| A ``` ## 1\. 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. ```sql 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. ```mermaid 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. ```sql -- 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** `LIKE` **and** `NOT 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 of `NOT 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. ```sql 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. ```sql 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 `CASE` is usually simpler and cheaper: ```sql 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: ```mermaid 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 --> G ``` ## 6\. 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 `ZORDER` on the same table, so choose one layout strategy rather than stacking them. - **Use Unity Catalog managed tables with predictive optimization.** Predictive optimization runs `OPTIMIZE`, `VACUUM` and `ANALYZE` for 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. ```sql 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 regular `OPTIMIZE` runs, because setting `CLUSTER BY` alone doesn’t cluster data that’s already there. - When you change keys, later `OPTIMIZE` runs and writes use the new keys, but existing data isn’t rewritten. Run `OPTIMIZE ... FULL` to 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 AUTO` clause) 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`. ```mermaid 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.partitions` governs 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. ```sql 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. ```mermaid 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"] end ``` ## 8\. 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. ```sql 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. ```python # 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'}, ) ``` ```mermaid 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 end ``` Values 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: ```python 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** `EXPLAIN` to 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. ```mermaid 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 concatenation - Query 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]() - Packt: [Understanding Spark SQL optimizations]() - Prophecy: [A Guide to Query Optimization Techniques for Databricks SQL]() - Perficient: [PySpark Coding Standards & Best Practices]() - Dev Genius: [11 Common Databricks Mistakes Beginners Make]()