Microsoft Business Intelligence Analyst - Junior Level Interview Preparation Guide (2026)

Direct answer

"The numbers don't make sense" is a critique of correctness, not taste, so the first move is to resist the urge to explain or defend the report before you know what they actually mean. Ask specific clarifying questions to pin down the exact number and expectation, investigate methodically rather than guessing, then deliver the resolution in the stakeholder's language and document it so the same confusion does not resurface.

Structured elaboration

Clarify before touching anything. A vague complaint like this usually hides a specific mismatch. Ask: "Which number specifically, the total, the trend, a single row?" and "What were you expecting to see instead, and why?" Getting a concrete anchor (their mental model of what the number should be) turns a fuzzy complaint into a testable hypothesis.

Investigate systematically. Once you know the specific number and the expected value, check the usual sources of a real discrepancy: definitions (does "revenue" mean gross or net to them versus to the report), filters and time windows (is a date range off by a day due to timezone handling), duplication (a join that fans out rows), and staleness (a source table that has not refreshed). Reproduce the number from source data yourself rather than trusting the report's own logic, since the bug (if there is one) may be in the exact code that produced the number you are checking.

Deliver the resolution. If you find a real bug, say so plainly, show the corrected number, and explain in the stakeholder's terms what went wrong, not in pipeline internals they were never meant to parse. If the number turns out to be correct and the mismatch was a misunderstanding of definitions, explain that just as plainly and without condescension. Either way, close the loop with the same person who raised it.

Document the resolution. Add the clarified definition to a shared glossary or the report's own documentation, and if it was a genuine bug, note the root cause and the fix in a changelog. The goal is that the next person who asks "why does this number look off" finds an answer without repeating your investigation.

Worked example

Say a marketing lead flags that "the numbers don't make sense" on a weekly revenue dashboard, saying it looks too low. Clarifying question: "Are you comparing this week's total against last week's dashboard, or against a number from finance?" They say finance's number is noticeably higher. Investigating: pulling the raw transactions shows the dashboard is filtering by a timestamp that uses UTC, while finance's report uses local business-day cutoffs, so a chunk of Friday evening transactions is landing in the following week's bucket on the dashboard. That is a real, reproducible discrepancy, not a perception issue. The fix is to align the dashboard's day boundary with finance's convention. Delivering the resolution means telling the marketing lead, in plain terms, "the dashboard was cutting the day off a few hours early due to a timezone setting, it's now aligned with finance's cutoff, and this week's number reflects the fix." Documenting means adding a note to the dashboard's definition page: "day boundary = local business day, matches finance convention," so a future viewer does not raise the same question.

Trade-offs and pitfalls

Jumping straight to defending the report's logic before asking a clarifying question reads as defensive and often wastes time investigating the wrong thing. Assuming the stakeholder is simply wrong is just as risky as assuming they are right; treat the claim as a hypothesis to test either way. Explaining the fix in overly technical terms (join logic, pipeline stage names) leaves the stakeholder unable to judge whether the resolution actually addresses their concern. Skipping documentation is the most common failure: the fix holds until the next person hits the same ambiguity and the whole cycle repeats.

Approach: show ISO-week (weeks start Monday; ISO week-numbering year) and a Sunday-start week-number (week starting Sunday) in Postgres and BigQuery for date '2024-01-01', then explain difference and recommendation.

Postgres (ISO week via to_char 'IYYY'/'IW', Sunday-start via date_trunc + extract):

SELECT
  date '2024-01-01' AS dt,
  to_char(date '2024-01-01', 'IYYY-IW') AS iso_year_week,        -- ISO year-week
  extract(week from date_trunc('week', date '2024-01-01' + 1))::int AS sunday_week_number; -- Sunday-start: shift then truncate

BigQuery (ISO via FORMAT_DATE/ISO_WEEK, Sunday-start via EXTRACT with WEEK(MODE)):

SELECT
  DATE '2024-01-01' AS dt,
  FORMAT_DATE('%G-%V', DATE '2024-01-01') AS iso_year_week,        -- ISO year (G) and ISO week (V)
  EXTRACT(WEEK FROM DATE '2024-01-01' WEEK(MONDAY)) - 0 AS monday_week_number,
  EXTRACT(WEEK FROM DATE '2024-01-01' WEEK(SUNDAY)) AS sunday_week_number;

