Meeting Title: Project-Reviews Date: 2024-08-07 Meeting participants: Uttam, Patrick Trainer, Ryan Luke Daque, Nicolas Sucari
WEBVTT
1 00:00:24.140 ⇒ 00:00:25.019 Nicolas Sucari: Hey, Beth!
2 00:00:25.020 ⇒ 00:00:25.810 Patrick Trainer: Yo.
3 00:00:27.080 ⇒ 00:00:27.900 Nicolas Sucari: How are you?
4 00:00:28.100 ⇒ 00:00:28.790 Patrick Trainer: Doing good.
5 00:00:30.320 ⇒ 00:00:31.220 Patrick Trainer: How are you.
6 00:00:33.060 ⇒ 00:00:33.890 Nicolas Sucari: All good.
7 00:00:37.340 ⇒ 00:00:38.700 Patrick Trainer: Heard that.
8 00:01:34.230 ⇒ 00:01:35.100 Ryan Luke Daque: Hey, guys.
9 00:01:36.560 ⇒ 00:01:37.380 Patrick Trainer: A.
10 00:01:38.020 ⇒ 00:01:38.810 Nicolas Sucari: Hey, Ryan.
11 00:01:39.360 ⇒ 00:01:40.620 Ryan Luke Daque: How’s everyone?
12 00:01:41.120 ⇒ 00:01:41.959 Patrick Trainer: Doing, good.
13 00:01:47.110 ⇒ 00:01:47.930 Nicolas Sucari: Good.
14 00:02:42.870 ⇒ 00:02:44.550 Ryan Luke Daque: Hey, Pat, it’s the
15 00:02:44.960 ⇒ 00:02:46.040 Ryan Luke Daque: the
16 00:02:46.120 ⇒ 00:02:48.930 Ryan Luke Daque: email thing. Not yet ready? Right?
17 00:02:50.620 ⇒ 00:02:51.510 Patrick Trainer: It
18 00:02:53.240 ⇒ 00:02:54.530 Patrick Trainer: kind it will
19 00:02:54.880 ⇒ 00:02:56.539 Patrick Trainer: for the
20 00:02:56.800 ⇒ 00:02:58.360 Patrick Trainer: campaign data
21 00:02:58.760 ⇒ 00:03:01.680 Patrick Trainer: that actually should be ready.
22 00:03:02.190 ⇒ 00:03:03.160 Ryan Luke Daque: Oh, cool!
23 00:03:03.580 ⇒ 00:03:04.739 Ryan Luke Daque: Is that like
24 00:03:04.750 ⇒ 00:03:09.420 Ryan Luke Daque: something you can see in Snowflake already like, does it have a data.
25 00:03:09.640 ⇒ 00:03:10.409 Patrick Trainer: Yeah, I mean.
26 00:03:10.410 ⇒ 00:03:11.310 Ryan Luke Daque: Data actually.
27 00:03:11.310 ⇒ 00:03:17.340 Patrick Trainer: What we can like. If you look in in slack, I just post about it in internal engineering.
28 00:03:18.110 ⇒ 00:03:18.830 Ryan Luke Daque: Oh!
29 00:03:19.480 ⇒ 00:03:20.270 Ryan Luke Daque: Shake.
30 00:03:23.710 ⇒ 00:03:24.420 Patrick Trainer: Yeah.
31 00:03:24.700 ⇒ 00:03:25.900 Patrick Trainer: and so we.
32 00:03:25.900 ⇒ 00:03:26.760 Ryan Luke Daque: I just.
33 00:03:26.760 ⇒ 00:03:29.730 Patrick Trainer: Yeah, I’ll just create like the conversions table.
34 00:03:30.090 ⇒ 00:03:31.889 Patrick Trainer: So basically, what it’s doing
35 00:03:32.150 ⇒ 00:03:33.510 Patrick Trainer: is like,
36 00:03:34.230 ⇒ 00:03:36.489 Patrick Trainer: we’re running an action
37 00:03:36.550 ⇒ 00:03:39.010 Patrick Trainer: in Github that is.
38 00:03:39.100 ⇒ 00:03:40.899 Patrick Trainer: parsing from the
39 00:03:41.480 ⇒ 00:03:43.749 Patrick Trainer: the parsing, from Gmail.
40 00:03:44.100 ⇒ 00:03:45.130 Patrick Trainer: grabbing that
41 00:03:45.270 ⇒ 00:03:46.600 Patrick Trainer: downloading it.
42 00:03:47.531 ⇒ 00:03:49.520 Patrick Trainer: Pushing it up to S. 3.
43 00:03:49.880 ⇒ 00:03:55.960 Patrick Trainer: And then I’ve created an external stage and storage integration for S. 3
44 00:03:56.180 ⇒ 00:03:56.720 Patrick Trainer: I.
45 00:03:56.720 ⇒ 00:03:57.200 Ryan Luke Daque: Hello!
46 00:03:57.200 ⇒ 00:03:58.360 Patrick Trainer: And I wrote this
47 00:03:58.440 ⇒ 00:04:00.584 Patrick Trainer: stored procedure to
48 00:04:01.610 ⇒ 00:04:06.510 Patrick Trainer: grab the data down from the external stage
49 00:04:06.920 ⇒ 00:04:09.800 Patrick Trainer: and then load it into a table.
50 00:04:10.100 ⇒ 00:04:11.380 Patrick Trainer: and then
51 00:04:11.860 ⇒ 00:04:13.669 Patrick Trainer: from there
52 00:04:13.870 ⇒ 00:04:18.649 Patrick Trainer: create a task in Snowflake that just will run that stored procedure
53 00:04:18.750 ⇒ 00:04:20.269 Patrick Trainer: like on a
54 00:04:21.100 ⇒ 00:04:22.350 Patrick Trainer: some schedule.
55 00:04:22.560 ⇒ 00:04:24.549 Patrick Trainer: probably daily, or something like that.
56 00:04:25.000 ⇒ 00:04:26.049 Patrick Trainer: And then.
57 00:04:26.050 ⇒ 00:04:26.940 Nicolas Sucari: So, then
58 00:04:27.460 ⇒ 00:04:30.410 Nicolas Sucari: through through one github, action.
59 00:04:33.030 ⇒ 00:04:37.590 Patrick Trainer: Yeah, so well. So the Github, the Github action, kicks off like the
60 00:04:37.710 ⇒ 00:04:39.800 Patrick Trainer: the the library that.
61 00:04:40.150 ⇒ 00:04:40.810 Nicolas Sucari: Right.
62 00:04:40.810 ⇒ 00:04:45.450 Patrick Trainer: That Parses gmail, that’s it, and then and then
63 00:04:45.860 ⇒ 00:04:47.579 Patrick Trainer: beams it up to S. 3.
64 00:04:48.430 ⇒ 00:05:00.419 Patrick Trainer: The storage integration and everything that’s all in within snowflake and lives there and then there’s a stored procedure and a task that runs within snowflake
65 00:05:00.730 ⇒ 00:05:02.049 Patrick Trainer: to grab it from
66 00:05:03.260 ⇒ 00:05:07.000 Patrick Trainer: the storage integration or from from the external stage.
67 00:05:07.290 ⇒ 00:05:09.049 Patrick Trainer: which runs on a schedule
68 00:05:09.670 ⇒ 00:05:11.920 Patrick Trainer: which loads the data
69 00:05:12.410 ⇒ 00:05:14.500 Patrick Trainer: incrementally into.
70 00:05:14.500 ⇒ 00:05:15.160 Nicolas Sucari: Okay.
71 00:05:15.160 ⇒ 00:05:16.170 Patrick Trainer: Whatever table.
72 00:05:16.470 ⇒ 00:05:17.260 Patrick Trainer: and then.
73 00:05:17.260 ⇒ 00:05:18.060 Nicolas Sucari: So.
74 00:05:18.060 ⇒ 00:05:18.479 Patrick Trainer: And then we.
75 00:05:18.480 ⇒ 00:05:19.490 Nicolas Sucari: We need.
76 00:05:19.490 ⇒ 00:05:20.810 Patrick Trainer: The the table.
77 00:05:20.810 ⇒ 00:05:21.560 Nicolas Sucari: Okay.
78 00:05:21.560 ⇒ 00:05:22.949 Patrick Trainer: In Dbt from
79 00:05:24.800 ⇒ 00:05:26.809 Patrick Trainer: that as a source just like
80 00:05:26.840 ⇒ 00:05:27.989 Patrick Trainer: just like normal.
81 00:05:28.780 ⇒ 00:05:29.480 Ryan Luke Daque: Nice.
82 00:05:29.480 ⇒ 00:05:44.629 Nicolas Sucari: Do we like? Do we need to map which files should go to? Where? Which tables in snowflake? Or it’s like, if like, if we if we receive a new file that we’ve never received before, it’s gonna be in a new table.
83 00:05:44.940 ⇒ 00:05:47.073 Patrick Trainer: No, we’re gonna need to.
84 00:05:47.540 ⇒ 00:05:53.489 Patrick Trainer: probably create a new procedure for each table like doing doing that dynamically, for
85 00:05:54.360 ⇒ 00:05:57.110 Patrick Trainer: like just arbitrary files would be like
86 00:05:57.530 ⇒ 00:05:59.200 Patrick Trainer: incredibly difficult
87 00:06:00.347 ⇒ 00:06:04.110 Patrick Trainer: and and probably like super error prone.
88 00:06:04.360 ⇒ 00:06:06.819 Patrick Trainer: So we’re not going to do it like that.
89 00:06:07.390 ⇒ 00:06:10.179 Nicolas Sucari: Okay, so we need to specifically map
90 00:06:10.190 ⇒ 00:06:14.479 Nicolas Sucari: the name of the file. Probably that we are receiving on the email.
91 00:06:14.480 ⇒ 00:06:15.999 Patrick Trainer: The name of the file.
92 00:06:16.030 ⇒ 00:06:17.959 Patrick Trainer: because, like, we’re
93 00:06:20.940 ⇒ 00:06:24.950 Patrick Trainer: yeah, it’s not not necessarily the name of the file, but we’ll need to like
94 00:06:25.060 ⇒ 00:06:26.760 Patrick Trainer: map the columns and
95 00:06:26.920 ⇒ 00:06:28.509 Patrick Trainer: make sure that the
96 00:06:28.560 ⇒ 00:06:33.249 Patrick Trainer: stuff is in the right format and whatnot. I saw that one of them is
97 00:06:33.330 ⇒ 00:06:36.150 Patrick Trainer: coming in as like an excel file.
98 00:06:36.440 ⇒ 00:06:38.410 Patrick Trainer: And so we’re going to need to
99 00:06:39.040 ⇒ 00:06:39.920 Patrick Trainer: fuck with that.
100 00:06:39.920 ⇒ 00:06:40.310 Nicolas Sucari: Very good.
101 00:06:40.310 ⇒ 00:06:41.000 Patrick Trainer: Little bit
102 00:06:41.770 ⇒ 00:06:45.740 Patrick Trainer: because, like, we’ll need it as like a Csv which we can.
103 00:06:45.740 ⇒ 00:06:46.370 Nicolas Sucari: Okay.
104 00:06:46.370 ⇒ 00:06:47.360 Patrick Trainer: Handle that.
105 00:06:48.240 ⇒ 00:06:48.880 Ryan Luke Daque: Yeah.
106 00:06:48.880 ⇒ 00:06:50.870 Patrick Trainer: Some like somewhere.
107 00:06:52.440 ⇒ 00:06:56.030 Patrick Trainer: but like right now, like it’s
108 00:06:56.310 ⇒ 00:07:01.909 Patrick Trainer: I mean, everything’s incremental, right. Everything’s a like a step and a step.
109 00:07:02.050 ⇒ 00:07:02.756 Patrick Trainer: but like
110 00:07:03.700 ⇒ 00:07:04.200 Patrick Trainer: no, no.
111 00:07:04.200 ⇒ 00:07:07.100 Nicolas Sucari: So that’s perfect. I mean, I’m trying to understand. Like.
112 00:07:07.100 ⇒ 00:07:07.490 Patrick Trainer: Yeah.
113 00:07:07.490 ⇒ 00:07:16.199 Nicolas Sucari: If if the the service is great, I mean if you tried it out and it’s working, I think it’s what we need now. It’s like a little bit more of fine tuning of
114 00:07:16.410 ⇒ 00:07:24.969 Nicolas Sucari: how are the file we are? Gonna receive the files, what we need to do with them, where it’s where I’m gonna go. That data. If we need to create new tables each time or.
115 00:07:24.970 ⇒ 00:07:25.460 Ryan Luke Daque: It’s been.
116 00:07:25.460 ⇒ 00:07:28.519 Nicolas Sucari: To populate an existing table with the new data.
117 00:07:28.680 ⇒ 00:07:29.080 Patrick Trainer: Right.
118 00:07:29.080 ⇒ 00:07:30.370 Nicolas Sucari: And like all of those like.
119 00:07:30.370 ⇒ 00:07:36.770 Patrick Trainer: Yeah. So so the stored procedure, like, I can walk everyone through it, too. Like.
120 00:07:39.040 ⇒ 00:07:39.760 Patrick Trainer: is, is.
121 00:07:39.760 ⇒ 00:07:42.159 Ryan Luke Daque: Is there a ticket to that? By the way.
122 00:07:43.335 ⇒ 00:07:44.040 Patrick Trainer: Yeah.
123 00:07:44.550 ⇒ 00:07:46.789 Ryan Luke Daque: So I can like, maybe link the
124 00:07:47.110 ⇒ 00:07:49.469 Ryan Luke Daque: my other tickets like moving
125 00:07:50.030 ⇒ 00:07:54.240 Ryan Luke Daque: the data models, effective data models. Basically.
126 00:07:54.240 ⇒ 00:07:58.979 Nicolas Sucari: This is the pull request. But let me see, you have the one
127 00:07:59.430 ⇒ 00:08:02.520 Nicolas Sucari: that’s called tracking pipeline. Yeah.
128 00:08:02.520 ⇒ 00:08:04.100 Patrick Trainer: Yeah, 7, 90,
129 00:08:06.060 ⇒ 00:08:07.229 Ryan Luke Daque: 7 90, Gotcha.
130 00:08:07.230 ⇒ 00:08:09.159 Patrick Trainer: Okay? And so
131 00:08:11.570 ⇒ 00:08:13.910 Patrick Trainer: basically, what we’re doing
132 00:08:14.640 ⇒ 00:08:15.980 Patrick Trainer: is
133 00:08:16.280 ⇒ 00:08:18.200 Patrick Trainer: so we’ve got this procedure
134 00:08:18.900 ⇒ 00:08:21.550 Patrick Trainer: here. It’s called Sp
135 00:08:21.730 ⇒ 00:08:24.980 Patrick Trainer: stored Procedures, load incremental campaign data.
136 00:08:25.290 ⇒ 00:08:26.790 Patrick Trainer: And then we have the.
137 00:08:26.790 ⇒ 00:08:27.730 Ryan Luke Daque: And arguments.
138 00:08:28.190 ⇒ 00:08:30.779 Patrick Trainer: Target table schema, database.
139 00:08:30.880 ⇒ 00:08:32.930 Patrick Trainer: stage name, file format
140 00:08:34.358 ⇒ 00:08:38.399 Patrick Trainer: and so what we’re doing is coming through. We’re
141 00:08:39.159 ⇒ 00:08:40.910 Patrick Trainer: going to be.
142 00:08:41.010 ⇒ 00:08:42.699 Patrick Trainer: I saw udum for a second.
143 00:08:44.970 ⇒ 00:08:48.649 Patrick Trainer: Should I wait until he gets here to explain it, or just keep going.
144 00:08:49.750 ⇒ 00:08:50.290 Ryan Luke Daque: Oh!
145 00:08:50.290 ⇒ 00:08:55.860 Nicolas Sucari: That’s I think he’s he’s gonna be right here in any minute. So probably we can just.
146 00:08:56.010 ⇒ 00:08:56.820 Patrick Trainer: Okay.
147 00:08:59.380 ⇒ 00:09:00.869 Ryan Luke Daque: Oh, there you go!
148 00:09:00.870 ⇒ 00:09:04.439 Nicolas Sucari: Let let me ask him. Let me ask him if he’s gonna be.
149 00:09:04.440 ⇒ 00:09:05.400 Ryan Luke Daque: Facility, here.
150 00:09:05.400 ⇒ 00:09:05.730 Uttam: Oh, yeah.
151 00:09:05.730 ⇒ 00:09:06.090 Nicolas Sucari: Out!
152 00:09:06.090 ⇒ 00:09:06.510 Uttam: Transfer.
153 00:09:06.510 ⇒ 00:09:07.719 Nicolas Sucari: He’s here. Yeah.
154 00:09:07.880 ⇒ 00:09:08.330 Patrick Trainer: Oh, yeah.
155 00:09:09.050 ⇒ 00:09:12.509 Patrick Trainer: what I’m doing is just explaining, kind of like end to end
156 00:09:12.980 ⇒ 00:09:14.759 Patrick Trainer: how this pipeline works.
157 00:09:15.080 ⇒ 00:09:18.079 Uttam: Cool. Yeah, I read through the pr, briefly. So, yeah.
158 00:09:18.080 ⇒ 00:09:21.519 Patrick Trainer: Yeah. So just for like a recap.
159 00:09:22.680 ⇒ 00:09:25.519 Patrick Trainer: a Github action kicks off.
160 00:09:25.760 ⇒ 00:09:31.190 Patrick Trainer: it parses a Gmail inbox looking for Csv attachments.
161 00:09:31.910 ⇒ 00:09:35.939 Patrick Trainer: It grabs those attachments downloads them.
162 00:09:36.300 ⇒ 00:09:40.079 Patrick Trainer: It saves them as one github artifact
163 00:09:40.557 ⇒ 00:09:50.379 Patrick Trainer: but also like, just so we can check it quickly. But then also it shoots them up to S. 3 stores them in that bucket.
164 00:09:50.790 ⇒ 00:09:53.619 Patrick Trainer: There’s a storage integration
165 00:09:53.670 ⇒ 00:10:03.069 Patrick Trainer: between Snowflake and S. 3 now, and we have an external stage for that that lives in our raw 5 trend database.
166 00:10:03.884 ⇒ 00:10:06.730 Patrick Trainer: So that’s where that stage is.
167 00:10:07.430 ⇒ 00:10:10.750 Patrick Trainer: And then I wrote this stored procedure
168 00:10:10.870 ⇒ 00:10:15.290 Patrick Trainer: that runs on a schedule that
169 00:10:15.640 ⇒ 00:10:17.980 Patrick Trainer: hits the external stage
170 00:10:18.971 ⇒ 00:10:27.279 Patrick Trainer: and then it loads that into a table and that runs on a schedule and will update incrementally.
171 00:10:28.260 ⇒ 00:10:32.369 Patrick Trainer: And so I’m just walking everyone through the stored procedure.
172 00:10:32.510 ⇒ 00:10:40.700 Patrick Trainer: And so we’ve got this load incremental campaign data. Actually, it’s probably easier to see it here.
173 00:10:41.733 ⇒ 00:10:43.450 Patrick Trainer: Can you all see this.
174 00:10:43.840 ⇒ 00:10:44.540 Patrick Trainer: Yeah.
175 00:10:45.440 ⇒ 00:10:47.700 Patrick Trainer: okay, cool. So we’ve got
176 00:10:47.810 ⇒ 00:10:48.694 Patrick Trainer: this
177 00:10:51.210 ⇒ 00:10:59.610 Patrick Trainer: stored Proc here. It has. These arguments takes a target table schema database.
178 00:10:59.920 ⇒ 00:11:03.289 Patrick Trainer: We’ve got our stage. And then we’ve got our file format.
179 00:11:03.760 ⇒ 00:11:06.940 Patrick Trainer: And so this is just written in Javascript.
180 00:11:08.140 ⇒ 00:11:09.060 Patrick Trainer: What it’s
181 00:11:09.170 ⇒ 00:11:12.030 Patrick Trainer: doing. It creates a table
182 00:11:12.560 ⇒ 00:11:19.849 Patrick Trainer: if it doesn’t exist just to like if we need to create like a new one or a test, one which you saw, that I just did
183 00:11:20.180 ⇒ 00:11:23.750 Patrick Trainer: defines the columns for everything.
184 00:11:24.896 ⇒ 00:11:25.753 Patrick Trainer: And
185 00:11:27.966 ⇒ 00:11:30.390 Patrick Trainer: does all that executes
186 00:11:31.070 ⇒ 00:11:31.920 Patrick Trainer: this
187 00:11:32.060 ⇒ 00:11:33.000 Patrick Trainer: statement.
188 00:11:33.480 ⇒ 00:11:35.740 Patrick Trainer: and then we create a temporary table
189 00:11:36.596 ⇒ 00:11:42.919 Patrick Trainer: that had like, actually calls the parameters in the the database and schema.
190 00:11:43.400 ⇒ 00:11:44.780 Patrick Trainer: and it grabs
191 00:11:44.850 ⇒ 00:11:46.240 Patrick Trainer: the values
192 00:11:46.260 ⇒ 00:11:48.309 Patrick Trainer: from the stage.
193 00:11:48.450 ⇒ 00:11:52.360 Patrick Trainer: So like the postcard, id coupon code, etc, etc.
194 00:11:52.711 ⇒ 00:12:06.010 Patrick Trainer: And then it also needed. We needed to do some like conversion on the date fields as well as the the total price field, because it was coming in with like a dollar sign and commas and
195 00:12:06.290 ⇒ 00:12:07.829 Patrick Trainer: shit like that. That
196 00:12:08.170 ⇒ 00:12:11.410 Patrick Trainer: wouldn’t come in as a actual value
197 00:12:12.150 ⇒ 00:12:18.880 Patrick Trainer: as well as like the date that was like a string. And so we’re converting it to an actual date field
198 00:12:19.722 ⇒ 00:12:23.827 Patrick Trainer: and then it all this table also didn’t have a unique id
199 00:12:24.270 ⇒ 00:12:26.339 Patrick Trainer: And so we just did.
200 00:12:26.900 ⇒ 00:12:30.129 Patrick Trainer: I didn’t. Md. 5 concatenate
201 00:12:30.230 ⇒ 00:12:32.799 Patrick Trainer: hash of all of the columns.
202 00:12:33.348 ⇒ 00:12:35.319 Patrick Trainer: Just to get a unique id
203 00:12:35.853 ⇒ 00:12:41.100 Patrick Trainer: and then added a current timestamp just to show when we loaded it.
204 00:12:41.220 ⇒ 00:12:45.559 Patrick Trainer: and it selects from that stage name and then uses
205 00:12:46.722 ⇒ 00:12:48.530 Patrick Trainer: Csv file format that.
206 00:12:48.650 ⇒ 00:12:50.229 Patrick Trainer: defined in snowflake
207 00:12:50.630 ⇒ 00:12:52.790 Patrick Trainer: goes executes that
208 00:12:53.853 ⇒ 00:12:59.620 Patrick Trainer: and then we insert into our target table that we created up here
209 00:13:00.050 ⇒ 00:13:04.860 Patrick Trainer: and selects it down from our temporary database
210 00:13:05.494 ⇒ 00:13:13.220 Patrick Trainer: and joins on the unique Id, just so that we always are just are. We’re only creating
211 00:13:13.390 ⇒ 00:13:17.369 Patrick Trainer: new rows and not duplicating anything.
212 00:13:18.180 ⇒ 00:13:19.650 Patrick Trainer: and then
213 00:13:19.680 ⇒ 00:13:20.970 Patrick Trainer: executes that.
214 00:13:21.170 ⇒ 00:13:31.990 Patrick Trainer: And then we get like a little like count of how many rows we inserted, and then it prints out this little nice message of where it was done.
215 00:13:32.300 ⇒ 00:13:35.919 Patrick Trainer: And so what this looks like in Snowflake
216 00:13:36.290 ⇒ 00:13:38.299 Patrick Trainer: is like I
217 00:13:38.900 ⇒ 00:13:39.800 Patrick Trainer: called it
218 00:13:40.210 ⇒ 00:13:41.369 Patrick Trainer: over here.
219 00:13:41.980 ⇒ 00:13:43.369 Patrick Trainer: and so
220 00:13:44.120 ⇒ 00:13:46.470 Patrick Trainer: like this is just the sequel for it.
221 00:13:47.060 ⇒ 00:13:50.269 Patrick Trainer: And like this is how you call
222 00:13:51.140 ⇒ 00:13:52.950 Patrick Trainer: the procedure
223 00:13:53.830 ⇒ 00:13:57.570 Patrick Trainer: is just like call stored. Proc. This.
224 00:13:57.820 ⇒ 00:13:59.939 Patrick Trainer: We had our our table
225 00:14:00.450 ⇒ 00:14:01.380 Patrick Trainer: schema
226 00:14:01.880 ⇒ 00:14:03.680 Patrick Trainer: database. We’re going to
227 00:14:04.170 ⇒ 00:14:05.460 Patrick Trainer: our stage.
228 00:14:05.470 ⇒ 00:14:06.959 Patrick Trainer: which is like here
229 00:14:07.150 ⇒ 00:14:10.599 Patrick Trainer: as well as our file format that we’ve created.
230 00:14:10.980 ⇒ 00:14:14.370 Patrick Trainer: And then that runs and then
231 00:14:14.550 ⇒ 00:14:15.710 Patrick Trainer: creates
232 00:14:16.260 ⇒ 00:14:17.170 Patrick Trainer: our
233 00:14:17.660 ⇒ 00:14:18.630 Patrick Trainer: table.
234 00:14:19.470 ⇒ 00:14:20.650 Patrick Trainer: which
235 00:14:20.720 ⇒ 00:14:21.730 Patrick Trainer: looks like
236 00:14:22.580 ⇒ 00:14:23.390 Patrick Trainer: this.
237 00:14:23.850 ⇒ 00:14:25.199 Patrick Trainer: And so we can.
238 00:14:25.560 ⇒ 00:14:28.480 Patrick Trainer: We have all of our data that comes from
239 00:14:28.500 ⇒ 00:14:29.960 Patrick Trainer: full parts. Whatnot.
240 00:14:30.300 ⇒ 00:14:31.999 Patrick Trainer: there’s our unique Id.
241 00:14:32.160 ⇒ 00:14:34.729 Patrick Trainer: And then it shows like the time that
242 00:14:35.050 ⇒ 00:14:37.509 Patrick Trainer: this data was loaded which was
243 00:14:38.480 ⇒ 00:14:40.950 Patrick Trainer: just now, or a couple of minutes ago.
244 00:14:41.430 ⇒ 00:14:44.659 Patrick Trainer: And so if we want to like, run this again.
245 00:14:45.500 ⇒ 00:14:48.050 Patrick Trainer: but actually just call the table Conversions.
246 00:14:48.180 ⇒ 00:14:50.550 Patrick Trainer: we will call that procedure.
247 00:14:51.380 ⇒ 00:14:54.220 Patrick Trainer: That’ll run. That’ll do all its things.
248 00:14:55.030 ⇒ 00:14:57.130 Patrick Trainer: I don’t know why it says 4 new rows.
249 00:15:00.000 ⇒ 00:15:01.770 Patrick Trainer: That doesn’t really matter.
250 00:15:03.290 ⇒ 00:15:05.360 Patrick Trainer: Then we see we have our table
251 00:15:06.840 ⇒ 00:15:08.689 Patrick Trainer: and we are. Boom.
252 00:15:11.040 ⇒ 00:15:14.230 Patrick Trainer: and it is that’s done.
253 00:15:14.280 ⇒ 00:15:18.839 Patrick Trainer: And then from Dbt, we can just call on this table
254 00:15:19.090 ⇒ 00:15:20.320 Patrick Trainer: and
255 00:15:20.640 ⇒ 00:15:24.580 Patrick Trainer: distort procedure will continue to update this. Let’s see if
256 00:15:24.700 ⇒ 00:15:27.739 Patrick Trainer: we run it again, if it’ll if it’ll do anything.
257 00:15:31.230 ⇒ 00:15:34.039 Patrick Trainer: I don’t know why it’s still doing that. But
258 00:15:34.190 ⇒ 00:15:37.519 Patrick Trainer: but you see the table, the the rows haven’t changed.
259 00:15:37.770 ⇒ 00:15:40.190 Patrick Trainer: So like we got conversions.
260 00:15:40.320 ⇒ 00:15:42.240 Patrick Trainer: Here, let’s do
261 00:15:43.714 ⇒ 00:15:44.649 Patrick Trainer: select
262 00:15:44.690 ⇒ 00:15:46.159 Patrick Trainer: counts one
263 00:15:47.160 ⇒ 00:15:47.980 Patrick Trainer: from
264 00:15:48.360 ⇒ 00:15:49.870 Patrick Trainer: conversions.
265 00:15:51.820 ⇒ 00:15:54.309 Patrick Trainer: We’ve got 1478.
266 00:15:55.150 ⇒ 00:15:56.800 Patrick Trainer: We’ll run that again.
267 00:15:59.910 ⇒ 00:16:02.939 Patrick Trainer: That’s that’s a pain in the ass. That’s that’s annoying me.
268 00:16:04.280 ⇒ 00:16:05.879 Patrick Trainer: And then we got
269 00:16:08.340 ⇒ 00:16:09.190 Patrick Trainer: 14.
270 00:16:10.230 ⇒ 00:16:12.429 Ryan Luke Daque: It looks like it did add 4.
271 00:16:13.980 ⇒ 00:16:15.060 Patrick Trainer: What the fuck?
272 00:16:20.040 ⇒ 00:16:21.380 Patrick Trainer: What is it? Adding.
273 00:16:24.690 ⇒ 00:16:25.580 Ryan Luke Daque: Probably
274 00:16:26.730 ⇒ 00:16:28.275 Ryan Luke Daque: adding the last
275 00:16:28.820 ⇒ 00:16:29.395 Ryan Luke Daque: or.
276 00:16:31.150 ⇒ 00:16:33.390 Patrick Trainer: 1486. What is it?
277 00:16:34.720 ⇒ 00:16:35.420 Patrick Trainer: But
278 00:16:35.540 ⇒ 00:16:41.009 Patrick Trainer: well, I’ll figure that out. I don’t. I don’t know why it’s doing that.
279 00:16:44.000 ⇒ 00:16:45.649 Patrick Trainer: but yeah, that’s how it works.
280 00:16:46.580 ⇒ 00:16:48.837 Uttam: I guess my one of my questions is,
281 00:16:49.290 ⇒ 00:16:58.629 Uttam: I just get nervous when there’s business logic here, and then there’s also stuff we’re doing in Cbt. Can we centralize some of the conversions and stuff in
282 00:16:58.830 ⇒ 00:17:00.210 Uttam: Dbt.
283 00:17:00.570 ⇒ 00:17:05.069 Uttam: like instead, like some of the business logic like where we’re transforming
284 00:17:06.536 ⇒ 00:17:08.289 Uttam: dates, or.
285 00:17:08.400 ⇒ 00:17:10.590 Uttam: you know, doing any sort of transformation.
286 00:17:12.560 ⇒ 00:17:15.189 Patrick Trainer: Do you mean like where we’re
287 00:17:15.700 ⇒ 00:17:17.020 Patrick Trainer: getting here?
288 00:17:19.235 ⇒ 00:17:19.920 Uttam: Yes!
289 00:17:21.380 ⇒ 00:17:24.589 Nicolas Sucari: You. You need that to create the table, or you can do it later.
290 00:17:25.250 ⇒ 00:17:27.989 Patrick Trainer: Yeah, yeah, like, we’d need to do this
291 00:17:28.000 ⇒ 00:17:31.090 Patrick Trainer: like later or or well, like now.
292 00:17:34.730 ⇒ 00:17:40.859 Uttam: Because why I don’t like. Why can’t we just bring it in as is, and then handle all the transformations
293 00:17:40.990 ⇒ 00:17:42.160 Uttam: in Dbt.
294 00:17:43.510 ⇒ 00:17:45.650 Patrick Trainer: I guess we can do that. Yeah, we could.
295 00:17:45.650 ⇒ 00:17:46.799 Uttam: You know like why.
296 00:17:46.993 ⇒ 00:17:50.670 Patrick Trainer: Yeah, I can give that a shot, and then we can just handle that in Dbt, if we want.
297 00:17:51.500 ⇒ 00:17:54.959 Uttam: Yeah, I just, I mean, the only reason is so that
298 00:17:55.290 ⇒ 00:17:58.740 Uttam: again, there’s just going to be a bifurcation of logic.
299 00:17:58.880 ⇒ 00:18:04.719 Uttam: So ideally, I think if we just bring in all this, I think anything where it’s like.
300 00:18:04.720 ⇒ 00:18:05.989 Patrick Trainer: I’m fine. I’m fine with that.
301 00:18:06.240 ⇒ 00:18:10.919 Uttam: Yeah. And if we need to like using snowpipe and everything just to shove everything in some area
302 00:18:11.090 ⇒ 00:18:12.460 Uttam: and then
303 00:18:12.470 ⇒ 00:18:14.989 Uttam: handling this in Dbt, I think, yeah.
304 00:18:14.990 ⇒ 00:18:18.000 Patrick Trainer: Yeah, that’s fine. I will have to
305 00:18:18.820 ⇒ 00:18:19.950 Patrick Trainer: have this
306 00:18:21.170 ⇒ 00:18:22.529 Patrick Trainer: unique id.
307 00:18:22.830 ⇒ 00:18:28.480 Uttam: Sure. Yeah, that sort of that. Those sorts of constraints make a make sense.
308 00:18:28.965 ⇒ 00:18:33.800 Uttam: I just get nervous when I see like the cause I just I’m like
309 00:18:33.920 ⇒ 00:18:37.730 Uttam: an erroneous file is gonna come in and it’s gonna change here. And then.
310 00:18:38.030 ⇒ 00:18:38.420 Patrick Trainer: Right.
311 00:18:38.420 ⇒ 00:18:40.279 Uttam: Like the Dbt dev
312 00:18:40.480 ⇒ 00:18:44.700 Uttam: to debug it. The logic is going to be somewhere else. So that’s where I get nervous. So.
313 00:18:45.090 ⇒ 00:18:45.750 Patrick Trainer: Right.
314 00:18:47.600 ⇒ 00:18:48.270 Patrick Trainer: cool.
315 00:18:48.270 ⇒ 00:18:55.650 Uttam: But otherwise it’s great. Yeah, I mean, the unique Id constraints is great. And then I mean, we ended up with a bunch more documentation. So that’s also awesome.
316 00:18:56.030 ⇒ 00:18:56.810 Patrick Trainer: Right.
317 00:18:56.910 ⇒ 00:18:59.289 Patrick Trainer: And I tested this like
318 00:19:00.600 ⇒ 00:19:02.809 Patrick Trainer: from end to end to like I just
319 00:19:03.490 ⇒ 00:19:04.509 Patrick Trainer: that’s not it.
320 00:19:08.320 ⇒ 00:19:11.079 Ryan Luke Daque: And which email is this? Looking at.
321 00:19:12.480 ⇒ 00:19:14.919 Nicolas Sucari: In it’s
322 00:19:15.580 ⇒ 00:19:17.219 Nicolas Sucari: so like a new email.
323 00:19:17.960 ⇒ 00:19:24.349 Patrick Trainer: This is the the one that comes from here. This.
324 00:19:24.350 ⇒ 00:19:25.339 Ryan Luke Daque: I see.
325 00:19:28.940 ⇒ 00:19:37.580 Patrick Trainer: And so the other. The other part, too. Udum, is that these emails aren’t being forwarded
326 00:19:37.740 ⇒ 00:19:39.030 Patrick Trainer: to that
327 00:19:40.890 ⇒ 00:19:42.960 Patrick Trainer: email address anymore.
328 00:19:44.020 ⇒ 00:19:45.009 Uttam: Oh, okay.
329 00:19:45.010 ⇒ 00:19:46.800 Patrick Trainer: Yeah, so we just need to do that.
330 00:19:47.710 ⇒ 00:19:51.480 Uttam: Yeah, there’s just like, probably 5 or 6 of these. So if we could just make
331 00:19:51.530 ⇒ 00:19:58.179 Uttam: a tracking ticket for all of them, and I think. Ryan asked, if you could do that just like, what are the emails? Where are they coming from?
332 00:19:58.540 ⇒ 00:20:01.990 Uttam: And then, like that way, we can just check all these off, because there’s probably like
333 00:20:02.210 ⇒ 00:20:07.759 Uttam: 5 or 6, and I’m I’m just no longer to able to recite them. I’m I’m really.
334 00:20:07.760 ⇒ 00:20:10.859 Ryan Luke Daque: Sure. Yeah, I’ll do that. I’ll link it to the
335 00:20:11.600 ⇒ 00:20:12.790 Ryan Luke Daque: to the ticket that
336 00:20:12.930 ⇒ 00:20:14.520 Ryan Luke Daque: this one, this ticket.
337 00:20:14.520 ⇒ 00:20:16.628 Patrick Trainer: Cool, and I also have, like
338 00:20:17.070 ⇒ 00:20:19.940 Patrick Trainer: a tracking ticket
339 00:20:21.680 ⇒ 00:20:22.690 Patrick Trainer: somewhere.
340 00:20:22.690 ⇒ 00:20:23.270 Nicolas Sucari: I mean pull.
341 00:20:23.270 ⇒ 00:20:24.240 Patrick Trainer: Yeah, 18.
342 00:20:24.240 ⇒ 00:20:25.299 Nicolas Sucari: Full Puerto Rico.
343 00:20:25.730 ⇒ 00:20:26.400 Patrick Trainer: Yeah.
344 00:20:27.140 ⇒ 00:20:28.930 Patrick Trainer: where? There we go.
345 00:20:29.140 ⇒ 00:20:29.720 Nicolas Sucari: Add one.
346 00:20:31.230 ⇒ 00:20:34.360 Patrick Trainer: Actually, I don’t think it’s in the pool parts, rep, though I think it’s in the
347 00:20:34.600 ⇒ 00:20:35.480 Patrick Trainer: Gmad.
348 00:20:37.870 ⇒ 00:20:40.550 Nicolas Sucari: Oh, yeah, in June.
349 00:20:41.200 ⇒ 00:20:42.329 Nicolas Sucari: Make that done.
350 00:20:42.820 ⇒ 00:20:45.259 Patrick Trainer: Yeah. So here, I’ll add this to.
351 00:20:48.080 ⇒ 00:20:49.779 Ryan Luke Daque: Yeah. And I think there’s
352 00:20:50.110 ⇒ 00:20:52.709 Ryan Luke Daque: there’s still like 2 more
353 00:20:54.430 ⇒ 00:21:00.469 Ryan Luke Daque: stuff manual stuff that I haven’t really like. I think it’s being sent to your email utam.
354 00:21:00.950 ⇒ 00:21:06.299 Ryan Luke Daque: So I guess the like the clay view and Clavio and the attentive ones.
355 00:21:07.750 ⇒ 00:21:08.540 Ryan Luke Daque: So
356 00:21:08.910 ⇒ 00:21:10.490 Ryan Luke Daque: yeah, maybe we can
357 00:21:10.610 ⇒ 00:21:12.170 Ryan Luke Daque: forward those as well.
358 00:21:13.690 ⇒ 00:21:16.789 Uttam: Yeah, I I mean, I got one of the Klavia ones.
359 00:21:17.520 ⇒ 00:21:19.960 Uttam: So I’ll just forward.
360 00:21:20.500 ⇒ 00:21:24.849 Uttam: I’ll just forward one to you now. But yeah, just make. Just if you can make that list.
361 00:21:25.290 ⇒ 00:21:25.770 Ryan Luke Daque: Right, right.
362 00:21:25.770 ⇒ 00:21:29.300 Uttam: You know, and I’ll just make sure that there’s forwarding rules for all these.
363 00:21:29.980 ⇒ 00:21:30.810 Ryan Luke Daque: Cool.
364 00:21:32.180 ⇒ 00:21:37.039 Nicolas Sucari: And what we were just discussing before you joined with them is that we will need to create.
365 00:21:37.190 ⇒ 00:21:42.959 Nicolas Sucari: like the service for each of the different files or tables that we want to create right.
366 00:21:43.140 ⇒ 00:21:43.800 Nicolas Sucari: But.
367 00:21:44.620 ⇒ 00:21:45.230 Patrick Trainer: Yeah.
368 00:21:46.510 ⇒ 00:21:50.580 Uttam: So it’s, and how’s the S 3 structure like it’s all going to individual folders.
369 00:21:51.333 ⇒ 00:21:54.436 Patrick Trainer: I mean, we can do that. Let’s
370 00:21:55.780 ⇒ 00:21:57.020 Patrick Trainer: actually do that
371 00:22:00.110 ⇒ 00:22:01.030 Patrick Trainer: sign in.
372 00:22:21.760 ⇒ 00:22:25.669 Patrick Trainer: So we have this bucket brain forge email artifacts.
373 00:22:27.050 ⇒ 00:22:29.930 Patrick Trainer: And yeah, right now, it’s just coming in.
374 00:22:29.940 ⇒ 00:22:31.310 Patrick Trainer: I need to delete
375 00:22:31.790 ⇒ 00:22:34.320 Patrick Trainer: these because those keep coming in, too.
376 00:22:37.000 ⇒ 00:22:37.580 Patrick Trainer: But yeah.
377 00:22:37.580 ⇒ 00:22:41.729 Uttam: Yeah, maybe one folder, cause you can just do one folder per
378 00:22:42.040 ⇒ 00:22:50.539 Uttam: email. And then the storage integration is actually at the root level. So you can actually just do slashes. And you don’t have to create another storage integration.
379 00:22:50.880 ⇒ 00:22:53.609 Patrick Trainer: Right? Right? Yeah, I don’t need to create that. What we would.
380 00:22:53.610 ⇒ 00:22:54.160 Uttam: Okay. Cool.
381 00:22:54.160 ⇒ 00:23:04.100 Patrick Trainer: What we will need to do is just because, like storage integrations have to be explicit. And so let’s
382 00:23:04.510 ⇒ 00:23:06.180 Patrick Trainer: the worksheets.
383 00:23:06.410 ⇒ 00:23:07.560 Patrick Trainer: snippets.
384 00:23:09.620 ⇒ 00:23:11.060 Patrick Trainer: storage integration.
385 00:23:14.570 ⇒ 00:23:17.289 Patrick Trainer: we will have to where to go, where to go.
386 00:23:18.140 ⇒ 00:23:20.319 Patrick Trainer: Okay, so we have like this stage.
387 00:23:20.590 ⇒ 00:23:24.709 Patrick Trainer: URL, and that’s where it’s at. And
388 00:23:25.310 ⇒ 00:23:26.429 Patrick Trainer: we’ve got.
389 00:23:29.200 ⇒ 00:23:31.909 Patrick Trainer: Oh, yeah. So we just need to like.
390 00:23:33.470 ⇒ 00:23:34.680 Patrick Trainer: we’ll just add
391 00:23:34.730 ⇒ 00:23:36.820 Patrick Trainer: it to this URL
392 00:23:37.330 ⇒ 00:23:41.219 Patrick Trainer: of, because, oh, it has these allowed locations. And so like.
393 00:23:41.830 ⇒ 00:23:43.479 Patrick Trainer: we just add another
394 00:23:45.110 ⇒ 00:23:47.830 Patrick Trainer: locate or like file path. Basically.
395 00:23:48.260 ⇒ 00:23:50.199 Patrick Trainer: Okay. But that’s super simple.
396 00:23:50.260 ⇒ 00:23:51.909 Patrick Trainer: It’s just a replacement.
397 00:23:56.480 ⇒ 00:24:00.029 Patrick Trainer: And we could do that, too. Like with
398 00:24:00.210 ⇒ 00:24:01.810 Patrick Trainer: if we wanted to
399 00:24:02.380 ⇒ 00:24:06.870 Patrick Trainer: like, subjugate this by or by, forget this by like
400 00:24:08.010 ⇒ 00:24:09.070 Patrick Trainer: to go
401 00:24:09.110 ⇒ 00:24:12.010 Patrick Trainer: or like, have another one, for
402 00:24:12.220 ⇒ 00:24:16.119 Patrick Trainer: like we can have different file paths, for, like Stella.
403 00:24:16.530 ⇒ 00:24:18.670 Patrick Trainer: And then we can just
404 00:24:18.710 ⇒ 00:24:22.499 Patrick Trainer: have different stages based on that, and then pull, pull down
405 00:24:22.600 ⇒ 00:24:24.050 Patrick Trainer: all using the
406 00:24:24.060 ⇒ 00:24:29.120 Patrick Trainer: the same integration and bucket. So yeah, we can just add
407 00:24:29.370 ⇒ 00:24:32.120 Patrick Trainer: other paths here just on another level
408 00:24:32.670 ⇒ 00:24:35.770 Patrick Trainer: and have everything live in these email or fax.
409 00:24:39.240 ⇒ 00:24:41.260 Uttam: Okay, cool. So yeah, I think the
410 00:24:41.380 ⇒ 00:24:46.640 Uttam: I mean again, I think, having Ryan test out like how we can manage the process, I think
411 00:24:46.790 ⇒ 00:24:57.484 Uttam: is basically the next step. And then, yeah, basically leveraging Dbt for the transforms. And then just querying, just using the store procedure for
412 00:24:59.110 ⇒ 00:25:06.769 Uttam: I guess. Yeah, I guess you have to tell me what the store procedure which components we’re going to use it for, and just for the primary id creation.
413 00:25:06.820 ⇒ 00:25:08.210 Uttam: And then the
414 00:25:09.026 ⇒ 00:25:10.574 Uttam: like incremental load
415 00:25:11.090 ⇒ 00:25:22.000 Patrick Trainer: So like, we’ll need to create like a stored procedure. For like each different file, because we’re needing to define like the the table schema.
416 00:25:22.290 ⇒ 00:25:22.730 Uttam: That’s fine!
417 00:25:22.730 ⇒ 00:25:24.869 Patrick Trainer: And that we’re loading into
418 00:25:24.980 ⇒ 00:25:29.959 Patrick Trainer: yeah. And and we’ll also need to create like these file formats
419 00:25:30.130 ⇒ 00:25:30.860 Patrick Trainer: off.
420 00:25:30.860 ⇒ 00:25:31.430 Uttam: Sure.
421 00:25:31.430 ⇒ 00:25:32.650 Patrick Trainer: Depending on
422 00:25:33.650 ⇒ 00:25:41.009 Patrick Trainer: what’s being sent. I don’t think Snowflake can natively load excel files, so.
423 00:25:41.190 ⇒ 00:25:45.320 Uttam: No, yeah, it’s Csv’s. I think it could do. But.
424 00:25:45.320 ⇒ 00:25:50.049 Patrick Trainer: Oh, yeah. Yeah. Csv’s is what we’re loading now. It can also load like Avro and
425 00:25:50.780 ⇒ 00:25:51.473 Uttam: Okay. Yeah.
426 00:25:51.820 ⇒ 00:25:53.240 Patrick Trainer: Orc and parquet.
427 00:25:54.036 ⇒ 00:25:54.713 Patrick Trainer: But
428 00:25:56.420 ⇒ 00:25:58.019 Patrick Trainer: We’ll need to do some
429 00:25:58.600 ⇒ 00:26:08.110 Patrick Trainer: something else to to move from. Well, not like we’ll need to do some transformation on the Excel file to just mash it into a Csv.
430 00:26:08.720 ⇒ 00:26:09.380 Uttam: Cool.
431 00:26:09.380 ⇒ 00:26:10.579 Patrick Trainer: Which isn’t like
432 00:26:11.200 ⇒ 00:26:15.430 Patrick Trainer: super difficult. That’s just something we’ll need to figure out.
433 00:26:16.180 ⇒ 00:26:16.860 Uttam: Okay.
434 00:26:16.860 ⇒ 00:26:18.240 Patrick Trainer: But at least for
435 00:26:19.050 ⇒ 00:26:21.079 Patrick Trainer: this file, we’re like.
436 00:26:21.720 ⇒ 00:26:25.740 Patrick Trainer: like, end to end. It works like we’re getting data into Snowflake
437 00:26:27.050 ⇒ 00:26:27.930 Patrick Trainer: from
438 00:26:28.690 ⇒ 00:26:30.350 Patrick Trainer: nothing cool.
439 00:26:30.350 ⇒ 00:26:31.120 Uttam: Yeah.
440 00:26:31.730 ⇒ 00:26:38.590 Uttam: no, yeah. I think we’re. We’re literally at the last stage. And then, ideally, I think the big Pr that needs to come out is
441 00:26:38.870 ⇒ 00:26:42.290 Uttam: basically putting the each of the stored procedure code
442 00:26:42.380 ⇒ 00:26:45.689 Uttam: that was run in this, probably in the same folder as
443 00:26:47.050 ⇒ 00:26:49.939 Uttam: The trans, the initial raw transformation
444 00:26:51.710 ⇒ 00:26:52.470 Uttam: right? And.
445 00:26:52.470 ⇒ 00:26:55.949 Patrick Trainer: Yeah, I created. I created like a new file structure
446 00:26:56.440 ⇒ 00:26:58.279 Patrick Trainer: like that, just like in
447 00:26:58.470 ⇒ 00:27:02.689 Patrick Trainer: Oh, shit! Did I put it in the Dock Github Directory. Not bad.
448 00:27:05.550 ⇒ 00:27:09.920 Uttam: Either way as long as it’s somewhere. And then basically, yeah.
449 00:27:09.920 ⇒ 00:27:11.100 Patrick Trainer: That to
450 00:27:11.210 ⇒ 00:27:11.900 Patrick Trainer: I’d
451 00:27:12.290 ⇒ 00:27:13.970 Patrick Trainer: into the root level.
452 00:27:14.020 ⇒ 00:27:15.260 Patrick Trainer: I didn’t mean to do that.
453 00:27:16.450 ⇒ 00:27:17.650 Uttam: Yeah.
454 00:27:17.650 ⇒ 00:27:19.809 Patrick Trainer: Just so that we have that, and then we can.
455 00:27:19.810 ⇒ 00:27:20.760 Uttam: Exactly.
456 00:27:21.290 ⇒ 00:27:23.089 Patrick Trainer: Deploy it, however.
457 00:27:23.680 ⇒ 00:27:29.679 Uttam: Yeah. And then I think, Ryan, some of this I don’t know if you’ve had experience working with file formats or
458 00:27:29.790 ⇒ 00:27:33.309 Uttam: store procs or this. So I want, I kind of don’t wanna
459 00:27:33.520 ⇒ 00:27:46.820 Uttam: intentionally give you all the answers that we can find a way that the you can work within the process. So basically, it’s like we, the email goes into the to the to the service. Like, the
460 00:27:46.860 ⇒ 00:27:53.590 Uttam: the files get processed, they get created in s. 3. And then finally, what happens is the
461 00:27:53.750 ⇒ 00:27:59.730 Uttam: store procedure, like the store procedure, runs to move in new files on the task.
462 00:28:00.333 ⇒ 00:28:01.360 Uttam: And then
463 00:28:01.500 ⇒ 00:28:05.320 Uttam: ideally, what happens is in Dbt, there is a select
464 00:28:06.130 ⇒ 00:28:13.409 Uttam: star. Select columns where that’ll just be the 1st layer. So your engagement basically happens
465 00:28:13.960 ⇒ 00:28:17.199 Uttam: as the data is available, and then you have, like a
466 00:28:17.698 ⇒ 00:28:19.540 Uttam: stage to pull up, or
467 00:28:19.630 ⇒ 00:28:21.219 Uttam: the table to pull it from.
468 00:28:21.760 ⇒ 00:28:22.660 Ryan Luke Daque: Right.
469 00:28:23.600 ⇒ 00:28:25.930 Uttam: So that’s like the contract handoff. Basically.
470 00:28:26.070 ⇒ 00:28:27.689 Uttam: right in my mind.
471 00:28:28.850 ⇒ 00:28:29.410 Uttam: Yeah.
472 00:28:29.410 ⇒ 00:28:30.700 Ryan Luke Daque: Yeah, makes sense.
473 00:28:34.700 ⇒ 00:28:35.720 Uttam: Okay. Cool.
474 00:28:36.100 ⇒ 00:28:36.920 Patrick Trainer: Cool.
475 00:28:38.750 ⇒ 00:28:40.399 Uttam: Nearly out of this.
476 00:28:42.170 ⇒ 00:28:45.500 Patrick Trainer: Yeah, I wish I would just would have gone the S. 3 route
477 00:28:46.230 ⇒ 00:28:48.250 Patrick Trainer: the entire time.
478 00:28:48.530 ⇒ 00:28:52.180 Uttam: Yeah, I’m starting to just we’re starting to get in the habit of like.
479 00:28:52.420 ⇒ 00:28:59.689 Uttam: what to avoid versus what to lean in on. So yeah, well, I think, s, 3. For this, I think if we get another opportunity.
480 00:28:59.700 ⇒ 00:29:10.829 Uttam: can optimize it further. But as long as this works, I mean, this is basically hopefully save, you know, an hour or so or 2 h per week, or however long. Ryan, you’re spending on it. And then.
481 00:29:10.890 ⇒ 00:29:12.190 Uttam: yeah, that’s more time
482 00:29:12.220 ⇒ 00:29:13.410 Uttam: on other stuff.
483 00:29:13.710 ⇒ 00:29:16.040 Uttam: And then again, any time where
484 00:29:16.300 ⇒ 00:29:18.790 Uttam: we need to do any sort of
485 00:29:19.090 ⇒ 00:29:21.280 Uttam: email related. Etl.
486 00:29:21.870 ⇒ 00:29:24.989 Uttam: securely, I think this is a great way of doing that.
487 00:29:25.760 ⇒ 00:29:27.559 Uttam: You know that we now have.
488 00:29:30.780 ⇒ 00:29:31.310 Ryan Luke Daque: Cool.
489 00:29:31.310 ⇒ 00:29:31.940 Uttam: Cool.
490 00:29:32.180 ⇒ 00:29:32.740 Patrick Trainer: We’ll call.
491 00:29:33.305 ⇒ 00:29:35.000 Uttam: Any other items.
492 00:29:35.580 ⇒ 00:29:37.020 Uttam: I guess, on.
493 00:29:37.200 ⇒ 00:29:40.809 Ryan Luke Daque: For on my end. It’s just the the github workflow
494 00:29:41.040 ⇒ 00:29:44.449 Ryan Luke Daque: for evidence. It’s still failing. I haven’t really.
495 00:29:44.730 ⇒ 00:29:45.410 Uttam: For elementary.
496 00:29:45.763 ⇒ 00:29:47.529 Ryan Luke Daque: Fixed, I mean, yeah. Elementary.
497 00:29:48.080 ⇒ 00:29:48.830 Ryan Luke Daque: Yeah, what kind of.
498 00:29:48.830 ⇒ 00:29:53.199 Uttam: What should we do about this like? I don’t have any other ideas, either.
499 00:29:54.710 ⇒ 00:29:55.909 Uttam: What like.
500 00:29:55.910 ⇒ 00:29:58.390 Patrick Trainer: I actually. So what’s the issue?
501 00:29:59.280 ⇒ 00:30:02.780 Ryan Luke Daque: It’s it looks like it’s the private key.
502 00:30:03.070 ⇒ 00:30:05.919 Ryan Luke Daque: So the the yaml file doesn’t
503 00:30:06.820 ⇒ 00:30:11.410 Ryan Luke Daque: like it can’t read the private key unless it’s so.
504 00:30:11.660 ⇒ 00:30:13.004 Ryan Luke Daque: But I think it’s
505 00:30:14.510 ⇒ 00:30:22.309 Ryan Luke Daque: yeah, I did a lot of things, but it looks like the root cause was that it was. It wasn’t able to read the the key.
506 00:30:22.340 ⇒ 00:30:23.460 Ryan Luke Daque: so does that.
507 00:30:23.460 ⇒ 00:30:25.040 Patrick Trainer: It needs like a path.
508 00:30:25.040 ⇒ 00:30:35.369 Ryan Luke Daque: Yeah. So I I checked the documentation. And they said, like, if we can put the path in a temp like temporary folder or something.
509 00:30:35.370 ⇒ 00:30:35.830 Patrick Trainer: Yeah.
510 00:30:36.220 ⇒ 00:30:40.780 Patrick Trainer: That we we could might be. So we’re defining the path
511 00:30:41.480 ⇒ 00:30:43.889 Patrick Trainer: like on the Github runner, right.
512 00:30:44.110 ⇒ 00:30:47.700 Ryan Luke Daque: Right? That’s what I tried doing. Like, I
513 00:30:48.600 ⇒ 00:30:55.559 Ryan Luke Daque: like, there’s an action that would create the path based on the key file, create the key file, basically
514 00:30:56.350 ⇒ 00:31:02.279 Ryan Luke Daque: from the secrets right? And like, put it in a on a temporary path. And then.
515 00:31:03.370 ⇒ 00:31:10.770 Ryan Luke Daque: yeah, it still didn’t work. Maybe I was. Maybe I was. I was doing it incorrectly or something. Maybe you can check it out, Pat, if you have.
516 00:31:10.960 ⇒ 00:31:11.470 Patrick Trainer: Okay.
517 00:31:11.470 ⇒ 00:31:12.429 Ryan Luke Daque: Like the time.
518 00:31:12.430 ⇒ 00:31:15.390 Patrick Trainer: Yeah, yeah, I can take a look at that.
519 00:31:17.960 ⇒ 00:31:19.890 Patrick Trainer: last case. And like
520 00:31:20.170 ⇒ 00:31:24.740 Patrick Trainer: Udom, I’ll leave this call up to you like, we can just
521 00:31:25.070 ⇒ 00:31:27.120 Patrick Trainer: use a password and
522 00:31:27.880 ⇒ 00:31:28.670 Patrick Trainer: not.
523 00:31:28.670 ⇒ 00:31:34.279 Uttam: Yeah, I mean. So then the nice thing is this is actually just the Ui generation step.
524 00:31:34.550 ⇒ 00:31:34.990 Ryan Luke Daque: This is.
525 00:31:34.990 ⇒ 00:31:41.520 Uttam: Actually the elementary test running. So actually, even my last call is going to be just ditch it.
526 00:31:41.790 ⇒ 00:31:44.750 Uttam: For now, because we can generate
527 00:31:44.940 ⇒ 00:31:46.570 Uttam: the HTML.
528 00:31:47.078 ⇒ 00:31:47.670 Patrick Trainer: We can do it. Yeah.
529 00:31:47.670 ⇒ 00:31:48.680 Uttam: Different way.
530 00:31:48.680 ⇒ 00:31:49.419 Patrick Trainer: That’s a good point.
531 00:31:49.420 ⇒ 00:31:50.340 Uttam: Or like, do.
532 00:31:50.340 ⇒ 00:31:50.740 Ryan Luke Daque: And someone.
533 00:31:50.740 ⇒ 00:31:53.529 Uttam: Else is, gonna be my. So like
534 00:31:54.720 ⇒ 00:31:56.759 Uttam: at maybe spend an hour.
535 00:31:56.960 ⇒ 00:31:58.130 Uttam: Max.
536 00:31:58.474 ⇒ 00:32:04.449 Uttam: if it’s not working, tell me, and or just push a Pr. Or rip it out of the step
537 00:32:04.500 ⇒ 00:32:07.415 Uttam: because I’m done. I’m just done with this.
538 00:32:07.780 ⇒ 00:32:10.249 Ryan Luke Daque: Anybody even like looking at the.
539 00:32:10.460 ⇒ 00:32:15.203 Uttam: No, I think it’s valuable. No, it’s definitely valuable to have. It’s not. It’s not
540 00:32:15.550 ⇒ 00:32:17.079 Uttam: that’s not what I’m
541 00:32:17.290 ⇒ 00:32:27.859 Uttam: doing here. I just can’t have. I just can’t spin wheels on this for any longer. So yeah, I think we’ll just find a different way of generating the HTML, which is actually a pretty.
542 00:32:28.240 ⇒ 00:32:31.329 Uttam: It’s actually a pretty easy step that we can even run
543 00:32:31.560 ⇒ 00:32:34.710 Uttam: somewhere else. Or as a separate workflow.
544 00:32:35.283 ⇒ 00:32:38.349 Uttam: There’s just something that’s happening with generating this.
545 00:32:38.670 ⇒ 00:32:40.170 Uttam: Yeah, so that’s fine.
546 00:32:40.790 ⇒ 00:32:42.959 Patrick Trainer: How does what does it use to
547 00:32:43.400 ⇒ 00:32:45.040 Patrick Trainer: generate the HTML.
548 00:32:46.720 ⇒ 00:32:51.100 Uttam: I think it’s similar to running Dbt source Docs or Dbt. Docs, or something like that.
549 00:32:51.100 ⇒ 00:32:52.789 Patrick Trainer: Okay. So it needs, like.
550 00:32:53.410 ⇒ 00:32:56.139 Patrick Trainer: I guess, like the manifest and the or.
551 00:32:56.140 ⇒ 00:32:56.650 Ryan Luke Daque: Yeah.
552 00:32:56.650 ⇒ 00:32:58.470 Patrick Trainer: Those one of those files.
553 00:32:58.470 ⇒ 00:33:00.449 Ryan Luke Daque: It has hit here.
554 00:33:00.610 ⇒ 00:33:01.330 Patrick Trainer: Okay.
555 00:33:04.270 ⇒ 00:33:06.299 Patrick Trainer: we’ve probably beamed that to
556 00:33:07.500 ⇒ 00:33:10.879 Patrick Trainer: S. 3, and like use like a lambda or something.
557 00:33:11.310 ⇒ 00:33:13.859 Patrick Trainer: Well, no. Then we’d still need to connect to?
558 00:33:15.290 ⇒ 00:33:18.420 Patrick Trainer: Oh, wait! No. So if it if it needs the
559 00:33:19.130 ⇒ 00:33:22.350 Patrick Trainer: a snowflake key, it it uses Snowflake.
560 00:33:22.460 ⇒ 00:33:24.560 Patrick Trainer: it queries. It’ll query Snowflake
561 00:33:25.880 ⇒ 00:33:26.870 Patrick Trainer: directly.
562 00:33:31.160 ⇒ 00:33:33.490 Patrick Trainer: Should we run it from in snowflake.
563 00:33:34.190 ⇒ 00:33:36.780 Patrick Trainer: like asset container, or.
564 00:33:36.780 ⇒ 00:33:38.680 Uttam: Oh, yeah, maybe.
565 00:33:39.410 ⇒ 00:33:41.529 Patrick Trainer: And then and drop it like
566 00:33:41.790 ⇒ 00:33:45.009 Patrick Trainer: in an iframe, in like a streamlit app.
567 00:33:47.450 ⇒ 00:33:55.189 Uttam: Yeah. I mean, I’m like, my mood is just changing these days where I’m like, just let’s move on to something more fun.
568 00:33:57.330 ⇒ 00:34:02.880 Ryan Luke Daque: I mean, we made it work with the user and password. It’s just the key file that didn’t work. I guess.
569 00:34:02.880 ⇒ 00:34:03.530 Patrick Trainer: Right.
570 00:34:04.220 ⇒ 00:34:09.770 Uttam: Yeah, I mean, it technically is a service account. So I guess we could try to do it.
571 00:34:10.480 ⇒ 00:34:12.860 Uttam: Oh, but it’s key pair service account.
572 00:34:13.060 ⇒ 00:34:13.510 Patrick Trainer: Yeah.
573 00:34:13.510 ⇒ 00:34:17.650 Uttam: Yeah, no. So that’s the 2 of it. Yeah, no, I don’t know. Just send an hour, Pat.
574 00:34:17.909 ⇒ 00:34:21.729 Uttam: if it doesn’t work just like call it I don’t like. Don’t let’s just move on.
575 00:34:22.049 ⇒ 00:34:22.359 Patrick Trainer: Alright!
576 00:34:22.360 ⇒ 00:34:25.031 Uttam: If it doesn’t work, just pull it out of that, pull it out of that
577 00:34:25.590 ⇒ 00:34:26.230 Uttam: workflow.
578 00:34:26.279 ⇒ 00:34:27.879 Ryan Luke Daque: Workflow. Okay.
579 00:34:29.559 ⇒ 00:34:30.389 Ryan Luke Daque: Cool.
580 00:34:33.230 ⇒ 00:34:40.339 Nicolas Sucari: Okay. Ryan, just one question about the stuff you’re working on on updating teams weekly report.
581 00:34:40.629 ⇒ 00:34:48.179 Nicolas Sucari: I think what we were so, seeing is that we are not receiving the conversions, and we need to go to the provider right to post pilot.
582 00:34:49.330 ⇒ 00:34:57.500 Ryan Luke Daque: Yeah, for direct. I think that was direct mail. We’re not seeing recent conversions. We’re still getting the conversions, but the
583 00:34:57.630 ⇒ 00:35:00.050 Ryan Luke Daque: old yeah, they’re ones.
584 00:35:00.050 ⇒ 00:35:00.510 Nicolas Sucari: Okay.
585 00:35:00.795 ⇒ 00:35:07.370 Ryan Luke Daque: But for the recent ones like the current week, or maybe even the current month, where it looks like we’re not getting any.
586 00:35:07.630 ⇒ 00:35:14.539 Ryan Luke Daque: That’s for direct mail for Facebook. I just fixed it yesterday. I did some tweaking in the 5 grand.
587 00:35:15.580 ⇒ 00:35:18.449 Ryan Luke Daque: what do you call this connector? Basically.
588 00:35:18.680 ⇒ 00:35:22.990 Ryan Luke Daque: And I have. I already have a Pr. For that one for
589 00:35:23.690 ⇒ 00:35:24.680 Ryan Luke Daque: Facebook.
590 00:35:26.630 ⇒ 00:35:27.590 Nicolas Sucari: Okay, perfect.
591 00:35:28.904 ⇒ 00:35:34.420 Nicolas Sucari: Okay, when that is when that period is already merged, let me know. And I can
592 00:35:35.052 ⇒ 00:35:53.900 Nicolas Sucari: talk to Kim again and see if if there is something else missing. If not, we can. Yeah, try to talk with someone in post from post pile to see what’s going on with that conversions or to check if what we are getting is like the real information. And yeah, conversions just went down.
593 00:35:54.180 ⇒ 00:35:57.729 Uttam: Yeah, you you have the the post file login is there.
594 00:35:57.790 ⇒ 00:36:04.170 Uttam: And it’s just that Lady Janae. I would just email her like, she’s pretty responsive like.
595 00:36:04.370 ⇒ 00:36:07.849 Uttam: don’t just email. And then, yeah, just
596 00:36:08.130 ⇒ 00:36:09.550 Uttam: keep pushing on it.
597 00:36:10.100 ⇒ 00:36:14.120 Nicolas Sucari: Okay, yeah, yeah, I wanna I wanna look into the data to see what we are getting. And
598 00:36:14.130 ⇒ 00:36:19.369 Nicolas Sucari: there is like, any difference. And yeah, I’ll I’ll email her. Okay.
599 00:36:21.107 ⇒ 00:36:30.010 Nicolas Sucari: and then I, we created a new ticket with Jacob to create that timestamp table, for when a customer becomes a pro.
600 00:36:30.280 ⇒ 00:36:34.359 Nicolas Sucari: but I don’t know what I mean. If we wanna just like bench that one
601 00:36:34.800 ⇒ 00:36:35.450 Nicolas Sucari: and.
602 00:36:35.940 ⇒ 00:36:37.630 Nicolas Sucari: Know how to proceed right.
603 00:36:38.030 ⇒ 00:36:39.510 Uttam: Yeah, I would.
604 00:36:40.200 ⇒ 00:36:42.400 Uttam: Just let’s just skip on that.
605 00:36:43.210 ⇒ 00:36:44.830 Nicolas Sucari: Yeah, okay, perfect.
606 00:36:45.670 ⇒ 00:36:46.610 Nicolas Sucari: Great. I’ll leave it there.
607 00:36:46.610 ⇒ 00:36:54.139 Uttam: You can just market. You could just yeah, you could just, or you can market as won’t do or like leave it in backlog. But yeah, let’s just keep that for now.
608 00:36:54.380 ⇒ 00:37:06.329 Nicolas Sucari: Okay, I’ll I’ll leave it in backlog for these weeks. Because we were just talking about that with team. So I’m gonna wait until she says something. If she asks for that we can take it, and if not, I’ll I’ll
609 00:37:06.570 ⇒ 00:37:09.369 Nicolas Sucari: target us. Won’t do for end of the week. Okay.
610 00:37:11.700 ⇒ 00:37:12.570 Nicolas Sucari: perfect.
611 00:37:13.357 ⇒ 00:37:15.391 Nicolas Sucari: Yeah, I think.
612 00:37:16.250 ⇒ 00:37:17.580 Nicolas Sucari: we have a bunch of
613 00:37:17.690 ⇒ 00:37:18.780 Nicolas Sucari: stuff there
614 00:37:18.900 ⇒ 00:37:20.330 Nicolas Sucari: on the
615 00:37:20.400 ⇒ 00:37:31.520 Nicolas Sucari: backlog. And Brian. You have like a lot of tickets already this week. But let me know if you are gonna be able to work on the unleashed stuff, if not, we can. Yeah, I think we can move that to next week. Probably.
616 00:37:31.520 ⇒ 00:37:36.273 Ryan Luke Daque: Yeah, sure. That’s what I was actually starting to work on today. So yeah.
617 00:37:36.810 ⇒ 00:37:37.630 Ryan Luke Daque: cool.
618 00:37:38.460 ⇒ 00:37:39.190 Ryan Luke Daque: yeah.
619 00:37:39.190 ⇒ 00:37:41.659 Uttam: And require a good amount of work with chuck.
620 00:37:42.840 ⇒ 00:37:43.360 Ryan Luke Daque: Right.
621 00:37:43.360 ⇒ 00:37:51.120 Uttam: So again, I I’m not. Gonna I’m not gonna be. I’m not gonna have the answers. So basically, any question you have just ping, chuck
622 00:37:52.490 ⇒ 00:37:55.059 Uttam: in the shipping channel because he’s gonna have
623 00:37:55.410 ⇒ 00:38:01.759 Uttam: all the answers, or be basically be able to connect us with whoever we need for from unleashed.
624 00:38:02.210 ⇒ 00:38:03.260 Uttam: Right?
625 00:38:03.870 ⇒ 00:38:06.160 Uttam: So yeah, it’s another new world.
626 00:38:07.870 ⇒ 00:38:08.710 Ryan Luke Daque: Okay.
627 00:38:08.860 ⇒ 00:38:09.480 Nicolas Sucari: Okay.
628 00:38:10.950 ⇒ 00:38:12.710 Nicolas Sucari: Perfect. Okay.
629 00:38:14.380 ⇒ 00:38:15.460 Nicolas Sucari: Anything. Else.
630 00:38:18.840 ⇒ 00:38:21.530 Uttam: Well, I think, Pat, there’s also some other stuff in ready.
631 00:38:24.010 ⇒ 00:38:32.540 Uttam: this week. Kind of I I wanted to see if we can also do something around while we’re basically at the end of the security stuff.
632 00:38:32.680 ⇒ 00:38:36.269 Uttam: I wanna just get everything in a dock. And I was gonna make like a nice
633 00:38:36.560 ⇒ 00:38:39.530 Uttam: brainforge, Doc, that we can
634 00:38:40.516 ⇒ 00:38:43.080 Uttam: have on the site. But then also
635 00:38:43.558 ⇒ 00:38:46.740 Uttam: have as far as materials, we get the clients. So.
636 00:38:51.110 ⇒ 00:38:52.150 Patrick Trainer: Cool. Yeah.
637 00:38:53.070 ⇒ 00:38:58.930 Uttam: Yeah, there’s a there’s a ticket for just create a security policy, doc. So hopefully, that’s not that bad.
638 00:38:59.020 ⇒ 00:39:00.670 Patrick Trainer: Okay, where?
639 00:39:01.270 ⇒ 00:39:03.360 Nicolas Sucari: I I’m only there to reuse it.
640 00:39:04.630 ⇒ 00:39:06.489 Patrick Trainer: I’m looking in backlog. Sorry.
641 00:39:07.180 ⇒ 00:39:08.520 Patrick Trainer: Okay. I see it.
642 00:39:09.310 ⇒ 00:39:10.470 Uttam: Yeah.
643 00:39:13.900 ⇒ 00:39:17.820 Patrick Trainer: Don’t like practices. Okay, cool.
644 00:39:20.530 ⇒ 00:39:21.140 Nicolas Sucari: Okay.
645 00:39:25.820 ⇒ 00:39:27.959 Uttam: Okay. Cool. Anything. Else.
646 00:39:31.410 ⇒ 00:39:33.010 Patrick Trainer: That’s it for me. Nope.
647 00:39:33.370 ⇒ 00:39:35.120 Ryan Luke Daque: Yeah, I think I’m good as well.
648 00:39:39.590 ⇒ 00:39:40.310 Ryan Luke Daque: Yep.
649 00:39:41.110 ⇒ 00:39:41.840 Nicolas Sucari: Okay.
650 00:39:42.710 ⇒ 00:39:43.320 Uttam: Okay.
651 00:39:44.016 ⇒ 00:39:44.910 Nicolas Sucari: I’d like
652 00:39:45.530 ⇒ 00:39:46.890 Nicolas Sucari: bye right.
653 00:39:46.890 ⇒ 00:39:47.540 Ryan Luke Daque: Bye, bye.