Microsoft Data Scientist Interview Preparation Guide (Mid-Level)

Qualitative vs. quantitative user research (brief): Qualitative methods (interviews, usability tests, open-ended surveys) produce rich, contextual insights about motivations, pain points and user workflows. Quantitative methods (A/B tests, analytics, structured surveys) yield numeric measurements that allow generalization and statistical inference.

Qualitative — strengths (data scientist view):

• Reveals causal hypotheses and edge cases you can later test quantitatively.
• Helps interpret ambiguous signals from models/metrics (why conversion dropped).
  Qualitative — weaknesses:
• Small sample sizes limit generalizability and make statistical validation hard.
• Harder to integrate into reproducible pipelines; data is unstructured and costly to encode.

Quantitative — strengths:

• Scalable and statistically rigorous; supports A/B testing and confidence intervals.
• Easy to instrument, monitor, and feed into modeling/feature engineering.
  Quantitative — weaknesses:
• Can miss context and root causes (signals without story).
• Requires good instrumentation and large samples; biased metrics mislead models.

When to prefer qualitative: Launching a new feature with unknown user workflows — run contextual interviews/usability tests to uncover needs and surface hypotheses.

When to prefer quantitative: Optimizing checkout funnel conversion — run A/B tests and analyze event data to measure impact and guide model features.

Direct answer

For 100-plus segments across SKUs (stock-keeping units) and regions, use adaptive statistical thresholds rather than one static number per segment, aggregate over windows sized to each metric's natural velocity, and treat correlated alerts as one incident instead of many. The single biggest lever against both alert fatigue and false positives is grouping: when a shared upstream cause moves many segments at once, a explainable root-cause alert (this vendor, this region cluster) beats a wall of individually-true but individually-uninformative segment alerts.

Structured elaboration

Threshold types

Aggregation windows

Match the window to how fast the underlying process can move: short windows (15 to 60 minutes) for operational signals like ingestion lag, medium windows (4 to 24 hours) for day-level product KPIs (key performance indicators) with intraday noise, and rolling 7- or 28-day comparisons for metrics with weekly seasonality.

Suppression, grouping, and explainability

• Require a sustained deviation across multiple consecutive windows before paging, not a single noisy reading.
• Cluster alerts that share a likely root cause (same upstream data source, same region, same recent deployment) into one incident alert rather than one alert per segment.
• Attach an explanation to every alert: which segments moved, by how much, and the top contributing driver, so the recipient does not have to reverse-engineer why they were paged.

Ownership and escalation

Assign a primary and backup owner per metric domain (not per segment, which does not scale at 100-plus), give each owner a runbook with the standard diagnostic queries, and escalate unresolved Warning-tier alerts to a manager after a fixed window, with Critical-tier alerts pagd immediately.

Worked example

Suppose 120 SKU-region segments are each monitored independently, and a single shared fulfillment vendor outage degrades a fulfillment-rate metric across 18 of those segments at the same moment. Monitored naively, that is 18 separate pages. Grouped by shared root cause (same vendor dependency flagged in the feature pipeline) into one incident alert listing the 18 affected segments:

Grouping cuts the paged-alert count for this single incident by about 94%, from 18 individual pages down to 1, without losing any of the underlying detection: every affected segment is still listed inside the one alert.

Trade-offs & pitfalls

Statistical thresholds adapt better than static ones but need a minimum history per segment; applying them uniformly to a brand-new, low-volume SKU-region pair produces an unstable baseline that either never fires or fires constantly, so new segments need a warm-up period on a coarser static rule until enough history accumulates. Grouping reduces fatigue and improves explainability, but a grouping rule that is too aggressive can mask two genuinely independent incidents that happen to touch overlapping segments; the grouping key (shared vendor, shared region, shared recent deploy) has to be a real causal hypothesis, not just co-occurrence in time. Assigning ownership by domain rather than by individual segment scales better but means no single owner has full context on any one segment, so the runbook has to carry enough segment-specific detail (typical baseline, known seasonality quirks) that a generalist owner can act without tribal knowledge.

