BI engineer interview questions

As asked

Sales, finance, and product all have different revenue numbers in dashboards. How would you build a semantic layer that people trust?

Sample answer outline

Start by reconciling definitions with stakeholders: booked revenue, recognised revenue, net revenue, refunds, tax, and currency conversion are different metrics. Encode canonical definitions in a semantic layer or governed BI model rather than in dashboard-specific SQL. Add ownership, documentation, tests, certification status, and a change process so metric changes are deliberate. Keep enough flexibility for exploratory analysis without letting every team redefine company metrics. Strong BI engineers know this is partly technical and partly governance, because trust is lost through unclear ownership as much as bad SQL.

Expect these follow-ups

• How do you handle a board dashboard that uses an old definition?
• When should a metric be certified?
• How do you stop the semantic layer becoming a bottleneck?

As asked

An executive dashboard takes 45 seconds to load every Monday morning. How do you make it fast and reliable?

Sample answer outline

Profile the dashboard queries first and identify whether the bottleneck is warehouse compute, BI tool rendering, filters, joins, or too many tiles refreshing at once. Pre-aggregate stable metrics into marts or summary tables, partition and cluster large fact tables, and cache results where freshness requirements allow it. Reduce unnecessary cross-filtering and dashboard-level queries that scan the same table repeatedly. Add freshness indicators so executives understand the tradeoff between live data and fast data. Candidates often only increase warehouse size, which can hide modelling problems and raise cost.

Expect these follow-ups

• Which dashboard tiles should not be live queries?
• How do you decide between materialised views and scheduled summary tables?
• What freshness SLA would you set for an executive revenue dashboard?

As asked

Regional sales managers should only see their own region's deals, while executives can see everything. How would you implement and test this in BI?

Sample answer outline

Model entitlements explicitly: user or group to permitted region, with executive groups mapped to all regions. Enforce row-level security in the BI semantic layer or warehouse policies, preferably close to the data if multiple tools consume it. Test with impersonation, fixture users, and negative cases so a dashboard change cannot bypass the rule. Handle edge cases like managers covering multiple regions, historical territory changes, and shared accounts. Strong answers consider privacy and auditability, not just a filter on a dashboard.

Expect these follow-ups

• Where would you enforce RLS if both Looker and ad-hoc SQL users need access?
• How do you test that a user cannot bypass the filter through drill-downs?
• What happens when a salesperson moves region mid-quarter?

As asked

Marketing wants dashboards optimised around leads, sales wants pipeline, and customer success wants retention. How do you design a BI reporting suite that supports all three without fragmenting truth?

Sample answer outline

Separate shared company metrics from team-specific operating metrics. Build a small set of certified cross-functional measures, then let each team have dashboards that answer their workflow questions against the same governed dimensions. Facilitate metric definition sessions where tradeoffs are explicit, especially around attribution, ownership, and time windows. Document which dashboard is authoritative for which question, and retire duplicates that disagree. Interviewers want evidence that the BI engineer can mediate ambiguity rather than simply build every requested chart.

Expect these follow-ups

• How do you push back on a VP asking for a misleading metric?
• What makes a dashboard worth retiring?
• How do you measure whether the reporting suite is actually being used well?

As asked

A downstream analyst reports that yesterday's signup count doubled. The source system says it emitted the correct number of events. How do you investigate?

Sample answer outline

Trace the count through each pipeline boundary: source extract, landing zone, staging table, transform, and mart. Look for retry behaviour, non-idempotent appends, joins that multiply rows, and missing uniqueness tests on event_id or user_id plus timestamp. Use run metadata to identify whether the same file or API page was processed twice. Fix the immediate data issue with a controlled dedupe or partition rebuild, then add tests and run-level safeguards. Interviewers want calm, evidence-based debugging rather than guessing that the analyst's query is wrong.

Expect these follow-ups

• How do you distinguish duplicate source events from duplicate loads?
• What test would have caught this before the analyst did?
• How do you explain the corrected metric to business users?