Round details
Post: Senior Data Engineer
Location: Bangalore

Total Rounds after discussion with HR:

• 1 Online Test (1 hour)
• 5 Tech/Fitment Rounds (all 45 minutes)

Round Details

1. Online Test

   • This round consisted of 4 SQL questions and a python coding question.
   • Total time allotted was 1 hour
   • All the SQL questions were regarding a holiday resort and calculations of metrics around it like People who booked the first time, Role 3 months cancellations average, busiest season, etc. Questions were between medium to hard
   • Python question was this: https://leetcode.com/problems/median-of-two-sorted-arrays/
   • It took me about 40-42 minutes to solve all the questions. SQL questions took about 20 minutes and the python question took another 20.
   • This was an elimination round so I got the call from HR the next day

2. Tech Round 1

   • This was a python coding round. The good thing about Airbnb is that the questions usually are related to the job profile and not some random question off the internet
   • The interviewer gave me a list of tuples and each tuple represented an activity of a user adding a city to its wishlist. So, if user1 adds city1 and city2 into its wishlist, the list would have 2 tuples (user1,city1) and (user2,city2). The same goes for other users
   • There was another list with tuples that denoted friendship between user1 and user2 as (user1,user2)
   • The question was to design a recommendation system where it would take the username of a user and would generate a recommended list of cities for that user using the cities of that user's friends. The cities in this list shouldn't be part of that user's wishlist
   • I discussed the approach and coded the solution in about 25 minutes. Then, there was a follow-up to sort this recommendation list based on the number of times a city has been wish-listed by the friends of that user.
   • After I coded it, we ran some test cases on the hackerrank platform. All cases ran perfectly.

3. Tech Round 2

   • This was a SQL coding round. The interviewer was a bit cranky and I got that because my webcam wasn't working suddenly and this person rolled its eyes. But, I got it back up.
   • After that, I got a question about a transaction table and was asked to develop three metrics.
   • I discussed the business rules and the structure of the table that was provided
   • I gave my initial solution which worked fine. Next, I was asked to parameterize the solution which I did
   • There was a date column and I was asked to generate a report of unique_users who booked for the first time on a given month even if the month wasn't there in the fact table.
   • I solved it using recursive CTE and joining that with the results of the transaction table. The interviewer was satisfied
   • After that I was asked about some data issues and checks I will apply on the data. I talked about duplicates, referential integrity, constraints, data types, business rules, NULLs, etc. The round was over after that

4. Tech Round 3

   • This was an experience/fitment discussion round and didn't really contain any tech questions as such
   • We discussed about two of my projects and my role in it
   • The interviewer asked specific questions about the technology I used and why
   • He also asked me to roughly draw the workflow of one of my projects on a whiteboard
   • He also asked about challenges, my future goals, my experience with specific technologies like Scala. I said that I have worked on Pyspark and not on scala which I felt wasn't quite welcomed by the interviewer
   • Final question was around if I had to develop a data warehouse from scratch, what would be my considerations. We discussed, the purpose of the DW, transactions to be captured, grain, business rules, data modeling and design, choice of technology, actual development, test cases to check the issues, etc. As most of my experience has been with RDBMS, I answered with reference to that

5. Tech Round 4

   • This was a mixed round of whiteboarding and SQL
   • I was asked to develop SQL for a feature of the website which told if a given property was priced more or less than the previous week same day. The value was to be represented in percent
   • Then, I was asked how to prepare another feature using the booking history of the user which I can't remember now
   • I was asked what would be by consideration to speed up the results and I talked about limiting the history, materialized views, partitioning and indexing of the table, etc.
   • Then he asked me if I have used Scala. I said no and said I have used Pyspark. He asked me about different considerations I have to take to optimize a slow-running job.
   • Also, we discussed one of my custom projects of automating AWS EMR pipelines I built for my company. He asked me why I built it if there are already solutions available that can do that on AWS. I said that it was a cost-cutting measure to remove dependency on AWS-managed services and explained my solution. I am not sure if he was convinced as he kept quoting the AWS data pipeline and its advantages. The round was over after that.

6. Tech Round 5

   • This was a data modeling round. I was presented with 5 issues and the interviewer asked me to develop a data model for it
   • I started with an initial design and asked if it was okay to improve the model based on each issue and he was fine with it
   • I started designing the model and also coded the SQL for the issues. The interviewer asked me my approach and then we moved to the newer issue
   • In the end I had a good data model with a transaction table, users, hosts, properties, price table, reviews, geography, time bucket, etc. All the issues had proper SQL which I explained to my interviewer and he was very satisfied with my approach
   • He asked me what would be the primary and foreign keys in my model and I showed different keys in my transaction table which were foreign keys to my dimension tables
   • He asked if I will use a surrogate key. I explained scenarios where I will use it (external dependent systems) and not use it (internal systems)
   • Final question was about my approach to handling changing data like phone numbers and addresses. I asked if history was required. He said yes. Then I explained my two approaches - use of a 'latest' flag and/or using a refresh_timestamp to add the timestamp when a datapoint was updated to pull the latest value. The interviewer was satisfied as I wrote the SQL for both. The round ended after that.

Verdict
A little over a week, I got an email saying that I was rejected. I am not sure what I did wrong but to be honest it did feel bad because I feel I had nailed almost all the rounds except my inexperience with Scala. However, the good thing about Airbnb is that the interviewers are very professional and friendly and that made the rounds really fulfilling for me.

Nevertheless, sometimes these interviews are all about luck. I have literally no idea what else I could do other than solve all the questions and answer with structure and clarity. Let's pray I am successful soon.

I hope this was useful.