►
From YouTube: Tech Chat | Slowly Changing Dimensions (SCD) Type 2
Description
We will discuss a popular online analytics processing (OLAP) fundamental - slowly changing dimensions (SCD) - specifically Type-2. As we have 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. Type 2 SCD within data warehousing allows you to keep track of both the history and current data over time. We will discuss how to apply these concepts to your data lake within the context of the market segmentation of a climbing eCommerce site.
Speaker:
Douglas Moore, Solution Architect
I’m passionate about helping customers find value in data analytics and helping the people I work better succeed. 25+ year data veteran, ranging from embedded systems to massive cloud based data lakes. My early career interest centered around producing 3D animations of Finite Element Modeled Elastic Waves. Career wise, I came for the data visualizations and stayed for the computation and data. Past roles have included: Solutions Architect, Data Architect, CTO, Engineer. Current Specialties: Big Data Strategy & Architecture, Data Lakes, Streaming, Delta Lake, Spark, and Databricks. 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
C
B
B
Boulder
all
right
we've
got
some
people
from
Austria
New
York,
City
Poland.
Oh,
that's
awesome
for
Polat
we're
in
Poland,
Medicare,
ste
and
then
San
Diego,
I
love
that
town.
That's
pretty
awesome:
London,
India,
J,
/,
Valley,
Netherlands,
hey
yeah;
that's
awesome,
cool,
so
good
to
see
everybody
jumping
onboard.
This
is
awesome.
Warsaw,
excellent.
A
B
A
Hope,
yeah
all
right,
I
think
we
should
be
ready
to
get
going
now.
The
the
pace
of
people
joining
is
slowed
a
little
bit
so
we're
here
today,
everyone
to
talk
about
slowly,
changing
dimensions,
sed
type
2,
which
is
a
fundamental
from
the
data
warehousing
world
and
we're
to
talk
about
how
that
applies
in
data
lakes
and
in
particular
Delta
Lake.
A
So
as
a
reminder,
the
upcoming
online
events
are
available
on
our
meetup
site,
so
the
data
AAI
online
meetup
and
you
can
also
subscribe
to
our
YouTube
channel
where
we're
launching
a
lot
of
new
live
content,
plus
pre-recorded
content
and
be
sure
to
turn
on
your
notifications.
In
order
to
get
alerted.
When
we
have
launched
a
new
online
or
sorry
new,
live
or
recorded
video
and
just
a
side
note,
there
is
we're
almost
at
30,000
subscribers
on
YouTube,
just
a
few
more
subscribers
to
go.
A
So
please
join
and
push
us
over
that
line
and
if
you
enjoy
the
content
here,
I
am
sure
you'll
enjoy
the
spark
plus
AI
summit
that
is
coming
up
here
in
just
a
couple
weeks
or
a
few
weeks,
and
so
that's
June
22nd
to
26th
and
both
Douglas
and
Denny
will
be
presenting
during
the
summit
on
a
variety
of
different
topics.
But
we
have
a
few
hundred
other
sessions
full
5
days
full
week
of
content.
We
have
quadrupled
the
pre-conference
training
and
here's
a
discount
code
for
you
to
get
25%
off
the
training.
A
C
Hi
everyone,
I'm
Douglas
Moore
I've,
been
working
at
day
bricks
for
just
over
a
year,
but
I've
been
in
data
databases,
first
OLTP
databases
and
then
more
data,
warehouses,
data
Mart's
for
analytics
over
the
last
20
years
or
so,
and
I've
been
working
in
big
data,
specifically
for
about
eight
years
and
I
kind
of
I
kind
of
smiled
that
that
you
actually
got
caught.
That
I
actually
wrote
that
that
quote
in
my
bio,
it's
kind
of
funny.
C
B
Also
on
the
team
that
helped
build
what
is
now
known
as
HD
insight,
we
were
the
nine
person
incubation
team,
so
I've
been
working
with
Apache
spark
for
quite
some
time
still
likes
to
intermix
data
warehousing
and
then
every
so
often
you'll
still
see
me
talk
a
little
bit
from
the
biological
side.
So
that
way,
I
don't
disappoint
my
parents.
Just
this
ride
called
out
all.
A
C
All
right,
very
good,
so
I
just
want
to
set
some
context
initially,
so
we,
you
know
a
lot
of
companies.
You
know
part
of
that
eight
years
in
Big,
Data
is
been
about.
Building
data,
lakes
and
and
data
looks
our
way
to
bring
in
all
these
different
data
sources
and
integrate
it
and
prepare
it
for
a
IML
reporting,
creating
data
products
and
doing
stuff
downstream.
So
Sebring
bring
your
data
in
and
RAW
format,
whether
it's
CSV
or
or
just
binary.
So
and
whatnot
you
clean
it
up
and,
and
you
prepare
a
goal.
C
C
You
need
to
build
your
dimension
tables,
because
you
know
the
techniques,
the
kimple
techniques
for
analyzing
dimensional
data
still
live
on
and
will
live
on
for
many
more
years
and
probably
decades
to
follow
so
doing
this
in
Hadoop
was
building
dimensional
tables
was
was
really
hard
because
common
technique
there
is
that
is,
for
a
dimensional
table
is
to
build
a
slowly
changing
dimension
type
to
now.
Why
type?
C
Now
you
don't
want
to
rebuild
that
fact
table
every
time
a
customer
and
devise
product
store
so
and
so
forth
changes.
So
you
want
to
add
a
new
customer
record
and
then
any
new
facts
that
come
in
you
know
sales
or
orders
related
to
that
customer.
They
just
get
added
to
and
and
link
to
that
new
customer
record
that
turn
active
customer
record.
C
So
that's
why
you
know
you
you
do
this
in
the
data
like
why
you
ant
use
a
type
2
dimension
here
and
then
you
know,
as
you
need
to
you,
your
kind
of
analyte.
You
have
some
users
analyzing
the
data
directly
off
the
gold
tables,
or
you
know,
you're
shipping,
this
for
your
high
concurrency
reports
and
dashboards
into
your
cloud
data
warehouse
Jenny.
Did
you
have
something
well.
B
C
B
C
B
Problem
so
let's
dive
into
that,
so
you've
been
bringing
up
the
concept
of
Kimball's
so
for
the
folks
that
actually
have
a
data
warehousing
background.
They're,
probably
gonna,
understand,
invariably
understand
Kimball,
quite
well,
but
how
about
for
those
folks
that
are
coming
more
from
the
distributor
side
of
the
plan?
What
what?
What
are?
What
is
in
reference
to
Kimball
in
this
case?
B
C
C
C
Yeah
so
a
good
point,
maybe
I
skipped
over
that
context.
Setting
so
there's
a
dimensional
or
star
schema
that
you
see
here.
You
have
the
fact
table
here
and
you
have
foreign
keys
from
from
your
customer,
devise
a
product
or
store
so
on
dimensions.
There's
usually
a
time
dimension
as
well
in
here
and
and
those
are
surrogate
keys,
say
on
the
customer
table
that
lend
themselves
to
this
fact
and
the
fact
might
be
generated
off
of
a
purchase
order
or
sales
transaction,
an
e-commerce
order,
so
on
and
so
forth.
C
So
so
these
facts
there
are
generally
append-only,
but
but
the
the
dimensions
that
represent
a
customer
customers
change
that
the
preferences
change
the
market
segment
that
you
might
want
to
classify
them
in
that
we're
going
to
use
for
this
example,
will
change
over
time
a
device
or
a
product.
You
might
have
different
revisions
of
a
product
that
you're
selling
you
might
not
want
to
or
device
a
device
might
get
moved
or
change
ownership.
Or
so
these
things
change
over
time.
You
need
to
capture
that
history.
C
C
Well,
you
know
you
can
look
at
the
facts
all
day
long,
but
you
know
without
that
context
that
maybe
some
of
your
customers
have
moved
out
of
the
region
around
your
store.
You
know
that
would
be
lost
if,
if
you
use
the
type
1
dimension
now
building
this
in
in
a
data
Lake
with
with
a
you
know,
technology
say
Hadoop
or
you
know
other
technologies
where
your
data,
like
is
append-only.
You
know
the
underlying
substrate
is
that's
3
or
HD
pass
yeah.
C
You
know
it's
a
pend,
only
file
system
that
gives
it
the
amazing
scale
that
that
you
get
petabytes
of
data.
You
can
store
in
your
daylight
and
integrate
at
all,
but
you
know
you
can't
update
it
directly
because
I
would
require
locking
so
Delta
Lake
creates
this
abstraction
over
s3
or
a
TLS
gen
to
over
your
cloud
storage
and
allows
you
to
get
these
updates.
C
These
deletes
these
up,
certs
or
merges,
and
so
the
that
absurd
emerge
is
is
key
to
doing
a
slowly
changing
dimension
type
to
because
what
you
do
is
you're,
going
to
update
the
previous
record
so
in
in
this
example.
Here
this
previous
record,
you
know
the
supplier
state
is
California
that
previous
record.
That
was
given
the
end
date.
You
know
when
was
a
supplier
in
this
example,
when
was
the
supplier
last
in
the
state
of
California,
well
2004,
12
22,
prior
to
that
they
were
in
California.
C
Now
after
that
date,
they're
considered
to
be
Illinois,
so
they're,
given
a
new
start
date,
end
date
is
unknown.
So
that's
no!
So
this
you
know.
As
you
can
see,
this
is
an
additive
process,
but
you
have
to
go
back
and
update
that
pre-exists,
that
prior
record
here
and
that's
what's
been
challenging
with
Hadoop
and
big
data
is
doing
updates
until
Delta
loop
comes
along,
and
so
now
we
can
do
that.
We
have
updates
and
in
inserts,
but
it's
important
to
note
that
you
want
to
do
these
two
things.
C
Add
the
new
record
and
update
the
previous
one
in
one
transaction,
because
you
don't
want
any
kind
of
inconsistency
and
Delta
it
gives
you
transactions
and
ask
the
compliance
on
that
updates
and
the
merge
operator
you
know
across
each
operator.
The
merge
operator
allows
you
to
combine
both
the
update
and
insert
so
database
people
know
this
as
up
cert.
The
kind
of
the
Hadoop
Big
Data
folks
know
this
is
emerge
so
and
in
in
in
Delta,
like
it's
called
the
merge
into
operator
and
so
we're
going
to
get
into
that.
C
But
before
we
do
that,
let's
explore
the
data
a
bit.
So
I
have
some
data
from
a
TPC
data
set
the
customer
data
I,
you
know
I
drop
into
a
bash
I.
Do
an
LS
I
see
it's
a
hundred,
seventeen
megabytes,
you
know
a
sample
data
set,
I
see
it's
got,
750
thousand
lines
in
it
and
I
can
see
that
it's
pipe
separated
and
I
see
that
has
no
header
to
it.
So
these
are
important
things
that
that
I
need
to
know
when
it
comes
to
loading
the
data.
So
I'm
gonna
split
this.
C
It's
pipe
and
further
schema.
No
header
I
learned
this
because
my
data
exploration
and
because
I've
run
this
as
a
demo
I'm
going
to
force
this
to
reload,
you
know.
Otherwise
what
will
copy
into
will
do
is
ensure
that
you
only
load
it
once.
It
keeps
track
of
all
the
files
that
you've
loaded
so
and
it
simplifies
your
ingestion
but
anyways
so
for
the
demo,
we're
going
to
force
that
the
truth,
so
that
runs
then
I'm
gonna
create
a
table
over
it
over
that
location.
Now
this
is
accessible.
C
This
customer
dim
table
is
now
accessible
via
sequel,
so
I
can
work
with
in
sequel,
I
can
look
at
and
all
the
different
types
of
it
the
fields
I
can
look
at
the
physical
location
of
where
that
data
is
in
I
can
browse
the
data
and
see
that
I've
imported.
Now
one
thing
I
didn't
talk
about
I,
don't
let
me
go
back.
You
know
if
you're,
an
ETL
shop,
you're
going
to
add
a
lot
of
audit
columns
here.
C
C
I
added
some
parameters
here:
the
job
date,
my
user
name,
the
the
target,
the
req
database
there
as
parameters
so
anyways
going
back.
I'm
loading
I'm
loading,
this
sorry,
so
I
loaded
it
all
that
date,
a
little
that
they
have.
The
audit
comes
here:
source
user
createdate
when
I
I
created
this
record
an
update
date,
so
I
I
think
I
mentioned
I,
designed
some
data
warehouses
in
the
past.
So
it's
hard
for
me
to
build
a
demo
without
some
of
these
production
type
things
that
you
would
expect
anyways.
C
Let's
look
at
the
customer
dimension
in
in
more
of
a
summary
point
of
view.
So
what
I
see
is
we
have
five
market
segments
in
the
TP
CH
data
set?
And
you
know
this
is
a
synthetic
data
set
and
and
your
first
clue
there
is
that
this
is
evenly
balanced.
You
won't
normally
expect
to
see
this
in
real
data
set,
but
you
know
because
we
clicked
here
with
a
bar
chart.
You
know
we
could
basically
see
that
we
have
five
segments
and
and
they're
evenly
balanced
and
so
with
Delta
lik.
C
You
can
look
at
the
transaction
history
and
you
can
see
that
I
did
a
load.
I
did
this
yesterday
and
in
the
top
and
the
copy
operation,
and
you
can
scroll
over
here,
you
can
see
a
bunch
of
operational
metrics.
You
know
I
loaded
a
little
bunch
of
records.
It
makes
sense
any
any
questions
as
far
as
so
I
took
this
through
the
overview
and
loading
the
data
and
and
a
little
bit
of
analyzing
the
data
that
we
loaded
sure.
B
Sure,
actually,
we've
got
some
great
questions
that
have
come
in.
So
why
would
take
a
pause
here
to
start
diving
into
those
questions
actually,
but
before
you
would
even
do
that?
I
did
want
to
call
that
one
thing
that
people
may
not
have
noticed
that
Douglass
is
doing
a
great
job,
showing
you
notice
that
this
notebook
is
a
sequel,
notebook
right,
so
very
much
in
line
with
how
our
data
warehousing
folks
are
typically
running
so
on
the
top
will
sequel
and
all
the
commander
in
sequel.
B
Up
to
this
point,
most
of
our
Delta
Lake
sessions
have
actually
been
on
Scala
or
on
Python,
but
Douglas
has
done
a
great
great
call
at
the
fact
that
whoa
yes
for
those
folks
who
are
very
much
in
that
data
warehousing
database,
world
I'll,
a
sequel
there.
You
go,
let's
go
ahead
and
build
this
stuff
in
sequel,
so
so
hey
Douglas
we've
got
some
good
questions
and
in
fact,
I
went
ahead
and
answer
some
of
them
privately.
But
I
did
something
I,
think
it's
good
for
the
entire
audience.
B
So
let's
go
ahead
and
start
with
the
first
question
is:
can
Delta
tables
generate
certain
keys
I
like
basically,
identity
calls
in
the
sequel
world
and
I
Basin
answers
like
no,
you
actually
have
to
automatically
create
them
yourself,
but
I
just
figured
since
you've
done
this
many
times.
Any
other
suggestions
on
how
you
would
approach
that
particular
problem.
C
Yeah
Danny,
that's
an
interesting
question
that
came
up
yesterday,
actually
with
a
customer,
perfect
and
and
and
there
there's
like
three
approaches
to
do
that
at
least
three
approaches
and
we
could
devote
a
whole
actually
session
to
generating
surrogate
kings.
That
scale
would
spark.
So
you
know
we
we
might
want
to
say
that
there's
there's
like
generating
a
hash
on
on
the
key
values.
There's
a
monotonically
increasing
ID,
a
lot
of
people
come
from.
C
C
So
you
know
you
gotta,
break
it
down
into
partitions
and
do
things
like
that
and
and
you
get
except
that
you
know
sooner
or
later,
your
your
your
ID
numbers
aren't
going
to
be
sequential
and
compacted,
so
we
could
actually
devote
a
whole
session
just
to
this
I've
gone
through
this
a
number
of
times
with
big
customers
and
it's
a
meeting
subject.
So
it's
an
excellent
question.
So
so
maybe
we
can
set
up
an
item
for
a
future
date.
Absolutely.
B
B
B
B
Doing
pretty
good,
so,
let's,
let's
ask
another
question
and
there's
some
other
questions
that
are
coming
in
the
Q&A
panel.
Let's
ask
the
one
more
question:
I'm
gonna
go
ahead
and
answer
them
privately.
While
you
go
ahead
and
continue
on
and
then
if
we
have
time
we'll
do
the
other
ones,
but
let's
at
least
ask
this
other
question:
Agron
other--
great
question:
I
thought
was
that
we
should
share
with
everybody.
Is
keeping
in
consideration
about
time
to
market
right?
B
Do
you
still
recommend
the
dimensional
modeling
that
you're
designing
and
my
response
to
him
was
that
was
that
you
could
potentially
do
that.
One
approach
that
some
of
the
customers
I
work
with
is:
basically
they
would
have
the
at
least
when
it
comes
to
the
medallion
model.
The
bronze
and
silver
tables
are
not
dimensional
modeling
and
the
gold
tables
are
so
because
the
gold
tables
would
end
up
being
where
you
apply
the
dimensional
model,
but
you
didn't
necessarily
apply
it
to
bronze
silver.
C
To
it,
yeah,
yes,
that's
definitely
my
been
my
experience
as
well
that
that
you
know-
and
it's
not
like
all
your
data
actually
meets,
makes
it
to
the
gold
layer
right.
You
know
we
draw
this
this
diagram,
but
you
know
the
bronze
layer
should
probably
be
ten
times
larger
than
the
silver
layer
and
and
that's
multiple
larger
than
what's
in
the
gold
layer.
C
You
know,
but
we
draw
these
kind
of
nice
and
even
cuz.
It
shows
better.
Yes,
you
know
what
what
companies
do
is
they'll
and
the
raw
data,
because
you
don't
want
store
in
filtering
because
they
might
drop
some.
You
might
need
today
or
you
might
need
in
three
months
from
now.
So
you
you
know,
you
have
the
theme
touch
it
take
it.
C
You
know
if,
if
you
touch
a
system
we'll
bring
it
all
in,
you
know
you
keep
the
catalog
and
the
provenance
of
that,
but
then
you
allow
people
power
users
even
to
go
in
and
explore
the
state
and
figure
out.
What's
there
figure
out
if
that's
even
worth
further
structure,
you
know.
Is
this
a
useful
value,
useful
metric
here
in
the
bronze
layer
before
we
put
together
an
IT
project
to
structure
it
and
create
reports
and
dashboards?
C
You
know
we
might
think,
like
the
number
of
windows
on
a
house
or
whatever
that
metric
is,
is
a
useful
number.
You
know
we
might
just
send
out.
You
know
a
scouting
party
say
well.
Is
that
really
useful
before
we
build
a
whole
IT
project
and
wait?
So,
in
terms
of
time
to
market
by
by
having
these
layers
of
curation,
you
know
you'll
get
there
faster
you'll
get
to
the
crux
of
it:
the
important
stuff,
a
lot
faster,
because
you're
weeding
out
a
lot
of
stuff
that
you
probably
don't
need
down
here.
C
C
And
you
know,
as
you
can
see,
you
can
access
all
this
data
with
sequel
or
Python
or
Scala,
but
you
notice
you
can
access
it
with
sequel.
You
open
it
up
to
a
broader
audience
with
enterprise,
but
you
can
get
to
it
quickly
and
you
don't
have
to
wait
for
that
long
IT
project
and
budgeting
and
justification
before
it
gets
into
your
reporting
system.
Engineer,
say
your
data
warehouse,
so
you
actually
in
a
way
get
there
faster
because
you
have
this.
B
Up
and
then
we
we
will,
we
do
have
a
bunch
of
other
questions.
I'll
be
answering
some
of
those
questions
online,
so
just
in
case
we
don't
have
time
to
answer
them
live
but
I'll
answer
the
questions
of
the
Q&A
panel.
While
you
go
ahead
and
talk
about
the
really
awesome
context
of
the
change
set
here,.
C
So
the
change
set-
this
comes
from.
Imagine
you
get
a
load
from
a
source
system
or
a
whole
bunch
of
source
systems.
These
are
changes
that
are
going
into
your
gold
tables
into
your
dimensional
model.
You
have
this
in
your
data
warehouse.
You
have
this
in
your
own
OLTP
system,
but
these
are
the
updates.
So
you
need
to
process
this
through
the
silver
layer
and
standardize
it
and-
and
now
you
have
a
chain
set
now
you
need
to
apply
it
to
your
dimension.
Okay,
so
I'm
simulating
one
Here
I
am
actually
sampling.
C
Now
you
can
make
one
of
up
whatever
business
scenario,
where
you
would
reclassify
some
of
your
customers
from
five
segments
to
say
six
segments,
but
we're
adding
a
sixth
segment
to
some
10%
of
our
customers
here,
and
you
know
we're
doing
this
for
demo
purposes,
but
they're
real-life
scenarios
where,
where
you
resegmented
your
customers
and
using
data
science
and
machine
learning
for
that,
so
that's
what
that's
a
change
set
and
then
I
go
and
evaluate
it.
You
want
to
make
sure
you
have
no
duplicates
in
your
chain
set.
C
We're
gonna
display
it
so
out
of
some
700,000
records
that
ended
up
in
the
in
the
customer
dimension
that
10%
is
70k
there
and,
and
here
here's
the
grand.
So
now
you
know
here's
the
here's,
a
big
big
thing
that
upset
emerge.
So
we
you
know
we
talked
about
what
we're
going
to
do.
We're
gonna,
do
a
merge
into
a
merge
into
the
customer
dimension
right,
we're
gonna
do
a
match
on
the
customer
key.
C
You
know
from
our
data
set.
So
to
do
that
we
create
a
set.
So
here's
our
chain
set
in
here
now
do
to
do
the
SCD
type
to
dimension
the
very
eighth.
The
variant
that
we
picked
was
between
the
I,
I
and
III
type.
But
what
we
have
to
do
is
we
select
the
chain
set
and
we
we
mapped
the
customer
key
as
the
merge
key
on
this,
and
you
can
do
this
with
multi-part
keys
and
other
things
like
that,
and
then
we
go
to
Union
that
we're
gonna
start
matlynn
old
has
a
merge
key.
C
The
first
one
is
used
for
updates.
The
second
one
is
used
for
inserts
now
by
putting
this
all
under
to
merge
into
both
of
these
that
update
and
insert,
or
the
absurd
is
done
under
one
transaction
at
scale.
You
know
whether
it's
750,000
records
or
or
750
million
records.
You
know
it's
all
done
under
one
transaction
in
the
Penn
State
Delta.
So
now,
what
are
we
gonna
merge
on?
Well,
the
customer
key
is
that
you
know
this
update.
C
So
if
I
run
this
and
then
run
it
again,
I
don't
want
to,
you
know,
create
unnecessary,
churn
and
add,
add
more
records
than
I
need
to
so
I'm,
adding
this
to
make
sure
that
it's
adding
both
on
there
now,
however,
all
right
so
then
you
do
your
update.
You
set
your
is
current
to
zero
and
you
end
date
that
record
so
I've
just
done
the
update
there.
You
know
and
that's
when
match.
So
when
your
keys
match
you
do
an
update.
I
says
now
when
not
matched.
C
Let's
say
you
have
a
brand
new
record
right
or
your
brand
new
record,
because
you
know
some
of
the
attributes
of
chains.
You
do
an
insert
and
we
have
this
little
shortcut
here-
insert
stock,
which
means
you
don't
have
to
list
out
all
the
fields
in
your
chain
set.
You
know
as
long
as
your
change
that
schema
lies
matches
or
your
your
target
table.
C
You
can
just
put
the
star
in
there
and
makes
things
a
lot
easier,
so
you
run
that
that's
a
lot
of
code
to
look
through
and
you
know,
I
went
through
and
here
you
can
see
in
the
transaction
log
all
that's
captured,
and
you
know
operational
statistics
about
what
happened.
That's
all
captured
I'm.
You
know
I
I
like
to
I
like
to
test
my
code,
so
I
left
some
of
that
in
here,
but
you
can
see
the
quote.
C
Unquote:
older
original
version
there
all
is
current,
had
seven
hundred
thousand
of
them
in
the
new
version
about
seventy
thousand
of
those
have
been
in
dated
in
in
the
in
the
quote:
unquote
new
version,
63
630,000,
went
untouched
and
and
and
about
seventy
thousand
were
inserted.
No,
and
so
those
are
all,
statistics,
do
a
final
check
of
all
current
records.
You
know
we're
just
resegmented
customer
base,
so
you
expect
700,000
there
and
again
big
data
stuff,
so
you
know
visualizing.
C
C
B
Yeah
definitely
I
mean
I
think
what
you're
doing
here
is
absolutely
providing
that
context
and
then
so
related
to
that
right.
You're,
noting
this,
the
switch
over
the
sports
right
that
this
segment
is
sports,
and
so
that
environment
leads
me
to
get
asked
a
completely
random
question
here,
which
is
what's
your
favorite
sport?
Well,.
B
C
We
we
recently
discovered
this
that
we're
both
into
Rocklin.
That's
right
and
Denny's.
Told
me
some
amazing
things
about
rock
climbing,
around
Seattle
and
and
one
of
my
favorite
places
out
here
in
the
East
is
Acadia
Park
climbing
over
to
see
cliffs
and
whatnot.
So
you
know
what
once
this
kovat
thing
is
is
is
over
with
we
hope
to
visit
each
other
and
go
climbing
outside
so
yeah.
B
B
C
A
C
C
So
because
we
did
this,
we
can
do
a
you
know
a
quarter
over
quarter
week
over
a
week
comparison
I'm,
doing
kind
of
a
you
know
because
a
date
said
it's
decades,
but
I
with
this
I'm
able
to
trade
a
before
and
after
view
of
the
data.
That's
now
in
my
customer
dimension
right,
and
so
this
is
what
you
would
normally
do
in
in
a
data
warehouse.
You
know
doing
that
dimensional
analysis
and
and
doing
that
time
based
analysis.
So
so
this
is.
C
This
is
a
typical
data
warehouse
query
in
here,
and
this
is
the
visual
now
with
Delta,
Lake
and
time
travel.
You
can
see
that
I
can
look
at
transaction
one
that
we
showed
a
little
bit
before
and
and
I
look
at
the
original
transaction,
the
load
of
the
data
and
and
and
I
map
that
to
old
and
new,
and
you
get
the
same
results.
C
Does
that
make
sense,
so
we
can
look
at
this.
Two
ways
verify
the
transaction
now,
as
you
run
this
daily
or
hourly
or
whatnot,
on
into
the
future,
you're,
ultimately
going
to
vacuum
this
this
table
and
remove
all
those
previous
transactions
continued
up,
and
so
that's
an
administrative
command,
and
you
know-
and
so
it's
best
to
use
this
square
here-
the
real
data
warehouse
query
in
there
now
for
a
short
period
time
under
that
administrative
theory.
You
can
certainly
use
this
to
understand
what
changed
and
say
last
night's
load.
B
Makes
sense
absolutely
so?
Actually
let
me
chime
in
bet,
there's
actually
a
question
which
I'm
in
the
halfway
answering
so
as
to
call
out
that
Douglas
is
doing
a
awesome
job,
calling
out
the
fact
that
we
have
this
ability
to
do
dimensional
modeling
within
data
Lakes
because
of
Delta
Lake,
because
Delta
Lake
has
acid
transactions,
which
is
pretty
sweet.
But
the
key
context
is
that
you
know
we
can
do
it,
but
obviously
we
don't
have
all
of
the
advantages
of
the
enterprise
data
warehousing
world
in
Delta
Lake.
B
Yet,
but
that's
okay,
so
one
things
that
we
would
definitely
suggest.
All
of
you
is
that
go
to
the
Delta
Lake
github
create
an
issue.
We,
as
the
Delta
Lake
community,
actively
look
at
all
the
the
the
requests
coming
in.
So
we
can
go
ahead
and
understand
better
what
you
do
or
do
not
want
to
work
with
right,
and
so,
if
we're
seeing
a
lot
more
people
saying
hey,
we
love
dimensional
modeling.
We
really
want
some
of
the
features
of
dimensional
modeling
within
Delta
Lake.
We
get
enough
of
the
community.
B
C
And
you
know
coming
from
a
Hadoop
background
last
eight
years
and
helping
customers
optimize
or
spend
on
their
data
warehouse
and
actually
move
all
the
ETL
out
of
their
data
warehouse
and
move
it
onto
the
daily
doing
it
with
Delta
Lake
and
the
merge
and
the
transactions
is
so
much
simpler,
so
much
easier
and
faster
as
well.
So
so,
with
Delta
leg
you
can,
you
can
or
actually
was
the
Duke.
You
have
to
shut
down
access
to
your
customer
dimension
to
your
tables.
C
They
want
to
optimize
their
spin
or
scale
up,
or
they
kind
of
want
to
avoid
all
the
hops,
because
you
know
this
data
this
this
or
this
clean
gold
customer
dimension,
you're
gonna
need
it
for
reporting
and
dashboards.
You're
also
gonna
need
it
for
your
AI
and
ml
to
do
that,
because
that's
what's
gonna
boost
the
next
round
of
profits
and
enterprises
is
that
AR
and
ml
does
that
looks
into
the
future?
The
dashboards
and
reporting
looks
backwards.
B
Perfect
so
I
believe
we
have
time
for
about
I'd
say
about
five
to
ten
minutes
worth
of
questions.
Look
right!
So,
let's
dive
into
that,
because
we
want-
and
so
I
answered
a
bunch
of
them
online,
but
there's
a
bunch
of
that.
I
have
not
yet
had
a
chance
to
type,
because
if
I
was
typing
right
now,
you'd
hear
my
my
mechanical
keyboard
clicking
away
and
then
nobody
hear
anything.
So,
let's
start
we
got
a
great
question.
I'm,
pretty
sure
you
have
your
perspective
and
I
have
my
perspective.
C
B
C
B
Had
a
little
more
time
to
actually
look
at
this,
so
fair
enough,
so
let
me
dive
right
into
it.
So,
yes,
there
actually
is
a
potential
performance
impact,
because
when
what
you're
thinking
about
is
that
time,
travel
is
great
for
a
short
term
like
segment.
Okay,
in
other
words
like
30
days,
90
days,
things
of
that
nature,
and
so,
when
you're,
looking
exactly
as
Douglas
remark
here,
you
definitely
want
to
be
able
to
use
time
travel
because
it's
great
for
debugging,
it's
great
to
see
market
changes
that
happen
recently.
Things
of
that
nature
right.
B
But
what
ends
up
happening
is
that
don't
forget
time
travel
what's
going
on
underneath
the
covers,
and
we
actually
have
a
bunch
of
other
YouTube
videos
specifically
on
this,
especially
the
one
that
says:
diving
into
Delta
Lake
unpacking
the
transaction
log.
What
happens
with
time
travel
is
that
there
basically
is
a
duplication
of
all
the
records
for
all
intents
and
purposes,
okay,
so
every
time
there's
a
new
version
of
data,
there's
a
new
set
of
data.
Okay,
that's
sort!
B
It's
it's
a
little
bit
more
complicated
that,
but
for
all
intents
and
purposes
the
ideas
are
more
files
underneath
the
covers,
because
you
have
more
files
of
the
native
covers.
You
don't
want
time
travel
to
be
running
too
long
because
it
runs
too
long.
You
end
up
actually
having
far
too
many
files,
hence
the
impact
and
performance
okay.
B
So
there
are
definitely
some
advantages
to
basic,
saying
yeah.
Well,
maybe
I
will
physically
go
ahead
and
manually
recode
the
sed
to
myself,
okay
and
it's
not
to
say
that
you
don't
want
to
use
time.
Travel
like
I,
said
the
four
specific
scenarios.
Time
travel
is
absolutely
a
massively
good
idea
where
it
comes
a
player's
more
like
if
you
want
s
EDD
to,
but
you
want
to
be
able
to
look
at
the
changes
that
happen.
B
Let's
just
say:
geographic
information,
customers,
don't
change
geographies
often,
but
they
will
change
over
like
a
10-year
20-year
period
right
if
I
want
to
go
back
20
years
in
time,
you
probably
don't
want
to
do
that
as
a
time
travel
trick.
You
probably
want
to
do
this
as
a
manual
coding
sed.
So
it
really
depends
on
your
scenario
and
often
I,
see
customers
where
they'll
do
things
slightly
differently,
depending
on
the
type
of
formation,
because
some
of
its
also
very
temporal.
B
In
other
words,
you
need
to
look
at
history,
but
you
only
need
to
look
at
that
history
for
short
period
of
time
time.
Travel
is
great
for
that
versus
no
I
need
to
look
at
those
history
for
a
very
long
period
of
time.
Okay,
ICD
coding
it
manually
how
it
makes
a
lot
more
sense,
so
at
least
that's
my
context.
Doug
go
for
it.
Yeah
I'm
sure
some
of.
C
You
guys
some
of
you
might
even
disagree
so
which
is
really
cool.
I
I,
like
it
I
like
what
you
said
there
a
lot,
so
this
is
yeah.
So
in
some
ways
this
is
a
shorter
path
to
to
doing
a
query
with
this.
If
you
look
at
this
query,
we're
looking
at
at
the
start
date
end
date
across
the
whole
table
across
the
current
version
of
the
data
and
we're
looking
at
both
active
and
inactive.
C
So
all
the
changes
that
we
reported
in
the
customer
dimension
we're
looking
at
and
unless
we've
kind
of
zo
ordered
this
on
start
date
and
end
date.
We're
really
you
know,
data
skipping
homes,
but
we're
really
looking
at
all
these
days,
probably
the
whole
table
here
and
and
and
we're
doing
that
twice
with
this
type
of
query.
Now
we
wouldn't
have
some
optimizations
that
kick
in
here,
but
we're
basically
looking
at
the
whole
table
and
all
of
its
twice
in
this
query.
C
If
you
look
at
this,
what
it
should
be
doing
on
the
cover
is:
go
to
the
Delta
log
transaction
table
find
the
zero
zero
zero
zero
dot
JSON.
That
Denny
mentioned
that
was
covered
in
the
previous
Chuck
Jack,
get
the
list
of
all
those
part
key
finals
and
then
pull
that
back
and
the
same
with
this.
So
in
a
way
we're
just
scanning
two
versions
of
the
table:
yeah
I,
guess!
Actually
you
know
as
I
talk
to
myself
in
this.
C
This
query
here
is
really
scanning
version,
one
of
the
entire
table.
This
query
here
is
scanning
both
versions
of
the
table,
so
this
is
this
is
ultimately
gonna
read
twice
as
much
data
right
yeah,
so
so
that
that's
that's
a
one
downside,
so
I
use
this
for
more
operational
concerns
like
like.
If,
if
you
had
a
bad,
if
customers
complaining
that
numbers
are
wrong
and
they're
like
what
happened
last
night
on
the
load,
go
look
at
this.
C
A
C
They
they
look
at
using
age
base
to
do
these
merges
and-
and
if
you
remember
the
HBase,
know
sequel,
it
would
simulate
updates
as
well,
but
it
also
will
keep
additional
versions
of
each
value
again,
that
was,
that
was
sensitive
to
administrative
commands,
wiping
out
the
previous
versions.
There
was
a
nice
trick
to
do
updates,
and
you
know,
even
though
HBase
generally
had
you
know,
because
of
water
was
stealing
maybe
four
times
less
I/o
for
a
whole
table
scan.
You
know
for
certain
cases
it
was
faster.
So
again
it
it.
It
depends.
C
B
Okay.
These
will
give
you
the
technical
context
behind
a
lot
of
what
Douglass's
has
been
talking
about
today.
But
in
addition
to
that
specific
to
the
question
of
an
incremental
load,
all
of
them
use
the
examples
of
incremental
processing
a
la
structure
streaming.
So
the
idea
of
when
you're
processing
data
and
loading
it
into
Delta
Lake.
The
idea
is
that
you
can,
even
if
it's
not
traditionally
a
streaming
system,
for
example,
it
is
a
file
that
gets
dropped
in
every
few
minutes
or
whatever
else.
B
You
can
then
run
structure
streaming
with
trigger
once
that
trigger
once
it
allows
you
to
go
ahead
and
then
still
basically
look
for
the
file
streaming
in
load
it
into
Delta,
Lake
anyways
and
with
all
the
asset
transactions
compliance
all
those
other
cool
features
that
are
included.
So
that's
the
most
typical
context
of
how
you
do
incremental
loads.
C
C
What
billion
rows
multi-building
rows
right
now?
You
don't
want
to
necessarily
reprocess
all
the
billions
of
rows,
if
say
only
a
hundred
thousand
of
those
chains,
so
you
want
to
figure
out
what's
different.
Now
you
can't
do
a
you
can
use
the
merge
into
and
then
when
not
matched,
you
know,
and
you
said,
set
the
primary
key
to
be
that
you
know
your
matching
key
here
would
be
your
your
your
business
identify.
We
start
that
in
date,
right
and
so,
when
not
match
to
you
and
insert
now,
you
have
just
the
changes
that
you've
received.
C
You
know
changes
based
on
imagine
women
by
McKee,
but
which
is
awesome,
and
you
can
go
back
to
this
query
with
the
version
numbers
and
you
can
see
exactly
which
records
have
changed
and
then
you
can
run.
You
can
update
all
your
downstream
analytics
that
are
based
on
that
that
business
dimension
table
and
what
might
have
changed.
You
know
if
you
need
to
rerun
your
your
scores
on
the
business,
but
only
on
the
businesses
they've
changed.
You
can
find
out
what
that
is
through
this
query.
C
You
know
that
that
Delta,
you
can
write
to
a
Delta
table.
You
can
have
a
stream
reading
off
that
Delta
table
it'll
pick
up
only
the
changes,
so
you
you
know
you
know
on
that
on
that
second
table
there,
you
don't
even
have
to
run
the
query
like
this,
your
yours,
your
structured
streaming
reading
from
Delta
as
the
Stars
will
automatically
pick
up
all
these
changes,
which
you
know
again,
can
greatly
simplify
your
Indian
pipeline
speed.
It
up.
C
B
So
hey
well,
time
is
up
for
us.
We
want
to
be
cognizant
every
time
we
have
a
lot
of
other
questions,
so
I
apologize
that
we
could
not
answer
all
of
them.
So
I
encourage
you
to
go
to
the
data
bricks
YouTube
channel,
where
this
video
is
actually
sitting
right
now,
because
you're
sitting
there
then
you're
able
to
go
ahead,
and
we
actually
do
answer
questions
so
take
your
questions,
whether
we
already
answered
them
not
doesn't
matter
prop
them
right
onto
the
YouTube
channel,
we'd
love
to
go
ahead
and
answer
them
live.
A
Great
Thank
You
Denny.
Well,
thank
you.
Everyone
for
attending
today.
It
was
really
exciting
to
hear
Douglas
share
all
his
knowledge
on
on
these
topics
as
well,
as
you
know,
Denny's
great
questions
and
sharing
his
own
knowledge
as
well.
But
if
you
want
to
hear
more
feel
free
to
like
like
then
he
said
subscribe
at
the
YouTube
channel
and
also
don't
forget
to
register
for
the
spark
and
AI
summit
where
they'll
both
be
speaking,
and
there
is
a
discount
code
for
25%
off
training
as
part
of
the
spark
AI
summit.