Result explanation: For 2024-01-01 (a Monday), ISO week = 2024-01 (ISO assigns week 1 to the week with Jan 4). A Sunday-start method may place the date in week 1 or week 0 depending on implementation — definitions differ and can show 2023-week numbers for dates near year boundaries.

Recommendation: Use ISO-week for global reports because it's standardized (ISO-8601), consistent across locales, and avoids ambiguous year-boundary behavior. If business requires US convention, document and stick to Sunday-start consistently.

Direct answer

When a small number of superusers dominate an average behavioral metric, the average stops representing a typical user, so robust reporting means leading with metrics less sensitive to extreme values, such as the median or a specific percentile, and reporting alternative cuts like top-decile share separately rather than folding everything into one mean.

Structured elaboration

A mean is pulled arbitrarily far by a small number of extreme values because it weights every unit of activity equally regardless of who generated it; a median is far more robust because it only cares about the middle of the distribution's ordering, not the magnitude of the extremes. For behavioral data specifically, a useful complementary pair is the median (what a typical user looks like) alongside a measure of concentration, such as what share of total activity comes from the top decile of users, since that number directly answers "how dependent is this metric on a small group" in a way neither the mean nor the median does alone. When stakeholders need to see the effect of a change on power users specifically, reporting a percentile (p90 or p99) alongside the median gives visibility into that population without letting it distort the headline number.

Worked example

import numpy as np
np.random.seed(5)

n = 1000
activity = np.random.poisson(5, n).astype(float)          # most users: modest activity
superuser_idx = np.random.choice(n, size=int(0.02 * n), replace=False)   # top 2%
activity[superuser_idx] += np.random.gamma(shape=2, scale=150, size=len(superuser_idx))

print('mean:', round(activity.mean(), 2), ' median:', round(np.median(activity), 2))
top_decile_share = activity[np.argsort(activity)[-100:]].sum() / activity.sum()
print('top-decile share of total activity:', f"{top_decile_share:.1%}")
print('superuser mean:', round(activity[superuser_idx].mean(), 1),
      ' non-superuser mean:', round(np.delete(activity, superuser_idx).mean(), 2))

Simulating 1,000 users' weekly event counts, where most users generate a modest, Poisson-distributed amount of activity but the top 2% (20 users) are superusers with much higher activity, gives a mean of 12.24 events per user but a median of only 5.00, a mean over twice the median. The top 100 users (the top decile) account for 65.5% of all activity in the simulation, and the 20 identified superusers alone average 361.8 events each, compared to 5.11 for everyone else. Reporting "average weekly engagement is 12.24 events" without qualification would meaningfully overstate what a typical user actually experiences, since more than 90% of users generated fewer events than that average.

Trade-offs and pitfalls

The main pitfall is reporting only the mean, or only the median, since each hides something the other reveals: the mean alone hides how unrepresentative it is of a typical user, and the median alone hides how much of the metric's total the tail contributes, which matters for questions like revenue or infrastructure cost that scale with total activity rather than with a typical user's activity. A second pitfall is treating superusers purely as a reporting nuisance to smooth away, when in practice they are often the most valuable and highest-retention segment of the user base, worth understanding and serving deliberately rather than only excluding from headline metrics.

Direct answer. Write the join condition so a NULL on the left side's key part is treated as an automatic match against ANY value on the right (l.key2 = r.key2 OR l.key2 IS NULL), and keep that wildcard logic in the join predicate itself rather than trying to fake it by coalescing NULL to a literal placeholder string, which risks colliding with a genuinely real value.

Structured elaboration. The two halves of this question are really two sides of the same judgment call about what NULL means in a composite key. Sometimes NULL genuinely means "matches anything" (a wildcard, a business rule saying "this row applies regardless of the second key part"), and an explicit OR-with-IS-NULL clause in the join condition expresses that intent directly. Other times NULL means "unknown, do not guess," and coalescing it to a literal sentinel like the string 'NULL' to force a match is dangerous specifically because that sentinel could ALSO be a legitimate value already present in the data (a SKU literally named "NULL" as a placeholder product code, say), silently merging two things that were never meant to be the same.

