►
From YouTube: Tech Talk Series Part Two: Boost Delta Lake Performance with Data Skipping and Z-Order
Description
Join us for the second in a four part series with Salesforce Engineering.
Abstract:
When building a data lake, partitioning strategy is one of the most critical decisions to make. Less optimized data partitioning strategy can generate small files and undermine read and write performance. Besides traditional file based partitioning with partition pruning, Databricks provides another option of Data Skipping and Z-Ordering (https://docs.databricks.com/delta/optimizations/file-mgmt.html) with I/O pruning and file Compaction. In this talk, we will share the evolving thinking of our partitioning strategy when building Engagement delta lake. Using this real world use case, We will elaborate why and how we leverage Data Skipping and Z-Ordering to Boost Delta Lake Performance.
Part 1: Engagement Activity Delta Lake - https://youtu.be/a7_I1Qi1LoU
-----------------
Speakers
-----------------
Zhidong Ke, Software Engineer PMTS, Salesforce
Zhidong is passionate in designing distributed systems, real-time/batch data processing and building applications.
Yifeng Liu, Software Engineer LMTS, Salesforce
Yifeng is a software engineer who has extensive experience in big data processing and distributed system, and interested in high volume, high complexity, low latency data pipeline and framework building.
Aaron Zhang, Software Engineering PMTS, Salesforce
Aaron is an experienced software engineering leader with interests and areas of focus in engineering secure, fault-tolerant, high volume systems built on micro services.
Heng Zhang, Software Engineering PMTS, Salesforce
Heng is a software engineer who is interested and specialized in micro services, distributed systems and big data. 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
Thanks
karen
so
good
morning,
good
afternoon,
good
evening,
appreciate
you
all
taking
the
time
to
to
join
us
today.
My
name
is
craig:
I'm
the
solutions,
architect
of
databricks
and
I'll,
be
you
know,
along
with
chris
who's,
another
solutions,
architect
and
denny
who's.
One
of
our
developer
advocates
we'll
be
co-moderating
the
panel
today,
but
I
didn't
want
to
take
some
time
to
let
the
salesforce
team
introduce
themselves
hang.
Do
you
want
to
go
first.
A
C
D
A
Awesome,
thank
you
so
before
we
pass
it
off
to
hang
to
present,
want
to
remind
you
that
you
know
if
you
have
any
questions
or
anything
at
all,
please
post
wherever
you're,
watching
either
on
linkedin
or
youtube
or
in
soon
so
yeah
feel
free
to
ask
away
and
we'll
be
sure
to
answer
your
questions.
Okay,
take
it
away.
B
Okay
cool,
so
today's
topic
is
boost
daily
performance
with
data
skipping
and
the
order.
So
in
our
previous
talk,
we
introduced
who
we
are
and
what
we
do
so
at
salesforce.
We
build
and
maintain
a
platform
to
capture
custom
activities
like
email
meeting
and
videos,
so
those
events
are
either
consumed
by
downstream
products
in
real
time
or
stored
in
our
data
lake,
and
this
data
lake
supports
the
creation
of
up-to-date
dashboards
by
downspin
product,
like
einstein
analytics
or
the
training
of
the
machine
learning
models
for
our
customers,
who
are
using
sales
cloud
einstein.
B
In
the
previous
talk,
we,
the
maining,
elaborated
a
major
design
principles
of
our
pipelines,
so
the
incremental
read
tracks
the
changed.
Data
partitions
for
downstream
for
efficient,
read
and
the
exact
ones
are
right
across
tables
ensures
the
atomic
schematics
where
we
write
to
multiple
delta
tables,
and
we
also
use
the
graph
api
to
detect
the
cascading
mutation
use
cases.
B
E
B
F
B
Okay,
cool,
then,
okay,
so
let
me
quick
quickly,
restart
yeah.
The
today's
topic
is
the
booster
delta
lag
performance
with
delta,
skipping
data
skipping
and
the
order.
So
in
our
previous
talk,
we
introduced
who
we
are
and
what
we
do
and
we
also
elaborated
the
design
principles
of
our
pipeline.
The
increment
incremental,
read
exact
ones
and
detect
cascading
mutation
with
graph,
and
today's
we
are
focused
on
the
performance
of
our
pipeline.
B
So
we
first
will
revisit
our
problem
and
the
pipeline
design
in
more
details,
and
after
that,
we
will
focus
on
the
partition
protein
and
will
pruning
and
share
the
story.
Why
we
choose
io
pooling
over
the
partition
pruning
and
do
a
side
side
comparation
of
t
of
these
two
mechanisms
and
last
we
will
share
our
performance
benchmarking
and
the
final
results.
B
So
in
this
project
we're
trying
to
build
a
data
lake
of
engagement
activity,
the
engagement
activity
is
like
a
customer,
viewing
a
presentation
or
webinar
or
click
a
emails,
and
they
are
captured
by
our
high
velocity
sales
product
and
the
engagement
activities
are
mutable.
So,
for
example,
a
lead
l
could
become
a
contact
c
with
a
new
id.
So
in
that
case,
all
the
engagements
that
belong
to
l
will
have
a
new
engagement
id,
so
it
is
called
engagement,
id
mutations
and
other
type
of
mutate
types
could
be.
B
A
merge
could
be
a
delete,
so
our
data
lake
of
engagements
has
to
handle
the
id
mutations,
and
this
is
our
pipeline
in
the
middle.
Is
our
delta
table
engagement
data
table
on
the
right
is
the
ingestion
job
which
takes
the
new
engagement
from
the
kafka
strings
and
appended
to
our
data
table
and
on
the
right
is
the
engagement
id
mutation
job,
which
takes
the
mutation
request
from
the
kafka
screen
and
apply
the
mutation
to
the
data
table
in
the
middle
up?
B
Is
our
data
retention
table
jobs
which
takes
the
data
policy
request
from
the
kafka
stream
and
apply
the
ttl
and
gdpr
requests
to
our
data
table?
So
today
we
are
going
to
focus
on
the
performance
of
id
mutations
job,
and
this
is
our
table
schema.
The
engagement
data
table
high
level
has
three
columns.
The
org
id
and
the
engagement
date
are
the
partition,
columns
and
the
engagement
date.
It
derives
from
the
engagement
time
stamp,
which
indicates
when
engagement
activity
happens,
and
we
run
it
up
into
a
date.
B
An
engagement
is
a
structure
with
the
detailed
field
describing
the
engagements.
One
of
them
is
the
id
which
unique
identified
engagement
activities,
so
the
mutation
request
table
has
the
partition,
columns,
org
id
and
engagement
date
and
also
has
a
mutation
operation
which
could
be
a
merge,
convert
or
delete,
and
the
key
is
the
engagement
id
which
links
to
the
specific
engagement
to
be
updated,
and
the
insert
record
is
the
new
engagement
structure.
B
Our
performance
goal
is
to
handle
one
10
million
mutation,
request
engagement
table
within
an
hour,
so
in
our
first
iterations
we
use
the
common
partition
strategies
rely,
which
relies
on
file
based
petitioning
by
having
one
subdirectory
for
every
distinct
value
of
the
partition
column.
In
our
case,
in
s3,
the
key
would
be
engagement
would
be
org
id
and
engagement
date.
B
B
B
B
B
So
before
we
continue,
I
want
to
introduce
the
delta
log
objects,
which
provided
by
databricks
runtime,
and
this
object
contains
up-to-date
metadata
of
our
delta
table.
One
of
the
important
is
the
snapshots
that
all
files
data
frame-
it
has
all
the
information
metadata
information
of
the
physical
files
of
our
data
table.
Here
I
printed
out
the
schema
of
these
data
frames.
Most
of
the
fields
are
straightforward.
Like
a
path
in
case,
the
path
of
the
file,
the
partition
values
indicates
the
values
of
the
petition,
columns
and
the
size
modification
time.
Data
change
the
stats
field.
B
So
with
delta
log,
we
randomly
pick
a
partition
and
check
the
number
of
the
files
in
that
partitions.
So
for
this
one
it
has
175
files
and
we
also
take
a
look
at
each
of
the
files
and
you
can
see
that
the
size
of
those
files
in
that
position
is
around
7
kb,
which
is
very
small,
and
we
checked
the
total
number
of
the
files
of
our
delta
table,
which
has
4
million
files
in
total.
B
So
based
on
the
findings,
we
find
out
that
one
partition
could
have
up
to
200
files,
and
this
is
decided
by
this
configuration
spark
sql,
shuffle
partitions
and
default
is
200,
but
ideally
we
want
one
partition.
One
file
and
during
the
write
back,
the
delta
sends
to
change
the
file
metadata
back
to
the
driver
so
because
we
have
two
minor
files,
the
total
file
metadata
size
exists,
thus
the
thresholds
which
controlled
by
the
maximum
results
for
size.
B
So
after
knowing
the
result
reasons,
we
take
a
couple
of
actions.
One
action
is
to
fix
our
delta
table
first
and
by
rounding
the
optimize
command,
which
compacts
all
the
small
files
within
the
same
partition
into
one
file.
After
we
run
this
optimize
command,
the
number
of
the
file
has
dramatically
reduced
to
135
thousands
and
the
second
action.
We
turn
on
the
two
options
into
our
id
mutation,
jobs
and
ingestion
jobs.
B
So
to
solve
our
puzzles,
we
try
to
gain
some
insights
from
our
engagement,
perf
traffic
models
based
on
the
model.
We
know
that
the
number
of
engagements
per
arc
per
day
ranges
from
about
two
thousand,
eight
hundreds
of
small
s,
of
small
sized
ark
to
about
two
million
for
the
extra
large
sized
orc,
and
we
also
know
on
average.
What
is
the
engagement
record
size
which
is
150
bytes?
B
We
will.
We
will
show
how
we
comma
come
up
with
this
number
and
with
this
two
info
piece
of
information,
we
can
calculate
the
total
size
of
the
engagements
per
day.
Per
org
could
be
range
from
400
422
kilobytes
to
295
megabytes,
and
the
traffic
model
also
indicates
that
ninety
percent
of
the
orgs
are
small
ones,
so,
which
means
ninety
percent
of
the
petitions
will
be
less
than
five
hundred
gigabytes.
B
So
before
we
continue,
let's,
let's
talk
about
what
is
data
scheming
and
the
order,
so
the
delta
leg
automatically
maintains
a
minimal
and
maximal
value
for
up
to
32,
fills
in
the
order
table
and
the
store
store
them
as
part
of
the
metadata,
which
is
in
the
stats
field.
In
that
all
data
data
frame
and
by
leveraging
those
minimum
and
maximum
values
data
lake
is
able
to
skip
the
files
that
are
out
of
the
range
of
the
querying
field
values.
B
So
to
turn
on
the
z
ordering,
we
run
optimize
command
again
on
the
data
table,
engagement
data
and
specify
the
z
order
by
engagement
date.
The
columns
so
changing
the
petition
scheme
is
quite
fundamental
and
we
have
to
reach
the
consensus
of
the
whole
teams.
So
we,
in
order
to
prove
that
we
are
in
the
right
direction.
We
set
up
a
new
pipeline
with
the
new
delta
table
which
shown
in
below.
B
So
the
the
bars
in
the
light
green
shows
the
time
taken
by
the
old
id
mutation
table
on
old
id
mutation,
jobs
on
old
tables
and
the
bar
in
blue
is
the
time
taken
by
the
new
job
on
the
new
mutate
engagement
tables.
So
you
can
see
that
the
job
running
on
a
new
table
is
is,
is
much
faster
and
on
the
second
half
of
our
testings,
the
old
job
cannot
just
survive.
B
B
B
So
in
a
tag
in
indicates
that
this
table
is
the
ordered
by
engagement
date
and
we
single
out
the
minimum
and
maximum
value
of
engagement
date,
which
is
the
order
column.
So
you
can
see
that
the
range
of
the
minimum
and
maximum
value
is
pretty
aligned.
For
example,
the
first
file,
the
engagement
record,
has
is
start
from
2019
august
16
to
2019
august
27th,
and
the
file
below
starts
with
august
27th
to
september
9
7..
So
this
is
a
pretty
aligned
without
any
overlap
and
in
the
stats
columns.
B
So
this
is
how
we
come
up
with
the
average
size
of
the
per
engagement
record
by
having
the
size
of
this
file
are
divided
by
the
number
of
records,
and
you
can
also
see
there
are
minimal
values
and
maximum
values
for
many
columns
now,
we'll
prove
that
our
new
partition
scheme,
with
ir
proving
is,
can
solve
our
performance
problems
and
now
it's
time
to
do
the
change.
So
in
order
to
switch
from
the
old
partition
scheme
to
a
new
new
partition
scheme.
B
First
in
the
ingestion
job,
we
remove
the
engagement
date
column
as
a
partition
column
and
second,
we
run
the
optimize
command
on
our
delta
table
and
there's
no
change
to
all
downstreams,
because
at
the
application
level
we
still
operate
on
a
combination
of
org
id
and
engagement
date.
So
we
call
it
virtual
petitions
now
it's
time
for
us
to
do
a
side
side
comparison
comparison
of
this
two
partition
scheme
and
the
two
type
of
pruning
mechanisms
so
on
the
left
is
petitioning.
B
Is
partition
pruning
so
in
this
in
this
mechanism,
the
files
are
written
into
a
partition
directory
of
org
and
date.
So
the
issue
we
have
is
the
data
file
size
is
distribute,
distribution
could
be
screwed,
so
once
the
partition
columns
are
decided,
it
is
impossible
for
us
to
change
the
file
size
or
the
partition.
Granularities
and
the
data
file
distribution
solely
relies
on
the
traffic
model
on
the
right
is
the
I
o
pruning.
B
So
our
next
question
is
what
will
be
the
best
value
for
this
configurations?
The
default
is
one
gigabytes.
So
in
order
to
answer
our
questions,
we
conduct
a
performance
benchmarking
on
our
id
mutation
job.
So
first
we
create
a
baseline
data
sets.
So
the
data
set
contains
the
408
4830
args
and
it
has
100
1.5
convenient
engagements
and
we
generate
five
five
million
mutation
requests
which
can
trigger
1.3
billion
engagement,
be
mutated,
so
the
mutation.
G
B
Here
I
print
out
the
our
bucket
query
for
each
of
this
database
with
different
configurations,
so,
for
example,
in
the
database,
with
the
configuration
of
four
megabytes
you
can
see
most
of
the
files
size
is
between
four
megabytes
and
to
eight
megabytes
and
in
the
eight
megabytes
status
database
you
can
see.
Most
of
the
file
is
aligned
with
eight
megabytes
and
16
max
buys
and
so
on.
B
And
this
is
our
final
results.
After
all
these
changes,
so
we
are
able
to
handle
the
25
28
million,
update
and
delete
requests
within
eight
minutes
with
the
cluster
of
32
nodes
with
the
type
of
i3
x
to
8x
large.
This
is
far
more
beyond
our
original
performance
goals,
which
is
only
10
million
per
hour.
B
So
for
the
gdprs,
the
query
patterns
is
simple:
we
first
identify
the
org
id
and
which
is
the
partitions,
and
then
we
are
delete
our
data
based
on
which
is
older
and
then
a
date
which
is
upper
bound
date
and
the
design
of
the
benchmarking
for
our
data
retention
job
is
we
reuse.
The
same
data
set
data
sets
net
for
id
mutations
and
we
control
the
upper
bound
date
field
to
manage
the
deleted
proportions.
B
B
So
the
way
we
control
it
is
that
if
we
want
to
delete
that
five
percent
of
the
files
and
our
baseline
data
is
engagement,
date
is
range
in
265
days,
then
we
just
delete
the
first
18
days
of
the
status
and
for
each
of
the
value
proportions.
Digit
proportions
we
run
three
times
and
pick
the
average
value
of
the
three
rounds,
so
the
overall,
the
performance
of
deletions,
is
good
to
us.
B
So
in
order
to
understand
this
behavior
first,
we
need
to
understand
the
way
that
the
delta
do
a
update.
Alright,
so,
for
example,
for
this
file
one,
and
if
we
want,
if
delta,
wants
to
apply
the
changes
to
this
file,
it
first
grab
create
a
snapshot
of
the
latest
version
of
the
file,
do
a
copy
and
then
apply
the
change
to
this
file
and
write
it
back
as
a
new
version.
B
B
So,
with
these
two
in
mind,
let's
look
at
what
happened
in
our
gdpr
testings,
suppose
that
we
are
going
to
delete
a
five
percent
of
our
files,
because
the
portion
is
very
small,
it
could
be
touch.
Many
files
in
this
case
is
file
files
and
it
will
apply
the
five
copy
and
write
backs
operations,
and
if
we
want
to
decide
50
of
the
files,
then
some
of
the
files
could
be
deleted
as
a
whole.
B
B
So
finally,
we
add
this
is
how
we,
the
pipeline,
looks
like
in
terms
of
the
after
performance
work.
So
first
we
turn
on
the
optimized
right
to
each
of
our
jobs
in
in
ingestion,
in
id
mutation
and
data
retention,
and
also
we
write
and
we
add
another
steps
right
after
the
writes
in
each
of
the
job,
to
do
a
optimized
command
to
ensure
that
every
time
after
the
rise,
the
the
files
are
still
aligned.
B
So
we
also
here
list
other
performance
considerations
here
so
first
we
we
noticed
that
the
i3
instances
is
pretty
accelerated
when
we,
when
we
run
our
job
in
a
databricks
runtime.
This
is
because
that
the
databricks
add
the
tunings
subsequent
for
this
type
of
instances
by
leveraging
the
local
ssd
disk
as
a
cache
and
the
second.
We
also
noticed
that
the
s3
buckets
versioning
also
has
an
impact
on
our
file
listings
performance.
B
So
we
add
a
ttl
policy
or
actually
disable
this
extra
brackets
versioning.
If
we,
if
you
don't
need
it,
and
the
configuration
of
spark
sql
shuffle
partitions,
decided
the
parallel
reasons,
why
we,
when
you
do
the
query
when
we
do
the
joins,
so
make
it
the
number
of
the
executor
of
the
course
to
maximize
your
cluster
capacitives.
E
Thank
you
very
much
hank
this
is.
That
was
awesome.
If
we
can
karen,
if
you
can
switch
us
to
gallery
mode
so
that
way
we
can
dive
into
the
questions.
We
have
a
few
questions
that
just
came
through,
but
in
case
you,
oh
and
hang
if
you
can
stop
sharing.
So
at
the
same
time,
so
we
have
a
few
questions
that
came
through
basically
from
the
q,
a
panel
and
also
from
youtube.
So
we're
gonna
go
ahead
and
dive
into
those
questions.
E
Okay,
perfect,
thank
you
very
much,
and
so
the
first
question
I'll
actually
ask
aaron.
If
that's
okay,
there
was
a
question
earlier
within
from
zoom
in
this
case
from
I
believe
from
bob
just
curious.
What
do
your
ids?
Why
do
your
ids
have
to
mutate?
Why
not
just
use
a
mapping
table
instead,
while
you've
already
answered
it
on
the
q,
a
I
figured.
It
might
be
helpful
if
you
let
everybody
else
know
as
well.
C
Yeah,
so
just
to
clarify
the
ids
that
mutate
we're
not
talking
about
the
actual
engagement
id.
So
within
an
engagement
record
itself,
we
have
references
to
say
the
contact
for
the
lead.
Those
are
the
ones
that
we
take
so
on
each
record.
We're
mutating
not
like
the
so-called
primary
key
but
values
within
the
record
itself,
yeah.
Hopefully
that
answers
your
question.
E
Yes,
the
regular
problem
of
the
fact
that
you
don't
get
to
control
all
the
entire
data
flow
right.
Yes,
fair
enough,
fair
enough.
Okay,
we've
got
some
other
delta,
more
specific
questions.
I'm
gonna
probably
target
them
for
craig
and
chris,
but
by
the
same
token,
for
the
folks
in
salesforce,
but
you
of
course
I
would
love
to
have
you
chime
in
as
well.
But
let
me
go
and
start
asking
the
question
and
chris
I'm
going
to
for
target
you
for
this
question.
E
F
E
Thanks
very
much
chris
and
by
the
way,
the
chris,
if
I'm
correct,
I
believe,
you're
referring
to
the
dml
internal
session,
perfect.
Okay,
I
will
paste
that
session
for
everybody
there
there's
a
just
provide
context,
there's
a
session
by
myself
and
td,
who
we
we
created,
something
called
diving
into
delta,
like
dml
internals,
delete,
update
and
merge
so
I'll
paste
that
both
in
youtube
and
zoom
chat.
So
you
can
have
reference
to
that.
E
E
A
Column,
that's
a
good
question.
I
think
it's
I
mean
that
this
is
a
very
common
and
I
don't
know
age-old
question
about
partitioning
strategies
for
tables.
So
just
to
start
by
saying,
if
you
have
a
table
of
that
size,
that's
300
megabytes.
You
know
because
spark
defaults
for
reading
data
in
128
megabyte
splits.
A
A
But
if
we're
speaking
generally
about
partitioning,
I
think
the
idea
is
that
you
want
to
partition
on
a
column
or
columns
that
will
allow
you
to
meaningfully
reduce
the
amount
of
data
that
your
execution
has
to
ingest
across
most
queries
of
the
table.
So
again
it's
just
it's
not
really
it's
as
much
art
as
it
is
science.
A
I
think
the
salesforce
team-
and
you
know,
if
you
listen
to
this
talk,
it's
it's
clear
that
you
know
partitioning
has
a
huge
effect
on
the
performance
of
your
jobs,
and
so
you,
you
really
need
to
think
about
who
is
consuming
the
table.
You
have
to
think
about.
You
know
potentially
over
partitioning,
because
if
you
set
too
many
too
many
levels
of
your
hierarchy
like
then,
the
resulting
files
are
gonna,
be
small,
no
matter
what
so
yeah.
A
E
E
Otherwise
you
know
that's
why
that's
why
we
have
jobs,
but
the
context
nevertheless,
is
that
you're
going
to
need
a
test
and
experiment
to
see
how
things
work
for
your
environment,
but
definitely
I
would
start
with
what
craig
said
as
your
as
a
great
starting
point
all
right.
Another
question:
hey
fish
back
to
you
in
the
case
of
etl.
If
I
do
and
optimize
on
the
table
before,
I
start
that
etl
the
table
is
going
to
be
updated
and
then
it's
going
to
get
fragmented.
F
F
So
we
have
a
feature
called
optimized
rights
and
that
will
help
you
help.
The
writers
write
out
a
limited
number
of
larger
files
instead
of
writing
out
a
ton
of
small
files
and
then
we're
also
currently
testing
a
feature
where
delta
will
be
much
smarter
about
how
it
manages
files.
When
you
are
running
a
lot
of
merge
or
update
workloads
against
a
table,
so
we
now
have
a
flag
where
you
can
set.
E
Perfect,
thank
you
very
much.
I
think
that's
it
for
questions
so
karen
before
I
go
ahead
and
flip
back
to
you.
I
just
want
to
say
hang
thank
you
very
much
for
this
awesome
presentation
and
leo
and
excuse
me,
as
I
try
to
not
cough
up
my
lungs
leo
and
aaron.
Thank
you
very
much
for
answering
your
questions
on
the
q,
a
and
again
fish
and
craig.
Thank
you
very
much
for
also
answering
the
questions
on
both
the
q
a
and
live
as
well.
G
Yep
thanks
so
much
jenny
and
thanks
everyone
from
the
salesforce
team
for
joining
us
and
again
craig
and
fish,
always
great
to
have
you
and
thanks
everyone
for
attending.
So
I
dropped
the
link
to
part
three,
the
next
session
in
the
chat,
so
hopefully,
hopefully,
you'll,
join
us
in
two
weeks
for
that
session
and
I'll
also
send
out
the
recording
as
well.
So
you
can
check
it
out
at
a
later
date,
if
you'd
like
to
so
with
that.
I
hope
everyone
has
a
great
rest
of
your
day
and
take
care.