Optimizing Lakehouse Tables and Spark Performance

What it is

A lakehouse table is a folder of Parquet files plus a Delta log. It performs well when it has a moderate number of large, well-organized files. Frequent small writes, streaming and merges create many small files, and every query then pays to open them.

Spark performance depends on the same files and on the compute: pool and node sizes, session start-up, the engine used, and code that keeps work distributed.

How it works

Optimize a lakehouse table

%%sql
OPTIMIZE sales_orders ZORDER BY (customer_id) VORDER;
VACUUM sales_orders RETAIN 168 HOURS;

The default retention for VACUUM is 7 days (168 hours). A shorter period removes the files that time travel and long-running readers need, so Delta blocks it unless you deliberately disable the safety check.

You can also run these from the lakehouse explorer: choose Maintenance on a table to run OPTIMIZE (optionally with V-Order) and VACUUM with a retention threshold. A notebook or pipeline can schedule maintenance for many tables.

Partition only large tables, on columns with few distinct values. Partitioning by a high-cardinality column such as order_id or a timestamp creates thousands of tiny files and makes queries slower.

Optimize Spark performance

Compute choices

Enable the native execution engine in an environment's acceleration settings, or for one session:

%%configure
{
    "conf": {
        "spark.native.enabled": "true"
    }
}

Code choices

• Filter and select columns early, so Delta can skip files and Parquet reads fewer columns.
• Broadcast small dimension tables in joins, so the large table is not shuffled.
• Prefer built-in functions to Python UDFs, which move data between the JVM and Python.
• cache() a DataFrame only when it is reused several times, and unpersist it after.
• Avoid collect() and toPandas() on large data; write results to a table.
• Repartition on a better key when one task runs far longer than the rest (data skew).

from pyspark.sql import functions as F

fact = spark.read.table("fact_sales").where(F.col("order_date") >= "2026-01-01")
dim = spark.read.table("dim_product").select("product_key", "category")

result = (fact.join(F.broadcast(dim), "product_key")
    .groupBy("category")
    .agg(F.sum("amount").alias("revenue")))

Adaptive query execution is on by default in Spark 3, so Spark already coalesces shuffle partitions and can switch join strategies at run time. Use the Spark UI to see slow stages, spills to disk and skewed tasks.

A worked example

A streaming job appends to iot_readings every minute. After three months, queries that took seconds take minutes, and the table folder holds hundreds of thousands of small files.

A nightly notebook runs OPTIMIZE iot_readings ZORDER BY (device_id) VORDER and a weekly VACUUM with the default retention. Queries filtering by device now skip most files. The team also turns on the native execution engine in the environment for the aggregation notebooks.

How the exam asks about this

• "A Delta table has many small files and slow queries." Run OPTIMIZE.
• "Improve Direct Lake and SQL endpoint read performance of a lakehouse table." Apply V-Order.
• "Remove unreferenced files to reduce storage while keeping 7 days of time travel." VACUUM with the default 168 hour retention.