Deloitte ETL Developer Interview Questions and Answers

Interview preparation tips for other job seekers - Proceed only if you are located near the client's office.

Interview preparation tips for other job seekers - Appear confident

Round: Resume Shortlist
Experience: Dual Degree students with decent CPI, relevant projects and extras
Tips: Highlight subjects and projects involving data mining, machine learning, marketing research

Round: Test
Experience: PST had 26 questions to be done in an hour (3 cases) and 20 QUESTIONS in 30 mins general apti (level: 2-3 of Arun sharma)
Tips: practice!
Duration: 1 hour 30 minutes
Total Questions: 46

Round: Technical + HR Interview
Experience: Since my resume was filled with

College Name: IIT Kanpur

Round: Case Study Interview
Experience: The interview started with a 'tell me about yourself' for which i had prepared a 1 minute intro highlighting specific aspects about myself that I wanted to bring to his notice . Then he asked me about what i did in CRISIL (my prior workplace) . I explained that I was engaged in assigning Credit Ratings to Corporates under Basel II Regulations.
How would you go about Rating a Company, lets say Bharti Airtel.
Because it was Bharti Airtel I had to give an answer tailored to what the approach would be for a Telecom company would be. While answering I was conscious of 2 things – One, that I have a clear structure to my answers and Two, tried to keep it as simple and jargon free as possible.
(An Offshoot from something I had mentioned in the earlier answer) What are the ratios that you would look at?Here I mentioned the financial ratios that I would look at stating specifically why I would look at each ratio. He probed me on a few ratios here and there. The intention appeared to be more to check my articulation of a financial concept.
Eg: What is a Current Ratio?
I explained the formula. However I did not stop there. I went on to explain that it was used to assess the liquidity position of the company.
Then I went on to explain to the logic behind the usage of the ratio – Simply put, it tells us if there is likely to be sufficient inflows of funds over the next one year to meet the maturing payment obligations. So a current ratio of less than one means the company has more to pay than what is coming in, a liquidity problem. (Based on the approving nods that I was getting, I felt he liked those portions of my answer where I endeavored to de-jargonize or simplify complex concepts)
So Tell me, how would you increase the revenues of Bharti Airtel.
That was my case, it had begun. However, at that point I thought it was just an extension of my previous discussion and I started rambling.
Analysis:
Preliminary questions - We all know Bharti Airtel and the services it offered quite well – So there was no need to ask preliminary questions. Also, since I wasn't aware that I was doing a case I did not have the opportunity to ask preliminary questions.
Structure: The Structure that I used was fairly simple – Increase in Revenues can in Existing markets or through expansion in new markets.
I explored the existing markets first. Here increase in revenues could occur through increased market penetration, increased usage of basic services among current users or increased usage of Value added services among existing customers. He asked me a couple of questions on how these things can be done and I gave him some off the cuff answers.
For the New markets, I suggested that the company could look at inorganic expansion through acquisitions or through establishment of Greenfield operations in new markets with high market potential.
The entire case was more like an informal chat. There were a few follow up questions on some of the options I had generated. The case did not last very long – about 10 minutes at the max.


Tips: Know your Resume well. At no point in the interview should you ramble on. All answers must be natural (ie, must not appear rehearsed), brief, structured and to the point.

Retaining composure through the interview is critical. Mistakes may happen during the interview, but it is important to pick you up and keep at it. People rarely have perfect interviews where everything has gone right and interviewers don‟t expect it either.