Direct answer
Overfitting is when a model learns patterns specific to its training data (including noise) that don't generalize; underfitting is when a model is too simple to capture the real signal even in the training data. Model capacity is the dial: too much relative to the data and signal available leads to overfitting, too little leads to underfitting.

Structured elaboration
Three observable symptoms of overfitting: (1) training error is very low while validation error is noticeably higher, a persistent gap that doesn't close as training continues; (2) small changes to the training set (different random seed, slightly different sample) produce visibly different model behavior or predictions; (3) the model performs suspiciously well on patterns that shouldn't be learnable from the amount of signal present, e.g. near-perfect accuracy on a genuinely noisy real-world label.
Three observable symptoms of underfitting: (1) both training and validation error are high and close together; (2) the model's predictions look "too smooth" or generic, missing patterns that are visibly present even by eye in the training data; (3) adding capacity (a slightly deeper tree, one more layer, dropping a regularization penalty) meaningfully improves BOTH training and validation performance together, which wouldn't happen if you'd already captured the signal.
Model capacity relates to which one you're facing directly: too much capacity relative to the available data and true signal complexity gives the model room to memorize training-set-specific noise (overfitting); too little capacity means the model literally cannot represent the true relationship regardless of how well it's trained (underfitting).

Worked example
Fit a decision tree with no depth limit to a small tabular dataset: you'll likely see 100% training accuracy and a meaningfully lower validation accuracy, the textbook overfitting signature. Constrain that same tree to max_depth=2 and you may see training accuracy drop to something mediocre while validation accuracy stays close to it, the underfitting signature; the tree is now too simple to represent the interactions that actually matter for this task.

Trade-offs & pitfalls
A single number for training and validation error can be misleading if the validation set is small; always sanity-check with cross-validation before concluding you're clearly in one regime, especially when the gap is small or the dataset itself is small enough that a single split carries meaningful sampling noise.

Direct answer. When a larger batch size pushes training past available GPU memory, the available fixes trade off differently between compute time, implementation complexity, and whether they change the actual math of what "batch size" means, so the right choice depends on which of those you can afford to spend.

The mitigation menu.

