►
Description
In the earlier Delta Lake Internals tech talk series sessions, we described how the Delta Lake transaction log works. In this session, we will dive deeper into how commits, snapshot isolation, and partition and files change when performing deletes, updates, merges, and structured streaming.
In this webinar you will learn about:
- A quick primer on the Delta Lake Transaction Log
- Understand the fundamentals when running DELETE, UPDATE, and MERGE
- Understand the actions performed when performing these tasks
If you want to join the live conversation on zoom, follow the link on our online meetup: https://www.meetup.com/data-ai-online/events/269776125/
- Watch Part 1, Unpacking the Transition Log: https://youtu.be/F91G4RoA8is
- Watch Part 2, Enforcing and Evolving the Schema: https://youtu.be/tjb10n5wVs8 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
This
is
the
thought,
talk
in
the
CSW
delta
lake
series
and
we're
going
to
talk
about
how
delete
update
mods
these
operations
were
conducted,
a
weather
update
little
bit
sneak
peek
into
the
internals
of
how
do
they
work
and
how
you
continue
the
performance
of
this
so
I
myself,
didi.
Thank
you,
Karen
for
the
introductions.
So
a
little
bit
background
about
myself.
I
have
been
working
in
the
Apaches
Fox
ecosystem
since
2011
back
then
I
was
a
grad
student
in
the
amplifier,
where
in
2012
I
see
some
area
who
started
the
spot
project
itself.
A
B
Thanks
very
much
so
I'm,
not
as
smart
as
TD,
but
I'm
a
staff
to
war
advocates
are
working
as
a
work.
I
worked
with
the
Apache
spark
since
0.6,
so
roughly
around
the
same
time
around
2011
as
well.
Former
senior
director
of
data
science
engineering
at
concur,
I'm
also
a
former
Microsoft
II
and
yeah
there
you
go
that
those
I
think
those
are
the
key
components
here.
So
I'm
glad
to
be
here
and
hopefully,
you'll
enjoy
today's
session.
A
Alright,
so
so,
for
those
who
are
completely
new
to
this
series,
this
is
third
talk
of
the
series,
so
just
to
catch
you
bring
it
up
to
speed.
This
is
a
one
slide
intro
on
what
Delta
Lake
is
it's
essentially
open
source
storage
layer
that
it
brings
as
the
transaction
to
spark
workloads?
So
the
format
is
open.
A
It
maintains
it
by
writing
these
JSON
files,
which
has
description
of
all
the
operations
that
happen
all
the
files
that
got
added
and
removed
in
each
of
the
operations,
and
so
each
of
these
JSON
files
is
essentially
like
a
new
version
of
the
table
and
other,
and
this
log
is
maintained
in
the
same
within
the
same
directory
where
all
the
rest
of
the
data
files
are
also
maintained.
So
all
the
information
both
the
data
eliminated,
is
co-located
in
one
location
and
this
log
is
updated
automatically
on
every
operation.
A
Therefore,
you
get
full
as
a
transaction
guarantees
on
any
operation
on
the
table.
That's
like
a
two
minute
version
of
what
Delta
legate's.
So
if
you
want
to
know
a
little
bit
more
detail
of
how
this
transaction
log
works,
etc,
go
take
a
look
at
the
part
one
and
of
the
series
youtube
link
is
here:
it's
also
in
the
YouTube
channel
that
you
hopefully
have
subscribed
to,
and
then,
if
you
want
to
learn
more
about
the
schema
enforcement
which
I
have
written
the
title
wrong
here,
it's
a
schema
information
and
how
schema
read.
Variegation
works.
A
That's
the
part
two
of
the
scenes,
so,
in
this
part,
3
I'm,
going
to
talk
about
DML
operations,
updating
much
how
they
work
under
the
hood
and
how
you
can
clean
the
performance
to
get
the
best
performance
out
of
it.
So
and
I
could
end
it.
By
talking
about
some
common
design
patterns,
we
have
observed
that
people
our
users
of
Delta
Lake,
have
used
and
how
they
can
probably
help
your
use
cases
as
well
reversions.
A
A
So
now,
incidentally,
the
sequel
support
is
currently
only
available
in
database
elderly,
but
we
are
really
trying
hard
to
get
it
out
in
the
open
source,
Delta
Lake,
but
it
will
come
out
as
long
as
soon
as
3.0
comes
out,
which
has
actual
parser
level
supports
for
these
DML
operations
like
update,
delete
much
so
as
soon
as
far
3.0
comes
out.
We
will
have
a
delta
lake
Greenlees
with
actual
sequel
support
for
these.
Until
then,
you
can
always
use
Scala
and
Python
for
doing
these
sort
of
operations
and
and
the
way
it
works
under.
A
The
hood
is
that
Delta
Lake
maintains
these
files,
and
so
it
only
tracks
data
at
the
granularity
of
files.
So
what
let's
say
here
there
are:
let's
say
you
have
the
version
12
of
the
table
where
you
have
four
files.
Now,
let's
say
you
run
update.
What
it
will
do
underneath
is
that
it
will
use
two
scans
on
this
data
to
update
them.
First,
it
will
do
as
first
can
it's
contained
the
data
that
needs
to
be
updated.
Based
on
the
predicate
that
you
provided.
So,
let's
say
out
of
these
four
files.
A
All
the
files
has
data
that
matches
the
predicate
now
in
the
entire
file,
not
all
the
rows
in
the
file
by
the
desert
park.
Offense,
that's
how
Delta
stores
it
stores
the
data
as
40
files.
So
now
or
not
all
the
rows
in
the
park'
files
may
match
the
data,
so
there
will
be
some
rows
that
actually
match
the
predicate
some
rows.
That
does
not
match
the
predicate,
as
you
can
see,
marked
here
as
green
and
red
so
waste
now
to
identify
these
files,
it
uses
the
predicate
and
column,
stats
and
partitions,
etc.
A
Partition
pruning
all
the
things
that
spark
provides.
It
uses
that
widen
to
narrow
down
what
files
it's
needs
to
actually
read
to
actually
find
that,
whether
the
file
country's
matches
or
not
now,
once
it
finds
those
files,
it
selects
them
and
does
another
scan.
Once
again,
we
still
rewrite
those
files,
so
those
two
files
got
rewritten
as
completely
new
files,
because
we
cannot
go
ahead
and
update
files.
A
We're
removed,
marked,
as
you
know,
they
are
not
physically
removed
from
the
directory
mix
so
that
we
can
do
time
travel,
which
I
will
talk
about
later.
We
can
still
query
those
files
as
a
previous
version
of
the
tape,
so
only
this
is
what
happens
so
let
me
actually
send
it
over
to
Jenny
who
can
actually
demonstrate
this
in
a
notebook
and
a
better,
cleaner
idea
of
how
this
works
underneath
or
what
do
you
Danny?
Let
me
suffer
a
mystery.
A
B
A
B
Okay,
now
here's
what
the
data
looks
like
initially,
okay,
so
basically
you've
got
a
state
if
I
was
to
just
explore
the
data.
Here's
what
here's,
what
it
looks
like
and
you
notice
that
Iowa's
null
and
Washington
has
a
value
of
3.2.
Okay,
but
now
I
want
to
run
an
update,
just
as
TD
was
talking
about.
So
let's
go
ahead
and
do
this
in
this
case,
I'm
just
gonna
be
I'm.
Gonna
be
rather
arbitrary
here
and
I'm,
just
gonna
say
hey.
B
Let
me
look
at
the
number
of
loans
that
I
want
to
go
ahead
and
update.
Okay,
in
this
case,
209
I'm
gonna
go
ahead
and,
in
this
case
run
this
update
statement
where
I'm
saying
for
this
for
the
state
of
Washington
updates
the
paid
amount
to
be
equal
to
the
funded
amount.
Okay,
so
in
other
words,
I
want
I
want
those
values
to
be
updated.
B
Okay,
just
as
TD
was
talking
about
all
right,
so
with
Delta
Lake
as
T
need
not
talked
about,
you
can
run
an
update,
but
then
what's
actually
happening
underneath
the
covers
all
right.
Well,
let's
go
look
at
the
jobs,
real,
quick,
alright.
So,
for
example,
we
have
John
1838.
Let's
go
look
at
that
now.
Sorry
all
right.
B
B
Yes,
I
meant
to
do
that
and
forgot
to
do
it
so
there
we
go
okay,
perfect,
so,
as
you
can
tell
I
want
to
zoom
back
in
from
the
statistics,
we
read
one
file,
okay,
it's
written
right
here
actually
in
the
youth
spark
UI.
Okay,
so
we've
read
one
file,
here's
all
the
information
we
did
like
you
know
the
cash
right
size
time
spent
file
system,
all
these
different
things,
but
here's
the
row,
rows,
output,
okay
and
sorry
close
that.
B
So
that's
what
how
it
starts
off
with
and
then
let
me
go
ahead
and
go
back
and
it
was
1839
anything
there
go
so
again.
Here's
the
four
rows
that
are
gonna,
be
instead
of
rows
that
potentially
gonna
be
impacted
and
all
the
exchange
processing
and
all
it's
actually
gonna
go
due
to
the
data.
Okay.
Now,
if
I
was
to
go
instead,
just
simply
look
at
the
history
of
this.
B
The
operation
metrics
are
actually
embedded
right
inside
here.
Okay,
so
the
total
number
of
rows,
14
705
the
update
of
rows.
There
were
340
rows
in
fact
that
were
actually
updated
because
of
we
actually
updated
all
of
Washington
State.
Not
just
not
just
the
ones
that
were
the
values
weren't
separate,
but
we
updated
all
of
them
because
of
the
way
I
wrote
that
particular
statement,
and
so
the
number
of
rows
that
were
copied
was
right
here.
14
365,
plus
3
40
equals
the
14
705
that
you
see
so
underneath
the
covers.
B
That's
what
we're
doing
we're
actually
taking
a
copy
of
all
the
files
into
a
new
set
of
files,
okay
and
we're
going
ahead
and
making
the
modification
as
you
see
here.
So
here's
what
the
result
looks
like
right.
You
know
where
I
just
simply
said:
Washington
State's
null
and
then,
when
I
look
at
it
see
it.
Here's
the
file
right
here
that
you
see
okay.
So
this
is
the
file
that
actually
has
the
new
set
of
data
per
the
updates.
B
A
All
right
so
so
now
that
you've
seen
how
you
can
create
new
versions
by
updating
the
data,
you
can
still
go
back
and
query
the
old
version,
so
spark
Delta
Lake
provides
these
ways
to
query
previous
versions
of
the
table
that
you
saw
in
the
history.
So
here,
if
I
had
created
version
13
from
my
updating
version
12,
you
can
see
query
motion
12
using
this
option
called
version
as
of
12
and
with,
and
so
now
we
can.
You
can
just
compare.
A
So
that
gives
a
lot
of
gives
a
lot
of
power
to
actually
go
back
in
time
and
figure
out
what
what
happened,
whether
all
the
updates
that
happened
were
correct
or
if
you
see
some
sort
of
corrupted
data
can
go
back
and
check
which
portion
if
the
data
get
corrupted
and
maybe
roll
back
using
by
reading
the
version
over
item
table
with
the
correct
older
version
once
again.
So
this
is
a
very
powerful
tool.
So
now,
let's
talk
about:
how
do
you
improve
the
performance
so,
as
Jenny
showed
you
that
there
are?
A
There
are
two
scans
on
the
data
now
somebody
asked
question
regarding
whether
those
are
two
full
scans
or
not.
The
simple
answer
is
that
the
first
one
may
be
a
full
scan.
The
second
one
is
usually
not
so
it
all
depends
on
what
predicate
you
put.
So
if
your
predicate,
for
example,
say
that
update
all
the
rows
in
a
particular
partition,
then
the
first
scan
to
find
all
the
files.
That
means
we
updated
very
only
query
that
partition
so,
and
so
it
doesn't
need
to
full
scan.
A
A
But
after
that,
that's
the
for
the
first
scan,
but
the
second
scan
usually
scans
over
only
the
files
that
needs
to
be
update,
and
so
so
so
to
answer
this
question,
it
doesn't
have
to
be
two
full
scans
or
complete
depends
on
your
table
setup.
But
now
the
key
thing
to
remember
here
is
that
the
more
predicates
you
add
in
the
update,
Clause,
you
know
the
better.
A
It
is
the
easier
it
is
for
Delta
lake
to
narrow
down
the
search
space
and
therefore
the
length
the
faster
that
first
scan
is
going
to
be
so
that
improves
the
total
run
time
of
object.
Now,
the
Delta
X
Delta
Lake
platform
has
a
few
more
performance,
optimizations
that
that's
the
difference
between
OSS
Delta
and
Delta
Lake
is
that
we
have
a
little
bit
few
more
performance
improvements
in
Delta
Lake.
A
There
are
tools
for
doing
better
data
skipping,
especially
this
thing
called
zero
optimize
that
essentially
can
optimize
the
layout
of
the
data
in
the
table
in
a
more
complex
in
a
more
organized
manner.
It's
essentially
like
multi-column,
sorting
but
much
better
than
that
using
space-filling,
curves
and
stuff,
and
so
it
essentially
improves
the
efficacy
of
storing
column
stats
at
the
level
of
files
and
stuff,
and
then
there's
also
support
for
bloom
filters.
A
You
would
not
go
into
detail
of
those,
but
well,
but
the
key
I
did
remember
is
that
if
you
really
understand
what
goes
on
underneath
these
operations,
it
makes
it
easier
for
you
to
reason
about
how
to
optimize
the
performance
of
these
operations
like,
for
example,
narrowing
down
the
search
space
by
adding
more
predicates.
If
you
know
where
your
matches
are
going
to
be
present
so
similar
to
update
there
is
date,
so
similar
syntax
delete
from
table
based
on
predicate
this,
obviously
sequel
that
is
going
to
come
with
participant
3.0
but
there's
until
then.
A
There
is
also
Scala
and
Python
support
directly
in.
But
it's
interesting
remember
that
as
really
showed
that
the
update
does
it
creates
new
files,
but
it
doesn't
replace
the
old
files
it.
It
only
marks
it
as
tombstones
masses
as
deleted
in
the
transaction
log
so
that
it
doesn't
automate
it
so
that
you
can
do
time,
travel
and
query
those
old
data
once
again
you
by
specifying
the
exact
version
number
but
now
for
deletion.
The
setup
is
exactly
the
same.
A
Any
data
that
needs
to
deleted
will
rewrite
new
files,
but
the
old
files
will
be
preserved
by
default
so
that
you
can
go
back
and
query
the
version
of
the
table
before
the
delete.
If
things
get
accidentally
leak,
it
you
can
roll
back
and
stuff,
but
in
some
cases
you
actually
do
want
the
data
to
be
completely
permanently
deleted.
From
this,
you
don't
want
any
file
to
have
the
world
data.
So
for
that
you
have
to
run
this
additional
operation
called
FAQ.
A
That's
an
operation
that
provided
that
is
a
specific
Delta
Lake,
and
what
it
does
is
that
it
you
can
say
how
many
previous
or
how?
What
is
the
length
of
the
previous
versions
that
you
want
to
retain?
So
let's
say
you
want
to
retain
all
the
versions
retained
in
last
one
day,
because
you
know
that
you
don't
want
to
care
about
querying
any
version
of
the
table
that
is
older
than
one.
Then
you
will
run
vacuum
table
retain
one
hour
by
default.
If
you
don't
specify
the
retain,
it
is
seven
days.
A
That's
what
we
have
observed
that
works
well
in
most
situations,
but
what
it
goes
ID
will
do
is
that
it
will
figure
out
what
are
all
the
files
present
in
the
directory
that
are
not
needed
by
any
of
the
versions
present
in
that
were
written
in
the
last
one
day.
So
so
any
file
that
is
not
needed
in
the
versions
written
within
the
last
24
hours.
A
Those
will
get
deleted,
and
that
includes
any
sort
of
partial
files,
corrupted
files,
and
so
that
may
have
been
written
but
not
actually
committed
to
the
log
because
of
failure
rights
itself.
So
vacuum
will
delete
all
of
that
and
using
that
you
can
actually
control
how
much
of
the
previous
versions
you
want
to
retain
and
what
you
can
delete,
and
eventually
you
can
age
data
out
from
all
the
data
files.
A
In
this
way,
the
data
will
actually
get
you
needed,
so
you
can
even
run
vacuum,
retain
zero
hours
that
actually
will
keep
only
the
latest
version
of
the
table.
If
you
really
don't
care
about
history-
and
you
really
want
to
ensure
that
after
deletion,
all
the
data
has
actually
been
physically
gilded,
you
can
run
vacuum
retention,
zero,
which
would
preserve
only
the
last
version.
A
However,
you
think
what
we
remember
that
vacuum:
zero
do
not
run
vacuum
zero
when
other
rights
and
in
progress,
because
you
may
delete
files
that
are
being
correctly
written
right
now,
so
just
be
careful
of
that
anyways.
So
then,
on
to
March,
which
is
probably
the
more
interesting
and
most
powerful
operation
among
these
three.
So
mods
has
a
standard
sequel.
A
Syntax
has
a
standard
sequel
and
seeing
sync
sequel
syntax,
where
the
idea
is
that
you
are
merging
into
a
target
table
using
data
from
source
table
where
B
on,
based
on
a
match
that
if
you
match
the
keys
of
a
source
row
and
a
target
row,
if
it
is
matched,
then
you
can
update
the
petcock
using
this
source
matched
source
data.
If
there
is
no
mass,
that
is,
if
there
is
a
source
row
that
does
not
match
any
target
row,
then
you
can
insert
a
new
target
table
column
based
on
the
source.
A
New
target
t
will
grow
based
on
the
source
flow.
Now
this
is
Santikos
index.
We
support
that
obviously,
but
we
have
additional
support
for
a
lot
more
extended
syntax,
that
that
makes
things
a
little
bit
more
interesting.
For
example,
we
have
support
for
additional
class
conditions,
say
that,
for
example,
you
have
matched
based
on
this
condition,
but
you
want
to
have
additional
condition
on
top
of
those
matches.
Then
here
you
can
specify
additional
Clause
conditions,
not
just
the
match,
condition
but
class
conditions
that
needs
to
be
satisfied
before
the
update
kicks
in
and
the.
A
Similarly,
when
not
matched
and
additional
class
tension,
then
only
instead
would
happen
and
where
this
is
useful
is
that
you
can
actually
have
multiple
at
least
up
to
two
matched
conditions
as
well.
So,
and
you
have
support
for
deletes
as
well,
which
NCC
context
doesn't
so
here
you
can
say
that
when
matched
and
cross
condition
then
update
otherwise
with
the
given
if
it's
match
then,
but
the
class
can
is
not
satisfied
deep.
So
you
can.
A
This
is
very
powerful,
as
we
will
show
in
using
examples
later
to
do
a
lot
of
complicated
changes
in
the
table
and
we're
going
to
talk
walkthrough
few
use
cases
where
this
is
very,
very
useful.
The
other
very
useful
feature,
and
is
this
star
support
to
auto
expand
target
columns,
and
this
is,
as
far
as
we
know,
the
only
sequel,
only
engine
that
supports
this
sort
of
a
syntax
which
is
once
you
introduced.
It
was
a
very,
very
popular
syntax.
A
What
it
does
is
essentially
for
a
very
for
a
table
with
very
large
number
of
columns.
You
do
not
want
to
handwrite
all
the
columns
that
you
need
to
update.
Let's
say
if
you
have
a
bunch
of
changes
which
requires
you
to
update
all
the
columns
if
it
matches
or
if
you
want
to
insert
all
the
columns
with
the
exact
same
as
a
source,
then
you
don't
want
it's
often
tedious
to
handwrite.
A
So
we
also
have
support
for
programmatic
API
is
Scala
and
Python
again
one
of
the
very
few
systems
we
know
of
that
has
actually
programmatic
ApS
for
doing
this
sort
of
sequel
module
operations.
But
it's
it's
I,
think,
depending
on
what
kind
of
future
you
as
a
primary,
a
sequel
user
or
a
primary,
primarily
a
programmatic
software,
engineer
data,
engineer
kind
of
person
which
I'm
more
like
that
I
like
type
safety.
This
is
going
to.
This
is
very,
very,
very
useful.
A
So,
under
the
hood,
what
happens
is
essentially
the
exact
same
thing
is
update.
There
are
two
puzzle
of
the
data
want
to
find
the
list
of
files
that
needs
that
has
matches
and
therefore
needs
to
be
updated
and
a
second
pass
to
update
those
files
by
rewriting
them
as
new
files.
But
the
interesting
bit
is
that
now,
unlike
update
and
delete
here
in
both
case,
you
have
to
do
or
join
between
the
source
the
target
to
actually
find
the
matches.
A
The
first
kind
is
a
cylinder
join
between
the
between
the
target
and
source
to
find
the
match.
The
second
one
is
an
outer
join
between
only
the
selected
files
in
target
and
the
source
to
generate
to
do
the
update,
delete,
insert
operations
on
the
on
the
data
on
those
files
and
in
some
of
the
data
may
be
copy.
Some
of
the
metadata
may
be
updated,
deleted,
inserted,
etc.
Again,
Jenny
will
demonstrate
this
much
more
effectively.
Then.
What
do
you
do?.
B
Thank
you,
sir,
and
this
time
I
actually
bothered
remembering
how
to
go
ahead,
unmute
myself,
so
that
actually
usually
helps
cool
alright.
So
exactly
what
TD
was
talking
about
em
but
I?
Don't
worry.
I've
asked
you
folks
to
go
ahead
and
ask
your
questions
in
the
Q&A
panel.
It's
not
that
we're
not
willing
to
answer
them.
It's
just
that
we're
gonna
most
likely
answer
some
of
these
questions
after
the
session.
B
Once
we
do
the
the
regular
presentation
portion
of
it,
okay,
so
alright,
so
here's
just
a
quick
animated
gif
about
the
insert
update,
slash
process
where,
when
you're
running
a
merge
right
so
typically
underneath
the
covers.
Basically,
what
we're
doing
just
like
TD
had
called
out
you
identify
the
new
rows
to
be
inserted,
identify
the
rows
that
need
to
be
replaced.
Ie
updated,
identify
the
URLs
that
are
not
gonna,
be
impacted
by
the
insert
the
update,
create
new
temp
tables,
delete.
The
original
team
will
Rini
the
timetable
and
drop
the
tip.
B
Now,
that's
if
you
were
to
do
it
under
the
regular
park'
process
right,
so
it's
pretty
inefficient
and
it
requires
you
to
actually
do
all
the
writing
and
I'm
lazy.
So
I
don't
want
to
do
all
that.
Writing.
So
that's
why
TV
shows
us
awesome,
merge
syntax,
which
actually
simplified
that
process
massively
alright.
So
in
other
words,
in
with
when
I
do
that,
I
can
just
simply
write
a
merge
statement.
B
Okay,
so
let's
use
a
quick
example
here:
I'm
gonna
go
ahead
and
take
a
look
at
some
data
here,
for
example,
if
I
was
to
look
at
just
New
York
with
loan
ID
less
than
30.
This
is
the
three
rows
that
I
have
here:
okay
in
terms
of
what
that
could
potentially
be
impacted,
okay
of
a
loan
idea
of
11
21
28,
each
of
them
being
funded
with
about
thousand
to
twelve
hundred
dollars.
Here's
the
different
paid
amounts
and
the
the
state
being
New
York
all
right.
B
Now,
let's
create
a
new
table
that
we
want
to
now
run
the
merge
okay.
So
that
remember,
the
first
table
is
the
source
table.
It's
of
the
14
705
three
rows
of
the
14
705
that
potentially
can
be
impacted.
Now.
Here's
this
new
table
that
we're
creating,
which
is
listed
here,
where
basically
for
loan
ID
11
for
$1,000
we're
gonna,
go
ahead
and
pay
it
off
right.
That's
the
first
of
this
first
row
that
you
see
here,
okay,
so
that's
why
the
funded
amount
of
thousand
and
the
paid
amount
of
thousand
okay
all
right.
B
So
then
we're
also
gonna
add
a
new
loan.
Okay
and
we're
also
gonna
for
the
fun
of
it
also
add
a
duplicate
loan:
okay,
in
other
words
by
accident,
the
source
system
screwed
up.
So
there
was
a
duplication,
alright,
so
again,
as
opposed
to
going
ahead
and
running.
All
of
those
other
statements
were
I'm
gonna.
B
Do
an
insertion
specifically
for
low
9012,
I'm
gonna
duplicate
the
data
myself
428
and
I'm
gonna
go
ahead
and
up
run
an
update
specifically
for
for
loan
11
I'm,
just
gonna
run
a
single
merge
statement,
and
it's
really
simple
right
here.
Actually,
it's
right
here:
okay
and
so
I'm
using
the
Python
syntax,
because,
unlike
TD,
who
loves
type
safety,
I'm
lazy,
so
I
don't
like
doing
it.
No
I'm
joking
I
just
happen
to
like
the
Python
language.
That's
all,
even
though
the
the
tabs
irritate
me
a
lot.
Nevertheless,
I'm
gonna
simply
do
a
merge,
we're.
B
Basically
it's
where
the
source
table
s
not
loan.
Id
is
equal
to
the
target
table.
T
dot
loan,
ID,
okay,
where
you
basically
when
it
matched
you
update
them
all
when
not
match
you
insert
them
all
and
then
implied
inside
here.
Also
is
the
deduplication
as
well.
Okay,
so
same
idea,
we
have
a
bunch
of
stages.
Okay,
now
there's
a
couple.
Quite
one
question
in
terms
of
that
was
asked
which
I'm
gonna
answer
right
now,
which
is
about
the
execute
plan.
B
Query,
the
query:
cool
toss,
cough
query:
cool
tool,
costs;
okay,
that's
actually
what
the
spark
UI
is
for
right.
It
actually
provides
you
a
lot
of
that
context.
I'm
gonna,
look
at
18:44
using
the
sequel
tab,
just
as
TD
head
cold
out,
so
I'm
gonna
go
look
at
this
right
when
I
look
here.
The
query
plan
is
actually
actually
before
I
even
show
you
the
dag.
B
Here,
let
me
do
a
quick
Cola,
the
logical
plan
and,
though,
is
actually
listed
right
here,
so
you
can
actually
understand
logically,
what's
orcs
doing
underneath
the
cover,
so
you
can
actually
go
ahead
and
see
similar
to
your.
You
know
when
you're
working
with
a
relational
database,
you
have
a
logical
plan,
same
concept.
All
that's
actually
placed
right
here
now.
B
Obviously,
some
people
are
gonna
turn
on
se,
but
I'd
love
to
understand
how
it
works
graphically,
and
then
that
goes
back
up
to
here
here,
right
which
basically,
the
timing
is
here,
some
milliseconds
for
the
whole
stagecoach
in
here.
One
second
for
this
particular
whole
stagecoach
in
in
order
for
it
to
do
its
various
processes.
Okay,
so
a
lot
of
your
query,
information
is
actually
all
in
that
spark
UI.
So
you
can
figure
out
how
to
improve
the
performance
by
understanding.
What's
going
underneath
the
covers
so,
for
example,
this
one
implied
right
away.
A
A
B
18:45,
okay,
so
here
is
that
sort
merge-join
right
so,
in
other
words,
here's
the
two
files?
Okay,
so,
for
example,
when
you
open
this
one
up,
okay,
it
tells
you
right
away.
17
milliseconds
was
taken
to
read
a
single
file.
Alright,
that
file
was
the
file
that
contained
the
14
705
rows
of
data
that
we
were
working
with,
okay,
and
so
in
fact,
it
actually
tells
you
right
here.
B
It
tells
you
about
the
cache,
the
filesystem
reads
and
all
these
other
statistics
which
actually
helps
you
understand
why
the
query
performance
is
gonna,
be
that
particular
way,
but
it
lets
you
know
right
away.
So
exactly
as
TV
was
explaining
about,
like
you
know
the
number
of
files
or
partition
right,
you
will
be
able
to
tell
like
if
this
is
running
in
seconds
or
minutes
right
a
very
long
time.
B
Perhaps
partitioning
needs
to
come
into
play
because
you
need
to
go
ahead
and
reduce
the
number
files
are
gonna
need
to
be
read
because
for
sake
argument,
you're
only
trying
to
process
one
state
or
one
date
of
data
as
opposed
to
three
years
of
data,
using
that
as
a
simple
example.
Okay,
all
right,
let
me
close
this
up.
There
go
all
right
so
then
versus
this
scan
existing
RTD.
That
was
the
three
rows
that
we
created.
Remember.
We
had
created
this
loan
updates
table.
B
Well,
those
this
is
what
the
skin
our
existing
our
DD
is
again.
This
is
a
small
idea,
but
here's
the
projection
that
you
can
see
it
right
here.
The
loan
ID
the
funded
amount,
the
paid
amount.
This
is
the
our
DD
that
we
explained
right
then
exactly
what
TD
was
talking
about
in
terms
of
the
joins
that
happen.
B
Oh
sorry,
the
projections
here
right
here
right
in
terms
of
how
it
figures
out
the
logic-
that's
actually
all
shoved
inside
here,
okay,
and
so
the
idea
is
that
underneath
the
covers,
then
you
can
tell
right
away
what's
actually
happening
to
the
data.
Okay,
in
other
words,
it's
grabbing
one
file.
It's
grabbing
the
three
rows.
It's
gonna
do
a
sort,
merge
joint,
that's
what
this
example
tells
us
versus
the
previous
step.
B
1844
I
believe,
yes
right,
this
is
actually
doing
a
broadcast
exchange
right
in
terms
of
here's.
The
amount
of
data
that's
being
pushed
over
right.
That
tells
you
that
the
data
size
itself
actually
helps.
You
understand.
Okay,
if
you've
got
a
ton
of
data,
that's
being
broadcast
or
exchanged
over
and
over
again
for
this
broadcast
hash
joint,
that's
happening
right
here,
yeah
this
tells
you
that
you've
you're,
probably
transferring
or
shuffling
or
moving
too
much
data
across
the
wire
right.
So
again,
can
you
sure
shuffle?
Can
you
as
a
filter?
B
Can
you
partition
to
reduce
the
sizes
that
you
are
working
with?
Okay
and
then,
ultimately,
back
to
this
again,
we
look,
for
example,
the
the
output
of
this
is
when
I
look
in
New
York
there
there's
the
loan
ID
11,
which
is
that
basically
the
one
where
we
updated
it
right.
Here's
loan
ID
12,
which
basically
is
the
brand
new
one
that
we
just
added
and
here's
the
other
loans
that
basically
were
unaffected
right.
B
In
other
words,
we
actually
put
a
duplicate
28,
but
that
duplicate
never
actually
entered
in
there,
because
the
merge
statement
automatically
had
deduplicated
the
data
okay
and
then
same
idea.
When
I
would
look
at
the
history
right,
I
can
look
at
the
metrics
right.
The
operation
metrics
are
right
within
the
history,
so
you
know
how
many
rows
were
copied
in
this
case
of
the
14
7
to
514
702
were
actually
copied.
Okay,
we
only
looked
at
one
single
file
in
order
to
make
the
sense
of
it
the
target
rows
that
were
updated.
B
Three
of
them
were
actually
updated
and
the
based
on
the
source
rows
of
three.
Oh
sorry,
you
know
okay
and
the
number
of
bikes.
So
all
that
information
is
basically
packed
inside.
Basically,
the
operation
metrics
that
you
see
within
the
history
table
and
within
the
spark
UI
the
sequel
tab
to
basically
make
sense
of
from
the
UI
perspective,
cool,
hey,
TD.
Anything
else.
Do
you
think
I
should
be
adding,
ordered
we're
good
to
go
here.
A
A
Let's
talk
about
how
we
can
improve
performance,
so
so
it
simply
because
I
said
with
update
easy
for
you
to
understand
what
goes
on
underneath
the
covers.
Then
he
just
showed
you
there's
an
inner
join
and
there's
an
outer
join.
So
you
have
to
really
understand
that
what
is
the
bottleneck
between
the
inner
and
outer
ature?
If,
if
you,
if
the
inner
join,
which
is
finding
the
file
story
light,
is
the
bottleneck
taking
the
most
time
in
the
query,
then
you
have
certain
optimization
techniques.
A
If
it's
the
other
one,
then
you
have
a
certain
other
optimizer
tables
so
for
if
the
inner
join
is
slow,
that
is.
You
have
you're
taking
a
lot
of
time
to
just
find
the
file
to
update.
Then
again,
the
standard
techniques
of
object
guiding
more
predicates
in
to
narrow
down
the
search
phase,
helps
you
can
obviously
adjust
the
shuffle
partitions
the
standards
for
optimization
techniques
and
there's
number
of
shuffle
partitions
to
control
the
parallelism
of
the
shuffle.
That
needs
to
be
done
for
the
join
you
can
adjust
the
broadcast
join
to
tweak
it
towards
broadcasting.
A
It
allow
larger
data
to
be
broadcasted
if
the
source
is
small
enough
to
fit
in
a
single
machine
memory.
If
there
are
some
times
it
slows
down,
because
there
are
too
many
small
files
in
the
table
like.
If
you
have
a
million
of
teeny
kilobyte
sized
files,
then
the
overheads
of
reading
each
one
of
them
is
much
higher.
Then
you
should
compact
within
the
table.
There
is
only
documentation
on
how
do
you
rewrite
the
layout
of
the
Delta
table
to
compact
them?
A
But
then
again,
you
shouldn't
make
extremely
large
files
like
10
GB
files,
because
it
will
remember
that
we
update
we
relied
only
on
the
granularity
of
files
and
the
larger
files
that
you
create.
The
more
data
may
need
to
be
rewritten
unnecessarily.
So
if
there
is
only
one
row
that
needs
to
be
updated
in
a
file,
it's
it's
cheaper
to
update
the
rewrite
and
entire
100
MB
file,
rather
than
a
10
GB
file.
For
that
one
row
being
updated.
So
you
have
to
tune
that
based
on
your
workloads
requirements
and
then
individual.
A
It
has
few
of
performance
optimizations.
The
z-order
optimize
that
I
mentioned
earlier
to
sort
the
data
in
a
certain
in
smart
ways
which
allows
you
to
exploit
the
locality
of
updates
that
if
your
changes
are
going
to
be
in
a
certain
range
of
values,
with
only
for
a
particular
column,
then
you
can
see
how
to
optimize
by
that
column,
to
get
better
locality
so
that
more
less
number
of
files
are
touched
and
needs
to
be
updated.
A
So,
but
on
the
other
hand,
if
your
outer
join
the
second
scanner
is
actually
rewriting
the
files
is
slow,
then
there
are
different
set
of
techniques.
Well,
there
are
common
ones
like
adjustable
partitions
that
again
controls
the
parallelism,
but
sometimes
what
happen?
If
that,
if
you
parallel,
is
it
too
much?
You
can
generate
too
many
small
files,
especially
with
partition
tables.
You
can
generate
too
many
small
files
for
that.
A
The
the
solution
the
the
knob
we
have
provided
is
that
you
can
actually
reduce
the
number
of
files
by
enabling
automatically
partition
of
the
data
based
on
the
partition
comes
before
the
right
now.
This
is
available
as
optimized
right
in
database
Delta
Lake,
but
we
in
the
next
upcoming
release
of
0
6
0,
which
we
will
release
tomorrow
or
day
after
tomorrow,
when
tomorrow,
actually
0
6.
A
0
also
has
support
for
this
automatically
partitioning
of
data
inside
mods
before
right,
then,
if
it's
a
now,
if
it's
a
full
outer
join
the
outer
join
that
you,
the
inspark,
cannot
do
any
sort
of
broadcast
respond
with
in
support
vodkas,
Jennifer,
dr.
Jones,
but
starting
from
Delta
X,
zero,
six
zero,
it's
possible
that
it
may
be
a
right
outer,
join
and
spark
in
that
case
can
do
broadcast
joins
and
then
again
you
make.
A
If
you
observe
that
in
the
spark
UI
that
you
are
doing
a
right
outer
join
when
looking
at
the
logical
and
the
plans
as
Danny
showed
in
the
spark
you
I,
you
know
also
get
it
programmatically
as
well,
then
you
can
address
the
broadcast
rationals
to
make
it
broadcast
larger
volume
of
data.
Then
what
is
what
spot
default?
Is
you?
It
also
helps
to
cache
the
source
stable
data
rate,
because
you're
going
to
do
two
passes
on
the
both
the
source
and
the
target,
especially
on
the
entire
source.
A
It
helps
to
cache
the
source
table
or
date
of
him.
It
can
speed
up
the
second
scan,
but
it's
recorded
remember
that
do
not
catch
the
target
delta
T,
because
caching,
the
Delta
table,
can
lead
to
weird
cache
coherence
issues,
because
if
the
Delta
table
updates
the
cache
doesn't
it
all
can
leads
to
all
sorts
of
confusing.
A
So
generally,
don't
cache
the
target
table
if
you're
going
to
object,
it
anyways
we're
we're
kind
of
short
on
time,
so
I'm
going
to
kind
of
squeeze
through
the
common
design
patterns
very
quickly,
so
the
common
design
patterns,
then
you
showed
a
little
bit
of
that
in
this
example
as
well
is
deduplication
during
an
ETL
in
util
pipeline
can
generate
duplicates.
You
don't
want
the
duplicates
in
your
final
data
table.
A
So
if
you
write
a
merge
query
that
only
inserts
only
if
they're
the
unique
ID
based
on
which
you
can
t
duplicate
only
if
it
doesn't
match
that
means
that
row,
the
new
row,
that
being
your
being
inserted,
is
not
in
the
table,
judging
by
the
unique
ID
only
then
insert
so
this
is
again
another
example
of
the
extended
merge
syntax
that
you
may
not
even
specify
when
master
clause
at
all.
You
can
only
specify
when
not
matched
then
insert
so.
This
is
a
kind
of
deduplication.
Now
you
can
optimize
it
further
by
well.
A
The
problem
will
face
is
that
it
may
scan
the
entire
table
every
time
to
find
out
whether
the
unique
ID
exists
or
not.
So
you
can
optimize
this
further.
If
you
know
that
your
duplicate,
it
is
going
to
come
within
only
a
certain
time
period
like
if
you
have
only
duplicated
only
over
last
7
days
and
not
older
than
that,
then
you
can
specify
inject
that
7
day
constraint
as
part
of
the
match
condition
and
therefore
they're,
forcing
must
only
search
last
7
days
of
data.
For
so
these
are
optimizations.
A
You
can
do
another
common
pattern.
Is
using
structure,
string,
structure
streaming,
your
you
may
compute
Gary
gates
using
structure
assuming
very
easily
I
previous
talks
in
Sparks,
and
we
can
start
based
on
structure
streaming.
If
you
can
look
it
up,
we
want
to
learn
more
about
structure
streaming,
but
what
comes
out
of
structure?
A
Another
common
pattern
is
gdpr,
which
is
very
easy
to
do
with
delete
and
vacuum.
You
delete
the
user
from
the
info
table
and
then
back
in
the
table
to
ensure
that
it
is
actually
physically
from
the
files,
but
if
there
are
again
better
ways
of
doing
that,
if
you
want
to
maintain
the
entire
history
of
a
user,
then
rather
than
rely
on
the
tables
history,
you
can
store
the
history
of
the
user
explicitly
by
keeping
all
the
previous
records
of
the
user
in
the
latest
version
of
the
table.
A
Another
extremely
common
pattern
is
change.
Data
capture
people
often
want
to
take
change
data
from
their
TV
databases
and
want
to
do
OLAP
operations
on
them
without
affecting
their
elective
databases.
So
they
were
the
changes
made
in
the
weldability
base
and
applied
to
some
table
in
the
Delta
lake,
the
format
for
which
is
great
for
OLAP.
So
for
that
again
the
merge
operation
supports,
delete,
insert
update
all
in
the
same
syntax.
So,
based
on
your
sequence
of
changes,
you
can
very
easily
apply.
A
Those
changes
from
Wendie
with
your
life
in
a
very
common
part
will
be
absolutely
any
ways
to
end
my
talk.
Let's,
let's
have
a
little
bit
of
the
community
and
the
ecosystem.
We
are
very
aggressively
making
releases
every
almost
every
quarter,
making
significant
improvements
in
terms
of
the
connected
ecosystem,
as
well
as
the
performance
of
very
popular
operations
like
mods
and
stuff
Ziya.
A
What
is
we
are
good,
really
something
in
the
next
couple
of
days:
zero,
seven,
zero
we're
going
to
release
our
patches
power,
3.0
support,
which
brings
a
whole
list
of
stuff
like
sequel,
radial,
support,
sequel,
DML,
support,
support
for
defining
tables
in
hive
meta
story
in
all
of
that,
and
this
connector
ecosystem
is
really
growing.
There's
online
documentation
on
how
to
use
Delta
Lake
with
Amazon
redshift,
Athena,
presto,
snowflake
and
hi.
A
We
just
released
a
couple
of
weeks
back
is
the
hive
connector,
where
you
can
natively
query
Delta
tables
from
hi
apart
from
hive
directly,
so
you
have
a
growing
partner
of
ecosystem
partner
that
is
promised
to
support
Delta
link
between
within
their
platforms
and
a
very
large
list,
and
a
very
rapidly
growing
list
of
users.
Of
Delta,
thank
you
very
much.
B
B
A
Yes,
in
most
cases,
it
doesn't,
but
in
some
case,
because
it
when
you're
wearing
the
table
using
take
the
because
of
transaction
log,
it
doesn't
need
to
list
the
directories
to
find
out
which
files
you
need.
So
it
doesn't
really
matter
from
Delta
next
point
of
view
whether
there
are
too
many
files
in
the
directory
for
a
very
large
number
of
versions
or
not.
But
in
some
cases
we
have
observed
that
with
very
very
long
history
like
that
leads
to
millions
tens
of
millions
of
files.
A
A
B
Perfect
and
then
relate
it
to
again
we're
gonna
talk
about
the
file
system,
a
little
more
just
because
it
along
there
seems
to
be
a
lot
of
questions
about
that
yeah.
What
about
the
file
distribution
after
you?
Do
the
updates
and
deletes
right?
There's
the
this,
the
I
guess
the
concern
is
that,
basically,
because
we're
adding
new
files,
as
we've
been
talking
about
during
an
update
and
delete
and
we're
cream
with
fruit
Foom,
so
a
little
bit
of
tombstone,
all
eventual
be
able
to
speak
English.
What
about
the
file
distribution
so.
A
Essentially,
this
in
terms
of
the
different
operations
just
read
as
a
data
frame,
we
partition
to
a
smaller
number
of
files
and
I
get
back
into
that
partition,
and
there
is
an
additional
support
for
an
option
called
data
change
setting
to
false,
which
means
that
it
is
telling
the
transaction
log
that
there
is
no
data
that
was
changed.
Only
data
was
rearranged
and
that
helps
in
actually
that
write
operation
to
not
conflict
with
any
other
actual
data
change
operations.
So
again,
all
of
this
is
documented
in
our
ok.
B
And
just
to
add
exactly
the
TD's
point
during
the
unpacking,
the
transaction
log
section,
we
actually
dive
a
little
bit
into
that
as
well.
So
there's
a
just
speed
up
to
the
section
where
we
talk
about
file
compaction
and
also
the
notebook
itself,
that's
associated
with
that
particular
Tech
Talk.
We
actually
dive
it
not
only
dive
into
it,
but
we
show
you
just
just
like
we
do
here.
We
show
you
in
a
demo
exactly
how
that
works.
B
A
Ok,
so
in
terms
of
Delta
Lake
operations
like
reading
and
writing,
there
is
absolutely
no
difference
it
between
spark
between
Spanish,
I,
hope,
I.
Three
in
terms
of
what
processing
you
want
to
do,
an
addition
on
top
of
that,
once
you
have
them
as
data
frames
and
stuff,
that
is
where
there
may
be
slight
performance
difference
between
Python
and
Java.
It
all
depends
on.
Let's
say:
if
you're
doing
built-in
functions
like
explode
and
stuff,
then
there
is
absolutely
no
difference
because
everything
that's
falling
down
and
code
generated
on
the
Java.
B
B
A
B
Perfect,
okay,
so
relay
I'm
gonna
switch
gears
a
little
bit
now.
We've
talked
about
the
languages
Oh.
Actually,
no
I'm
gonna
stay
in
the
language.
Just
for
this
one,
quick
and
quick
answer:
can
we
use
that?
How
can
we
use
the
table
name
in
the
sequel
query,
as
opposed
to
the
Scala
and
Python
syntax?
You
already
implied
it
with
the
SPARC
3,
not
open,
I
figured.
It
was
worthy
of
us
going
in
totally.
Yes,.
A
I
can
explain
that
in
more
detail,
so
Apache
spark
in
the
2.4
line
or
the
to
extent
before
3.0
does
not
allow
any
data
source
like
Delta
to
really
customize
what
gets
written
out
to
the
Metis
in
for
Delta.
What
we
really
need
is
that
customization,
because,
unlike
like
parka
tables
or
CSV
tables
and
stuff
Delta
does
not
rely
on
keeping
the
meta
it
matters
to
the
other
keeps
all
the
metadata
in
the
transaction
log.
A
That
means
that,
when
you're
querying
or
lining
or
query
on
a
data
table,
it
needs
a
certain
amount
of
customization
such
that
it
should
not
suffer
that
it
can
specify
that
I
do
not
want
all
the
meta
data
stored
in
high
administered.
Other
I
want
to
use
the
meta
data
in
the
transaction
law
that,
since
before
spark
3.0,
there
were
no
API
used
to
do
that.
Customizations.
That's
why
we
did
not
the
OSS
Delta.
It
does
not
working
on
spark.
A
2.4
does
not
support
hive
meta
store,
defined
tables,
because
we
could
not
do
that
customized,
but
it's
Fox
3.0,
you
added
all
the
necessary
API
is
by
working
with
the
spark
community
very
closely.
You
added
all
the
place
so
that
we
can
the
Delta
Lake
datasource
can
do
those
customizations
and
therefore
read
from
hive
meta
store
by
such
that,
given
a
table
in
can
map
it
to
just
the
location,
ignore
all
the
other
metadata
from
the
higher
meta
store.
A
Just
read
the
location
from
that
go
ahead
and
get
the
rest
of
the
metadata
from
the
transaction
log
in
that
location
and
then
plan.
The
query
based
on
this
customization
will
come
with
spark
3.0,
which
the
time
frame
would
be
some
probably.
We
can
only
guess
the
process
takes
its
little
unpredictable
for
such
a
major
release.
You
probably
land
somewhere
between
June
and
July,
most
likely
and
as
soon
as
we
will
have
delayed
release,
most
likely
in
seventies.
B
Excellent,
ok,
I
think
we
only
had
time
for
probably
one
more
question,
so
we're
just
gonna
leave
it
at
that.
The
question
is:
how
does
the
connectors
with
presto-
or
you
know,
basically,
work
when
it
comes
to
trying
to
read
Delta
like
right?
Does
it
actually
look?
It
read
the
Delta
log
itself
to
figure
things
out.
How
does
presto
connect
basically
very.
A
A
Is
that
it's
basically
a
bunch
of
text
file
that
contains
the
names
of
the
data
files,
the
Marquis
data
files
that
that
someone
needs
to
read
to
read
a
full
snapshot
of
the
Delta
T,
so
the
Delta
Nate
API
is
provide
the
command
for
generating
these
manifest
files
on
Delta
table,
which
can
then
get
be
led
by
resto
to
read
the
actual,
to
figure
out
what
are
the
actual
party
files
that
needs
to
be
read
to
prove
his
identity
and
then
according
equate,
so
Christo
has
inbuilt
support
for
this
manifest
facts.
Well,
that's
open!