Round: Case Study Interview
Experience: Case: I am the manufacturer of Tata Nano. While setting up the plant I expected to have sales of around 5 lac Nanos per annum, but now the number is way below that. Can you help me find out why.
Analysis: Preliminary questions – Since it was Tata Nano, lot of the background questions were not required. I clarified whether the problem was a lack of bookings that they received. He agreed.
Structure: The Structure that I used was not the best but it did get me to the problem. I started off by saying the lack of bookings could be on account of 3 reasons – One, a lack of awareness of the product among the target customers (Marketing problem), Two, Being aware of the product but not being able to place a booking (Distribution problem) or a Three, being aware of the product yet not interested in the product(i called this the product problem). He asked me to ignore the first 2 (he did not seem highly impressed) and explore the 3 rd the product problem.
Here I again broke it up in to 2 further branches – Problems that the Car has by itself (Internal factors) and problems in the external environment mainly competition, etc. I deep delved in to the Internal factors.
Of the internal problems I mentioned, he agreed that the negative publicity from the steering wheel catching fire was an issue and comfort factor was another issue. As and when he acknowledged a problem, I made a separate note of it. Eventually when he asked me to explore the external factors, I used these points to do a quick summary of the key takeaways up to that point.
In the external problem, I divided it into competition from other cars and two wheeler. I asked a few questions here trying to pick up a clue or 2 of how to proceed from there but he just refused to shell out any information. At this point he also took out his Blackberry and acted uncooperative. I went on to analyze for myself that given that the price of the next cheapest car was over a lakh higher than this car the issue lay more with the 2 wheeler. I went on to list factors why people would prefer 2 wheeler to a Tata Nano. Of the factors I listed, he acknowledged that the high running costs and availability of easy financing options seemed like the distinguishing factors. He said I seemed to have arrived at the problem.
He now asked me to tell me how he could provide
financing options for the Nano. This I felt was like a
guess estimate problem. I said I would work backward
to solve the problem. This is how I solved it – I said the average Nano target customer would earn Rs 10000 per month (he had given me that number in an earlier discussion, he liked the fact that I had noted it down and was using it again) I said, given the average savings rate of the country is at 35%-40% (this is from the RBI website I think). That meant a saving of about Rs 4,000 per month. No one would be comfortable giving up their


Skills:
College Name: IIM Lucknow

Round: Case Study Interview
Experience: PI Questions came after the case. It started with interviewer asking about my prior work experience. All subsequent questions emanated from the first question which was: “Of all your achievements, which one do you think is the most important to you”. This was a classic case of being able to draw the interviewer to where you want to go. I talked about a very tough implementation project that I did at ITC Factory in Munger, Bihar. I said for me an achievement is about 2 things: (1) The Context in which I
achieved it (2) What it meant to me at that stage. In the context part, I talked about managing an un-skilled workforce.
Then he asked me how do you manage such a work force? I said by established an internal connect with them. He asked how do you do that? Went into a couple of instances which made me establish such connections. Although he had asked me to talk about just 1 instance, but seeing that he was impressed, I requested him if I could talk of another different type of incident which is also very close to my heart. He allowed. I talked about selling a extravagant project to the company chairman. It was
about high-end world class patented Audio-Visual systems. interviewer too seemed to have some knowledge about those equipments and thus we had an interesting conversation. The idea was, I could not only show my Project Implementation Abilities
but also talk about Planning and Inter-personal skills.

The case was about a canned juice manufacturing company. Recently they have diversified into different types/more variety of juices. Their profitability has gone down in comparison to their competitors. Need to analyze, identify root causes and advice solutions to the client. I asked a few ‘Start-Up’ question These are standard questions which you would ask for most of the cases. Some of them were: How big is the client? Has been in the business for how long and how and when has their business strategy changed. Who are the major competitors? What are the various revenue streams (different type of juices et al)? He gave
me some info but asked to assume one standard fruit juice for the analysis. Now I approached in the classical Profitability Case. Profit = Revenue – Cost. Problem can be in either of the two factors i.e. Revenue or Cost. I asked him if he wanted to delve into Revenue or Cost first. He advised to go into the cost aspect (we never visited the revenue aspect). Then he asked me about what are the various cost heads in such a business. This was the key!!! Process Value Chain rules I drew the entire value
chain from Agri-sourcing of fruits to In-Bound Logistics to Warehousing to Processing/Manufacturing to Out-Bound Logistics to Retailer to Customer. Started from the Left Side i.e. Sourcing. He asked me what could be reasons for lower profits. This was more of a brainstorming session. Coming from ITC (of e-choupal fame) background, I could talk the possibility of competitors being able to backward integrate and establish a symbiotic relationship with the farmers which allows them to remove the middle-men and thus get cheaper and good quality fruits, whereas our client has not done that. Then came to In-Bound logistics. I
said that juicy fruits being mostly hygroscopic are prone to damage in transit. Talked about the quality of infrastructure en-route to their plants and quality of logistics partner. Then came the crux: Processing/Manufacturing. At first it was qualitative.
I said the client might have a very high Cost/Unit because of excess/high quality infrastructure. Somehow this turned out to be the case. I realized there has been loads of ‘strategy’ based talking and there bound to be some nos. I asked him and he gave me whole lot of nos. about their manufacturing costs as well as the manufacturing costs of competitors. There was Maths involved here as he gave costs and throughputs of different plants of client as well as competition. Had to find out the average cost per unit of juice manufactured by client vs competition. Of course client’s cost of manufacturing was way higher. Talked about some ways as to how can this be brought down. Talked about economies of scale as some of the assets were under-utilized. This would involve launching other related products as well or expanding markets. There were a few more points discussed but this was merely a closure.
Asked him a couple of questions on his area of expertise and how markets were shaping this year.