• Gradient accumulation: run several smaller micro-batches, summing their gradients before a single optimizer step, simulating a larger effective batch size without ever holding the full batch in memory at once. Trade-off: nearly free in implementation complexity and mathematically equivalent to true large-batch training for most loss functions, but costs extra wall-clock time since you're doing more forward/backward passes for the same effective batch. The exception is the batch-size-semantics one, and it is worth knowing by name: any layer that computes statistics over the batch, batch normalization above all, sees only the micro-batch, not the full effective batch, so a network containing batch normalization is NOT equivalent under accumulation and needs synchronized batch normalization across micro-batches or a normalization layer that does not depend on the batch at all.
• Mixed precision: store activations (the intermediate outputs each layer produces during the forward pass, which are kept in memory because the backward pass needs them, and which usually dominate memory during training) and weights in fp16 or bf16 (bf16 being a 16-bit format with fp32's exponent range but fewer mantissa bits, so it trades precision for far less overflow risk) instead of fp32, roughly halving memory footprint. Trade-off: often a "free" win in both memory and speed on modern hardware, but introduces its own numerical-stability considerations (dynamic loss scaling, meaning the loss is multiplied by a large factor before backward and the gradients divided by it afterwards, so that small gradient values do not vanish to zero in fp16, with the factor adjusted automatically when it causes an overflow; plus watching for NaNs from fp16 underflow and overflow), so it is not entirely free of engineering cost.
• Model or pipeline parallelism: split the model itself (not just the data) across multiple devices, so no single device needs to hold the full model or full activations. Trade-off: the most memory-scalable option but the most complex to implement correctly, and introduces communication overhead between devices.
• Activation checkpointing: don't store intermediate activations from the forward pass, recompute them during the backward pass instead. Trade-off: significantly reduces memory (often the single biggest lever for very deep networks) at the direct cost of extra compute, since the forward pass effectively runs twice for the checkpointed segments.
• Reducing sequence length: for sequence models, truncating or bucketing to a shorter maximum length directly reduces the memory footprint of attention and activations. Trade-off: free in implementation cost, but changes what the model actually sees, potentially losing information for the fraction of examples whose true length exceeds the new cap.
• Reducing the batch size itself, with the learning rate re-tuned to match: the option that is easy to forget precisely because it looks like giving up. Trade-off: free in both compute and implementation complexity, and the one option on this list whose cost lands squarely on effective batch-size semantics. A smaller batch means noisier gradient estimates and a different effective noise scale, so the learning rate and schedule usually need re-tuning (often downward), and the run's convergence path, and sometimes its final quality, genuinely changes rather than merely being reorganized.
• Parameter sharding (splitting optimizer state and/or parameters themselves across devices, as in ZeRO-style approaches, ZeRO being a family of techniques that partitions optimizer state, gradients and eventually parameters across devices instead of replicating them on every one): substantially reduces per-device memory for the optimizer state specifically (which is often larger than the model weights themselves for adaptive optimizers, that is, optimizers such as Adam that keep one or two running statistics per parameter and therefore carry several times the model's own size in state). Trade-off: adds communication overhead to gather sharded parameters when needed, and is more complex to set up than gradient accumulation or mixed precision.

How to choose. The two to know cold are mixed precision and gradient accumulation: both are close to free in engineering cost and, batch-normalization layers aside, neither changes what the model learns. Activation checkpointing is the third to reach for, since it is a pure compute-for-memory trade with no semantic change at all. Only then reach for model or pipeline parallelism, sharding, reducing sequence length, or simply shrinking the batch, since each of those trades away something the first three do not: implementation complexity and communication overhead for parallelism and sharding, information in the input for a shorter sequence, and effective batch-size semantics for a smaller batch with a re-tuned learning rate.

How they work together:

• CPython uses reference counting: each object tracks references; when count hits zero it's reclaimed immediately.
• The cyclic GC (generational, meaning it groups objects by how long they've survived so far and scans the youngest group most often, since most garbage dies young and rarely needs a second look) complements this by detecting reference cycles that reference counting alone can't free.

Example needing GC:
Two objects referencing each other (cycle) with del avoided or handled by GC. Example:

class Node:
    def __init__(self):
        self.ref = None

a = Node()
b = Node()
a.ref = b
b.ref = a
# If no external refs remain, ref counts are non-zero due to cycle -> GC must collect

Actually watching the reclamation happen, verified on CPython 3.12, extending the example above with a __del__ and an explicit gc.collect():

import gc

class Node:
    def __init__(self, name):
        self.name = name
        self.ref = None
    def __del__(self):
        print(f'__del__ called for {self.name}')

a = Node('a')
b = Node('b')
a.ref = b
b.ref = a
del a, b            # neither refcount reaches zero; both still reference each other

gc.collect()         # the cyclic GC finds the unreachable pair and reclaims it

Output:

__del__ called for a
__del__ called for b

Both finalizers ran as part of gc.collect() reclaiming the cycle, confirming the cycle really was found and cleaned up, not just theoretically collectible.

Reference counting handles most deallocations quickly; the cyclic GC periodically finds unreachable cycles and reclaims them. Cycles involving objects with __del__ used to require special handling: on Python versions before 3.4, the cyclic GC could not safely decide what order to finalize such objects in, so it left them permanently uncollected in gc.garbage instead of guessing. Since Python 3.4 (PEP 442), __del__ is called safely as part of collecting the cycle, exactly as demonstrated above, and the objects are then reclaimed normally; citing the old "cycles with __del__ can't be collected" claim as current fact is a dated answer today.

Direct answer

On a small sample table with age missing for one user and country missing for another, look at what else is known about each row before concluding anything: if the missingness seems tied to specific OTHER observed fields (say, both missing values belong to users with very short last_active_days, suggesting incomplete signups rather than a random gap), that points toward missing at random (MAR) rather than missing completely at random (MCAR); if there's no such pattern at all, MCAR is the more plausible working assumption, though a sample this small can't rule out much with confidence either way.

Reading the indicators in a small sample

The judgment here is necessarily provisional: with only a handful of missing values, you're looking for suggestive patterns, not statistical proof. Check whether the missing values cluster on particular values of other observed columns (a specific signup channel, a specific date range, a specific last_active_days value) rather than spreading randomly across the table; a cluster is a hint worth chasing further, while a scattered, unpatterned handful of missing values in an otherwise unremarkable small sample is consistent with MCAR, though genuinely inconclusive at this size.

Worked example

In a four-row sample: age is missing for the user with last_active_days of 5, and country is missing for the user with last_active_days of 30. If this were the ENTIRE evidence, it's genuinely ambiguous at n=4, but the diagnostics worth running on a larger version of the same table would be: does age missingness correlate with recency of signup or activity level across many more rows (pointing toward MAR via activity level as the observed explanation), or does it look scattered across every level of activity (more consistent with MCAR)? The two concrete diagnostics to propose here are a missingness-versus-last_active_days crosstab and a simple bar chart of missingness rate by signup cohort, both cheap to run once you have more than four rows to work with.

Trade-offs and pitfalls

Resist over-interpreting a genuinely small sample: the honest answer at n=4 is "here's a hypothesis and here's how I'd test it on the full dataset," not a confident MCAR-versus-MAR conclusion drawn from four rows.

1. Clarify business objective and decision context
   Purpose: Convert the vague prompt into a single, testable question (e.g., “reduce churn rate by X%” or “rank leads by conversion probability”). Ask “who uses this, when, and what decision changes?”
   Reduces ambiguity by anchoring the work to a clear outcome and stakeholder action.

2. Specify measurable target metrics and units
   Purpose: Choose primary metric(s) (e.g., precision@K, lift, AUC, revenue per user) and exact units/timeframes. Define whether higher/lower is better and tolerance bands.
   Reduces ambiguity by replacing qualitative goals with quantitative, comparable measures.

3. Define scope, constraints, and success thresholds
   Purpose: List inputs/segments included, latency, budget, model complexity, regulatory/privacy constraints, and concrete acceptance thresholds (e.g., precision ≥ 0.6, latency ≤ 200ms).
   Reduces ambiguity by bounding feasibility and non-functional requirements.

4. Inventory data & validation plan
   Purpose: Confirm data sources, needed features, expected quality, and a reproducible validation strategy (train/test split, cross-validation, uplift tests).
   Reduces ambiguity by surfacing data gaps and ensuring the metric is measurable with available data.

5. Define evaluation, rollout, and monitoring criteria
   Purpose: Describe offline vs. online evaluation, A/B test plan, rollback conditions, and monitoring metrics/alert thresholds post-deployment.
   Reduces ambiguity by ensuring acceptance is tied to real-world performance and maintainable guardrails.

This 5-step framework quickly translates fuzzy prompts into concrete, testable acceptance criteria aligned with business impact.

Approach: segment regions by density, demand variability, regulatory strictness, and supply elasticity. Prioritize features where ROI aligns with regional constraints. Example feature: dynamic shared-ride incentives (encouraging pooled rides via discounts and pickup clusters). In a dense metro: high priority — pooled rides reduce congestion and increase utilization; expected high adoption and network effects; implement early with aggressive incentives and micro-matching. In a rural region: low priority — sparse demand means pooling increases wait times and deters riders; focus instead on driver-supply reliability features (guarantees, flexible drop-offs). Use regional KPIs (utilization, wait time, margin) to allocate development resources; run localized pilots before scaling. This ensures product investment yields maximum impact per region.

Direct answer

Periodic retraining is simplest and most predictable but can leave a model stale between schedules; trigger-based retraining reacts faster but needs a reliable trigger signal; continuous/online learning stays freshest but carries the highest infrastructure and stability risk: for a fraud model with seasonal patterns and costly false negatives, a hybrid of scheduled-plus-triggered is usually right.

Structured elaboration

• Periodic (scheduled): retrain weekly or monthly regardless of signals. Ideal use case: stable domains where the underlying relationship changes slowly and predictably. Infrastructure implication: simplest to build and reason about: a cron-style pipeline. Risk profile: can leave the model stale for the entire interval between retrains if something changes suddenly (a real risk for fraud, where fraudsters adapt quickly).
• Trigger-based: retrain when a drift or performance signal crosses a threshold. Ideal use case: domains with irregular, event-driven shifts (a fraud ring adapts, a new product launches). Infrastructure implication: needs reliable monitoring wired directly into the retraining orchestrator, plus guardrails against thrashing (retraining repeatedly on noisy signals). Risk profile: faster response to real problems, but a noisy trigger can cause wasted retrains or, worse, retrains on CORRUPTED data if the trigger itself was caused by a pipeline bug rather than real drift.
• Continuous/online learning: the model updates incrementally as new labeled data arrives, with no discrete "retrain" event at all. Ideal use case: extremely high-velocity domains where even daily retraining is too slow (ad ranking, real-time personalization). Infrastructure implication: the heaviest lift: needs streaming infrastructure, careful learning-rate and regularization control, and safety constraints to prevent a single bad batch of data from corrupting the live model. Risk profile: highest: catastrophic forgetting, instability from noisy individual updates, and the hardest to reproduce or audit after the fact ("which exact update caused this?").

Worked example

For the fraud system specifically: scheduled weekly retraining as the baseline (captures the slow seasonal drift reliably, on a predictable cadence stakeholders can plan around), PLUS a trigger-based override that fires an out-of-cycle retrain if a PSI-style drift signal or a sudden false-negative-rate spike crosses a threshold BEFORE the next scheduled run: this bounds the worst-case staleness (never longer than a week) while still reacting fast to an adapting fraud pattern that can't wait a week. Continuous online learning is deliberately avoided here specifically because of the cost asymmetry: a single corrupted or adversarially-poisoned online update in a continuously-learning fraud model could open a real exploit window, and the instability risk isn't worth the marginal freshness gain over a well-tuned scheduled+triggered hybrid.

For VALIDATION cadence specifically (a close corollary of the retraining-cadence question): scheduled validation (say, daily backtest against a held-out set) catches slow drift reliably and cheaply; continuous/near-real-time validation catches fast-moving problems sooner but costs more compute and is more prone to false alarms from single-window noise: the same hybrid logic applies: scheduled as the baseline, with a lighter-weight continuous check (a cheap proxy metric, evaluated more frequently) as an early-warning layer that doesn't replace the full scheduled validation.

Trade-offs & pitfalls

The common mistake is treating "more frequent" as strictly better across all three options: a trigger-based or continuous system retrained on a pipeline bug's noise (rather than real signal) can actively make the model WORSE, faster than a slower, more deliberate scheduled cadence would have. The freshness-vs-stability trade-off is real in both directions, not just "stale is bad."

Self-join the monthly MRR (monthly recurring revenue) snapshot table to itself, offsetting by 12 months, restricted to accounts that had an MRR row 12 months ago. The numerator sums those accounts' current MRR (expansion adds, contraction subtracts, full churn contributes exactly 0); the denominator sums the same accounts' MRR from 12 months earlier. New accounts are excluded entirely from both sides, correctly, since NRR is a retention metric on an existing cohort, not a growth metric, and fully churned accounts stay in the denominator while contributing 0 to the numerator, which is exactly what pulls NRR below 100%.

Formula

where ${\text{cohort}_t}$ is the set of accounts that had a snapshot row 12 months before month $t$ (existed then, whether or not they have since churned).

Approach

1. Self-join mrr_snapshot to itself on account_id, matching prev.snapshot_month = date(cur.snapshot_month, '-12 months').
2. LEFT JOIN (not INNER JOIN), so a churned account whose snapshot legitimately dropped to 0 still shows up, while a genuinely new account with no row 12 months ago produces mrr_t_12 = NULL.
3. Filter to WHERE mrr_t_12 IS NOT NULL. This single filter is what correctly excludes new accounts from the cohort, no extra CASE statement needed.
4. Sum both sides, divide, and guard the denominator with NULLIF to avoid a divide-by-zero if an entire cohort had zero MRR 12 months ago.

Worked example

CREATE TABLE mrr_snapshot (
  account_id TEXT,
  snapshot_month TEXT,   -- first day of month, 'YYYY-MM-01'
  mrr_cents INTEGER
);

-- A1: existing account, expands (10000 -> 15000 cents)
INSERT INTO mrr_snapshot VALUES ('A1', '2024-06-01', 10000), ('A1', '2025-06-01', 15000);
-- A2: existing account, contracts (8000 -> 6000 cents)
INSERT INTO mrr_snapshot VALUES ('A2', '2024-06-01', 8000),  ('A2', '2025-06-01', 6000);
-- A3: NEW account, only appears in the current period (no row 12 months prior)
INSERT INTO mrr_snapshot VALUES ('A3', '2025-06-01', 4000);
-- A4: existing account that fully churned (still snapshotted monthly at 0)
INSERT INTO mrr_snapshot VALUES ('A4', '2024-06-01', 5000),  ('A4', '2025-06-01', 0);

WITH pairs AS (
  SELECT
    cur.snapshot_month AS month_t,
    cur.account_id,
    cur.mrr_cents      AS mrr_t,
    prev.mrr_cents     AS mrr_t_12
  FROM mrr_snapshot cur
  LEFT JOIN mrr_snapshot prev
    ON cur.account_id = prev.account_id
   AND prev.snapshot_month = date(cur.snapshot_month, '-12 months')
),
cohort AS (
  -- only accounts that existed 12 months ago; this single filter drops
  -- new accounts (A3) from both numerator and denominator
  SELECT * FROM pairs WHERE mrr_t_12 IS NOT NULL
)
SELECT
  month_t,
  SUM(mrr_t_12)                                        AS base_mrr_12mo_ago,
  SUM(mrr_t)                                            AS current_mrr_of_that_cohort,
  ROUND(1.0 * SUM(mrr_t) / NULLIF(SUM(mrr_t_12),0), 4)  AS nrr
FROM cohort
GROUP BY month_t
ORDER BY month_t;

Result:

┌────────────┬───────────────────┬─────────────────────────────┬───────┐
│  month_t   │ base_mrr_12mo_ago │ current_mrr_of_that_cohort  │  nrr  │
├────────────┼───────────────────┼─────────────────────────────┼───────┤
│ 2025-06-01 │ 23000             │ 21000                       │ 0.913 │
└────────────┴───────────────────┴─────────────────────────────┴───────┘

A1 expanded (10000 to 15000 cents), A2 contracted (8000 to 6000), and A4 fully churned (5000 to 0). A3 is a brand-new account with no row at 2024-06-01, so it is excluded from both numerator and denominator. Base MRR from the cohort a year ago: 10000 + 8000 + 5000 = 23000. Current MRR from that same cohort: 15000 + 6000 + 0 = 21000. NRR = 21000 / 23000 = 0.9130..., rounded to 0.913: this cohort retained 91.3% of its revenue from a year ago even after A4's full churn, because A1's expansion offset most of the loss.

Trade-offs & pitfalls

• Complexity: the self-join is close to O(n) per month if account_id and snapshot_month are indexed together; without that index it degrades to a much more expensive scan. Check the index first if this query is slow against a real snapshot table.
• Edge case: a NULLIF-guarded denominator handles a cohort with zero base MRR. A gap in an account's snapshot history (paused billing, a missed snapshot run) will incorrectly look like a new account or like churn depending on which side of the 12-month window the gap falls on; flag that as a data-quality risk rather than trusting the join silently.
• Common wrong turn: computing NRR as all current MRR divided by all MRR 12 months ago, with no cohort restriction at all. That is a blended growth metric, not NRR: it lets new-account growth mask churn in the existing base, which is exactly what NRR is meant to isolate.
• Common wrong turn: excluding fully churned accounts from the numerator sum instead of including them as 0. Summing a 0 versus omitting the row gives the same numerator total, but implementing the exclusion via an INNER JOIN or a WHERE mrr_t > 0 filter also drops those accounts from the denominator, which does change the answer and overstates retention.