Meeting Title: Uttam <> Ryan Weekly Date: 2025-01-15 Meeting participants: Luke Daque, Uttam Kumaran
WEBVTT
1 00:31:02.040 ⇒ 00:31:02.840 Uttam Kumaran: Hey, Ryan.
2 00:31:17.420 ⇒ 00:31:19.569 Uttam Kumaran: I cannot hear you.
3 00:31:20.870 ⇒ 00:31:21.690 Luke Daque: Hello! Hello!
4 00:31:22.370 ⇒ 00:31:22.930 Uttam Kumaran: Yeah. Sorry.
5 00:31:22.930 ⇒ 00:31:24.679 Luke Daque: Yeah, there you go. Cool.
6 00:31:28.930 ⇒ 00:31:30.650 Luke Daque: Yeah. How’s how’s everything?
7 00:31:32.040 ⇒ 00:31:33.195 Uttam Kumaran: Good dude, just
8 00:31:33.880 ⇒ 00:31:41.069 Uttam Kumaran: like working on Dvc stuff for a bit, and then like. Got through a bunch of stuff in the morning. So the rest of my day is sort of
9 00:31:41.430 ⇒ 00:31:43.260 Uttam Kumaran: development work, which is good
10 00:31:46.370 ⇒ 00:31:49.310 Uttam Kumaran: and just poking@onethingfirstst
11 00:32:11.040 ⇒ 00:32:13.549 Luke Daque: Let me just get some drink.
12 00:32:14.210 ⇒ 00:32:14.970 Uttam Kumaran: Okay, no worries.
13 00:33:27.340 ⇒ 00:33:34.230 Uttam Kumaran: Okay, I’m literally just like getting my own environment set up on my new machine, too. So let me just get Bs up and.
14 00:33:40.210 ⇒ 00:33:41.569 Luke Daque: Or Eden.
15 00:33:42.420 ⇒ 00:33:43.050 Uttam Kumaran: Yeah.
16 00:34:14.260 ⇒ 00:34:17.590 Uttam Kumaran: do you use any of the bigquery extensions or no?
17 00:34:18.900 ⇒ 00:34:22.456 Luke Daque: I I didn’t i i’m not using it at the moment.
18 00:34:31.080 ⇒ 00:34:32.869 Uttam Kumaran: You’re just running directly in bigquery.
19 00:34:33.199 ⇒ 00:34:34.079 Luke Daque: Yeah.
20 00:34:36.199 ⇒ 00:34:42.629 Luke Daque: Well, the Dbt extension still works, though. And if it’s a in bigquery.
21 00:34:42.989 ⇒ 00:34:46.829 Uttam Kumaran: Oh, really do you, are you? Which dbt extension are you using.
22 00:34:47.909 ⇒ 00:34:50.329 Luke Daque: The Dbd power, user thing.
23 00:34:53.969 ⇒ 00:34:55.909 Uttam Kumaran: It’s power user per dvt.
24 00:34:55.909 ⇒ 00:34:57.599 Luke Daque: Yeah, that one.
25 00:35:01.559 ⇒ 00:35:03.929 Luke Daque: That’s what I’ve been using.
26 00:35:17.459 ⇒ 00:35:18.679 Luke Daque: Oh, very intact.
27 00:36:32.280 ⇒ 00:36:35.070 Uttam Kumaran: Python on this machine yet, so you can just install it.
28 00:36:39.220 ⇒ 00:36:41.242 Luke Daque: There’s a very new one machine.
29 00:36:41.920 ⇒ 00:36:46.980 Uttam Kumaran: I got a macbook a Mac mini.
30 00:36:47.970 ⇒ 00:36:54.110 Luke Daque: Oh, yeah, I think you you showed that like last last month, I guess.
31 00:36:54.350 ⇒ 00:37:01.430 Uttam Kumaran: Yeah, but dude, I haven’t been doing any development. Snowflake. So yeah.
32 00:38:01.090 ⇒ 00:38:01.770 Luke Daque: Hmm!
33 00:39:44.610 ⇒ 00:39:48.079 Luke Daque: Oh, anyway. I did try to look into the
34 00:39:49.190 ⇒ 00:39:56.010 Luke Daque: like. The screenshot of the dashboards that they had. Looks like it’s all product related. And like, it’s just
35 00:39:56.410 ⇒ 00:40:02.060 Luke Daque: per product units sold and revenue. Basically, that’s it.
36 00:40:02.340 ⇒ 00:40:07.490 Luke Daque: And yeah, so I, I did try to look at like what
37 00:40:07.740 ⇒ 00:40:13.670 Luke Daque: tables we can potentially use. But it looks like, maybe it’s just the back bask orders.
38 00:40:14.910 ⇒ 00:40:21.999 Luke Daque: Although there’s like 3 different data sets there for Baske orders. There’s like bask orders completed.
39 00:40:22.190 ⇒ 00:40:27.219 Luke Daque: pass order shipped and updated. So most likely we will just use the completed ones for this.
40 00:40:27.220 ⇒ 00:40:27.860 Uttam Kumaran: Yeah.
41 00:40:30.900 ⇒ 00:40:33.190 Luke Daque: Yeah, I did try to.
42 00:40:33.920 ⇒ 00:40:38.709 Uttam Kumaran: So they have, but you can see that they have 3 different order. They have, like order, status.
43 00:40:41.450 ⇒ 00:40:43.000 Luke Daque: In bask orders.
44 00:40:43.530 ⇒ 00:40:48.469 Uttam Kumaran: No, in the final table. There’s there’s a couple of metrics based on what table is coming in from
45 00:40:49.010 ⇒ 00:40:50.170 Uttam Kumaran: the status.
46 00:40:50.490 ⇒ 00:40:54.650 Uttam Kumaran: So maybe our best bet is to create a union table with everything, or what.
47 00:40:57.610 ⇒ 00:41:02.890 Luke Daque: You mean? Which final table are you referring to the the one that’s coming from the scheduled query.
48 00:41:19.650 ⇒ 00:41:22.930 Uttam Kumaran: Okay, let me just let me finish installing this, and I’ll open it as well.
49 00:45:12.330 ⇒ 00:45:13.290 Luke Daque: Outlooks.
50 00:52:06.020 ⇒ 00:52:11.239 Uttam Kumaran: So I have Dbt installed. I have the both the adapters installed.
51 00:52:12.090 ⇒ 00:52:16.600 Uttam Kumaran: What’s the best way to set up the Dbt power user?
52 00:52:19.610 ⇒ 00:52:20.720 Uttam Kumaran: Extension.
53 00:52:21.750 ⇒ 00:52:24.020 Luke Daque: You mean like.
54 00:52:24.020 ⇒ 00:52:26.210 Uttam Kumaran: Do you just automatically see everything on your side?
55 00:52:27.680 ⇒ 00:52:31.519 Luke Daque: Yeah, I, yeah, it should work as long as like we have the
56 00:52:34.390 ⇒ 00:52:41.590 Luke Daque: Let’s the profiles, yamo setup. And like the DVD project, it should work.
57 00:52:42.290 ⇒ 00:52:42.970 Uttam Kumaran: Okay.
58 00:52:55.130 ⇒ 00:53:03.550 Luke Daque: Think, especially dimensions. Are you? Are you installing it in cursor?
59 00:53:04.870 ⇒ 00:53:06.139 Uttam Kumaran: No, I’m on Bs code.
60 00:53:06.140 ⇒ 00:53:07.540 Luke Daque: Oh, you’re in Vs code.
61 00:53:12.250 ⇒ 00:53:14.850 Luke Daque: Yeah, that’s essentially what I did.
62 00:53:16.790 ⇒ 00:53:22.079 Luke Daque: I saw it both in cursor and Vs code, and it worked well.
63 00:54:00.850 ⇒ 00:54:04.330 Uttam Kumaran: But you’re using Dvt core like you’re using your credentials locally.
64 00:54:05.340 ⇒ 00:54:12.210 Luke Daque: Yeah, currently, just yeah, I guess we can create a service account for for it.
65 00:54:13.410 ⇒ 00:54:19.999 Luke Daque: Well, I guess maybe we can use the service account that you create. You used for Dbt
66 00:54:20.960 ⇒ 00:54:27.830 Luke Daque: setting up the project. We can use that like if you have the the the Json thing.
67 00:54:31.500 ⇒ 00:54:37.359 Luke Daque: Json, we can use that I can. Yeah, let me see.
68 00:54:37.780 ⇒ 00:54:41.590 Uttam Kumaran: Do you have your profiles? Does it have your your credentials in it?
69 00:54:41.590 ⇒ 00:54:48.560 Luke Daque: I haven’t yet. I haven’t created my profiles in Dbt core, because I was like working on using Dbt cloud at the moment.
70 00:54:49.240 ⇒ 00:54:51.559 Luke Daque: So basically, whatever yeah.
71 00:54:52.500 ⇒ 00:54:53.750 Uttam Kumaran: I guess I’ll create one.
72 00:54:55.680 ⇒ 00:54:56.320 Luke Daque: Sure.
73 00:55:00.710 ⇒ 00:55:03.119 Uttam Kumaran: We can use both right? It’s not gonna matter.
74 00:55:03.760 ⇒ 00:55:07.630 Luke Daque: What do you mean like? We can use the service account for both the project and.
75 00:55:07.930 ⇒ 00:55:13.359 Uttam Kumaran: No, no, like we can have people running stuff on core, and we can run it via.
76 00:55:13.750 ⇒ 00:55:14.799 Luke Daque: Yeah, it should work.
77 00:55:30.990 ⇒ 00:55:33.080 Luke Daque: Yeah, we can use this service account.
78 00:55:33.480 ⇒ 00:55:45.780 Luke Daque: Json, with that with the file service account file something like this. Lucinda link.
79 00:55:52.270 ⇒ 00:55:54.980 Luke Daque: Yeah, let me maybe let me just set up my
80 00:55:55.850 ⇒ 00:55:58.239 Luke Daque: course core as well. DVD. Core as well.
81 00:56:41.080 ⇒ 00:56:44.399 Uttam Kumaran: And then you just did Oauth to log in right.
82 00:56:46.610 ⇒ 00:56:48.150 Luke Daque: In DVD. Cloud.
83 00:56:49.050 ⇒ 00:56:50.790 Uttam Kumaran: No, for your local.
84 00:56:53.980 ⇒ 00:56:55.359 Luke Daque: In where, in which, one.
85 00:56:55.720 ⇒ 00:56:58.490 Uttam Kumaran: Like in order to authenticate your profile.
86 00:56:59.840 ⇒ 00:57:07.870 Uttam Kumaran: Typically like, did you just use your password, or you used a service account file or.
87 00:57:09.440 ⇒ 00:57:17.200 Luke Daque: Oh, yeah, you can. Yeah, you can just use O off your your password username password.
88 00:57:20.130 ⇒ 00:57:22.280 Uttam Kumaran: Do you have that like profiles.
89 00:57:22.730 ⇒ 00:57:27.259 Luke Daque: No at the moment, because yeah, because we’re using, I’m I was using Dp cloud.
90 00:57:27.260 ⇒ 00:57:30.570 Uttam Kumaran: Oh, okay, sorry. I’m like, okay, I’ll get it. Now I get it.
91 00:57:34.980 ⇒ 00:57:38.629 Luke Daque: But then I’m also setting up my dpt core right now.
92 00:59:35.120 ⇒ 00:59:35.870 Luke Daque: Issue.
93 01:00:52.600 ⇒ 01:00:54.320 Luke Daque: I guess we can save the
94 01:00:54.860 ⇒ 01:00:58.549 Luke Daque: the key file in the project and then just make it.
95 01:00:59.320 ⇒ 01:01:00.379 Luke Daque: I just ignore it.
96 01:01:00.380 ⇒ 01:01:04.489 Uttam Kumaran: We don’t even need. Well, yeah, we basically. Well, it can reoff every time.
97 01:01:05.440 ⇒ 01:01:09.989 Uttam Kumaran: And I’ll just ignore the key file. So let me, I’m just gonna try it on my side
98 01:01:10.730 ⇒ 01:01:11.440 Uttam Kumaran: like
99 01:01:14.010 ⇒ 01:01:16.220 Luke Daque: But we should be able to like.
100 01:01:21.210 ⇒ 01:01:26.839 Luke Daque: yeah, like, just save it in as a the key file in our Dbt project and just
101 01:01:27.440 ⇒ 01:01:29.059 Luke Daque: ignore it. I guess.
102 01:01:29.300 ⇒ 01:01:30.840 Uttam Kumaran: Oh, yeah. Exactly.
103 01:01:32.600 ⇒ 01:01:34.630 Luke Daque: Let me see, I think I have. We have.
104 01:01:35.850 ⇒ 01:01:41.450 Luke Daque: Oh, do you have the Json of the you are the one who created the service account right
105 01:01:46.890 ⇒ 01:01:50.150 Luke Daque: or Dbt. When you created the Dbt project.
106 01:01:52.626 ⇒ 01:01:55.820 Uttam Kumaran: Yes, it should be in one password.
107 01:01:56.240 ⇒ 01:01:58.539 Luke Daque: Oh, okay, let me check that
108 01:02:04.390 ⇒ 01:02:06.580 Luke Daque: Eden. Where’s Eden here?
109 01:02:22.338 ⇒ 01:02:24.969 Luke Daque: It doesn’t look like it’s here.
110 01:02:39.210 ⇒ 01:02:43.609 Luke Daque: I don’t see it in one password at least.
111 01:02:43.610 ⇒ 01:02:44.480 Uttam Kumaran: If I haven’t.
112 01:03:51.550 ⇒ 01:03:54.079 Luke Daque: Yeah, in in the in the cloud. It’s
113 01:03:56.910 ⇒ 01:03:59.219 Luke Daque: hashed out so we can’t see it.
114 01:04:06.310 ⇒ 01:04:07.720 Uttam Kumaran: One second. I’m almost.
115 01:04:08.280 ⇒ 01:04:08.950 Luke Daque: Sure.
116 01:05:42.950 ⇒ 01:05:44.889 Uttam Kumaran: No, there’s like a local.
117 01:07:02.740 ⇒ 01:07:10.339 Luke Daque: And I guess we can add a new key. I guess I can add a new key
118 01:07:10.860 ⇒ 01:07:14.140 Luke Daque: to a service account, so I can have the Jason file.
119 01:07:14.670 ⇒ 01:07:17.990 Uttam Kumaran: What is the like? What does the key look like?
120 01:07:18.870 ⇒ 01:07:19.660 Luke Daque: It’s
121 01:07:22.720 ⇒ 01:07:25.099 Uttam Kumaran: Oh, I have it here. Can I send it to you?
122 01:07:25.350 ⇒ 01:07:27.859 Luke Daque: Yeah, sure one password. It’s not there.
123 01:07:28.170 ⇒ 01:07:30.270 Luke Daque: Hmm! I didn’t. I don’t see it.
124 01:07:46.740 ⇒ 01:07:48.270 Luke Daque: Wait! What’s that?
125 01:07:51.980 ⇒ 01:07:54.050 Uttam Kumaran: I just added it to the Dvt.
126 01:07:54.440 ⇒ 01:07:55.370 Uttam Kumaran: Ian.
127 01:07:57.640 ⇒ 01:07:58.310 Luke Daque: Okay.
128 01:08:10.300 ⇒ 01:08:12.020 Luke Daque: Yep, I see it now. Cool.
129 01:08:22.040 ⇒ 01:08:22.770 Luke Daque: cool.
130 01:09:16.370 ⇒ 01:09:21.279 Luke Daque: No, this is here.
131 01:10:49.760 ⇒ 01:10:51.570 Uttam Kumaran: I’m almost there.
132 01:11:25.620 ⇒ 01:11:28.390 Luke Daque: Project name Eden data warehouse
133 01:11:34.150 ⇒ 01:11:35.720 Luke Daque: data sent me.
134 01:12:57.560 ⇒ 01:13:03.050 Luke Daque: Yeah, looks should be good on my end. But let me check.
135 01:13:04.570 ⇒ 01:13:06.720 Luke Daque: Dbt.
136 01:16:42.050 ⇒ 01:16:45.289 Luke Daque: looks like I was able to make mine work.
137 01:16:46.420 ⇒ 01:16:47.710 Uttam Kumaran: Okay. I’m almost there.
138 01:16:48.860 ⇒ 01:16:51.120 Luke Daque: I can show you my profile. Seattle.
139 01:18:02.560 ⇒ 01:18:03.630 Uttam Kumaran: Okay. I’m in.
140 01:18:05.020 ⇒ 01:18:05.770 Luke Daque: Okay.
141 01:18:07.960 ⇒ 01:18:10.330 Uttam Kumaran: That was really annoying. Okay.
142 01:18:17.370 ⇒ 01:18:19.940 Uttam Kumaran: but still nothing is showing up on my
143 01:18:25.030 ⇒ 01:18:26.310 Uttam Kumaran: Dvt.
144 01:18:27.800 ⇒ 01:18:28.730 Uttam Kumaran: Mention.
145 01:18:30.340 ⇒ 01:18:31.200 Luke Daque: Oh, really.
146 01:18:31.910 ⇒ 01:18:32.700 Uttam Kumaran: Yeah.
147 01:18:34.110 ⇒ 01:18:39.480 Luke Daque: Wait, let me check if I have any anything here.
148 01:18:52.640 ⇒ 01:18:54.130 Uttam Kumaran: Oh, hold on!
149 01:18:56.270 ⇒ 01:18:58.809 Uttam Kumaran: I have to change some associations.
150 01:21:12.380 ⇒ 01:21:14.413 Uttam Kumaran: Okay, it’s working. But I don’t know why the
151 01:21:16.890 ⇒ 01:21:21.360 Uttam Kumaran: I don’t know why the extension isn’t working. But whatever maybe I can
152 01:21:22.710 ⇒ 01:21:24.640 Uttam Kumaran: doesn’t really matter right now. I guess.
153 01:21:25.970 ⇒ 01:21:26.920 Luke Daque: Okay.
154 01:21:28.716 ⇒ 01:21:34.542 Uttam Kumaran: Okay, cool. So let’s now now that we haven’t cut on that, let’s talk about
155 01:21:35.390 ⇒ 01:21:38.509 Uttam Kumaran: the core data model. So what did you find today.
156 01:21:38.870 ⇒ 01:21:42.400 Uttam Kumaran: and I have. I have the warehouse open on my end.
157 01:21:43.250 ⇒ 01:21:44.510 Luke Daque: Yeah, sure. So
158 01:21:45.740 ⇒ 01:21:51.769 Luke Daque: yeah, basically, I was looking at the screenshot. Let me share my screen. So maybe we can follow through.
159 01:21:52.670 ⇒ 01:21:57.150 Luke Daque: Where was that here?
160 01:22:02.930 ⇒ 01:22:05.089 Luke Daque: Can you see my screen?
161 01:22:08.200 ⇒ 01:22:09.330 Luke Daque: So, yeah.
162 01:22:09.330 ⇒ 01:22:09.910 Uttam Kumaran: Yes.
163 01:22:09.910 ⇒ 01:22:14.070 Luke Daque: Basically we look into this, these are just.
164 01:22:14.190 ⇒ 01:22:15.873 Uttam Kumaran: Looks like these are just
165 01:22:16.470 ⇒ 01:22:17.760 Luke Daque: Product names.
166 01:22:18.476 ⇒ 01:22:29.930 Luke Daque: And then there’s like, it’s basically just units sold and sales revenue right? And like, these are all just different products. And these are all just units, sales and sales revenue as units sold
167 01:22:30.150 ⇒ 01:22:37.769 Luke Daque: and like, they basically have like a table here. That’s revenue yesterday and products sold yesterday.
168 01:22:38.490 ⇒ 01:22:39.170 Uttam Kumaran: Like that.
169 01:22:39.380 ⇒ 01:22:44.540 Luke Daque: So I did try to take a look at the potential
170 01:22:45.250 ⇒ 01:22:50.849 Luke Daque: tables that we can use. This is like, if we create it from scratch right?
171 01:22:51.519 ⇒ 01:22:57.809 Luke Daque: But and looks like we have Basque order the the Basque orders data sets.
172 01:22:58.040 ⇒ 01:23:03.599 Luke Daque: There’s a couple of them. There’s like Basque order completed. Basque order shipped and bask order updated.
173 01:23:03.930 ⇒ 01:23:09.920 Luke Daque: And I did try to run some queries over here, just using the completed orders.
174 01:23:10.150 ⇒ 01:23:16.670 Luke Daque: Is this already looks like just basing on this. It already has the product name.
175 01:23:17.670 ⇒ 01:23:18.610 Luke Daque: So I don’t.
176 01:23:18.820 ⇒ 01:23:26.930 Luke Daque: Well, just based on this. Maybe we don’t have to map it to any product mapping. But maybe we do. Because it also has product, id and
177 01:23:27.350 ⇒ 01:23:31.150 Luke Daque: variant id, which is like very similar to
178 01:23:31.300 ⇒ 01:23:35.819 Luke Daque: I mean, we can use the variant id, and I think there’s also a bundle id here somewhere.
179 01:23:36.610 ⇒ 01:23:40.480 Luke Daque: I think I found that I think.
180 01:23:41.360 ⇒ 01:23:47.559 Luke Daque: yeah. But yeah, we can basically use this. If this is our master product mapping.
181 01:23:48.181 ⇒ 01:23:55.630 Luke Daque: We can use the product name here as opposed to the product name that’s in in that table.
182 01:23:56.790 ⇒ 01:23:58.419 Luke Daque: And this also.
183 01:23:58.420 ⇒ 01:23:59.740 Uttam Kumaran: Product ids.
184 01:24:00.340 ⇒ 01:24:04.430 Luke Daque: Yeah, it does. It does have product. Id a variant id.
185 01:24:04.730 ⇒ 01:24:10.389 Luke Daque: And where’s the bundle id? I think I thought I saw that.
186 01:24:11.450 ⇒ 01:24:18.140 Luke Daque: Yeah, here bundle id, so basically, we can join it with these 2.
187 01:24:18.370 ⇒ 01:24:25.280 Luke Daque: This doesn’t have a product. Id, because we did create the combo id on our own, which is.
188 01:24:26.040 ⇒ 01:24:27.040 Luke Daque: yeah, okay.
189 01:24:27.250 ⇒ 01:24:33.470 Luke Daque: But yeah, we can basically just join these 2 to get the product name here.
190 01:24:34.140 ⇒ 01:24:43.920 Luke Daque: But for now I was just playing around with it? Just trying to query, basically, yeah, some.
191 01:24:44.410 ⇒ 01:24:51.880 Luke Daque: I tried. I tried completed orders, shipped orders, but it looks like I don’t think we can.
192 01:24:52.510 ⇒ 01:24:54.660 Luke Daque: You shipped orders because there’s like
193 01:24:54.830 ⇒ 01:24:58.780 Luke Daque: 3 shipped orders for a single order for some reason.
194 01:24:59.290 ⇒ 01:25:04.310 Luke Daque: single order Id. And then they they just have different shipping dates so.
195 01:25:05.700 ⇒ 01:25:10.840 Luke Daque: and I don’t know which one do we choose here? Because it doesn’t have any like status or something?
196 01:25:14.050 ⇒ 01:25:22.029 Luke Daque: But yeah, if if I just directly query completed orders. We already can get the, you know.
197 01:25:22.030 ⇒ 01:25:23.720 Uttam Kumaran: Maybe with completed orders.
198 01:25:24.180 ⇒ 01:25:24.970 Luke Daque: Yeah.
199 01:25:25.480 ⇒ 01:25:33.460 Luke Daque: we can. Just we can basically get the order total minus discount, or maybe minus the cogs as well to get the gross
200 01:25:34.345 ⇒ 01:25:38.160 Luke Daque: sales or like total sales amount, maybe
201 01:25:38.922 ⇒ 01:25:43.159 Luke Daque: depending. So we we need to know, like what the logic is for revenue.
202 01:25:43.430 ⇒ 01:25:48.750 Luke Daque: So I think it’s just order total minus cogs or minus discount. Maybe.
203 01:25:49.020 ⇒ 01:25:56.599 Luke Daque: So. I just tried this. We should be able to see that, and we can break it down by status as well and product name.
204 01:25:57.876 ⇒ 01:25:59.030 Luke Daque: I did.
205 01:25:59.370 ⇒ 01:26:05.219 Luke Daque: There’s a yeah, like we in-in in bigquery. You can actually
206 01:26:06.180 ⇒ 01:26:13.880 Luke Daque: open this in like looker studio directly or in sheets. So basically the table output. I tried doing okay.
207 01:26:14.420 ⇒ 01:26:18.010 Luke Daque: studio. So I was able to do something like this
208 01:26:18.260 ⇒ 01:26:24.689 Luke Daque: where it’s already a breakdown in product name. And yeah, there’s like status statuses here.
209 01:26:24.860 ⇒ 01:26:30.700 Luke Daque: It’s also dynamic, like we can click, abandon, for example, and we’ll be able to see, like all the products there.
210 01:26:32.404 ⇒ 01:26:39.549 Luke Daque: But yeah, it depends on like what the final visualization tool will be. But this is just for me to like
211 01:26:39.710 ⇒ 01:26:45.339 Luke Daque: quickly validate the data. Basically like, we have product names here. So.
212 01:26:46.300 ⇒ 01:26:48.349 Uttam Kumaran: Where’s where’s revenue?
213 01:26:48.930 ⇒ 01:26:50.744 Luke Daque: Revenue. I did.
214 01:26:51.680 ⇒ 01:26:54.330 Luke Daque: I made it just order total minus
215 01:26:54.780 ⇒ 01:26:58.289 Luke Daque: the discount. But yeah, we can change that logic depending.
216 01:26:58.577 ⇒ 01:27:04.559 Uttam Kumaran: Where is that like? What table is that in it’s in order.
217 01:27:05.560 ⇒ 01:27:13.782 Luke Daque: Yeah. Order complete bask orders completed has order total and
218 01:27:14.640 ⇒ 01:27:20.909 Luke Daque: discount and cogs as well already in there. So maybe we can use that.
219 01:27:22.460 ⇒ 01:27:28.770 Luke Daque: and then quantities there as well. So make. So we already have like total quantity.
220 01:27:29.590 ⇒ 01:27:31.115 Luke Daque: If you need that like.
221 01:27:31.690 ⇒ 01:27:37.670 Luke Daque: Like. In this, for example, we have, like quantity, order, total discount revenue.
222 01:27:38.260 ⇒ 01:27:40.589 Luke Daque: something like that. And then we can, you know.
223 01:27:41.960 ⇒ 01:27:48.140 Luke Daque: if we do yesterday, for example, or like this year.
224 01:27:49.880 ⇒ 01:27:53.049 Luke Daque: yeah, we should be able to see it based on this.
225 01:27:54.840 ⇒ 01:27:56.100 Luke Daque: Yeah, maybe this can be there.
226 01:27:56.453 ⇒ 01:27:58.930 Luke Daque: Where is that sheet? By the way.
227 01:27:59.160 ⇒ 01:28:02.949 Uttam Kumaran: What’s what’s the name of that sheet ma-master product?
228 01:28:02.950 ⇒ 01:28:10.179 Luke Daque: Yeah. Eden, master product data mapping. I actually, yeah, I loaded that here in raw in raw Google sheet
229 01:28:12.095 ⇒ 01:28:17.560 Luke Daque: but this is already coming from Dbt, where I already added the
230 01:28:18.480 ⇒ 01:28:23.880 Luke Daque: Oh, no, this is wait. This is the role one
231 01:28:28.720 ⇒ 01:28:33.420 Luke Daque: I did create the combody
232 01:28:37.699 ⇒ 01:28:44.429 Luke Daque: the pro. This one is directly coming from Google Sheet. If you can. You can see here in details. The source is the Google Sheet
233 01:28:44.890 ⇒ 01:28:46.380 Luke Daque: link already.
234 01:28:47.302 ⇒ 01:28:52.850 Luke Daque: So I I just use the native connector for bigquery Google Sheet to Bigquery to load this in.
235 01:28:53.450 ⇒ 01:28:56.409 Luke Daque: And then in under Dbt.
236 01:28:57.260 ⇒ 01:29:06.940 Luke Daque: Martz, I created a product mapping model which already has the combo, id.
237 01:29:06.940 ⇒ 01:29:08.370 Uttam Kumaran: How did you make the combo.
238 01:29:09.000 ⇒ 01:29:12.920 Luke Daque: I. It’s just a concatenation of
239 01:29:13.150 ⇒ 01:29:20.300 Luke Daque: all the ids that, Robert said. Here, like bundle the variant, the membership this one.
240 01:29:20.760 ⇒ 01:29:28.020 Luke Daque: and made it into a sh SH, ash, yeah.
241 01:29:28.140 ⇒ 01:29:31.150 Luke Daque: cool. So yeah, it’s it’s basically like this.
242 01:29:31.550 ⇒ 01:29:34.660 Luke Daque: Md, 5, yeah, tape.
243 01:29:35.980 ⇒ 01:29:36.459 Luke Daque: Yep.
244 01:29:39.320 ⇒ 01:29:39.890 Uttam Kumaran: Great.
245 01:29:42.250 ⇒ 01:29:49.230 Luke Daque: But yeah, this, yeah, it, it would look something like this. Basically, they should make it unique.
246 01:29:52.020 ⇒ 01:29:53.309 Luke Daque: So yeah, we just need.
247 01:29:54.720 ⇒ 01:29:58.639 Luke Daque: I guess we need to. We need the. We need them to fill this out.
248 01:29:59.300 ⇒ 01:30:00.667 Luke Daque: To make this like.
249 01:30:03.280 ⇒ 01:30:04.270 Uttam Kumaran: So let me just look.
250 01:30:04.270 ⇒ 01:30:05.160 Luke Daque: Sure it’ll be.
251 01:30:05.240 ⇒ 01:30:05.799 Luke Daque: Let me just.
252 01:30:05.800 ⇒ 01:30:10.859 Uttam Kumaran: Let’s walk one by one. Hold on one second. So pro. So I’m looking at the columns for the final table.
253 01:30:11.260 ⇒ 01:30:14.920 Uttam Kumaran: We have product. This is coming from bask orders
254 01:30:16.820 ⇒ 01:30:21.989 Uttam Kumaran: revenue also from Bath. So all this can basically. So what about where does customers come from?
255 01:30:24.910 ⇒ 01:30:26.979 Luke Daque: Which final table are you looking at.
256 01:30:28.708 ⇒ 01:30:32.889 Uttam Kumaran: So I’m just kind of starting Doc, doing documentation here.
257 01:30:32.920 ⇒ 01:30:41.929 Luke Daque: Oh, so the I would. I would think the data source is the bask orders completed.
258 01:30:42.080 ⇒ 01:30:43.429 Uttam Kumaran: For everything which is.
259 01:30:44.500 ⇒ 01:30:46.891 Luke Daque: Yeah, for just for this specific
260 01:30:48.220 ⇒ 01:30:50.349 Uttam Kumaran: Where is like? The customer details.
261 01:30:51.580 ⇒ 01:30:54.729 Luke Daque: It’s ha mask, order should have like
262 01:30:56.130 ⇒ 01:31:00.109 Luke Daque: customer identity there, or something it’s called.
263 01:31:00.750 ⇒ 01:31:03.270 Luke Daque: yeah, it has, like, phone number,
264 01:31:08.580 ⇒ 01:31:10.489 Luke Daque: full name. And
265 01:31:14.760 ⇒ 01:31:17.108 Luke Daque: I think there’s email as well.
266 01:31:18.040 ⇒ 01:31:22.010 Luke Daque: yeah. Patient patient email, patient. Last name.
267 01:31:26.990 ⇒ 01:31:27.840 Luke Daque: yeah.
268 01:31:29.740 ⇒ 01:31:33.359 Uttam Kumaran: Let’s just solve this one second. Just give me a little sec.
269 01:31:34.370 ⇒ 01:31:35.010 Luke Daque: Sure.
270 01:31:41.410 ⇒ 01:31:43.890 Luke Daque: we got patient 1st name, patient.
271 01:31:47.100 ⇒ 01:31:48.550 Luke Daque: last name.
272 01:31:51.920 ⇒ 01:31:54.190 Luke Daque: patient email phone number.
273 01:32:26.660 ⇒ 01:32:33.310 Luke Daque: We can, we can update the notion document with the stuff.
274 01:32:34.150 ⇒ 01:32:35.390 Uttam Kumaran: Yeah, just one second.
275 01:32:36.750 ⇒ 01:32:39.380 Uttam Kumaran: So take a look at, take a look at like this.
276 01:32:40.440 ⇒ 01:32:48.829 Uttam Kumaran: So this is their orders. Order, detail table, and they have a customer’s query that’s pulling from this.
277 01:33:11.410 ⇒ 01:33:12.230 Luke Daque: Oh!
278 01:33:19.240 ⇒ 01:33:22.360 Uttam Kumaran: Right. So this is one of the scheduled queries that they have.
279 01:33:22.980 ⇒ 01:33:25.039 Luke Daque: Yeah. The-, the order detail table.
280 01:33:25.040 ⇒ 01:33:28.060 Uttam Kumaran: This is what I don’t want to. Just guess where stuff is.
281 01:33:28.520 ⇒ 01:33:33.119 Uttam Kumaran: Please let’s just take our time and find out where all the information is
282 01:33:33.390 ⇒ 01:33:38.550 Uttam Kumaran: right. So it’s not enough just to I just, I don’t want to just solve for the one problem we have
283 01:33:38.670 ⇒ 01:33:41.199 Uttam Kumaran: wanna find the source of truth for customers.
284 01:33:41.650 ⇒ 01:33:45.600 Luke Daque: Okay, yeah, I guess.
285 01:33:45.600 ⇒ 01:33:48.079 Uttam Kumaran: Time right now, and let’s find out where
286 01:33:48.210 ⇒ 01:33:51.129 Uttam Kumaran: the core customer details are coming from
287 01:33:51.260 ⇒ 01:33:53.829 Uttam Kumaran: and where the core order details are coming from.
288 01:33:55.810 ⇒ 01:33:56.720 Luke Daque: I guess in that
289 01:33:56.720 ⇒ 01:34:01.600 Luke Daque: it looks like this query is taking data from all sorts. It’s like a bunch of different places.
290 01:34:02.350 ⇒ 01:34:07.029 Luke Daque: Yeah, we already have that in Dbt. So we should be able to see the lineage like.
291 01:34:07.030 ⇒ 01:34:08.019 Uttam Kumaran: Yeah. So let’s take a look.
292 01:34:08.020 ⇒ 01:34:09.579 Luke Daque: The sources are.
293 01:34:15.620 ⇒ 01:34:20.970 Luke Daque: So this is what the lineage looks like or order detail. There’s on.
294 01:34:21.230 ⇒ 01:34:23.430 Uttam Kumaran: Yeah, for order details.
295 01:34:23.840 ⇒ 01:34:25.810 Luke Daque: It’s a lot here. Yeah.
296 01:34:25.810 ⇒ 01:34:30.800 Uttam Kumaran: Order shifts, shipbo.
297 01:34:31.770 ⇒ 01:34:32.660 Luke Daque: Yeah.
298 01:34:33.320 ⇒ 01:34:40.960 Luke Daque: Bask stuff identifies for something, and then bask order ship.
299 01:34:41.710 ⇒ 01:34:43.560 Luke Daque: Last order completed.
300 01:34:43.560 ⇒ 01:34:51.479 Uttam Kumaran: I have one question like in bigquery. If you look at the definition for that order detail, it looks like they’re pulling from like
301 01:34:52.360 ⇒ 01:34:53.210 Uttam Kumaran: some.
302 01:34:55.910 ⇒ 01:34:56.820 Luke Daque: This does.
303 01:34:57.800 ⇒ 01:34:59.565 Luke Daque: But if you’re referring to hilarious.
304 01:35:00.270 ⇒ 01:35:04.350 Uttam Kumaran: Yeah, go to the order detail and go to details. Yeah? And go to the query.
305 01:35:04.350 ⇒ 01:35:09.139 Luke Daque: Yeah, I I don’t know if this is the same as the scheduled query.
306 01:35:09.750 ⇒ 01:35:10.830 Uttam Kumaran: Oh, okay.
307 01:35:10.830 ⇒ 01:35:14.469 Luke Daque: Because the scheduled query over here.
308 01:35:15.290 ⇒ 01:35:19.860 Luke Daque: Let let’s see, let’s check order. D.
309 01:35:19.860 ⇒ 01:35:25.369 Uttam Kumaran: And then where is this customer? Schedule like? Is customer? Is there schedule? Query for customers or no.
310 01:35:29.890 ⇒ 01:35:32.070 Luke Daque: Doesn’t look like it.
311 01:35:33.170 ⇒ 01:35:33.740 Uttam Kumaran: Okay.
312 01:35:34.306 ⇒ 01:35:35.769 Luke Daque: This is the order.
313 01:35:35.770 ⇒ 01:35:36.910 Uttam Kumaran: User, summary.
314 01:35:36.910 ⇒ 01:35:37.950 Luke Daque: Detail.
315 01:35:38.560 ⇒ 01:35:43.680 Luke Daque: Let’s see if it’s the same, because this is already materializing it as a table
316 01:35:43.840 ⇒ 01:35:50.060 Luke Daque: under materialized view. So this is different from what you showed here, which is just as a view.
317 01:35:50.810 ⇒ 01:35:51.220 Uttam Kumaran: Okay.
318 01:35:51.220 ⇒ 01:35:57.349 Luke Daque: So it’s it’s this table over here, materialized materialized views, and then order details.
319 01:35:57.350 ⇒ 01:36:03.149 Uttam Kumaran: Take a look at the customer data set, and within there, take a look at the active customer.
320 01:36:04.120 ⇒ 01:36:07.439 Uttam Kumaran: or, like active customer detail.
321 01:36:07.600 ⇒ 01:36:08.430 Luke Daque: Hmm.
322 01:36:09.730 ⇒ 01:36:13.059 Uttam Kumaran: They have user Id full name email.
323 01:36:13.440 ⇒ 01:36:15.639 Uttam Kumaran: All of that we can get from Basque.
324 01:36:18.400 ⇒ 01:36:20.200 Luke Daque: Let’s let’s see.
325 01:36:21.160 ⇒ 01:36:21.800 Uttam Kumaran: Right.
326 01:36:24.140 ⇒ 01:36:32.630 Luke Daque: So and using calendar, we’ll just pivot dates. And then it’s coming from order detail.
327 01:36:34.460 ⇒ 01:36:35.727 Luke Daque: Yeah, this is
328 01:36:37.620 ⇒ 01:36:45.550 Luke Daque: quite messy, because this is coming from the the view, the order, detail, view, not really the order detail that’s materialized from the
329 01:36:46.000 ⇒ 01:36:47.790 Luke Daque: scheduled query.
330 01:36:48.190 ⇒ 01:36:48.580 Uttam Kumaran: Okay.
331 01:36:48.580 ⇒ 01:36:52.070 Luke Daque: And it looks like they’re getting it just from here.
332 01:36:52.610 ⇒ 01:36:53.260 Uttam Kumaran: Okay.
333 01:36:53.260 ⇒ 01:36:54.050 Luke Daque: So 4 Am.
334 01:36:54.050 ⇒ 01:37:00.474 Uttam Kumaran: Here’s so here’s like, I think, probably what the move is one we need to start. We need to create like,
335 01:37:02.270 ⇒ 01:37:07.459 Uttam Kumaran: So you created the source tables. I guess we should create the raw
336 01:37:10.690 ⇒ 01:37:16.320 Uttam Kumaran: the raw tables for, or we should create the staging tables for orders right?
337 01:37:19.830 ⇒ 01:37:26.020 Luke Daque: Yeah, we could do that. So basically, the the Basque orders, which is
338 01:37:26.600 ⇒ 01:37:31.870 Luke Daque: being used in this order, details table like password to complete it.
339 01:37:32.565 ⇒ 01:37:39.420 Uttam Kumaran: So yeah, basically, the Basque, let’s just start with order completed.
340 01:37:39.740 ⇒ 01:37:42.790 Uttam Kumaran: Let’s create an orders table on our side.
341 01:37:46.470 ⇒ 01:37:53.050 Luke Daque: Okay, and let’s just let’s just pull directly from bask order completed.
342 01:37:56.460 ⇒ 01:37:59.670 Uttam Kumaran: And then we can just create that as the 1st intermediate.
343 01:38:02.030 ⇒ 01:38:04.100 Uttam Kumaran: or I guess, as a first, st
344 01:38:04.530 ⇒ 01:38:07.590 Uttam Kumaran: like, how do we do this before we had like.
345 01:38:07.940 ⇒ 01:38:12.390 Uttam Kumaran: Did we do select stars for cleanup? Or we just went straight there.
346 01:38:12.620 ⇒ 01:38:14.940 Luke Daque: We just went directly to the source.
347 01:38:15.390 ⇒ 01:38:15.990 Uttam Kumaran: Okay.
348 01:38:15.990 ⇒ 01:38:19.549 Luke Daque: Currently but yeah, we can do something like this
349 01:38:20.100 ⇒ 01:38:27.219 Luke Daque: where it’s get getting select star from the source and just do whatever? Yeah.
350 01:38:28.280 ⇒ 01:38:29.440 Uttam Kumaran: So let’s do that.
351 01:38:30.450 ⇒ 01:38:32.752 Luke Daque: So this is gonna be in raw. Right?
352 01:38:34.180 ⇒ 01:38:37.580 Uttam Kumaran: So raw bask order completed, I guess.
353 01:38:37.580 ⇒ 01:38:42.580 Uttam Kumaran: Well, no, not necessarily.
354 01:38:43.270 ⇒ 01:38:43.990 Luke Daque: Just the stage.
355 01:38:43.990 ⇒ 01:38:49.160 Uttam Kumaran: We may not have raw at all, I guess.
356 01:38:51.560 ⇒ 01:38:53.215 Uttam Kumaran: Let’s think about it.
357 01:38:54.730 ⇒ 01:39:01.480 Uttam Kumaran: Because previously we had raw for everything that it gets ingested in this situation. We don’t have like that much control over that.
358 01:39:01.920 ⇒ 01:39:06.399 Uttam Kumaran: So we’re gonna have 2 data. We’re gonna have 2 data sets, one for Dbt. Mart.
359 01:39:06.620 ⇒ 01:39:08.329 Uttam Kumaran: One for Dvds.
360 01:39:08.900 ⇒ 01:39:09.600 Uttam Kumaran: What we call you.
361 01:39:09.600 ⇒ 01:39:09.960 Luke Daque: Media.
362 01:39:09.960 ⇒ 01:39:13.639 Uttam Kumaran: Or what do we call it? What do we call it? In the in? The? In the notion, Doc.
363 01:39:13.770 ⇒ 01:39:15.320 Luke Daque: Yeah, it was intermediate.
364 01:39:15.630 ⇒ 01:39:18.130 Luke Daque: We have. We had raw intermediate invites.
365 01:39:21.730 ⇒ 01:39:24.010 Uttam Kumaran: I like the I like intermediate.
366 01:39:24.410 ⇒ 01:39:29.410 Luke Daque: Okay, so I guess we can older here.
367 01:39:29.610 ⇒ 01:39:31.898 Uttam Kumaran: Well, actually, what we had is
368 01:39:39.750 ⇒ 01:39:40.989 Luke Daque: Let’s let me create it.
369 01:39:40.990 ⇒ 01:39:45.120 Uttam Kumaran: Oh, we I mean we did. We did propose that we have raw.
370 01:39:45.790 ⇒ 01:39:46.470 Luke Daque: Yeah.
371 01:39:51.230 ⇒ 01:39:53.520 Uttam Kumaran: Oh, okay, let’s do that. Let’s do that.
372 01:39:55.030 ⇒ 01:39:58.940 Luke Daque: So in enroll, basically right?
373 01:39:59.730 ⇒ 01:40:03.210 Luke Daque: And we name this raw Basque order complete.
374 01:40:03.210 ⇒ 01:40:05.200 Uttam Kumaran: Yeah, let’s just do, Rob.
375 01:40:05.350 ⇒ 01:40:08.679 Uttam Kumaran: Yeah, Rob, bask order is completed.
376 01:40:15.390 ⇒ 01:40:19.649 Uttam Kumaran: Well, we don’t need to. You don’t need to name the table raw, right.
377 01:40:19.830 ⇒ 01:40:25.320 Uttam Kumaran: It’s already gonna be in the Dbt raw schema.
378 01:40:27.960 ⇒ 01:40:32.010 Uttam Kumaran: correct? Or, Oh, I see what? Okay? Yeah. You do have to name it raw.
379 01:40:32.530 ⇒ 01:40:34.939 Luke Daque: Yeah, cause I think if there’s another.
380 01:40:35.470 ⇒ 01:40:36.230 Uttam Kumaran: Yeah, yeah.
381 01:40:36.230 ⇒ 01:40:41.649 Luke Daque: Well, yeah, yeah, let’s let’s do that.
382 01:40:44.250 ⇒ 01:40:47.840 Luke Daque: And I guess maybe all the raw
383 01:40:48.400 ⇒ 01:40:52.329 Luke Daque: models would be views, because I don’t think we need them as tables?
384 01:40:53.480 ⇒ 01:40:55.320 Luke Daque: Or what do you think.
385 01:40:57.430 ⇒ 01:40:59.209 Uttam Kumaran: Yeah, let me think so.
386 01:41:20.760 ⇒ 01:41:22.820 Uttam Kumaran: Actually, we should do it like
387 01:41:23.800 ⇒ 01:41:29.570 Uttam Kumaran: the way we had in our documentation was, we’re gonna do bask, underscore underscore table name.
388 01:41:30.030 ⇒ 01:41:32.889 Uttam Kumaran: so source underscore underscore table name.
389 01:41:34.920 ⇒ 01:41:36.919 Luke Daque: Instead of raw. It’s source.
390 01:41:37.340 ⇒ 01:41:40.609 Uttam Kumaran: Well, it’s gonna be like bask underscore underscore.
391 01:41:41.910 ⇒ 01:41:45.180 Luke Daque: Deleted something like that.
392 01:41:46.990 ⇒ 01:41:49.890 Uttam Kumaran: Yeah, exactly. I think this is the best.
393 01:41:50.450 ⇒ 01:41:52.720 Luke Daque: Yeah, okay, so we have.
394 01:41:53.330 ⇒ 01:41:54.999 Luke Daque: So we know, like, which
395 01:41:56.140 ⇒ 01:42:02.720 Luke Daque: source it comes from. Because, yeah, cause, there could be another source that’s also named orders completed.
396 01:42:03.060 ⇒ 01:42:10.450 Luke Daque: So it’s that source underscore is for orders complete as well. So we don’t get confused.
397 01:42:16.740 ⇒ 01:42:21.680 Luke Daque: So I guess we need to change this as well the instead of product data mapping, it should be
398 01:42:22.960 ⇒ 01:42:28.009 Luke Daque: Google sheet underscore underscore product data mapping.
399 01:42:40.000 ⇒ 01:42:42.627 Uttam Kumaran: Okay. So should I work on? Should I start working on my customers?
400 01:42:46.010 ⇒ 01:42:48.539 Uttam Kumaran: I’m gonna work on a separate customer staple.
401 01:42:49.060 ⇒ 01:42:52.880 Luke Daque: Sure, and I can work on the order details table. Then.
402 01:42:53.110 ⇒ 01:42:59.450 Uttam Kumaran: Yeah, okay, let me just go get a copy. Let me call back.
403 01:42:59.730 ⇒ 01:43:00.290 Luke Daque: Cool.
404 01:43:00.940 ⇒ 01:43:04.360 Uttam Kumaran: So we’re gonna do. So if you do orders.
405 01:43:05.560 ⇒ 01:43:12.310 Uttam Kumaran: So you just run, just run towards our generalized orders table. I’m gonna run towards the generalized customers table.
406 01:43:12.900 ⇒ 01:43:14.289 Luke Daque: Wait. So this is
407 01:43:17.120 ⇒ 01:43:24.619 Luke Daque: not necessarily. The orders, detail order, details, table, which they already have. So we’re creating our own orders. Table.
408 01:43:25.070 ⇒ 01:43:34.179 Uttam Kumaran: Yes, because for cause can I give you? Can I give you example? So they have a couple of metrics here. There are new customers pending new customers, total new customers.
409 01:43:34.330 ⇒ 01:43:37.260 Uttam Kumaran: all of those I want to source from the customers table
410 01:43:37.570 ⇒ 01:43:40.449 Uttam Kumaran: any order or order related revenue
411 01:43:40.590 ⇒ 01:43:42.379 Uttam Kumaran: I want to source from your table.
412 01:43:42.560 ⇒ 01:43:44.860 Uttam Kumaran: So order details.
413 01:43:45.100 ⇒ 01:43:54.499 Uttam Kumaran: Think about this is gonna like we’re gonna start the schedule queries don’t even worry about them. Use them to like, understand a logic. But we’re building our own warehouse now.
414 01:43:54.620 ⇒ 01:43:58.879 Uttam Kumaran: So we’re gonna build core entities. So 1st is gonna be orders.
415 01:43:59.080 ⇒ 01:44:00.809 Uttam Kumaran: We’re gonna have customers.
416 01:44:00.990 ⇒ 01:44:05.040 Uttam Kumaran: And then we’re gonna kind of try to understand like, do we have shipments?
417 01:44:05.180 ⇒ 01:44:16.639 Uttam Kumaran: And then like, kind of build that out. 1st thing to start with, we really just need orders. We need the financials associated with the product associated with it. And then I’m gonna make I’m gonna build customers. And then we should be good.
418 01:44:17.160 ⇒ 01:44:20.759 Luke Daque: Okay, makes sense cool.
419 01:44:28.580 ⇒ 01:44:31.200 Luke Daque: Yeah, let me. I’ll just go to the restroom real quick.
420 01:53:38.560 ⇒ 01:53:40.040 Uttam Kumaran: How are your kids doing dude.
421 01:53:41.550 ⇒ 01:53:42.980 Luke Daque: Yeah, they’re doing well.
422 01:53:46.300 ⇒ 01:53:49.450 Luke Daque: Like, it’s like they’re back to school and stuff
423 01:53:50.250 ⇒ 01:53:54.890 Luke Daque: after like 2, I don’t know. Like, 3 weeks of the holidays. Yeah.
424 01:53:56.200 ⇒ 01:54:02.579 Luke Daque: they were all like, they don’t wanna go back to school and stuff. But yeah, now they’re back.
425 01:54:03.050 ⇒ 01:54:03.880 Uttam Kumaran: Nice.
426 01:54:12.020 ⇒ 01:54:13.680 Luke Daque: Do you get your coffee.
427 01:54:14.290 ⇒ 01:54:16.800 Uttam Kumaran: Yes, espresso.
428 01:54:17.630 ⇒ 01:54:21.019 Luke Daque: Noise you have like a coffee machine or something.
429 01:54:21.790 ⇒ 01:54:23.450 Uttam Kumaran: Yeah, I have espresso, machine.
430 01:54:23.450 ⇒ 01:54:24.960 Luke Daque: Oh, wow! That’s cool!
431 01:54:25.680 ⇒ 01:54:29.570 Uttam Kumaran: Yeah, I don’t have any milk right now. I’m actually, I’ve been meal prepping
432 01:54:30.030 ⇒ 01:54:35.580 Uttam Kumaran: and like trying to get my diet. So I actually been eating really healthy and cooking. I made a
433 01:54:35.820 ⇒ 01:54:45.430 Uttam Kumaran: I make Korean beef with like soy sauce, rice, vinegar, garlic, Goji song.
434 01:54:46.476 ⇒ 01:54:50.240 Uttam Kumaran: And I made a bunch of beef on the grill.
435 01:54:50.340 ⇒ 01:54:51.550 Uttam Kumaran: Kimchi.
436 01:54:51.820 ⇒ 01:54:54.370 Luke Daque: Wow, that’s nice. Yeah, that’s that’s fun.
437 01:54:54.830 ⇒ 01:54:55.480 Uttam Kumaran: Yeah.
438 01:54:57.150 ⇒ 01:55:02.550 Uttam Kumaran: I’m just trying to like, cause I just trying to. I just wanna be able to go microwave food like for lunch.
439 01:55:02.800 ⇒ 01:55:06.380 Luke Daque: I’m so busy. And that’s so. It’s nice by meal. Prep, you know.
440 01:55:07.110 ⇒ 01:55:07.910 Luke Daque: Yeah.
441 01:55:31.870 ⇒ 01:55:37.275 Luke Daque: Oh, looks like there’s a new update from Robert, with regards to cogs.
442 01:55:38.180 ⇒ 01:55:39.870 Uttam Kumaran: Yeah, should we watch it? I’m gonna share.
443 01:55:42.060 ⇒ 01:55:42.840 Luke Daque: Sure.
444 01:56:00.910 ⇒ 01:56:13.500 Luke Daque: Okay, recording this video to talk through some of the updates to product level mapping and and cogs. So as we’re building out the data model. Eventually, we’re, gonna you know, have this massive product data mapping kind of sit here right now. V, 2, bundle.
445 01:56:13.500 ⇒ 01:56:16.169 Uttam Kumaran: Can you hear it? Is it too slow, too? Is it quiet?
446 01:56:17.058 ⇒ 01:56:43.009 Luke Daque: It’s okay. Yeah. It’s fine mapping and 4 mapping are models that get pulled directly into here, as we can tell from V, 2 Pb and Po. These are both models that get pulled in from this Google sheet that’s maintained by the edit team. What we’re doing differently here as a recap is we’re adding a concatenated like combo universal id whatever we want to call that. And then we’re also including a few other identifiers that we’re getting from future bask orders on new products
447 01:56:43.310 ⇒ 01:56:53.850 Luke Daque: along the way. We have all of this product level enrichments. And so all of the the columns that are highlighted in yellow are the columns that we need to bring in. And so the part I’ve been focusing on is the fee structure.
448 01:56:55.210 ⇒ 01:57:06.479 Luke Daque: I’ve broken out total cogs into these different fees. So what we can see is, if we look into a Basque order and let’s go to one of their more bigger pharmacies.
449 01:57:06.770 ⇒ 01:57:08.630 Luke Daque: Alan. How?
450 01:57:08.940 ⇒ 01:57:09.770 Luke Daque: Okay?
451 01:57:13.090 ⇒ 01:57:30.810 Luke Daque: I like to spell it wrong. Yeah. So we have a visit fee. We have a pharmacy fee, dispense fee shipping fee, and then we have cogs which is uploaded at a product level that the pharmacy team negotiates, so this will be manually maintained. But the rest we can probably figure out
452 01:57:31.350 ⇒ 01:57:45.569 Luke Daque: from the order itself. Maybe if it’s not clear, then we need to have the pharmacy team also update this as well. Then there’s this bask fee which comes out to something like 1% less than 1% of an order. I’m not really sure but I included in here because I’ve seen it pop up on some some fees.
453 01:57:45.700 ⇒ 01:57:58.400 Luke Daque: So the idea is that all of these added together is what total cost should be. And when we’re doing profitability analysis, it would be the sales minus discount, minus cox, right? And that would give us the overall margin.
454 01:57:59.120 ⇒ 01:58:02.770 Luke Daque: The other thing to keep in mind here is
455 01:58:03.500 ⇒ 01:58:19.650 Luke Daque: in the model itself. You’ll notice that there’s realized revenue transaction revenue transaction costs. And so what’s happening here with these Ctes is, let’s take one example of this. Let’s just say, row 15. This
456 01:58:19.920 ⇒ 01:58:20.740 Luke Daque: product?
457 01:58:21.259 ⇒ 01:58:42.229 Luke Daque: It’s a quarterly payment schedule product that has 3 monthly shipments, and therefore 3 payments. And so what happens is the when we, the the 1st transaction that comes through that is going to have the payment in full. That’s gonna have the whatever the price is, the full payment, and then it’s also gonna have the full transaction box
458 01:58:42.770 ⇒ 01:58:47.719 Luke Daque: in there and then over the next 2 months it will
459 01:58:48.410 ⇒ 01:58:55.410 Luke Daque: 2 more orders that are tied to the same transaction that end up being like
460 01:58:56.160 ⇒ 01:58:59.980 Luke Daque: smaller than the original transaction. So let’s like make it more concrete.
461 01:59:00.030 ⇒ 01:59:03.849 Uttam Kumaran: So it would end up being something like month, one
462 01:59:04.540 ⇒ 01:59:10.639 Uttam Kumaran: month, 2 month, 3 month, 2 month, 3. So let’s say, oops.
463 01:59:11.060 ⇒ 01:59:14.190 Uttam Kumaran: Okay, sorry. The same other one. What does that mean?
464 01:59:14.890 ⇒ 01:59:16.969 Luke Daque: Yeah, I’m a bit confused as well.
465 01:59:17.680 ⇒ 01:59:24.009 Luke Daque: It’s a quarterly payment. You’ll notice that there’s realized revenue.
466 01:59:25.770 ⇒ 01:59:28.359 Luke Daque: So he’s breaking down the quarterly
467 01:59:28.740 ⇒ 01:59:34.939 Luke Daque: payment schedules to 3, so there’d be like 3 lines, I would think, or like.
468 01:59:34.940 ⇒ 01:59:37.009 Uttam Kumaran: What does this? V. 2. P. Be?
469 01:59:38.120 ⇒ 01:59:44.480 Luke Daque: That’s probably one of their sources which is coming from a different Google sheet, I guess.
470 01:59:49.660 ⇒ 01:59:51.789 Luke Daque: Yeah, let me check real quick
471 01:59:54.990 ⇒ 01:59:58.990 Luke Daque: order details. V. 2. Pg, Pv.
472 01:59:59.810 ⇒ 02:00:06.150 Luke Daque: V. 2. Pv is v. 2 bundle mapping. That’s another model.
473 02:00:06.150 ⇒ 02:00:07.799 Uttam Kumaran: Where? Where is that? In
474 02:00:10.460 ⇒ 02:00:12.740 Uttam Kumaran: Where is that? In in bigquery?
475 02:00:14.121 ⇒ 02:00:19.060 Uttam Kumaran: It’s also in the materialized views currently or coming from their schedule.
476 02:00:19.060 ⇒ 02:00:20.799 Uttam Kumaran: The 2 product bundles.
477 02:00:20.800 ⇒ 02:00:23.430 Luke Daque: Yeah. VV, 2 bundle mapping.
478 02:00:25.020 ⇒ 02:00:26.850 Uttam Kumaran: I see? Yeah, yeah, yeah.
479 02:00:31.320 ⇒ 02:00:34.749 Uttam Kumaran: So here there is a payment schedule
480 02:00:36.120 ⇒ 02:00:50.729 Uttam Kumaran: transaction revenue transaction costs. And so what’s happening here with these Ctes is. Let’s take one example of this. I have to blow it up this way. Let’s just say row 15. This.
481 02:00:52.420 ⇒ 02:01:02.419 Uttam Kumaran: It’s a quarterly payment schedule product that has 3 monthly shipments, and therefore 3 payments. And so what happened?
482 02:01:04.680 ⇒ 02:01:06.490 Uttam Kumaran: Wait, what does that mean?
483 02:01:08.578 ⇒ 02:01:12.289 Uttam Kumaran: It’s a quarterly payment scheduled product.
484 02:01:13.410 ⇒ 02:01:17.550 Luke Daque: It’s it’s paid quarterly, but it’s it’s shipped monthly.
485 02:01:18.710 ⇒ 02:01:20.779 Uttam Kumaran: But then why are there 3 payments?
486 02:01:23.740 ⇒ 02:01:25.710 Luke Daque: Yeah, it should be.
487 02:01:27.480 ⇒ 02:01:29.949 Luke Daque: Yeah, I don’t know. Let’s let’s listen
488 02:01:38.820 ⇒ 02:01:42.517 Luke Daque: unless it’s being paid monthly or like.
489 02:01:45.580 ⇒ 02:01:46.470 Luke Daque: I don’t know.
490 02:02:05.370 ⇒ 02:02:16.679 Luke Daque: That has 3 monthly shipments, and therefore 3 payments. And so what happens is the when we, the the 1st transaction that comes through.
491 02:02:17.430 ⇒ 02:02:20.789 Uttam Kumaran: So let’s look at this the logic. Here.
492 02:02:21.330 ⇒ 02:02:23.730 Uttam Kumaran: let me pull. I just wanna pull it up so we can see it.
493 02:02:23.730 ⇒ 02:02:24.930 Luke Daque: Yeah, bye.
494 02:02:33.330 ⇒ 02:02:34.780 Luke Daque: analytics.
495 02:02:35.530 ⇒ 02:02:38.049 Uttam Kumaran: Yeah, it’s in what’s in their org.
496 02:02:52.880 ⇒ 02:02:56.210 Luke Daque: It’s that one dbt order detail.
497 02:02:56.340 ⇒ 02:03:01.880 Luke Daque: But it’s order details. Yep.
498 02:03:06.810 ⇒ 02:03:08.170 Luke Daque: statuses.
499 02:03:12.540 ⇒ 02:03:14.089 Luke Daque: wait 2 min. Yeah.
500 02:03:15.210 ⇒ 02:03:17.090 Uttam Kumaran: Line, 1, 62.
501 02:03:23.620 ⇒ 02:03:24.350 Uttam Kumaran: What?
502 02:03:25.050 ⇒ 02:03:26.150 Uttam Kumaran: Oh, 5, 6.
503 02:03:26.150 ⇒ 02:03:27.129 Luke Daque: 62.
504 02:03:37.040 ⇒ 02:03:38.769 Uttam Kumaran: What is this? What is safe? Divide.
505 02:03:41.160 ⇒ 02:03:48.920 Luke Daque: It returns a 0 if, like the divisor, is not that so? It doesn’t. Yeah.
506 02:03:49.470 ⇒ 02:03:50.700 Luke Daque: that’s an error out.
507 02:04:01.000 ⇒ 02:04:04.830 Uttam Kumaran: Oh, so it’s basically like, divide the order.
508 02:04:05.240 ⇒ 02:04:09.530 Uttam Kumaran: buy the payments to get the realized revenue.
509 02:04:12.430 ⇒ 02:04:17.759 Luke Daque: Payments, which is the integer, the 3 that we we wish.
510 02:04:19.080 ⇒ 02:04:20.020 Luke Daque: I guess.
511 02:04:22.770 ⇒ 02:04:33.979 Uttam Kumaran: That is going to have the payment in full that’s going to have the whatever the price is the full payment. And then it’s also going to have the full transaction fox
512 02:04:34.710 ⇒ 02:04:40.590 Uttam Kumaran: in there and then over the next 2 months it will
513 02:04:41.510 ⇒ 02:04:45.389 Uttam Kumaran: 2 more orders that are tied to the same transaction.
514 02:04:46.067 ⇒ 02:04:49.969 Uttam Kumaran: That end up being like
515 02:04:50.800 ⇒ 02:04:59.919 Uttam Kumaran: smaller than the original transaction. So let’s like make it more concrete. So it would end up being something like month, one
516 02:05:00.840 ⇒ 02:05:08.070 Uttam Kumaran: month, 2 month, 3 month, 2 month, 3. So let’s say, oops.
517 02:05:08.700 ⇒ 02:05:16.520 Uttam Kumaran: Okay, sorry. Let’s say. Month one. It’s 300 cops
518 02:05:17.100 ⇒ 02:05:23.339 Uttam Kumaran: great. So that’s the full transaction revenue and the transaction costs, and then month 2, it’ll show
519 02:05:23.660 ⇒ 02:05:33.770 Uttam Kumaran: 300 Rev. And 300. Rev. Here. Maybe it’ll show 100 cogs 100 cogs right?
520 02:05:34.320 ⇒ 02:05:35.900 Uttam Kumaran: And so
521 02:05:36.980 ⇒ 02:05:57.130 Uttam Kumaran: if we counted all of these transactions, we’d be over reporting revenue. If we counted all of these cogs, we’d be over reporting cogs. And so what would actually be ideal for profitability is that we would recognize the transaction in month one, because that’s when the revenue comes in. But then we only recognize cogs monthly.
522 02:05:57.150 ⇒ 02:06:10.849 Uttam Kumaran: right? Because we’re collecting that 900 upfront. But then we are. We’re only paying the cogs every month when we ship out the orders. And this is important because we don’t send them all the orders at once. They could end up
523 02:06:11.020 ⇒ 02:06:20.619 Uttam Kumaran: just doing the 1st month and then canceling that, and then we refund them the other 2 months. Right? So that’s important, because we don’t send them all the orders at once. They could end up
524 02:06:20.810 ⇒ 02:06:25.630 Uttam Kumaran: just doing the 1st month and then canceling that, and then we refund them the other 2 months
525 02:06:28.620 ⇒ 02:06:35.400 Uttam Kumaran: every month, when we ship out the orders, and this is important because we don’t send them all the orders at once. They could end up
526 02:06:35.580 ⇒ 02:06:50.029 Uttam Kumaran: just doing the 1st month and then canceling that, and then we refund them the other 2 months. Right? So that’s an important distinction. We want it really to be transaction revenue and realized cogs, and that should be what the
527 02:06:50.740 ⇒ 02:06:54.510 Uttam Kumaran: the like. The transaction level revenue should look like.
528 02:06:55.000 ⇒ 02:07:22.069 Uttam Kumaran: That being said realized cogs is going to change over time right as month, 2 and month 3 fill in. Then it’ll go back up to 300, because over 3 months we have for that particular. For this particular order we have gotten to the or the for this for the particular transaction. It took 3 months to realize the cogs in total. So I know that’s a bit of a mouthful to kind of handle. But we need to think about how we’re accounting for like transaction.
529 02:07:25.070 ⇒ 02:07:28.290 Uttam Kumaran: Okay? So I think roughly,
530 02:07:34.710 ⇒ 02:07:38.510 Uttam Kumaran: we basically need to create an event table with
531 02:07:38.810 ⇒ 02:07:43.790 Uttam Kumaran: all of these sorts of events, I don’t. Wanna, I don’t wanna do this aggregation
532 02:07:44.450 ⇒ 02:07:49.430 Uttam Kumaran: like just in the model basically like month. One transaction should come in month.
533 02:07:49.760 ⇒ 02:07:57.029 Uttam Kumaran: And then, basically, I don’t know what he means exactly by like, why is there still revenue coming in in month, 2 and month, 3.
534 02:07:57.030 ⇒ 02:08:01.505 Luke Daque: Yeah, because we already the revenue is already like full in month one
535 02:08:02.590 ⇒ 02:08:05.850 Luke Daque: in this example, right? The 900 revenue.
536 02:08:07.260 ⇒ 02:08:08.220 Uttam Kumaran: So we yeah.
537 02:08:08.220 ⇒ 02:08:13.920 Luke Daque: If if we add the 300 in month, 2 and month 3, then it’s we’re still like over.
538 02:08:18.770 ⇒ 02:08:22.479 Luke Daque: yeah, we’re over over stating it or something.
539 02:08:28.070 ⇒ 02:08:28.876 Luke Daque: I guess.
540 02:08:29.940 ⇒ 02:08:34.000 Luke Daque: Yeah, he did mention like, what if they cancel
541 02:08:34.320 ⇒ 02:08:37.389 Luke Daque: in month? 2. So we’ll have to refund
542 02:08:37.840 ⇒ 02:08:41.119 Luke Daque: the 600 to to them right, something.
543 02:08:41.260 ⇒ 02:08:44.760 Luke Daque: because they only use the 301 month one.
544 02:08:45.410 ⇒ 02:08:50.369 Uttam Kumaran: But that’s the thing. I don’t want to erase it. I want it to come in as a refund and net out.
545 02:08:50.370 ⇒ 02:08:51.660 Luke Daque: Yeah, yeah.
546 02:08:54.700 ⇒ 02:09:05.700 Luke Daque: should be a a different line, or like we, we should have a different table for refunds, though, but
547 02:09:05.990 ⇒ 02:09:10.610 Luke Daque: or like a on a different on, on a field for refunds or something. I don’t know.
548 02:09:10.920 ⇒ 02:09:12.109 Uttam Kumaran: Oh, okay.
549 02:09:14.510 ⇒ 02:09:18.839 Luke Daque: Okay, this makes sense. And what is, can you understand what he was meaning by the cogs thing?
550 02:09:19.910 ⇒ 02:09:26.699 Luke Daque: Which part idea is that all of these added together is what total cost.
551 02:09:26.700 ⇒ 02:09:28.829 Uttam Kumaran: So we get all the fees.
552 02:09:28.860 ⇒ 02:09:31.170 Luke Daque: Because the cogs is like monthly.
553 02:09:31.320 ⇒ 02:09:34.629 Luke Daque: There’s no like quarterly cogs or something.
554 02:09:34.630 ⇒ 02:09:38.509 Uttam Kumaran: Yeah, they’re more right now. V, 2.
555 02:09:38.560 ⇒ 02:09:58.319 Luke Daque: Mapping and for mapping are models that get pulled directly into here, as we can tell from V, 2 Pb. And Po. These are both models that get pulled in from this Google sheet that’s maintained by the edit team. What we’re doing differently here as a recap is we’re adding A, and we’re also including
556 02:09:58.560 ⇒ 02:10:01.289 Luke Daque: along the way we have. All below are the call curve.
557 02:10:03.020 ⇒ 02:10:32.700 Luke Daque: I’ve broken out total cogs into these different fees, so what we can see is if we look into a Basque order and let’s go to one of their more bigger pharmacy. So we have a visit Fee. We have a pharmacy fee, dispense, fee, shipping fee, and then we have cogs which is uploaded at a product level that the pharmacy team negotiates. So this will be manually maintained. But the.
558 02:10:33.300 ⇒ 02:10:34.940 Uttam Kumaran: That’s it! Mainly maintain.
559 02:10:35.250 ⇒ 02:10:43.650 Luke Daque: Yeah. So I guess all the yellow fields in that Google sheet would be manually maintained. One thing I noticed, though, is he’s using this sheet.
560 02:10:43.760 ⇒ 02:10:47.319 Luke Daque: and I I uploaded a different sheet, which is the.
561 02:10:47.460 ⇒ 02:10:53.520 Luke Daque: So this is coming from Eden product offerings 2,024. So I guess I’ll have to update the
562 02:10:54.390 ⇒ 02:11:02.709 Luke Daque: the source table in bigquery to to this sheet, because he also added a couple of fields here, like the pharmacy fee.
563 02:11:02.860 ⇒ 02:11:04.360 Luke Daque: shipping fee and cogs.
564 02:11:04.580 ⇒ 02:11:05.270 Luke Daque: Stop!
565 02:11:09.500 ⇒ 02:11:10.250 Luke Daque: Let me just.
566 02:11:11.800 ⇒ 02:11:15.290 Uttam Kumaran: We should confirm whether dogs is gonna come from
567 02:11:17.770 ⇒ 02:11:21.350 Uttam Kumaran: whether cogs is gonna come from this table or not.
568 02:11:22.040 ⇒ 02:11:26.800 Luke Daque: That’s what I understood like, it’s gonna be coming from that table.
569 02:11:27.900 ⇒ 02:11:33.239 Luke Daque: So it doesn’t look like they’re using the cogs that’s in bask.
570 02:11:35.710 ⇒ 02:11:39.760 Luke Daque: I know they’re coming from bask. But is there a cause coming from bask
571 02:11:40.040 ⇒ 02:11:42.690 Luke Daque: in order is completed? There’s a calls field.
572 02:11:42.690 ⇒ 02:11:43.250 Uttam Kumaran: Oh, really.
573 02:11:43.460 ⇒ 02:11:45.050 Luke Daque: Yeah, it looks like it.
574 02:11:45.760 ⇒ 02:11:46.859 Luke Daque: Let me double check.
575 02:11:48.250 ⇒ 02:11:52.620 Luke Daque: Yep, there’s a cogs let me see, there’s even pharmacy.
576 02:11:56.610 ⇒ 02:12:01.839 Luke Daque: But not pharmacy. Fee. What’s the other one? This pens?
577 02:12:02.900 ⇒ 02:12:07.499 Luke Daque: No, we don’t have that. The pharmacy fee and dispense fees. And then here
578 02:12:07.990 ⇒ 02:12:11.589 Luke Daque: shipping fee is in order. Shipped looks like
579 02:12:20.860 ⇒ 02:12:23.499 Uttam Kumaran: Where do you see if an order is completed?
580 02:12:25.530 ⇒ 02:12:29.919 Luke Daque: In bask orders in bask order completed data set.
581 02:12:30.340 ⇒ 02:12:38.789 Luke Daque: There’s an order completed table, and then there’s cogs there somewhere in the middle.
582 02:12:40.710 ⇒ 02:12:41.450 Uttam Kumaran: Oh!
583 02:12:44.750 ⇒ 02:12:49.990 Luke Daque: But yeah, I don’t. I don’t know if this is the cogs that he’s referring to.
584 02:12:50.710 ⇒ 02:12:52.450 Luke Daque: or this is the same.
585 02:13:01.730 ⇒ 02:13:03.450 Luke Daque: Do you see the feed coming in.
586 02:13:04.550 ⇒ 02:13:06.030 Luke Daque: No, I don’t see them.
587 02:13:07.950 ⇒ 02:13:11.170 Luke Daque: The pharmacy fee and dispense fee shipping fee
588 02:13:12.154 ⇒ 02:13:17.509 Luke Daque: I don’t see them in order completed. I don’t see them in order. Ship shipped either
589 02:13:24.440 ⇒ 02:13:26.330 Luke Daque: about order updated.
590 02:13:33.510 ⇒ 02:13:35.760 Luke Daque: It’s not here so.
591 02:13:38.850 ⇒ 02:13:40.240 Uttam Kumaran: You don’t see any fees there.
592 02:13:40.240 ⇒ 02:13:41.110 Luke Daque: Nope.
593 02:13:54.640 ⇒ 02:14:00.900 Uttam Kumaran: Oh, maybe that’s the cogs, the rest the farms which.
594 02:14:01.170 ⇒ 02:14:04.209 Luke Daque: Like the sum of all the fees. Is it the cogs.
595 02:14:04.980 ⇒ 02:14:08.459 Uttam Kumaran: Yeah, I don’t know. Honestly, it doesn’t look like.
596 02:14:08.460 ⇒ 02:14:11.489 Luke Daque: It could be we can, we can query that it does that happen?
597 02:14:11.490 ⇒ 02:14:15.129 Uttam Kumaran: Looking at it. They’re not this high. They’re all like hundreds of dollars.
598 02:14:16.450 ⇒ 02:14:22.080 Uttam Kumaran: Okay? Well, can you give? Can you? Can you take a look at like one of these orders, and see if you can find it like
599 02:14:24.580 ⇒ 02:14:27.459 Luke Daque: Yeah, this does that have a an order number.
600 02:14:27.690 ⇒ 02:14:30.460 Luke Daque: But let me see if I can access.
601 02:14:30.460 ⇒ 02:14:35.730 Uttam Kumaran: Here he sent one. He sent one in here in the thread in slack.
602 02:14:36.270 ⇒ 02:14:37.310 Luke Daque: Oh.
603 02:14:41.010 ⇒ 02:14:41.880 Luke Daque: okay.
604 02:15:11.200 ⇒ 02:15:12.790 Luke Daque: I don’t see this.
605 02:15:13.820 ⇒ 02:15:14.660 Uttam Kumaran: You don’t see what.
606 02:15:15.190 ⇒ 02:15:18.950 Luke Daque: And this order number I mean.
607 02:15:19.960 ⇒ 02:15:20.670 Uttam Kumaran: Oh!
608 02:15:20.670 ⇒ 02:15:24.320 Luke Daque: Oh, this is transaction. Id not order. Id.
609 02:15:31.100 ⇒ 02:15:31.510 Luke Daque: Okay?
610 02:15:40.110 ⇒ 02:15:42.199 Luke Daque: Oh, it’s not showing either.
611 02:16:16.050 ⇒ 02:16:17.440 Luke Daque: And see this
612 02:16:25.730 ⇒ 02:16:28.449 Luke Daque: row 15 from his video.
613 02:16:30.540 ⇒ 02:16:36.570 Luke Daque: Oh, so this is bundle id not variant id.
614 02:17:19.840 ⇒ 02:17:22.129 Uttam Kumaran: Is it? Has. Was there anything in refunds.
615 02:17:27.400 ⇒ 02:17:28.487 Luke Daque: Let me see.
616 02:17:29.554 ⇒ 02:17:31.079 Uttam Kumaran: Can just do a control F.
617 02:17:31.690 ⇒ 02:17:36.340 Luke Daque: Yeah, it doesn’t look like there’s any refund in order completed, maybe in.
618 02:17:36.900 ⇒ 02:17:37.440 Uttam Kumaran: Yeah, I don’t see.
619 02:17:37.440 ⇒ 02:17:45.870 Luke Daque: It’s a different, maybe in a different table, maybe an order updated somewhere.
620 02:17:48.549 ⇒ 02:17:49.240 Luke Daque: Nope.
621 02:17:49.820 ⇒ 02:17:51.520 Uttam Kumaran: It could be from updated. I didn’t.
622 02:17:51.520 ⇒ 02:17:57.559 Luke Daque: Yeah, in in updated, there’s body data refund amount. Maybe that’s it.
623 02:18:03.120 ⇒ 02:18:07.110 Luke Daque: We’ll have to look further.
624 02:18:18.959 ⇒ 02:18:28.500 Luke Daque: Cogs is 3, 7, 5 for this one, and in the orders table it looks like cogs.
625 02:18:29.160 ⇒ 02:18:31.240 Luke Daque: It’s 1 0, 5.
626 02:18:32.650 ⇒ 02:18:36.300 Luke Daque: So yeah, it’s not matching the Cokes in the
627 02:18:37.763 ⇒ 02:18:43.160 Luke Daque: order completed. Table does not match with the cogs that’s in the Google Sheet.
628 02:18:43.780 ⇒ 02:18:44.440 Uttam Kumaran: Okay.
629 02:18:49.780 ⇒ 02:18:50.360 Uttam Kumaran: Let me.
630 02:18:50.360 ⇒ 02:18:50.965 Luke Daque: Maybe.
631 02:18:55.850 ⇒ 02:19:07.459 Luke Daque: I wonder if yeah, it’s also not like 3 times 3, it’s 1 0, 5 in. In, in
632 02:19:07.730 ⇒ 02:19:15.079 Luke Daque: the order, complete table. So if we multiply this by 3, it should be like 3, 15, not 3, 75, or something.
633 02:19:23.500 ⇒ 02:19:24.600 Luke Daque: Yeah.
634 02:19:28.690 ⇒ 02:19:32.619 Luke Daque: there is even payments here.
635 02:20:02.740 ⇒ 02:20:11.410 Luke Daque: Where did you find that table that you did cabin, which which data set.
636 02:20:11.830 ⇒ 02:20:13.410 Uttam Kumaran: It’s in temp data set.
637 02:20:13.410 ⇒ 02:20:14.210 Luke Daque: Attempt.
638 02:20:17.100 ⇒ 02:20:18.850 Uttam Kumaran: Yeah. Just search for refunds.
639 02:20:19.030 ⇒ 02:20:19.770 Luke Daque: Hmm!
640 02:20:23.150 ⇒ 02:20:26.339 Luke Daque: Once, and that was, where’s that?
641 02:20:26.940 ⇒ 02:20:28.900 Luke Daque: We do refunds
642 02:20:32.990 ⇒ 02:20:35.670 Luke Daque: that doesn’t look like up to date, though, like it.
643 02:20:35.670 ⇒ 02:20:36.480 Uttam Kumaran: That’s the last.
644 02:20:36.480 ⇒ 02:20:38.090 Luke Daque: Modified last July.
645 02:20:38.400 ⇒ 02:20:39.080 Uttam Kumaran: Yeah.
646 02:20:39.400 ⇒ 02:20:45.870 Uttam Kumaran: okay, this is fine for now I think Robert still has to get us some stuff. I guess we’ll con. I mean, I just wanna continue on customers.
647 02:20:46.030 ⇒ 02:20:46.900 Luke Daque: Yeah.
648 02:20:47.030 ⇒ 02:20:49.009 Uttam Kumaran: Let’s get out our stuff, and then
649 02:20:50.740 ⇒ 02:20:56.310 Uttam Kumaran: I I can let you go and then continue tomorrow. Once I get some more answers.
650 02:20:56.910 ⇒ 02:21:00.773 Luke Daque: Sure like for the orders I’m working on the orders table at the moment.
651 02:21:01.710 ⇒ 02:21:08.150 Luke Daque: Does that mean we we don’t put in logic for the revenue. For now, because
652 02:21:08.480 ⇒ 02:21:13.320 Luke Daque: that’s what we’re trying to figure out with Robert like a free.
653 02:21:17.530 ⇒ 02:21:22.069 Luke Daque: Or I can just put in the calculation for revenue just based on the.
654 02:21:22.070 ⇒ 02:21:22.410 Uttam Kumaran: Yeah.
655 02:21:22.410 ⇒ 02:21:23.759 Luke Daque: Order, questions.
656 02:21:23.760 ⇒ 02:21:28.200 Uttam Kumaran: Let’s let’s just do that now and then. Do it straight. Now and then we’ll we’ll
657 02:21:28.380 ⇒ 02:21:30.420 Uttam Kumaran: for the Pr. We’ll we’ll change it.
658 02:21:30.610 ⇒ 02:21:31.240 Luke Daque: Okay.
659 02:21:32.630 ⇒ 02:21:33.980 Luke Daque: Sounds good.
660 02:21:33.980 ⇒ 02:21:38.749 Uttam Kumaran: Cause. I just wanna show them that we’re somewhere. And then we’re figuring out this sort of logic.
661 02:21:39.140 ⇒ 02:21:39.890 Luke Daque: Okay.
662 02:21:44.910 ⇒ 02:21:48.220 Luke Daque: I think we should be good then, for the orders.
663 02:21:49.640 ⇒ 02:21:52.039 Uttam Kumaran: Okay, let me just write the customers. One too.
664 02:21:53.880 ⇒ 02:21:56.230 Luke Daque: I’ll just create a Pr for this.
665 02:22:04.450 ⇒ 02:22:07.189 Luke Daque: What do we name this in mind? It’s just orders.
666 02:22:11.420 ⇒ 02:22:12.050 Uttam Kumaran: Hmm.
667 02:22:43.080 ⇒ 02:22:47.709 Uttam Kumaran: okay, well, I’m gonna try to be a little organized. I’m gonna create a I’ll create a ticket for customers. Table.
668 02:22:48.940 ⇒ 02:22:53.910 Luke Daque: Yeah, that me create the orders one as well, and also the one.
669 02:22:53.910 ⇒ 02:22:56.340 Uttam Kumaran: Save notion, save Nico. A little bit of time.
670 02:22:56.730 ⇒ 02:23:01.480 Luke Daque: Yeah, yeah, let me do that.
671 02:23:03.250 ⇒ 02:23:07.780 Uttam Kumaran: Yeah, dude, we should have 2 more clients starting.
672 02:23:08.050 ⇒ 02:23:12.290 Luke Daque: Yes, so you added me to stack Blitz.
673 02:23:12.290 ⇒ 02:23:15.989 Uttam Kumaran: Yeah. So you know, you know, have you heard of bolt, bolt, dot new.
674 02:23:16.840 ⇒ 02:23:18.173 Luke Daque: What’s that? I had a.
675 02:23:18.440 ⇒ 02:23:20.540 Uttam Kumaran: It’s a new like AI product.
676 02:23:20.540 ⇒ 02:23:24.120 Luke Daque: Oh, bolt!
677 02:23:24.440 ⇒ 02:23:29.109 Uttam Kumaran: It’s basically like you type in anything you want. And it builds a full stack app for you.
678 02:23:29.690 ⇒ 02:23:31.280 Luke Daque: That’s cool.
679 02:23:31.600 ⇒ 02:23:35.089 Uttam Kumaran: My friend like runs data there, and he’s bringing us in.
680 02:23:37.390 ⇒ 02:23:38.080 Luke Daque: Wow!
681 02:23:39.660 ⇒ 02:23:44.339 Uttam Kumaran: I’ll ask him. Once we start working with, I’ll ask him, maybe, if we can get a free version of it or play around.
682 02:23:44.840 ⇒ 02:23:48.789 Luke Daque: Yeah, let me try to sign in is it’s not free, I guess.
683 02:23:49.020 ⇒ 02:23:50.339 Uttam Kumaran: I think it’s free. Yeah.
684 02:23:54.800 ⇒ 02:23:57.490 Luke Daque: Deploy full stack web apps. That’s crazy.
685 02:24:05.170 ⇒ 02:24:09.460 Luke Daque: I mean, I did see the A podcast between
686 02:24:10.511 ⇒ 02:24:13.720 Luke Daque: Joe, Rogan and mark Zuckerberg.
687 02:24:13.870 ⇒ 02:24:15.600 Uttam Kumaran: Oh, I’m just halfway through that. What do you think.
688 02:24:15.600 ⇒ 02:24:17.880 Luke Daque: Oh, yeah, yeah, it’s pretty cool.
689 02:24:18.418 ⇒ 02:24:22.880 Luke Daque: And like 1 1 of the topics there was like about AI and stuff. And like
690 02:24:24.350 ⇒ 02:24:32.690 Luke Daque: Mark just said, like almost almost half their code is already like AI created by AI,
691 02:24:33.120 ⇒ 02:24:36.380 Luke Daque: like they have like AI, as like mid leveling
692 02:24:36.480 ⇒ 02:24:39.333 Luke Daque: software engineers or stuff like something like that.
693 02:24:39.690 ⇒ 02:24:40.220 Uttam Kumaran: Crazy.
694 02:24:40.220 ⇒ 02:24:40.870 Luke Daque: I know.
695 02:24:47.740 ⇒ 02:24:50.840 Uttam Kumaran: Oh, you changed your notion picture! It looks nice.
696 02:24:51.130 ⇒ 02:24:52.850 Luke Daque: Yeah, I changed it because, like.
697 02:24:54.095 ⇒ 02:24:57.139 Luke Daque: Roberts is also like this. R and
698 02:24:58.690 ⇒ 02:24:59.280 Uttam Kumaran: Oh!
699 02:24:59.280 ⇒ 02:25:02.689 Luke Daque: Yeah, it was the same. I I just changed it.
700 02:25:03.290 ⇒ 02:25:05.020 Luke Daque: It looks like like notion. Can.
701 02:25:05.020 ⇒ 02:25:08.400 Uttam Kumaran: I wish I could. I would update it for folks if I could, but.
702 02:25:19.690 ⇒ 02:25:22.370 Luke Daque: The Eden.
703 02:27:01.300 ⇒ 02:27:04.599 Luke Daque: Yeah, it’s great to know we get getting more clients.
704 02:27:05.930 ⇒ 02:27:09.070 Uttam Kumaran: Dude. We’ve been grinding sales like.
705 02:27:09.070 ⇒ 02:27:09.830 Luke Daque: Yeah.
706 02:27:11.290 ⇒ 02:27:14.400 Uttam Kumaran: To an like as as much as possible.
707 02:27:35.510 ⇒ 02:27:39.840 Luke Daque: Yeah. So I think I saw like, we’re gonna go back to Jabbie as well.
708 02:27:40.220 ⇒ 02:27:40.660 Uttam Kumaran: Yeah.
709 02:27:40.660 ⇒ 02:27:43.219 Luke Daque: Or she won’t, or she won’t work for Javi.
710 02:27:45.520 ⇒ 02:27:46.280 Uttam Kumaran: Yes.
711 02:27:53.220 ⇒ 02:27:54.040 Luke Daque: Nice.
712 02:28:27.920 ⇒ 02:28:32.490 Luke Daque: I have too many notion documents open, maybe. Tabs.
713 02:28:42.690 ⇒ 02:28:48.050 Luke Daque: Yeah, I’ll I’ll push this. I’ll create a Pr for the orders. And yeah, we can go from there.
714 02:28:48.270 ⇒ 02:28:51.619 Uttam Kumaran: Okay, okay, I’m gonna work on customers, too.
715 02:29:30.620 ⇒ 02:29:36.800 Luke Daque: Are you still looking for other like people? I I think you’re still like always interviewing people. Right?
716 02:29:37.340 ⇒ 02:29:38.710 Luke Daque: Yes, you have.
717 02:29:38.870 ⇒ 02:29:42.210 Uttam Kumaran: If you have people, then totally let me know.
718 02:29:43.000 ⇒ 02:29:50.530 Luke Daque: Cool. I gotta. I think I have someone else like he doesn’t really have experience with data or anything. But maybe he can be a good
719 02:29:50.960 ⇒ 02:29:52.930 Luke Daque: like project manager, or whatever.
720 02:29:53.130 ⇒ 02:29:59.640 Luke Daque: He’s also an engineer, though. But like he just got laid off last month. So we sleep
721 02:30:00.030 ⇒ 02:30:02.560 Luke Daque: looking for for a job. So maybe.
722 02:30:02.900 ⇒ 02:30:04.350 Uttam Kumaran: Okay. Yeah. Please.
723 02:30:05.350 ⇒ 02:30:07.500 Luke Daque: Maybe I’ll just send an.
724 02:30:07.500 ⇒ 02:30:10.069 Uttam Kumaran: Send me his, send me his, send me his resume.
725 02:30:11.090 ⇒ 02:30:12.030 Luke Daque: I’m sure.
726 02:30:12.770 ⇒ 02:30:17.042 Uttam Kumaran: And then, if you want to just make the
727 02:30:18.210 ⇒ 02:30:20.329 Uttam Kumaran: make an intro, I’m happy to talk to him.
728 02:30:20.440 ⇒ 02:30:23.523 Uttam Kumaran: Just tell me just, and when you send me his resume just
729 02:30:24.030 ⇒ 02:30:28.979 Uttam Kumaran: cause I’ll be looking at it when I go. Talk to him again. Just let me know how. Remind me how you know him.
730 02:30:29.400 ⇒ 02:30:30.460 Luke Daque: Okay. Cool.
731 02:30:31.080 ⇒ 02:30:32.090 Luke Daque: Sounds good.
732 02:32:02.110 ⇒ 02:32:04.140 Uttam Kumaran: Yeah, I’ve been listening to a lot of drum and bass
733 02:32:05.790 ⇒ 02:32:10.689 Uttam Kumaran: lot of drum and bass heavy so much.
734 02:36:30.310 ⇒ 02:36:33.769 Uttam Kumaran: I think we should also make some decisions around column naming.
735 02:36:35.960 ⇒ 02:36:36.730 Luke Daque: Yeah.
736 02:36:39.440 ⇒ 02:36:40.540 Uttam Kumaran: What do you want to do.
737 02:36:49.690 ⇒ 02:36:56.070 Luke Daque: Yeah, like, the there are weird column names here like timestamp and stuff.
738 02:36:57.390 ⇒ 02:36:58.110 Luke Daque: Yeah.
739 02:37:07.960 ⇒ 02:37:13.509 Luke Daque: well, 1st of all, I guess we can just standardize it by making everything snake case
740 02:37:14.743 ⇒ 02:37:22.010 Luke Daque: like cause. Some sources have column names that are like don’t have underscore.
741 02:37:22.010 ⇒ 02:37:25.770 Uttam Kumaran: Oh, wait 100%, 100% snake case.
742 02:37:26.590 ⇒ 02:37:35.029 Luke Daque: Yeah, so that’s 1 another would be for ids. We we have to be like specific at
743 02:37:35.380 ⇒ 02:37:37.160 Luke Daque: what kind of id that is
744 02:37:37.690 ⇒ 02:37:44.310 Luke Daque: like in bask order completed. For example, there’s there’s a field there called Id, and
745 02:37:44.420 ⇒ 02:37:49.310 Luke Daque: we should know, like what id is that is, that the order Id, or something else.
746 02:37:50.450 ⇒ 02:37:51.190 Uttam Kumaran: Okay.
747 02:37:54.380 ⇒ 02:38:01.050 Uttam Kumaran: I think, for dates. It could be like event, event, name, underscore, date.
748 02:38:02.320 ⇒ 02:38:05.370 Luke Daque: Depending on what that date is. Right.
749 02:38:05.370 ⇒ 02:38:09.560 Uttam Kumaran: And then how do you feel about transaction? Count versus like count transactions?
750 02:38:14.250 ⇒ 02:38:16.500 Uttam Kumaran: I think it should be transaction. Count.
751 02:38:17.220 ⇒ 02:38:18.880 Luke Daque: Yeah, that makes sense.
752 02:38:21.490 ⇒ 02:38:22.000 Uttam Kumaran: Right.
753 02:38:22.000 ⇒ 02:38:28.110 Luke Daque: Is that true? For all kinds of aggregation, or just for count
754 02:38:28.350 ⇒ 02:38:30.899 Luke Daque: cause, the average would be average
755 02:38:31.930 ⇒ 02:38:36.410 Luke Daque: users, something like that right? Or like some
756 02:38:36.800 ⇒ 02:38:41.290 Luke Daque: total users, for, like some aggregations, I guess, I believe.
757 02:38:42.730 ⇒ 02:38:43.369 Luke Daque: What do you think.
758 02:38:43.370 ⇒ 02:38:44.599 Uttam Kumaran: Well, we have some.
759 02:38:45.520 ⇒ 02:38:51.210 Luke Daque: If it’s a sum, would it be like? If it’s in the number of orders, or like this
760 02:38:51.940 ⇒ 02:38:56.460 Luke Daque: sum of revenue, then is it?
761 02:38:58.410 ⇒ 02:39:01.339 Luke Daque: Usually it’s like total revenue or something right?
762 02:39:03.050 ⇒ 02:39:05.540 Uttam Kumaran: Yeah, but the total we shouldn’t do total.
763 02:39:05.820 ⇒ 02:39:08.730 Luke Daque: Yeah, I guess some underscore revenue.
764 02:39:09.240 ⇒ 02:39:13.479 Uttam Kumaran: Yeah, I’m gonna look at a couple of other Dbt guides.
765 02:39:14.180 ⇒ 02:39:19.710 Luke Daque: Yeah, let’s do that, or let let me try to ask, chat. Gpt, what it recommends.
766 02:41:02.990 ⇒ 02:41:05.719 Luke Daque: Yeah, this makes sense. Let me share this.
767 02:41:08.520 ⇒ 02:41:10.280 Luke Daque: Chat gpt.
768 02:41:43.010 ⇒ 02:41:50.830 Luke Daque: We can add this to our documentation as well as to the the standard practice.
769 02:46:00.560 ⇒ 02:46:04.880 Uttam Kumaran: yeah, I think we just have to make a decision. But let’s keep going for now, I have some ideas.
770 02:46:05.750 ⇒ 02:46:06.430 Luke Daque: Okay, cool.
771 02:48:04.470 ⇒ 02:48:05.760 Luke Daque: Can’t I find the
772 02:48:11.390 ⇒ 02:48:12.870 Luke Daque: task that I create.
773 02:59:38.690 ⇒ 02:59:42.880 Uttam Kumaran: Dude. You know. What? The how do I convert a timestamp in bigquery like? What?
774 02:59:43.320 ⇒ 02:59:45.829 Uttam Kumaran: How is this? Not like this? Easy?
775 02:59:49.370 ⇒ 02:59:50.500 Uttam Kumaran: You’re on mute.
776 02:59:51.250 ⇒ 02:59:53.770 Luke Daque: Oh, sorry! And to date.
777 02:59:55.180 ⇒ 02:59:57.400 Uttam Kumaran: I just want to convert the time zone.
778 02:59:59.020 ⇒ 03:00:01.600 Luke Daque: Oh, cause it’s utc.
779 03:00:02.670 ⇒ 03:00:03.250 Uttam Kumaran: Yeah.
780 03:00:03.250 ⇒ 03:00:05.630 Luke Daque: Yeah, I don’t know that.
781 03:00:06.170 ⇒ 03:00:13.630 Luke Daque: Let’s see, I don’t know the exact function to use
782 03:00:31.230 ⇒ 03:00:32.820 Luke Daque: something like this.
783 03:00:33.940 ⇒ 03:00:35.260 Uttam Kumaran: Date, time.
784 03:00:35.260 ⇒ 03:00:46.000 Luke Daque: Yeah, and then add the time zone, if you need, in a different time zone.
785 03:00:49.460 ⇒ 03:00:50.070 Uttam Kumaran: Okay.
786 03:04:40.860 ⇒ 03:04:47.860 Uttam Kumaran: okay, I’m basically ready. Once you push your new raw source, I should be ready.
787 03:04:49.670 ⇒ 03:04:51.789 Luke Daque: For the for the orders you mean.
788 03:04:53.180 ⇒ 03:04:53.910 Uttam Kumaran: Yeah.
789 03:04:54.400 ⇒ 03:04:55.230 Luke Daque: Or the product.
790 03:04:55.230 ⇒ 03:04:58.969 Uttam Kumaran: Or or I can, or if you have it in your branch, I could pick it from your branch.
791 03:05:00.620 ⇒ 03:05:03.269 Luke Daque: Yeah, I did create the pr, though. I.
792 03:05:03.270 ⇒ 03:05:03.620 Uttam Kumaran: Oh!
793 03:05:03.620 ⇒ 03:05:04.569 Luke Daque: I’ll get here.
794 03:05:04.830 ⇒ 03:05:05.200 Uttam Kumaran: Fair enough.
795 03:05:06.620 ⇒ 03:05:07.979 Uttam Kumaran: Yeah. Let me go push it.
796 03:05:08.300 ⇒ 03:05:09.659 Uttam Kumaran: Oh, nice. Okay.
797 03:05:11.000 ⇒ 03:05:14.929 Luke Daque: Yeah, but we can definitely change the logic, for the revenue and stuff
798 03:05:15.260 ⇒ 03:05:19.799 Luke Daque: do whatever the correct one is based on the product, mapping or or whatever.
799 03:05:21.420 ⇒ 03:05:23.160 Luke Daque: But yeah, that should be
800 03:05:26.640 ⇒ 03:05:28.230 Luke Daque: fine. I guess.
801 03:05:28.500 ⇒ 03:05:31.910 Luke Daque: I mean, we should have everything for at least 4,
802 03:05:33.680 ⇒ 03:05:35.959 Luke Daque: the ones that we need currently.
803 03:06:03.910 ⇒ 03:06:08.369 Uttam Kumaran: Okay, yeah, we should rename some of these columns eventually.
804 03:06:08.840 ⇒ 03:06:09.550 Luke Daque: Yeah.
805 03:06:14.200 ⇒ 03:06:22.880 Luke Daque: yeah. I’m also like, just looking at the order details table. We can also add, like ship dates in there from the order basket shipped
806 03:06:27.400 ⇒ 03:06:33.510 Luke Daque: and some stuff from the from the order update table as well.
807 03:06:34.930 ⇒ 03:06:43.100 Luke Daque: although I’m not sure what they’re adding here, like, I guess
808 03:06:43.750 ⇒ 03:06:48.260 Luke Daque: they’re using the updates table to determine whether an order is cancelled or not.
809 03:06:48.770 ⇒ 03:06:49.799 Luke Daque: But I can.
810 03:06:50.100 ⇒ 03:06:54.369 Luke Daque: I can investigate further that. Or if that’s it, even any.
811 03:06:56.870 ⇒ 03:07:00.999 Uttam Kumaran: Can we? Can we remove any of the patient information.
812 03:07:01.690 ⇒ 03:07:03.120 Luke Daque: Yeah, sure.
813 03:07:03.620 ⇒ 03:07:05.239 Uttam Kumaran: From the orders table.
814 03:07:05.630 ⇒ 03:07:07.910 Luke Daque: Yeah, cause that should be in the.
815 03:07:08.130 ⇒ 03:07:09.967 Uttam Kumaran: Customer like that.
816 03:07:10.800 ⇒ 03:07:12.270 Luke Daque: Yeah, okay?
817 03:07:13.160 ⇒ 03:07:18.909 Luke Daque: And the phone number, I guess, 1st name, last name email phone number, user. Id, we don’t need as well, right?
818 03:07:20.150 ⇒ 03:07:23.729 Luke Daque: Because that’s already in your or I guess we need.
819 03:07:23.730 ⇒ 03:07:25.740 Luke Daque: No, I would leave the Id, but I would.
820 03:07:25.740 ⇒ 03:07:26.470 Uttam Kumaran: With joints.
821 03:07:26.470 ⇒ 03:07:30.459 Uttam Kumaran: This is where I want to change. This is where we. I want to make a customer. Id.
822 03:07:31.060 ⇒ 03:07:31.990 Luke Daque: Right.
823 03:07:32.240 ⇒ 03:07:37.960 Uttam Kumaran: So can you change it to Customer Id in the bask order completed.
824 03:07:39.900 ⇒ 03:07:42.349 Luke Daque: We can. We can the raw.
825 03:07:43.140 ⇒ 03:07:43.720 Uttam Kumaran: Raw table.
826 03:07:58.360 ⇒ 03:08:03.200 Luke Daque: What else products? I guess I guess
827 03:08:04.200 ⇒ 03:08:06.937 Luke Daque: we can leave them there for now. But
828 03:08:07.860 ⇒ 03:08:14.510 Luke Daque: ideally, we just need we can. We? We just have to join that to the product mapping table.
829 03:08:14.510 ⇒ 03:08:14.920 Uttam Kumaran: Yeah.
830 03:08:14.920 ⇒ 03:08:18.590 Luke Daque: Need to add the Ids, the bundle id and the.
831 03:08:20.160 ⇒ 03:08:20.930 Uttam Kumaran: Id.
832 03:08:31.570 ⇒ 03:08:34.980 Luke Daque: I’ll remove all the product stuff.
833 03:08:40.890 ⇒ 03:08:41.320 Luke Daque: Yeah.
834 03:08:42.230 ⇒ 03:08:44.820 Uttam Kumaran: Can you change status to order status.
835 03:08:45.210 ⇒ 03:08:46.729 Luke Daque: Yeah, makes sense.
836 03:08:54.070 ⇒ 03:08:56.399 Uttam Kumaran: And then things like loaded at
837 03:08:56.630 ⇒ 03:08:59.590 Uttam Kumaran: any timestamp. We want to put the timestamp.
838 03:09:01.980 ⇒ 03:09:03.090 Luke Daque: So the the.
839 03:09:04.850 ⇒ 03:09:08.300 Uttam Kumaran: Load. It’s like loaded at timestamp.
840 03:09:08.600 ⇒ 03:09:09.930 Uttam Kumaran: Basically, right?
841 03:09:11.230 ⇒ 03:09:12.999 Luke Daque: Or what do we decide?
842 03:09:14.300 ⇒ 03:09:16.559 Luke Daque: Yeah, let’s let’s do that for now, until we.
843 03:09:16.840 ⇒ 03:09:18.730 Uttam Kumaran: Yeah. Loaded at timestamp.
844 03:09:22.340 ⇒ 03:09:22.970 Luke Daque: Okay.
845 03:09:27.790 ⇒ 03:09:30.460 Luke Daque: Pharmacy name. I don’t think we need right.
846 03:09:36.470 ⇒ 03:09:38.620 Uttam Kumaran: Let’s leave it there for now.
847 03:09:38.620 ⇒ 03:09:39.470 Luke Daque: Okay.
848 03:09:40.850 ⇒ 03:09:43.260 Uttam Kumaran: You got rid of full name, or what is full name.
849 03:09:43.260 ⇒ 03:09:49.115 Luke Daque: Yeah, that’s yeah. I got rid of that, because that should be the customer name or something.
850 03:09:56.460 ⇒ 03:10:00.059 Uttam Kumaran: Okay, go ahead and push the latest, and then I’ll take a look again.
851 03:10:14.460 ⇒ 03:10:16.770 Luke Daque: Yeah, I just pushed it.
852 03:12:19.930 ⇒ 03:12:22.160 Uttam Kumaran: I see I still see full name.
853 03:12:29.410 ⇒ 03:12:30.850 Luke Daque: Oh, it shouldn’t be there!
854 03:12:30.970 ⇒ 03:12:36.370 Luke Daque: I am looking at the recent commit. It should.
855 03:12:36.370 ⇒ 03:12:38.650 Uttam Kumaran: On 9, 20 in orders.
856 03:12:55.210 ⇒ 03:13:00.053 Luke Daque: Oh, yeah, was that a duplicate.
857 03:13:03.230 ⇒ 03:13:03.950 Uttam Kumaran: Maybe.
858 03:13:12.120 ⇒ 03:13:14.640 Luke Daque: I just pushed up another commit.
859 03:13:39.230 ⇒ 03:13:41.140 Uttam Kumaran: Okay? And you check that. This runs.
860 03:13:44.310 ⇒ 03:13:46.309 Luke Daque: Let me let me do that now.
861 03:16:56.160 ⇒ 03:16:57.090 Uttam Kumaran: How does it look?
862 03:16:57.870 ⇒ 03:17:02.190 Luke Daque: Yeah, there’s some clear. I missed some stuff just.
863 03:17:02.190 ⇒ 03:17:02.800 Uttam Kumaran: Go ahead!
864 03:17:02.800 ⇒ 03:17:03.355 Luke Daque: Fixing.
865 03:17:05.450 ⇒ 03:17:08.100 Luke Daque: Yeah, it should be running fine. Now
866 03:17:14.080 ⇒ 03:17:14.950 Luke Daque: cool.
867 03:17:16.470 ⇒ 03:17:18.711 Luke Daque: Push the fix. The fixes
868 03:17:27.930 ⇒ 03:17:29.160 Luke Daque: should be good.
869 03:17:34.980 ⇒ 03:17:39.010 Uttam Kumaran: What’s your perspective on doing the select star at the end.
870 03:17:42.170 ⇒ 03:17:44.690 Luke Daque: What do you mean like? Why, I do that.
871 03:17:45.350 ⇒ 03:17:51.629 Uttam Kumaran: Yeah, cause I’m gonna I’m gonna I’m gonna add a SQL Fluff and some stuff to our repo.
872 03:17:51.910 ⇒ 03:17:52.650 Luke Daque: Hmm.
873 03:17:52.650 ⇒ 03:17:55.010 Uttam Kumaran: But I just wanted to get your.
874 03:17:55.460 ⇒ 03:18:01.669 Luke Daque: Yeah, I I usually just do that because like for debugging purposes.
875 03:18:01.670 ⇒ 03:18:02.190 Uttam Kumaran: Oh!
876 03:18:02.190 ⇒ 03:18:02.629 Luke Daque: If I need.
877 03:18:02.630 ⇒ 03:18:05.089 Uttam Kumaran: Yeah, yeah, yeah. Look at one column or something.
878 03:18:05.090 ⇒ 03:18:05.830 Luke Daque: Yeah.
879 03:18:06.330 ⇒ 03:18:07.237 Uttam Kumaran: Makes sense. Okay.
880 03:18:11.280 ⇒ 03:18:15.050 Uttam Kumaran: okay, yeah. Go ahead, merge. And then.
881 03:18:15.500 ⇒ 03:18:16.309 Luke Daque: Cool! Oh.
882 03:18:16.310 ⇒ 03:18:20.480 Uttam Kumaran: Well, I’m gonna do my customers.
883 03:18:20.920 ⇒ 03:18:21.930 Luke Daque: Nice.
884 03:18:31.010 ⇒ 03:18:32.870 Luke Daque: Okay. Merged.
885 03:18:33.600 ⇒ 03:18:34.160 Uttam Kumaran: Cool.
886 03:18:38.420 ⇒ 03:18:39.627 Uttam Kumaran: Okay. Let me
887 03:18:41.250 ⇒ 03:18:44.650 Luke Daque: Where did you get the source for the customers.
888 03:18:45.620 ⇒ 03:18:47.620 Uttam Kumaran: I’m gonna pull it from basketors.
889 03:18:48.030 ⇒ 03:18:51.470 Luke Daque: Oh, still, Baske orders, and just make a dynamic.
890 03:18:51.470 ⇒ 03:18:56.660 Uttam Kumaran: And then. But I’m gonna I’m gonna start to include shipment. We’ll start to add more stuff there
891 03:18:56.770 ⇒ 03:18:57.910 Uttam Kumaran: over time.
892 03:18:58.120 ⇒ 03:19:03.990 Luke Daque: Yeah, I guess that’s also true for the orders, like shipments and stuff.
893 03:19:06.550 ⇒ 03:19:07.950 Luke Daque: Ship, dates.
894 03:19:55.360 ⇒ 03:19:57.710 Uttam Kumaran: What the fuck? What is? Where is it?
895 03:21:07.900 ⇒ 03:21:12.239 Luke Daque: Wonder if there’s a DVD package for air by sources.
896 03:21:14.610 ⇒ 03:21:17.699 Uttam Kumaran: Oh, dude! I want to use dlt! We should try that.
897 03:21:17.700 ⇒ 03:21:19.700 Luke Daque: Oh, yeah, you haven’t.
898 03:21:20.200 ⇒ 03:21:23.780 Luke Daque: Yeah. I’ll look into that. You shared that previously.
899 03:21:32.310 ⇒ 03:21:35.390 Luke Daque: We can try it on Eden, I guess, since we’re just.
900 03:21:35.390 ⇒ 03:21:35.860 Uttam Kumaran: Yeah.
901 03:21:35.860 ⇒ 03:21:37.060 Luke Daque: Starting from scratch.
902 03:21:37.370 ⇒ 03:21:40.280 Uttam Kumaran: Well, it depends like what other stuff they want, you know.
903 03:21:40.740 ⇒ 03:21:41.560 Luke Daque: Yeah.
904 03:24:49.380 ⇒ 03:24:55.060 Uttam Kumaran: Oh, but did you? You didn’t. You didn’t add anything to the top of the Dbt, so we’re using the default for now.
905 03:24:56.150 ⇒ 03:24:58.709 Luke Daque: I added, you mean the config block
906 03:25:00.187 ⇒ 03:25:03.309 Luke Daque: I added it in the Dbt project. Yano already.
907 03:25:03.310 ⇒ 03:25:04.590 Luke Daque: Oh, nice
908 03:25:04.590 ⇒ 03:25:09.320 Luke Daque: models! Yeah, so we don’t have it. I mean, we don’t have to add it to each model.
909 03:25:10.090 ⇒ 03:25:10.990 Uttam Kumaran: Okay, great.
910 03:27:05.070 ⇒ 03:27:09.669 Uttam Kumaran: Okay. Can we go ahead and create? I guess. Last thing, can we go ahead and create
911 03:27:09.910 ⇒ 03:27:12.810 Uttam Kumaran: a view in March?
912 03:27:14.170 ⇒ 03:27:18.490 Uttam Kumaran: For this sort of like high, level table.
913 03:27:21.090 ⇒ 03:27:24.450 Luke Daque: What do you mean? Like, what? What view do we do? You need.
914 03:27:27.770 ⇒ 03:27:28.625 Luke Daque: Like that.
915 03:27:32.850 ⇒ 03:27:36.659 Luke Daque: A date with a date. Spanes, perhaps. Oh, no, I don’t know.
916 03:27:37.280 ⇒ 03:27:42.460 Uttam Kumaran: I guess I just wanna bring in the yeah, the or
917 03:27:50.430 ⇒ 03:27:52.059 Uttam Kumaran: like, we want the
918 03:27:59.750 ⇒ 03:28:06.140 Uttam Kumaran: we almost want like this sort of daily reporting view.
919 03:28:09.340 ⇒ 03:28:16.779 Luke Daque: So it’s like a date spine, and then aggregates of like count of orders. Sum of revenue, something like that.
920 03:28:17.840 ⇒ 03:28:18.690 Uttam Kumaran: Yeah.
921 03:28:21.010 ⇒ 03:28:21.810 Luke Daque: Okay.
922 03:28:22.610 ⇒ 03:28:24.117 Luke Daque: I’ll call it
923 03:28:27.730 ⇒ 03:28:28.970 Luke Daque: order.
924 03:28:33.510 ⇒ 03:28:35.190 Uttam Kumaran: Or actually, maybe it was just.
925 03:28:36.920 ⇒ 03:28:41.189 Uttam Kumaran: I think maybe we just go from here or yeah, actually go ahead and build it. And let’s see.
926 03:28:43.340 ⇒ 03:28:48.500 Luke Daque: Well, actually, if we have a a data visualization tool that should be able to do
927 03:28:48.700 ⇒ 03:28:52.430 Luke Daque: to do it from this model, right? We don’t have to create a different.
928 03:28:53.070 ⇒ 03:29:00.450 Uttam Kumaran: I guess we just need new orders, new customers, right?
929 03:29:00.670 ⇒ 03:29:03.100 Uttam Kumaran: So it’s like, where day is today.
930 03:29:04.010 ⇒ 03:29:06.830 Luke Daque: Sum of revenue where date is today.
931 03:29:07.530 ⇒ 03:29:08.075 Luke Daque: Right?
932 03:29:09.910 ⇒ 03:29:11.779 Luke Daque: So new.
933 03:29:11.780 ⇒ 03:29:19.340 Uttam Kumaran: Some some case when data today, then revenue as revenue today.
934 03:29:22.450 ⇒ 03:29:23.560 Uttam Kumaran: see what I mean.
935 03:29:23.760 ⇒ 03:29:24.560 Luke Daque: Yeah.
936 03:29:25.260 ⇒ 03:29:32.930 Luke Daque: And this would be based on, I guess, for the orders. It’s the order completed.
937 03:29:33.530 ⇒ 03:29:34.490 Luke Daque: 8.
938 03:29:46.180 ⇒ 03:29:47.000 Uttam Kumaran: yeah.
939 03:29:47.270 ⇒ 03:29:48.040 Luke Daque: Okay.
940 03:29:50.760 ⇒ 03:29:52.169 Luke Daque: I’ll do that.
941 03:29:52.980 ⇒ 03:29:53.690 Uttam Kumaran: Okay.
942 03:29:55.280 ⇒ 03:30:01.610 Uttam Kumaran: cool. Alright. Do you want to? Just send me that Pr when we’re done, and then I think we’re probably good for today.
943 03:30:01.920 ⇒ 03:30:02.570 Luke Daque: Sure.
944 03:30:03.620 ⇒ 03:30:06.620 Uttam Kumaran: Okay, all right.
945 03:30:07.820 ⇒ 03:30:09.570 Uttam Kumaran: Sounds good. Thanks for that.
946 03:30:09.730 ⇒ 03:30:11.400 Luke Daque: I’ll talk to you soon. Bye, bye.
947 03:30:11.400 ⇒ 03:30:12.249 Uttam Kumaran: You too, bye.