►
Description
Apache Spark™️ has become the de-facto open-source standard for big data processing due to its ease of use and performance. And the open-source Delta Lake project enhances Spark’s lead with new capabilities like ACID transactions, Schema Enforcement and Time Travel. These features help ensure that data lakes and data pipelines can deliver high-quality, reliable data to downstream data teams for successful data analytics and machine learning projects.
In this tech talk, we will discuss the top tuning tips for Apache Spark 3.0 and Delta Lake on Databricks. Come prepared to ask your questions and join Joe Widen, Chris Hoshino-Fish, and Denny Lee to discuss when to use which join operations, how to pick your machine sizes, how to help speed up your merge operations, and how to make your jobs easier!
Link to slides and the notebooks used in this tutorial: https://github.com/databricks/tech-talks
Chapters
0:00 Welcome
02:52 Use the latest version of DBR
04:53 Picking the best join strategy
13:39 Use Apache Spark 3.0 and AQE
26:27 Partition Pruning
28:36 Data Skipping
31:24 Z-Ordering
39:34 Databricks Delta Lake and Stats
44:39 Optimizing Merges
47:24 Picking good instance types
Speakers:
Chris Hoshino-Fish is a Solutions Architect at Databricks. Chris is an active member of the Performance Subject Matter Expert group and a former Principal Consultant focused on Data Engineering, working with several Fortune 500 Databricks customers. Prior to Databricks, Chris worked for an adtech company as a data engineer managing pipelines using Apache Spark for 3.5 years. Chris has a B.A. in Computational Mathematics from University of California, Santa Cruz.
Denny Lee is a developer advocate at Databricks, where he works on Delta Lake, Apache Spark, Data Sciences, and Healthcare Life Sciences. He has previously built enterprise DW/BI and big data systems at Microsoft including Azure Cosmos DB, Project Isotope (HDInsight), and SQL Server as well as the Senior Director of Data Sciences Engineering at SAP Concur. Denny holds a Masters in Biomedical Informatics from Oregon Health Sciences University.
Joe Widen is a Solutions Architect at Databricks. Joe leads the Performance and Delta SME horizontal initiatives along with making customers successful with the Databricks Unified Analytics Platform. Joe has been working with Spark and more generally Hadoop for 5 years, with previous stops at Hortonworks and Capital One.
To join the zoom live chat:
https://www.meetup.com/data-ai-online/events/274093223/ Databricks is proud to announce that Gartner has named us a Leader in both the 2021 Magic Quadrant for Cloud Database Management Systems and the 2021 Magic Quadrant for Data Science and Machine Learning Platforms. Download the reports here. https://databricks.com/databricks-named-leader-by-gartner
A
Perfect,
karen
thanks
very
much
really
excited
to
have
the
session,
if
you
don't
know
which
session
you're
you're
currently
on
the
top
tuning
tips
with
the
apache
spark
in
delta
lake
on
databricks,
my
name
is
denny
lee,
I'm
a
developer
advocate
and
long
time,
data
geek,
you're,
probably
sick
of
me
at
this
point.
So
let
me
switch
right
over
to
chris
fish
and
joe
one
chris.
Once
you
start
it
and
then
joe
you'll
be
right
after
that.
C
B
C
All
right
so
we'll
go
ahead
and
get
started
with
our
tuning
tips
here.
So
these
are
the
top
five
tuning
tips
that
I
generally
give
to
all
of
the
companies
that
I
work
with
when
I'm
working
with
them.
I
generally
go
through
a
progression
of
different
things
to
make
their
jobs
run
faster
when
they're
using
data
bricks
and
delta
lake
and
spark
3.0,
so
we'll
go
ahead
and
share
those
with
everybody.
C
C
Using
spark
3.0
with
adaptive
query
execution,
we'll
talk
a
little
bit
about
delta
lake,
how
it
works,
how
to
think
about
delta
lake
and
how
to
optimize
it
and
then,
at
the
very
end,
if
we
do
have
time,
we
will
try
to
send
or
tell
you
how
to
pick
the
right
machines
and
we'll
try
and
be
very
prescriptive
on
all
this.
I
know
in
the
past
a
lot
of
people
had
said
that
working
with
spark
is
really
difficult,
but
when
you
know
what
to
do,
it's
actually
really
easy
to
do
so.
C
C
C
So
when
you
go
from
databrick
6.6
to
7.0,
you
actually
got
an
upgraded
version
of
spark
from
2
4
to
3.0.
You
got
some
features
in
delta
lake,
like
the
autoloader
and
the
copy
into,
and
you
got
some
performance
improvements
like
dynamic,
subquery,
reuse,
the
next
one.
If
you
went
to
7.1
there
are
a
bunch
of
performance
improvements
in
merge
into
in
addition
to
inserts,
updates
and
deletes
it's
the
same
version
of
spark.
If
I
remember
right,
it
might
be.
C
3
0
to
301
and
all
you
had
to
do
was
upgrade
to
get
those
performance
improvements.
Seven
one
two,
seven
two:
we
upgraded
the
cloud
connector.
So
if
you
were
working
on
the
amazon
cloud,
you'd
probably
see
a
bunch
of
s3
requests
that
were
optimized
on
the
azure
platform,
we're
always
improving
the
performance
of
adls,
gen
2
and
the
wsav
connector.
So
that's
there
and
then
in
dvr
7.3.
C
You
may
have
noticed
your
delta
queries
execute
a
lot
faster
to
begin
with,
so
there
were
some
metadata
overhead
times
that
were
drastically
reduced
just
by
upgrading
and
again,
if
I
remember
right,
the
difference
between
7.0
and
7.3
is
for
3.0
to
spark
3.0.1,
so
very
minimal
impact
to
the
upgrade
and
you
get
all.
These
benefits
by
upgrading
databricks
has
tons
of
regression
tests
built
in
and
we
don't
actually
release
a
new
version
of
the
databricks
runtime.
C
C
So
on
the
left
here
we
see
the
different
type
of
joins
that
are
commonly
used
in
spark
and
from
the
top
to
the
bottom,
is
decreasing
in
performance.
So
the
broadcast
hash
join
is
by
far
the
fastest
joining
spark.
Then
we
have
the
shuffle
hash
join
then
the
sword
merge
join
and
then
the
dreaded
cartesian
jump.
So
again
the
broadcast
joins
the
fastest.
C
C
C
The
first
is
well,
the
main
requirement
is
that
one
side
of
the
join
has
to
fit
completely
into
memory,
and
that's
not
memory
on
the
size
of
the
data
on
disk.
That's
the
size
of
the
data
deserialized
put
into
memory,
there's
a
rule
of
thumb.
The
defaults
are
10
megabytes
on
disk,
which
is
really
small.
I'll
show
you
how
to
up
that
in
a
second,
the
biggest
you
can
do
is
two
gigs.
C
C
The
next
join
is
the
shuffle
hash
join.
The
shuffle
hash
join
is
when
you
do
a
full
shuffle
of
both
sets
of
the
date
or
both
sides
of
the
data.
Excuse
me
and
it's
not
a
broadcast
join,
but
it
is
a
little
bit
faster
than
the
sort
merge
node.
So
what
a
shuffle
hash
join
requires
is
that
any
given
partition
of
the
right
side
needs
to
fit
in
memory,
so
that
doesn't
mean
the
entire
table
needs
to
fit
in
memory.
It
just
means
any
partition
at
any
time
can
fit
in
memory.
C
Then
it
will
invoke
this,
but
we
can
also
give
it
a
hint
and
I'll
show
you
an
example
of
this
in
a
second
but
the
biggest
difference
between
the
shuffle
hash
join
and
the
sort
merge
join
is
the
shuffle
hash
join.
Does
a
basic
hash
partition.
It
then
creates
a
hash
map
for
the
right
table.
So
when
you
go
ahead
and
you
join
the
two
tables
together,
it's
basically
just
to
look
up
in
the
hashmap
to
go
ahead
and
join
the
table,
so
it
is
generally
faster
than
the
sort
merge
join.
C
The
sort
merge,
joint,
always
works.
So
if
reliability,
robustness
is
super
important
and
performance,
not
so
much
sort
merge
time,
the
sort
merge
doing
basically
sorts
both
sides
of
the
both
tables
that
are
being
joined.
It
then
goes
ahead
and
does
a
merge
and
that's
how
it
does.
The
join
the
most
expensive
part
of
the
sort
merge
join
is
the
sort
if
you're
working
with
really
large
amounts
of
data.
C
What
ends
up
happening
is
you
start
to
spill
to
disks
during
the
sort
and
that
can
really
really
increase
the
run
time
of
that
jump,
so
the
sort
merge
join
can
handle
anything
can
handle
any
data
sizes,
it
always
works,
but
when
you
have,
the
ability
to
you
should
just
use
a
shuffle
hash.
Join
a
cartesian
join
is
a
cartesian
joint
everywhere,
it's
slow
everywhere.
It
requires
you
to
basically
compare
every
data
point
to
every
data
point.
Yeah,
just
try
not
to
use
it,
not
a
good
idea.
A
Hey
before
before,
you
switch
the
slides
sorry
about
that
joe
there
was
a
good
question
from
q,
a
I
know
fish.
You
had
already
answered
it,
but
actually,
I
think
if
it's
useful
for
everybody,
so
fish,
you
might
want
to
answer
this
question.
The
question
is:
is
eight
gigabytes
still
the
maximum
data
size
to
broadcast
within
spark
and
fish?
Why
don't
you
chime
in.
B
Yeah
the
response
I
gave
so
right
now
the
default
for
broadcast
joins,
I
think,
is
still
pretty
low.
It's
only
10
megabytes,
I
believe,
generally.
We
recommend
that
it's
safe
to
increase
that
threshold
to
100
megabytes
or
one
gigabyte
generally
but
past
that
you
may
want
to
benchmark
it
in
some
way,
because
the
network
traffic
will
start
to
get
higher
and
higher
and
become
more
of
a
bottleneck.
C
C
C
It
can
be
broadcasted,
so
this
will
increase
the
performance
of
your
small
table
to
bigtable
join
without
having
to
go
ahead
and
put
a
hint
in
this
one's
pretty
safe
to
do,
as
chris
mentioned,
when
you
get
to
the
two
four
or
eight
gigs,
it's
not
always
better
to
do
it
that
way,
and
I
believe
spark
will
throw
an
exception
if
you
try
to
broadcast
more
than
eight
gigs
in
memory,
eight
or
two,
I
forget
the
exact
number
so
that
one's
a
good
one.
To
always
set
on
all
of
your
clusters,
the.
D
C
One
is
spark:
sql
join,
prefer
sort,
merge
join
false,
so
in
spark
1.6
the
default
join
was
the
shuffle
hash
join
in
spark
2.
It
switched
to
the
sort
merge
join
in
2
1.
I
think
it
switched
back
to
the
shuffle
hash
and
then
2
three.
It
went
back
to
the
sort
merger
or
something
like
that
in
general,
the
sort
merge
joint
is
faster
than
the
shuffle
hash
join
and
the
spark
catalyst.
Optimizer
is
smart
enough
to
basically
figure
out
based
on
some
projections
and
some
multipliers.
C
How
big
each
table
is
going
to
be
when
it
does
the
join.
So
if
the
catalyst
optimizer
thinks
that
at
runtime
it
will
be
small
enough
by
setting
prefer
sort,
merge
joint
to
false
it'll,
actually
do
the
sort
merge
join
and
again
what
that
does.
Is
it
gets
rid
of
all
the
spill
to
this
during
the
sort
which
is
naturally
a
very
skewed
and
compute
heavy
and
slow
operation?
C
That's
when
I
actually
will
fall
back
to
the
previous
slide
and
start
adding
hints.
But
generally
you
do
know
your
data
best.
So
there's
some
cases
where
a
sort
merge
join
is
better
than
a
shuffle
hash
join.
A
good
example
is
when
you're
joining
tables
on
a
partition
column.
So
let's
say
you
partition
by
some
sort
of
shard
id
well,
the
default
shuffle
hash
join
requires
a
full
shuffle.
The
sort
merge
joins
actually
localized
to
the
partition,
so
the
sort's
actually
not
that
bad.
C
So
adaptive,
query
execution
by
far
the
coolest
thing
that
spark's
done
in
a
long
time.
So
what
it
does
is
during
runtime
it'll,
actually
change
the
query
plan,
and
it
does
that
because
we
have
stats
on
all
the
different
partitions
that
are
created
during
shuffles.
So
we
take
that
information
and
we
can
intelligently
do
different
things.
C
So
the
first
one
is,
we
can
adaptively
change
a
sort,
merge
join
to
a
broadcast
hash
join,
we
can
coalesce
the
shuffle
partitions
and
we
can
basically
split
out
skewed
partitions
automatically
for
you.
I
have
pictures
that
show
how
all
of
these
work,
but
it's
a
super
powerful
feature,
and
I
have
a
few
customers.
D
C
C
We
have
two
data
sets
the
left
child
the
right
child
it'll
go
ahead
and
do
a
shuffle
right,
which
is
basically
shuffling
the
data
into
arranged
partitions
it'll,
go
ahead
and
read
those
data
on
the
remote
executors,
it'll,
then
sort
them,
and
then
it
will
do
these.
The
merge
and
the
sort
merge
join.
C
But
let's
say
on
the
right
side:
we
have
a
very
small
table
that
doesn't
fit
in
our
broadcast
join
threshold,
so
the
static
size
is
15
megabytes
the
default
size
for
the
broadcast
join
is
10,
megabytes,
so
spark
would
say:
hey.
I
can't
broadcast
that
so
with
adaptive
query
execution,
what
happens
is
during
the
shuffle?
We
actually
just
track
the
stats
of
each
size
of
the
shuffle
partitions
and
given
the
size
of
that,
we
can
dynamically
re-plan
the
query
so
that
we
use
the
broadcast
journal.
C
C
That
and
the
nice
part
is
for
really
large
tables
that
don't
get
filtered
or
reduced
much.
It
works
pretty
well,
but
let's
say
you
have
a
query
where
you're
doing
some
sort
of
aggregation
and
then
a
join
and
then
an
aggregation
and
the
number
of
partitions
you
chose
for
the
first
join
was
optimal
but
for
the
second
aggregate
was
sub-optimal.
C
So
what
the
adaptive
query
execution
is
going
to
do
is,
after
the
shuffle
right,
it's
going
to
go
ahead
and
use
those
stats
and
basically
coalesce
those
small
partitions
into
fewer
big
partitions.
So
in
this
example,
you
can
see
on
the
left.
We
chose
the
shuffle
partitions
as
5000
arbitrarily,
and
we
didn't
really
need
that
many.
C
C
You
set
spark
sql
shuffle
partitions
once
the
entire
job
uses
that
and
then
you
don't
really
have
any
flexibility
there
with
adaptive
query
execution,
it
will
go
ahead
and
coalesce
those
for
you
and
then
again
with
the
databricks
runtime
we're
going
to
be
releasing
something
to
actually
increase
that
number.
If
it's
sub-optimal
as
well
so
right
now
the
spark
3.0
just
coalesces
them,
but
databricks
will
be
releasing
something
to
increase
the
number
as
well.
A
All
right,
perfect,
actually
hey
joe
there's,
a
good
question
that
popped
up
in
this
case
from
youtube,
and
it
was
based
on
what
you
had
been
talking
about
in
terms
of
aqe
and
noted
the
fact
that
does
this
mean
we
should
refrain
from
using
actually
using
broadcast
since
ourselves.
So
my
response
online
was
basically
saying
it's
not
that
we're
trying
to
tell
you
to
refrain
it.
It's
just
more
a
matter
of
like
why?
A
C
So
if
you
give
the
broadcast
hint
we'll
skip
the
shuffle
on
the
left,
child
and
the
right
child
and
it'll
be
more
performant.
So
again
you
know
your
data,
the
best.
If
you
know
you
can
broadcast
it,
it's
always
best
to
put
that
hint,
but
in
the
event
that
you
forgot,
aqe
is
still
going
to
try
and
help
you
out.
B
B
Yeah,
just
increasing
the
threshold
and
turning
on
aqe
is
pretty
nice
and
spark
will
try
to
adaptively,
adjust
the
plan
and
then
to
joe's
point.
One
thing
you
can
do
is
you
can
you
know,
look
at
the
plans
and
see
if
adaptive
query
execution
has
kicked
in
and
then
maybe
that
tells
you
what
hints
you
should
be
using
the
next
time
you
get
on
the
quitter.
C
It
makes
dealing
with
skew
almost
trivial
in
spark,
so
a
skew
joint
or
a
skewed,
joint
or
skewed
partitions
is
when
you're
joining
two
data
sets
and
a
subset
of
the
keys
make
up
a
significant
portion
of
your
data
and
what
ultimately
ends
up
happening
is
in
this
example.
The
l1
and
r1
are
pretty
small
partitions
and
they
go
ahead
and
they
execute
really
quick
and
then
l2,
r2,
l3
and
r3
are
larger
partitions,
and
we
need
to
update
that
graphic
on
the
right
there
they're
larger
partitions
and
they
end
up
taking
significantly
longer.
C
C
If
you
didn't,
sometimes
it
would
regress
performance
with
the
adaptive
query,
execution,
sku
split.
What
it
does
is,
after
the
the
writes
for
the
shuffle
it'll,
compare
l1,
l2
and
l3
sizes
of
each
partition
and
it'll
go
ahead
and
actually
split
those.
So
what
you'll
see
in
the
spark
ui
is
you'll
see
the
25th
percentile
input
size
is
like
10
megabytes,
the
medians
like
30
megabytes,
the
75th
is
like
50
and
the
max
is
like
8
gigs.
C
So
this
will
basically
solve
that
issue.
For
you,
I've
worked
with
a
couple
customers,
I'm
going
to
show
you
an
example
of
it,
but
basically
with
zero
tuning,
except
enabling
the
feature
we
were
able
to
increase
the
runtime
or
decrease
the
runtime
by
threefold
and
again,
what
it's
doing
is.
After
the
shuffle
write,
it's
taking
a
look
at
the
partitions.
It's
written
comparing
the
sizes
and
splitting
out
the
big
ones,
and
this
just
reduces
the
skew
and
your
cluster
can
run
more
optimally.
C
There's
going
to
be
less
straggler
task,
so
your
cpu
usage
will
go
kind
of
through
the
roof
by
car.
By
far
the
coolest
feature.
Sometimes
it'll
kick
in,
you
didn't
even
know
you
had
sku
your
job.
You
know
a
particular
joint
run,
five
or
ten
minutes
faster,
this
one's
the
closest
to
magic.
I
think
there
is
so.
A
C
A
A
We
would
have
to
do
all
sorts
of
magic
to
basically
get
the
databases
to
be
able
to
work
and
then
that
translated
in
one
way
or
another
to
when
we
applied
that
to
spark
and
the
fact
that,
basically
with
aqe
and
spark
3.0,
also
it's
just
able
to
handle
the
skus
for
us.
It's
a
lot
less
work
and
it
runs
significantly
faster
and
it's
just
happening
all
under
the
covers
and
it's
it's
gorgeous
and
it
does
simplify
things
a
lot.
So
I
cannot
overemphasize
this
enough.
A
C
Yeah
definitely
one
quick
call
out,
though
spark
3.
The
open
source
version
only
supports
the
sort.
Merge
join
for
the
split
sku
databrick
supports
the
the
broadcast
or
the
the
shuffle
hash
and
the
sort
merge
join.
So
if
you're
doing
this
with
open
source
spark
3.0-
and
you
followed
my
recommendation
and
prefer
sort
merge
joint
to
false-
you
may
not
see
it
kick
in.
It
only
works
for
the
sort,
merge
joint
on
the
open
source
version
of
spark.
C
C
C
We
were
looking
at
it
for
a
while
and
you
know
able
to
pinpoint
it
pretty
quickly
and
then
we're
figuring
out
how
to
solve
it
again.
This
ran
in
six
hours
and
47
minutes.
The
customer
was
actually
reasonably
happy
because
where
they
were
running
it
before
was
12
hours,
but
as
a
data
engineer,
this
still
kind
of
bugged
me.
So
we
went
ahead
and
turned
on
adaptive.
Query
execution
with
the
broadcast
or
the
shuffle
hash
split,
sku
join
and
it
ran
in
two
hours
and
28
minutes.
C
You
can
see
the
cluster
was
actually
completely
utilized
or
significantly
more
utilized.
For
this
query,
and
literally
all
we
did
was
enable
the
adaptive
query
execution
and
we
set
the
preferred
sort
merge
joint
to
false.
That's
the
only
two
things
we
did
and
three
times
improvement.
Cluster
utilization
was
through
the
roof
and
a
very
happy
company
that
we
were
working
with
all
right
now.
B
So
this
section
is
going
to
go
through
how
to
partition
your
table
a
little
bit
where
the
benefits
of
partition
pruning
and
then
how
we
can
leverage
optimize
the
order
to
improve
different
types
of
operations
as
well.
So
this
here
this
is
just
showing
you.
What
partitioning
actually
looks
like
on
disk
spark
partitioning
is
based
on
hives
partitioning,
which
it
uses
physical
folders,
to
separate
the
data,
and
then
this
has
benefits
where
we
can
isolate
operations
to
a
specific
folder
and
control.
B
If
you
have
low
data
volumes,
maybe
a
month
would
be
better
or
you
can
partition
by
things
that
are
actually
in
your
data.
Things
like
customer
information
or
geographic
location,
that
kind
of
stuff,
and
then
something
that
was
added
new
in
spark.
3
is
dynamic.
Partition
pruning,
which
is
where
spark
will
actually
look
at
both
of
your
all
of
your
tables
that
are
being
joined
together
and
it'll
check.
B
If
you
have
a
filter,
that's
being
applied
to
one
table
that
might
also
be
able
to
be
pushed
down
to
other
tables
that
are
partitioned
on
that
value.
So
you
could
be
joining
in
something
like
a
lookup
table
with
metadata
information
and
where
you're,
applying
a
filter
for
a
specific
partition,
value
and
spark
will
be
able
to
identify
that
and
push
it
over
to
the
other
side
of
the
join
and
do
partition
pruning
on
the
table.
B
You
did
not
specify
the
filter
on
and
that's
super
helpful
where
he'll
be
able
to
to
speed
up
some
joins
without
you
having
to
rewrite
them
yeah.
If
you
want
to
go
to
the
next
one,
joe
so
in
lower
level
than
partition,
pruning
data
breaks,
delta
is
also
able
to
do
what's
called
data
skipping,
and
this
has
other
names.
B
So
what
delta
is
able
to
do
is
delta
is
able
to
pull
these
statistics
up
into
the
delta
log
itself
and
what
delta
will
do
is
delta
will
first
issue
a
metadata
query
to
the
delta
log
and
determine
if
your
filter
can
possibly
avoid
files
inside
the
the
delta
table
based
on
these
min
max
values?
And
so
here
we
see
an
example
of
we
have
a
query
that
has
a
really
simple
filter
on
it
and
it's
filtering
on.
B
You
know
an
integer
column
and
so
then
we're
able
to
take
the
min
max
values
that
are
applied
to
each
file
and
check
whether
or
not
these
files
are
going
to
have
relevant
values.
For
your
query.
So
in
this
example,
we're
able
to
eliminate
that
first
parquet
file,
because
we
can
see
based
on
the
statistics
that
it
won't
match
your
filter.
B
So
then,
to
improve
these
statistics
and
make
them
even
more
effective.
Obviously
the
statistics
are
going
to
work
best
when
your
data
is
clustered
or
sorted
in
some
way,
and
so
that's
where
z
order
comes
into
play.
So
in
data
breaks
you
have
this
compaction
job
called
optimize
and
you
can
run
optimize
against
any
delta
table
you'll
go
in
and
it
will
compact
your
smaller
files
into
larger
files
for
better
read
performance
and
better,
better
metadata
management.
B
B
And
so
just
some
general
tips
and
best
practices
around
z,
order,
z
order
works
really
well
up
to
about
three
or
four
columns,
and
then
the
effectiveness
is
going
to
start
to
drop
off
quite
a
bit.
So
if
you
use
the
order
on
five
columns,
you
kind
of
would
expect
three
to
four
of
them
would
give
you
decent
performance
and
then
probably
that
fourth
and
fifth
column
would
start
to
have
a
lot
less
data
skipping
we're
also
in
currently
we're
beta
testing
a
new
type
of
curve
called
hilbert
curve.
B
The
hilbert
curve
has
some
slightly
cooler
properties
over
z
order,
where
the
hilbert
curve
kind
of
bounds,
how
big
of
a
range
each
file
is
going
to
have
and
make
sure
that
there
are
no
big
jumps
between
files,
and
it
also
is
more
effective
at
clustering,
your
data,
so
the
hilbert
curve
in
our
our
simple
testing
or
in
our
you,
know,
sort
of
synthetic
testing.
We
see
that
the
hilbert
curve
provides
good
clustering
up
to
five
or
six
columns
depending
on
how
cluster.
How
naturally
clustered
your
data
is
data.
B
A
Oh
actually,
before
you
dive
into
that,
we
had
a
couple
questions
that
I
wanted
to
just
chime
in
here.
So
for
some
folks,
the
first
question,
which
is
actually
a
little
bit
more
about
just
data,
skipping
and
z,
ordering
in
the
first
place,
is
that
do
they
have
to
actually
do
anything
special
in
order
for
data
skipping
number
one
and
then
z
order
number
two
for
that
to
kick
in
for
their
environment,
so
fischer
joe,
why
don't
you
go
for
it
or
I
can
take
care
whatever
whatever
makes
sense.
B
It's
a
data
skipping
is
going
to
be
automatically
applied
and
automatically
picked
up
with
whenever
you're
writing
to
delta
from
datafix.
So
databricks
will
automatically
collect
the
statistics
for
you
for
any
delta
table
and
then
automatically
handles
taking
advantage
of
those
statistics.
So
it's
all
hidden
behind
the
scenes
to
the
user,
and
you
just
have
to
write
data
into
your
delta
table
and
then
query
it
and
you
get
the
advantages.
B
B
Most
use
cases
you're
only
actually
going
to
query
and
filter
on
a
few
of
your
columns,
and
some
of
your
columns
may
not
have
useful
statistics
either
if
there's
no
variation
in
the
columns.
If
there
are
a
really
small
number
of
values,
the
statistics
may
not
actually
be
helpful
for
data
skipping.
So
what
I
tend
to
look
at
is
columns
that
you're
going
to
filter
on
a
lot
or
columns
that
you're
going
to
run,
update
or
merge
operations
on.
You
definitely
need
to
collect
statistics
on
and
then
with
delta.
B
You
can
simply
reorder
the
tables
and
ensure
that
the
columns
you
want
are
the
ones
being
that
statistics
are
being
collected
on
for
and
down
here
at
the
bottom.
We
show
you
what
the
config
is
to
alter
the
number
of
columns
that
are
being
that
statistics
are
being
collected.
One
cool
thing
about
delta
is
that
because
it's
managing
the
parquet
files
under
the
hood
for
you,
you
can
reorder
columns
in
a
delta
table
without
actually
physically
rewriting
the
data,
and
because
we
have
columnar
storage
behind
the
scenes.
B
We're
able
to
just
map
the
column
order,
that's
being
presented
at
the
table
level
and
map
that
to
the
correct
column
in
the
parquet
file
itself.
So
you
can,
after
you've
generated
your
tables.
You
can
go
in
and
start
moving
around
columns
and
alter
what
order
they
appear
in,
so
that
you
have
a
better
logical
representation
of
your
table
and
then
we're
able
to
take
advantage
of
these
statistics
and
turn
a
bunch
of
queries
into
metadata.
B
Only
queries-
and
these
will
then
be
super
super
fast
and
starts
to
give
you
an
experience,
a
lot
more
similar
to
a
data
warehouse
where
a
data
warehouse
is
going
to
be
collecting
these
statistics
all
the
time
for
you
and
that's
how
it's
able
to
be
really
fast
for
certain
queries,
so
things
like
selecting
the
maximum
value
or
the
minimum
value
from
a
column.
You
know:
we've
already
collected
all
the
min
max
values
from
every
file.
B
So
then
we
can
issue
a
metadata,
only
query
to
determine
what
the
maximum
value
is
for
a
particular
column
and
then,
as
mentioned
before,
it
allows
us
to
get
really
granular
with
file
skipping
and
and
potentially
load
at
any
given
time.
You
know,
if
you
have
a
selective
enough
filter,
you
could
load
just
one
file
out
of
your
entire
table.
B
The
statistics
that
get
collected
on
them
typically
aren't
super
useful
so
for
strings
there
is
truncation
being
applied,
and
so,
if
your
strings
don't
vary
in
the
first
few
characters,
then
you
probably
are
not
going
to
get
good
statistics
that
are
useful
for
those
string
types
and
then
for
time
stamps.
There's
a
precision
element
where
time
stamps
require
you
know
nanosecond
or
millisecond
precision,
and
we
don't
collect
statistics
down
to
that
level
at
the
moment.
So
typically
for
timestamp.
B
A
B
Yeah,
so
last
thing
is:
you
can
completely
disable
stats
collection,
there's
a
couple
instances
where
you
would
want
to
do
that.
Stats
are
not
useful
if
you
consume
the
table
via
streaming
right
streaming
is
going
to
consume
every
file
in
the
table,
so
you
don't
really
need
stats
collected
if
you're
only
reading
it
via
streams.
B
However,
you
may
want
to
go
back
and
collect
stats
later
on.
If
you
have
interactive
query
use
cases,
so
you
can
always
go
back
and
turn
the
stats
collection
back
on
and
we
have
actually
there's
a
hidden
api
to
force
stats
collection
or
you
can
run
something
like
z-order
and
it'll
collect
the
stats.
B
So
you
do
want
to
make
sure
that
whatever
your
merge
into
clause
is
using
for
the
actual
update
clause,
the
on
portion
of
it,
you
want
that
to
be
selective
enough,
that
you
are
getting
matches
out
of
your
merge.
Otherwise,
even
though
you
think
this
merge
may
be
a
no-op,
we
still
may
rewrite
some
data,
so
that's
definitely
something
to
watch
out
for.
B
And
then
explicitly
filtering
on
your
partitions
is
going
to
help
a
lot
with
merge.
This
is
something
where
we're
working
on
making
this
dynamic
so
that
it
automatically
picks
up
what
partitions
you're
targeting
with
your
merge,
but
at
the
moment
it's
extremely
beneficial.
If
you
can
pull
out
the
explicit
partition
values
and
pass
them
in
as
a
explicit
list
to
your
merge
operation
and
then
it'll
apply
the
partition
printing
correctly.
B
The
last
thing
is
mentioned
before
you
can
alter
the
file
size
for
your
tables
and
we've
definitely
seen
plenty
of
cases
where
reducing
the
file
size
can
improve
the
data
skipping
a
lot
and,
in
turn,
can
improve
the
merge
operations
quite
a
bit.
So
I
think
we
had
one
customer
where
we
reduced
the
file
size
from
one
gigabyte
down
to
64
megabytes
and
we
were
able
to
reduce
the
amount
of
data
rewritten
by
merge
by
over
50
percent.
B
And
this
is
kind
of
just
a
general
tip.
I
personally
recommend
optimized
rights
all
the
time.
This
is
going
to
enable
your
rights
to
write
out
larger,
better
sized
files,
it
targets
128
megabytes
as
the
default,
and
this
is
going
to
improve
sort
of
your
stability
on
the
read
side
and
give
you
better
read
performance
after
writing
your
data,
but
it's
also
going
to
help
you
avoid
any
throttling
from
the
storage
layer
and
get
around
overloading
the
storage
layer
with
too
many
put
requests.
C
All
right
and
then
we
just
have
a
couple
more
slides,
one
of
the
most
common
things
I
see.
Customers
do
is
coming
from
a
different
system.
They
always
just
pick
the
cheapest
or
the
biggest
instance
type,
because
they
think
that's
the
best.
So
here's
trying
to
give
you
some
prescriptive
guidance
on
how
to
pick
your
instance
type
using
databricks.
C
C
C
So
for
this
particular
query,
we
ran
it
with
different
databricks,
runtimes
versions.
We
ran
it
with
data
in
the
delta
cache
and
we
ran
it
on
the
different
instance
types
and
what
you'll
see
is
contrary
to
popular
belief.
The
smaller
machine
actually
performed
much
better.
So
this
is
mostly
due
to
some
of
the
jvm
overhead
and
garbage
collection,
but
you
can
see
with
the
sixth
node.
C
Oh
this
should
say,
or
this
excuse
me,
16
core
32
gig
ram
vm.
It
took
us
about
a
minute
1.8
minutes
to
go
ahead
and
read
that
entire
data
setting
with
the
same
number
of
cores
but
and
machines,
but
four
times
the
ram
it
actually
took
a
little
longer,
and
then
obviously
you
can
see
here
when
the
data
is
already
in
the
delta
cache
on
the
nvme
ssd.
C
The
performance
is
a
little
better
on
the
second
read
just
for
the
sake
of
comparison,
we
did
work
with
a
older
version
of
the
runtime,
and
you
can
see
that
the
older
version
was
a
little
bit
slower
kind
of
reinforces
the
fact
you
should
try
and
always
use
the
latest
version
of
the
data.
Brick
stormtime
and
the
most
interesting
thing
here
is:
we
went
from
three
six
machines
to
three
machines,
but
we
just
increased
the
size
of
the
machines.
C
Performance
actually
started
to
deteriorate
pretty
quickly,
so
this
is
kind
of
just
a
little
bit
of
evidence
for
the
next
slide
here.
But
basically,
some
rules
of
thumb
you
can
live
by
use,
32
to
128,
gigabyte
machines.
Cores
are
cheap.
You
and
your
employees.
Time
aren't
as
long
as
you
have
less
than
800
cores,
you're,
probably
not
going
to
run
into
any
cloud
limitations
for
the
most
part.
So
a
bigger
cluster
usually
scales
pretty
linear
in
terms
of
performance.
C
C
Some
processors
are
better
than
others
and
definitely
take
that
into
consideration
when
you
choose
your
machine
type
for
particular
workloads,
etl's
generally
bottlenecked
by
io,
I
mentioned
earlier
the
more
cores
you
have,
the
quicker
you
can
do
your
I
o
so
use
compute,
optimize
instances
for
interactive
queries
and
building
machine
learning
models,
any
instance
with
an
nvme
ssd
that
can
power
or
leverage
the
delta
cache
is
going
to
be
more
optimal
for
you.
So
just
some
quick
rules
of
thumb
there
and
I
believe,
that's
my
last
slide.
D
Thanks
chris
thanks
joe
great
presentation
and
thanks
everyone
for
joining
us
on
all
of
your
questions,
we
did
record
this
session
and
it
will
be
it's
available
now
in
youtube,
and
I
posted
the
link
in
the
linkedin
chat
and
also
in
the
zoom
chat
and
we'll
also
post
a
link
there
to
the
slides
as
well.
So
you
can
review
them
after
the
session.