Data Engineering Interview Questions Answered and Explained! — Transcript

Full transcript

1. 0:00hey y'all data guy here and today I have
2. 0:03a kind of followup video to a previous
3. 0:05video I had on you know just common data
4. 0:07engineering interview questions and
5. 0:09today I'm going to take that a step
6. 0:10further and go through some of the most
7. 0:12common data engineering questions I've
8. 0:14seen and just have heard about uh in my
9. 0:16time in the industry um and give you
10. 0:18some answers for them and not only
11. 0:20answers but a framework for how to think
12. 0:22about responses to these different types
13. 0:24of questions um because that's really
14. 0:26what you need because every question
15. 0:27isn't going to be the exact same but a
16. 0:29lot of them especially you know some of
17. 0:30these most common ones follow along the
18. 0:32same lines of testing how you think
19. 0:34about developing certain Solutions what
20. 0:36considerations you're going to make so
21. 0:38I'm going to show you how you can do
22. 0:39that um and then also talk about the
23. 0:41considerations that went into that
24. 0:42answer so that you know how to build
25. 0:44your own answers in the future um so
26. 0:46without further Ado let's get into it
27. 0:48now the first and probably most common
28. 0:50question I've seen out there is how
29. 0:52would you design a data pipeline for X
30. 0:56um in this case let's say there how do
31. 0:57you design a data pipeline for batch
32. 0:59processing
33. 1:00now the main thing you're going to want
34. 1:01to focus on is discussing the
35. 1:03architecture and the tools you would use
36. 1:06as well as emphasizing how you're
37. 1:07promoting things like scalability
38. 1:09reliability and efficiency so an example
39. 1:12response there would be I would design a
40. 1:14pipeline with a cloud-based data Lake
41. 1:16such as Amazon S3 or Azure data Lake as
42. 1:19a Central Storage I will use tools like
43. 1:21Apache spark to handle bat processing
44. 1:24for Transformations and aggregations um
45. 1:27for orchestration of my data pipelines
46. 1:29I'll use some something like aache air
47. 1:30flow to manage dependencies schedule
48. 1:32workflows across dis Sprint systems um
49. 1:35and then finally processed data will be
50. 1:37loaded into a data warehouse like
51. 1:39snowflake for analytics and business
52. 1:41intelligence um I would ensure fault
53. 1:43tolerance and scalability by leveraging
54. 1:45the distributed nature of spark and also
55. 1:47the elasticity of cloud resources um and
56. 1:50there you're hitting on hey good
57. 1:51knowledge of the different components of
58. 1:53the modern data stack understanding what
59. 1:55they do what they're best at um and also
60. 1:58talking about how you design this Pike
61. 1:59Line for actual production setting um
62. 2:02you know in a production setting you
63. 2:03can't just be some Fly by Night setup on
64. 2:04a VM you need to think about things like
65. 2:06reliability like scalability what if a
66. 2:08region goes down how are you preparing
67. 2:10for that U so make sure you include all
68. 2:11those types of things in your response
69. 2:14now another question you might get is
70. 2:16something like how do you handle scheme
71. 2:18evolution in a data lake or a data
72. 2:20warehouse um and let's say actually they
73. 2:22say a data lake house um but here what
74. 2:25you really want to focus on is you know
75. 2:27think about backward and forward
76. 2:29compatib
77. 2:30um and also the tools and practices you
78. 2:32would use to implement this because
79. 2:33scheme evolution is something that is a
80. 2:35constant factor and so they want to
81. 2:37think about hey are you designing
82. 2:39systems that are both you know friendly
83. 2:41to existing formats and existing data um
84. 2:44but also support and enable us to
85. 2:46support future data sets and additional
86. 2:48uh Evolution um and so here an example
87. 2:51response would look something like I
88. 2:52handle scheme evolution by using formats
89. 2:55specific features like schema evolution
90. 2:57in Apache abro or paret uh and to
91. 3:00support backwards compatibility I would
92. 3:02allow new Fields with default values and
93. 3:04assure existing Fields aren't REM
94. 3:06removed or altered unexpectedly in a
95. 3:09data warehouse like big query I would
96. 3:10use a schema migration tool to actually
97. 3:12validate and roll out the changes
98. 3:14incrementally and there you're showing
99. 3:16both hey you know the different formats
100. 3:17for efficient scheme Evolution like AO
101. 3:19or parquet you understand the process of
102. 3:22how exactly to implement scheme
103. 3:23evolution by allowing new fields and uh
104. 3:26setting rules for ex existing Fields but
105. 3:28then you also mentioning tools like a
106. 3:31screen migration tool to actually
107. 3:33Implement these changes in production
108. 3:34because you aren't going to be able to
109. 3:35do it all manually in production most of
110. 3:37the time you need to have tools and out
111. 3:39automation to do this at a really large
112. 3:41scale now another type of question you
113. 3:43might get I think is really popular is
114. 3:46how would you optimize a large slow
115. 3:48running SQL query um and here people
116. 3:51really want to see you know what is your
117. 3:53kind of decision Matrix similar to
118. 3:55something like this you see up on the
119. 3:56screen you know understanding your train
120. 3:57of thought or what are the steps you
121. 3:59would take to just look at any SQL query
122. 4:02um and analyze it for ways to optimize
123. 4:05it and so in terms of your what you
124. 4:06actually want to mention there is
125. 4:08understanding analyzing those execution
126. 4:10plans going deep into the code of how
127. 4:12those SQL queres are actually being
128. 4:13executed and then address things like
129. 4:15indexing query restructuring and table
130. 4:17optimizations to display your knowledge
131. 4:21of you know how do I do in-depth
132. 4:23Enterprise scale SQL optimization um so
133. 4:26a good response would be something like
134. 4:27I'd start by examining the query
135. 4:29execution plan plan to identify
136. 4:30bottlenecks then I would add appropriate
137. 4:32indexes to filter columns or frequently
138. 4:34joined columns uh which is often
139. 4:37affective at uh optimizing queries and
140. 4:39then I would also look at rewriting
141. 4:41correlated subqueries as joins or using
142. 4:43window functions um for example if a
143. 4:45query performed a full table scan I
144. 4:47might create an index on the filter
145. 4:48column uh to significantly improve
146. 4:51performance um and there you're showing
147. 4:53hey I understand how what you need to
148. 4:55look at to optimize a SE query and then
149. 4:57also what the typical steps might be for
150. 4:59different scenarios
151. 5:00based on what I find there and how I
152. 5:01would actually optimize and respond to
153. 5:03that now another popular question when I
154. 5:05actually got one of my first job
155. 5:06interviews was explain the difference
156. 5:09between a star schema and a snowflake
157. 5:11schema and when would you want to use
158. 5:13each um and here you really want to
159. 5:15focus on hey provide very clear
160. 5:16definitions clear guidelines for
161. 5:18identifying you know what a star schema
162. 5:20does what a snowflake schema does and
163. 5:22then what each best atat highlighting
164. 5:24their respective use cases and tradeoffs
165. 5:27uh for each Snowflake and star schema um
166. 5:30and there are other types of schemas
167. 5:31that you might be asked to compare but
168. 5:32these are the primary ones that you'll
169. 5:34normally be asked about um and example
170. 5:36response would be something like you
171. 5:37know a star schema has a central fact
172. 5:39table directly connection connected to
173. 5:42Dimension tables making it easier and
174. 5:44faster for querying a snowflake scheme
175. 5:47on the other hand normalizes Dimension
176. 5:48tables into multiple related tables
177. 5:50reducing redundancy but increasing query
178. 5:53complexity I would use a star schema for
179. 5:55analytics requiring High query
180. 5:57performance and a snowflake schema when
181. 5:59data consistency and storage
182. 6:00optimization are critical um and there
183. 6:03you know you give both a solid
184. 6:04understanding of what each of those
185. 6:05schemas are but then also what each each
186. 6:07are best at and related to the use cases
187. 6:10that benefit the most from those
188. 6:12advantages and lose the least from those
189. 6:15trade-offs now another question you
190. 6:17might be asked that's especially
191. 6:18pertinent in these day this day and age
192. 6:20um is what are the trade-offs between
193. 6:21batch and streaming data pipelines um
194. 6:24and here it's really about understanding
195. 6:25hey how do you think about processing
196. 6:27data um and also what are you best at
197. 6:30here what do you you know what do you
198. 6:31kind of lean towards some organizations
199. 6:32lean more towards batch some lean more
200. 6:34towards streaming um and here you really
201. 6:37want to give your framework for how you
202. 6:39would compare latency complexity and use
203. 6:41cases and really talk about the
204. 6:43requirements um that you would use to
205. 6:45dictate hey which one is best for which
206. 6:47use case um and so an example response
207. 6:49would look something like bash pipelines
208. 6:51process data in large chunks which makes
209. 6:53them simpler to build and more suitable
210. 6:55for use cases like nightly ETL jobs or
211. 6:57historical reporting however they don't
212. 7:00provide realtime insights um streaming
213. 7:02pipelines process data continuously with
214. 7:04low latency ideal for real-time
215. 7:06dashboards or event trien architectures
216. 7:08but they're more complex resource
217. 7:10intensive um and there you give a good
218. 7:12understanding of how what each of those
219. 7:13uh processes are how they fit into the
220. 7:16broader data ecosystem and also talk
221. 7:18about the use cases that you'd most
222. 7:20commonly use each in just really kind of
223. 7:22showcasing your all around understanding
224. 7:24of both the actual process and its use
225. 7:26case um in modern data engineering now
226. 7:29now the next common question you might
227. 7:30get asked is how do you Monitor and
228. 7:33trouble data shoot or troubleshoot a
229. 7:35data pipeline um and here it's the
230. 7:37questions really twofold number one what
231. 7:39is your familiarity with the monitoring
232. 7:41tool SC tool stack different tools out
233. 7:44there how to use them how they fit
234. 7:46together um but then also your specific
235. 7:49strategies for actually figuring out an
236. 7:51alert you know where are you going to go
237. 7:52first to look how are you then going to
238. 7:54purse that issue down the pipeline
239. 7:56identify root causes um and so here
240. 7:59you're going to to discuss monitoring
241. 8:00logging alerting and then also include
242. 8:02tools and specific strategies that you
243. 8:04might have already used in a previous
244. 8:06role and so a good example response here
245. 8:08would be something like I use logging
246. 8:10Frameworks like elk stack or Cloud
247. 8:13native Solutions like AWS cloudwatch for
248. 8:15log log aggregation and monitoring
249. 8:18metrics such as data processing times
250. 8:20throughput and error rates are tracked
251. 8:22using tools like Prometheus and grafana
252. 8:24and I set up alerts for SLA violations
253. 8:26or anomalous patterns and then drill
254. 8:28into logs to identify root causes uh
255. 8:31during
256. 8:31failures and there you've given a clear
257. 8:34picture of your understanding of the
258. 8:35entire monitoring stack but then also
259. 8:37have talked about hey this is how I use
260. 8:40these tools in practice to actually fix
261. 8:42pipelines because that's what any data
262. 8:44engineer wants the colleague is someone
263. 8:45that's really good at identifying and
264. 8:47fixing data pipelines when they break
265. 8:50now the last thing I want to talk about
266. 8:52the last question I have for you is
267. 8:54describe how you would build and
268. 8:56maintain a highly available data
269. 8:58pipeline um and this really incorporates
270. 9:00a lot of features from these other
271. 9:01questions um to into one box where you
272. 9:04want to be able to address redundancy
273. 9:06monitoring fault tolerance you mentioned
274. 9:09specific tools architectural strategies
275. 9:11ways on how you would build it um and
276. 9:13then also maintaining it for the long
277. 9:15term so it really folds a lot into this
278. 9:16question and so therefore your response
279. 9:19is going to want to fold a lot into it
280. 9:21as well and so a good example response
281. 9:23for something like this is I'd build a
282. 9:25highly available data pipeline by
283. 9:27leveraging distributed systems like C
284. 9:29for their data ingestion and Spark for
285. 9:31processing because of their distributed
286. 9:33nature um and then for data storage I
287. 9:35would use cloud-based Solutions like S3
288. 9:38or Google Cloud Storage with replication
289. 9:40across multiple regions to ensure uh
290. 9:42resiliency against a single Regional
291. 9:45outage um I'd also Implement fault
292. 9:47tolerance by implementing retries item
293. 9:49potency and checkpointing and Spark to
294. 9:52make sure that faults trigger alarms uh
295. 9:54when they arise uh and then I would
296. 9:56layer on top of that a monitoring tool
297. 9:58like dog or Prometheus for realtime
298. 10:01visibility and then Implement automated
299. 10:03fail failover mechanisms to minimize
300. 10:05downtime in the event of an outage of
301. 10:06any of those tools and there that's
302. 10:08really bringing it all together that's I
303. 10:10wanted to finish you with it because it
304. 10:11is an all-encompassing question of hey
305. 10:14this is exactly what you're going to be
306. 10:15doing day-to-day as a data engineer and
307. 10:17so it shows how you can combine all
308. 10:19these different skills that you know
309. 10:20into actual functional work how are you
310. 10:24going to take those skills and produce
311. 10:26value for the company that is a perfect
312. 10:28question for assessing that um so I hope
313. 10:30you've enjoyed this video if you have
314. 10:31any more questions or any other things
315. 10:32you'd like to see covered please let me
316. 10:34know we would love to discuss it um but
317. 10:36above all else have a great rest of your
318. 10:37day get a guy out