Worked example. left_table(id, key1, key2, value): (1, 'US', NULL, 'v1') (a wildcard row for all of key2 under 'US'), (2, 'US', 'east', 'v2'). right_table(key1, key2, price): ('US', 'east', 100), ('US', 'west', 120).

SELECT l.id, r.key2, r.price
FROM left_table l
JOIN right_table r
  ON l.key1 = r.key1
 AND (l.key2 = r.key2 OR l.key2 IS NULL)
ORDER BY l.id, r.key2;

Result: (1, 'east', 100), (1, 'west', 120), (2, 'east', 100). Row 1 (the wildcard, key2=NULL) correctly matches BOTH right-side rows since its key2 is NULL, while row 2 (key2='east') matches only the one exact right-side row it specifies.

Trade-offs and pitfalls. The danger case for the counterpoint: imagine a real SKU is legitimately coded as the literal string 'NULL' by an upstream system's bug or convention. If you'd instead written COALESCE(l.sku, 'NULL') = COALESCE(r.sku, 'NULL') to make actual NULLs match each other, that real SKU would now be silently treated as identical to every genuinely-missing SKU, merging unrelated rows. The wildcard pattern above avoids this entirely because it never manufactures a fake value to compare against; it just tests IS NULL directly. Before implementing either pattern, confirm with whoever owns the business rule which meaning NULL is actually supposed to carry here, since the two interpretations produce very different (and both silently plausible) results.

Situation: I joined a BI team with many conflicting metric definitions (e.g., “active user”, “churn”, “MRR”) and an MVP release scheduled in 4 weeks. The product must ship, but core metrics need standardization without blocking release.

Plan (pragmatic, time-boxed):

1. Triage & scope (Days 0–3)

• Identify 6–8 core metrics required by MVP reports (stakeholder ask + analytics usage).
• Categorize conflicts: definition drift, calculation variance, ETL timing, naming.

2. Produce canonical definitions (Days 3–10, parallel work)

• For each metric, create a one-page canonical spec: name, business intent, formal SQL definition, data source(s), calculation window, timezone, cadence, owner, examples.
• Store specs in a lightweight metadata repo (Markdown in git or a simple catalog) linked to BI tool lineage.

3. Ensure backward compatibility (Days 10–18)

• Implement canonical views/materialized views in the warehouse that expose the new canonical metrics with stable API names.
• Create compatibility layer: maintain legacy views as thin wrappers that map old names to canonical logic where semantics align. If differences exist, tag legacy view as “deprecated” but keep consistent results for MVP by versioning views (e.g., users_v1_active_users).
• Add feature flags / dataset version parameter so dashboards can opt into v1 or canonical definitions.

4. Validation & QA (Days 18–24)

• Run reconciliation tests: row-level sampling and automated checks comparing legacy vs canonical results, with acceptable delta thresholds documented.
• Smoke-test key dashboards; produce a “differences report” for stakeholders highlighting any material changes.

5. Release without blocking (Days 24–28)

• Ship MVP using legacy views mapped to canonical calculations where possible. If immediate parity cannot be achieved for a non-critical metric, document discrepancy and ship with a note in the dashboard metadata.
• Communicate clearly in release notes which metrics are canonical, which are deprecated, and where to find specs.

Governance to prevent recurrence:

• Establish metric stewardship: assign owners for each canonical metric and a lightweight change process (metric RFC in PRs, 48–72 hour review SLA).
• Add metric registry in the BI catalog (includes lineage, owners, SLA, tests).
• Enforce CI for metric SQL: unit tests, data contract checks, and automated lineage verification before merging.
• Quarterly metric review meetings with cross-functional reps to approve new metrics or changes.
• Embed canonical metric badges in BI dashboards and block publishing to “production” if using non-canonical sources (soft enforcement first, hard after one quarter).

Why this works:

• Time-boxed to avoid blocking MVP.
• Produces repeatable artifacts (specs, views, tests) that scale.
• Compatibility layer prevents breaking reports while enabling migration.
• Governance balances speed and control so standardization becomes sustainable.

Direct answer