I believe that your work-experience (which is a huge factor in your being short-listed) must be reflected in the way you approach cases. Sometime we lose sight of that in following standard structures/frameworks etc. At each stage, I could use my FMCG and Manufacturing experience to come up possible reasons. So, your experiences must show in case solving as well and I believe I did that well. One very interesting thing, right at the start which I think worked well for me. When I entered the room, I generally talked about the view being great from the window. Nal generally asked whose room was it. I had not noticed the name-plate while entering which anyways was covered with a McKinsey signage. Looking at the books and brochures in the room, I
made a guess giving the reason of the guess. It was Bang On!!!

Tips: Do not lose sight of Who you are and What have you achieved till date.This should reflect in your interview. Also, be ready to delve into nos. Interviewer also wants to test your analytic ability with nos. as they back/justify your claims, ideas and suggestions. So ask him if he has nos. when he says “Cost is High” or “Profits are Low” etc.


Round: Case Study Interview
Experience: Just imagine. It does not get bigger than him. The senior most guy in the panel who is rumored to take the final call on each and every candidate. I was to interview with him in my Round-1. Thought this was the do or die situation. Questions started coming right from my CV. We talked about taking up very good projects additional to normal course curriculum, participation in sports, organizing ILS and some other personal focus areas for the year. Then he asked which one of the 3 academic experiences: School,
Undergrad, ISB was most enriching. I split it into 2: Academic and Over- all. Academic was School and Over-all was Undergrad for me. He asked me the reason. Came from the CV plus how it was important for personality development and so on… Then he asked about my work-experience. Talked about CAPEX for a while. What did I do as Capex Manager etc.

Started off with the standard warm-up questions but somehow warm-up questions and interviewer don’t go too well. I asked him about the background of the company/competition/industry. For each question I was quizzed on why did I think that question was
relevant and how will it help me achieve the final objective i.e. valuation. Some useful information which I could generate was that the ‘patent’ was for cotton farming and would increase the yield of cotton growth. Realizing that it was a valuation case, I proposed following the a combination of 3 approaches -(1) Value Based Costing (2) Competition Based Costing (3) Cost-Up; to come up with a band of prices and then zero-in on the final price based on other factors. He agreed. Started with Value i.e. Willingness to Pay on the part of the customer. The customers would of course be cotton formers. Then he asked what is the value of the patent to them? I told him about it increasing the yield and asked if he could provide some nos. on the same. He said it increases the yield per sq. ft by a factor of 50%. Believing that it would again be a complicated nos. case, I took some time and came up with a complicated structure to calculate the monetary value of this increase in yield. It involved numerous factors such as average land-holing, no. of cotton farmers, current yield, cotton prices in the market etc. etc. But rest-assured interviewer cases would rarely involve complicated calculations. He said he was looking for something very simple and asked me to think about it.

Now was the moment of surprise. While I was thinking, he asked me to pause. He said “Oh you have worked in Capex right? How would you reduce Capex Cost in a Power Plant?” I was really surprised. I started off by giving him a ‘structure’ (yeah right!!!) which we need to put in place to identify the scope of reduction in Capex. I came up with various stages involved in setting up and commissioning a power plant which would be:
Engineering Design > Construction of Infrastructure > Procurement of equipments > Electricity Generation > Electricity Distribution & Transmission
Told him we need to look into Capex requirement at each of stage and started off with Engineering Design. Just I as started, he asked me to go back to the Patent Case (strange!!!!)

