►
Description
The Delta Architecture pattern has made the lives of data engineers much simpler, but what about improving query performance for data analysts? What are some common places to look at for tuning query performance? In this session we will cover some common techniques to apply to our delta tables to make them perform better for data analysts queries. We will look at a few examples of how you can analyze a query, and determine what to focus on to deliver better performance results.
About:
Databricks provides a unified data analytics platform, powered by Apache Spark™, that accelerates innovation by unifying data science, engineering and business.
Read more here: https://databricks.com/product/unified-data-analytics-platform
See all the previous Summit sessions: https://databricks.com/sparkaisummit/north-america/sessions
Connect with us:
Website: https://databricks.com
Facebook: https://www.facebook.com/databricksinc
Twitter: https://twitter.com/databricks
LinkedIn: https://www.linkedin.com/company/databricks/
Instagram: https://www.instagram.com/databricksinc/ 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
A
All
right
so
table
structure,
here's
the
thing
with
delta
tables
stats
are
collected
on
the
first
32
fields.
This
is
configurable.
There
is
a
config
called
data,
skipping
num
index
columns
that
essentially
spark
and
delta
will
attempt
to
collect
statistics
on
the
first
of
32
columns
or
whatever
you
have
that
config
set
to,
and
essentially
what
we
want
to
do
is
take
advantage
of
this
operation.
And
how
do
we
do
that?
A
We
want
to
move
those
fields
over
to
the
left
into
the
first
32
positions
or,
if
you've
adjusted,
that
that
config
within
the
the
the
columns
of
that
config
and
what
we
also
want
to
do
is
we
want
to
move
long
strings
that
really
don't
differ,
that
much
to
the
after
the
30
seconds
position
or
that
that
setting
wherever
you
have
that
column
to
and
the
reason
here
is
that
long
strings
are
kryptonites.
The
stats
collection-
and
we
really
don't
want
to
waste
time
if
it's
not
going
to
help
us
with
query
execution.
A
Table
properties
all
right,
so
here's
the
thing
there's
this
awesome
new
feature
called
auto
optimize,
optimize
right
and
essentially
what
it
does
when
you
break
it
all
down,
there's
an
adaptive
shuffle
that
actually
happens
before
the
write
operation
that
organizes
the
data
accordingly,
so
that
it's
doing
a
more
efficient
write
to
the
partitions
in
the
delta
tables,
and
this
works
for
all
of
the
different
types
of
write
operations.
So,
if
you're
writing
back
to
persisted
store,
you
want
to
turn
this
feature
on.
A
You
know
the
way
that
this
feature
works
and
how
I
see
it
in
the
wild.
I
highly
advise
just
turning
this
on
on
the
cluster
level.
Unless
you
find
performance
to
be,
you
have
an
edge
case
or
something
where
it's
really
not
taking
advantage
of
it
and
where
I
really
see
the
benefit
of
this
is
in
all
of
the
right
operations,
but
also,
if
you're
not
running.
A
If
so,
if
you
have
a
stream,
that's
coming
in
constantly
adding
data
to
your
delta
tables
and
you're
and
you're,
maybe
you're
running
optimize
every
every
day
or
it's
on
a
schedule
with
optimize
rights
when
it
does
that
adaptive
shuffle
before
the
right
actually
organizes
the
data.
So
on
streaming
queries
we
actually
get
better
performance
on
selects
in
between
optimize
runs.
So
in
general,
I
in
the
wild
I'm
seeing
really
good
performance
with
this
setting,
so
I'm
advising
to
give
it
a
shot.
A
What
we
do
is
we
randomize
prefixes
on
s3,
so
we
can
avoid
the
hotspots
in
the
metadata
and
what
we
do
is
we
we
dedicate
an
s3
bucket
to
a
delta
table,
so
you
have
to
put
it
at
the
root
and
then
you
turn
on
this
table
property
randomize
file,
prefixes,
and
this
what
this
does
is.
It
helps
even
out
the
the
requests
so
that
it's
not
putting
hot
spots
on
s3
and
you're,
not
getting
errors
from
s3
during
production
runs.
A
A
A
So
what
you
want
to
be
able
to
do
is
is
essentially
what
optimizes
the
order
is,
is
optimized
will
bin
pack,
our
file
files
for
better,
read
performance
and
z
order,
will
organize
our
data
for
better
data
skipping,
so
data
skipping
is
already
included
with
the
stats
collection
that
we
talked
about
a
little
bit
earlier,
but
what
z
order
does?
Is
it
actually
organizes
that
data
so
that
you
range
selections
are
more
effective,
so
data
skipping
becomes
more
effective
when
we
organize
that
data?
So
what
how
should
you
be
doing
this?
A
How
should
you
be
organizing
that
data
so
commonly?
So,
if
you're
in,
like
the
data
warehousing
realm
or
if
you
have
those
types
of
etl
jobs,
what
you
want
to
do
is
the
order
by
the
keys,
so
the
primary
foreign
keys
in
your
demon,
fax
tables,
so
come
and
join
operations
and
remember
on
numericals,
so
keys
are
very
common
to
be
numbers
between
tables.
So
we
want
to.
We
want
to
z
order
by
these
keys,
so
on
very
large
fact
tables.
A
We
want
to
kind
of
take
a
look
at
at
those
at
those
as
prime
candidates
and
then,
if
you
have
like
id
fields,
so
if
you
don't
have
traditional
dim
and
fact
tables,
but
if
you
have
keys
that
are
being
joined
on
by
different
tables
right,
we
want
to
and
the
maybe
you
want
to
increase
in
performance
on
those
joins.
You
want
to
use
that
you
want
to
z
order
by
that
key,
that's
going
to
make
the
operation
the
execution
morph
efficient
and
then
very
commonly.
A
A
All
right,
so
we
talked
about
high
cardinality.
What
what
exactly?
Does
that
mean?
How
should
I
be
thinking
about
this?
Where
does
partitioning
come
into
to
play
like
how?
How
should
I
balance
these
things?
So
essentially,
this
is
how
I
I
see
the
world,
and
I
provide
a
little
bit
examples
here-
to
be
able
to
visualize
and
understand,
what's
going
on
here,
so
essentially
with
partitioning
what
you
most
commonly
people
use
date.
A
That
is
a
very,
very
good
candidate,
because,
if
you
think
about
how
data
comes
in
usually
there's
a
cadence
to
date,
right
you'll
get
n
number
of
equal
records
per
per
period
of
time,
and
so
data
is
a
very
common
thing
to
do.
But
let's
say
you
have
an
instance
where
date
isn't
an
option
or
something
like
that.
Essentially
partitioning
is
good
when
we
create
about
a
gigabyte
partitions,
a
piece
per
partition,
so
we
want
regular
cardinality.
A
We
want
something
in
the
middle
for
partitioning
effectiveness
and
you
could
see
some
examples
there
of
common
repeatable
data,
but
essentially
what
you're
aiming
for
is
to
create
nice,
even
chunks
of
data
of
about
a
gigabyte
in
size
and
that's
where
you're
going
to
get
the
most
effectiveness
for
partitioning
z
order,
on
the
other
hand,
is,
is
a
kind
of
a
different
case.
What
you
want
to
do
to
optimize
for
z
order
effectiveness
is
high,
cardinality
columns
and,
and
in
particular
you
know,
items
that
will
increase
or
decrease.
A
They
have
ranges
you
can
order
them.
That's
where
you're
gonna
get
the
most
effect,
because
it's
you're,
essentially
with
data
skipping,
which
is
the
key
to
performance
on
these
types
of
queries,
takes
advantage
of
min
max's
on
on
numericals,
so
z
order
will
help
with
the
high
cardinality
columns
and
then
partitioning.
We
want
regular
cardinality
and
in
general,
this
is
how
how
things
work
and
how
you
can
think
about
those
two
constructs.
A
All
right
so
now
we're
going
into
some
some
parameters.
There
are
some
new
things
that
came
out
in
spark
3..
One
of
my
favorite
is
adaptive,
query
execution
and
I'm
going
to
go
through
a
couple,
a
few
of
the
optimizations
and
how
they
work
in
order
to
explain
a
little
bit
about
what's
going
behind
on
behind
the
scenes
and
to
kind
of
let
you
know
how
why
you
should
be
turning
these
things
on.
A
If
you
have
those
types
of
use
cases,
so
adaptive
query
execution,
you
should
turn
it
on
first
off
right,
so
you
want
to
enable
that
this
is
default
on
databricks,
runtime,
7.3,
plus
awesome,
but
just
in
case
you're
using
a
different
runtime.
You
know
if
it's
previous
to
7.3,
it
has
to
be
on
7.0
plus,
because
this
is
spark
3..
A
What
we
want
to
do
next
is
there's
a
new
feature
in
adaptive.
Query
execution
called
coreless
partitions
if
you've
ever
had
to
mess
with
the
sql
partitions
in
order
to
tune
performance
on
your
job.
You
know
that
you
kind
of
have
to
query
the
cluster
to
figure
out
how
many
cores
you
have,
and
then
you
want
a
multiple
of
that
there's
a
lot
of
math
that
goes
on,
but
this
can
be
made
significantly
simpler.
A
Essentially,
what
happens
here
is
that
you
know
the
optimizer
will
attempt
to
coalesce
the
sql
partitions
down
in
order
for
it
to
make
sense
for
the
execution
of
that
query.
So
you
know
I
I
just
set
this
parameter
to
turn
it
on,
because
then
I
just
let
adaptive
query
execution
managed
my
sql
partitions,
which
is
excellent.
A
So
if
you
know
you
might
have
skewey
data
where
there's
more
data
on
one
side
of
a
join
than
the
other,
when
you
want
to
do
is
play
around
with
the
adaptive
sku
join
configuration,
I
have
found
that
turning
this
on
really
helps
with
queries
that
would
have
skew
in
the
data
and
it's
less
for
me
to
manage.
I
can
just
turn
it
on
and
if
it's
there
aqe
will
just
kind
of
manage
it.
If
not,
it
kind
of
really
doesn't
hurt
very
much
for
the
testing
that
I
found.
A
So
I
suggest
turn
it
on
and
and
take
advantage
of
that.
It's
also
another
feature
that
I
really
like,
especially
if
you,
if
you
use
different
types
of
of
cluster
instance
types
and
I'm
going
to
be
explaining
a
little
bit
of
this
in
the
demo
section,
but
essentially
the
local
shuffle
reader
instead
of
communicating
over
the
network
on
shuffle
if
the
cores
are
within
the
same
box,
it'll
attempt
to
use
the
local
shuffle
reader
instead
of
going
over
the
network
but
actually
improves
performance
when
you
do
have
shuffle
in
your
jobs.
A
So
I
recommend
turning
that
on
and
then
the
join
threshold,
so
very
commonly
the
broadcast
hash
join
is
one
of
the
it
turns
out
to
be
very,
very
performant
in
in
a
lot
of
our
queries,
so
the
key
to
that
is.
We
have
to
broadcast
the
smaller
side
right.
There's
a
big
table.
Think
of
like
dim
fact,
tables
right
fact:
table
large
dim
table
small
a
lot
of
times
that
dimension
table
could
be
larger
than
the
default
default.
Here
is
10
megabytes,
but
the
cluster
actually
has
enough
memory
to
broadcast
more.
A
So
if
we
increase
this
value,
we
can
actually
increase
the
likelihood
that
the
that
the
plan
will
broadcast
that
smaller
table.
So
I
recommend
bumping
this
up
to
100
megabytes.
You
know
you
could
play
around
with
different
configurations,
but
what
I
found
in
most
use
cases
in
general,
100
megabytes
is
a
good
place
to
start
and
then
finally,
not
to
prefer
sort,
merge
join
start.
Merge
join
was
great,
it
kind
of
for
big
data.
A
It
provides
a
an
excellent
execution
mechanism
to
kind
of
manage
that
at
execution
time,
unfortunately,
most
often
than
not
with
a
lot
of
the
most
common
queries.
What
we
find
is
that
we
don't
actually
need
sort
merge,
join.
We
actually
need
like
a
broadcast
hash,
join
or
something
like
that,
and
we
don't
want
it
to
use
that.
A
So
here
we're
going
to
say:
hey,
you
know
what
don't
prefer
sort
merge,
join
and
see
how
that
works
and
I'll
kind
of
show
you
a
little
bit
about
how
these
parameters
can
help
out
a
little
bit
later,
all
right
now,
the
delta
configs
first
things
first
turn
the
cache
on,
especially
if
you're
doing
etl
or,
if
you're
doing
queries
this.
This
is
enabled
by
default
on
cluster
types
and
data,
bricks
that
say,
delta
cache
enabled,
but
some
cluster
instant
types.
A
What
I'm
going
to
talk
about
a
little
bit
when
we
get
to
the
demo,
don't
have
this
turned
on
by
default,
but
they
actually
do
have
locally
attached
fast
disks,
which
delta
cache
really
the
proponent
of
it
is
the
locally
attached
fast
disks.
So,
if
you're
not
using,
if
you
are
using
a
cluster
instance,
that
does
have
that,
but
it's
not
in
the
delta
cache
accelerated
turn
this
on.
A
You
will
get
much
better
performance
and
then
you
know
the
key
here
is
the
faster,
the
disk,
the
better
the
performance
you
can
enable
it
on
clusters
that
don't
have
fast
disks.
So
if,
for
some
reason
as
a
hard
drive
or
something
it
still
is
a
little
bit
more
performant,
but
you
know
on
nvmes,
you're
gonna
get
much
better
performance,
all
right,
delta
cash
staleness
this
one's
interesting,
a
lot
of
sometimes
because
the
delta
cache
is
loaded
right.
A
This
can
actually
hurt
execution
time
and
oftentimes.
If
you
think
about
etl
orchestration,
you
will
have
you
won't
be
getting
your
data
refreshed
very
frequently.
So
if
you
have
like
a
batch
job
where
maybe
your
data
is
getting
refreshed
every
day
or
every
six
hours
or
maybe
every
hour,
you
don't
need
to
check
the
cash
that
frequency
that
frequently,
you
don't
need
to
call
back
to
the
files,
so
you
can
actually
increase
performance
on
bi
use
cases
or
analytics
use
cases
by
increasing,
what's
called
the
staleness
limit.
A
You
can
turn
it
on
on
7.2,
with
the
delta
config
checkpoint,
write
stats
as
json.
Essentially,
it
eliminates
a
deserialization
step
in
the
checkpoint
process,
which
inc
d
speeding
up
the
latency
on
on
short
queries.
So
definitely
turn
this
on
in
bi
use
cases
and
make
sure
when
you're
writing
delta
tables
you're
using
7.3
plus
or
you're
going
to
have
to
mess
with
different
levels
of
support.
For
this,
I
just
recommend
using
7.3.
A
Plus,
okay,
so
now
what
happens?
If
adaptive
query
execution
isn't
getting
the
hint
right
there
might
be?
I
want
to
use,
I
wanted
it
to
prefer
broadcast
hash
join,
but
for
some
odd
reason
in
execution
time,
it's
doing
it's
doing
sort
merge,
drawing
I'm
gonna,
give
it
a
hint
this
time.
So
we're
gonna
strongly
suggest
hey,
take
a
look
at
this
and
you
could
do
that
by
these.
You
know.
Putting
these
query
hints
inside
your
sql
statements,
broadcast
hash
join
or
nested
loop
join.
A
A
If
your
data
is
skewed
so
kind
of
be
careful
there
and
then
sort
merge
join
right,
it's
the
most
robust
one,
but
it
kind
of
you
have
the
the
shuffle
and
the
sort
and
it
can
be
slower
when
you
know
you
have
small
data
or
it's
not.
Both
sides
aren't
large
data
and
then
finally
cartesian.
I
don't
recommend
this
unless
you
have
a
use
case
that
requires
it.
You
know
I
can
think
of
a
few,
but
but
generally
you
want
to
stay
away
from
that.
A
You
know
when
you're
joining
data
all
right,
so
we've
talked
about
a
lot
right.
Well,
let's
think
about
the
delta
architecture
pattern
that
we
have
in
front
of
us
and
kind
of
align
this
these
table
structures,
and
these
tips
that
we've
kind
of
been
been
talking
about.
Now,
how
do
I
structure
my
layers
of
my
delta
architecture,
and
what
I
can
suggest
is:
is
these
tips
for
your
bronze
tables,
and
this
is
raw
data.
This
is
kind
of
data,
that's
coming
in
from
the
source
system.
A
You
want
to
land
it
raw,
don't
change
anything
and
turn
off
stats
collection,
because
at
this
point
in
time
it's
kind
of
just
extra
overhead
on
on
data
processing
and
we're
not
really
querying
bronze.
Very
often
we're
actually
just
using
it
as
a
staging
area,
so
let's
kind
of
diminish
what
we
have
to
do
there,
but
when
we
get
to
silver
or
the
next
layer,
filtered
or
clean
layer.
This
is
when
we
want
to
start
moving
data
around.
A
You
want
to
make
sure
turn
turn
on
optimized
rights
right
at
the
cluster
level.
That
way,
all
of
our
insert,
update
and
merge
operations
are
are
efficient,
and
then
we
want
to
optimize
and
z
order
by
the
keys
between
the
bronze
layer
and
the
silver
layer
and
on
our
silver
tables,
and
this
is
going
to
help
with
our
etl
processing
right.
A
Again
turn
on
optimize
writes
because
we
want
the
merge
from
silver
to
gold
to
be
efficient
as
possible.
We
want
to
enable
delta
cache
and
we
want
as
fast
as
disk
as
we
have
available
to
us
and
then
on
these
on
these
clusters.
Let's
turn
that
staleness
limit
up
to
align
with
our
our
etl
orchestration.
So
if
it's
every
hour,
you
could
do
one
one
hour.
It's
every
day
you
could
do
one
day.
This
is
going
to
help
manage
that
delta
cache
and
make
that
experience
more
performant
for
our
analysts
at
runtime.
A
A
The
key
to
fast
update,
merge
operations
is
to
rewrite
the
least
number
of
files
optimize
rights
help
here.
You
can
also
configure
the
max
file
size
for
optimize,
which
kind
of
helps
with
rewriting
the
least
amount
of
data,
so
we
recommend
32.
You
can
go
between
16
and
128
depending
upon
how
your
operations
are
working.
A
The
key
to
fast
select
queries
is
delta,
cache,
optimize
and
z
order
and
adaptive
query
execution.
So
you
want
to
make
sure
you
take
advantage
of
those
configuration
items
and
settings.
Finally,
the
hilbert
curve
for
optimize.
This
is
something
that's
new,
so
it's
it's
kind
of
a
little
bleeding
edge.
A
I
don't
you
might
not
want
to
put
this
in
production,
but
you
might
want
to
test
it
out
in
your
pre-production
systems
to
see
what
kind
of
performance
you're
getting
it's
the
the
the
multi-dimensional
curves
that
we
use
right
now
is
the
z
order
curve,
and
if
you
look
at
it
in
in
dimensional
space,
it
kind
of
looks
like
a
z.
That's
why
it's
called
the
order.
Hillberg
curve
is
actually
like
a:
u
it's
a
different
curve,
different
math.
A
A
Okay!
So
now,
let's
take
a
look
at
some
code
and
actually
see
this
stuff.
If
it
really
works,
do
these
tips
actually
work,
let's
check
it
out
all
right.
Thank
you.
Everyone!
So
now
we're
going
to
take
a
look
at
actually
in
practice.
How
are
these
configurations
really
going
to
help
me
with
my
etl
performance
and
how
can
I
nbi
performance,
and
what
can
I
be
doing?
Can
I
see
this
in
in
real
time?
A
Absolutely
so,
let's
take
a
look
at
a
common
use
case
that
I
I
see
in
the
field
I'm
going
to
be
using
tpc-h
data.
It's
it's
kind
of
just
benchmarking,
test
data.
It's
really
just
you
know
synthetic
data.
It's
not
really
meant
to
be.
You
know
real,
but
what
I'm
gonna
be
doing
here
is
I'm
gonna,
be
loading
that
data
into
a
database
in
delta
lake
tables
and
I'm
gonna
be
performing
etl
operations
so
insert
operations
and
then
we'll
also
be
performing
some
queries.
A
So
here
I'm
going
to
be
creating
that
database
in
the
in
the
meta
store
and
then
I'm
going
to
be
creating
my
tables.
So
here
are
all
the
tables
metadata
and
I'm
going
to
be
using
a
delta
on
those
tables
and
then
I'm
what
I'm
doing
here
is
just
looking
at
all
of
my
files,
I'm
going
to
be
collecting
a
list
of
all
the
tables.
A
I
have
a
simple
function
here
to
help
me
insert
the
data
and
what
I'm
doing
is
just
kind
of
reading
the
file
in
and
then
creating
a
temp
view
on
it
and
then
inserting
that
data
into
the
table,
and
I
also
have
a
function
for
optimizing.
All
of
my
tables
I'll
show
you
a
little
bit
about
that
in
the
in
in
the
following
steps.
A
A
A
Instead
of
having
to
rewrite
code,
which
is
very
key
for
when
you
think
about,
you
know
how
to
manage
your
operations
from
day
to
day,
so
I'm
just
going
to
create
a
simple
table
and
it
each
each
column
here
is
the
table
name,
and
then
these
are
the
keys
of
that
table.
The
join
keys,
I'm
just
going
to
run
through
those
and
I'm
going
to
optimize
them.
A
You
can
see
here
it
took
about
17
minutes
to
perform
on
this
cluster
and
then,
after
my
optimizing
z
order,
my
query
went
down
to
about
1.4
seconds.
So
if
you
think
about
before
the
optimizing
z
order,
I
was
about
double
the
time
than
it
was
to
perform
it
after
the
optimizing
z
order.
So
that's
the
type
of
effect
you
can
have
on
just
leveraging
optimizing
z
order.
Now
you
might
be
thinking
to
yourself.
Is
that
the
really
the
bus
that
it
can
get?
Absolutely
not?
A
We
need
to
optimize
all
those
configurations
I
haven't
done
any
of
those
here.
This
is
just
a
simple
default
configuration
for
for
data
breaks
right.
So
now
what
happens?
If
I
apply
some
of
those
those
tweaks
to
my
configurations,
so
here
I'm
creating
a
new
database
same
step
as
before
so
I'll
kind
of
just
skip
over
a
lot
of
the
intro
material.
But
essentially
this
one
is
going
to
be
optimized.
A
So
what
are
we
doing
to
optimize
it?
I'm
using
an
instance
type
called
c5d9xl,
so
these
are
compute
optimized,
but
they
actually
include
locally
attached
fast
disks
and
instead
of
doing
auto
scale,
I'm
going
to
do
static,
I'm
going
to
set
8
workers
to
this
cluster
and
then
I'm
going
to
be
activating
a
few
of
the
optimizations
that
I
talked
about
today,
as
well
as
some
of
the
optimizations
around
optimize
itself.
A
A
This
time
it
took
1.03
minutes.
Let's
just
go
back.
How
long
did
that
take
on
the
previous
cluster
18
minutes?
It's
like
significant
performance
improvement.
But
what
does
this
mean
for
performance?
Is
one
thing
I
I
might
have
had
better
nodes.
You
know:
is
this
really
accounting
for
cost
performance?
Let's
take
a
look
at
something
the
instance
types
I
used
for
the
previous
step
was
i3
xl
and
that's
kind
of
the
information
about
it.
A
What
you
want
to
know
is
it's
about
3.312
dollars
per
hour
right
if
I
were
the
same
job
that
took
nine
twenty,
we're
just
running
up
to
twenty
minutes
and
two
minutes
at
this
point,
even
though
it
really
wasn't
two,
if
I
look
at
cost
performance
right
running
it
on
the
better
cluster
type
actually
is
cheaper.
If
you
consider
execution
time
because
remember
folks
in
the
cloud,
it's
all
you
pay,
it's
everything
is
metered,
so
you
pay
infrastructure
over
time.
A
A
So,
let's
actually
optimize
and
z
order,
those
those
by
those
keys
and
see
if
we
can
improve
performance
a
little
bit
and
what
we
get
is
performance
going
down
to
about.
You
know
these
aren't
these
are
nanoseconds,
so
it's
less
than
a
second
but
332
nanoseconds.
So
we're
talking
a
significant
performance
improvement.
A
So
this
is
great,
so
we
covered
kind
of
etl
performance,
cost,
perform
cost
and
time
performance.
Now,
let's
take
a
look
at
some
sql
analytics
use
cases
like
actually
querying
the
data
itself
and
some
of
those
optimizations.
So
here
what
I'm
doing
is
I'm
using
those
optimized
tables
and
I'm
saying,
select
the
data.
So
this
is
before
doing
any
of
those
configuration
items
that
we
talked
about
and
first
run
on
this.
It
took
about
six
seconds
so
6.6279
on
on
this
query.
So
this
is
just
a
cold
boot.
It's
not
using
delta
cash.
A
I
kind
of
remove
removed
the
cash
beforehand
and
that's
how
long
it
takes
not
not
bad.
Can
we
get
any
better,
so
this
is
an
optimized
cluster,
so
using
those
optimized
delta
lake
files
that
I
generated
in
the
etl
optimize
step.
Now
I
want
to
analyze
that
data
right,
so
here
I'm
using
I'm
not
using
the
c5ds,
I'm
using
the
i3xl,
it's
a
very
important
thing
to
keep
in
mind
and
then
I'm
doing
all
those
other
configurations
so
before
the
delta
cache
is
even
loaded.
I
have
about
a
second.
A
This
query
took
about
one
second
to
execute,
which
is
pretty
good
if
you're
thinking
about
a
cold
boot
right.
So
that's
what
those
those
configurations
really
gain.
It's
really
good
instantaneous,
query
execution
and
then
now
what
happens?
If
I
leverage
some
of
the
delta
cache
wow,
so
I'm
getting
down
to
the
through.
You
know
the
300s
again
like
I
did
before
so.
A
The
key
here
is
that
the
delta
cache
is
really
really
important
and
even
if
you're,
not
using
a
a
bigger
instance
type,
even
if
you
have
locally
attached
fast
disks,
which
the
delta
cache
accelerated
types
have
that
you're
still
going
to
get
good
performance.
So
that's
why
I
always
recommend
to
use
the
right
instance
type
for
each
type
of
operation
when
we're
talking
etl
and
optimize
compute
optimized
instances
with
locally
attached
disk
are
really
good.