I ran a six-week test of a new onboarding email sequence, hypothesizing that adding a short personalized video would raise activation, and by week four the data was inconclusive rather than clearly negative, which is the harder call: deciding whether to keep running for a real signal or stop because the result had stopped being informative. I stopped at week five, explained the decision and the reasoning to the two stakeholders who had sunk real time into producing the videos, and made sure what we'd learned about the underlying segment behavior carried into the next attempt instead of being lost with the failed one.

The hypothesis, design, and timeline

The hypothesis was that a short, personalized video early in onboarding would raise activation among users who had signed up but not completed setup, based on a pattern we'd seen in a smaller pilot. I designed a six-week A/B test with a defined minimum sample size calculated up front, specifically so I wouldn't be tempted to call it early or late based on how the numbers happened to be trending on a given day.

How I made the stop-or-continue call

By week four, the treatment group's activation rate wasn't meaningfully different from control, but the sample was also smaller than planned because a tracking issue had silently dropped a portion of the treatment group's data for the first ten days, which meant the result was underpowered (we didn't have enough clean data left to trust a negative result either way, not that the result was actually bad), not simply negative. I spent part of week four determining whether that was an environmental problem, the tracking gap, rather than a genuine sign the video didn't work. Extending the test to compensate was one option; I decided against it, because even a clean extension wouldn't have told us anything about the actual hypothesis with confidence by a reasonable date, and continuing mainly to avoid calling it a failure would have been the wrong reason to keep going.

What I did with what I'd learned

I stopped at week five and told the two people who had built the videos directly: the specific reason, an underpowered and contaminated dataset rather than a clear negative result, and that the honest conclusion was "inconclusive," not "the idea doesn't work." Rather than letting the attempt just end there, I salvaged what was usable: the clean portion of the data still showed a real behavioral pattern in how users engaged with onboarding content at all, which fed directly into redesigning the next attempt's tracking and targeting before we tried a similar idea again.

Trade-offs and pitfalls

The trade-off in a stop-or-continue call like this is sunk cost against real signal: the video work represented real time from real people, and there's pressure to keep going just to justify that investment rather than to actually learn something. The pitfall I watch for is treating "inconclusive" and "failed" as the same thing when explaining the decision, since conflating them either overstates how wrong the idea was or understates how little the test actually proved either way.

Direct answer

Lead with a one-line factual headline, not a narrative buildup, then follow immediately with what you're doing about it. Leadership's first question is always "what happens next," and making them wait for it while you explain the backstory reads as stalling.

Structured elaboration

1. Headline first: what's late, by roughly how much, stated plainly, no hedging language that makes people wonder if you're minimizing it.
2. One or two sentences of cause, at a level leadership can act on, a dependency slipped, a scope surprise, not a blow-by-blow of every technical decision that led here.
3. The plan: concrete next steps with owners and rough timing, even if the timing itself isn't final. "Here's how we'll know more by Friday" is a plan; "we're working on it" is not.
4. Handling pointed questions: answer what you actually know, say plainly when you don't know something rather than guessing to sound in control, and commit to a specific follow-up instead of a vague "I'll keep you posted." Take the most senior or highest-stakes question first rather than letting the conversation drift to whoever's loudest.
5. Close with a cadence: when you'll update again, and through what channel, so the room isn't left wondering whether this becomes a pattern of surprises.

Worked example

A high-visibility platform migration is going to miss its committed date by three weeks. In the leadership update, you open with "the migration will land three weeks past the committed date," not with a summary of everything that's gone right so far. You give the cause: "a dependency on the vendor's new authentication API turned out to need more integration work than their documentation implied." You lay out the plan: a revised, phased timeline with the riskiest piece de-risked first, and an owner and date for each phase. When someone asks "why didn't we know this two weeks ago," you say plainly: "we suspected it a week ago and confirmed it Tuesday, that's a valid gap, and here's what we're changing about how we track vendor dependencies going forward," rather than getting defensive or deflecting.

Trade-offs and pitfalls

• Leading with justification instead of the headline, explaining everything that went right first, reads as avoidance and makes people tune out before they hear the actual news.
• Promising a specific new date under pressure before you've actually validated it is the single most common way this conversation creates a second, worse version of itself two weeks later.
• Answering "I don't know" honestly feels risky in the room but is almost always received better than a confident guess that turns out wrong. Confidence you can't back up erodes trust more than admitting a gap does.
• Treating every pointed question as an attack invites a defensive tone that reads worse than the delay itself. Most pointed questions from leadership are about risk to other commitments, not about assigning blame.

