BI engineer analytics 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, recognized 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

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?