Hey, Cloud Gurus.
Welcome back to our further adventures in data,
continuing on with pruning data.
In this lesson, we're going to be taking a look at
what data skipping is,
how Z-ordering plays into that,
and really building up to dynamic file pruning,
and then making sure we know how that all ties together
in a review.
First step, data skipping.
There is metadata collection
that is happening automatically.
There's no need to enable this.
It's part of the Databricks runtime
and is enabled whenever applicable.
So data skipping information is automatically collected
when data is written into a Delta table.
This allows us to have target ranges,
where the Delta lake on Databricks
uses the minimum and maximum values to speed up queries.
The min and max values are part of
that aforementioned metadata,
and it is capitalizing on those to target specific data.
Now, this all depends on the data layout.
For the highest level of effectiveness,
something like Z-ordering should be used,
which begs the question, what is Z-ordering?
Well, this is a technique for co-locating
related information in the same set of files.
It is automatically used by the data-skipping algorithms
of Data Lake on Databricks
to substantially reduce the amount of data to be read.
And, of course, these are the same algorithms
that we were just speaking about.
All of that to say,
dynamic file pruning, or DFP,
is what we're using to prune out unnecessary data.
This is all about going fast.
It can dramatically improve query performance
because it allows files to be skipped within partitions.
Most of our performance tactics
have been centered around
limiting the result set as much as possible.
And so we've already looked at ways to partition data
and only pull back the partitions we need.
This is taking that a step farther
and allowing us to skip information
within the partition itself,
such as only searching for records in this week.
The performance impact is correlated
to the clustering that's used,
and so this is where Z-ordering comes in.
It relies on pre-sorted data such as Z-ordering clustering.
And so when you're using Z-ordering,
it is especially speedy.
It really stands out for the non-partitioned.
It's especially efficient for non-partitioned tables
or for joins of non-partitioned columns.
Some of the basic configurations you'll encounter are
spark.databricks.optimizer.dynamicPartitionPruning.
And this is the top-level setting to turn it on or off.
By default, it's set as true.
There's also
spark.databricks.optimizer.deltaTableSizeThreshold,
defaulting to 10 billion bytes, or 10 gigs.
And this represents the minimum size and bytes
of the Delta table on the probe side of the join
required to trigger DFP.
If the probe side is not very large,
it's probably not worthwhile to push down the filters,
and at that point, you can just scan the whole table.
Lastly, there's
spark.databricks.optimizer.deltaTableFilesThreshold,
defaulting to 10 for Databricks runtime 8.4 and above.
This represents the number of files of the Delta table
on the probe side of the join required to trigger DFP.
And when the probe-side table contains fewer files
than the threshold value, DFP is not triggered.
If a table only has a few files,
it's probably not worthwhile to enable dynamic file pruning.
By way of review, Delta Lake on Databricks
utilizes metadata to power data skipping.
Z-ordering co-locates related information
in the same set of files, pre sorting your data.
And then dynamic file pruning utilizes these technologies
to skip files within a partition based on filters.
This is a powerful technology for speeding up your queries.
And I hope this lesson helps you understand
how it accomplishes that.
Thanks for joining me.
And when you're ready,
I'll see you in the next video.