Microsoft Data Scientist (Staff Level) Interview Preparation Guide 2026

A watermark is a marker that says "I've processed everything up to this point, and I'm not expecting anything older to still arrive." It's the mechanism that turns "read new data" from a vague instruction into something a query can actually execute: a boundary condition (WHERE updated_at > watermark).

Event-time vs. processing-time

• An event-time watermark tracks progress against when something actually happened in the real world (a created_at or event_time column on the record itself).
• A processing-time watermark tracks progress against when your pipeline saw or ingested the record (a load timestamp your job assigns).

The distinction matters because these two clocks disagree whenever data arrives late: an event that happened at 10:00 but only reaches your pipeline at 10:45 has an event-time of 10:00 and a processing-time of 10:45. If your watermark advances on processing-time, that event is correctly picked up whenever it shows up. If your watermark advances on event-time and you set it strictly (never accept anything before the current watermark), that late event gets silently dropped once the watermark has moved past 10:00.

Watermarks as incremental load windows
A monotonic column, most commonly updated_at on a mutable table or an auto-incrementing offset on an append-only log, gives you a cursor: each run reads WHERE watermark_column > last_recorded_watermark and, on success, advances the stored watermark to the maximum value it just processed. This is exactly the high-water-mark pattern behind most incremental extraction.

Handling late-arriving data without losing it
The honest fix is allowed lateness: instead of treating the watermark as a hard cutoff, you keep accepting records within a bounded grace window behind the watermark (say, up to 2 hours late) before you consider that window closed. This is a real trade-off, not a free lunch: a longer allowed-lateness window catches more genuinely late data but delays when you can consider any given window "final" and keeps more state open longer (memory in a streaming job, or a longer window before a daily aggregate can be marked complete).

A two-dimension bucket (customer AND month) is a GROUP BY on both a foreign key and a truncated date expression together, producing one row per customer per active month rather than one total per customer or one total per month.

Structured elaboration

SELECT customer_id, strftime('%Y-%m', order_date) AS month, SUM(amount) AS monthly_revenue
FROM orders
GROUP BY customer_id, month
ORDER BY customer_id, month;

This is the same GROUP BY-on-multiple-columns mechanic as any multi-dimension breakdown, just with one dimension being a plain identifier (customer_id) and the other a computed date-truncation expression (month). The output naturally has one row per customer per month in which that customer had at least one order, again omitting customer/month combinations with zero orders unless combined with the zero-fill technique (generate every customer/month combination that should appear, for example via a calendar/date-series CTE cross-joined against customers, then LEFT JOIN the aggregated revenue onto that full combination list so a zero-order month shows an explicit 0 in the result instead of simply being absent from it).

Worked example

Given customer 1 with orders of $100 (January) and $50 (January), and $30 (February): the query returns two rows for customer 1, January with $150 and February with $30, correctly separating the two months rather than combining them into one customer-level total.

Trade-offs and pitfalls

