►
From YouTube: Tutorial: How Delta Lake Supercharges Data Lakes
Description
Delta Lake’s transaction log brings high reliability, performance, and ACID compliant transactions to data lakes. But exactly how does it accomplish this?
Working through concrete examples, we will take a close look at how the transaction logs are managed and leveraged by Delta to supercharge data lakes.
In this tech talk you will learn:
- Enabling and configuring OSS Delta Lake
- Creating Delta Lake tables
- Using history() to view metadata and table versioning
- How Delta manages the log files
- What goes into the transaction logs for various DML operations
- How Delta constructs snapshots of data
- The small file problem and how to mitigate it
- How to construct time travel queries
- Configuring Delta tables for deleted files and log retention
Speaker: Louis Frolio is a Senior Technical Instructor at Databricks. Leveraging his successful career in Data and AI, Louis trains Databricks business partners on Databricks and Spark. He has two Master Degrees, one in Applied Physics from the University of Massachusetts and a second in Strategic Analytics from Brandeis University. Louis lives in New England with his wife and son. As a former professional chef, Louis still considers himself a culinarian and uses his personal time to explore the world of food.
The notebooks for this video can be found at: https://github.com/databricks/tech-talks/tree/master/2020-08-27%20%7C%20How%20Delta%20Lake%20Supercharges%20Data%20Lakes 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
Hi
there
my
name
is
denny
lee
and
I'm
your
host
today
for
this
awesome
awesome
tutorial
session
on
how
delta
lake
supercharges
daedalix
with
louis
frollo
we're
gonna
start
in
about
two
minutes,
so
note
that
we
will
start
very
shortly.
We
will
be
answering
your
questions
via
the
q,
a
so
on
the
bottom
of
your
zoom.
If
you're
using
zoom
click
on
the
q,
a
and
ask
your
questions
there
or
if
you're
on
youtube,
live
no
problem
at
all,
there's
the
live
chat.
A
So
we
will
be
also
on
that
to
answer
some
of
your
questions.
There
zoom
be
a
little
bit
more
straightforward
to
be
honest,
but
if
you're
on
youtube
live,
don't
worry
we're
going
to
be
there
to
answer
your
questions
as
well,
so
as
we're
waiting
for
the
for
people
to
dial
in
for
the
next
minute
or
so.
If
you
want
to
chime
in
on
the
chat
and
tell
us
where
you're
from
which
city
you're
located
myself
again,
my
name
is
danielle.
I'm
your
host
today,
I'm
based
out
of
seattle,
washington.
B
A
It's
not
sunny.
Okay,
sorry,
to
hear
that.
Okay,
we
got
somebody
from
new
york
city
from
originally
from
paris.
I
wonder
if
that
that's
the
same
person
that
I
was
talking
to
from
two
days
ago,
if
it
is
that's,
awesome,
bonjour
hope
things
are
going
well
and
yes,
you
are
the
same
person.
That's
awesome,
perfect
and
oh
france,
montpelier
sunny
as
usual
from
one
pillar.
That
is
awesome.
Well,
bienvenue.
Welcome
aboard
glad
to
have
you.
A
A
A
Cool
I'm,
instead
of
actually
going
through
the
chat
right
now.
It
is
two
past
the
hour,
so
louis
want
to
take
it
away
and
start
the
show,
as
noted
for
everybody,
if
you
have
your
questions,
please
prop
them
in
the
q,
a
not
the
chat
for
zoom
or
youtube
live
we're
gonna
myself
will
most
likely
to
answer
your
questions
while
louis
is
gonna,
go
ahead
and
run
this
tutorial
all
right,
louie.
Take
it
away.
B
Thank
you
denny,
so
welcome
everyone
a
bit
about
me.
My
name
is
louis
frolio
or
louis
ferlio.
I
am
a
senior
technical
instructor
here
at
databricks,
been
with
the
company
about
eight
months
now
best
job
ever
prior
to
databricks.
I
came
from
ibm
where
I
was
a
technical
evangelist
prior
to
that.
I
worked
for
emc
corporation
as
part
of
their
big
data
solutions
team,
where
I
helped
with
technical
enablement
and
prior
to
that
many
roles
throughout
my
career
as
a
data
architect,
I'm
a
data
guy.
B
B
Currently
I
mentor
with
the
company
called
american
corporate
partners
acp
and
what
they
do
is
help
active
duty.
Military
people
who
are
you
know
long
term,
had
careers
looking
to
transition
into
the
private
sector.
I
help
out
with
that.
So,
every
year
I
take
on
a
new
person,
and
I
help
you
know,
get
them
out
and
into
a
job.
Usually
you
know
analytics
related
type
jobs,
so
I
enjoy
that
very
much
and
then
here
linkedin,
please
feel
free
to
connect
with
me.
B
I'm
always
happy
to
expand
my
network
all
right.
Let's
get
going
here
all
right,
all
right,
so
how
delta
lake
supercharges
data
lakes
right
so
here
delta
lakes,
transaction
log,
it
brings
high
reliability
and
performance
and
asset
compliant
transactions
to
data
lakes,
and
and
so
how
does
it
do
this
right?
That's
the
goal
of
today's
tutorial
right.
B
I
want
to
take
I'm
going
to
walk
you
through
a
close
look
at
the
transaction
log
mechanisms
within
delta
lake
and
how
it
accomplishes
all
the
great
things
that
delta
lake
does
and
which
I
will
go
through
in
a
moment
right,
so
we're
using
for
this
tutorial.
It's
going
to
be
the
open
source
delta
lake
and
in
fact
this
entire
environment
I
am
in,
is
open
source.
B
I
I'm
running
on
centos
7-8
spark,
3-0
delta
lake
0.7.0,
scala,
212
and
then
jupiter
lab
215
right
installing
delta
lake
is
is,
is
trivial
right.
You
simply
need
to
have
an
environment
with
spark3o
running.
If
you
want
to
run
0.7.0
of
delta
lake-
and
you
just
have
to
you
know-
go
to
the
maven
repository
and
download
the
jar
file
put
it
into
spark
home
jars,
and
then
there
are
multiple
ways
to
configure
delta
lake
to
run
in
the
spark
session.
B
I'm
gonna
show
you
one
particular
way
the
way
I
you
know
the
most
convenient
way
for
me
to
do
this
tutorial.
That
is,
source
data.
For
this
notebook
comes
from
the
uci
machine
learning
repository.
It
is
uk
online
retailer
sales
data.
If
you
want
to
learn
more,
I
put
I
put
a
link
here
too.
So
when
you
go
to
the
github
repository
for
all
these
lessons,
you
know
this
notebook
will
be
there
and
you'll
be
able
to
do
that.
B
Just
so
a
bit
about
delta,
late
right,
so
delta
lake
is
this
open
source
storage
format
right?
That
brings
acid
transactions
to
apache
spark
and
big
data
workloads.
On
top
of
data
lakes
right
so
data
lakes,
being
it
could
be
hdfs,
it
could
be
cloud
storage,
you
know
aws3
or
blob
storage,
azure
right.
It's
it's
a
layer
that
sits
on
top
of
that
and
what
delta
brings
to
the
table
is
it's
an
open
format
right?
We,
you
know,
we
use
parquet
rk
file
format
under
the
hood.
B
B
It
has
schema
enforcement
and
schema
evolution.
So,
unlike
traditional
data
lakes
that
are
schema
on,
read
delta
lake
is
schema
on
right,
so
it
you
know
it
helps
ensure.
You
know
your
data
is
consistent
and
doesn't
become.
Corrupt
delta
lake
provides
a
complete
audit
history
of
what's
happening
on
a
particular
table
or
a
set
of
tables
right.
You
can
track
through
time.
The
provenance
from
creation
to
the
current
state
of
that
table
delta
lake
provides
the
ability
to
time
travel
queries.
B
So
you
can
go
back
and
query
the
table
as
it
were
at
an
instance
in
time.
Either
by
time
stamp
or
by
table
versioning
delta
lake
supports,
deletes
and
upserts
on
tables
right,
it
has
scalable
metadata
management
right.
So
you're
gonna
see
that
as
we
go
through
delta
lake-
and
I
show
you
all
the
cool
features
you
know
you
can
see
these
transaction
logs
being
created
and
depending
on
you
know
how
you
set
it
up
and
how
you
use
it
in
your
organization.
B
B
So
this
here's
a
nice
image
demonstrating
you
know,
delta
lake
here
it's
built
on
top
of
parquet
and
it's
these
transaction
logs.
What
I
call
the
secret
sauce
behind
delta
league
right
that
that
provides
all
of
these
cool
features
that
we
just
went
through
within
delta
lake
right.
If
you
have
a
delta
table,
it
supports
batch
loads,
it
supports
streaming
loads
and
it
you
know,
supports,
updates
and
deletes
with
delta
tables.
You
can
do
batch
loading
and
stream
loading
at
the
same
time,
while
someone
else
is
reading
the
table.
B
So
it's
very
powerful
right-
and
you
know,
through
all
of
this
right.
You
know
delta,
provides
that
high
quality
and
reliable
data
all
right.
So,
let's,
let's
dive
in
here
so
the
way
this
is
going
to
work
right.
I
have
this
jupyter
lab
notebook
here,
I'm
going
to
talk
you
through
just
some
quick
setup
and
helpers
and
configuration
how
I
standardize
on
data
file.
Pathing,
we're
gonna
download
some
data,
we're
gonna
define
schema,
we're
gonna
do
just
some
quick.
You
know
clean
and
exploration
of
that
data.
B
Right,
I'll,
walk
you
through.
You
know
how
our
hive
metastar
is
going
to
be
set
up
and
then
I'll
go
through.
You
know
a
few
cells
of
working
with
parquet
files
just
to
remind
everybody,
you
know
how
parquet
works
and
and
what
it
looks
like
and
then
we'll
dive
right
into
delta
lake,
with
all
the
cool
features
and
looking
closely
at
that
transaction
log
right.
That's.
The
purpose
of
this
tutorial
is
to
really
look
closely
at
that
transaction
log.
In
what
happens
and
then
I'll
talk
through
some
administrative
features,
file
compaction.
B
Retention,
all
right
so
set
up
here
right,
so
we
start
off.
Just
you
know
loading
up
our
required
classes.
Here,
I'm
going
to
create
the
spark
session
that
will
be
used
in
this,
this
notebook
right.
So
a
couple
things.
You
know
the
delta
that
jar
file
what
we
installed
right:
here's
how
we
reference
it
right.
If-
and
this
is
just
one
way
of
of
instantiating
and
connecting
with
delta
lake
right-
I'm
gonna.
Do
it
right
here
in
the
session.
B
It
makes
it
easy
another
configuration
here
is
the
delta
spark
session
extension
and
that
just
enables
you
know:
delta
lake,
specific
sql
support
with
apache
spark
and
then
down
here
the
catalog
delta
catalog,
it's
going
to
enable
integration
with
the
catalog
apis
so
that
we
can
use
the
hive
metastar
and
all
that
good
stuff
right
and
then
here
I'm
setting
up
my
shuffle
partitions
in
default.
Parallelism
generally,
you
want
these
to
align
to
the
cores
right,
the
number
of
cores
in
your
cluster.
B
I
have
a
vm
running
here
and
it
has
four
cores,
so
I'm
going
to
set
that
to
four
and
then
finally,
I
have
a
another
notebook
and
by
the
way
this
notebook
will
be
part
of
what
you
know.
Denny
puts
up
in
github
in
the
helper
function
and
it's
a
helpful
function.
I
need
to
look
at
the
files
on
disk
just
to
make
it
easy
from
within
the
notebook
to
be
able
to
reach
out
to
the
file
system
and
have
a
look
at
the
data.
B
B
B
Next
up
is
the
code
to
download
that
data
right
the
data,
the
reference
is
to
the
uc
irvine
machine
learning
repository,
but
I'm
actually
pulling
down
the
data
from
github
databricks
github,
and
it
relates
to
the
spark
the
definitive
guidebook.
The
data
is
nice
and
formatted
there.
So
we
do
that
right.
B
All
right-
and
so
I
went-
and
I
looked
at
the
schema
of
this
data
right,
and
so
I
do
want
to
define
a
schema
when
I
work
with
this
data
in
these
data
frames.
It's
it's,
it's
spark
requires
data
frames
require
schemas
and,
as
most
of
you
probably
know,
you
can
do
infer
a
schema,
where
spark
will
figure
it
out.
B
You
know
we
don't
recommend
that.
That's
not
a
that's,
not
a
best
practice.
You
should
always
define
your
schema
right,
so
you
know,
let's
have
a
just
a
quick
peek
at
that
data.
That
was
downloaded
right
in
text
format.
Here
right
we
can
see.
We
have
the
first
row
is
just
the
column
headings
and
then
just
a
bunch
of
tuples
of
data
right.
B
This
is
the
schema
that
corresponds
to
that
right
and
this
here,
I'm
using
the
sql
ddl
method.
Right
there
are
other
ways
you
can
do
the
struct
type.
I
put
the
the
code
in
here
if
you're
curious
how
to
create
a
schema
with
the
struct
type
for
a
simple
flat
schema
like
this.
This
sql
ddl
method
works.
Fine.
If
you
have
schemas
that
have
nested
attributes
or
nestings
within
nestings,
you're-
probably
going
to
want
to
use
this
struct
type
method,
that
gives
you
more
control
all
right.
B
B
This
data,
some
of
the
queries
we're
going
to
be
doing
right.
We
have
to
ensure
that
some
of
the
data
does
not
have
any
nulls
in
it
right
and
so
looking
at
all
of
the
columns
looping
through
the
columns
and
looking
for
null
count,
I
can
see
that
there
are
invoice
records
that
have
invoice
numbers
that
are
null
and
records
that
have
customer
ids
that
are
null
same
with
description.
B
I
don't
care
so
much
about
description,
but
the
invoice
in
the
country
customer
id
is
important
all
right,
so
I
just
want
to
clean
that
up
and
that's
what
this
code
is
going
to
do
right
here.
For
us,
it's
going
to
remove
those
records
right
and
now
I
see
that
there
are
no
more
nulls.
However,
I
did
lose
143
985
records,
but
that
still
leaves
us
with
this.
You
know
397.
A
B
Right,
so
what
I'm
gonna
do
now
is
create
a
new
data
frame
based
on
our
clean
data
right,
and
I
don't
need
all
of
this
data
for
this
demo.
I
don't
need
all
397
000
rows,
so
I'm
just
going
to
take
a
fraction
25
of
it,
and
I
am
going
to
set
a
seed
so
that
I
this
is
reproducible
every
time.
I
run
this
I'll,
get
the
same
results
and
that's
what
the
seed
is
going
to
do
for
us
and
just
having
a
quick
peek
at
our
data
right.
B
B
Moving
forward
we're
going
to
want
to
work
with
sql
right,
so
delta
lake
was
designed
to
accommodate
olap
type
use
cases
right
and
so
generally,
in
those
use
cases
you
know,
you're
gonna
have
folks
that
are
many
more
folks
that
are
comfortable
with
sql
compared
to
say,
pi,
spark
or
working
with
the
data
frame
api
here
right.
So
we
want
to
set
up
our
hive.
B
B
This
is
just
going
to
clean
them
up.
So
we
can
see
here
that
I
do
have
a
database
called
delta
demo
and
I
my
current
database
is
set
to
that
database
delta
demo,
and
you
know
here's
some
information
metadata
about
itself.
You
know
it
lives
down
this
path
on
my
local
file
system
and
there
are
no
tables
in
it
at
all
it's
empty.
B
B
So
when
I
run
this
command
a
couple
things
one
that
hive
metastore
right
that
database
that
we
that
I
created
will
have
the
metadata
for
this
table
all
the
information
right,
and
so
you
know
what
metadata
you
ask.
Well,
it's
the
metadata
that
it
got
from
the
data
frame
right,
so
the
the
column,
names
column,
types
right
in
the
table,
name
itself
that
I
gave
it
that's
persisted
in
the
hive.
Meta
store.
B
The
second
thing
it's
going
to
do
is
actually
write
this
data
out
to
the
file
system.
So
when
spark
writes
data
to
the
file
system,
it
actually
it
creates
a
directory,
and
within
that
directory
it
will
place
the
files.
In
this
case
it's
going
to
place
parquet
files
right
and
so
right
now
I've
run
this.
This
table
has
been
created,
so
it's
in
the
hive
metastar
and
that
data
is
out
on
disk
all
right.
B
I
can
look
through
the
command
spark.catalog.listtables,
it
will
query
the
hive
metastar
and
I
can
see
that
my
table
is
in
fact
there
right
it's
in
the
database.
I
wanted
it
to
be
in,
and
it's
in
a
table
type
is
external
as
well.
That
just
means
that
I'm
managing
the
data
for
this,
the
pathing
and
the
cleanup
of
that
data
right,
and
so
when
it
writes.
I
I
mentioned
that
it
creates
that
directory
directory
of
files.
Let's
have
a
peek
in
there
and
see
what
we
got
right.
B
B
I'm
able
to
go
ahead
and
see
that
data
right,
it's
what
I'm
expecting
so
with
parquet
we're
able
to
you
know
in
insert
into
right.
Actually
it's
it's
more,
I
think
of
it
more
as
a
of
an
append
right.
So
parquet
files
are
immutable
when
we
go
to
insert
data
into
that
table
here,
those
four
original
files
will
not
change
right.
What
we're
going
to
see
is
that
a
new
file
gets
created
right.
B
So
I'm
just
going
to
add
one
record
here
and
what's
going
to
happen,
is
we'll
see
that
we
have
that
one
file
right
here
right
and
if
I
were
to
put
the
path
in
there
I'd
be
able
to
see
the
record.
I
just
created
right.
So
you
know
above
we
just
saw
how
to
create
table
structures
using
parquet
and
using
the
hive
metastore
right
to
store
that
table
metadata
right.
B
We
use
sql
to
query
and
insert
data
into
that
table.
However,
there's
no
history
there's
no
detailed
history
of
these
events
that
took
place
right.
I
just
know
the
current
state
that
I'm
in
right
now,
but
I
can't
go
and
look
and
say
you
know
what
happened
along
this
timeline
and
what
are
the
details
of
what
happened
along
that
timeline
right.
B
Instead
of
specifying
the
format
as
part
k,
we
just
need
to
specify
it
as
delta,
pretty
straightforward
right,
and
so
here
I'm
going
to
take
that
data
frame
that
we
created
earlier
right
and
I'm
going
to
go
ahead
and
write
it
in
delta
format
to
a
new
path
that
I
call
delta
path
here
right
and
then
we're
going
to
go
ahead
and
select
from
it.
Now
it
should
look
no
different
than
when
we
did
this
with
the
parquet,
because
under
the
hood
right,
the
the
storage
is
actually
is
parquet
files
right.
B
So
it
looks
the
same,
and
so
here
we're
doing
a
query
on
it.
Right
and
what's
neat
here
is
that
I
can
actually
run
sql
against
a
directory
right.
So
here's
my
delta
path,
I
just
need
to
preface
it
with
delta
dot
and
I'm
able
to
query
that
directory
just
like
I
would
if
it
were
a
table
right
we're
going
to
create
a
table
in
a
minute,
but
here
I
just
wanted
to
show
you.
This
works
for
park
k
as
well.
B
This
isn't
exclusive
to
delta
right,
okay,
so
for
that
particular
path,
I
you
know.
I
want
to
create
a
path-based
delta
table
right
and
I'm
going
to
put
it
into
a
variable,
and
once
I
do
that
right
with
delta,
I
can
look
at
that
history.
You
know
one
of
the
the
closing
statement
I
made
about
parquet
was
I
I
don't.
I
don't
know
the
the
history
I
don't
know
you
know
the
providence
of
that
table
running
onto
that
table
from
creation
to
current
state
delta
lake.
B
B
There
are
many
more
attributes
that
you
can
look
at,
I'm
just
taking
a
select
few
here
right
and
we
can
see
that
currently,
the
delta
table
we're
at
version
zero
right,
the
initial
creation
of
it,
and
then
we
have
this
metadata
about
what
happened
to
this
table
at
that
point
in
time.
Right
so
at
this
time
stamp
there
was
a
write
operation
that
occurred
all
right.
The
mode
was
overwrite.
That
tells
me
this
table
was
created
at
this
point
in
time,
and
then
we
have
these
operation
metrics
right.
B
We
saw
that
four
data
files
were
created,
we
see
the
total
size
of
it
and
then
the
number
of
rows
that
were
written
right.
So
I
showed
you
when
we
wrote
that
parque
file
it
created
four
parquet
files,
delta
uses
parquet
right,
and
so
we
wrote
the
same
data
frame
out
to
a
to
a
delta
format
and
it
created
four
files.
B
Right,
and
so
here
is
the
directory
of
data
that
was
created
when
I
wrote
this
delta
data
out
notice.
The
you
know
here
are
my
four
parquet
files:
nothing
special
standard,
parquet
files.
However,
we
have,
we
do
have
a
directory
right.
We
have
a
subdirectory
and
it's
always
called
underscore
delta
underscore
log.
So
when
delta
writes
data,
you
create
a
table
or
write
data
out
to
the
file
system
within
that
directory
of
data
data
is
going
to
delta
is
going
to
create
a
subdirectory
and
within
that
subdirectory
it's
going
to
keep
its
transaction
logs
right.
B
B
Let's
have
a
look
inside
that
json
file
right.
So
there's
a
lot
in
here
right,
but
notice
right
just
at
the
top
here
right
we
saw
that
there
was
an
operation
of
right.
Four
files
were
created,
output,
bytes
right,
a
bunch
of
stuff.
This
looks
you
know
similar
to
this
right.
So
when
you
run
this
history
command
right
delta
is
going
to
actually
go
through.
The
trend,
the
relevant
transaction
logs
and
put
it
in
a
much
prettier
format
for
you,
that's
what
it's
doing
for
us.
This
is
the
raw
transaction
log
file
itself.
B
You
can
see
in
here
the
schema
right
when
it
creates
the
table
it's
going
to
actually
within
the
transaction
log
right
out
the
whole
schema
for
you
and
then
it's
going
to
have
other
information
about
the
protocol
all
right.
This
relates
to
the
versioning
of
delta
lake,
to
be
able
to
read
and
write
et,
cetera
and
and-
and
you
know,
the
creation
time
as
well
right,
so
quite
a
bit
of
relevant
information
in
this
transaction.
B
B
I'm
expecting
well,
let's
see
what
it
looks,
what
it
says
all
right,
so
here's
the
history
right
so
we
can
see
the
table
is
now
at
version
one
right.
The
last
thing
I
did
was
a
write
operation.
The
mode
was
append
right.
I
added
four
files,
so
I
have
a
total
of
eight
files
out
on
the
file
system.
Right
now
and
then
I
just
you
know
the
size
and
the
number
of
rows
right.
So
here
are
those
files.
Here
are
the
eight
here,
the
original
four,
when
I
initially
created
it.
B
So
this
is
ordered
by
time,
and
here
are
the
the
new
four
right.
So
these
eight
files
now
make
up
the
data
of
for
the
current
state
of
that
table
all
right.
Let's
go
look
at
the
transaction
log
right.
So
whenever
we
have
a
dml
operation
right,
we
have
data,
we're
adding
deleting
or
updating
on
a
delta
table.
Those
are
all
those
are
all
transactions.
B
B
The
schema
was
already
addressed
in
the
first
transaction
log
right,
so
delta
is
fully
aware
of
the
schema
right
because
it
would
just
need
to
read
this
transaction
log
here,
and
so
this
one,
it's
just
the
new
files
that
we
added
right
and
then
we
can
see
you
know
the
right.
It
wasn't
append-
and
this
here
this
read
version
right.
It's
wrapping
right
here.
It
starts
right
here
right.
That's
saying
that
this
transaction
delta
red
from
version
zero
of
the
table
to
get
the
information
to
be
able
to
write
out.
B
You
know
the
data
right,
which
becomes
version
one
of
the
table
right.
So
it
tells
you
what
it
read
from,
and
so
you
can
see
sort
of
the
pattern
of
what's
happening
here
as
these
transactions
go
through.
The
transaction
log
captures
is
going
to
capture
that
information
and
delta
is
going
to
use
that
right
so
that
when
it
needs
to,
you
know
reconstruct
the
current
state
of
the
table
for
queries.
B
It's
able
to
do
that
easy
enough
and
you'll
see
soon
enough
that
also
these
transaction
logs
are
going
to
be
used
to
help
with
time
travel
queries
all
right.
So,
let's
move
away
from
our
path
base
table
and
just
go
to
a
straight
sql
table
now
right.
So
I
created
this.
It's
called
cells
delta
format.
B
If
I
do
the
catalog,
you
know
query
the
catalog
again
and
list
the
tables.
I
can
see
that
my
table
is
created
right
here.
Right
so
I
have
the
original
parquet
table
and
I
have
this
delta
table
right
and
what
I
want
to
do
now
is
to
step
through
some
dml
operations.
We
worked
with
inserts
so
far
adding
data.
Let's
look
at
what
happens
when
we
do
updates
and
deletes
all
right.
So
the
first
thing
I
want
to
do
is
just
generate
a
random
invoice.
B
I'm
gonna
go
out
and
query
my
data
and
randomly
pick
an
invoice
so
that
I
can
pick
on
that
invoice
throughout.
You
know
the
rest
of
these
dml
operations
right.
So
if
we
have
a
peek
at
it
right
we
can
see.
This
is
what
the
record
looks
like
now.
I
added
this
this
virtual
column.
It's
called
the
input
file
name
and
what
it's
going
to
do,
what
it
does
is.
B
It
shows
you,
the
actual
file,
the
parquet
file,
that
this
record
lives
in
right,
and
this
is
going
to
be
useful
for
us
we're
going
to
see
it's
going
to
be
useful
in
a
few
minutes
here,
all
right
all
right.
So
I
have
this
invoice
number
here
right
and
I
want
to
actually
add
another
line
item
to
that
invoice
and
that's
what
I'm
going
to
do
here
right,
I'm
at
a
record
the
world's
best
jam
making
set
all
right.
So,
let's
add
that.
B
B
I
just
did
right,
let's
have
a
peek
inside
of
it,
and
we
can
see
here
that
in
fact
we
did
add
another
parquet
file
and
then
the
operation
was
a
write
and
an
append,
and
you
can
see
that
read
version
incremented
right.
It
had
to
read
version
one
of
the
table
to
get
the
information
needed
to
be
able
to
do
the
right
all
right,
pretty
straightforward
number
output
rows
one,
because
I
only
inserted
one
row.
B
Right
so
now
we
can
see
there
are
two
records
right
and
each
of
them.
You
know
the
original
is
right
here,
this
guy
and
then
here's
the
one
I
just
added.
So
these
two
records
live
in
two
different
parquet
files,
two
file
different
files,
all
together
right.
So
now,
let's
see
what
happens
when
I
make
a
change
to
this
invoice.
So
what
I'm
going
to
do
in
the
next
step
is
just
add
a
thousand
to
the
quantity
of
each
of
these
right.
B
All
right,
so
I'm
using
sql
for
that
you'll
see
throughout
this.
I
this
is
a
side
note
I
put
in
the
data
frame
api
syntax
if,
if
you're
more
comfortable,
using
that
over
sql
right
all
right,
so
I
just
updated
these
two
records
right.
Let's
go
ahead
and
run
that
select
again
right
and
we
can
see
that
in
fact
the
quantity
is
now
thousand
and
six
in
here
it's
a
thousand
and
five.
B
I
want
you
to
notice,
though
the
file
name
right.
The
file
names
have
changed
right.
It's
changed
because
again,
those
parquet
files
are
immutable.
If
we
make
a
change
to
data,
that's
in
a
parquet
file,
what's
going
to
happen,
delta
is
going
to
grab
all
that
data.
It's
going
to
read
it!
It's
going
to
apply
the
changes
and
it's
going
to
write
a
new
parquet
file
right,
so
that
original
one
here
right
it.
Actually
it
had
to
rewrite
that
one
and
then
the
one
here
right.
B
We're
currently
at
version
3
of
the
table,
the
operation
was
an
update
and
look
here.
Whenever
you
do
an
update
or
a
delete
or
a
merge
delta
is
going
to
capture
the
predicate
right,
and
so
here
it
was
an
update
where
invoice
equals
this
right.
So
that's
pretty
cool.
It's
going
to
capture
you
know
all
of
the
operational
metrics
as
well.
As
you
know,
for
dmls
it's
going
to
capture
the
the
predicates.
B
By
going
through
these
dml
operations
is
not
going
to
delete
physically
delete
our
records
when
it
says
remove
files,
actually
what
it's
doing
it's
putting
a
tombstone
mocker
on
that
file
right,
it's
it
and
that
indicates
that
that
particular
file
is,
is
not
needed
to
be
able
to
query
the
data
in
the
most
in
the
most
recent
version
of
that
table
right,
it's
not
needed
for
that.
However,
it
might
be
needed
for
time
travel
queries
right.
So
that's
why
delta
is
not
going
to
delete
these
when
these
dml
operations
happen
all
right.
B
So
if
we
look
in
our
folder
of
data
files
right,
we
can
see
it's
growing
right,
and
this
here
was
the
last
operation.
That's
the
update.
I
did
right
it
pretty
much.
I
had
to
rewrite
all
of
that
data
all
right,
and
so
it
created
this
this
big
old
file
right
here,
and
so
as
we
go
through
these
dml
operations,
this
the
the
list
of
files
is
going
to
keep
growing
and
growing,
and
the
list
of
you
know
the
number
of
transaction
logs
is
going
to
keep
growing
and
growing
right.
B
So
we're
up
to
this
is
the
fourth
transaction,
but
it's
zero
base.
So
it's
three
dot
json
right.
We
can
see
here
that,
for
we
had
two
files
that
were
removed
right
and
it's
going
to
tell
us
what
files
are
removed
and
two
new
files
replaced
it
right.
So
this
this
is
these
transactions
right.
They
are
keeping
an
accounting
of
all
of
these
files
that
are
being
added
and
deleted,
as
particular
operations
happen.
Right
so
I
said
when
I
did
that
update
those
files
were
deleted.
They
weren't
deleted.
B
B
Right
and
so
here
right,
so
here's
just
looking
at
that
table
data
again
right
to
remind
us
what
it
is,
because
I'm
gonna
go
and
do
a
delete
now
right,
so
those
records
are
there.
I
wanna
actually
do
a
delete,
so
I
wanna
delete
them.
I
wanna
remove
them
from
the
data
set,
all
right
and
so
again
just
using
sql.
B
B
B
B
B
One
row
lived
in
the
original
parquet
file
with
all
the
other
records
and
then
that
second
row
that
I
added
it
was
the
only
record
in
the
parquet
file
right.
So
if
I
delete
it,
there's
nothing
for
delta
to
recreate
right.
So
it
only
had
to
take
that
first
parquet
file
with
all
the
with
the
record
that
I
was
deleting
along
with
all
the
other
records
it
just
had
to
recreate
that
minus
the
record.
B
I
was
deleting
right,
so
we
got
all
these
deletes
going
on
and
we
can
see
that
you
know
our
parquet
files
are
growing,
we'll
get
more
and
more
parquet
files
right.
If
we
look
at
the
transaction
logs
right,
we
have,
this
is
transaction
four,
all
right.
We
have
a
peak
in
there
right
again,
it's
showing
us
what
was
removed.
What
was
added
and
let's
see,
spark
catalog
the
table.
That
was
impacted
right.
B
Let's
now
have
a
look
at.
You
know
how
these
transaction
logs
are
managed
right.
We
know
what
goes
in
them.
We
know
that
delta
is
reading
them
to
get
to
understand
the
history
of
the
table,
but
these
things
keep
growing
and
growing
growing
right.
If
you
could
look,
if
you
count,
there
are
20
zeros
in
front
of
the
period
here
right.
So
the
idea,
the
intention
is
that
you're
going
to
potentially
have
a
lot
of
these
right
and
so
delta
right
every
10
transactions.
B
It
creates,
what's
called
a
checkpoint
right,
so
a
checkpoint
means
it's
going
to
look
at
the
prior
10
transactions
and
roll
up
that
information
into
that
parquet
file
right.
So
that's
what
I'm
going
to
do
right
here,
I'm
going
to
force
a
checkpoint
right,
I'm
going
to
have
five
transactions
happen
and
it's
going
to
force
a
checkpoint.
B
So,
let's
let
that
go,
and
so
what
I
expect
to
see
when
this
finishes
is,
you
know,
it'll
stop
with
10.json
and
then
those
are
the
10
transactions
and
then
delta
creates
a
parquet
file
with
the
all
of
the
the
transactional
information
that
led
up
to
it
right
right.
So
here
we
go
within
this
checkpoint
file.
B
It
doesn't
necessarily
help
with
the
time
travel.
Queries
right.
Delta
is
going
to
need
all
of
these
past
transaction
logs
for
time
travel,
but
this
here
is
a
roll
up
of
all
of
the
parquet
files
right
that
are
needed
to
be
able
to
construct
that
current
image
of
the
table
right.
So
let's
just
have
a
peek
in
here
and
have
have
a
look
see
what
that
looks
like
right.
B
B
B
And
let's
do
a
select
right
so
here
we
are
right
if
you
follow
these
records
from
the
start
till
the
end
right,
you're
going
to
find
that
it
doesn't
start
with
the
initial
well,
it
may,
but
it
doesn't
necessarily
have
to
start
with
the
initial
parquet
files
that
were
created
right,
because
delta
doesn't
necessarily
care
if,
if
a
particular
file
was
deleted
and
replaced,
what
it
cares
about
is
right
now.
Is
it
able
to
reconstruct
a
current
snapshot
of
that
table
right
and
that's
what
it's
able
to
do
here.
B
So,
if
delta
were
to
read
this
to
do
a
a
query
on
the
table
in
it,
it's
most
recent
version
right.
It's
going
to
look
at
all
these
that
have
the
ads
right,
because
the
deletes
don't
apply
right
so
much
and-
and
so
this
is
important
right,
because
delta
doesn't
need
to
be
able
to
keep
all
those
transaction
logs
or
have
to
open
all
those
transaction
logs.
In
fact,
delta
caches
these
into
memory
right.
B
So
it
has
fast
access
to
you
know
the
transaction
logs,
the
current
state
of
of
the
table
itself
right
and
here,
let's
go
back
here
for
a
second
right.
So
what's
going
to
happen
is
when
another
transaction
10
transactions
occur,
delta
is
going
to
create
another
checkpoint
and
within
that
checkpoint
it'll
have
all
the
files
it
needs
to
be
able
to.
You
know,
answer
questions
on
the
current
version
of
that
table.
The
most
recent
version
of
that
table.
B
All
right,
so
another
nice
cool
feature
about
delta.
Is
this
merge
capability
right?
The
up
cert,
you
know
it
plays
well
into
you
know:
change
data
capture,
type,
use
cases,
data
warehousing,
you
know,
updating,
dimension,
fact
tables
right
of
the
sort
delta.
It's
pretty
straightforward
right.
Here's
what
it
looks
like
in
sql
right.
You
can
do
this
with
the
data
frame,
api,
syntax
as
well,
but
it's
not
it's!
It's
not
nice
to
look
at
it's
harder
here,
it's
a
straight
sql
and
so
the
way
you
do
it
is
right.
B
B
Right
and
so
here
I'm
joining
the
table
in
that
view
on
these
two
keys
and
what
I'm
saying
here
is
when
you
find
a
match,
just
update
all
the
rows
on
the
target
right,
take,
take
the
entire
row
and
update
the
target
right.
There's
a
lot
of
flexibility.
Here,
though,
with
this
update
you
just
don't
you
know
you
don't
have
to
specify
replace
the
whole
row,
you
can
specify
individual
rows.
You
can
do
calculations
to
update
the
target
row
based
on
the
data
coming
in.
B
You
can
even
set
up
to
do
deletes
right,
but
here
for
simplicity,
I'm
just
specifying.
I
want
the
whole
row
replaced
and
when
it's
not
matched
meaning
the
data
is
not
present,
just
go
ahead
and
insert
it
right
and
what's
neat
here
is
that
this
whole
merge,
along
with
all
the
other
dml
operations
that
we've
done
the
deletes
and
the
updates
and
the
inserts
right
they
conform
to
asset
acid-compliant
transactions.
B
So
if
this
is
a
big
update
here,
so
you
get
a
lot
of
data
that
you're
merging
in
you
know,
because
it's
acid
either,
you
know
the
entire
thing
is
going
to
succeed
or
fail.
It
won't
ever
leave
your
table
in
a
partial
state,
and
that
goes
that's
the
same
as
well
for
the
other
dml
operations,
if
you
have
someone
writing
to
a
table
and
someone's
reading
it
right,
they
won't
because
it's
acid
compliant
they're
not
going
to
collide
with
each
other
right.
B
B
B
B
B
B
So
if
you
find
yourself
with
you
know,
15
20,
50,
000,
parquet
files,
all
right
spark
has
to
be
able.
You
know
to
be
able
to
read
that
data.
It's
going
to
have
to
open
all
of
those
files
right,
and
that
can
be
time
consuming
assuming
so
you
want
to
do
file
compaction
right
and
that's
just
reducing
the
size
of
the
number
of
the
files
that
you
have
so,
and
this
corresponds
to
what
we
call
the
small
file
problem.
B
If
you
have
many
small
files,
it's
going
to
be
you're,
going
to
have
a
performance
hit
on
queries
right
and
so
here
I
want
to
actually
create
an
artificial
small
file
problem.
I'm
going
to
take
my
data,
which
is
what
28
files
I'm
going
to
turn
it
into
a
thousand
files
right,
a
thousand
smaller
files
right,
and
so,
when
I
go
to
query
this
spark's
gonna
have
to
open
up
all
of
those
files
to
get.
You
know
all
of
the
the
relevant
files
to
get
the
information
that
it
needs
right.
B
So,
if
I
go
out,
I
can
see
now
that
I
have
a
1028
files
out
on
the
file
system
and
I'm
going
to
go
ahead
and
run
this
query,
which
is
a
doing
a
group
by
an
account
and
I'm
going
to
time
it
and
see
how
long
it
takes
all
right.
So
the
wall
time
is
what
we
care
about
here.
It
took
1.2
seconds
to
run
this
query
with
spark
having
to
open.
You
know
the
the
thousand
some
odd
files
that
it
needed
to
open.
B
Right,
we
can
see
that
we
now
have
1032
files,
because
again
these
operations
aren't
deleting
files
physically
deleting
the
files,
so
we
had
a
thousand
twenty
eight.
I
compacted
it
down
to
four,
and
so
now
we
have
a
thousand
thirty
two,
but
of
the
thousand
thirty
two,
only
four
are
needed
to
run
a
query
against
the
current
state
of
the
data.
So
here's
the
same
query
that
I
ran
above
that
took
1.2
seconds
now.
It's
compacted
less
files
need
to
be
opened.
B
B
I
want
to
talk
briefly
here
about
time.
Travel
queries
right
with
time,
travel
now,
you're
able
to
go
back
and
look
at
a
table
as
of
a
particular
version.
So
let
me
scroll
back
up
here
so
right
here
we
can
see
these
versions
11
10,
9,
8,
7,
6
right.
You
can
write
a
query
to
query
up
against
that
particular
version
of
the
table
based
on
the
version
or
you
can
do
it
by
time
stamp
as
well.
B
All
right
conversion
just
makes
it
easier,
in
my
opinion,
than
trying
to
format
that
time
and
figure
out
the
time
right.
So
we
can
see
here
that
we
are
currently
at
version
13
of
the
table
and
it
has
198
400
rows
of
data
right
right
and
see
here
we
can
see
from
the
history
right
that
we
had
four
files
were
added
right
to
the
system,
so
this
is
right
this
this
is
version
13.
There
are
only
four
parquet
files,
and
that
represents
the
whole
of
the
current
version
of
that
table.
B
I'll
compare
it
to
the
original
right,
so
here
we
see
there
are
99
174,
more
rows,
more
rows
in
version
13
than
version
0
of
the
table.
So
a
lot
happened
from
version
0
to
the
current
version
right
and
so
you're
able
to
you
know,
do
these
queries
with
this
time
travel
mechanism,
and
it's
because
of
these
transaction
logs
and
the
versioning
that
delta
imposes
on
these
tables
every
time
a
transaction
occurs.
B
right
and
so
here
I
want
to
test
that
that
actually
happened.
I
want
to
compare
the
record
count
of
the
current
state
of
the
table
to
the
record
count
of
version
zero
of
the
table,
and
so
that's
what
I'm
doing
right
here.
These
I'm
looking
for
equivalency
between
the
current
version
and
version,
zero.
B
And
it
should
come
back
true
right,
so
I
successfully
rolled
back
that
table
right
and
that
last
transaction
was
transaction.
14
right
and
you
could
see
the
mode
was
an
overwrite
all
right.
It
was
a
write
with
the
mode
with
an
override
telling
me
that
I
recreated
that
table
and
four
files
were
created
all
right,
so
that's
pretty
cool
that
you
can
go
back
to
prior
versions,
roll
back
or
you
can
query
as
of
prior
versions
all
right.
B
You
may
not
want
to
keep
a
history
of
data
around.
You
may
not
be
able
to
right
so
say
think
of
a
gdpr
use
case.
I
call-
and
I
say
please
delete
my
data
all
right
from
and
and
then
the
tech
goes
in
from
their
delta
table
and
they
delete
luffrolio
from
it.
We
know
that
at
that
point
in
time,
they're
they're,
not
gdp,
gdpr
compliant
because
the
only
thing
we
did
was
create
a
new
version
of
the
table.
B
Without
my
information,
however,
those
older
parquet
files
are
still
there
with
my
information
right
and
so
that's
a
use
case
where
you
may
not
want
to
keep
data
around
all
right,
and
so
you
do.
We
have
a
mechanism
called
vacuuming,
that'll,
go
and
clean
up
right.
So
look
here.
We
have
1036
files
in
that
directory
and
vacuuming
is
pretty
easy
right.
You
just
say
vacuum.
B
This
is
using
sql
the
table
name,
and
then
you
have
to
specify
the
number
of
hours
you
want
to
retain
all
right
and
then
this
dry
run
here
it
does,
it's
just
a
dry
run,
it's
not
going
to
actually
delete
it
all
right,
but
here
I'm
saying
zero
is
I
want
no
history
when
you're
done
with
this
vacuum,
I
only
want
those
four
files
and
so
oops.
When
I
run
this
right,
we
get
this
error
message.
B
B
You
could
also,
if
someone's
doing
a
time,
travel
query
and
you
run
a
vacuum
and
you
remove
all
the
data.
Their
query
will
fail
right,
so
delta
puts
in
these
guard
rails
to
keep
this
from
happening
right
because
it's
recommended
to
keep
seven
days
or
168
hours
around
right,
so
to
override
that
we
have
to
set
this
retention,
duration
check,
dot,
enabled
to
false
and
those
guard
rails
are
gone,
and
then
I
can
go
ahead
and
run
this
vacuum
right,
and
so
it's
going
to
take
a
couple
seconds.
B
But
what's
going
to
happen,
is
delta
is
going
to
go
through
the
transaction
logs?
All
right
and
it's
going
to
say
you
know
what
files
don't
I,
what
what
files
are
not
needed
anymore
for
the
current
snapshot
of
the
data
right.
We
saw
here
that
there
were
1036
files
out
in
that
directory
right.
Only
four
of
them
are
needed
right
now
right
because
we
rolled
back
to
version
zero
and
there's
only
four
files
for
that.
B
So
if
we
go
and
look
in
that
directory,
we
only
see
the
four
files
that
are
needed
right
as
well
and
so
yeah
there
we
go
all
right
all
right,
just
doing
a
record
count,
so
by
default
delta
has
a
30
day
if
it
sets
a
a
30-day
window
of
deleted
files
that
will
keep
around
in
30
days
for
transaction
logs.
So
after
30
days
I
mean
30
days,
old
delta
is
going
to
start
cleaning
up
the
transaction
logs
and
those
deleted
files.
B
You
can
override
that
right
by
just
doing
at
the
table
level
right,
you
have
to
issue
an
alter
table
statement
against
the
table
and
you
set
these
table
properties.
So
here
I'm
going
to
set
log
retention
duration
to
seven
days.
In
other
words,
I
only
want
to
keep
seven
days
of
log
files
around
and
same
with
the
deleted
files.
B
A
Thank
you
very
much
louis.
I
really
appreciate
you
taking
the
time
to
pop
this
tutorial.
That
was
awesome.
I
did
also
want
to
call
out
that
the
this
video
is
on
youtube
live
and
the
notebooks
are
there
as
well.
I'm
going
to
go
ahead
and
share
my
screen
just
for
a
second
just
because
I
wanted
to
finish
up.
A
I
apologize
if
we
didn't
answer
all
of
your
questions
but
basically,
like
I
said
it's
a
great
session
so
couple
real
quick
notes:
first,
go
to
delta
dot
io,
and
if
you
want
to
know
more
about
the
understanding
about
how
delta
lake
works,
we
actually
had
a
vldb
2020
paper
that
just
was
published
on
delta
lake
high
performance
asset
table
storage
over
cloud
object
stores.
So
please
be
looking
at
you
it's
a
great
paper.
We
dive
deep
into
it,
and
I
did
want
to
finish
up
that.
A
Basically,
for
next
tuesday
we
have
the
data
collab
lab
with
franco
and
myself,
we're
gonna
be
doing
smart,
sql
queries
powered
by
delta
engine
on
your
lake
house
with
dylan
boswick
and
mary
grace
mesta.
So
that's
it
for
today's
session.
Again,
thanks
very
much
to
louie
for
this
awesome
session.
Please
do
chime
in
with
comments
or
questions
and
directly
into
the
youtube
live.
That's
where
we're
going
to
follow
up
on
the
on
the
youtube
link,
so
again,
yeah
louis
thanks
very
much
anything.
B
Now
I
want
to
thank
everybody
for
that
time.
I
apologize.
I
went
right
up
to
the
window,
but
I
you
know
I
felt
it
was
important
to
go
painfully
through
all
those
details.
So
I
wanted
everyone
to
understand
and
see
what
happened
but
yeah.
Definitely
you
know
post
they
can
post
the
questions
out
on
youtube
right.