He wanted a simplified model to calculate the value. After some discussion, we have me the annual turnover of cotton in India and the average price per ton. From this I calculate the value of the higher yield that all Indian farmers could get.

He asked apart from increasing revenue, how else the patent could benefit the farmers. Now with revenue there has to be cost!!! I said the new technology could lead to reduction in costs. Was asked to brainstorm on the costs involved. I told land, water for irrigation etc. Yes the patent reduced irrigation requirements. So I talked about reducing water consumption, associated energy costs as well as the psychological costs and risks associated with reliance on monsoons which could now be mitigated. He asked what else? Very happily I told him that now lesser seeds may be required. He said “The patent is a variety of seeds only”. I realized I had forgotten to ask him what does the ‘patent’ entail….in one of the professor’s language, “is it a bird or a plane”. Should have asked it upfront.

Anyways recovered. Had already found out of the increase in revenue (was asked to neglect decrease in costs) . Told him this would be the maximum willingness to pay and thus the upper end of the price band. He agreed. Asked him the cost of Research and Development. He asked why? Told him this would be the lower end of the price band unless the focus was not profitability but social upliftment. He said the focus was completely profitability and gave me a Net Present Value (assume N). Could not understand it immediately but realized that it is Revenue net the costs. Thus, the price band for the patent (in NPV terms) would be between Rs. 0 to N. He agreed and asked me to zero down on a value. Told him we need to look at similar launches in the past to capture some trend (and therefore arrive at a value in the middle) but he cut me short and said that no need to look at past records and
you would price it at less than N as we need to incentive the farmers to overcome their switching costs by passing on some benefit to them. Now this is what I eventually wanted to say but could not.

Firstly it seemed that Nothing went right. I wasn’t confident interviewing with him. Missed basic pointers and took time to identify the ‘simple’ model he was looking for. And of course couldn’t conclude well. But, later I was told, what went right was the CAPEX piece He wanted to test how good I was with my basics while I was caught up doing something else. Can’t expect this from anyone but him. The framework was bang on!!!

Got a bit psyched. Could not structure the Patent Case well and it was more of bouncing off ideas. But was later told this is what happens with everyone when they interview with him



Tips: Can’t emphasize more the need to be thorough with your work-ex and reflect your experience in the case. And if can, be confident no matter whosoever is interviewing you. Please remember, you know stuff much better than the interviewer. You have slogged your back while working. Who else would know about it better?


Round: Case Study Interview
Experience: I was told that he was very keen to meet me because of my Operations background and was especially interested in my CAPEX experience. I was told that he wanted to know more about what I had done during that stint. So I went in with the frame of mind that I have to maximize on that aspect of my profile. As expected, he asked me thread bare on what all I did in that stint. He did try to intimidate me by passing a few sarcastic remarks but I seemed to enjoy it and gave him appropriate responses. Then he asked a very interesting “What do I expect to hear if I were to go out and ask people randomly what they thought about you?” Now
McKinsey allows you to be innovative, frank and candid in such questions. But these need to be backed by some logic. Told him that different people are entitle to have different opinions about a person and how my different types of interactions i.e. in class, in events, in sports would result in varied responses. Also, talked about the situational aspect of it being 1st Day of placements and how that could influence the responses. Then came up with a few candid ones. You can call me to
find those out

