I interviewed with Amazon in the month of March for the post of Data Engineer II (L5) in Bangalore
Total rounds: 6
Outcome: Offer declined due to Compensation

Round 1
Online test with 30 questions in 25 minutes. The questions were around DBMS concepts, SQL outputs, easy python outputs, theoritical big data concepts. It would be a very easy round if you are a regular with the above concepts. I am not sure how much I scored but if I eliminate the questions for which I was doubtful, it would be easily 24+. I am 2000% sure about my answers to the questions which I knew were correct!

Round 2
This was a short 35 minutes round with an HR person which happened about 2 weeks after the first round. She called to discuss about the role and asked me about my current projects. She was picking out different requirements for the role and was asking if I have done any kind of project with that. Some of the items were redshift, AWS Lambda, ETL development, etc. After this, she told me that someone from HR would contact me about the next steps.

About a week later an HR guy called to ask me the date for the interview and to explain the process. I asked him 2 weeks preparation time and he accepted.

Round 3

• This round was mainly around concepts of data warehousing and data modelling. I was asked to introduce myself and talk about one of my recent data engineering projects. There were 2 interviewers who asked me to create a data model for my project and questioned me regarding certain reasons about why I implemented something in some way. There were questions about slowly changing dimensions, surrogate keys, indexing, etc. This was about 75 minutes long.
• Next I was asked a SQL question to find out percentage difference between 4 month and 6 month running average. The dates were provided in YYYY-MM-DD so I had to extract month, and then use rows preceding window function. It was a good question I think
• After that, I was asked a SQL question which was based on gaps and islands. I was able to grasp the concept but was not able to formulate the final logic to identify gap and island so I couldn't solve it totally
• Next, I was provided with a data with rows having lots of text. The interviewer asked me to process the data with python and generate a new dataset with first letter capitalized for each word in the text provided. It was a simple problem I think
• There were finally 2 behaviorial questions where I was asked about how I have saved company money and if i have ever worked with an unrealistic timeline

Round 4
This round happened 2 hours after the previous round on the same day. The interviewer was the hiring manager. He asked me to introduce myself and then jumped right into SQL questions. This round too last about an hour or a bit more.

• First question was a simple one about a dynamic price market. It asked to find optimal time to buy and sell a particular item. I got confused in between but the interviewer provided some hint and then it was smooth ride and was able to solve it
• Next, he asked me about the databases I have worked on and if I have used redshift. After that, there was a question regarding performance of a query and how do I handle it. After that, he asked me about what's my choice of ETL tool. I told him I schedule and run pipelines using python so he asked me to explain the steps
• Next, he asked me to provide an example of conflict with my manager. After I answered, he asked about if I have worked with someone or a team who couldn't perform their tasks well

Round 5
This was a surprise round about which I wasn't informed beforehand. HR guy called me to set this one day before and I agreed. This was the longest round which lasted about 90 minutes.

• First question was regarding my introduction and one of my projects
• Next he asked me to create a model of a clothing store data warehouse and explain the components. After that, he asked me to write DDL statements for them
• After this, he asked me to find sum of value for even number transaction for a product A. He asked me to use the schema I created and write the SQL code. Once I was done, he asked me to explain what I wrote. I used row_number to define the transaction_number based on transaction date and partitioned on product. Then I grouped the amount value by product for all transactions which could be divided by 2. He was satisfied
• Next, he asked me if I have invented something outside my expertise. I told him about a Slack analytics bot I created which would provide values of KPIs to leadership anywhere around in response to certain commands. It used rest API, AWS API Gateway, S3, AWS Lambda and on prem to cloud ETL pipelines. He asked me about the language I used for this and then asked me to explain the process theoritically by showing how the components interacted when a consumer wrote a command on slack. He also asked how I implemented authentication and I explained the function I wrote to match key and secret and other metadata to verify if the request was genuine.
• Next, he asked me about a time when I made a decision without any data points. After this, I asked 2 questions and the round ended.

Round 6
This was the final round for which I was informed. By this time I was a bit frustrated because this round happened after 2 weeks from the previous one. This round again last about 70 75 minutes

• Started with the introduction and experience of a project. There were 2 interviewers again
• Next, we moved on to SQL questions. He asked a question about some customers who bought 2 products and didn't buy third one. It was quite simple one
• Next, pasted a nested JSON text on the editor and asked me to flatten it into a tabular structure. He mentioned specific values he needed from the file. I asked if i can use Pandas and he said yes. It was by far the easiest question of all the rounds. It involved simple conversion of a dictionary to a dataframe
• Then he asked me some output questions which were very simple. One python question about creating abbreviation out of a sentence by using the first letter of each word.
• After this, the other interviewer asked if I knew Tableau and I said yes. He asked me to explain action filters and Level of detail expressions. I am not sure why it was asked
• Then the first interviewer moved on to theory questions. He asked me disadvantages of indexes. Use of CTEs. Difference between CTE and temp table. Correlated query and its solution. Finally he asked about how joins worked behind the scene. I didn't really understand what behind the scene meant then he said that he wanted to know what goes behind. I didn't have very good knowledge about it so I politely accepted that I do not have proper idea about it
• Finally, he asked me about a time when I went above and beyond in my project. Next, he asked what is one thing I want to improve about myself and I told him about how I am working on my people management skills and taking necessary trainings to move to a engineering manager role in the future.

That was the final round and i sighed with relief knowing there aren't anymore. I didn't really bother about the results because I knew Amazon's hiring bar is quite high and I couldn't answer 2 questions so I might already be out.

About 3 days later, I got a Congratulations email. Honestly, I was elated. But, later during negotiations, they quoted a very low salary which was not acceptable so I passed on the offer.

If you have made till now, thanks for reading and best of luck if you are preparing for the interview!