Performance Tips for Databricks: Optimizing Delta Lake and Spark 3.0

Effectively using Databricks is essential for processing large volumes of data with high performance and reduced costs. Delta Lake and Apache Spark 3.0 are key components in achieving this goal. However, running pipelines and queries at scale requires following best practices to optimize performance. This article provides a comprehensive set of tips and techniques, including practical code examples and advanced configurations, to help you get the best performance out of your Databricks environment.

1. Use Photon and Updated Runtimes

Photon is Databricks' native execution engine, optimized for compute-intensive queries, improving speed without requiring code changes.

Advantages

• Up to 12x faster than other cloud data warehouses.
• 40% cost savings in ETL operations.
• Better performance for joins and aggregations on structured and semi-structured data.

Configuration Example

SET spark.databricks.photon.enabled = true;        

Always use the latest Databricks Runtime (DBR), as every new release brings significant improvements. For example:

• DBR 12.1: Introduced Deletion Vectors, accelerating DML operations by up to 10x.
• DBR 11.2: Added dynamic partition pruning and Graviton support.

2. Partitioning and Z-Ordering: Control I/O

Avoid Excessive Partitioning

Partitioning can speed up queries, but improper usage can hurt performance. Use partitioning only on large tables (above 1 TB) and keep file sizes between 32 MB and 64 MB.

Code Example – Creating a Partitioned Table with a Generated Column

CREATE TABLE events (
  event_id BIGINT,
  event_time TIMESTAMP,
  event_date DATE GENERATED ALWAYS AS (CAST(event_time AS DATE))
) PARTITIONED BY (event_date);        

With generated columns, you can efficiently apply partition filters:

SELECT * FROM events WHERE event_time >= '2024-01-01' AND
event_time < '2024-02-01';        

In this example, Spark automatically applies a partition filter on the event_date column, reducing unnecessary data reads.

Improving Queries with Z-Ordering

Z-Ordering physically reorganizes data to improve filter-based queries.

OPTIMIZE my_table ZORDER BY (filter_column1, filter_column2);        

• Use up to 3 columns for Z-Ordering.
• Focus on columns frequently used in WHERE clauses.

3. Efficient Joins and Merges

With Spark 3.0, Adaptive Query Execution (AQE) dynamically adjusts the query execution plan based on runtime metrics.

Join Strategies

• Broadcast Join: Use for small tables that fit into memory.
• Sort-Merge Join: Best for large datasets.

Join and Partition Configuration

SET spark.sql.autoBroadcastJoinThreshold = 100 * 1024 * 1024; SET spark.sql.shuffle.partitions = auto;        

Optimizing Merges with Filters

Include as many filters as possible in the ON clause to reduce I/O during merges:

MERGE INTO my_table t USING my_changes m ON t.id = m.id AND t.date > current_date() - INTERVAL 7 days WHEN MATCHED THEN UPDATE SET t.value = m.value WHEN NOT MATCHED THEN INSERT (id, value) VALUES (m.id, m.value);        

4. Deletion Vectors: Reducing Write Amplification

Deletion Vectors eliminate the need to rewrite entire files during updates and deletions, significantly improving merge performance and reducing write amplification.

How Deletion Vectors Work

Each row in a file corresponds to a bit in the deletion vector. When a row is deleted, the respective bit is marked, preventing unnecessary rewrites:

• Original file:
• Deletion vector:

5. Minimize Network Data Movement

Reducing data movement across the network is crucial for performance. Use efficient join strategies and avoid unnecessary shuffles.

• Collect Statistics:
• Enable AQE to dynamically adjust query plans:

6. Caching and Delta Cache for Frequently Accessed Data

Caching can significantly reduce query runtimes. Enable Delta Cache for specific instances to avoid redundant reads:

SET spark.databricks.io.cache.enabled = true;        

Additionally, cache query results to optimize frequent queries:

CACHE TABLE my_table;        

7. Choosing the Right Cloud Instances

Choosing the correct instance types affects both cost and performance. On Azure, explore the dav4, eav4, and f-series instances, while on AWS, prefer m6gd or r6gd instances.

Considerations for Instance Selection

• Core-to-RAM ratio: 1 core for every 2 GB.
• Local vs. remote storage: Prefer local storage for I/O-intensive operations.

If shuffle operations need optimization:

SET spark.sql.shuffle.partitions = largest_shuffle / 200mb;        

8. Monitoring Logs and Handling Failures

Failures are common in complex pipelines. Regularly review event logs to identify bottlenecks:

1. Open the SQL UI in Databricks.
2. Check for spill events (disk writes). If present, adjust shuffle partitions to improve performance.

Conclusion

By implementing the performance practices discussed in this article, you can maximize your Databricks environment's efficiency, optimize Delta Lake and Spark 3.0 operations, and minimize costs. From selecting the correct join strategy to leveraging Photon and Z-Ordering, every optimization detail can make a significant difference.

For more tips and insights on Data Engineering and Data Science, follow my profile on LinkedIn . There, you will find a variety of articles, courses, and in-depth content to help you become an expert in the field.