Written by Austin Gorsuch
In today’s fast-paced, data-saturated 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 business intelligence analyst and business intelligence engineer.
If you’re looking to start (or continue) a career in the field of business intelligence, we here at Interview Query have broken down the business intelligence field into its roles, the structure of its interview process, and the types of questions you’re likely to encounter in an interview, so that you can enter the interview 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 is typically charged with constructing and maintaining the data pipeline that a BI analyst uses to deliver insights to their employer. BI engineer is therefore a more technical role than BI analyst and will require a more specialized knowledge of the problems relating to 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, they will be expected to be familiar with at least one of the following BI tools:
- Microsoft BI
Finally, beyond the problem of possessing the technical expertise to derive insights from large amounts of structured and unstructured 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
In general, the business intelligence interview is split into three parts: the phone screen, the technical interview, and the onsite interview. Each stage in the interviewing process is intended to weed out candidates with different criteria. A technical interview, for instance, is designed to weed out candidates who may have basic qualifications for the position but lack the deeper technical expertise to make them effective in the role. Here’s what to expect from each stage in the business intelligence interview process.
Initial Phone Screen
This small(er) 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 third portion of the interview, if you’ve passed the phone screen and the technical interview, is the onsite interview. At this point in the interview process, you’ve already established that you’re qualified and know, in the most general sense, what you’re doing. Now, a 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.
Here, you’ll encounter a lot of behavioral questions meant to weed out candidates who may not work well with others, or may struggle in a complicated problem solving environment. You may encounter technical questions that relate to the specific problems the business is already facing.
The important thing when answering these questions is not necessarily demonstrating that you know specifically what to do, 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.
The structure of the onsite interview will vary from company to company. Some common roles that you might apply for at prominent companies include:
- Amazon Business Intelligence Engineer
- Google Business Analyst
- Facebook Business Analyst
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 interview question is intended to test your knowledge of specific business intelligence concepts. You might be asked if you understand what the terms “benchmarking” or “normalization” entail, or to explain 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 will ask you:
- “What would you do if…”
- “Tell me about a time when…”
They are intended 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 be asked to complete short (or sometimes more in-depth) coding exercises to prove your proficiency in the technical aspect of business intelligence. This is especially true of SQL, which is where the lion’s share of the technical work in the business intelligence discipline 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?
We always have to remember to get more context out of the question. In this scenario when we are talking about a table with a billion rows, this could be a table that is powering an entire company in which downtime would affect thousands of dollars in sales or could just be an offline analytics events table that would cause impact to internal employees.
It's crucial to then mention to the interviewer and assess, what is the potential effects of downtime for seconds, minutes, and hours. Figuring out the impact is pertinent then 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. You can get started with your free Interview Query account today.