Was told this is a short case and we need to wrap up the case fast. Without asking too many ‘Start-Up’ questions, came to the 3 dimensional approach of (1) Value to Customer (Willingness to Pay) (2) Competition (3) Cost-up. Started with Value. Was asked to brainstorm on how could this service be valuable to the customers? Talked about the qualitative aspects of Mumbai traffic having various tangible and in-tangible costs. Tangible benefits could be saving of time, fuel and fare. Intangible benefits could be alleviation of psychological costs like being stuck in traffic and getting to spend less valuable time at work and office. But he said that fare is something we need to find outThen moved to the 2nd aspect of competition. Asked him what were the other modes of transport available to the public? He said two alternatives existed- Road (Taxi) and Rail (Train). Asked him information on Time and Cost. Don’t remember the exact nos. so will use indicative figures. By Taxi you took 45 minutes and incurred costs (all inclusive- tangible + intangible) of Rs. 250. By train you took 30 minutes and incurred costs of Rs. 50. Now asked him the time supposed to be taken by Hovercraft and how many of them were there. He said that a hovercraft would take 20 minutes to cover the distance. The company was planning to start off with just 1 hovercraft. Asked him it’s capacity and he gave a figure of 100 people. In a flash of rushing ideas, I just said that then there would be a capacity constraint or atleast the hovercraft would always be full as the no. of travelers would far exceed 100 in a busy place like Mumbai. Although at that stage I didn’t know how this was important in the context of the case but he liked it Ok, coming back. Hovercraft saves 25 minutes over Taxi and 10 minutes
over Train. So though would try and valuate in monetary terms this saving of 25 minutes and 10 minutes. But point to note is that cost of Train is much lower than cost of Taxi. So, value based approach if applied on both the modes would result in two different nos. poles apart. He quizzed me how to proceed and after some discussion, I concluded that it made no business sense for the Hovercraft company to compete with the Train and thus focus on competing with the Taxi only. Plus, with only 100 seats, there would be no issue of in-sufficient traffic. Now this is where the earlier remark on capacity made sense At this stage he asked me to synthesize by giving an elevator pitch to the client CEO, asked me to fit it in 1 minute and started looking at his watch saying I was being evaluated for brevity. To wrap up, knowing that he Head the Ops practice, I asked him a question on benchmarking in operations and whether Indian firms are at a stage where they can be set as international benchmarks. Surprisingly,
he asked me why I wanted to know about it. Now this was strange as I had never ever heard of an interviewer asking the interviewee a counter question on the closing question. I told him how we had to benchmark many of our expansion/ up gradation projects with Japanese firms and wanted a view from someone who has been exposed to such facilities across the world. He was satisfied and then answered the question.

Discussion on Work-ex. Taking the sarcastic remarks in the right way and not getting intimidated. Again reflecting the learning from the work- experience in the case.

The synthesis could have been better. His staring at the watch for an accurate 1 min pitch intimidated me a bit. My bad. Otherwise nothing much was wrong here.


Tips: Again, work-ex thoroughness is imperative. Think through carefully the closing questions that the interviewer offers to answer at the end of the interview. The questions need to make sense in the backdrop of your profile, experiences and those of the interviewer.


Round: Case Study Interview
Experience: PI came after a couple of cases. This PI for this interview too was Work- Ex centric. Noshir asked me about the two most important leadership instances in me work life. Again my idea here was to use these 2 instances to reflect upon 2 different sides of my profile. First example was of course on leading one very tough project in a logistically challenging location. The other was captaining my ex-company’s cricket team which comprised of my bosses and super-bosses, and the leadership tools that you need to put into use in such situations.

This 11th interview of the day and for sure the final/most critical interview for McKinsey. When I was given the first case on Tata Nano, he could figure out looking at the time of the day as well as the previous few interviews that all he wanted was a structured brain-storming output on strategic cost management. So, I did not spend any time on asking clarifications/start-up questions and got down to business. As there wasn’t any readymade framework/structure for this, I imagined myself a part of the Nano team and thought through the sequence in which the material design and procurement process would unfold (of course, again, my experiences at work came in very handy). After a minute of thought, I started off by saying that this was a huge cut- down on costs (by almost 75%) thus process improvements won’t suffice and that it called for radical changes in the design and sourcing
processes. I then recommended that we should first identify the various steps that are involved in the material procurement process and see how we can cut costs in each one of them. The sequence I gave was as
follows: Technical Specifications of the car and individual parts and sub- assemblies > Preparing the Bill of Materials (BOM) > Identification and Selection of Vendors > Negotiations > Logistics- Freight, Duties, Insurance, Taxes etc.
I asked interviewer for his approval which he promptly gave. Then I brain- stormed various ways of cutting costs as each step. Car & Parts Specifications: Car to adhere to the bare minimum specifications that are needed to be adhered to by Indian Rules n Regulations (E.g. no need to Euro N+1 of Euro N compliance required in India) but without compromise on the safety of the passenger which is very important. Technical Specifications of the parts and sub-assemblies to the at Minimum Technical Standard (MTS) i.e. the specs need to be just right to meet the requirements. Bill of Materials (BOM): Talked about how big companies being risk averse always order and add extra quantity of material in everything. E.g. if 90 kgs steel is required, they would order 100 kgs and end up putting 95 kgs. So optimal safety margins need to defined and be adhered to.