Start with a hypothesis-driven, step-by-step approach so you don’t chase symptoms.

1. Reproduce and isolate

• Recreate the blank in a simple table visual with the measure and the smallest set of slicers/filters. Remove visuals with implicit totals/aggregations.

2. Check relationships & keys

• Confirm relationships exist and are active between fact and dimension tables and that cardinality/direction is correct.
• Validate join keys: run a table that shows COUNTROWS for fact rows and related lookup values (e.g., use RELATED or lookup tables) to catch missing keys.

3. Inspect filter context

• Add intermediate debugging measures:
  • Measure: CurrentFilters = CONCATENATEX(VALUES(Table[Key]), Table[Key], ",")
  • Use HASONEVALUE / VALUES / ISFILTERED to see what context the visual passes.
• Temporarily remove ALLSELECTED/ALL/CROSSFILTER manipulations to see if blanks disappear.

4. Check implicit conversions & data types

• Ensure keys and columns used in joins/filters have matching data types (text vs numeric) — mismatches cause no match -> blank.
• Look for leading/trailing spaces or different casing; use TRIM/UPPER in queries to test.

5. Examine measure logic (ISBLANK/IF and short-circuiting)

• Search for ISBLANK, IF, DIVIDE or filters that return BLANK() intentionally. Replace IF(condition, value, BLANK()) with explicit test or provide fallback to see intermediate results.
• Break complex expressions into smaller measures (numerator, denominator, filter table) to see which part returns BLANK.

6. Use DAX Studio / Evaluate intermediate results

• In DAX Studio run EVALUATE of the VALUES or SUMMARIZE for the relevant keys to see which rows exist.
• Use Server Timings and Query Plan to spot expensive filters or unexpected context transitions.
• Run the measure with EVALUATE ROW("Measure", [YourMeasure]) while varying FILTERS using CALCULATE(..., KEEPFILTERS(...)).

7. Practical checks

• Test with ALL(Table) to remove filters; if value appears, a filter is excluding it.
• Test with USERELATIONSHIP to validate inactive relationships.
• Temporarily return 0 instead of BLANK to see row presence.

Result: By isolating context, validating relationships/types, decomposing the measure, and using DAX Studio to evaluate tables and measure outputs, you can pinpoint whether blanks are caused by missing joins, filter context, implicit conversions, or explicit BLANK logic and then apply the appropriate fix.

A strong candidate writes this directly:

SELECT id, name, email
FROM users
WHERE status = 'active'
ORDER BY created_at DESC
LIMIT 100;

Structured elaboration

The clause order to reason about, and the order the database actually evaluates it in, are different things worth keeping straight:

• FROM picks the table.
• WHERE filters rows before anything else happens (status = 'active').
• ORDER BY sorts the filtered rows.
• LIMIT trims the sorted result to the first N rows.

Selecting only the columns you need (id, name, email instead of *) keeps the result narrow and avoids breaking callers if the table later gains columns.

Worked example

Given rows (1,'Ann',...,'active','2025-01-05'), (2,'Bo',...,'inactive','2025-01-06'), (3,'Cy',...,'active','2025-02-01'), (4,'Di',...,'active','2025-03-01'), the query returns Di, Cy, Ann in that order: the inactive row (Bo) is filtered out first, then the three active rows are sorted newest-first.

Trade-offs and pitfalls

LIMIT without ORDER BY gives you an arbitrary 100 rows, not "the most recent 100"; the two clauses have to travel together. If created_at can be NULL, decide up front whether those rows should sort first or last (see NULLS LAST). At real scale, LIMIT 100 with no index on (status, created_at) still requires scanning and sorting every active row before truncating, so this is the point where an index recommendation is a reasonable follow-up, not a requirement of a correct answer.

Direct answer

INNER JOIN keeps only rows where the join key matches in both tables. LEFT JOIN keeps every row from the left table and fills unmatched right-side columns with NULL. RIGHT JOIN is the mirror image (every row from the right table, unmatched left-side columns are NULL). FULL OUTER JOIN keeps every row from both sides, with NULLs on whichever side didn't match.

