Mock Interview | Data Engineer

The video begins with a sponsorship message from Astronomer. Then moves into the introduction of the mock interview session with Sanam, a data engineer with three years of experience. The first problem statement involves finding the top five movies with the highest average rating among users who signed up in the last 12 months and watched at least three movies. The problem includes details on the required tables and schema.

Sanam outlines his approach to solving the first problem, which involves filtering users based on signup date, joining tables to identify movies watched, calculating the number of movies watched per user, filtering users who watched at least three movies, and then ranking movies based on average rating.

The interviewer introduces the second problem statement, which is a PySpark coding challenge. The challenge involves enriching a PySpark DataFrame with city names based on latitude and longitude by calling an API for records where the city is null.

The interviewer asks if Sanam has ever worked with User Defined Functions. Sanam confirms that he has experience with UDF's. The interviewer and Sanam discuss the advantages of using User Defined Functions instead of array's.

Sanam continues to participate in the interview answering questions about Delta Lake, comparing its' features to a data warehouse. He mentions that it is used to store the historical data with raw/instructed data, while a Data Warehouse is used for analytical cases.

The interviewer asks Sanam questions about Airflow. Sanam describes the advantages such as defining different stages of a job, and being able to schedule tasks. Sanam also mentions disadvantages such as having to manage airflow worker scheduler.

The interviewer asks Sanam to list examples on Spark optimizations implemented in his previous projects. Sanam gives examples such as converting a python project into spark, and using persist to read table data.

Sanam and the interviewer discuss Spark job failures, specifically an out of memory error. Sanam discusses how he extracted data and converted it into struct, and stored the table into a defined table for analytical use case.

Sanam lists several AWS services that he has worked with, which includes: EMR, S3, Athena, Glue catalog, and MSK. Together, the interviewer and Sanam dive into detail about streaming data, and different operations.

The interviewer provides feedback to Sanam, commending his SQL query and coding skills. However, he suggests improving his theoretical knowledge, particularly regarding Delta Lake, Airflow, and providing more concise answers during project discussions.