Identification and Selection of Vendors: Said for experience that big companies chose their trusted and loyal vendors, and are inclined to have single vendors/point of contact for a plethora of related/unrelated materials. This way, the vendors make unnecessary money. Thus, to manufacture the lowest car in the world, we need to rationally evaluate vendors outside our ‘Zone of Comfort’ and their scope of supplies. Talked for Chinese manufacturers that give both cost and quality advantage. Gave examples of some proposals of Chinese manufacturers that I had evaluated at work.

Negotiations: Then, negotiate hard. Tata can use their buyer power (multiple businesses and product lines) to fleece suppliers but showing them possible opportunities in near future.

Logistics- Freight, Clearance etc.: This was a pure brainstorming session with ideas like bulk shipments through sea (no part shipments, air freights etc.). Centralized insurance and clearance agents. Managing road taxes etc.

Finally, something which is typically talked of in manufacturing: INNOVATION. Although setting Indica as an anchor and working backwards is a possible way, big companies like Tata Motors which have presence in the foreign markets (talked of JLR) and access to the modern technology, must mobilize their R&D department to come up with revolutionary car design. Some cutting-edge innovation like a new cost effective fuel injection system etc.

Whoa…I spoke for 20 minutes continuously non-stop. Did not find the need to. Interviewer was constantly smiling and acknowledging.

Then straight away came the second case on IT. Now that’s his area of expertise and he wanted to check if I could talk about an industry I had no prior exposure to. I came to know later that this case was in some past year’s case-book and he had written a paper on that. Anyways, I had missed both

Again I gave the initial questions a pass and asked for a minute to think. Exhausted after so many cases, I was not able to recollect any jazzy structure and thus stuck to the basics. I proposed looking at 2 types of factors/aspects which need to looked at by the IT giants of the country:

(1) Internal Factors (2) External Factors
Told him that I am a firm believer that any individual/organization should first have an internal introspection and then start looking at the world outside. Thus would recommend starting off with the Internal Factors. He smiled and said go-ahead. Internal factors meant how the company needs to improve and uplift it-self internally. Said the biggest assets for IT firms were its people and it boiled down to (a) Productivity (b) Innovation. (a) Productivity: Here, I used Productivity = f(Efficiency, Effectiveness) .
Explained the difference between the two and gave some ideas on how IT industries could do that. We could see Microsoft from one of the windows so pointed out and shared some experiences I had while I was visiting an old friend there. (b) Innovation: This is always a safe point to say. No one will challenge Innovation as an improvement measure . Talked a bit about Google
and how it fostered a culture of innovation. This again translated to sound people practices. (2) External Factors: Here I used the 3 Us framework in disguise- Users, Uses, Usage. Users: Talked about IT firms expanding their user base in India and un-explored markets abroad. Once, once you get an organization on-board, need to penetrate to all departments. Uses: Used some SAIT funds to discuss codability and measurability. How certain key process where still handled by companies internally (gave example
from work) as they were highly strategic in nature. Here, IT firms could built enhance their credibility and build stronger partnerships with the clients. Usage: Discussed growth through service provisions like AMCs etc. And then of course, Mergers and Acquisition abroad. Talk of more and more Indian firm’s expanding outside India through this medium and how biggies like Infosys, TCS etc. could continue this trend. This was followed by PI (explained above). Asked him a couple of questions on increasing role of consultants in assisting implementation and talked about the trend at McKinsey.

By this time, I was a robot. This was the best interview of the day for me.

Tips: If you can’t immediately think of established structures, just think-through the logical sequence of events. If you were there in that situation then how would things unfold stepwise? Then within each step, mini- structures/frameworks would readily fit. Again, give examples from work- ex and life beyond work. If you have seen something happen, it has to be true. Also, industry awareness is important to some extent. Reading a Financial Daily for 2 months before the interviews if enough.


Skills:
College Name: Indian School Of Business (ISB)