Structured elaboration

A useful mental model: start from LEFT JOIN (all of the left table, matched where possible), and each other join type is a variation of which side you insist on keeping in full.

Worked example (sqlite3, verified)

Sample data:

CREATE TABLE customers (customer_id INTEGER PRIMARY KEY, name TEXT);
CREATE TABLE orders (order_id INTEGER PRIMARY KEY, customer_id INTEGER, amount INTEGER);

INSERT INTO customers VALUES (1, 'Alice'), (2, 'Bob'), (3, 'Carol');
INSERT INTO orders VALUES
  (101, 1, 50),
  (102, 1, 30),
  (103, 2, 20),
  (104, 9, 75);  -- order for a customer_id that does not exist (orphan row)

Carol (customer_id 3) has never ordered. Order 104 belongs to customer_id 9, who doesn't exist in customers, an intentional reconciliation-style gap (an order with no matching customer, the mirror case of a customer with no orders).

INNER JOIN:

SELECT c.customer_id, c.name, o.order_id, o.amount
FROM customers c
INNER JOIN orders o ON c.customer_id = o.customer_id;

Result (3 rows): (1,'Alice',101,50), (1,'Alice',102,30), (2,'Bob',103,20). Carol and order 104 both vanish.

LEFT JOIN:

SELECT c.customer_id, c.name, o.order_id, o.amount
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id;

Result (4 rows): the 3 inner rows plus (3,'Carol',NULL,NULL). Carol appears once with NULL order columns; order 104 still doesn't appear (it lives on the right side).

RIGHT JOIN:

SELECT c.customer_id, c.name, o.order_id, o.amount
FROM customers c
RIGHT JOIN orders o ON c.customer_id = o.customer_id;

Result (4 rows): the 3 inner rows plus (NULL,NULL,104,75). Now order 104 survives with NULL customer columns; Carol drops out.

FULL OUTER JOIN:

SELECT c.customer_id, c.name, o.order_id, o.amount
FROM customers c
FULL OUTER JOIN orders o ON c.customer_id = o.customer_id;

Result (5 rows): the 3 inner rows, (3,'Carol',NULL,NULL), and (NULL,NULL,104,75). Both unmatched sides are preserved.

Business-reporting framing (reconciliation). This is exactly the shape of a reconciliation report: "which customers show up in our CRM but never billed" versus "which billing records reference a customer we don't recognize." A FULL OUTER JOIN filtered to only the unmatched rows surfaces both at once:

SELECT c.customer_id AS cust_id, o.customer_id AS order_cust_id, o.order_id
FROM customers c
FULL OUTER JOIN orders o ON c.customer_id = o.customer_id
WHERE c.customer_id IS NULL OR o.order_id IS NULL;

Result (2 rows): (NULL, 9, 104) (an order referencing a missing customer) and (3, NULL, NULL) (a customer with no orders). In finance/reconciliation terms these are the two classic break types: unmatched source records and unmatched target records.

Trade-offs & pitfalls

• MySQL has no FULL OUTER JOIN keyword (and older MySQL/other engines also lack RIGHT JOIN in some restricted dialects, though RIGHT JOIN is broadly supported). The portable emulation is LEFT JOIN ... UNION ... RIGHT JOIN ... filtered appropriately, or LEFT JOIN ... UNION ... (INNER JOIN swapped). This answer's queries were run and verified against SQLite 3.39+, which supports RIGHT JOIN and FULL OUTER JOIN natively.
• RIGHT JOIN is rarely used in practice because you can always rewrite it as a LEFT JOIN by swapping the table order; most style guides prefer that for readability.
• A common mistake: putting a filter on the right table in the WHERE clause instead of the ON clause of a LEFT JOIN. WHERE o.amount > 10 after a LEFT JOIN silently turns it back into an INNER JOIN, because NULL rows fail that comparison. The filter belongs in the ON clause if you want to keep unmatched rows: LEFT JOIN orders o ON c.customer_id = o.customer_id AND o.amount > 10.
• If the join key isn't unique on one side, a "safe" LEFT JOIN can still fan out rows (one customer row duplicated once per matching order), which quietly breaks downstream counts and sums if you forget to aggregate first.