►
Description
For this tech chat, we will discuss a popular data warehousing fundamental - surrogate keys. As we had discussed in various other Delta Lake tech talks, the reliability brought to data lakes by Delta Lake has brought a resurgence of many of the data warehousing fundamentals such as Change Data Capture in data lakes. Surrogate keys are unique and lack any business context so they can stand the test of time when joining domain (or dimensional) and fact data. This can be difficult in single-node systems and can be even more complex for distributed systems. In this session, we will discuss the history and value of surrogate keys and what are the requirements for good strategies to implement this data warehousing fundamental into your Delta Lake.
You can find the notebooks for this video at: https://github.com/databricks/tech-talks/tree/master/2020-08-25%20%7C%20Generating%20Surrogate%20Keys%20for%20your%20Data%20Lakehouse%20with%20Spark%20SQL%20and%20Delta%20Lake 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
B
Exactly
exactly,
and
so
all
right.
Well,
I
think
it's
a
good
time
for
us
to
start
so
so
instead
we'll
start
shortly.
Let
me
go
ahead
and
just
remove
that-
and
this
is
this-
is
the
how
nicely
professional
edition
we
have
here
for
us
there
we
go
boom
all
right.
So
before
we
dive
into
some
of
the
logistics,
I
did
want
to
again
remind
everybody
who's
either
here
on
zoom.
Thank
you
for
joining.
B
B
A
Yeah,
certainly:
hey
hey
everyone,
so
I'm
I'm
douglas
moore.
I'm
a
solutions
architect
here
at
databricks,
I've
been
in
data
warehouses,
data
modeling
data
architecture,
etl
for
20,
some
years
across
oltp
olap
systems,
big
data
for
last
nine
ten
years
as
well,
so
very
happy
to
be
joining
you
all
from
outside
of
boston.
B
Cool
thanks
very
much
doug.
If
you
haven't
already
heard
my
name
is
denny
lee,
I'm
a
I'm
based
out
of
seattle,
washington
actually
specifically
kirkland
washington,
which
is
close
to
redmond.
I
love
microsoft
and
that
segway
basically
allows
me
to
explain
that
I'm
a
former
sql
server
guy,
I
used
to
be
part
of
the
sql
server
engineering
team
back
in
sql
server
2005,
so
I've
been
working
with.
B
In
fact,
I
was
the
sql
customer
advisory
team
data,
warehousing
and
bi
lead,
so
I
had
been
working
with
some
of
the
larger,
if
not
the
largest
enterprise
data,
warehousing
customers
with
sql
server,
and
so,
as
you
can
tell
both
doug-
and
I
love
talking
about
this
type
of
stuff,
because
our
backgrounds
are
very
much
within
the
context
of
data
warehousing.
So
even
though
we
we
are
come
from
very
different
prospects
of
different
angles,
we
both
went
ahead
and
decided.
B
Hey
spark
makes
a
ton
of
sense
right
so
that
that's
the
the
context
here
so
hence
today's
session
is
about
generating
certain
cases,
a
la
a
very
fundamental
data,
warehousing
concept
for
your
data
lake
house
with
spark
sql
and
delta
lake,
because
we
obviously
are
fans
of
well
spark
sql
and
delta
lag
all
right.
So
before
we
dive
into
a
little
bit
of
what
the
some
of
the
backgrounds
some
of
the
concepts,
let
me
go
ahead
and
get
some
logistics
out
of
the
way
number
one
like
I
said
before.
B
Please
use
the
chat
or
use
the
q
a
panel
to
ask
us
questions
right
now.
We're
going
to
keep
the
chat
panels
open
so
by
all
means
chime
in
so
we
noticed,
for
example,
andy
had
called
out
the
fact
that
we're
using
row
number
for
certain
keys.
We
are
in
fact
going
to
call
using
that
as
an
example
all
right,
so
a
couple
other
quick
things
to
showcase.
There
is
o'reilly's
learning
spark
second
edition
for
free,
okay.
This
is
a
a
book
called
learning.
Spark
second
edition.
B
I
happen
to
be
one
of
the
co-authors
of
it
as
well.
It
does
cover
apache
spark
3.0
and
it
does
cover
apache
spark
delta
lake
mlflow
and
koalas,
and
I'm
going
to
leave
it
up
here
for
a
couple
seconds,
but
it's
d
bricks.co
slash,
get
dash
ebook.
You
can
download
the
book
for
free,
okay,
so
by
all
means
you
know
download
the
book
all
right
and
if
there's
any
errors
or
issues
with
it,
there
is
an
errata
by
all
means
go
ahead
and
chime
in
and
probably
it's
my
fault.
B
So
I
will
call
that
out.
So
all
right.
We
also
recently
published
a
vldb
paper
on
you
can
see
it
on
delta.io.
It's
called
delta
lake,
high
performance,
acid
table
storage
over
cloud
object
stores.
So
if
you
go
to
delta
dot,
io
there's
a
little
black
banner
right
up
top,
you
can
just
click
on
it
and
download
the
the
vldb
paper
for
free.
So
if
you
want
to
understand
some
of
the
technical
and
academic
merits
behind,
why
we
built
delta
lake,
this
is
basically
the
paper
to
cover
it
all
right.
B
B
A
B
A
And
then
you
know
we'll
try
to
stuff
out
if,
if
these
are
acceptable
approaches
or
not
so
the
the
history
surrogate
key,
so
you
know
think
of
data
warehouses,
star
schemas,
you
have
fact
tables
and
dimension
tables.
You
know,
data
warehouses
are
are
designed
to
capture.
You
know
those
dimensions
could
be
your
customers,
your
products,
these
things
change
over
time.
They
have
a
natural
key.
Like
you
know,
douglas
moore
might
be
my
name.
A
I
might
have
an
email
address,
but
these
natural
keys
change
over
time
and
it's
very
important
in
data
warehouse
to
capture
these
changes
over
time.
So
you
can
see
what
what
the
world
looked
like
or
what
your
business
looked
like
as
of
a
given
point
in
time,
so
a
surrogate
key
is
to
identify
a
very
distinct
instance
of
an
entity
in
that
dimension
table
at
a
given
point
in
time.
So
you
need
a
a
unique
identifier.
You
know.
Sometimes
it's
called
a
synthetic
key
system,
generator
key,
a
sequence
number
so
on
and
so
forth.
A
So
so
there
there's
a
lot
of
names
for
this
and
you
know
ralph
campbell.
You
know
he
called
out
that
it's
it's
incredibly
important,
a
not
to
use
the
natural
key,
but
to
create
a
synthetic
key.
He
described,
creating
an
integer
and
and
using
a
sequence
for
that.
You
know
one
that
increments.
Now
I
you
know,
I
I
think
we
agree
on
that
danny
with
one
small
caveat:
okay,.
A
B
A
A
B
No
problem,
so
I
hope
you
don't
mind
I'll,
just
ask
you
to
scroll
up
and
down
oh
scroll
down.
Don't
worry,
I'm
not
gonna,
but
one
thing
I
did
want
to
add
to
doug's
point
where
I
think
we
are
in
agreement
is
actually
the
part
of
the
reason
why,
especially
in
the
old
days,
okay,
with
the
with
databases
where
we
actually
wanted
ids
is
also
because
it
took
up
less
memory
space
right
so
especially
when
it
came
to
data
warehousing
or
laptop
queries.
B
You're
typically
going
to
be
duplicating,
for
example,
that
value
right,
so
you're
going
to
duplicate
the
circuit
key.
So,
instead
of
actually
duplicating,
let's
just
say,
douglas
moore
or
danny
lee,
you
duplicated
one
duplicate
two,
so
in
other
words
you're
only
working
with
four
bytes
right.
So
it's
it's
a
lot
less
memory
space
that
was
utilized
and
so
especially
when
you
had
a
lot
servers
that
had
a
lot
less
memory
and
frankly,
we
weren't
being
as
efficient
and
how
we
used
that
memory
as
well.
B
So
it
was
actually
crucial
that,
instead
of
actually
trying,
if
we're
going
to
be
duplicating
data
that
we
actually
were
duplicating
int
as
opposed
to
var
cars
or
strings
or
things
of
that
nature,
and
that's
ultimately
why
you
know
we
also
wanted
not
ultimately
sorry,
but
that
was
an
important
component
of
why
surrogate
keys
were
like
crucial
in
any
of
these,
whether
you're
doing
kimball
or
any
of
the
other
data
data
warehousing
database
design
you.
Basically,
we
all
agreed
on
that
concept.
At
least
early
on.
I
should
say.
B
Exactly
all
right
perfect!
So
for
those
of
you
who
are
wondering
what
I
have
here
by
the
way,
I
am
a
fan
of
stargate
sg1,
okay
and
I've
recently
restarted
watching
the
whole
series
all
over
again
and
so.
Hence
now
I'm
gonna
have
references
to
stargate,
hd1,
towery
and
samantha
carter
so
and
daniel
jackson's
in
here
too,
don't
worry
so
I
apologize
in
advance,
not
really
okay.
B
So
all
right,
why
are
circuit
keys
hard
for
even
when
you're
working
with
single
node
or
traditional
data
warehousing
right
so
partially,
I'm
just
gonna
be
reading
this
okay,
so
in
databases
you're,
incrementally
you're
gonna
be
using
some
increment,
some
identifier,
some
value
that
you're
just
basically
generates
as
unique
as
a
unique
circuit
key.
Let's
just
use
identity
for
now,
just
because
I'm
a
sql
server
guy,
so
yeah
there
you
go!
So
it's
a
number
that
goes
up.
Okay
right
it!
B
B
If
you're,
if
you
were
to
basically
tie
both
the
shard
number
and
the
unique
value
you
have,
but
the
point
is
that
it's
even
hard
when
you're
only
dealing
with
one
note,
let
alone
dealing
with
multiple
notes
right.
So
let's
talk
about
exactly
what
doug
was
talking
about
right
in
the
beginning.
What's
your
natural
key
versus?
What's
your
certain
key?
Well,
your
natural
key
in
this
case
I'm
using
is
samantha
carter,
okay,
she's
based
on
earth.
B
Okay
for
those
who
are
into
sg-1
it's
the
tauri
and
by
the
way
I
just
gotta
chime
in
about.
If
provide
these
notebooks,
we
won't
be
private,
we'll
we'll
provide
the
notebooks
actually
as
part
of
the
databricks
tech
talk
url.
So
as
soon
as
we
upload
the
notebook
to
the
databricks
tech
talks,
which
isn't
github,
we'll
update
youtube
with
actually
the
link
okay.
So
just
that
we
haven't
had
a
chance
to
do
that.
Yet,
okay,
but
so
the
context
anyways
is
samantha
carter.
B
The
natural
key,
her
location
is
earth
which
is
tari
as
if
you
were
to
start
at
sg1
and
she's
part
of
the
sg1
team,
okay,
cool
all
right.
So
after
15
years
of
continually
saving
the
milky
way
galaxy
because
she's,
a
rock
star,
okay,
now
she's
leading
the
atlantis
team
in
the
pegasus
galaxy,
so
the
natural
key
for
her
is
still
samantha
carter.
That's
her
name
right,
but
you're
gonna
need
to
change
some
of
the
information
based
on
her
demographics,
and
so
let's
go
ahead
and
roll
that
down
all
right.
B
So
in
this
example
right,
the
identifier
is
her
the
primary
key
well
and
name
actually,
technically
just
the
name,
but
we're
now
needing
to
update
carter's
demographic
information
by
the
way.
The
reason
I'm
using
her
last
name
by
the
way
is
because,
throughout
the
show,
that's
exactly
how
they
did
it.
They
always
referred
as
carter,
not
sam.
Well
sometimes
they
were
sam,
but
really
was
carter.
So
anyways,
I
digress
massively
here.
B
The
context
is
that,
okay,
if
you
were
to
go
ahead
and
forget
the
third
line
for
now,
okay,
it
was
just
one
note:
the
circuit
is
100
for
samantha
carter,
the
team,
sg-1
location,
tari
and
the
point
of
origin.
That
actually
happens
to
be
the
tauri
symbol.
That's
referring
to
egyptian
symbology
throughout
the
show.
Sorry,
sergey
key
102..
B
B
If
you
had
charted
databases,
so
forget,
forget
about
spark
or
other
distributed
systems,
just
talk
about
sharda
database
and
pretend
that
you
actually
got
the
rece
two
different
databases
received
the
location,
the
new
demographic
information,
the
new
location,
information
for
carter,
and
it
wasn't
distributed
properly
either
due
to
design
scale.
The
multiverse
and
the
reason
I'm
using
multiverse
is
because
multiverse
was
a
reference
multiple
times
in
sci-fi
shows
especially
sg1.
So
again,
apologies.
B
What
would
end
up
happening
is
that
you
actually
have
two
different
circuit
keys
for
carter,
okay,
and
is
that
a
good
thing
not
really
because
what
ends
up
happening
is
that
on
chart
three?
Basically,
we
have
now
1182
for
also
carter
for
her
team
at
atlantis
and
the
locations
to
be
in
the
pegasus
galaxy.
So
this
is
undesirable
because
now
what
happens?
Is
that,
in
all
of
your
fact,
data
you're
gonna
actually
have
two
circuit
keys:
okay,
you're,
not
gonna,
you're
gonna
have
102
and
1182..
B
Now
this
is
an
overly
simplistic
problem
and,
frankly,
most
of
the
time
you're
not
going
to
design
it
this
way.
But
the
context
nevertheless,
is
the
same
that
you
you're
trying
to
explain
or
provide
the
context
that
if
you
don't
have
a
some
centralized
way
of
controlling
how
this
data
is
generated
or
some
agreed
upon
mechanism
you're
gonna
result
in
different
values:
okay,
which
is
not
what
you
want.
So
that's
where
we
say.
B
Perhaps
let's
centralize
it
all:
okay,
well
one
it
at
least
where
I,
where
I
come
from,
we
actually
a
bunch
of
us
did
try
that
approach
initially
on
when
we
were
building
data,
warehouses
right
saying:
can
we
go
ahead
and
use
the
central
server,
which
is
the
only
job,
was
a
five
unique
sks
like
certain
keys?
Okay.
So,
for
example,
those
of
you
with
a
very
very
much
an
oltp
background,
they're
going
like
yeah.
A
B
Just
put
high
transactional
throughput
on
my
transactional
database,
its
only
job
is
to
basically
generate
these
certain
keys,
so
then
boom
there,
you
go
right.
The
problem
is
that,
as
the
database
transactions
increase,
the
higher
the
load
to
generate
the
circuit
keys
and
then
even
if
you
can
handle
the
creation
aspect
of
things,
it
became
extremely
resource
intensive
when
you
had
that
much
data
trying
to
do
a
lookup.
B
On
that
one
note
now,
there
were
often
workarounds
to
this
issue
by
saying:
okay,
we'll
send
out
once
you've
generated
you'll,
send
out,
like
you
sort
of
like
a
broadcast
hash
right,
you
would
broadcast
the
the
the
data
to
all
the
the
shards
of
the
worker
notes.
So,
but
then
there
was
this
issue
of
constantly
ensuring
that
everything
was
up
to
date,
and
so
that
also
became
its
own
pro
set
of
problems.
B
B
B
It's
also
known
as
the
birthday
problem
right,
and
so
basically,
if
you're,
for
example,
argument
just
talking
about
32-bit
hash
on
100,
000
users,
let's
just
say
well,
there's
actually
a
68.7879
chance
that
you're
going
to
result
in
a
hash
collision,
okay,
and
so
that's
where
things
became
problematic
when
using
caches.
So
that's
why
we
said:
okay.
Well,
let's
just
go
ahead
and
knock
up
the
rate.
So,
for
example,
when
we
start
saying
64-bit
hashes
right
when
you're
talking
about
100
million
users,
actually
the
the
collision
chance
of
collision
actually
dropped
at
0.027.
B
B
Okay,
how
do
I
solve
that
problem
now,
so
we
have
a
potential
clue
by
using
hashes,
but
then
obviously
we're
going
to
have
to
increase
the
the
bit
the
hash
size
in
order
to
be
able
to
handle
that,
and
so
this
is
a
pretty
good
segue
for
us
to
talk
about.
What's
next,
which
is
certain
keys,
are
even
harder
for
distributed
systems,
because
that's
what
we're
going
to
have
to
go
do
now.
So
if
we
can
just
scroll
a
little
bit
down.
A
B
A
One
one
thing
that
occurs
to
me
here
in
this
example:
you're
hashing
on
samantha
carter
and
sg1
right.
But
if
you
go
back
here,
you
know
that
you
know
based
on
the
location,
point
of
origin
and
other
attributes
and
and
changes
in
these.
You
want
an
additional
surrogate
key
right
right.
So
you
know
with
each
of
these
attributes
here,
you're
going
to
have
to
add
that
to
your
hash
function,
absolutely
and
then,
as
say,
the
width
of
your
dimension
table
increases
over
time.
B
B
One
thousand
percent-
there's
there's
definitely
an
issue
where,
if
you
are
going
to
build
hashes
based
on
all
of
the
democratic
information,
then
the
problem
you're
going
to
run
into
obviously,
is
that
you
constantly
have
to
maintain
the
code.
If
you
then
decide
that
you
actually
want
to
do
a
hash
just
on
a
smaller
set
of
data.
The
problem
is
that
they're,
using
this
example,
there
possibly
is
more
than
there's
in
the.
B
In
my
reference
in
my
mind,
there's
only
one
samantha
carter,
but
how
about,
if
there's
another
samantha
carter
on
the
that
happens
to
join
the
sg-1
team
or
sgc,
and
then
also,
but
it's
actually
a
different
person
right.
So
then
you're
gonna
create
a
collision
because
those
two
people
since
I'm
using
the
same
name,
the
natural
key
samantha
carter
and
same
team
as
g1
they're,
but
we're
going
to
result
in
the
same
hash
value,
even
though
they're
actually
different
people.
So
it's
that
balance
between
the
two
exactly.
A
B
Right
so
harder
for
distributed
systems
because
again
goes
back
to
the
idea
that
we
couldn't
really
do
centralization,
or
at
least
that
was
very
problematic.
So
basically,
the
idea
is
that
the
more
unique
the
identifiers
you
have
the
result
in
higher
probability
of
hash
collisions,
because
the
fact
is
that
you
have
that
much
data
to
work
with,
so
that
meant
you
had
to
increase
the
hash
size
or
use
a
different
algorithm,
for
example,
in
patchy
spark.
You
have
md5,
which
is
128
bit.
B
You
have
sha-2,
which
ranges
from
shaw
224
to
sha
512,
which
are
supported
right,
but,
of
course,
the
larger
or
the
more
complicated
the
hash,
the
more
the
potential
impact
exactly
like
doug
was
talking
about
you.
Maybe
you
solved
that
problem
by
adding
more
attributes,
but
then
now
you
have
to
maintain
that
code
base
right
and
by
the
way
we
want
to
be
very
clear:
we're
not
necessarily
telling
you
what
what
the
best
option
for
all
issues
are.
B
In
fact,
the
key
thing
is
that
we
want
to
review
all
the
different
pros
and
cons
of
each
approach.
There
are
approaches
that
that
doug
actually
likes
doing
that's
different
from
the
approaches
that
I
like
doing,
but
it's
possible.
It
also
has
to
do
with
the
fact
that
what
doug
designs
and
builds
and
what
I
design
and
build
are
often
different
things
anyways.
So
there's
a
reason
behind
that.
It's
not
just
because
we're
just
arbitrary
saying.
B
B
B
There
are
scenarios
that,
where
range
partitioning
actually
makes
sense,
and
so
we'll,
then,
if
I
choose
a
circuit
key
that
can't
be
distributed
evenly
will
end
up.
Having
is
good,
old-fashioned,
skew
and
irrelevant
of
whether
you
prefer
range
or
prefer
hash
and
by
the
way,
just
because
I
said
I
wanted,
the
option
doesn't
mean
I
actually
use
range
all
the
time.
B
In
fact,
I
use
hash
most
of
the
time,
but
the
idea
is
that
if
I
end
up
with
a
certain
key
value
that
basically
is
industry,
probably
then
I
have
good
old-fashioned
skewed
data
and
guess
what
you
as
a
data
warehousing
as
a
data
lake
house
person,
yeah
you're
gonna.
We
all
hate,
sku,
right,
apache,
spark
3.0,
actually
just
added
as
part
of
the
eq
as
part
of
the
aqe,
the
ability
to
go
ahead
and
do
dynamic
skus
for
handle
dynamic
skills
when
you're
just
doing
joins.
B
So
we're
trying
to
add
to
the
code
to
be
able
to
handle
that,
but
in
the
end
it's
still
a
problem
for
all
of
us.
I
mean,
thank
goodness
spark.
3.0
makes
it
easier,
but
the
reality
is.
It
can
still
be
problematic,
all
right,
so
anyways.
I
think
we
covered
sort
of
the
historical,
the
concepts
doug.
Let's
just
go
dive
into
the
demo
here.
A
Yeah
absolutely
so
quick
setup.
This
only
requires
two
parameters,
number
of
rows.
I
believe
that's
set
to
a
million
and
a
number
of
partitions,
the
number
of
spark
partitions.
We
we
want
to
simulate
the
distributed
processing,
nature
here
and
then
so.
First
we're
going
to
test
the
monotonically
increasing
id
a
popular
answer
in
in
slack
sorry
sorry,
not
slack
overflow,
but
all
right.
So
for
you
know
here
we
create
a
a
test
dimension
table
with
a
surrogate
key
identifier,
random
number
and
some
other
attributes.
A
Now
here
we
use
a
range
table
value
function,
and
here
we
substitute
in
the
number
of
rows
the
number
of
partitions,
and
so
we
we
can
generate
kind
of
a
synthetic
data
set
very
quickly
to
any
size
that
we
want
into
any
degree
of
parallelism
that
that
we
we
run
this.
That
takes
you
know.
That
was
probably
the
first
run
that
took
29
seconds,
but
we
look
through
the
data.
A
The
idea
of
this
thing
is
that
you
know
you're
going
to
be
updating
your
your
data
in
in
batches,
maybe
a
million
a
time,
maybe
a
thousand
at
a
time
and
you're
going
to
be
doing
this
repeatedly.
So
we
want
to
see
how
the
surrogate
key
generation
performs
over
multiple
inserts
and
so
again
now,
if
it's
successful
the
account
and
count
the
stink
would
be
the
same.
But
as
you
can
see
it's
not
so
denny
can
you
can
you
guess
I
mean?
B
Actually,
sir,
why
don't
we
actually
engage
with
the
community
since
I
do
know
right
exactly
so,
for
those
of
you
who
are
listening
to
us
live
whether
on
youtube
live
or
on
chat?
Can
you
guess
why
this
is
broken?
Can
you
guess
why,
when
we're
doing
the
insertion
using
monetary
increasing
id
that,
in
fact
what
we
end
up
having
is
we
literally
have
a
massive
amount
of
hash
collisions
here
by
definition
right
and
in
fact,
100
collisions
exactly
exactly.
Can
anybody
can
anybody
guess.
B
A
A
A
There
are
no
other
guarantees
that
there's
like
no
guarantees
in
terms
of
the
spacing
between
the
numbers
and
so
on,
so
we're
basically
generating
the
same
range
of
integers
or
big
ends
again
twice,
so
that
that's
why
we
have
a
hundred
and
in
fact
it
again,
there's
no
guarantee
it'll
generate
the
same
numbers,
but
there's
no
guarantee
either
that
it
won't
generate
the
same
if
that
makes
any
sense
but
anyways,
so
that
was
kind
of
a
failure.
So
what
do
we
have
to
do
there?
A
Well
we're
going
to
reset
it,
and
we
have
this
create
a
replace
table
in
in
a
spark
3.0.
Just
recreating
the
table,
we're
doing
the
insert
again
the
same
range
again,
but
this
time
we're
going
to
get
the
max
surrogate
key
value
from
the
fir
from
the
you
know.
The
current
state
of
of
the
table-
and
we
we
actually
inject
that
right
here
as
sql
since
monotonically
increasing
id-
starts
with
zero,
we're
going
to
add
1
to
it,
and
so
we're
going
to
do
this
all
over
again.
A
So
whatever
the
max
value
plus
a
a
range
of
ids,
you
know,
is
what
we're
going
to
generate
for
sk
for
the
second
and
in
subsequent
attempts
here.
So
we
do
that
and
we
see
that
it
succeeds.
So
all
right,
yeah
we've
achieved
we've
achieved
uniqueness,
which
is
a
very
important
criteria
in
surrogate
keys.
A
A
You
know
we
use
the
notebook,
histogram
plotting
feature
and
we
can
see
it
ranges
up
to
something
like
eight
and
a
half
trillion
in
the
values.
Now
we
only
have
two
million
values
in
the
table,
so
that's
spread
out
over
eight
and
a
half
trillion.
So
there's
a
lot
of
space
between
those
surrogate
key
facts:
energy.
B
B
A
B
A
B
I
think
that
the
dba
is
going
to
probably
complain
about
two
things.
Personally,
the
first
thing
would
be
the
fact
of
the
sparsity,
especially
if
you're,
like
thinking
just
purely
from
a
database
perspective,
the
idea
that
you
actually
have
that,
like
literally
go
from
like
zero
to
eight,
whatever
trillion
or
whatever,
that
number
is,
which
is
massively
large
right.
I
think
the
sparsity
is
going
to
be
an
issue
in
terms
of
performance
of
trying
to
join
things
like
that.
B
If
you
do
have
a
lot
of
it,
that
is,
of
course,
I
think
the
other
issue
with
monotonically
increasing
ideas
is
in
general,
like
when
you
have
new
data
that
come
in
right,
you're,
constantly
processing,
new,
constantly
processing
right
the
idea
that
then
I'd
actually
have
to
go
back
and
join
right
to
that
table,
to
figure
out
what
the
latest
value
is
in
order
to
be
able
to
increase
it.
So
that
way,
I
wouldn't
go
ahead
and
actually
overrun
and
generate
the
the
value
right,
because
this
works.
B
This
will
work
one
time
right.
You
know
no
problem
at
all,
but
then
how
do
you?
How
do
you
determine?
Basically,
you
have
to
keep
on
running
this
subsequently,
so
it
keeps
on
going
up
and
up
and
up
right.
I
think
at
least
from
a
dba
perspective,
at
least
from
where
I'm
sitting,
and
you
may
agree
or
disagree,
and
for
that
matter,
for
those
of
you
online
right
now
live
chime
in
right,
chime
in
and
tell
us.
B
If
you
disagree
or
agree
with
us,
you
know
we're
not
the
experts,
we
we
claim
to
be
pretty
good
at
it,
but
we're
not
the
experts
right
so
but
at
least
from
where
I'm
sitting
from
as
a
dba
I'd
say,
you
know
what
I
I
I'm
not
sure.
I'm
happy
with
the
idea
that
I
have
to
generate
this
often
like
if
I
have
like
multiple
times
a
day
processing
and
I
have
to
keep
doing
something
like
that.
How.
A
About
you
doug,
what
do
you
think
yeah
yeah,
so
my
thoughts
on
it
is,
you
know,
and
working
with
a
lot
of
dbas
over
the
last
20
some
years,
they're
used
to
these
auto
increments
and
seeing
each
new
record
just
like
bumped
up
by
one
and
I've,
I've
seen
a
lot
of
etl
code
that
will
just
do
select
maths
and
then
add
one
and
insert
that's
super
inefficient.
But
you
know
people
are
expecting
plus
one
even
ralph
kimball
writing
long
long
ago,
kind
of
before
a
lot
of
parallel
execution
said
that
it
should
increment.
A
B
A
B
A
B
Oh
absolutely,
I
completely
agree
with
you
actually
on
that
one,
so
so,
in
other
words,
the
the
context
here
without
a
doubt
is
that
there
are
certainly
mitigations
to
the
process
provided
that
you're,
generating
with
monotonically
increasing
id
correctly.
Then
it's
probably
going
to
be
okay
for
for
a
distributed
system.
Yes,
mark.
A
B
A
A
A
Here
we're
going
to
run
our
spark
sql
generate
our
data
frame.
That's
our
test
data,
we're
going
to
zip
it,
and
what
this
does
is
add
a
circuit
key
to
this
data
frame.
Here
we're
going
to
create
a
replace
we're
going
to
kind
of
put
into
sql
space
with
this
temp
view
and
then
we're
going
to
insert
that
into
our
test
dimension
file.
That's
our
delta
table
and
you
can
see.
A
A
A
Oh
one
more
thing,
so
that
was
zip
with
index
there's
another.
Now
you
might
say
actually
why
don't
we?
Why
don't
we
go
and
look
at
the
other
one
then
we'll
come
back
and
try
to
understand
what
differentiates
them.
What
really
is
the
difference
between
zip
with
index
and
zip
with
unique
index
again?
It
has
a
little
bit
different
approach
to
signing
the
ids
there.
B
A
A
B
A
A
The
ultimate
number
is
a
little
bit
higher
than
2
million.
I
mean,
from
my
point
of
view,
it's
distinct.
You
have
a
little
bit
of
leakage,
not
too
bad.
It's
pretty
dense.
I
I
think
that's
okay,
yeah.
I
think
so
I
I
I
think,
that's
pretty
decent
yeah
dba
might
question
that
distribution.
I
I
think
a
dba
would
probably
have
a
bigger
problem
and
I
would
frankly
like,
if
someone's
coming
at
this
from
a
single
perspective,
you
don't
really
want
to
drop
into
python
now.
A
B
B
A
A
It
doesn't
even
run
a
spark
job,
so
it's
even
more
efficient
and
so,
as
this
scales
up
from
millions
to
billions,
I
think
simply
unique
ids
can
perform
a
lot
better
in
in
this
example,
so
that
that's
the
main
callout
and
the
main
difference
between
the
two
so
and-
and
this
is
pulled
directly
from
the
documentation,
so
you
can
look
at
it.
So,
let's,
let's
move
ahead
to
hash.
A
A
B
B
B
Yeah
absolutely
so
now.
One
thing
one
thing
I
do
want
to
add
is
that
that's
with
the
the
hash
function,
you
can
also
use
other
functions,
other
algorithms
and
I'm
actually
a
big
fan
of
using
like
sha-2,
especially
things
like
sha-512
right
right
and
then
so.
The
likelihood
of
hash
collision
drops
to
infinitesimally
small
I'm
a
huge
fan
of
using
those
personally,
but
I
also
but
doug's
calling
out
a
very
important
point.
B
B
A
Right
yeah
right
so,
but
let's
within
the
hash
function,
yes,
it's
busted
all
right
and
we'll
get
to
one
more
hash.
If
we
have
time
so,
here's
the
requested
row
number
now.
This
is
a
window
function
here,
we're
doing
a
reset,
we're
doing
a
row
number
over
order
by
null
another
trick
from
my
friends
at
stack,
overflow.
A
They're
great
row
number:
we
do
that
again.
Here
we
have
to
you
know
we
have
to
do
that.
Offset
row
number
starts
with
one,
so
we
don't
have
to
add
in
one
so
again
we're
no
collisions
there.
That's
good!
It's!
It's
dense,
it's
densely
packed!
So
that's
successful
again!
You
can
see
it
visually
here
with
our
histogram
plot.
B
B
A
Computing
at
row
number
is,
is
single
thread,
it's
just
one,
one
single
thread,
so
you
know
maybe
for
two
million
it's
not
too
bad.
It's
4.64
seconds,
you'll
see
as
you
go
through
this.
This
is
actually
a
little
larger
than
some
of
the
other
approaches,
not
by
a
huge
number
but
a
couple
seconds,
maybe
double,
but
you
know
it
could
serialize
and-
and
that's
not
good
for
for
really
large
datasets.
So
right
it.
It
does
meet
kind
of
a
dba
perspective.
Distribution,
yes,
but
scale
is,
is
of
a
concern
so.
B
B
B
So
if
you've
got
a
large
amount
of
data
when
using
row
number,
even
if
I
was
to
say
you
know
what
it's
okay,
I'm
gonna
use
the
partition
by
to
avoid
the
problem
that
we
just
mentioned
here
right
in
terms
of
sending
everything
back
to
the
driver
like
now
you
you're,
so
in
other
words
either
your
scale
will
hit
a
bottleneck
because
you,
you
slam
the
driver
or
your
scale,
hit
the
bottleneck
because
you're
shuffling
like
mad
right.
So
that's
your
concern
and
then.
B
A
A
B
A
No,
I
I
want
to
call
it
like
the
data
vault
folks,
the
they
love
this.
They
call
for
the
md5
yeah,
and
you
know
there
is
a
functional
dependency
between
the
hash
value
and
what's
in
the
row,
but
it
is
a
fast
way
of
joining
tables
and
doing
some
other
things.
You
know
you
can't
do
the
range
drawing
that
we're
talking
about.
A
Anyways
you
got
uniqueness,
which
you
know
relying
on
theory
that
is
well
distributed.
Yes,
we
didn't
have
a
quick
way
of
demonstrating
that
here
sorry
ebas
will
will
have
to
wrap
their
head
around
using
hashes,
and
you
know
listening
to
the
data
vault
perspective,
I
think
you
know
the
fact
and
dimension
partitioning
you're
going
to
be
hash
partitioning
that
range
and
then
parallel.
Yes,
it's
actually
embarrassing.
B
B
A
A
B
Yes,
so
so
the
the
the
zip
is
more
like
the
because,
if
you're
comfortable
with
python
rdds,
then
apps
or
that
type
of
mindset,
I
should
put
that
way.
That's
very
plausible
for
the
sql
dba
type
person
versus
the
the
sql
dba
person
would
like
monotonically
increasing
id,
but
they
would
not
like
the
sparsity
of
it.
That's.
A
Right
yeah,
we
both
clearly
agree
on
that
yeah
and
I
I
would
like
to
maybe
encourage
the
audience
to
actually
reopen
the
issue
for
the
spark
community.
Yes,
and
and
convert
this
to
data
frames,
make
sure
there's
the
sql
api
on
there.
So
there's
a
link
to
the
the
spark
jira
here
so
all
right
and
then
and
then
we
talked
about
row
number,
that's
sql
based
it's!
It's
dense!
It's
unique!
B
A
A
B
Yeah
so
like
doug
is
calling
out
so
we're
about
to
we're
about
to
tail
over
and
switch
over,
but
the
context.
What
we're
trying
to
say
basically,
is
that
and
actually
there's
a
good
chat
here.
What
is
the
solution?
The
solution?
That's
why
we
kept
on
saying
plausible
or
versus
busted.
Basically,
what
we're
trying
to
call
out
and
we'll
probably
add
some
text
to
the
notebook
before
we
share
it
out-
is
that
basically
most
of
these
issues
are
actually
the
solutions
are
actually
plausible.
B
It's
just
plausible
from
what
your
perspective
is,
but
the
only
one
that
we
really
are
saying,
no,
as
you
know,
is
busted,
is
basically
hash
by
itself
versus
like
md5
and
a
shot.
Two
row
number
is
certainly
plausible,
especially
if
you're
looking
with
smaller
numbers,
but
not
you
know
for
larger
ones,
because
of
shuffling
whatever
else,
we
would
like
you
to
vote
for
zip
with
index,
so
we
can
bring
the
data
frame.
So
that's
the
the
the
crux
or
the
answer
that
we're
trying
to
provide
to
you
that
there's
really
no
straight
up
solution.
B
I
do
so,
could
you
please
stop
sharing,
and
I
shall
on
my
screen
absolutely.
Thank
you,
sir
all
right
now.
I
need
to
find
it
perfect,
okay,
so
to
wrap
things
up.
If
you
want
to
learn
more
about
delta
lake
and
underneath
the
covers,
because
that's
what
we're
running
underneath
the
covers
is
please
go
to
delta.io,
as
you
can
see
here.
Okay,
that's
also
where
you
get
the
vldb
paper.
B
Oh
sorry,
we
also
have
some
new
sessions
coming
up
this
thursday,
as
in
two
days
from
now,
we
have
a
tutorial
on
how
delta
lake
supercharges
your
data
lakes,
with
lee
frolio,
he's
actually
a
chef
and
senior
technical
instructor.
At
the
same
time,
so
pretty
cool,
it's
gonna
be
a
fun
session,
so
we're
gonna,
hopefully
see
you
guys
all
there
on
thursday
on
next
tuesday.
This
is
gonna,
be
the
data
collab
lab
with
myself,
franco,
padano
and
myself.
B
We
are
running
this
lab
where
we
get
together
with
other
people
to
talk
about
how
to
solve
some
pretty
hard
problems.
In
this
case,
smart
sql
queries
powered
by
the
delta
engine
on
your
lake
house
with
dill
boswick
and
mayor
grace,
nesta.
So
it'll
be
a
lot
of
fun
sessions.
So
same
idea
come
aboard
chime
chat
in
whether
it's
by
zoom
or
youtube
live
we're
good
to
go
and
then
finally,
I
didn't
want
to
call
out
and
say
you
know,
try
out
spark,
3.0
and
delta
like
today.
B
You
can
try
out
spark
through
and
death
like
using
database
community
edition
the
notebooks
from
today's
session
and
any
of
the
all
the
other
tech
talks
we
have.
You
can
actually
see
them
right
here
on
github
databricks
tech
talks
or
you
came
to
us
for
the
data
and
ai
meet
up
the
dbrix.com
for
get
ebook
to
get
the
to
download
learning,
spark
second
edition
for
free
and
then
don't
forget
the
vldb
paper.
So
that's
it
for
today's
session.