Understanding EXPLAIN FORMATTED in Databricks SQL

As data engineers, we spend a significant amount of time writing SQL queries to ingest, transform, and analyse data. However, writing a query that returns the correct results is only part of the equation. Equally important is understanding how Spark plans to execute that query.

That's where EXPLAIN FORMATTED becomes an invaluable tool.

In this article, I'll demonstrate how to use EXPLAIN FORMATTED in Databricks SQL, explain why it should be part of every data engineer's toolkit, and show how it helps us understand the execution strategy chosen by the Spark Catalyst Optimizer.

What is EXPLAIN FORMATTED?

EXPLAIN FORMATTED is a Databricks SQL command that returns a formatted execution plan for a SQL query without executing it.

Instead of returning the query results, Databricks displays the physical execution plan generated by the Spark Catalyst Optimizer, together with detailed information about each execution stage.

This is particularly useful when you want to:

✅Understand how Spark plans to execute a query.
✅Investigate slow-running SQL workloads.
✅Identify unnecessary sorts, scans, or shuffles.
✅Verify how window functions are processed.
✅Tune queries for better performance before deploying them into production.

Unlike SQL Server's SHOWPLAN_ALL, which is a session-level setting, EXPLAIN FORMATTED is applied to a single SQL statement. Once the execution plan is displayed, your next query executes normally.

Sample Query

For this walkthrough, I'll use the following query, which calculates three window functions against an Orders table stored in Databricks.

EXPLAIN FORMATTED 
SELECT 
    order_id,
    order_date,
    region,
    customer,
    amount,
    ROW_NUMBER() OVER (ORDER BY order_date) AS rn,
    RANK() OVER (ORDER BY order_date) AS ranking,
    DENSE_RANK() OVER (ORDER BY order_date) AS dense_ranking
FROM 
    sales_cat.orders_schema.orders;

Notice that the query begins with EXPLAIN FORMATTED.

Instead of returning the rows from the orders table, Databricks returns a detailed execution plan describing how Spark intends to execute the query.

Why This Query Makes a Great Example

This query is an excellent candidate for examining execution plans because it uses three different window functions:

ROW_NUMBER()
 RANK()
DENSE_RANK()

Although these functions appear similar, Spark still needs to perform several internal operations before it can calculate them efficiently.

By inspecting the execution plan, we can better understand what Spark is doing behind the scenes.

What Happens Behind the Scenes?

Although the exact execution plan depends on your cluster configuration, table statistics, and Spark version, the Catalyst Optimizer will generally perform operations similar to the following:

1. Scan the Delta Table

The optimizer first reads the data from:

text
sales_cat.orders_schema.orders

If the table is stored as a Delta table, Spark takes advantage of Delta Lake optimizations such as metadata pruning and predicate pushdown where applicable.

2. Project Required Columns

Spark only selects the columns referenced in the query:

order_id
 order_date
* region
* customer
* amount

This reduces unnecessary data movement throughout the execution plan.

3. Sort the Data

Since all three window functions are ordered by:

sql ORDER BY order_date

Spark performs a sort of operation before computing the rankings.

Sorting is often one of the most expensive operations in analytical workloads because every row must be ordered correctly before the window calculations can begin.

4. Compute the Window Functions

After sorting, Spark calculates:

sql ROW_NUMBER()
sql RANK()
sql DENSE_RANK()

One thing I particularly like about this query is that all three window functions use the same ordering clause.

Rather than performing three independent sorts, the Catalyst Optimizer can often reuse the same sorted dataset and compute all three rankings within a single Window operator.

This is one of the many optimizations that Spark performs automatically.

5. Return the Final Projection

Finally, Spark projects the requested columns together with the three calculated ranking columns before returning the results.

Why Use EXPLAIN FORMATTED Instead of EXPLAIN?

Databricks provides several variants of the EXPLAIN command, including:

EXPLAIN
EXPLAIN FORMATTED
EXPLAIN EXTENDED
EXPLAIN COST
EXPLAIN CODEGEN

For day-to-day SQL tuning, I generally recommend EXPLAIN FORMATTED.

Its output is significantly easier to read because it organizes the execution plan into logical sections and provides additional information about each operator.

Rather than viewing a long block of text, you can quickly identify the major stages involved in query execution.

What Should Data Engineers Look For?

When reviewing an execution plan, I typically look for answers to questions such as:

✅Is Spark scanning the entire table?
✅Is there an expensive Sort operation?
✅Are Shuffle operations occurring?
✅Is Spark creating unnecessary Exchange operators?
✅Are multiple Window operators being generated?
✅Can partitioning improve performance?
✅Would Liquid Clustering reduce the amount of data being scanned?

Understanding these operators often reveals why one query performs significantly better than another.

Why This Matters for Window Functions

Window functions are extremely common in modern data engineering.

They are used for:

Ranking customers

Calculating running totals

Finding the first or last transaction
Detecting duplicates
Performing change data analysis
Building Slowly Changing Dimensions (SCDs)

Because window functions frequently require sorting large datasets, they can become expensive as data volumes increase.

Using EXPLAIN FORMATTED allows us to verify how Spark plans to process these operations before running the query against billions of rows.

Conclusion

Modern data engineering is about much more than writing SQL that produces the correct answer. It's about building solutions that continue to perform as data volumes grow from thousands to billions of records.

The Spark Catalyst Optimizer does an excellent job of transforming SQL into efficient execution plans, but it shouldn't remain a black box.

By incorporating EXPLAIN FORMATTED into your development workflow, you gain visibility into how Spark processes your queries, how window functions are executed, and where performance bottlenecks may exist.

The next time you write a complex SQL query in Databricks, take a moment to prepend EXPLAIN FORMATTED. The execution plan may reveal optimization opportunities that aren't immediately obvious from the SQL itself—and those insights can make a measurable difference in the performance and scalability of your data pipelines.