This shape (entity x time period) is the foundation of most cohort and retention-style reporting; combined with the zero-fill technique (to show customer/month pairs with zero activity) and a running total (a window function, out of this topic's scope), it becomes a full cohort revenue table.

Direct answer

Batch scoring runs predictions over a large set of inputs on a schedule and writes the results somewhere for later use; a low-latency hosted endpoint keeps a model warm and always-on behind an API to answer individual requests in milliseconds; serverless inference spins compute up on demand per request and back down when idle, trading an occasional cold-start latency hit for not paying for idle capacity. Reach for batch scoring when nobody is waiting synchronously for that specific prediction; reach for a hosted endpoint when request volume is high and steady enough to justify always-on capacity and latency must be predictable; reach for serverless when traffic is bursty or low-volume enough that paying for an idle endpoint is wasteful and occasional cold starts are acceptable.

Structured elaboration

Worked example

Suppose keeping a model warm on a small always-on instance costs $0.50 per hour, while a serverless platform charges $0.0004 per invocation, and every request genuinely needs an individual synchronous answer (batching is not an option).

At 200 requests per day:

At this volume serverless is roughly $360/2.40 \approx 150$ times cheaper, so the decision comes down entirely to whether occasional cold-start latency is tolerable. The two costs are equal at the volume $V$ where $V \times 30 \times 0.0004 = 360$:

Below roughly 30,000 requests per day, serverless is cheaper; above it, the always-on hosted endpoint becomes the cheaper and lower-tail-latency choice. Batch scoring sits outside this comparison entirely: scoring 10 million items once overnight on a shared batch cluster is typically far cheaper per prediction than serving each one synchronously through either of the other two options, precisely because nothing is paying for an always-on or per-invocation serving layer; the cost is traded for freshness, since results are only as current as the last run.

Trade-offs and pitfalls

A common wrong turn is choosing serverless for a latency-critical path without checking cold-start behavior under the platform's real concurrency model, since a burst of concurrent requests can each trigger a separate cold start rather than sharing one warm instance. Another is defaulting to a hosted endpoint out of habit for something that is genuinely a batch problem, such as a once-a-day digest score that never needs an always-on API. Batch scoring's biggest pitfall is treating it as maintenance-free simply because nothing is synchronous: a broken nightly job can silently serve yesterday's scores for days if nobody watches the job's completion status or output row count.

Task (a) Predicting house prices (right‑skewed distribution)

• Recommended metrics: MAE and R² (optionally Median Absolute Error).
• Why: House prices are right‑skewed with a few very expensive homes. MSE/RMSE penalize large errors quadratically so they can be dominated by outliers; MAE is more robust (linear penalty) and easier to interpret as “median absolute dollar error” if you report median MAE. R² (formula: R² = 1 - SS_res/SS_tot, i.e. 1 minus the ratio of the model's squared prediction errors to the variance already in the target) gives explained-variance context for stakeholders as a familiar 0-1 "fraction of variance explained" number, but note it inherits MSE's quadratic sensitivity to outliers since it's built from the same squared residuals, so a handful of very expensive homes can swing it sharply even though it reads as a single tidy number. If skew is extreme, consider evaluating on log(prices) and reporting MAE in log-space (or back-transformed) to stabilize variance.

Task (b) Predicting delivery time, where absolute error in real units matters most

• Recommended metrics: MAE and MAPE (with caveats).
• Why: When absolute deviations matter (a customer cares about being told "2 days" and the delivery actually taking 2 days, not some squared penalty), MAE directly measures average absolute error in the same real units the business and the customer both understand (days), which is intuitive and robust compared to MSE. MAPE provides relative error (%) useful for comparing across order types or distances, but avoid when true values can be near zero (MAPE explodes for near-instant deliveries). Also consider symmetric alternatives (sMAPE) if zero-or-near-zero delivery times occur. R², by contrast, would be a poor fit for this scenario: it summarizes variance explained, not the real-unit error size a customer actually experiences.

Task (c) Financial forecasts, where occasional large errors occur

• Recommended metrics: Median Absolute Error or MAE combined with robust diagnostics; avoid MSE/RMSE as primary.
• Why: Financial forecasts occasionally miss badly around shocks (earnings surprises, market moves) even when the model is otherwise solid day-to-day; MSE/RMSE overweight those occasional large misses and can make model selection chase rare tail events rather than typical performance. Median Absolute Error (or MAE with trimmed/winsorized evaluation) is far more robust to those occasional large errors. Additionally, report a quantile loss (e.g., 90th percentile error) or percentage of forecasts within tolerance to capture tail behavior explicitly, since a single robust average can otherwise hide how bad the worst misses get. Always complement numeric metrics with residual/outlier analysis and visualizations.

General notes:

• Use RMSE when large errors are particularly costly and you want to penalize them.
• Always report units and distribution-aware stats (median, percentiles), and consider transformations (log) when appropriate.

A cohort-based root-cause approach to a retention decline works by isolating whether the drop is a real, uniform product problem or concentrated in a specific slice of users, which changes the fix entirely.

Defining the cohorts

Define cohorts by onboarding month over the last six months (the population described in the question), so each cohort's retention curve is comparable at the same days-since-onboarding, rather than comparing raw calendar-time retention across cohorts that started at different points in their own lifecycle.

Comparative metrics to compute

• Retention curve per monthly cohort, to see whether the decline is concentrated in the most recent cohorts (suggesting something changed recently, like a product regression or a shift in acquisition quality) or is spread evenly across all six months (suggesting a longer-running, structural issue).
• Retention broken out by acquisition channel within each cohort, since a channel-mix shift (more low-intent users from a new paid channel, for example) can look like a retention decline when it's really a composition change.
• Retention broken out by platform/device, to rule out a technical regression specific to one platform.

Confounding factors to watch for

• Acquisition-channel mix shifts: if a cheaper but lower-intent channel grew as a share of signups over the period, average retention will fall even if EVERY individual channel's retention is unchanged, a classic Simpson's-paradox-style trap.
• Seasonality: if the six-month window spans a seasonal dip (a post-holiday lull, for example), part of the decline may be a normal yearly pattern rather than a genuine regression.
• Instrumentation changes: verify that the definition of 'active' (the retention numerator) hasn't quietly changed partway through the period, which would make the cohorts artificially incomparable.

Trade-offs and pitfalls

Jumping to a product-change explanation before ruling out channel-mix shifts and seasonality is the most common analytical mistake here; segment first, hypothesize second.

Direct answer

A moat's durability is not a one-time judgment, it has to be re-tested every time the underlying conditions that created it change. The right response is to separate the specific mechanism the advantage relies on from the fact that the company has historically been profitable, and ask directly whether that mechanism still holds given the new conditions, rather than assuming past profitability is evidence the advantage is intact.

Structured elaboration

Work through it in three steps:

1. Name the mechanism precisely. Is the advantage really a network effect (the product gets more valuable as more people use it), a switching cost (real friction or risk in leaving), an economy of scale (unit costs that only a large player can achieve), or something else entirely? Vague language like "brand" or "being first" is often standing in for a mechanism nobody has actually named.
2. Identify what specifically changed. New technology can lower the cost or skill required to replicate what used to take years to build. A shift in customer behavior can reduce the friction of switching that used to be real. A new distribution channel can let a challenger reach customers without needing the incumbent's original advantage at all.
3. Look for leading indicators, not trailing ones. Revenue and margin are lagging signals; by the time they move, the erosion is often well underway. Better leading signals: the pace and quality at which new entrants are winning customers, whether the incumbent is discounting defensively (a company cutting price to hold share is often a company that already senses the mechanism weakening), whether the most sophisticated or price-sensitive customers are the first to leave, and whether a challenger is winning on a genuinely different axis rather than just spending more on marketing.

The core distinction to hold onto throughout: more competitors showing up, or a rival spending more to compete, is noise. A rival achieving comparable value at a structurally lower cost, using the same mechanism the incumbent used to own, is erosion.

Worked example

Consider a company whose advantage historically came from the cost and time it took to build a large, high-quality proprietary dataset or model, a real barrier few competitors could clear. If a new class of general-purpose tools now lets a challenger reach comparable output quality with a fraction of the data and engineering investment, that is a direct hit on the specific mechanism, even if the incumbent's revenue and existing customer base have not moved yet. The leading indicator to track here is not "did we lose customers" but "can a new entrant now hit our quality bar at a materially lower cost than we originally needed," which can be checked well before any customer actually churns, by watching what challengers are shipping and at what apparent cost.

Trade-offs & pitfalls

The biggest risk is complacency: waiting for revenue to visibly decline before acting, since moats tend to erode gradually and then suddenly, by which point the response options are much narrower and more expensive. The opposite risk is overcorrecting, treating every new entrant or every price move by a competitor as an existential threat, which wastes resources chasing noise instead of the real signal. The senior-level discipline is building a small, explicit set of leading indicators tied to the specific mechanism behind the company's advantage, reviewed on a regular cadence, rather than either ignoring the shift or reacting only once the market has already forced the issue.

Direct answer: The sliding-window (two-pointer) technique transforms a naive $O(n^2)$ scan of all subarrays/substrings into $O(n)$ by maintaining a contiguous "window" with two pointers (left and right boundaries) that each move forward AT MOST $n$ times total across the whole algorithm - instead of restarting the inner scan from every possible left boundary, the window incrementally EXPANDS (advance right) or CONTRACTS (advance left) based on whether the current window satisfies some condition, reusing work already done rather than recomputing from scratch.

Structured elaboration

The naive approach to "find something about every contiguous subarray" tries every $(i, j)$ pair of boundaries explicitly - $O(n^2)$ pairs, each potentially requiring $O(n)$ work to evaluate the subarray, for as much as $O(n^3)$ naively (or $O(n^2)$ if each subarray's property can be evaluated in O(1) incrementally from the previous one). The sliding-window insight: for many such problems, as the RIGHT boundary advances, the optimal or relevant LEFT boundary only ever moves FORWARD too (never needs to backtrack) - so instead of trying every left boundary for every right boundary, you can maintain a single window and incrementally adjust its two ends. Since each pointer only ever moves forward, and each can move at most $n$ times total (not per outer iteration), the TOTAL work across the whole algorithm is $O(n)$, not $O(n^2)$.

Worked example

For "find the length of the longest substring with no repeated characters": naively, you'd check every substring for repeated characters - $O(n^2)$ substrings, each taking up to $O(n)$ to verify, giving $O(n^3)$ naively (or $O(n^2)$ with a smarter per-substring check). With sliding window: expand the right pointer one character at a time, tracking seen characters in a set; if a repeat is found, advance the LEFT pointer (removing characters from the set) until the repeat is resolved, then continue expanding right. Both pointers move only forward, together traversing at most $2n$ total steps across the whole string - $O(n)$.

Trade-offs & pitfalls

• Sliding window CANNOT be applied directly when the window's "validity" condition is not MONOTONIC as the window grows - specifically, when adding an element to the right could make a currently-invalid window valid again without needing to shrink from the left (breaking the "left pointer only moves forward" assumption). A concrete example: "find a subarray whose sum is exactly K" (not "at least K" or "at most K") when the array can contain NEGATIVE numbers - here, shrinking the window from the left doesn't monotonically increase or decrease the sum in a predictable direction, so the standard two-pointer approach's core assumption breaks, and you typically need a different technique (like a prefix-sum-plus-hash-map approach) instead.
• The window's tracked STATE (a running sum, a character-frequency map, a count of distinct elements) must be updateable in O(1) as the window's boundaries move - if maintaining that state incrementally is itself expensive, the overall O(n) bound doesn't hold.
• Recognizing WHEN a problem has the right monotonic structure for sliding window (versus superficially resembling one) is the real skill - the technique's mechanics are simple once you've correctly identified that the problem qualifies.

Direct answer

Write each stage (read, filter, impute, feature creation, write) as a small pure function that takes a DataFrame and returns one, and compose them with .pipe() so the pipeline reads top-to-bottom as its own table of contents. Each function can then be unit-tested in complete isolation with a two- or three-row DataFrame, with no need to run the rest of the pipeline or touch the filesystem.

Approach

import pandas as pd

def read_csv(path):
    return pd.read_csv(path, parse_dates=["event_time"])

def filter_rows(df):
    return df[df["event_time"].notna() & (df["revenue"] > 0)]

def impute(df):
    df["country"] = df["country"].fillna("unknown")
    df["revenue"] = df["revenue"].fillna(0)
    return df

def create_features(df):
    # cast the period to str: parquet's partition_cols can't hash a raw Period dtype column
    df["month"] = df["event_time"].dt.to_period("M").astype(str)
    df["rev_per_item"] = df["revenue"] / df["items"].replace(0, 1)
    return df

def run_pipeline(input_path, output_dir):
    result = (
        read_csv(input_path)   # the chain starts from the function that produces the first DataFrame,
        .pipe(filter_rows)     # not from an undefined variable piped through read_csv
        .pipe(impute)
        .pipe(create_features)
    )
    result.to_parquet(output_dir, partition_cols=["month", "country"], engine="pyarrow")
    return result

Key points:

• The chain has to start from a call that actually produces a DataFrame (read_csv("input.csv")), not from .pipe()-ing an undefined name through a reader function; .pipe() threads an existing DataFrame through the next step, it can't conjure the first one.
• create_features casts month to a plain string before it becomes a partition column: to_period("M") produces a pandas-specific Period dtype, and PyArrow's write_to_dataset (which to_parquet(partition_cols=...) uses under the hood) cannot hash that extension dtype as a partition key, so writing the raw Period column raises ArrowNotImplementedError: Keys of type extension<pandas.period<ArrowPeriodType>>.
• impute and create_features each receive the DataFrame that the previous .pipe() call already produced fresh (from filter_rows's boolean-indexed slice, which is itself already a new object under Copy-on-Write), so a plain df["col"] = ... inside them is an ordinary column assignment, not the chained-slice pattern that pandas 3.0 flags.

Worked example

pd.DataFrame({
    "event_time": ["2026-01-05", "2026-01-20", "2026-02-01", None, "2026-02-10"],
    "revenue": [10.0, 0.0, 25.0, 15.0, -5.0],
    "items": [2, 1, 0, 3, 1],
    "country": ["US", None, "DE", "US", "FR"],
}).to_csv("input.csv", index=False)

result = run_pipeline("input.csv", "out/")
print(result)

Output (verified against pandas 3.0.3, pyarrow):

  event_time  revenue  items country    month  rev_per_item
0 2026-01-05     10.0      2      US  2026-01           5.0
2 2026-02-01     25.0      0      DE  2026-02          25.0

Of the five input rows, three are filtered out by filter_rows: the row with revenue == 0, the row with a missing event_time, and the row with negative revenue (a data-quality problem, not something impute should paper over). The items == 0 row on 2026-02-01 survives filtering (its revenue is positive) and rev_per_item correctly falls back to dividing by 1 instead of 0 via .replace(0, 1). Writing this result with partition_cols=["month", "country"] succeeds and reading it back with pd.read_parquet("out/") reproduces the same two rows.

Complexity and edge cases

Complexity: each stage is a single O(n) pass over the rows it touches; .pipe() itself adds no algorithmic cost, it is function composition. Memory: each .pipe() call's result is a fresh DataFrame (a shallow copy for the untouched columns plus whatever the step added or filtered), so peak memory during the chain is bounded by roughly the size of the largest single intermediate, not the sum across every stage, since Python frees each intermediate once the next step has consumed it.

Edge cases: an items value of exactly 0 needs the .replace(0, 1) guard or rev_per_item divides by zero; a missing event_time is dropped by the filter rather than imputed, since a synthetic timestamp would be a worse assumption than dropping the row; an already-unknown or already-zero-filled value passing through impute a second time is a no-op, so the function is safe to call more than once (idempotent) if the pipeline is ever re-run on partially-processed data.

Trade-offs and pitfalls

Unit-testing each function in isolation is the main payoff of this shape: create_features can be tested with a two-row DataFrame asserting the exact rev_per_item value and that month is a plain string (not a Period), without needing a real CSV file or a filesystem to write parquet to; impute can be tested by asserting that a null country becomes "unknown" and a null revenue becomes 0, again with no dependency on the rest of the chain. Mocking I/O becomes trivial too, since read_csv and the final to_parquet call are the only two functions that touch the filesystem, everything in between is a pure DataFrame-to-DataFrame transformation that pytest fixtures can exercise directly. The pitfall in a .pipe() chain specifically: because each step only runs when the chain executes, not when it's defined, a bug like the undefined-starting-variable above or a missing import inside a step function stays completely invisible until someone actually runs the full pipeline end-to-end, which is exactly why testing each function independently (rather than trusting that the chain "looks right") is the point of building it this way in the first place.

Direct answer. UNION ALL simply stacks the rows from both queries, including duplicates. UNION does the same stacking and then performs an implicit dedup pass over the combined result, which costs extra work and is only worth paying for when you actually need it.

Structured elaboration. Reach for UNION ALL by default. Only add the dedup pass (plain UNION) when duplicate rows would genuinely be wrong for what you're building, for example combining exports from two systems that are known to sometimes report the exact same event twice. The cost of the dedup isn't just conceptual: on a large combined result, UNION effectively does a sort or hash pass over every column to find duplicates, whereas UNION ALL does none of that.

Worked example. jan_sales(customer_id, amount): (1,100), (2,50). feb_sales(customer_id, amount): (2,50), (3,80). Note customer 2 appears in both months with the identical amount (say, a reporting overlap).

SELECT * FROM jan_sales
UNION ALL
SELECT * FROM feb_sales
ORDER BY customer_id;
-- returns 4 rows: (1,100), (2,50), (2,50), (3,80). Customer 2 appears twice.

SELECT * FROM jan_sales
UNION
SELECT * FROM feb_sales
ORDER BY customer_id;
-- returns 3 rows: (1,100), (2,50), (3,80). The duplicate (2,50) row got collapsed to one.

Trade-offs and pitfalls. The risk with defaulting to UNION ALL is that if the two overlapping sources genuinely CAN produce the exact same logical row twice (as in this example), you'll silently double-count it in any downstream SUM. The risk with defaulting to UNION is the performance cost at scale, and a subtler correctness trap: UNION's dedup works on the WHOLE row, so two rows that are conceptually the same event but differ in even one column (a slightly different timestamp, an extra trailing space) won't be deduplicated at all, giving a false sense of safety. If you need dedup on a specific business key rather than the whole row, UNION ALL followed by an explicit GROUP BY or ROW_NUMBER() is usually the more precise tool.

Direct answer

A threshold alert fires when a metric crosses a fixed, pre-agreed line, such as below 90% of a stated baseline, while statistical or anomaly-detection alerting, for example change-point detection or EWMA (exponentially weighted moving average), fits a model to the metric's normal noise, trend, and seasonality and fires only when the metric deviates from what that model expected. Thresholds are simpler and more explainable, so they suit stable, well-understood metrics with a clear business rule; statistical methods cost more to build and maintain but produce far fewer false alarms on metrics with real day-to-day or weekly variation.

Structured elaboration

A practical rule: start with a threshold for metrics with a clear, unchanging business rule, and add statistical detection incrementally for the noisy ones. Combining both, so a threshold breach only pages if the statistical detector also confirms an abnormal pattern, cuts noise while keeping a simple safety net.

Worked example

A metric holds steady around 100 for two days, then drifts down over three more: 100, 95, 90, 85, 50.

Static threshold rule: alert if the value falls below 90% of baseline, that is, below 90. The metric first crosses this on day 4 (85 < 90).

EWMA with smoothing parameter $\alpha = 0.3$, seeded at day 1's value, using $EWMA_t = \alpha x_t + (1-\alpha) EWMA_{t-1}$:
$EWMA_1 = 100$
$EWMA_2 = 0.3(95) + 0.7(100) = 28.5 + 70 = 98.5$
$EWMA_3 = 0.3(90) + 0.7(98.5) = 27 + 68.95 = 95.95$
$EWMA_4 = 0.3(85) + 0.7(95.95) = 25.5 + 67.165 = 92.665$
$EWMA_5 = 0.3(50) + 0.7(92.665) = 15 + 64.8655 = 79.8655$

The static threshold and the EWMA agree on the real anomaly, both react by day 4 or 5, but the EWMA smooths out a single noisy day, since a one-day dip to 90 alone would not have moved it below the 90 line, while the static threshold treats day 4's 85 exactly the same whether it's an isolated blip or the start of a real decline. The EWMA's day-over-day change is what signals which case it actually is.

Trade-offs & pitfalls

• A static threshold set once during a high-traffic period will sit wrong, too loose, once traffic normalizes; revisit thresholds on a schedule, not only when someone complains.
• An EWMA or change-point detector tuned only on historical data can miss a genuinely new failure mode the training window never saw; keep a hard floor threshold as a backstop even when running a statistical detector.
• Combining both methods, requiring agreement before paging, reduces false positives but can also delay a real critical alert by however long the second check takes to confirm; that delay needs to be small relative to the incident's cost of being late.