Written by Austin Gorsuch
In today’s fast-paced world, more businesses are becoming more data-driven in order to remain competitive. One field that has emerged from this constantly changing landscape is business intelligence, which contains many potential careers and roles, such as the business intelligence analyst and business intelligence engineer.
Let's break down the business intelligence field into its roles, the structure of its interview process, and the types of interview questions you’re likely to encounter, so that you can enter the process prepared and ready to face the challenges that await.
What Is Business Intelligence?
In simple terms, business intelligence, or BI, is the practice of applying insights from data to the problem of running an enterprise business. This field is split between two common careers (BI analysts and BI engineers).
While there is some overlap between the two roles, a BI engineer mainly constructs and maintains the data pipeline that a BI analyst uses to deliver insights to their employer. Therefore, BI engineers have a more technical role than BI analyst and require specialization in data storage and the ETL (Extract, Transform, Load) process.
However, there are skills that are widely applicable to any business intelligence role. A strong background in business, economics, statistics, or a related field is a standard requirement. In general, anybody working in the field of business intelligence will be expected to have a strong background in at least one of the following coding languages:
- Python (especially pandas, Matplotlib, Seaborn, and Numpy libraries)
Interested in learning SQL? Check out our SQL course on Interview Query today.
In addition, positions usually require the following BI tools:
- Microsoft BI
Beyond having the technical expertise to derive insights from large amounts of data, it’s important for people in business intelligence to be able to communicate their insights to people who may not be familiar with data science.
Therefore, a BI engineer or BI analyst should invest some time into developing good communication skills, specifically in terms of explaining complicated concepts in layperson’s terms. An insight that cannot be effectively communicated serves no one.
That having been said, the business intelligence interview will test for all of the above skills to determine whether someone is a good fit for a particular role.
The Business Intelligence Interview Process
The business intelligence interview follows the standard interviewing process, including the phone screen, the technical interview, and the onsite interview. Each stage weeds out candidates with different criteria. For instance, the technical interview separates people who meet the basic qualifications from those with deeper technical experience. Here’s what to expect from each stage in the business intelligence interview process:
Initial Phone Screen
This interview will likely be held with a hiring manager or recruiter. The phone screen will generally involve topics such as your experience in the field of business intelligence, your resume, and any role-specific requirements that a company may have. It’s unlikely that anything profoundly technical will come up during this interview, so our best advice is to relax and do your best to represent your experience and qualifications to the company as well as possible.
This will likely be another, more in-depth phone interview where you’ll be expected to demonstrate your technical expertise in the field of business intelligence. You can expect to encounter topics like:
- Basic SQL knowledge
- Basic Statistical Modeling
- Data Warehousing
- Chart Types
How difficult the questions are and how many you’ll be expected to answer depends largely on the specific nature of the position you’re applying for. In the Amazon Business Intelligence Engineer interview, for instance, you are asked five SQL questions of increasing complexity as the technical portion of the interview continues.
Learn more about the Amazon Business Intelligence Engineer interview here:
The final part of the interview is the onsite. At this point in the interview process, you’ve already established that you’re qualified and that you know, in the most general sense, what you’re doing. Now, the company is trying to gauge how you go about solving problems so that they can determine whether you’re a good fit for their company.
You’ll encounter a lot of behavioral questions meant to weed out candidates who may not work well with others or struggle in a complicated problem solving environment. You could encounter technical questions related to the specific problems the business is currently facing.
The important thing when answering these questions is not necessarily demonstrating that you know what to do specifically, but that your problem solving strategy is compatible with what the company is already doing. At this stage in the interview process, it’s important, if possible, to familiarize yourself with the company’s values and the goals of the team you’ll be joining.
Larger companies may have multi-stage onsite interviews, which involve multiple interviewers and rounds, meaning that you may spend the better part of a day interviewing with members of various teams, other business intelligence professionals, etc.
Types of Business Intelligence Interview Questions
What is X concept? What is the difference between X and X?
This type of question is meant to test your knowledge of specific business intelligence concepts. You could be asked to explain what “benchmarking” or “normalization” entail, or discuss the different types of data warehousing. These questions are intended to establish that you have a basic working knowledge of what business intelligence actually means. So relax, keep your answers short, simple, and to the point, and you’ll do fine.
Scenario Driven Questions
These sorts of questions ask you:
- “What would you do if…”
- “Tell me about a time when…”
They're designed to assess your judgment and problem solving abilities, as well as test your experience in dealing with business intelligence scenarios. More important than being able to give the “correct” response to the scenario is your ability to talk through your thought process when confronting a novel problem. Make sure your analysis is clear, thorough, and relevant to the scenario at hand and you’ll do fine with this type of question.
For instance, if you’re asked a question about a time when you encountered a business intelligence problem you had difficulty solving, first lay out the specifics of the problem, then talk through your thought process (whether you think it’s ‘right’ or ‘wrong’) when it came to solving the problem, then discuss your final course of action. Remember that the goal, here, is to see how you deal with complex situations, not necessarily to gauge your ability to solve the problem (which is what the technical and coding portions of the interview are meant to determine).
During the technical interview and onsite interview, you will have to complete some coding exercises to prove your proficiency in the technical aspect of business intelligence. This is especially true of SQL, where the lion’s share of the technical work occurs. You may be asked to write any number of queries, some more relevant to the company you’re applying to than others.
An example of a SQL problem you might encounter at a company like Microsoft is:
Employee Salaries (ETL Error)
'employees' table +---------------+---------+ | column | type | +---------------+---------+ | id | integer | | first_name | string | | last_name | string | | salary | integer | | department_id | integer | +---------------+---------+
Let’s say we have a table representing a company payroll schema.
Due to an ETL error, the employees table did an insert, instead of updating the salaries every year when doing compensation adjustments. The head of HR still needs the current salary of each employee.
Write a query to get the current salary for each employee.
Assume no duplicate combination of first and last names. (I.E. No two John Smiths)
Here’s a hint:
The first step we need to do would be to remove duplicates and retain the current salary for each user.
Given we know there aren't any duplicate first and last name combinations, we can remove duplicates from the employees table by running a GROUP BY on two fields, the first and last name. This allows us to then get a unique combinational value between the two fields.
Next, here’s an example of a question you might encounter involving Python:
Given a list of timestamps in sequential order, return a list of lists grouped by week (7 days) using the first timestamp as the starting point.
ts = [ '2019-01-01', '2019-01-02', '2019-01-08', '2019-02-01', '2019-02-02', '2019-02-05', ] output = [ ['2019-01-01', '2019-01-02'], ['2019-01-08'], ['2019-02-01', '2019-02-02'], ['2019-02-05'], ]
Here’s a hint:
This question sounds like it should be a SQL question doesn't it? Weekly aggregation implies a form of GROUP BY in a regular SQL or pandas question. In either case, aggregation on a dataset of this form by week would be pretty trivial.
But since it's a scripting question, it's trying to pry out if the candidate deal with unstructured data. Data scientists deal with a lot of unstructured data.
In this function we have to do a few things.
1. Loop through all of the datetimes
2. Set a beginning timestamp as our reference point.
3. Check if the next time in the array is more than 7 days ahead.
a) If so, set the new timestamp as the reference point.
b) If not, continue to loop through and append the last value.
Finally, let’s take a look at a systems design question that you might encounter.
Modifying A Billion Rows
Let's say you have a table with a billion rows.
How would you add a column inserting data from the original source without affecting the user experience?
Here’s a hint:
Before jumping into the question, we should remember to clarify a few details that we can potentially get out of the interviewer. It helps to ask questions to understand problem and show that you can think holistically. Rushing too fast into a solution is a red flag for many interviewers.
Given the problem statement, let's clarify a few questions.
1. What is the potential impact of downtime?
Always remember to get more context out of the question. When we're talking about a table with a billion rows, is this a scenario where that table ispowering an entire company– in which downtime would affect thousands of dollars in sales– or is it just an offline analytics events table that would cause impact to a few internal employees?
It's crucial to ask the interviewer and assess the potential effects of downtime for seconds, minutes, and hours. Figuring out the impact is imperative to determining our strategy going forward.
Interviewer: Let's say downtime is bad. This table is powering Amazon's product catalog and so we can't afford any downtime.
2. What kind of database is it?
For a Postgres table, we can use a default value field that we can leverage that allows us to insert a column without writing to the table.
Some additional questions that you might encounter during the interview process include:
- Draw a Poisson Distribution.
- How many kinds of join are there in SQL?
- What is a pivot table?
- How would you write a SQL query to compute the second highest salary of an employee at a company?
- How many taxi bookings occur in Mumbai in a day?
- Compute the top ten best performing airline routes based on the following conditions.
- How would you parse data from one server to another?
Like any other interview, the business intelligence interview combines technical elements with problem-solving assessments.
Whatever position you’re interviewing for in the field of business intelligence, if you're still wondering how you can best prepare yourself for the interview process, check out our questions from real interviews at some of the biggest and most sought-after companies on the job market.
Thanks for reading!
We hope that you found the article helpful. If you did, subscribe to the Interview Query blog to receive weekly updates and new articles to keep you up-to-date on the state of the data science profession and loaded with tips and tricks to keep you one step ahead of the competition.
If you're currently in the process of interviewing for a job in the data science field, or just looking to brush up on your DS skills, consider signing up for a free account on Interview Query for access to our question bank filled with real interview questions from companies like Google, Facebook, Amazon, and more. Our questions are sorted by question type, position, and difficulty to make sure that you're practicing exactly what you need to practice to level up your skills and ace your next interview.
Finally, if you're looking for an all-inclusive starter course for data science, look no further than the Data Science Course on Interview Query. Covering subjects ranging from Product Intuition to Machine Learning to SQL, our courses will bring you up to date on the things you need to know to become a data scientist.
Take the next step in your data science career. Try Interview Query today.