Optimizing Geospatial Queries with Dynamic File Pruning in Databricks Delta

Explore techniques for optimizing geospatial queries using dynamic file pruning in a 25-minute presentation by Databricks. Learn how to leverage z-ordering and dynamic file pruning to significantly reduce data retrieval from blob storage and improve query times, potentially by an order of magnitude. Discover specific techniques for handling petabytes of geospatial data, including data generation methods and SQL query design to ensure dynamic file pruning is included in the query plan. Examine real-world data examples, understand potential pitfalls and workarounds in the current implementation, and witness the impressive query performance achievable when properly executed. Gain insights into topics such as partitioning, dynamic partition pruning, Delta Log, Delta Data Skipping, bounding boxes, z-ordering, geohashing, and repartitioning by range. Evaluate file pruning effectiveness and learn which columns to use for optimal results in this comprehensive exploration of geospatial query optimization.

Introduction
Agenda
Background
Highlevel diagram
Databricks
Use Cases
Why we use these tools
Partitioning
Dynamic Partition Pruning
Dynamic File Pruning
Delta Log
Delta Data Skipping
Bounding Box
Data Set
Zorder Data
Zorder by
Measuring File Pruning Effectiveness
Which columns to use
Geohash
Getchas
Repartition by Range
Review Pipeline
Review Results