Reduce Friction In Your Business Analytics Through Entity Centric Data Modeling

WEBVTT
NOTE
Transcription provided by Podhome.fm
Created: 7/6/2024 1:32:12 PM
Duration: 4374.47
Channels: 1
1
00:00:11.264 --> 00:00:15.445
Hello, and welcome to the Data Engineering Podcast, the show about modern data management.
2
00:00:16.545 --> 00:00:18.380
Introducing RudderStack Profiles.
3
00:00:18.920 --> 00:00:28.665
RudderStack Profiles takes the SaaS guesswork and SQL grunt work out of building complete customer profiles so you can quickly ship actionable enriched data to every downstream team.
4
00:00:29.365 --> 00:00:35.705
You specify the customer traits, then profiles runs the joints and computations for you to create complete customer profiles.
5
00:00:36.489 --> 00:00:39.629
Get all of the details and try the new product today at dataengineeringpodcast.com/
6
00:00:41.370 --> 00:00:41.870
rudderstack.
7
00:00:42.409 --> 00:00:54.675
Your host is Tobias Macy, and today, I'm interviewing Boeschman about the concept of entity centric data modeling for analytical use cases. So, Max, for people who haven't heard any of your past appearances on the show, can you just give a bit of an introduction?
8
00:00:55.190 --> 00:00:59.850
Yeah. For sure. And I I was, trying to think how many times I've been on the show before
9
00:01:00.230 --> 00:01:05.255
and then, how many times we're planning together to get me back on the show too. So this is like a
10
00:01:05.555 --> 00:01:10.055
ace you know, part of a series of multiple dozens of episodes that
11
00:01:10.435 --> 00:01:22.525
we'll we'll be doing together. Little bit of background on me. So I think to this to this, community and audience, I'm probably best known for being the creator of Apache Airflow. So I started this project while I was at Airbnb
12
00:01:23.385 --> 00:01:24.205
in 2014.
13
00:01:25.945 --> 00:01:30.600
A little bit after that, also while at Airbnb, I started Apache Superset. So Apache Superset,
14
00:01:30.979 --> 00:01:35.479
for those not familiar with it, is a business intelligence, data visualization,
15
00:01:35.860 --> 00:01:36.360
dashboarding,
16
00:01:36.979 --> 00:01:39.720
web application that's open source. Right? So you can
17
00:01:40.055 --> 00:01:40.555
essentially,
18
00:01:41.335 --> 00:01:47.175
download that, install that, and run that in your company just like you would run Tableau and Looker. And,
19
00:01:47.960 --> 00:01:59.105
people are probably familiar with with Superset in the audience, but, like, it has gotten really, really freaking good over the past, like, year or 2. So if you haven't checked it out, you know, since I first started talking about it in
20
00:02:00.205 --> 00:02:00.705
2017,
21
00:02:01.325 --> 00:02:07.950
2018 is is probably when we launched it. It is good now. It is really it's it's really solid, really competitive,
22
00:02:08.570 --> 00:02:09.950
better than, you know,
23
00:02:10.330 --> 00:02:13.630
some of the vendor tools in in many, many regards,
24
00:02:14.045 --> 00:02:19.265
especially for a technical audience. Right. So for for people that are more technical, no sequel,
25
00:02:19.724 --> 00:02:35.114
the tool is more designed for the, you know, the the modern data practitioner too. So invite people to to try it. And then I don't do much of a commercial bit, but, like, if you wanna try Apache Super Set, the best way to try it is on presets of so I am a startup founder. So I started
26
00:02:35.415 --> 00:02:36.715
a a a company called Preset
27
00:02:37.174 --> 00:02:41.070
around Apache Superset. And the goal there is to offer a really awesome managed
28
00:02:41.950 --> 00:02:44.930
service around Apache Superset with bells and whistles.
29
00:02:45.710 --> 00:02:55.444
And it's all it's all managed and there's a freemium component so you can try it, you know, for for free today. And you can try it and go and use open source if you want to, but you could the easiest way to try superset
30
00:02:55.745 --> 00:02:59.330
latest greatest version is on preset. Io that you get out.
31
00:03:00.050 --> 00:03:05.670
Yeah. So found so founder now, I guess, I'm more of a founder, but, like, very much an open source enthusiast.
32
00:03:06.450 --> 00:03:08.310
Love writing code, building communities,
33
00:03:09.585 --> 00:03:11.765
and and more recently, building a company
34
00:03:12.145 --> 00:03:18.245
to that can that can fuel open source. Right? So the idea is much more like, how do we create something
35
00:03:19.220 --> 00:03:19.720
sustainable,
36
00:03:20.099 --> 00:03:36.285
around the open source project so we can be a little bit the the parent or the the the foster parent for the open source project and do all the the difficult things and and, you know, really help the community. So for me, it was, like, how business can help open source disrupt
37
00:03:36.640 --> 00:03:49.715
in this symbiotic kind of way much more than, like, oh, let's extract, you know, the the the money out of this community. Like, that that's really not the goal. The goal is to have this symbiotic like, you know, capital infusion and open source and helping
38
00:03:50.095 --> 00:03:54.730
helping us to get open source to to, to disrupt and and eventually
39
00:03:55.270 --> 00:03:56.890
dominate, you know, in,
40
00:03:57.430 --> 00:03:58.569
in in business intelligence.
41
00:03:59.110 --> 00:04:11.020
Yeah. I I definitely see Superset all over the place these days. Pretty much everywhere I look, if there's any sort of open source aspect at all to a stack, Superset's usually involved there. So definitely great work on that.
42
00:04:11.500 --> 00:04:16.080
And, just a brief recap again for people who haven't listened to any of your past appearances,
43
00:04:16.460 --> 00:04:19.200
how you first got involved with working in data.
44
00:04:20.015 --> 00:04:20.415
Yeah.
45
00:04:21.055 --> 00:04:29.475
And, you know, we could look at look back at the topics we talked about together too as a series of, like, my shifting and ever evolving interest over time.
46
00:04:29.840 --> 00:04:36.580
But, but I first started a while ago. So, like, you you could tell if we were on video how much, like, gray hair
47
00:04:36.925 --> 00:04:38.945
I have at this point. But I started,
48
00:04:39.325 --> 00:04:40.225
early 2000
49
00:04:40.685 --> 00:04:41.185
as
50
00:04:41.725 --> 00:04:50.510
well, first my career started, I I did 1 year of web development, which is kind of a it's a great thing to be able to build apps. Right? Regardless what your
51
00:04:50.970 --> 00:04:52.965
engineering career is gonna be like,
52
00:04:53.365 --> 00:05:05.120
being able to build little website and little tools is is super helpful. So I did a year of that early on. And then and then I got into pretty much data warehousing. So I use I was at Ubisoft,
53
00:05:05.580 --> 00:05:09.815
a bit of a company that's doesn't need to be introduced anymore probably,
54
00:05:11.075 --> 00:05:13.255
at this at this point in time. But they,
55
00:05:14.195 --> 00:05:19.130
yeah. So there I started doing, like, traditional data warehousing. So I picked up,
56
00:05:20.150 --> 00:05:25.610
you know, I'd had some classes in school. And I'm a college drop out, but I I had, like, an IT program
57
00:05:25.965 --> 00:05:29.825
equivalent of an associate degree where I learned some SQL, some data modeling,
58
00:05:30.765 --> 00:05:31.905
a little bit of everything,
59
00:05:32.845 --> 00:05:44.855
in terms of building apps and co encoding. I guess we didn't call them apps at the time. But, but yeah. So I had done some SQL, some data modeling, and then got into data warehousing, picked up the the rough Kimball books
60
00:05:45.395 --> 00:05:46.215
at the time,
61
00:05:46.835 --> 00:05:50.295
around the dimensional modeling and and started building a
62
00:05:51.000 --> 00:05:51.800
data warehouse,
63
00:05:52.280 --> 00:05:56.060
for for Ubisoft, all of their financial data, retail data,
64
00:05:57.755 --> 00:06:02.255
you know, pretty much covering the the realm of the data they had at the time.
65
00:06:02.635 --> 00:06:04.735
And that was the start of a, you know,
66
00:06:05.195 --> 00:06:06.415
a career in
67
00:06:07.210 --> 00:06:07.710
everything
68
00:06:08.250 --> 00:06:13.629
data pretty much. Right? Data. So I was the data warehouse architect. I was the the business intelligence
69
00:06:13.930 --> 00:06:14.430
engineer.
70
00:06:15.035 --> 00:06:17.695
And then, you know, later on in my career, I joined,
71
00:06:18.715 --> 00:06:24.170
more modern I moved to Silicon Valley and then joined companies like Facebook, Airbnb, Lyft,
72
00:06:25.370 --> 00:06:32.510
And that's where I started, you know, working working on open source and doing what we call today data engineering in in in a more
73
00:06:33.275 --> 00:06:34.974
a more modern way, you know.
74
00:06:35.435 --> 00:06:50.530
Yeah. And, a lot of people have credited you as being, to some extent, the godfather of data engineering as it's defined today given your well timed blog posts of the rise and fall of the data engineer, which roughly coincided with the start of this podcast. So
75
00:06:51.755 --> 00:06:55.695
Yeah. Yeah. I remember that was right, like, our first 1 of our first conversation
76
00:06:56.075 --> 00:06:56.575
together.
77
00:06:57.515 --> 00:07:00.575
Yes. I remember at the time and, you know, it's not like I invented
78
00:07:00.919 --> 00:07:04.300
that engineering, but, yeah, I wrote the blog post that try
79
00:07:04.680 --> 00:07:09.180
to, you know, try to define what it was and and what it was not
80
00:07:09.725 --> 00:07:16.705
at the time. And clearly, you know, for me, my experience is I joined Facebook a data warehouse architect.
81
00:07:17.280 --> 00:07:24.660
And by the time I left a few years later, I I left as a data engineer. We realized while we were there that what we were doing was
82
00:07:25.145 --> 00:07:28.205
was not, you know, data warehouse architecture
83
00:07:28.665 --> 00:07:31.805
or business intelligence. It was a whole new discipline
84
00:07:32.420 --> 00:07:35.560
with different like, with different where different skills were
85
00:07:35.940 --> 00:07:39.400
leveraged in different ways, and the approach was completely different.
86
00:07:40.115 --> 00:07:43.095
So at some point, internally on Facebook, we decided to call ourselves
87
00:07:43.795 --> 00:07:51.680
data engineer. And that I think I was inspired. There was an influential blog post called the Rise of the Data Scientists or something like that.
88
00:07:51.980 --> 00:07:55.095
I dug that out. That was that was pretty
89
00:07:55.395 --> 00:07:57.175
critical to to that,
90
00:07:58.115 --> 00:08:01.255
to kinda cementing, defining that term and and creating
91
00:08:01.860 --> 00:08:04.120
that that new role and persona.
92
00:08:04.500 --> 00:08:09.080
So I was like, hey, I'd I'd like to do that for data engineering. I wrote that blog post. And,
93
00:08:10.055 --> 00:08:11.835
yeah, rest is a little bit of history.
94
00:08:12.215 --> 00:08:15.115
Bringing us now to the topic at hand for today,
95
00:08:15.655 --> 00:08:28.235
brings us around to another 1 of your blog posts. So writing is definitely 1 of your strong suits. I appreciate all of the effort you put into that. And so you wrote a post about this concept of entity centric data modeling.
96
00:08:28.615 --> 00:08:36.030
And so for people who haven't already read the post, we'll have the link in the show notes so you can either pause now, read it, come back, or just read it afterwards.
97
00:08:36.730 --> 00:08:46.015
But for people who have don't don't wanna take the time, can you just give a bit of an overview about what it is when you describe entity centric data modeling and
98
00:08:46.795 --> 00:08:50.334
some of your process of coming to this formulation
99
00:08:50.714 --> 00:08:51.455
of the problem?
100
00:08:52.240 --> 00:08:59.300
Yeah. So first, a bit of a disclaimer. Right? Like, talking about data modeling without schematics and image is a little difficult,
101
00:08:59.760 --> 00:09:13.280
or it's not difficult for me to talk about. It might be difficult for for people to make sense of whatever we're gonna be saying here. So I encourage people to to read the blog post too, and, and we'll try to stay in the
102
00:09:13.660 --> 00:09:16.160
the language territory as opposed to visuals,
103
00:09:16.700 --> 00:09:18.640
to to talk about it. But
104
00:09:19.605 --> 00:09:24.905
the the idea the core idea behind entity centric data modeling is to,
105
00:09:27.050 --> 00:09:38.255
well, first, like, maybe I'll talk about the driver of, like, why I wrote the blog post. But, I've been a dimensional modeling aficionado for, like, 20 years. Right? So I read the Kimball books a while ago.
106
00:09:39.195 --> 00:09:44.860
You know, facts and dimensions make sense to me. For those not familiar with dimensional modeling,
107
00:09:45.480 --> 00:09:47.260
go pick up that book. Though it is like
108
00:09:47.720 --> 00:09:57.065
fading and relevance in some ways. Right? But it's, you know, if you're a data engineer, it's probably good to go and read the maybe that's the the the original, the old testament. You know?
109
00:09:57.685 --> 00:10:09.439
I don't know what the New Testament the New Testament might have to be written still, but, it's not a bad thing to go and and read that. Still, the general idea behind dimensional modeling is star schemas where,
110
00:10:10.295 --> 00:10:14.715
you know, some of the core principles is you have fact tables and you have dimension tables.
111
00:10:15.975 --> 00:10:16.475
Fact
112
00:10:16.990 --> 00:10:20.290
mostly contain foreign keys to dimensions and metrics,
113
00:10:20.830 --> 00:10:21.730
and then dimensions
114
00:10:22.110 --> 00:10:24.770
have, you know, the the primary key of
115
00:10:25.235 --> 00:10:28.615
the entity and then, all sorts of denormalized,
116
00:10:29.635 --> 00:10:31.735
things at the entity level.
117
00:10:32.950 --> 00:10:41.050
I'm not gonna get into snowflake schema because I feel like it starts to get into, like, the area where visuals are helpful. If you wanna learn more about dimensional modeling, check out the books.
118
00:10:42.095 --> 00:10:47.714
But, yeah. So I've been do practitioning dimensional modeling. I think, over the past
119
00:10:48.015 --> 00:10:51.810
decade or so, we started to be much more pro denormalization
120
00:10:52.830 --> 00:10:54.130
in the Hadoop era.
121
00:10:54.910 --> 00:10:57.490
If you're not familiar with normalization denormalization,
122
00:10:57.790 --> 00:11:09.340
you're probably in trouble for this blog post. You probably need to go and, you know, pursue your career in data engineering for a little while, read some articles, and come back to this blog post. But but we've been pro denormalizing
123
00:11:09.800 --> 00:11:11.820
the idea to have, like, more flat tables,
124
00:11:12.840 --> 00:11:16.380
as opposed to a bunch of small tables with a lot of foreign keys.
125
00:11:16.695 --> 00:11:23.995
Right? Another concept we might talk about in this blog post is, like, you know, normal forms, first normal form. It's like a normal, full thermal, normal form.
126
00:11:25.120 --> 00:11:27.459
But, yeah. So going back to dimensional modeling,
127
00:11:28.160 --> 00:11:31.940
1 thing that I've been doing and I've seen a lot of folks do,
128
00:11:32.695 --> 00:11:37.115
in the the the past, like, 5 to 10 years is starting to bring
129
00:11:37.735 --> 00:11:39.274
metrics into their dimensions.
130
00:11:39.975 --> 00:11:41.675
And, that can be
131
00:11:42.360 --> 00:11:43.579
really, really powerful
132
00:11:44.199 --> 00:11:44.699
pattern.
133
00:11:45.399 --> 00:11:57.575
In a lot of ways, we can get into why it is powerful. It's a little bit of a discretion as far as, like, say, if Ralph Kimball was on the show, he'd be like, wait, you did you did what? You brought some metrics into your dimension.
134
00:11:58.380 --> 00:12:09.825
Is that is that okay? Is that not okay? Like, you know, why are you doing that? So that's 1 of the ideas of, like, yes, no, it is an entity centered data data modeling. It is virtuous and encouraged to bring,
135
00:12:10.845 --> 00:12:17.025
metrics and dimensions. We have ways to talk about this and how to do that properly. So that we'll get into.
136
00:12:17.620 --> 00:12:18.440
Another idea
137
00:12:19.060 --> 00:12:19.560
is,
138
00:12:20.339 --> 00:12:20.839
legitimizing
139
00:12:21.300 --> 00:12:22.200
also bringing
140
00:12:22.580 --> 00:12:24.580
the data structures, net native data
141
00:12:25.459 --> 00:12:26.839
nested data structures
142
00:12:27.375 --> 00:12:28.995
inside your dimension tables.
143
00:12:29.375 --> 00:12:35.635
Right? So now we have, databases like BigQuery, Snowflake, or all databases have learned, you know, JSON and
144
00:12:36.070 --> 00:12:37.290
complex data structures
145
00:12:38.070 --> 00:12:38.570
over,
146
00:12:39.190 --> 00:12:44.890
over the past decade, which was not the case when the those early books were written. Right? So it's a new
147
00:12:45.394 --> 00:12:47.334
set of tools that we have.
148
00:12:48.115 --> 00:12:48.595
And,
149
00:12:49.074 --> 00:12:50.535
you know, by the book,
150
00:12:51.074 --> 00:12:55.470
starting to put an array of objects into a table
151
00:12:56.090 --> 00:12:57.310
was probably discouraged.
152
00:12:58.730 --> 00:13:02.935
And and now I'm saying like, hey, it actually sometimes it does make a lot of sense
153
00:13:03.415 --> 00:13:04.635
to do that. Right?
154
00:13:06.455 --> 00:13:14.870
So those are some of the core ideas that I've seen done. I wanted to legitimize and offer a little bit of a framework as to how
155
00:13:15.490 --> 00:13:18.310
to do that. Right? You bring metrics in your dimension.
156
00:13:18.714 --> 00:13:26.175
What do you do with time? You know, like, do you have, like, a customer table? You're gonna bring some some metrics. Like, yeah, you might have,
157
00:13:26.550 --> 00:13:28.889
you know, cost of acquisition or lifetime
158
00:13:29.350 --> 00:13:30.569
value of the customer,
159
00:13:31.189 --> 00:13:36.015
but you might also wanna pivot time, like, how many visits and as this user,
160
00:13:37.355 --> 00:13:41.535
you know, visited the site over the past 28 days or 7 days. So
161
00:13:42.279 --> 00:13:45.580
so I wanted to, like, legitimize some new set of practices
162
00:13:46.040 --> 00:13:50.540
and offer a bit of a framework or hints as to how to do that
163
00:13:51.005 --> 00:13:51.505
properly.
164
00:13:52.365 --> 00:13:54.385
Here's another thought too that's pretty foundational
165
00:13:55.165 --> 00:14:07.410
is, and some of the these ideas come from the field of feature engineering. And if you're a data engineering a data engineer today, you may or may not know depending on how close you sit to your fellow
166
00:14:08.805 --> 00:14:10.665
data scientists and your team.
167
00:14:11.204 --> 00:14:11.704
And
168
00:14:12.005 --> 00:14:15.625
whether, you know, they they have a feature store and do feature engineering,
169
00:14:16.470 --> 00:14:17.370
might differ
170
00:14:17.750 --> 00:14:19.529
depending on your organization. But
171
00:14:19.990 --> 00:14:27.495
I also wanted to take some of these concepts and practices that I've seen used in the field of feature stores and feature engineering
172
00:14:28.275 --> 00:14:28.935
and then
173
00:14:29.315 --> 00:14:35.870
mash that into, you know, data modeling for analytics as I've seen, you know, a a lot of of benefits
174
00:14:36.330 --> 00:14:39.470
of doing that. And feature engineering is extremely
175
00:14:40.330 --> 00:14:46.545
entity centric too. So that, you know, that kinda fits together. So in the blog post, I talk about the the parallels between,
176
00:14:47.005 --> 00:15:08.730
you know, feature engineering and entity centric data modeling too. So that's a lot of stuff. Right? That's a lot of information. I'm speaking fast. Hopefully, people are still still with us. You know? I've lost, 80% of the audience here. But, but, yeah, we can unroll to and go deeper into these different aspects of of it. Yeah. There there's
177
00:15:09.590 --> 00:15:16.570
any number of directions that I would love to go with this. Before we get too far down any of those paths, I also think it's maybe worth
178
00:15:17.024 --> 00:15:30.899
giving a concrete sense of what we mean when we're talking about an entity. Obviously, this is going to be different depending on your business, but from the I guess, not the broadest sense, but from a relatable sense, what are some of the ways that you think about how
179
00:15:31.440 --> 00:15:41.630
to go about defining an entity? What are the heuristics that you use to say, this is a, a business object or a domain object within my the context of my organization?
180
00:15:42.190 --> 00:15:45.890
Yeah. Yeah. So so clearly, you know, this notion of an entity,
181
00:15:46.830 --> 00:15:48.610
we've talked about it in the abstract
182
00:15:49.095 --> 00:15:49.815
here. Right?
183
00:15:50.135 --> 00:15:55.514
But but the reality is in the in a lot of cases, you know, analytics are applied to businesses.
184
00:15:56.110 --> 00:15:58.850
And then your core entity as a company,
185
00:15:59.710 --> 00:16:00.850
should be really
186
00:16:01.470 --> 00:16:08.824
clear to to you. Right? And, you know, while they may vary from a business to another or from, like, a business vertical
187
00:16:09.285 --> 00:16:10.024
to another,
188
00:16:10.485 --> 00:16:20.470
there there are some that are very universal and like most companies have that are very relatable. So the customer, right, like, is is a very clear business entity.
189
00:16:21.165 --> 00:16:30.145
Right? So if you think entity centric model, data modeling for a lot of people, that might be, like, entities like your core business entities, like your products,
190
00:16:30.800 --> 00:16:31.620
your customers,
191
00:16:32.480 --> 00:16:33.380
your users,
192
00:16:34.160 --> 00:16:36.100
your perhaps your departments.
193
00:16:36.400 --> 00:16:39.220
If you're in an a more of an HR world
194
00:16:39.654 --> 00:16:42.075
and finance, it might be your business units.
195
00:16:42.455 --> 00:16:42.955
Right?
196
00:16:43.335 --> 00:16:48.154
So these, I think, to me, if you look at the the definition of what's a dimension
197
00:16:48.535 --> 00:16:49.889
in dimensional modeling,
198
00:16:50.269 --> 00:16:51.490
it's fairly aligned
199
00:16:52.110 --> 00:16:59.355
with that. 1 thing that that's a little bit of discretion here that that might be interesting yet a little bit confusing. When you think about facts,
200
00:16:59.975 --> 00:17:05.195
right, is an event an entity, right, or is a and when you look at your your sales
201
00:17:05.575 --> 00:17:06.315
fact table,
202
00:17:07.060 --> 00:17:14.840
usually behind the scene, you know, it's not that far from an invoice or an invoice line fact table. So I would say in data modeling, most things
203
00:17:15.775 --> 00:17:17.395
are entities whether
204
00:17:17.695 --> 00:17:19.235
you want it or not.
205
00:17:19.775 --> 00:17:29.580
And the idea of, like, entity centric data modeling, when I named this approach, I was like, oh, do I go with entity? Because even if you look at, like, 3rd normal form, like your OLTP
206
00:17:29.960 --> 00:17:32.299
type data modeling, like data modeling for apps,
207
00:17:32.845 --> 00:17:39.730
This is also very, very entity centric. So entity centric seems like it calls for high normal form and third normal form.
208
00:17:40.210 --> 00:17:42.870
But but in this case, it's really anti centric for,
209
00:17:43.809 --> 00:17:47.110
for analytics. So, yeah, you're what are the big dimensions
210
00:17:47.410 --> 00:17:48.095
and entities
211
00:17:56.870 --> 00:18:09.274
focus on too much, is what you were talking about with this juxtaposition of analytical use cases for data versus machine learning use cases for data where machine learning has largely been dominated by this concept of feature engineering
212
00:18:09.575 --> 00:18:15.274
unless you happen to be doing deep learning, in which case you just throw a whole bunch of data and hope something useful comes out the other side.
213
00:18:15.690 --> 00:18:17.550
Yeah. So I think in deep learning
214
00:18:18.170 --> 00:18:26.775
too, I think, like, really often you'll use so feature source can be used for deep learning. I believe it's just LLMs now is the whole new field of unsupervised
215
00:18:27.155 --> 00:18:27.655
learning
216
00:18:28.195 --> 00:18:41.640
where now you can throw a bunch of text. Right? And then, you know, so it's not as you don't need to have as structured data if you have a language model. But, but I think still and, you know, if you wanna train a neural net for a special purpose
217
00:18:42.315 --> 00:18:43.855
kinda type use cases,
218
00:18:44.155 --> 00:18:59.460
you you still need a feature store or you still need, you know, things to be, like, organized in a similar way. When you think about it, like the you know, know, for data engineers on the call here, they think about, like, what's so different about feature engineering or ML use cases and what are
219
00:19:00.105 --> 00:19:02.925
some of the needs there. So so typically,
220
00:19:03.305 --> 00:19:03.805
in
221
00:19:04.425 --> 00:19:07.885
machine learning, I think you want to have, like, fairly, like,
222
00:19:08.210 --> 00:19:09.110
entity centric
223
00:19:09.970 --> 00:19:16.785
thing. So you want these very, very flat table with, like, a 1000000 columns. So let's say if you're trying to make prediction on
224
00:19:17.265 --> 00:19:22.965
is a user likely to churn. Right? So what you're gonna need is the for the user entity,
225
00:19:23.745 --> 00:19:33.120
a 1000000 metrics that might be related or correlated to the the fact that whether they may churn or not. So you're gonna say, okay. So I'm gonna train a model
226
00:19:33.580 --> 00:19:47.250
with you know, or I'm gonna give it data for a bunch of users. And for for each users, I'm gonna give them a 1000000 different attributes or probably not a 1000000. Like, most likely, like, you know, a hunt at least a 100, maybe a 1000,
227
00:19:48.750 --> 00:19:49.250
attributes
228
00:19:49.789 --> 00:19:54.154
related to these users that may or may not be related to their lihood
229
00:19:54.695 --> 00:19:59.034
to churn. And it might be thing like time since last visit. Right? That's a metric.
230
00:19:59.335 --> 00:20:02.554
How many visits did they have over the past year
231
00:20:03.100 --> 00:20:10.240
and which part of the product they engage with or they don't? Usually usually as a column, like, did they engage with with
232
00:20:10.875 --> 00:20:28.205
feature a and the product? Did they engage with feature b and the product? All pivoted. So typically, you know, the models like to receive data that is all pivoted. And then what, you know, the the under what the machine learning model is gonna do is try to find correlation, like, which are the columns or metrics or combination of columns or metric
233
00:20:37.890 --> 00:20:39.830
learning is a really clear
234
00:20:40.210 --> 00:20:42.150
entity, in this case, the user,
235
00:20:42.530 --> 00:20:56.679
a metric that you're trying to predict, like, whether they churn they will churn or not, and then a 1000000 attributes that may or may not be related to that. Let the machine learning, you know, model figure out what correlates with that. And
236
00:20:57.140 --> 00:20:59.080
given those 2 different use cases,
237
00:20:59.460 --> 00:21:07.875
1 of the things that I've been curious about of late is whether both of those different teams, so the data analyst and the ML engineer,
238
00:21:08.415 --> 00:21:14.980
are they both going to the same location to get the data, or does the ML engineer need their own feature store, feature repository,
239
00:21:15.280 --> 00:21:22.525
feature pipelines, and the data analyst uses the data warehouse and they're off in their own world? Or are we getting to a point where
240
00:21:22.985 --> 00:21:27.750
the data warehouse is powerful enough and flexible enough where everybody's actually just working on top of that
241
00:21:28.630 --> 00:21:34.490
same substrate of the data warehouse, but maybe with a different set of tables that are derived from those core sources.
242
00:21:35.294 --> 00:21:43.030
Yeah. I think it it's it's very intuitive to think, like, hey, man. We should share. We should be dry. Right? We should, like, only have, like, 1 definition
243
00:21:43.410 --> 00:21:49.190
of these metrics and 1 way to model these things. And we should all be consistent, managed in the same
244
00:21:49.625 --> 00:21:50.125
repo.
245
00:21:50.585 --> 00:21:52.765
And we all, you know, combine,
246
00:21:53.065 --> 00:21:58.205
you know, between, like, data engineers, data scientists. But I would say the needs are, like, intricately similar
247
00:21:58.585 --> 00:21:59.920
and and different,
248
00:22:00.700 --> 00:22:04.560
and similar to the needs and the constraints for on both sides are
249
00:22:05.020 --> 00:22:05.520
intricately
250
00:22:06.140 --> 00:22:10.865
different too. So there's definitely, like, should they share? Should they talk? Should they
251
00:22:11.565 --> 00:22:12.065
align?
252
00:22:12.845 --> 00:22:17.720
Yes. Then can they use the same technology and processes and live in the same repo?
253
00:22:18.100 --> 00:22:19.080
In my experience,
254
00:22:20.259 --> 00:22:23.889
that doesn't tend to work so well. So I can try to explain,
255
00:22:24.425 --> 00:22:26.445
like, some of the the bigger
256
00:22:26.905 --> 00:22:27.405
divergences
257
00:22:28.185 --> 00:22:36.820
on both sides. So on the future side of things, there's a line there's a need often for online prediction, like, you know, live kinda streaming
258
00:22:37.360 --> 00:22:43.245
streaming datasets. So stream that batch, that need may or may not exist on on the analytics side.
259
00:22:43.784 --> 00:22:47.245
The kind of metrics they care about versus the ones we care
260
00:22:47.730 --> 00:22:52.150
about and the call it the 90 day, you know, analytics versus the,
261
00:22:52.530 --> 00:22:54.550
you know, machine learning type use cases,
262
00:22:54.934 --> 00:23:04.394
They're not there's a lot of common metrics. There's a lot of metrics they care about when you might not. Right? Like and then just the the practices, the mindset, the personas,
263
00:23:06.279 --> 00:23:15.455
the approaches tend to be differentiated enough that it's hard to agree and settle on a place to work together. So I'd say give it a shot, but it's,
264
00:23:16.095 --> 00:23:18.355
I I would say your likelihood to succeed
265
00:23:19.055 --> 00:23:19.555
is,
266
00:23:20.335 --> 00:23:20.815
is
267
00:23:21.309 --> 00:23:43.650
I don't know. Not guarantee. I'd be really curious, like, if there are people that do that successfully to an extent, that would be a really interesting, you know, thing for the conversation for the the show notes. But in my experience, they you know, these different percent tends to live in slightly different world. And they do share at some point, they'll share some source table. Right? Like, you'll you'll have, like, you know, you have your staging area, your work tables, your, you know,
268
00:23:43.950 --> 00:23:45.810
your your more curated schemas.
269
00:23:46.350 --> 00:23:56.955
You know, they might tap into that or you might have a shared layer somewhere. You're working particularly with the same data. The question is, like, how far along you go together before you you diverge into
270
00:23:57.390 --> 00:23:59.570
different systems or different use cases.
271
00:23:59.950 --> 00:24:04.530
And so bringing us back now to the space of data modeling,
272
00:24:04.830 --> 00:24:05.890
dimensional modeling,
273
00:24:06.515 --> 00:24:22.500
We talked a little little bit already about the juxtaposition of entity centric modeling with some of the more so called traditional dimensional modeling strategies of star and snowflake schema, and we can maybe even lump the data vault in there as well, if you're feeling, adventurous.
274
00:24:23.075 --> 00:24:24.375
And beyond those
275
00:24:24.835 --> 00:24:30.775
established practices, what are some of the other competing modeling approaches that you've seen cropping up?
276
00:24:31.250 --> 00:24:36.630
1 in particular that came to mind when I was preparing for this episode was the activity schema
277
00:24:37.010 --> 00:24:37.990
that's been popularized
278
00:24:38.424 --> 00:24:55.590
by a company whose name is escaping me at the moment where every event just goes into 1 table, and you just have an event type, and you just query based on event type. I was wondering if you can talk to some of the other ways that you've seen people try to tackle this problem and come to grips with the increased compute power that we have now with the,
279
00:24:56.795 --> 00:25:00.575
structural benefits that are offered by these dimensional strategies?
280
00:25:01.195 --> 00:25:01.595
Yeah.
281
00:25:02.155 --> 00:25:04.255
So first, it's like, what is the inventory
282
00:25:04.715 --> 00:25:05.215
of
283
00:25:05.750 --> 00:25:17.025
of, like, the different data modeling, you know, techniques that you might wanna study or use as a data engineer today? Right? Like, like, what what are the relevant ones that you mentioned?
284
00:25:17.725 --> 00:25:34.284
Activity schema that I would probably best describe as, like, the criminal in in 1 table with, you know, more complex data structure, which has has some virtues. And and, you know, frankly, as a pattern that I've used for some parts of the data warehouse for frameworks that, you know, pivot,
285
00:25:34.605 --> 00:25:38.010
time metrics and things like that. So we could we could talk about how
286
00:25:38.390 --> 00:25:41.370
an activate an activity schema type approach
287
00:25:41.750 --> 00:25:44.085
can be user leveraged to
288
00:25:44.465 --> 00:25:46.565
produce entity centric practices
289
00:25:47.105 --> 00:25:52.565
in some ways, just like trying to enumerate. So there's I know there's Data Vault, I think, that has some
290
00:25:52.950 --> 00:26:02.225
some traction and some good literature. Right? There's data mesh, which to me is I would dismiss as not data modeling and more like it's more like organizational
291
00:26:03.165 --> 00:26:05.585
and it's about, like, how should you structure your
292
00:26:05.885 --> 00:26:06.625
your organization?
293
00:26:06.925 --> 00:26:10.385
It's not really prescriptive in terms of data modeling technique.
294
00:26:10.790 --> 00:26:15.130
Yeah. So activity schema, they they have all data mesh. There's probably a bunch
295
00:26:15.430 --> 00:26:17.430
of other ones more traditionally than there's,
296
00:26:18.115 --> 00:26:23.095
know, dimensional modeling. That's the Kimball 1. There's the corporate corporate information factory.
297
00:26:23.635 --> 00:26:26.775
It sounds like an awful term. That's the Bill Inman approach,
298
00:26:27.330 --> 00:26:33.669
which this 1, I think, suggests a normalized data warehouse and then building data marts off of it.
299
00:26:34.245 --> 00:26:37.225
Kimball, dimensional modeling talks about conformed dimensions.
300
00:26:37.845 --> 00:26:40.345
But, yeah, I think it's a it's a bit of a messy
301
00:26:41.285 --> 00:26:46.090
world, and there's nothing that says you have to pick only 1 of these religions
302
00:26:46.950 --> 00:26:55.644
too. And then there's been very little innovation in that field. Right? Like, so feel like, hey, what are the the best data modeling for analytics,
303
00:26:56.184 --> 00:27:00.125
blog posts, or books that I should read from the past 10 years?
304
00:27:00.580 --> 00:27:04.039
I don't think you're gonna end up with a lot of reading material, you know.
305
00:27:04.580 --> 00:27:11.054
Maybe because the the the topic is like I don't know. Or maybe it's, like, partially solved or partially unsolvable
306
00:27:11.355 --> 00:27:12.735
or not that interesting
307
00:27:13.835 --> 00:27:28.295
to people. And yet you have, like, now with the the rise of the analyst engineer and and people basically doing whatever the heck they want in terms of data modeling. Right? Like, I'll just write a bunch of sequel. I'll just write mountains of sequel that derive derivatives and
308
00:27:28.915 --> 00:27:32.940
of other dataset. And I I don't know what normalization, denormalization
309
00:27:33.400 --> 00:27:36.700
means. I just run queries and produce dataset, and
310
00:27:37.080 --> 00:27:48.075
we try to make sense of of all of this. So given that, I saw that as a bit of an opportunity to to write, you know, some content on data modeling. You know, there's probably an audience out there.
311
00:27:48.560 --> 00:27:56.740
You know, there's people thinking like, is it okay to put metrics in my dimension? Or should I should I read a page from the feature engineering book and,
312
00:27:57.294 --> 00:28:01.715
you know, apply some of these techniques on my side? So, yeah, it's a it's a bit of a jungle
313
00:28:02.174 --> 00:28:03.794
out there. The the most comprehensive,
314
00:28:04.174 --> 00:28:05.154
I think, complete
315
00:28:05.760 --> 00:28:08.660
thing that's self standing to me is the Kimball
316
00:28:09.280 --> 00:28:17.294
books. Right? And it's what? It's 20 years old now. It predates parquet. It predates, like, column store maybe in in many ways. Right? So
317
00:28:17.595 --> 00:28:23.580
so things some of the stuff in there needs to be freshened up. Another interesting thing that I
318
00:28:23.880 --> 00:28:28.860
see in the community and have experienced firsthand a little bit is also the
319
00:28:29.175 --> 00:28:29.675
propensity
320
00:28:29.975 --> 00:28:32.875
for teams who are working with data to
321
00:28:33.175 --> 00:28:54.035
not even go through the modeling process of just saying particularly with ELT and tools like DBT to say, I've already got my data. I'm just gonna write some SQL to pull together a few things into a table, and there we go. And then that works for a little while until it doesn't. And I'm curious what you see as the overall impact of tooling on the
322
00:28:54.370 --> 00:28:59.110
ways that teams approach that thought process of data modeling, data transformation,
323
00:29:00.130 --> 00:29:07.455
the downstream usage and impact of the ways that they approach that transformation and modeling stage and also
324
00:29:08.075 --> 00:29:21.420
lumping into the tooling category, the underlying database engines as well and the capabilities they bring. Just the way that all of that jumbles together to either encourage teams to think about modeling or to just go forth and hope for the best.
325
00:29:21.915 --> 00:29:32.430
Yeah. I mean, there's you know, 1 1 thing, there's a lot of virtues in in making it easier for people to do things. Right? And then Airflow did that in many ways. Like, you know, Airflow made it easy
326
00:29:32.890 --> 00:29:36.590
ish. I mean, not not that it's super easy, but for people
327
00:29:37.105 --> 00:29:38.645
to manage and maintain
328
00:29:39.425 --> 00:29:42.165
and grow their network of data pipelines faster.
329
00:29:42.625 --> 00:30:15.595
Right? So with that comes a lot of enablement, like more people can create more pipeline faster and manage more because they have the this nice, you know, UI to understand what fails and retrigger things, do change management in a a decent or half decent way. I think dbt elevates that to a a next level for SQL specific use cases. Right? So making it really easy for anyone to that knows a little bit of SQL, a little bit of git to start producing dataset and scheduling them running every day. You know, the downside of making that so accessible
330
00:30:16.054 --> 00:30:25.510
is, well, if it's really easy to to produce a dataset, then, you know, people can create many datasets per day. And I think this controversial,
331
00:30:25.890 --> 00:30:30.664
person on Twitter, Lauren, I think she talks quite a bit about how, like, the explosion
332
00:30:31.125 --> 00:30:36.424
of, like, there's a conspiracy theory on, like, you know, Snowflake making money off of
333
00:30:36.850 --> 00:30:52.065
dbt, you know, empowering people to create, you know, just these mountains of sequel. And I I certainly don't think there's a conspiracy theory, but I do think that that there's a conspiracy going on. Like, making it easy for more people to do certain things
334
00:30:52.659 --> 00:30:59.000
brings a new wave of people that may not be expert in the art of, say, data modeling
335
00:30:59.865 --> 00:31:01.245
and can lead to
336
00:31:01.545 --> 00:31:07.480
technical debt being created faster than it will, you know, in a way that there's no hope to maybe fix it ever.
337
00:31:08.040 --> 00:31:11.820
Right? You know? So yeah. You know? And specifically about
338
00:31:12.280 --> 00:31:21.165
about dbt, something that's, like, very different from, like, with Airflow. Airflow has a very kinda scheduler approach. It's like, this job runs every day. So then it becomes,
339
00:31:21.565 --> 00:31:53.904
kinda natural to say, like, I'm gonna process a day worth of data every day. If I need to reprocess a day, I can go and just reprocess that day. DBT has a a simpler approach around that. It's just like write code that either rebuilds the whole table every day or or you can set up these incremental loads and the approach for incremental load is catch up. Right? So, like, write code that if you're in incremental mode, it will figure out what range to run and basic. It looks like a compiler. I did a build tool. It's a build tool. So that makes it maybe a little bit easier in a way to
340
00:31:54.205 --> 00:31:59.700
to author these jobs. I mean, make it extremely easy to be lazy too and just, like, refresh
341
00:32:00.160 --> 00:32:07.184
the whole table every day. So when you run dbt run-in a lot of places, you know, it rebuilds the entire data warehouse every day.
342
00:32:07.485 --> 00:32:09.645
That works for a start up. That probably doesn't work,
343
00:32:10.365 --> 00:32:23.190
you know, when you start having a little bit more data. You have to to go and start thinking as a retrofitting, like, how do we bring in incremental to incremental load to to that world? So, yeah, I think of this new wave of, like, analyst engineers,
344
00:32:23.955 --> 00:32:28.055
you know, which know SQL, they wanna visualize data,
345
00:32:28.675 --> 00:32:31.095
how much do they know about and care
346
00:32:31.580 --> 00:32:39.679
about data modeling? And how much do they know and care about data modeling? I'd say, like, a little bit less than what I have I have observed personally. It'd be it'd be better
347
00:32:40.155 --> 00:32:40.895
if everyone
348
00:32:41.195 --> 00:32:45.455
knew a little bit more about data modeling and add a little bit more rigor
349
00:32:46.155 --> 00:32:57.325
around how they, you know, they think about creating and managing and maintaining these datasets for them. Another interesting aspect of the modeling question is, who are you doing it
350
00:32:57.625 --> 00:33:01.645
for? Where in the Kimbell era, it was very much for the
351
00:33:02.105 --> 00:33:21.495
DBA and the business intelligence engineer maybe working together to say, okay. This is the report that I'm trying to build, so these are the facts and dimensions that I need. This is the way that I need this to be transformed to be able to write this report. 6 months later, okay. I've got a new report, so we're gonna go back and update to the set of tables. Whereas now, there isn't that clear
352
00:33:22.070 --> 00:33:33.755
connection between the way that the data is coming in, the way it's being transformed, and the way it's being used. There are sort of interfaces between each of those stages of, okay. I've done my job. I don't know what you're gonna do with it, but good luck.
353
00:33:34.535 --> 00:33:35.035
And
354
00:33:35.735 --> 00:33:51.115
But no contract to us. So, like, is this private? Is that public? Right. Who uses it? It's like, I have baked a thing, and you can break it all on top of it. You don't have to let me know if you do. And and, you know, change management is just hard and data too. So
355
00:33:51.495 --> 00:33:52.555
I don't think we
356
00:33:52.935 --> 00:33:55.195
respect the problem of of change
357
00:33:55.580 --> 00:34:03.040
management well, you know, and then engineering. Just put it out there. People will latch something else on top of it, and it's all SQL all the way down.
358
00:34:03.605 --> 00:34:08.265
And so to that point as well, dimensional modeling, star, and snowflake schemas,
359
00:34:08.964 --> 00:34:18.299
those are very technically oriented. There's a lot of detail to it. Once it's there, it's very valuable if you know how to use it and what you're trying to get out of it.
360
00:34:18.680 --> 00:34:19.239
I'm wondering
361
00:34:19.640 --> 00:34:23.085
going back to the entity centric modeling that you're proposing,
362
00:34:23.785 --> 00:34:24.925
what are the
363
00:34:25.465 --> 00:34:39.589
players in that space of saying, okay. I'm going to build this entity centric model because now it's easier for you to be able to self discover what are the things that you need to know, what are the ways that you're going to use it, what are some of the downstream benefits of the entity centric model
364
00:34:39.944 --> 00:34:48.365
in terms of building that interface between the data engineer or the analytics engineer who constructs it and the downstream consumers of that data?
365
00:34:48.770 --> 00:34:56.630
So first, like, going back to, like, who was the star schema serving or who did we did we data model for in the past?
366
00:34:57.065 --> 00:34:59.885
And certainly, we're not catering to a
367
00:35:00.345 --> 00:35:10.280
SQL savvy organization that people are gonna go and run c you know, connect to the database and run SQL. We did it in service of the BI tool. Like, that that is really clear to me that
368
00:35:10.820 --> 00:35:11.320
star
369
00:35:11.780 --> 00:35:14.520
star schema's dimensional modeling snowflake models
370
00:35:15.174 --> 00:35:16.555
were there to power
371
00:35:17.335 --> 00:35:19.835
a BI tool that had the semantic layer.
372
00:35:20.375 --> 00:35:27.820
Right? And, I guess now we're getting into the semantic layer, which I I think we I think we have we have to. But historically,
373
00:35:29.000 --> 00:35:29.740
you know,
374
00:35:30.120 --> 00:35:32.140
tools like business objects, micro strategy,
375
00:35:32.755 --> 00:35:34.295
SQL Server Analysis Services,
376
00:35:35.555 --> 00:35:38.215
even like tool a tool like like Looker today.
377
00:35:39.875 --> 00:35:42.455
They expect to work with
378
00:35:43.140 --> 00:35:49.480
fairly complex schemas or data set. They they won't do super well typically with, like, 3rd normal form, you know,
379
00:35:50.485 --> 00:35:54.505
type model, but they do they do very well with these star schemas.
380
00:35:55.365 --> 00:35:55.865
Now,
381
00:35:56.805 --> 00:36:01.180
do humans do well with star schemas is a question too. So if I deliver
382
00:36:01.560 --> 00:36:05.320
well, let's let's stay on the semantic layer for for a moment too. So,
383
00:36:05.720 --> 00:36:07.660
so if you build the the
384
00:36:08.484 --> 00:36:11.945
the schema or data model and service the BI tool,
385
00:36:12.645 --> 00:36:18.869
then that assumes that you have to put this semantic layer on top. Right? And that promise, the semantic layer is self-service.
386
00:36:19.490 --> 00:36:23.750
So in tools like, you know, this is object market strategy, Looker, you can
387
00:36:24.315 --> 00:36:32.415
drag and drop these business objects like your your dimensional attribute and your metric. And then the tool will figure out how to query your underlying
388
00:36:32.760 --> 00:36:33.980
complex star schema
389
00:36:34.440 --> 00:36:35.180
to serve
390
00:36:35.720 --> 00:36:39.900
that result set or to eventually power, you know, your visualization
391
00:36:40.280 --> 00:36:40.735
or
392
00:36:41.295 --> 00:36:41.795
dashboard.
393
00:36:42.335 --> 00:36:52.670
Now there's some big problems with the semantic layer. So first and I wrote a different blog post on this 1. I encourage people if people are interested more in the semantic layer. I I wrote something about dataset centric data modeling.
394
00:36:53.050 --> 00:36:58.670
Entity centric builds on top of dataset centric. But dataset centric says, in general,
395
00:36:59.585 --> 00:37:02.164
the interface for the BI for consumption,
396
00:37:03.105 --> 00:37:05.045
whether it be a BI tool,
397
00:37:05.825 --> 00:37:06.724
a data scientist,
398
00:37:07.025 --> 00:37:08.900
or someone writing SQL,
399
00:37:09.520 --> 00:37:16.180
it is a dataset or can like, it's really great when it's a dataset. It's easy for people to reason about tabular flat tables.
400
00:37:16.685 --> 00:37:20.045
It's nice to not have to figure out which joints to make,
401
00:37:20.765 --> 00:37:29.920
when you don't have to. And that's some of the ideas behind the entity centric data modeling too, where if you have everything around a customer in a customer table, including
402
00:37:30.540 --> 00:37:33.840
metrics, activity, engagement, likely at the churn,
403
00:37:34.924 --> 00:37:37.244
then you're in a good place in terms of,
404
00:37:37.964 --> 00:37:39.424
having just to understand
405
00:37:39.724 --> 00:37:42.944
the table you're working with, the column it has, and their definition.
406
00:37:43.450 --> 00:37:50.990
Then you have to go to a map to figure out, like, what do I need to join here to augment this data. Yeah. So get getting
407
00:37:52.015 --> 00:37:53.535
a bit deeper into
408
00:37:54.015 --> 00:37:57.075
and and to, like, you know, serving the VI tools. So now
409
00:37:57.375 --> 00:38:04.369
you have multiple BI tools. So most organizations have multiple BI tools for a variety of reasons. We could or cannot get into, but,
410
00:38:04.750 --> 00:38:11.125
the reason why is usually, like, you know, there's different generation of BI tools. There's different personas in your organization that
411
00:38:11.425 --> 00:38:14.245
prefer there's new BI tools coming on the market.
412
00:38:14.545 --> 00:38:17.685
There's a great open source 1 called Superset. You can go and
413
00:38:18.060 --> 00:38:20.720
use it and start using today.
414
00:38:21.260 --> 00:38:28.955
So people accumulate multiple BI tools even through acquisition sometimes or different business units who wants different BI tool for whatever reason. So
415
00:38:29.255 --> 00:38:34.235
then if you the semantic layer are not universal and open source, they are
416
00:38:34.740 --> 00:38:35.240
proprietary
417
00:38:35.540 --> 00:38:37.480
to the BI tool that you use.
418
00:38:38.260 --> 00:38:41.160
And they are fairly complex, hard to manage.
419
00:38:42.315 --> 00:38:50.575
I talked a little bit about change management before. Change management in data engineering is really hard in the data warehouse and the transform layer. It's also hard
420
00:38:51.100 --> 00:38:52.720
in the semantic layer,
421
00:38:53.500 --> 00:38:57.840
and keeping, you know, these multiple BI vendor tools in sync
422
00:38:58.220 --> 00:39:00.000
with your evolving schema
423
00:39:00.835 --> 00:39:03.575
is quite a challenge and is duplicative
424
00:39:03.875 --> 00:39:08.535
of work. Right? So if you put a a lot of that, how do you query this star schema
425
00:39:09.260 --> 00:39:12.400
in that semantic layer and you have multiple semantic layers to manage,
426
00:39:13.260 --> 00:39:22.445
you're a bit in trouble. So for me, I argue that in this era where this we don't really have a good universal reusable semantic layer across tools.
427
00:39:23.145 --> 00:39:23.645
It's
428
00:39:24.000 --> 00:39:27.060
better to put more of that logic in the transform layer
429
00:39:27.440 --> 00:39:28.340
and to denormalize
430
00:39:28.640 --> 00:39:29.140
further.
431
00:39:29.600 --> 00:39:31.540
And then you end up with this,
432
00:39:32.565 --> 00:39:34.265
who you are serving. So
433
00:39:34.565 --> 00:39:39.305
in a a perfect entity centric modeling type world,
434
00:39:39.680 --> 00:39:42.260
you have these very rich, very comprehensive
435
00:39:43.280 --> 00:39:44.180
entity centric
436
00:39:44.480 --> 00:39:47.300
datasets that can be used and reused across
437
00:39:47.755 --> 00:39:51.135
BI tools by different personas, by people who write SQL,
438
00:39:53.035 --> 00:39:56.575
you know, without having to really understand complex
439
00:39:57.549 --> 00:39:58.049
underlying
440
00:39:58.349 --> 00:39:59.329
data models.
441
00:39:59.789 --> 00:40:02.690
Right? So I I wanna do a customer bound analysis.
442
00:40:03.150 --> 00:40:06.265
Then I'm gonna pull the customer table. And great. It has
443
00:40:06.744 --> 00:40:08.765
200 well documented columns
444
00:40:09.224 --> 00:40:12.924
in different groups and with some really clear naming conventions and descriptions.
445
00:40:13.224 --> 00:40:16.599
And it has everything it I need for me to do
446
00:40:17.059 --> 00:40:21.000
really deep analytics for my customers. Like, I want my customers who
447
00:40:21.460 --> 00:40:27.945
are likely to change, who haven't been active in the past 90 days, who who have acted in response of this,
448
00:40:28.965 --> 00:40:33.385
you know, ad campaign or whatever it may be and to have that at your
449
00:40:34.440 --> 00:40:36.140
fingertips or SQL tips,
450
00:40:36.840 --> 00:40:37.580
you know,
451
00:40:37.880 --> 00:40:43.585
there. And then that can be used in your, you know, as a source for your superset dashboard
452
00:40:43.965 --> 00:40:46.305
or your Tableau as a Tableau extract
453
00:40:46.925 --> 00:40:50.145
or, you know, by a science data scientist in a notebook
454
00:40:50.690 --> 00:40:52.790
and can be much more comprehensive
455
00:40:53.170 --> 00:40:57.670
because tabular datasets are just easy and natural to reason about.
456
00:40:58.744 --> 00:41:01.244
And so for people who are
457
00:41:01.545 --> 00:41:07.410
trying to figure out what is the modeling strategy that I'm going to use or they are in that situation
458
00:41:07.790 --> 00:41:18.105
of, I've got a whole bunch of source tables. I've got a whole bunch of derived tables, but there's not really any rhyme or reason to it. What are some of the tactical aspects of iterating towards
459
00:41:18.405 --> 00:41:19.145
a workable
460
00:41:19.765 --> 00:41:24.640
entity centric domain model that they can build and maintain and evolve for their organization?
461
00:41:25.260 --> 00:41:27.440
Yes. I would say you probably and
462
00:41:27.820 --> 00:41:28.640
and your warehouse
463
00:41:29.180 --> 00:41:29.680
today
464
00:41:30.380 --> 00:41:35.975
already have like, your core business entities are probably very, very well represented
465
00:41:36.915 --> 00:41:37.815
in slightly
466
00:41:38.115 --> 00:41:40.535
flat table. Right? So you probably have
467
00:41:40.839 --> 00:41:41.900
some sort of,
468
00:41:42.920 --> 00:41:43.420
central
469
00:41:43.720 --> 00:41:45.420
or, you know, master central
470
00:41:45.720 --> 00:41:47.500
customer table. Or if
471
00:41:47.885 --> 00:41:53.345
your business is in supply chain, you have your private very rich, like, warehouse table somewhere
472
00:41:53.965 --> 00:41:54.285
that,
473
00:41:54.925 --> 00:41:58.410
that has the bulk of everything that you should know
474
00:41:58.869 --> 00:41:59.930
about a warehouse.
475
00:42:00.470 --> 00:42:12.675
If you don't have 1, then I would argue, like, you should build 1, you know. And then how you build it, you know, whether you use a staging area and work tables in the, you know, back room and and and front room
476
00:42:13.330 --> 00:42:14.070
type approach
477
00:42:14.370 --> 00:42:16.790
there, I would say, probably stick to your
478
00:42:17.250 --> 00:42:21.590
internal practices as to how to do that, whether you do it in airflow or in VBT
479
00:42:22.050 --> 00:42:22.550
or
480
00:42:22.915 --> 00:42:23.575
in ELT
481
00:42:24.035 --> 00:42:26.295
or in Spark, I think is also
482
00:42:26.675 --> 00:42:30.775
something that is very kind of cultural and, like, you know, unique to your organization.
483
00:42:31.160 --> 00:42:43.684
If you have a blank slate, which, like, I don't think many of the people like, who on the call if we had people on the call? Who on the call here as a blank slate and gets to define, you know, their nomenclature and data modeling
484
00:42:44.305 --> 00:42:47.845
practices for their organization. That happens very few times in
485
00:42:48.250 --> 00:42:53.309
that era. That doesn't last very long usually. But I think that that'd be an interesting question,
486
00:42:53.770 --> 00:43:01.475
too. If you have nothing today, where do you start? Could do an episode on that. But assuming you already have practices in place, tooling in place,
487
00:43:02.015 --> 00:43:03.955
and a reflection of your
488
00:43:04.300 --> 00:43:05.040
core entities
489
00:43:05.500 --> 00:43:09.280
somewhere in a queryable place. The question is, like, how do you augment it
490
00:43:09.740 --> 00:43:11.120
to be even more useful?
491
00:43:11.505 --> 00:43:14.005
Some some of the ideas behind entity centric
492
00:43:14.545 --> 00:43:17.445
is, yes, you can and should bring
493
00:43:18.065 --> 00:43:23.050
metrics, like numerical metrics to your dimension tables. And that we can talk about,
494
00:43:24.470 --> 00:43:27.930
how how to do that. I I touch on it in a blog post,
495
00:43:28.315 --> 00:43:38.100
but it it's it's fairly complicated and abstract. The big question too is, like, how do you bring time into that? Right? Like, so fact tables are usually combination of foreign keys,
496
00:43:38.400 --> 00:43:42.660
an event, some metric, and a time 1 or multiple time dimension.
497
00:43:43.040 --> 00:43:51.174
Now here we know we're in a table in the entity entity centric model, we're in a table that has a really clear grain. The grain is 1 row
498
00:43:52.194 --> 00:43:52.934
per entity
499
00:43:53.234 --> 00:43:55.255
per instance. Right? So in the customer,
500
00:43:56.420 --> 00:43:58.840
entity centric table, there's 1 row per customer.
501
00:43:59.220 --> 00:43:59.720
Now
502
00:44:00.020 --> 00:44:01.000
if there's probably
503
00:44:01.620 --> 00:44:06.445
dozens, if not, you know, dozens of metrics that are very, very key and relevant
504
00:44:07.065 --> 00:44:08.605
to qualifying that customers.
505
00:44:09.305 --> 00:44:11.165
How do you bring the time dimension
506
00:44:11.945 --> 00:44:20.160
and colonize it is an interesting question that I talk about in the blog post. The first thing is to bring these, I call them town time bound
507
00:44:20.700 --> 00:44:21.200
metrics.
508
00:44:21.500 --> 00:44:23.040
So say for a user table,
509
00:44:23.555 --> 00:44:25.175
you know, call it visits
510
00:44:25.475 --> 00:44:25.975
or
511
00:44:26.515 --> 00:44:28.535
certain types of actions seem
512
00:44:28.995 --> 00:44:30.275
really important. And,
513
00:44:30.970 --> 00:44:36.910
the time dimension for that is, well, okay, I can bring the total number of visits that this user
514
00:44:37.210 --> 00:44:39.150
has had. Right? So it could be like
515
00:44:39.605 --> 00:44:44.585
me on your podcast, you know, I have, add on your podcast website.
516
00:44:45.045 --> 00:44:46.424
I visited in total
517
00:44:46.780 --> 00:44:48.940
5th 50 times over the past,
518
00:44:49.740 --> 00:44:56.640
over the past 10 years, like almost a decade now. Right? So, so that's a metric. So that that I would call life to
519
00:44:57.005 --> 00:44:57.664
life to
520
00:44:58.045 --> 00:45:05.345
date visits. But then it's really interesting to get something like 7 day visits, 28 day visits, you know, 90 day visits.
521
00:45:05.710 --> 00:45:08.290
So then for each cuss for each,
522
00:45:08.990 --> 00:45:10.690
user in this case, you have
523
00:45:11.150 --> 00:45:13.730
a new set of columns, 1 that might be called,
524
00:45:14.425 --> 00:45:22.525
say, 90 day visits. Now you probably want something similar with time pivoted lessons. Right? Complete lessons, partial lessons,
525
00:45:23.200 --> 00:45:23.700
average
526
00:45:24.480 --> 00:45:24.980
completion
527
00:45:25.440 --> 00:45:25.940
of
528
00:45:26.240 --> 00:45:28.339
does this user, you know, typically
529
00:45:28.960 --> 00:45:32.500
listen to the whole episode and snippets or how much they
530
00:45:32.945 --> 00:45:34.085
might fast forward,
531
00:45:34.865 --> 00:45:37.765
how many times did they fast forward on their last listen.
532
00:45:38.145 --> 00:45:44.200
So there you have to think about what are the metrics that are most useful in relation to that entity.
533
00:45:44.579 --> 00:45:46.599
In this case, say, visit, listens,
534
00:45:47.299 --> 00:45:49.319
percentage completion of listen.
535
00:45:49.915 --> 00:45:55.695
And you have to think about what are the time frames that are relevant to your business and time analysis.
536
00:45:56.635 --> 00:45:59.135
Typically, you know, 7 days, 28 days,
537
00:45:59.520 --> 00:46:00.020
90
538
00:46:00.400 --> 00:46:03.540
days, 1 year, life to date, and then start
539
00:46:04.240 --> 00:46:08.214
pivoting these things. Right? Start computing and pivoting these,
540
00:46:09.255 --> 00:46:09.994
these columns.
541
00:46:10.375 --> 00:46:13.275
Now, like, 1 thing I talk about in the blog post is, like,
542
00:46:13.750 --> 00:46:16.890
having all of these time bound metrics
543
00:46:17.190 --> 00:46:17.930
that are
544
00:46:18.230 --> 00:46:19.450
rich and useful
545
00:46:20.390 --> 00:46:22.410
make a new class of analysis,
546
00:46:24.255 --> 00:46:29.155
very, very natural and simple. Right? So if you have to join to multiple fact tables
547
00:46:29.934 --> 00:46:32.994
to go to and to do, like, intricate time filtering
548
00:46:33.440 --> 00:46:47.085
to try to gather that together to run an analysis, you you might not bother. Right? Or you might not bother on, like, okay. I'm gonna do this complex joint to get the number of, like, 28 day, you know, listens for that user.
549
00:46:48.000 --> 00:46:50.660
But if they are there at your fingertip, all of a sudden,
550
00:46:50.960 --> 00:46:53.940
it becomes really easy and natural to do segmentation
551
00:46:54.480 --> 00:46:57.565
and engagement analysis and, like, give me the users
552
00:46:57.945 --> 00:47:02.685
or targeting. Right? But give me the users that have not listened to any of my podcasts in the past
553
00:47:03.370 --> 00:47:03.870
year,
554
00:47:04.650 --> 00:47:05.790
but I've demonstrated
555
00:47:06.170 --> 00:47:12.465
interest on something else that is relevant and use that for targeting, like, I'm I'm gonna shoot them, you know, this
556
00:47:12.945 --> 00:47:13.445
email,
557
00:47:14.145 --> 00:47:22.059
for them to reengage with this episode. So so as these these tables have a mix of, say, in the case of users, they have user attributes, like the the traditional user
558
00:47:22.680 --> 00:47:33.015
demographics type things, but you enrich that at your fingertips with a bunch of, like, metrics that are very relevant to your business and timely too. And that's, that's that can be extremely useful.
559
00:47:33.315 --> 00:47:36.214
And as an extension to that exercise,
560
00:47:36.755 --> 00:47:38.055
what are some of the
561
00:47:38.369 --> 00:47:47.269
specific elements that maybe introduce added complexity or maybe it's all the same thing when you're talking about working across multiple different
562
00:47:48.005 --> 00:47:53.545
sub organizations or business units within a larger organization, particularly thinking in terms of enterprise
563
00:47:54.005 --> 00:47:56.859
users where they have multiple different,
564
00:47:57.480 --> 00:47:57.980
entities
565
00:47:58.280 --> 00:48:05.914
or categories of entities or bucketing of entities that they maybe then want to stitch together across the entire organization
566
00:48:06.214 --> 00:48:09.115
to be able to get a lateral view of everything
567
00:48:09.494 --> 00:48:27.535
at maybe a higher level. Yeah. I mean, in in some ways, you know, this type of data modeling, like, enables you to have a more, like, you know, a 3 60 view of the entity. I hate that term. It sounds so, like, mart marketing y. But, like, okay, well, we have, you take a really core entity,
568
00:48:28.320 --> 00:48:28.980
the user.
569
00:48:29.280 --> 00:48:46.085
And then, of course, there's some metrics that matter most to, say, the marketing department and to the sales department or to the product department. They're like, oh, how are the people engaging with the product? Which features are they using? But I would argue that in the end, they're all, like, useful feature
570
00:48:46.760 --> 00:48:47.260
features
571
00:48:48.040 --> 00:48:58.875
of the user that may or may not predict their behavior or may or may not, you know, be relevant to another department. So in the context of a feature storage, like, cram more features in there,
572
00:49:00.055 --> 00:49:03.595
that may or may not be useful or predict
573
00:49:03.940 --> 00:49:13.545
the behavior you're trying to predict through machine learning. But I think in this case, you know, 1 challenge is clearly the well, for on the consumption size
574
00:49:13.845 --> 00:49:17.224
side, if you have, like, hundreds and hundreds of columns for a user,
575
00:49:17.605 --> 00:49:22.930
then you're like, okay, which ones of these columns are actually make sense, are usable, are fresh?
576
00:49:23.630 --> 00:49:25.170
There's some challenges around
577
00:49:25.790 --> 00:49:26.770
just latency
578
00:49:27.630 --> 00:49:28.450
too because
579
00:49:29.305 --> 00:49:42.530
now if you wait for the you have to wait for the entire data warehouse and all the facts to come together for your user table to be ready. So and and and then there's, like, a documentation issue of, like, what does the column called m_ltv_,
580
00:49:45.954 --> 00:49:46.694
you know,
581
00:49:47.395 --> 00:49:51.494
visit qda. Like, what is qda? Like, I don't so so I think there's a documentation
582
00:49:51.795 --> 00:49:52.295
and
583
00:49:52.595 --> 00:49:54.694
issue around it of how do you,
584
00:49:55.849 --> 00:49:57.150
you know, document
585
00:49:57.609 --> 00:49:59.230
these these these metrics
586
00:49:59.530 --> 00:50:02.030
so that they are actually relevant to people.
587
00:50:02.730 --> 00:50:03.470
There's also
588
00:50:04.105 --> 00:50:05.645
some issues potentially around,
589
00:50:06.025 --> 00:50:13.805
you know, performance. And what are the column stores? You know, if you have super wide table, it doesn't really matter. But, you know, in the parquet file world,
590
00:50:14.140 --> 00:50:23.075
now you end up with these, like, parquet chunks that don't have a lot of rows because they have a lot of columns in them. So there's, like, some some techniques to be applied
591
00:50:23.775 --> 00:50:31.154
there. The latency 1, the spaghetti issue, is like now your dimension table and your DAG depends on your fact table
592
00:50:31.500 --> 00:50:36.800
and your and your DAG. And the fact tables depend on other things. So you can get into circular
593
00:50:37.100 --> 00:50:37.600
dependency
594
00:50:37.900 --> 00:50:38.720
type challenges
595
00:50:39.020 --> 00:50:40.080
too because now
596
00:50:40.555 --> 00:50:43.535
the complexity score of building your user table
597
00:50:44.075 --> 00:50:56.250
is not just like, I'll get all the demographics and be done with it. It's build all of your fact tables and pivot time and build, you know, a 100 metrics and crown them into your user tables. So that brings a,
598
00:50:56.895 --> 00:50:59.555
you know, ETL kind of that complexity
599
00:51:00.415 --> 00:51:01.315
set of challenges
600
00:51:01.935 --> 00:51:06.000
too. So in the blog post, I talk about some of these things. I talk about, what I called
601
00:51:06.480 --> 00:51:07.460
vertical partitioning.
602
00:51:08.640 --> 00:51:12.900
That's 1 idea to say different groups of attributes can actually be stored in different
603
00:51:13.675 --> 00:51:15.855
user table. So you would have you use
604
00:51:16.235 --> 00:51:26.000
a table maybe called use, you know, dim user extended with all the fields, but you might have dim user, you know, marketing or with different subsets or groups of columns.
605
00:51:26.619 --> 00:51:27.119
There's
606
00:51:27.420 --> 00:51:31.520
probably a little bit more to it, but I I talk in the blog post too around, like, how to
607
00:51:31.825 --> 00:51:34.005
how to deal with the circular dependency
608
00:51:34.545 --> 00:51:39.125
issue. And the idea there is to to have, an extra layer. And
609
00:51:39.450 --> 00:51:46.910
the warehouse so people familiar with the idea of a staging area, that's where you receive all your your data that's very raw from the different system,
610
00:51:47.370 --> 00:51:48.430
mostly unprocessed
611
00:51:48.835 --> 00:51:49.815
and raw ingredients,
612
00:51:50.355 --> 00:52:04.840
the same way that a staging area for our warehouse is just where, you know, the trucks unload. And then, you know, then there's, you know, the back room and the work table. So let's say, like, there you would do a bunch of entity bound work and fact bound work.
613
00:52:05.505 --> 00:52:14.645
And then you have, I think I I forgot if I call it shuffle layer in the blog post, but that's where you pick from these, like, premade assets and and
614
00:52:15.220 --> 00:52:15.720
merge,
615
00:52:16.339 --> 00:52:19.640
call it, like, entity attributes and facts together.
616
00:52:20.180 --> 00:52:22.279
There's no perfect way to do it, you know,
617
00:52:22.625 --> 00:52:28.005
and, it's a little bit at at your discretion given your your your style and practices. But
618
00:52:28.305 --> 00:52:35.339
but yeah. So those are definitely, like, some of the challenges that come with with this approach. 1 thing is, like, I think progressive adapt
619
00:52:35.720 --> 00:52:36.220
adaptability
620
00:52:36.520 --> 00:52:40.380
of any method methodology is really important. Right? You're not gonna go from
621
00:52:40.715 --> 00:52:47.855
what I do, start schema now, and I we're moving to entity centric modeling. We have to rewrite the whole warehouse. No. Right? It's like, okay.
622
00:52:48.400 --> 00:53:00.785
At first, like, how do we sprinkle some more metrics and nested structure into our our dimension tables? Like, now that we know it's okay to do it, where do we start? Like, what would be what are what are the top 3
623
00:53:01.085 --> 00:53:01.585
user
624
00:53:02.285 --> 00:53:05.360
useful metrics for this business? And let's just start with those.
625
00:53:06.000 --> 00:53:06.500
Absolutely.
626
00:53:06.800 --> 00:53:07.540
And also
627
00:53:08.400 --> 00:53:17.365
the entire concept of building the building the table structures around these different entities also brings the question of entity extraction, which is a whole another episode.
628
00:53:18.385 --> 00:53:20.885
And for and and so in your experience
629
00:53:21.185 --> 00:53:23.500
of working through this problem space,
630
00:53:24.119 --> 00:53:24.619
documenting
631
00:53:24.920 --> 00:53:33.435
these concepts of entity centric modeling, working with the customers that you have at preset who are trying to drive these different insights and visualizations
632
00:53:33.735 --> 00:53:44.609
of their data based on the different domain structures that they've built up in their warehouse. What are some of the most interesting or innovative or unexpected ways that you've seen people approaching this practice of entity centric modeling?
633
00:53:45.184 --> 00:54:05.964
Yeah. So we have limited visibility as to, like, exactly, like, what our customers and users do, you know, at preset, like, in and for good reasons. Right? Like, so we we cannot get into your, you know, your private data warehouse in any way. 1 thing I can talk about is how I've seen I would say, over the past decade, like, since the beginning of the the Hadoop era,
634
00:54:06.744 --> 00:54:09.484
how people who didn't come from a background
635
00:54:09.785 --> 00:54:17.540
of, say, star schema or dimensional modeling have been modeling their table on some some interesting use cases that we've seen emerge
636
00:54:19.025 --> 00:54:32.260
over the years. But I think when I first saw the what I call the time bound metrics was at Facebook in a table called dim user. And dim user was the most used table in the entire data warehouse
637
00:54:32.960 --> 00:54:35.380
of, of all of Facebook. And
638
00:54:35.760 --> 00:54:37.525
this dimension table,
639
00:54:39.185 --> 00:54:43.125
like, had 100 by 1,000 of columns that had
640
00:54:43.480 --> 00:54:44.359
to do with,
641
00:54:44.760 --> 00:54:49.819
with their behavior on Facebook or, like, basically, everything related to user was
642
00:54:50.200 --> 00:54:50.700
crammed
643
00:54:51.160 --> 00:54:56.775
back in to this or not not everything was crammed back into this. But, like, things like so say,
644
00:54:57.075 --> 00:55:07.590
if you work on the photos team, you might wanna know how many photos does this person have uploaded over the past 28 days. Right? Are they a tagger? Do they tag people in photos?
645
00:55:08.130 --> 00:55:14.435
Are they do they use search? Do they you know, and on what time frame? So we start seeing a lot of, like, metric,
646
00:55:14.975 --> 00:55:22.030
underscore 7 day, underscore 28 days, underscore 90 days. And for the data engineering, so I was part of the data engineering team.
647
00:55:22.330 --> 00:55:25.150
And, someone in my team was working on
648
00:55:25.975 --> 00:55:30.235
making sure this table this table is so large and so useful and so
649
00:55:31.335 --> 00:55:31.835
multidimensional
650
00:55:32.990 --> 00:55:42.210
that it became like it had a dependency on just about everything else in the warehouse and everything dependent on it. So it became like a very, very, very, very key
651
00:55:43.055 --> 00:55:45.075
hot spot or focal point
652
00:55:45.695 --> 00:55:48.995
in the warehouse, both in, like, what it took to build it
653
00:55:49.295 --> 00:55:51.395
and how much it was used downstream.
654
00:55:52.170 --> 00:55:58.190
But then once you have that I think the real thing that's really interesting is, like, once you have that at your fingertips,
655
00:55:58.730 --> 00:55:59.230
then
656
00:55:59.530 --> 00:56:01.654
doing things like cohort analysis
657
00:56:02.035 --> 00:56:02.855
and segmentation
658
00:56:04.115 --> 00:56:12.940
becomes very, very natural. Right? You don't need to be go and figure out, where's the data mark for photos and photo upload and how the heck am I gonna join this?
659
00:56:13.320 --> 00:56:17.180
It's more like, okay, now I have a table where there's 1 Roper table
660
00:56:17.560 --> 00:56:22.435
and there's the bulk of everything I might wanna know about this user, you know, is,
661
00:56:23.395 --> 00:56:33.370
is there at my fingertips. So I can start saying, like, hey, the users we did this, we did not do that in a certain time frame or in a different time frame. There's a lot you can do there.
662
00:56:33.990 --> 00:56:35.110
1 thing too that,
663
00:56:35.670 --> 00:56:36.490
that existed
664
00:56:37.125 --> 00:56:42.585
there that was, like, very much a pattern. That's something I talked about in my functional data engineering,
665
00:56:43.365 --> 00:56:48.810
blog post a while ago too is the idea of snapshotting dimensions too. So that means not only
666
00:56:49.190 --> 00:56:51.610
you enrich your dimension with,
667
00:56:53.030 --> 00:56:57.615
with with things like time bound metrics and nested structure.
668
00:56:58.155 --> 00:57:06.750
And and we could get into nested structure a little bit more, like, you know, like, what what are some examples, like, why is that relevant. But so not only you have a super rich
669
00:57:07.289 --> 00:57:09.710
entity centric table, they call it demuser@facebook
670
00:57:10.730 --> 00:57:22.285
with, you know, billions of users and a 1000000 attributes about them. But we also would snapshot these dimensions. So you could do basically, every day, there's a new partition added to the dim table
671
00:57:22.589 --> 00:57:28.690
with all of the users. So if, you know, today, you have 10,000,000,000 user. Yesterday, you had 9,000,000,000, 99100,
672
00:57:29.230 --> 00:57:34.105
you know. And and then you can do time series analysis on these rich
673
00:57:34.485 --> 00:57:41.410
tables too. So that's something I do talk about in the blog post. And in general, I think is a good practice. So if you treat your,
674
00:57:41.970 --> 00:57:47.589
your datasets as immutable, something that's very natural when you build your dimension is to create a new
675
00:57:48.035 --> 00:57:48.535
snapshot
676
00:57:48.994 --> 00:57:56.615
of your dimension table every day. And when you have that, you know, when you have this table, it becomes really natural to do
677
00:57:57.210 --> 00:57:59.630
time series analysis on your populations.
678
00:58:00.650 --> 00:58:07.685
Right? So here you could say, give me the percentage of users who visited more than 6 days over the past 7 days
679
00:58:08.145 --> 00:58:09.445
and how that's evolving
680
00:58:09.825 --> 00:58:10.565
over time.
681
00:58:11.025 --> 00:58:17.099
So there you would use a metric called l 7 visits, which represent the number of visits over the past 7 days.
682
00:58:17.480 --> 00:58:30.445
Then you could say, which percentage of my user visit more than 6 out of the past 7 days? Okay. I got that. I I can give you a nice big percentage. But how is that evolving over time? You do that through the idea of snapshotting your dimensions,
683
00:58:30.810 --> 00:58:32.650
being able to do time series on,
684
00:58:33.609 --> 00:58:41.155
on your dimension table. So that's that 1 is a little bit more controversial. You don't have to do it if you do entity centric data modeling, but I think it's just a great
685
00:58:41.615 --> 00:58:42.575
practice to,
686
00:58:42.975 --> 00:58:52.400
to do that. And once you do that, of course, there's compute costs associated to that. Right? Because now you're storing your whole snapshot of your whole dimension every day. But then the empowerment
687
00:58:53.020 --> 00:58:57.119
in terms of, like, the kind of analysis you can do very easily on these tables
688
00:58:57.765 --> 00:59:00.265
is, is is really, really powerful.
689
00:59:01.125 --> 00:59:01.625
And
690
00:59:01.925 --> 00:59:02.825
in your experience
691
00:59:03.125 --> 00:59:05.465
of working in this space and
692
00:59:05.910 --> 00:59:06.730
trying to
693
00:59:07.030 --> 00:59:15.735
distill the different requirements and use cases and capabilities around data modeling, what are some of the most interesting or unexpected or challenging lessons that you've worked through?
694
00:59:16.295 --> 00:59:16.795
Yeah.
695
00:59:17.175 --> 00:59:17.915
Well, also,
696
00:59:18.215 --> 00:59:29.359
I think the thing that's really difficult with data modeling is that there's there's no the best practices are not clear. They tend to emerge culturally within an organization.
697
00:59:31.099 --> 00:59:32.400
There's different like,
698
00:59:32.780 --> 00:59:35.039
now everyone's been invited to the party,
699
00:59:35.435 --> 00:59:38.655
and there's a there's a wide spectrum from
700
00:59:39.115 --> 00:59:41.855
amateur and people trying to figure it out
701
00:59:42.315 --> 00:59:43.375
all the way to,
702
00:59:43.755 --> 00:59:47.089
you know, experts that really know their art.
703
00:59:47.630 --> 00:59:48.109
And,
704
00:59:48.990 --> 00:59:50.930
and right now, we're all
705
00:59:51.755 --> 00:59:54.974
working together. Right? Like, we've seen people I've seen people make, like,
706
00:59:55.435 --> 00:59:55.935
gigantic
707
00:59:56.955 --> 01:00:09.645
mistakes in terms of, like, just basic, like, SQL writing and data modeling logic. Just like joining on a field that's not unique, like doing these Cartesian joins. Right? That just like and they're just not aware that
708
01:00:10.184 --> 01:00:25.349
this is gonna be a problem or right? So so there's some, like, very basic mistakes that get made. There's also people who don't know Git. So we've seen people that kinda get put get push master on on, in certain areas that, you know,
709
01:00:25.715 --> 01:00:29.175
they they they use a git GUI, and they don't really understand,
710
01:00:29.635 --> 01:00:31.655
you know, how git and branching
711
01:00:32.115 --> 01:00:39.950
works too. So 1 thing is, like, you know, if you look at the distribution from amateur to expert, there's a lot more
712
01:00:40.250 --> 01:00:42.270
amateur now that are empowered,
713
01:00:42.664 --> 01:00:44.285
and that comes with empowerment,
714
01:00:44.585 --> 01:00:46.125
but some some risk
715
01:00:46.664 --> 01:00:49.884
and some issues too. So that's definitely an emerging pattern
716
01:00:50.750 --> 01:00:54.690
there. But yeah. And then in terms of, like, how to teach
717
01:00:55.630 --> 01:01:04.475
the data modeling type of, you know, how to educate people on that is kinda unclear because there's no there's no bible you can point to, really.
718
01:01:05.015 --> 01:01:14.080
So people have to look at, like, how other people do it, do a little code review, and and try to figure it out. But, yeah. It's it's as if, like, we had invited a bunch of,
719
01:01:15.280 --> 01:01:24.625
of of people to software engineering that, you know, barely know how to code, barely know how to get. And, that comes with a set of challenges. And the challenge is is education,
720
01:01:25.005 --> 01:01:26.945
I think, in a context where,
721
01:01:28.280 --> 01:01:31.420
you know, there's not a lot of material to to train people.
722
01:01:31.800 --> 01:01:39.645
So that I I guess, like, the the the DBT community has empowered a lot of that. Right? So people can go and get a lot of resources from, say, the dbt community
723
01:01:40.585 --> 01:01:40.985
and,
724
01:01:41.385 --> 01:01:44.605
and, you know, the dbt slack and the dbt documentation.
725
01:01:45.465 --> 01:01:48.510
But, you know, still there's still a gap in making people,
726
01:01:48.910 --> 01:01:49.410
experts
727
01:01:50.030 --> 01:01:51.410
and, tech depth.
728
01:01:51.710 --> 01:01:56.944
To to me, even for a seasoned expert data engineer that's really good at data modeling,
729
01:01:57.325 --> 01:02:01.904
it does feel still like data pipelines are tech depth from day 0. So now
730
01:02:02.420 --> 01:02:09.940
we we're dealing with a much more much bigger issue at scale, like these mountain of SQL that are growing and,
731
01:02:10.420 --> 01:02:11.480
brittle. And,
732
01:02:11.915 --> 01:02:12.895
I don't know if we'll
733
01:02:13.515 --> 01:02:16.095
ever kinda dig ourselves out of that hole.
734
01:02:17.115 --> 01:02:20.735
And for people who are working through these exercises
735
01:02:21.170 --> 01:02:23.510
of building up their data architecture,
736
01:02:23.890 --> 01:02:30.790
working in their data warehouse, what are the cases where entity centric modeling is the wrong choice and they're better suited with
737
01:02:31.315 --> 01:02:31.815
standard
738
01:02:32.115 --> 01:02:37.575
star schema or activity schema or wide tables or just no modeling at all.
739
01:02:39.119 --> 01:02:44.020
Yeah. Yeah. No modeling at all or, you know, I think I think there's a bunch of things that have become,
740
01:02:44.720 --> 01:02:59.310
you know, practices that we used to do that don't make sense anymore. I would say don't start with entity centric. Just start by I mean, I I think anything you're gonna do is gonna be in some way entity centric. Right? You're gonna look at what are my
741
01:02:59.770 --> 01:03:00.270
business
742
01:03:01.050 --> 01:03:07.464
objects, like what are my business entities and how do I make sense of them. I know 1 1 thing that's a bit unrelated
743
01:03:07.924 --> 01:03:13.625
to modeling per se, but 1 thing that's that's happening is, like, with the rise of the Fivetran, Airbyte,
744
01:03:14.349 --> 01:03:26.695
Meltano, and other, like, the data sync. Like, 1 thing we don't do anymore is, like, writing data sync. Right? Like, in in there, we enter it comprehensive models from these tools. So at least you'd have to data model
745
01:03:27.075 --> 01:03:32.079
the staging area. So the staging area, you you get it it lands into your data warehouse.
746
01:03:32.380 --> 01:03:34.000
I think 1 mistake that
747
01:03:34.779 --> 01:03:39.039
people tend to do and a bit of a pitfall is to go straight from that
748
01:03:39.615 --> 01:03:45.075
data sync to report. Right? Like, what is the SQL that I can run to create the report
749
01:03:45.535 --> 01:03:47.555
directly from my raw tables?
750
01:03:48.030 --> 01:03:49.410
And I think we all
751
01:03:49.869 --> 01:04:03.255
figured out, you know, over the years that you need some sort of, like, middleware there. I mean, like, middle section where you're gonna go from staging area into, like, pre transformed data and clean up and have a bit of a layered approach
752
01:04:03.609 --> 01:04:06.829
to your warehouse. I would say, like, don't go straight from
753
01:04:07.210 --> 01:04:08.750
your raw table into
754
01:04:09.130 --> 01:04:10.670
entity centric or
755
01:04:10.975 --> 01:04:22.359
or into reports. Right? Like, you think about, like, a layer that's much more like a probably something like a star schema to start with. So I would say, like, thinking about facts and dimensions
756
01:04:22.900 --> 01:04:32.494
in that first layer and the early layer is good. And then how do you bring your facts and your measures into your dimension? And maybe is a is a concern once you've solved,
757
01:04:33.135 --> 01:04:36.670
this the area of more like modeling your facts and dimension.
758
01:04:37.069 --> 01:04:38.930
And as you continue
759
01:04:39.230 --> 01:04:40.609
to work in this space
760
01:04:41.470 --> 01:04:46.049
and try to distill these various ideas and workflows and use cases,
761
01:04:46.405 --> 01:04:53.224
what are some of the predictions that you have for the future direction and adoption of entity centric modeling or
762
01:04:53.559 --> 01:04:56.380
maybe some other modeling techniques that are,
763
01:04:56.680 --> 01:04:58.700
waiting in the wings to come and
764
01:04:59.160 --> 01:04:59.980
obtain dominance?
765
01:05:00.495 --> 01:05:07.235
Yes. I would say I like to see the, I believe it's called activity schema idea. Right? I think there is a place
766
01:05:07.750 --> 01:05:09.530
for these generic
767
01:05:09.910 --> 01:05:10.410
metrics
768
01:05:10.710 --> 01:05:15.849
table. So we talked about, like, the metrics layer in the past and the future repository.
769
01:05:17.195 --> 01:05:26.175
As much as, like, what we're talking about here today, in terms of, like, in into the centric modeling is, like, an entity bound table with a lot of columns.
770
01:05:26.619 --> 01:05:34.799
The question of, like, how you get to a lot of columns, I think somewhere in your data warehouse, you need more of a metric centralized metric table, where
771
01:05:35.195 --> 01:05:38.335
that 1 would be a very thin table that is very
772
01:05:39.035 --> 01:05:53.935
long, that very, very high. Right? And and that, or by high, I mean, they're just the formatting of that table is not wide as thin. And and what's typically in those tables, it will be something like what is the time, what is the metric name,
773
01:05:54.235 --> 01:05:55.855
what is the entity name,
774
01:05:56.475 --> 01:06:03.300
or the entity type, the entity ID, and what is the value for that metric. And these are generic schemas
775
01:06:03.760 --> 01:06:05.300
that allow you to store
776
01:06:05.680 --> 01:06:13.875
things like events or metrics in time in a very kind of thin ways without having to change your schema. So I think we see that
777
01:06:14.255 --> 01:06:19.400
tends to exist quite a bit called a metrics repository or a feature repository,
778
01:06:20.099 --> 01:06:22.280
but it it's pretty common to go
779
01:06:23.015 --> 01:06:24.635
have that table that is
780
01:06:25.095 --> 01:06:35.160
metric bound, entity bound, and time bound. So I'll try to repeat it, like, but, yeah, it's very common pattern. You have a table that that has something like entity type customer,
781
01:06:35.540 --> 01:06:38.120
entity ID, you know, entity 1,
782
01:06:38.580 --> 01:06:39.800
metric visit,
783
01:06:40.225 --> 01:06:40.965
and value,
784
01:06:41.825 --> 01:06:49.365
and and time. So it would be on that date, this person for this metric at as this value. So once you have these very generic tables,
785
01:06:49.730 --> 01:06:53.430
you can do a lot of that pivoting I was talking about before.
786
01:06:53.730 --> 01:06:56.630
That's more like, okay. Now I want to I want to compute
787
01:06:57.115 --> 01:07:09.859
7 day visits for all my user, 28 day visits for my all my users. I wanna do that in 1 big scan. So once you have this table, it becomes much more natural to pick and choose how you're gonna bring metrics into your dimensions.
788
01:07:10.240 --> 01:07:11.059
And that table,
789
01:07:12.240 --> 01:07:16.265
or that that pattern is is close to what I believe, you know, act
790
01:07:16.905 --> 01:07:26.190
activity schema is about, and I believe is a place to loop back to what we were talking about in the beginning of episode. That may be a place where
791
01:07:26.569 --> 01:07:29.470
data scientists and data engineers can collaborate and
792
01:07:29.849 --> 01:07:30.829
share definitions
793
01:07:31.845 --> 01:07:48.290
upstream for their own pipeline or to agree of, like, what are the metrics all the metrics we track for each entity type and on what time frame. So there there's probably much more to to be written there. Right? Or maybe there's a question, like, how do you take something that's activity schema,
794
01:07:48.984 --> 01:07:51.325
entity centric, and and, you know, produce
795
01:07:51.865 --> 01:07:53.325
entity centric table
796
01:07:53.625 --> 01:07:56.585
off of those. I I wanna say too, like, there's 1 thing we,
797
01:07:57.150 --> 01:08:07.165
we didn't talk about, which is, you know, I said early in the episode that the I wanted to legitimize bringing metrics and time bound metrics to dimension tables.
798
01:08:07.545 --> 01:08:14.445
Another thing I think that's important is bringing or allowing people to bring nested complex data structure
799
01:08:15.020 --> 01:08:15.520
inside
800
01:08:15.900 --> 01:08:19.120
dimension tables too. And going back to the podcast
801
01:08:19.580 --> 01:08:23.840
user listener table we're talking about, like, maybe it does make sense to
802
01:08:24.224 --> 01:08:36.910
have an array of what are the top the last 5 episodes the user listened to. Right? Like, for whatever reason, like, and if you wanted to do that, well, you're not gonna create 1 column called, you know,
803
01:08:37.290 --> 01:08:49.745
last episode they listened to, previous episode they listened to. If you wanna track, like, what percentage of the episode they listened to, right, 1 okay. What is the episode ID, episode name, and what percentage of the episode did they listen to?
804
01:08:50.200 --> 01:08:52.300
Okay. Well, now you'd have 3 columns
805
01:08:52.680 --> 01:08:54.219
times 10 episodes
806
01:08:54.600 --> 01:09:06.574
that we have to manage this dynamically. So then they're, like, well, this is, to me, really valid information, really core information to the user, like, 10 last episode. I wanna put that in the table. Well, put an array of, of
807
01:09:07.550 --> 01:09:09.650
obstructs in there and put it in there.
808
01:09:10.110 --> 01:09:16.450
And, it will be easy for retrieval with a little bit of a JSON extract type function or,
809
01:09:16.835 --> 01:09:20.034
you know, whatever the database of choice, you know, that use,
810
01:09:21.074 --> 01:09:22.855
offers to query this.
811
01:09:23.395 --> 01:09:40.844
Now you could do a nest type operation, and you've seen those in SQL. But you could say, hey, if I have an array of 10 things, you can say generate pivot that into 10 rows into my query. And people know how to do that now. Like it comes naturally for software engineer to to work with data structures and
812
01:09:41.145 --> 01:09:54.605
and pivot things. So so don't necessarily abuse that. You don't wanna put all the facts, you know, about all the users as necessary structure, but it's something that you can do for things that are best represented that way too.
813
01:09:55.065 --> 01:10:16.225
Alright. Well, for anybody who wants to get in touch with you and follow along with the work that you're doing, I'll have you add your preferred contact information to the show notes. And as the final question, as usual, this is, I don't know how many times you've answered this, but from your perspective of today, what do you see as being the biggest gap of the tooling our technology that's available for data management?
814
01:10:16.620 --> 01:10:25.120
Yeah. Maybe I'll I'll go on, you know, we talked about this just to stay with the theme of the episode. I think I would say, like, education
815
01:10:26.585 --> 01:10:36.460
and data modeling. Like, we it'd be great if we had, like, data modeling 101, and and maybe that's, like, somewhat provided in in and around, like, say, the DBT community.
816
01:10:37.080 --> 01:10:49.335
But, you know, it's like what what even if it was just, like, the the the 30 things that you should know before you go and pile on your SQL to the amount in a SQL that your organization has
817
01:10:49.715 --> 01:10:59.860
in terms of I I think there's much more to data modeling in that, but data modeling is a component of it. So, you know, things like if if you work with SQL
818
01:11:00.585 --> 01:11:04.445
and you create this set, you should know what the like, normalization, denormalization
819
01:11:04.825 --> 01:11:10.739
means. You should know, you know, what the dimension and effect is. You you know, so what is that vocabulary.
820
01:11:12.400 --> 01:11:20.494
And there's a there's there's a bit of a gap there. Right? If you talk to 10 different DBT users, they might not use the same
821
01:11:21.275 --> 01:11:21.775
nomenclature
822
01:11:22.474 --> 01:11:23.135
and practices
823
01:11:23.594 --> 01:11:31.219
and convention. So we need to 0 in here on a on a set of, like, knowledge that people agree about or even just, like,
824
01:11:31.600 --> 01:11:37.785
definitions and and, terms that everyone knows that is close to that that area.
825
01:11:38.245 --> 01:12:07.260
Alright. Well, thank you as always for taking the time today to join me and share the work that you're doing and your thoughts on this overall space of domain modeling, the benefits of entity centric approaches to that, and the ways that our newer compute substrates enable more detailed approaches to data exploration. So, appreciate you taking the time, and I hope you enjoy the rest of your day. Yeah. It was awesome to be on the show as always, and, I'm sure I'll be back soon enough. So enjoy your day too.
826
01:12:13.265 --> 01:12:23.580
Thank you for listening. Don't forget to check out our other shows, podcast dot in it, which covers the Python language, its community, and the innovative ways it is being used, and the Machine Learning podcast,
827
01:12:24.125 --> 01:12:28.545
which helps you go from idea to production with machine learning. Visit the site at dataengineeringpodcast.com
828
01:12:29.965 --> 01:12:40.040
to subscribe to the show, sign up for the mailing list, and read the show notes. And if you've learned something or tried out a product from the show, then tell us about it. Email hosts at data engineering podcast.com
829
01:12:40.580 --> 01:12:46.844
with your story. And to help other people find the show, please leave a review on Apple Podcasts and tell your friends and coworkers.