Join my list to get latest updates on articles and courses.
It's been more than a month since I last uploaded a course. I am glad that more than 130,000 students have taken up my databases courses. I keep a close eye on the Udemy forums and my Udemy Inbox for your questions and doubts and try to reply to each and every one of you. One of the top questions asked was Interview Tips for different Database job roles.
Frankly, database jobs are hugely popular these days. However there are a few different job areas to look for. Some of them are:
- Database / Reporting Analyst / Business Analyst
- BI Engineer / Database Engineer / ETL Developer /BI developer.
- ML Scientist / Data scientist.
Database / Reporting Analyst / Business Analyst
Each company has different job descriptions that may overlap one another. But majorly, I have seen that a Reporting Analyst typically will need to have basic to intermediate SQL Skills, learn the domain well and be thorough with at least 1 visualization tool. Commonly in interviews you will be asked to analyze a situation. Some of the common situations that you may find online are “I am launching a new initiative for a large high traffic company. How do i go ahead an estimate my market size?” or it could be even very common scenarios like “how do you estimate the number of beer bottles in the US” or “How many window panes are there in the US”. Remember the goal is not to provide a correct answer. Infact, even if you provide an exact 100% right answer, the interviewer won’t be impressed, if you didn’t ask the right questions or walk him/her through the thought process.
Yes, I say thought process for a reason. Frankly, they look for how well you can think and analyze a situation. Mainly you need to uncover the unknown, because that’s what you will be doing in your job. For example, if someone asks me how many milk cartons are sold in Europe, you can ask the interviewer things like - “what is the average size of a milk carton?” , “what is the average consumption / household” etc. Remember even the interviewer won’t have these stats - so you should assume them. It's okay to even say let ‘x’ be the average household size, then finally arrive at an equation. Be rational and walk through your steps. Be interactive!
You might also be asked about TSQL questions. Many companies expect that you should be able to write basic queries and get the data you need. I would focus on the following:
- Basic SELECT queries, nested queries, joins, aggregations, conditions.
- Stored procedures
- Transferring some basic data from source to destination (Imagine a situation where you are given web traffic data from Google analytics. Its very easy to export that in Excel and write a 1 line script to load that into a SQL server and analyze the data)
The next one to aim for is communication skills. By communication skills, I don’t really mean how well and grammatically correct english can you speak. What interviewers look for is - can you tell a simple story of your analysis without the technical jargons? Can you cater your message depending on the audience? For example, if the interviewer asks, explain your last project - don’t use acronyms that may be confusing from the interviewer. Explain what problem you were trying to solve and how you solved it? How did you message your solution for adoption? I have seen many folks tell me - “I handled a $50 Million Dollar project”.
Best way to practice is - “Explain concept of joins to your grandmother” , “now explain the concept of joins to your english teacher”, “now explain the concept of joins to a database architect”. If you see, it should be the same message, but conveyed with different levels of details.
BI Engineer / Database Engineer / ETL Developer /BI developer:
This is a little tricky part. I would suggest you to read the job description thoroughly. For instance, even though a DB engineer mainly works on advanced database querying etc., I have seen that title given to a DBA as well. So here is how i would dissect. You can refer the relevant section that closely matches with the job description.
Database Querying: Almost all roles in this section would require you to be an expert in T-SQL (or querying skills). You will most probably be asked to whiteboard queries and will be asked to explain. You need to pay special attention to how querying works and executes and how to write the most efficient queries. Interviewers will mainly look for these things. All features/functions with new releases should be at your fingertips. You should be in a position to take an existing query and optimize it. You should have a good understanding on query plans (or related features for other technologies). I would say, web search for some practice problems and try it yourself. You should also ask relevant questions to the interviewer to make sure you showcase your understanding. For example, if you get a problem that need you to join and query 50+ million rows, ask questions like what kind of indexes are present? How is it partitioned? What are the hardware specs? Try recommending / tuning this setup - then show your query. This will exhibit your strong understanding of querying, architecture and hardware. Take your time, ask the right questions and deliver the most beautiful code. It's okay to draw a few tables on the whiteboard for clarity. At the end - remember to summarize your query in plain english.
Recommended book: T-Sql Querying
Basic DBA knowledge: Not all companies will have a dedicated DBA pod. So it's a good idea to get some basic understanding on common DBA tasks. Refer a good book on basic tasks. This will help you fine tune your work and gain some knowledge on internals as well. Remember this will not be 100% of your daily job duties. Also be ready to answer vague questions like “your query is running too slow, how do you debug?”
Advanced DBA knowledge: I personally don’t have much knowledge in this space, but this position will require you to have a deep understanding of all the DBA administrative tasks.
ETL / Data-warehouse engineer: This position will require the first 2 skillsets and some more. As an ETL Engineer you are required to know basic frameworks and best practices in transferring data. You need to know how to maintain consistency and speed while transferring data. There are lots of tools which help you do this. Some of them are SQL Server Integration Services (SSIS), Informatica etc. You should have a sound understanding of cleaning up data, working with upstream data, data transfer techniques and effective deployment techniques. You should also know how to install and configure drivers to connect to these upstreams. Many of the upstreams will need for you to write some custom code. It’s always a plus if you know Python or C# - that way, you don’t need to rely too much on the tool, but rather write your own component as needed. A typical scenario would be - transferring CRM data from say, Salesforce cloud into a database.
You should have a sound understanding on how to make this data reportable. What i mean is that the upstream data cannot be used as it is - it needs to be massaged. Have a strong understanding of merging and transforming techniques. As a warehouse developer, you should know Kimball strategies, slowly changing dimensions and so on. You should have a strong understanding on Fact and Dimension tables. Basically you should have a good knowledge on how to get the most upstream data to an analyst, quickly and correctly for further analysis.
Data scientist / ML Scientist: Many companies require different levels of knowledge for this role. I would say you definitely need to know a little querying (since it simplifies a lot of your day to day work). Mainly a candidate should have sound mathematical knowledge and knowledge on Statistical algorithms. There are many software solutions like R, Python, SAS etc. that help in creating statistical models. If you are aiming for this kind of a job, I would suggest you get a good hang of linear/logistic regressions, Classification problems etc. You need to be thorough in at least 1 statistical software (like Python). A good gauge might be to participate in www.kaggle.com contests.