In this Deep Dive, Frank and Andy delve into the world of Data Warehousing, what is it and do they know things? Let’s find out!
Frank also shares that he has a new role at Microsoft.
AI Generated Transcription
Hello and welcome to data driven,
the podcast where we explore the emerging field of data
We bring the best minds in data,
software, engineering, machine learning and artificial intelligence.
Now hear your hosts Frank Lavigna and Andy Leonard.
Hello and welcome back to data driven.
The podcast where we explore the emerging fields of data
science machine learning an artificial intelligence.
If you like to think of data as the new
oil then you could consider us like Car Talk.
However, we can’t go on a road trip because of
the Corona virus lock down.
So it’s just Andy and I kind of stuck at
And thanks to the Magic of Technology we can be
on the show at the same time.
And, uh, how’s it going?
Andy? It’s going
well, Frank, how are you doing?
Good, good, uh, you’ll
probably hear my kids in the background.
will, and you know what Frank,
I think it’s fine. You know I’m going to.
I understand why you said the word stuck with you
and I work remotely an awful lot.
We usually record like this.
There’s there’s less in the background.
It’s your place most of the time,
but you have couple of young boys there and you
need to be in the room with them when mom
who’s also working from home is you know is doing
some of her work so kudos to you to both
of you for finding a way to manage this.
Everybody’s going through these sorts of things and I’m sure
that none of our listeners will mine here in your
sons play in
the background or hopefully won’t start fighting so that’s Well,
I asked, I asked if they do I think a
lot of folks
can relate though. Yeah, oh absolutely,
so. We’re recording us on April 16th.
We Speaking of kids, we had your son on which
if the order of recording goes the way I planted
in my head.
That would have been released last week.
And Uh, which I thought was a pretty good,
uh, discussion on. How stem is taught?
How stemmers perceived by quota quote policymakers?
And how the actuality of it is?
And some of the interesting stuff your son is doing
with Raspberry Pi and stuff like that.
I was a I was first I was very proud
You know the work that he’s doing and he’s he’s
had his his hands in machine learning for really a
couple of three years.
Now I want to say he was 14 and I
came into his room.
You know just checking on say something or something I
A Mario Brothers playing in the background.
Like what do you think you know he was?
He he had done his school work?
He was home schooled at the time he done his
So you know what he wants.
But um, later talking to him about it,
he said he actually came and got me and he
OK, dad, it took, you know with I think it
was like 6.
You know neural nodes. Here he was able to,
Mario was able to figure this out and something like
4 hours or something you know later he said I
wonder what it would be if I added a note.
I wonder what that would do to it and I’m
kind of sitting there with my mouth hanging open.
Going show dad more about that nice,
but he’s been doing it for awhile.
I know your kids are interested in the same thing.
They’re younger Stevie 17 now and you know.
and I know that your sons are coming up in
In this age as well,
they are mentioned Mark Tapatio in that show as he
referred to digital natives.
They are digital natives and yeah,
that comes with some pretty interesting stuff.
So I’m just glad we were able to record that
show as he gets ready for his first sequel Saturday
presentation here on that topic.
So and that’s all assuming that we were able to
overcome the technical glitch.
We we learned something, Frank,
I’d learn something. Yeah,
it’s not a glitch. If you learn something.
So if if for some reason.
The you know what hit the fan then that episode
will be recorded at a future date,
so we’ll see it will,
but we’ve got. We’ve got
a great topic today. You and I’ve been bad this
around I want.
I know it’s been several weeks.
It may have been a couple of months.
We’ve been talking about doing this.
Right absolutely, and part of what motivates this?
An based on the release schedule that I anticipate this
will have already happened.
I’m changing jobs at Microsoft Woo.
At your new job. I will be the data and
the AI technology architect at the Reston MTC or Microsoft
so congratulations. Thank you very much.
It’s an honor to join such a prestigious team.
If you’re not familiar with what the MTC is.
MTC is a Microsoft Technology Center.
There is about 80 of them around the world,
and they basically are meant to provide specific experiences.
Ends well as architecture design guidance for customers around the
world and it’s an honor to be kind of in
It’s very rarely does an opening happen in an empty,
so when one opened up in my neck of the
Woods is like I have to take it.
I have to at least try.
Right right? So Fortunately I am super excited.
And Uhm, ’cause That’s what we say at Microsoft were
super excited and it’s a great team.
Great stuff that they do.
They do a lot of work with the community.
They do a lot of work with customers.
It’s just an awesome gig.
I’m really looking forward to it and.
Yeah, I’m really excited about
it. Congratulations brother. That’s a great thing and I think
you’re perfect for that job.
I know, I know, someone else in that job at
an MTC in the northeast.
And it’s it’s kind of a rare breed of person
that has to walk into that role because.
It optimally you have a smattering of exposure to all
whole slew of enterprise architecture,
an both both you and this other individual that I
know fit that mold.
You’ve got programming experience, software development experience,
and you also have data experience,
and it’s just rare to be good at both of
those things I know,
but I know you’re good at it,
and I know my other friend is good at this
so I just I just think it’s going to be
a great fit for you,
Frank. I’m I’m excited, you got
it. Thank you. Thank you very much.
So with that, one of the things that I’ve been
ramping up on in anticipation for this job or whatever
opportunity I was going to go to next.
I was learning more about the quote Unquote traditional side
of the data world,
which let me move kind of explain my little worldview,
which is twisted and as weird as it may be,
it might actually be right.
I see this alot in my current current or old
roll current as of April 16th.
Role is that we have data in the I cloud
but there’s a very clear line of demarcation between the
Part of the data in the icy essays and the
sequel veterans side of things.
So I actually had a call this morning where it
It was very, very much laid bare ’cause we were
talking about that and that there’s essentially kind of two
types of data in AI folks at Microsoft for sure,
probably everywhere else, to you have the RDBMS folks.
These folks have been doing sequel since it was aside
based joint venture,
right, right? That’s their world.
Ann, you have kind of the big data open source
kind of tooling world,
right? The folks that are more comfortable in spark or
Hadoop or with the crazy statistics and math around machine
learning and AI,
right? You kind of have those two.
the two. Rarely do you
have a person who’s. Comfort,
Rible and happy in both.
I am aiming to be happy and comfortable in both.
Obviously I’m more in the data science kind of world.
And part of my part of what I see is
the opportunity in this new role is to grow into
the kind of the sequel.
RDBMS traditional database world. That makes
sense. They are no. It makes perfect sense.
and I mean coming at coming at this from,
you know, we as we shared in each show the
past few days that we’ve recorded.
We’ve known each other for like 15 years.
And most of that time you were a professional software
You are a Microsoft MVP in.
I forget which discipline it was.
Frank, I know it was software development related.
the world has forgotten that this discipline never existed.
PC. Tablet PC right? OK and you did an awful
lot in there and I know there’s a lot of
people out there working in what that evolved into mobile.
That benefited from the blog post you shared,
solutions. You shared an all of that,
but yeah, that whole mobile thing turned out not to
be such a,
you know, it was a trend it and it evolved.
To what it is now,
and having that experience, I think you’re going to find
that that plays well into kind of backfilling like you
or filling this other bucket that you want to go
which is traditional T SQL an.
I know, I know, from experience and dabbling in machine
learning and AI.
I’m on the opposite side of the fence,
although I’m not really that good at,
you know. Let’s say like DBA level T SQL,
but I you know I can hold my own in
but if we are. If we’re selling tuning performance tuning
to a client.
I may be involved in the project,
but rarely am I the person actually performing the tuning.
There are lots of people out there that we subcontract
as a as a consulting firm,
enterprise data and Analytics. We bring others in who are
better at that much better at that than I am,
and we have people on the team who are much
better than I am as well,
but it’s. I think your experiences has his set you
up really well.
To make this transition and it will like everything else
We talked about this in the other shows.
It takes time. And it’s frustrating,
but I think you’re well positioned to pick up this
skill as fast or faster than almost anyone else I
know just well,
thank you now you know part of it,
I’m not. I’m not completely like naive to the ways
I took sequel in college,
database design and college and my professor worked with card
So you know, like. You know,
I’m only two degrees of Kevin Bacon away.
From the founders of the theory,
so you know that’s going for me,
but I never really got into just kind of the
nuts and bolts of it,
and I’m not. I’m not concerned about that.
I’m actually fascinated about it,
because it’s just another way to solve the same problem.
Absolutely. Ultimately, at the end of the day,
you’re moving bits around, and it’s a question.
What’s your philosophy? Or obviously,
RDBMS has a philosophy and it you know I’m not
I mean, it worked well for 5060 years.
But now we live in a world where there’s a
lot more unstructured data.
And how do you deal with that?
And how do you deal with it now that you’re
not making assumptions about spinning
disks, right? Right there’s a whole.
’cause we haven’t hazard.
Yeah who talked about that on our show that yeah
there still leaves it’s 2020 and I would say still
most of our code is designed for that age of
the heads picking up seeking a sector an reading data
and then picking up again.
So there’s there’s a whole new opportunity where obviously relational
databases are going to still matter,
but it’s just one of many tool sets.
In fact, one of the things that I learned when
I was doing start up with angelism for Microsoft was.
You know, having debates with startup founders who UR?
I will say I put them in a hipster category,
right? I worked with when you work with startups runs
the gamut between really like I mean like that this
person is going to be the next Steve Jobs to
this person is kind of like I think they’re living
in their parents basement,
but rather than seeing unemployed they haven’t so somewhere in
the middle you kind of what I have.
The hipster ones where they learned code because of make
Now that’s not nothing wrong with that,
but do you think that you’re an expert in all
things technology because you learn to code?
Right, you know, and then you go to a person
that is supposed to help you take your stuff to
the next level and kind of talk down to them.
So right context this conversation.
So they were basically lamenting the fact that they wanted
They wanted to have the reliability of.
Up an RDBMS, but they wanted to do it in
a note SQL type of environment.
An I was like that’s
a fair. You know that’s a fair thing to want.
I’m just all cards on the table.
Approaching that, architecturally, that’s that’s not an unreasonable request.
But unless and until you get into the engineering part
And that’s where you start to see that you just
can’t have everything that you want.
I mean, there’s no single do it all type application,
everything, every software application ever.
And I’m going to maintain,
probably forever. They’re going to be applications.
There’s going to be some spot that I define as
It’s something that the application or server or what have
you doesn’t do well.
And what you’ll often find is there’s some other application
out there that’s available,
or some other platform, and it will do that part
But again, that also has its corners,
and So what you’re trading is pain.
The nicest way possible. You’re picking your picking your poison,
picking your pain. What is it that you want to
And it depends on. You know.
Relational databases have their pain points.
No sequel. It turns out a lot of companies have
learned this over the past few years.
Also has its pain points as well so.
You can’t always get what you want,
but if you try sometimes you might get get what
Awesome. So, so I
mean part of it is,
you know, sometimes whether it’s technology,
anything else, you have, kind of these dueling philosophes an
there is a point where they just won’t meet just
They’re they’re kind of philosophically opposed an you’re right,
you have to kind of pick which one you want
to have over the other.
And there’s cause and effect to that.
So with that kind of deep philosophical you were data
so that’s good. So I wanted to talk to you
We want to do a deep dive.
It’s not officially a deep dive until I have fun
with my soundboard there.
Into data warehousing, what is data warehousing?
Where did it start? I’ll channel A little bit of
What is data warehousing? What do they know?
Do they know things? Let’s find out.
Well, yeah, data
warehousing in my opinion in my experience is really this
idea of of collecting data from all over different places
and placing it into a centralized location.
Now there’s some distinctions and there’s other scientific answers to
and you can actually build something that today is not
considered a technically a data warehouse.
You can gather all of the information that spread across
the enterprise in different places.
Into what’s now called an operational data store.
Ann, it’s not totally unlike a data warehouse.
In fact, I think the Euler diagrams have quite a
bit of overlap for that,
at least if we if we kind of improve or
add to the word data warehouse or the term data
warehouse with relational data warehousing,
there’s a lot of overlap between relational data warehousing and
operational data store.
Wanna confuse that really with our listeners?
But I just want to make you aware if you
hear oh DS or DW or EW.
It could be that they’re talking about largely the same
And when you think about like you think about supply
which is a topic on everyone’s mind these days as
we’re talking about the economic impact of the pandemic.
Supply chains are where really where really way more important
than we realize and it’s kind of like oxygen or
You don’t recognize how important it is until you don’t
An supply chains are like this and you could think
of a data warehouse.
In that terminology. The analogy holds for quite a bit,
and I’m going. I’m just going to use Walmart and
Amazon as you know,
is kind of examples of this.
They both have these distribution centers and they have these
network set up all over the United States,
probably all over the world and its places where the
goods come from the source and they’re trucked into.
You know, they may be collected at other points along
But they’re trucked into these large,
physically large warehouses and then stocked.
And then from there there actually shipped out to in
the case of Amazon.
Usually there handed off to some delivery service.
In the case of Walmart,
they’re placed on other Walmart trucks that are shipped to
The actual brick and mortar stores and that warehouse in
That distribution center. That’s what I think of when I
think of data warehouses.
I think of the the electronic equivalent of that because
there’s all of these. You’ll see especially at what I
consider an EDW enterprise data warehouse.
You’ve got a collection of companies that have been acquired
in mergers and acquisitions,
and they’re looking at. I want to get all of
But they have and want to bring that into this
and that I want it there for a number of
But one of the big reasons is so I can
query that data and I can learn how my entire
How’s it working? And. And in that,
and now if I apply that Walmart Amazon analogy to
that to the data there,
they reports that come out of querying a data warehouse.
Those are like the end user customers for Amazon,
say and like the Walmart stores in that other analogy.
So it’s and it really turns out Frank.
It’s always good to to answer the question what’s the
problem we’re trying to solve?
And here’s the problem. Most people are trying to solve
with an enterprise data warehouse.
They want to know how’s my business doing today.
And if they can answer that question,
that’s going to be answered by a report of some
kind or an analytics dashboard.
Still a report. In my mind,
it’s in the best analogy is something we’re used to.
It’s a stoplight. All we green everything is good or
Are we yellow? Some things are in trouble,
and most enterprise data warehouses during the pandemic are going
to be showing yellow or some red.
We’re in trouble. It’s going to be very few showing
green and but that’s the kind of thing that you
You want to know at the very highest level are
Are we bad or are we somewhere in the middle?
And of course, you want to always be able to
Especially things are in the warning state or in the
You definitely want to drill in.
See what the problem is.
Pick up more information about that,
but at the very tippy top.
You want to be able to answer that question.
How are we doing
today? So I have two questions.
Uhm one is you mentioned that with mergers and acquisitions
you want to have one place for all the data
to the live?
Sure how is how is it data warehouse different than
a data Lake?
a That’s a really good question,
so you can think about data lakes being a similar
So from a purely functional standpoint,
my experience with data lakes I’ll share is limited.
So that you know to take this with a grain
but I consider data lakes to be more of just
a a collection of the data.
A copy of the data in its raw or raw
and when I draw a line between what I call
a data warehouse and what I call an operational data
store there more on that side of the operational data
store. There more of a copy of the data from
wherever it’s come from.
Now I know for a fact working with people who
work with data lakes that they see that a little
An I’m not going to speak for them.
But I just know that my definition is there definition
differs a little bit because you can achieve an awful
lot of what an enterprise data where warehouse does for
you in delivering those results that answer the question.
How are we doing today?
You can achieve a lot of that just going by
querying directly querying the data in a data Lake and
the same can be said for an 0 DS so
don’t. Don’t don’t you know,
trying to mix and match here and draw some distinctions.
I wish I could share with you the picture in
But maybe like to show notes,
so do our best. So what is that?
of the things is that someone on my side where
I kind of see.
I see things for more.
The data Lake POV just want to place the for
stuff to land weather that’s coming,
streaming data, iot or from various types of data stores.
You just want to place to put this stuff absolutely
so one of the things I’ve seen in architectures is
that at least kind of in more modern architectures,
because historically I think data warehouses have been used like
but one of the things that at least data warehouses.
Half they still assume a schema,
right? Like you still have to have a schema right
for it to be in a data warehouse so,
but so if you have completely unstructured data.
Uh, you still dealing with the primacy of the schema
rise at that.
is an you know what I was going to share
is just thinking through this a little bit more.
You know, one of the distinctions that I’ll make from
certainly from a relational data warehouse.
Between that in an operational data store is,
you know, there’s a little bit of hybrid here,
and let’s just kind of.
I’m going to take Cody S out of the picture
for just a minute.
I’m going to talk about staging from an extract transform
and load or ETL.
Or data engineering data integration,
whatever you want to call it,
our perspective. Whatever the format that date is in,
when it starts its track into into whatever we’re going
to end up with with an EDW.
When it begins that the very first thing I like
and that is an area where I specialize is going
to get a copy of that data into some central
Now it could be that that central location is is
a data Lake Ann,
often in modern data warehouses they are.
It goes into a data Lake,
but it could also be a relational database,
or it could be a just a centralized collection of
And there’s a lot of things that you can define
but there’s this concept of it at the very first
collecting and staging. It’s all here now.
And I still do that when I’m designing ETL solutions
I get it to that first step that first place
first and I want to get it all there and
I mostly deal still with relational data warehouses that go
to tabular models or cubes.
You know, some sort of analytics solution and you know
when I do that,
Frank if I’m, let’s say I want to load data
from files into a SQL Server.
I load that data, it’s just as as constraint free
so if it exists in the file I want to
read whatever is in that field and bring it into
What we would call a heap in.
In Relational Database management that if there’s any constraint on
it’s some sort of identity column.
That’s just, you know, counting the rows.
Basically enumerating the Rosen that’s there just to make sure
No row is identical to any other Rd.
And that would be the only constraint I would place
And I would do the same for a data Lake.
You know we were pumping data into a data Lake.
I want to get all of the data from all
of the places into this stage first.
So that’s that’s kind of the very first step that
After that, we then began applying what you just said.
We format it into a schema,
we make it make sense the very first thing I
would do if I was staging it into usaia SQL
Server table a heap.
The very next step would be read the heap and
take these these text columns that contain dates an numeric
values and try to fit them into date an numeric
fields. Right, I would make that attempt an if something
happens and it won’t fit.
Maybe one of the date columns has February 30th in
You know, maybe somebody fat Fingered something and put the
13th month in there.
Anything could happen. I’m going to redirect that row and
try to store that data,
but I won’t want to human to look at it
and that could happen in a data Lake.
It could happen in loading heaps tables from anything else,
but the idea is I want to pick up whatever’s
get it into this engin that I can then apply
these rules to like in my very first rule is
as strong typing.
Want to make sure that I get all of the.
Values that are valid out of there and I also
want to mark the invalid values for later.
So and eventually we flow down this to the end
and we get to a place where you know we’ve
got a collection of all of the values we’ve ever
loaded, and we then fit our new data that are
We fit that new data into that prospect,
but not before going through that staging process.
Then some cleansing, and I would first step in cleansing
for Maine is strong typing using.
There’s a second step that minimally minimal of another step
Apply soft rules so if I’m loading claims data for
an insurance company and I’ve gotta clean initiated date.
I may have a claim closing date or settle date.
I want to check a software would be the claim
8 is greater than the claim initialize date.
You know stuff like that.
That makes sense. So kind of like common sense rules.
So so I guess you said you want to remove
So that probably plays into the fact that you hear
the term denormalization being used a lot is that.
Is that what they’re
referring to somewhat? Usually I applied the normalization when we
get to the end of a relation of funnel here,
where I’m loading ETL stuff and what I’m after there
is is flattening out the data so that it’s ready
for the consumption.
In these reports that tell me how’s my enterprise doing
And you get various various and sundry opinions about D
If you think about third normal form something you mentioned
the premise behind cards rules of.
Normal forms at normalization is that one of the principles
is you only want to have data live in one
place so this value you want in a single location
you want to copy it in 14 different places or
even even 2 you want to be able to keep
it in one location because if that data needs to
be changed or if we make a change to it
we want to go hunt it down.
Here and also also one
of the Canonical examples is an address right so I
think this is really to me anyway this underscores kind
of what’s why the 2 philosophies on that.
Are important and why they’re both compatible in a sense
so if you have an address if I if I’m
more like an online transaction?
Platform right OLTP. Or processing that’s what the TV stands
for not toilet paper although people supporting it.
But but but if you’re dealing with like a real
time transaction you want to know the customers current address
but if you want people kind of.
Now go ahead
but if you want to know historically if you’re doing
reports on where customers have sent stuff or what their
dresses have been then you would want to buy definition
you kind of have to store those address in more
than one place that this order was placed On this
date and will send to that address as opposed to
this is where customer ex lives today they live in.
And it’s a great example an there’s yet another use
case some people own multiple homes and so they have
multiple current address is and this is where you start
getting into things kind of getting interesting I’ll say it
that way in data warehousing but that’s if you look
at the percentage of all of the people who have
an address in the world most of them by far
have a single address at a time and they go
through this but you do definitely have to account.
For those customers who may have something shipped to say
one location and kind of like going back to Amazon
this is happened to me I own you know I
have one address only one home well me in a
mortgage company but I have been on vacation and ordered
from Amazon and had it shipped to the address where
I was you know overnight.
There as well so it’s while it look this is
one of the things that looks simple that you know
it’s not so simple and a friend of mine and
coauthor tell Mitchell he’s ETL specialist San Tim actually blog
he wrote a really good blog post about you know
how fuzzy some of these concepts can be and I
believe the title was what is a day.
You think about if I called yeah well you know
at tenmitchell.net if you want to check that out and
search for it he brought up some really good use
cases you know what is a dangerous thing sure and
we think that simple no it’s not.
And part of what happens and I kind of skipped
right over this is when I’m collecting data from these
But I’m doing ETL it could be somebody has a
warehouse in Liverpool england and they’ve got a bunch of
warehouse locations in Canada.
And the US in Mexico and maybe even in East
East Asia wherever they store dates in different formats.
Yeah so at I could have what looks like a
13th month right I could have 13 dash 03 dash
2020 you and I know because we work with us
so much that that’s referring to the 13th of March
but in the United States we would reverse the first
2 we would have 03 dash 13 dash 2020 and
one of the one of the goals of data warehousing
or even to DS is to collect that data and
then pick a format it doesn’t matter which just pick
one. And make that perform at your going to use
for dates the same goes for other measurements that you’re
using and and it can go beyond that the weights
in some locations are going if I set up a
something that includes how much something weighs in Germany I’m
probably going to get kilograms but if I do that
in wiscconsin I’m going to get pounds that’s against Wisconsin
nothing against Germany it’s just different.
So we have to convert so that when we do
comparisons on how much things weigh it doesn’t matter which
measure we picked by the way we did it just
needs to be consistent so that we can compare apples
to apples and we’re trying to tell which is heavier
something that weighs 2 pounds or something to weighs 2
kilograms going to get different answers.
No that’s a good point that’s a good point and
there’s all those sorts of things and and you right
when you when it comes to you kind of narrowing
down what do you mean by this and I think
one example that’s not necessarily data related was I was
on a mapping project it was basically for a hotel
chain in they wanted to map out local points of
interest right in San Francisco.
And this was all done in Silverlight Maps and all
that very cool 3 D work although doesn’t work anymore
but take your word for it was awesome.
But uh one of the questions that I had and
I worked with the work.
I work with the manager of the hotel is you
know they wanted to be able to put pins on.
You know various points of interest now for some things
that’s easy but when it came to like a park.
Well where’s the interest of the park where is the
park really like right I think was Golden Gate State
Park or whatever it was like well where is Clark
where do you want that pin to be do you
want to be at the center of the park do
you want to be at the gate or do you
want to be the part that was ultimately they went
with the part that was closest to the hotel so
there was not was a little bit less but I
mean but I mean like you know how you define
where something is precisely.
That’s that’s a you know when that ultimately I think
it should come down to what’s the problem you’re trying
to solve which in this case was you know how
do you get there and sort things so it’s an
interesting problem that data modeling and as well as geospatial
stuff kind of
get into sure yeah it really is and you know
when you start looking at that people could say well
your fudge Ng you know the answer because maybe there’s
only 1 entrance to the park and you want to
show who’s closest and you happen to be closest to.
You know the complete opposite end of the park and
yet if your hotel you may want to advertise Hayward
closer to the park than anyone else even though you
can’t get in that way.
You know it’s not in
this case I am working with them they wanted to
make sure that that it was the entrance closest to
them was like what they
were going for. Yeah totally fair
remember. So go ahead drink now go ahead.
Oh real estate people play this game all the time
like you know they say their their minutes in New
OK 90 minutes 10 minutes.
Yeah you know I once looked at a property back
when I lived up there they said that they were
10 minutes of New York City and it was in
Bayonne and I’m like that’s not possible turns out remember
Staten Island is technically part in New York city so
you were 10 minutes in Staten Island no knock on
Staten Island I was born there so you know respect
to shaolin Wu Tang would say.
Uh if you have no idea what I’m talking about
that’s fine I’ll explain it another day.
But uhm no I mean it wasn’t true yes was
it accurate now yeah but wasn’t meaningful now ’cause most
people wouldn’t say New York City they you assume Manhattan
and right. Now at that was closer to 60 minutes
or 65 minutes which again is minutes away.
It’s just yeah. Now a personal pet peeve
sorry I don’t know it’s it’s accurate and it’s like
you said yeah I play this game all the time
with data the difference between accuracy and truth an and
really in the middle there’s this whole other thing where
it’s the art of communication right an the fact that
the book how to lie with statistics was written in
the Forties. Is a clue not new yeah people have
been playing these games with statistics anan our language and
the exactitude of our language a lot of people throw
off on King James English but it was very accurate
so way more precise than what we use now and
you know there’s all of this science that goes into
literally and into tricking someone into believing what you want
them to believe while presenting non truth using accurate terms?
And one just one of them went Rick is anchoring.
And if you haven’t read about anchoring it is a
you know it’s you can just suggest any number it
has nothing to do with anything else but if that
number is. Is a high number and then you ask
someone to estimate some other thing that it’s been shown
it’s been proven over and over again they will guess
higher? On that estimate and if you then anchor them
with a very low number and do the same trick
you know different group of people may be different day
they’ll guess consistently lower the estimation is is anchored to
the number they previously heard.
And it’s just. It’s a scientific fact about the way
the human mind works,
so. You know you can.
You can see this, you know we see this in
data is probably.
If these days more more so maybe than than before,
is we’re looking at statistics dealing with the pandemic an
Andrea Benedetti, who is as a Microsoft employee power BI
pretty sure he was an MVP before he joined Microsoft.
He lives in Italy and he’s been posting at AKA
Dot Ms Slash Kovid report and he’s been updating this
almost daily with numbers from around the world about that
he he posted about a week or so back that.
When he stops and thinks about the numbers and realises
these our lives,
especially the lives that are lossed It,
yeah, it’s almost what he was saying was like you
almost can’t do this kind of work and keep that
in your mind at the same time you as.
I don’t know the right word for this,
but in my opinion it’s a Noble thing to do.
It’s like. When I hear about jaded doctors and nurses,
my first thought these days is what did it take
to get you to Jay?
Did you know? It’s like if your options are functioning
jaded or not functioning in that role?
Especially in these days Frank?
I’ll take jaded. It’s you don’t understand,
you know it’s really easy to set back and arm
arm chair quarterback and say now you know you should
not disengage your emotions.
You should just do this job and my ex wife
my ex-wife said neonatal intensive care unit nurse.
And you know, I’ve had some insight into what this
can do when someone loses a patient,
especially in infant. It’s crazy hard and to ask people
to just now.
I want you to keep feeling everything that’s like.
I don’t think you understand really what you’re asking folks
And Andrea mentioned something like that.
You know these numbers are lives.
These numbers are people with
two things, one on the numbers aspect.
We’ve addressed this before. And the story is you can
go back and listen to it.
We’ve been recording almost three years so crazy that we
have that much content,
but you have done nothing about collecting data about the
hurricane and all that.
And you know, I kind of set offline.
These are people’s lives, and yeah,
kind of story behind this was when I am a
survivor of the attack on the World Trade Center on
Don’t want to go down that rabbit hole right now.
Right, but I was sharing my pictures that I took
with the with researchers and kind of people that were
doing the fire research over at Nest.
No. There was this moment where I didn’t respond to
the guy in awhile and.
You know he was he he I basically said sorry,
I’ve been to like maybe about 7:00 or 8 funerals
this week because you know what happened and right.
And then he kind of wrote back because,
you know, it’s really hard.
I forget sometimes that when I look at the pictures
of the burning building,
I’m looking at peoples, basically death scene.
And you know he was kind of like the funeral
kind of. Carlo brought him back to that like these
are people’s lives like and you’re right.
I mean, one of the things I did when I
was in college.
I got halfway through college.
I originally went to college to be an engineer chemical
then switch to computer science.
We talked about that in a previous episode of the
humorous aspect of that,
but midway through I thought,
well, when I was, maybe I want to go to
pre Med and I became an EMT and stuff like
that and it’s a hard job because you really have
to. Kind of mentally insulate yourself from what’s happening around
Absolutely the hardest part of that job was really emotional
toll because you can see somebody injured and kind of
think about it.
Almost like a car, like not a living thing like
you have to repair this.
You have to Patch that up.
That kind of helps you do it,
but then like what kind of sticks with you is,
why would one human being being do that to another
and that psychological aspect? So I’ve always had respect for
the medical community because they can kind of do that,
but I think if you know that separation of kind
of what you’re seeing from what it means,
I think is a core survival instinct that I think
all people have to a certain degree.
But the medical people have really perfected it.
and you know the whole concept there.
I think we’re dancing around empathy and being able to
not turn it off,
but maybe turn it down.
So that you could continue to function logically and walk
through the steps,
especially in the case of an EMT and Frank.
I say this being the type of person who cannot
It is not possible for me to do that consciously,
but I had this one experience went through.
Of course I worked in manufacturing back in the 90s.
I was a plant electrician.
We had to go through first aid just in case
and late one night I’m on 3rd shift and we
had a medical emergency occur now.
Keep in mind that at the end of the medical
the nurse registered nurse had delivered this training to us
In such she says to Maine Andy,
if somebody gets hurt, you take a flashlight and go
outside and wave down the ambulance.
Make sure they know how to get into the plant
parking lot because you’re not going to be very helpful
and it they weren’t wrong.
You know just listening to my responses to this,
but here’s this did happen.
Somebody got hurt and they were potentially in a life
threatening situation and.
I guess it was my old army basic training.
First aid kicked in or something,
but as soon as I realize the first thing I
just saw the person I was the first one to
see him come out of the place where they had
been hurt. I won’t explain in detail,
but I could tell they were hurt.
It was very easy to glance at this person to
realize they were hurt bad.
The. I remember thinking Frank,
I’m the only one here who can help.
Right right they rest of the people were working on
the other side of the building.
It was a large facility.
And when once I had that thought,
it was like the entropy entropy.
It was, it was like the you know the emotional
They all, then empathy. They just turned off and I
just went into gear.
And realizing that I was able to help him,
I would say I saved it live.
I don’t think so, but I definitely helped you know,
by getting to the first calling 911 and doing first
So it you know I I’ve had that experience,
so I can kind of relate to just let you
know a thousandth of a percent of what it must
Being in Madison on a normal day.
And these are not normal days.
Now it’s true. It’s true,
it’s uh. Now, God bless people who can do that
and who are doing agreed,
agreed. So, uhm, so one last question on the data
warehouse deep dive.
I think we’ve kind of danced around about the normalization,
but star schema versus Snowflake Schema.
Yeah, so I consider snowflakes to be actually a component
It’s a way to represent hierarchies in a star schema.
You can have a Snowflake Demention.
A great example of that is found in the adventure
where they have products that are have sub categories and
sub categories are related to categories.
When you load that date,
of course you want to start at the top of
load categories first because there could be a brand new
category that has a brand new sub category that has
a brand new product.
If you try to load up from the bottom which
you want to do is you want to kind of
grab these keys that are artificial,
we call them sorted keys.
You want to grab that from the next level up
on the hierarchy,
so you have to load it from the top down
to get that in an.
And adding confusion to this these days is there is
a data database engine called snowflake
and it’s going to say totally
online engin and I am not an expert in it
but I do know just from working closely with someone
who is an expert in it that it’s really fast
for loading large amounts of data as long as you’re
not trying to maintain like updates to that data it
gets painfully slow when you try to update data in
that platform again. There are advantages and disadvantages and the
truth is Frank there’s some data that you never update
like you never hear the weather person say yeah I
know we said yesterday that I was 55 Fahrenheit but
it was really 56 that that never happens.
That data is set and it’s recorded that way whether
it’s right or wrong it’s recorded that way for that
time financial currency exchange rates same sort of thing they
pick a daytime some agency runs the algorithms and bam
you don’t ever hear them changing that that’s not updated
so if you’re loading that kind of forward only type
data. Any kind of platform that’s good at inserts is
going to be really good at managing that type of
data so and there’s other ways to?
Attack updates as well. You can do these transactional things
where you have a from date.
Anna two date appended to the data and you can
Then you’re you know maybe data that is being updated
the way you update it is you put and it’s
from this state.
Time to this other day time.
Maybe it is updated from the last time,
but you’re inserting a neuro every time that there’s an
update and you see this in accounting generally accepted accounting
Double entry. You will entry ledgers there done that way
all the time,
every time. Even if there’s a mistake.
Even somebody goes to the bank and takes out,
you know $200 an. You know they only get 100.
Don’t realize it till later and they have to adjust
that what you’ll see you won’t see them erase the
200 and put 100.
They will add another entry that says there’s an extra
$100 in that account.
That’s the way that accounting works on paper.
And again it’s an update.
But it’s a transactional kind of list or log if
That says no, we just add a new transaction to
fix the mistake.
So it’s kind of like H base.
That way where one state is in the Ledger,
it’s immutable. Absolutely
yeah, that’s a great way.
Much better way to say that then I did.
Jesuit education, I guess. I got I got a bag
dollar words that’s funny. But a lot of ways to
I’m sorry, go ahead. Go ahead.
I was just going to say there’s a lot of
ways to solve some of the same problems that we’re
and we can end up playing to the strengths of
the software that we’re using all the platform that we’re
We always want to do that because speed is King,
especially these days or Queen.
It’s true. That’s true. So what are your thoughts?
Is a light as a longtime kind of data warehouse
What are your thoughts on synapse Azure synapses?
This meant to be kind of the next generation of
SQL data warehouse.
I have seen presentations where I’ve kind of seen the
bigger picture and it’s fascinating,
but what are your thoughts about that?
So I’ve not yet used Azure Synapse and.
Can’t even get access to it and I like to
think I’m one of the cool kids.
I’ve to my to my discredit.
At this point. I haven’t yet attempted to gain access
so I’m looking at the looking at it purely based
on marketing information and we know marketing information is usually
But we had that discussion earlier
about accuracy versus true. There’s
a caveat hanging off that so having not.
They’re sort of hanging on the wall and throwing the
I don’t know enough to really give you experience based
feedback on that,
but what I can tell you from just kind of
a general architectural overview,
I have seen enough information not only from marketing people
buy something from some practitioners,
and the way they are approaching solving the problem they’re
trying to solve it very much appears to be a
Type thing and Frank. I’ve written a couple of books
with design patterns in the in the title,
but one two editions of the same book.
SSI S design patterns and I think about that a
lot when I’m when I’m speaking architecturally,
Ann. I use this analogy to describe just the concept
of design patterns.
You can think about it as like Legos,
right? Everybody, maybe everybody has played with Legos.
I did, and you build that you’re going to build
a Lego wall.
So you put a few legos.
You know, bottom up against each other,
and that’s your base. And then you build your next
layer on top of them an you know another layer
on top of those.
And before you done, you know you’ve got 3,
maybe 8 Legos High. You’ve got this fall.
I picture design patterns that way,
and elegant patterns lend themselves to that sign.
That kind of of. His self similarity just at different
scales and you know,
the more you know, the more you’re able to do
Once you’re able to take these basic functions like storing
reporting data. You know data acquisition.
You know all of these pieces once you put these
That’s kind of like your base bottom part.
But then if you can add a little bit of
automation to each of those,
now you’re building on that second layer,
an maybe on that. Thirdly,
and I think that’s where synapses.
I think it’s. I think it’s at least #2,
maybe third layer where they’re starting to group these together
under a common interface and kind of mask some of
the complexity that happens beneath that.
And I’m totally all for that.
I’m an automation freak, you know this.
And if that’s what it looks like to me,
it looks like they’re trying to simplify this stuff that
could be challenging.
Does that mean challenges won’t exist in it?
Gonna snow? They will, and you’ll still need expert help
in these other areas here.
And you know, in specific areas,
but you may not need it.
You may not need as much as you did before,
So what? what I like about this division of Labor
is you replying expertise only where you need it.
And In other words, another way to look at it
You’re not hiring an expert to come in and do
this very basic stuff for you.
You’ve automated that. So you don’t need to pay an
expert expert wages to do that.
Pretty, you know what we consider kind of menial for
If you automate those types of things away and there’s
a slew of that available in tons of examples,
business intelligence, markup language is one for generating SSI S
Staging is a pretty simple pattern.
Read the data and write it into this heap as
I talked about earlier,
that’s a pattern that doesn’t.
You know you don’t want to pay somebody a Virginian
Dollars an hour to do that.
You get a junior person to do that type of
Even better, you pay someone who has expertise in Bemol
Business Intelligence Markup language and they just build it all
for you through automation.
and I got some wild stories about that.
My biggest anecdote is idea.
10 1/2 months of work in 3 1/2 days.
So it’s that kind of game changing stuff,
but again, as long as you can get,
you know, as long as you create an interface that
masked the drudgery of repetitive work away,
then you only need experts for the places where you
need expertise and that can change the game.
It changes economically and it changes it from a technological
standpoint as well so.
That’s what appeals to me about.
The tidbits I’ve gleaned from listening to people talk about
synapse and looking at a couple of,
you know, a couple of demos.
I’m as an MVP. I get very honored to be
able to do these product group interactions with Microsoft and
attended A at least one of those.
I want to say maybe 2 and we get to
see a little behind the scenes.
Not much. It’s not as much as you think,
but you know we’re talking to the team.
Not and nothing against the marketing people.
They’re doing their thing and God bless him.
We need him. But I’m talking to the engineers.
I’m watching the music, making those decisions about what’s possible.
What’s practical, exactly. You definitely get better or clearer picture
of what’s going on.
See yeah, I
would say different. It’s nothing again.
Marketing is doing its thing and I’m not a marketing
There we go. Yeah, I agree with you and it
is definitely clearer and it makes more sense to me
’cause I’m an engineer so I get it.
But that’s options would say the main nerve.
There we go. And so you know,
in a nutshell, I would say Azure Synapse based on
again my limited exposure to it appears to be one
of these next level jumps and I refer to these
often is tectonic. They’re laying down a whole new layer
you know, and they’re building on like on my my
They’re building the next layer of the wall,
if that’s what it looks like those are.
You know they used to be every decade or so.
We have that sort of stuff,
but it’s accelerating, so now I’m seeing a couple of
three times a decade where we get this new tectonic
shift in what we can do,
and automation that’s built on top of the previous layer
That’s how I see it.
Don’t know if that helps or hurts.
Not definitely helps. Uh, so with that were at the
Uh, just want to point out.
I also have a meeting.
I gotta go onto. OK,
yeah? We had a great session here,
hopefully this answer some of your data warehousing questions and
if you want we could do another deep dive.
And let us know what you think of the show.
If you’re interested in iBooks or not iBooks audiobooks,
audible is a sponsor, so go check it out.
I should probably see if there’s any good data warehousing
books on audible.
Yeah, I don’t know a lot of them have to
I think because
the example I don’t know it would be interesting to
see if there are all.
I’ll do a search if.
You are not already inaudible subscriber and you would like
to get one free audio book on our dime,
or actually it’s audibles time and you go to the
data driven book com routes you to inaudible page if
you get a subscription.
After that they toss us a dime or two and
help support the show.
Uh, particularly given that my monetization strategy has been primarily
Amazon merch, which has been shut down?
Yeah, every little bit helps.
It’ll be back. You spring is still shipping.
Sorry true, very true that design that.
I got in trouble with.
I actually have one printed version on the way so.
It’ll be fun anyway. I’m not going to post it
I’ll just put it that way,
but with that in mind,
um, have a great day and stay safe out there.
Any any other parting thoughts?
Now that’s perfect. Awesome and will let the nice British
Lady and the show.
Thanks for listening to data driven.
Don’t just listen. Become a data driver by going to
datadriven.tv to sign up to join the community.
Access to special events, tips and tricks and more.
Sign up today