Microsoft Senior Data Scientist Interview Preparation Guide - 2026

Direct answer: Preparing monthly per-store revenue for a linear regression forecast requires handling seasonality explicitly (rather than assuming a flat trend), encoding categorical store attributes sensibly, and deciding a clear missing-value policy, since a naive month-over-month feed with none of these will systematically mis-predict around any seasonal pattern.

Structured elaboration:
For seasonality: include calendar features (month, a holiday flag) directly as inputs, or explicitly detrend/deseasonalize the target before modeling and re-apply the seasonal pattern to the forecast afterward; a linear model with no seasonal information at all will systematically under- or over-predict during predictably high or low months, showing up as a repeating pattern in the residuals if you check for it. For categorical store attributes (region, store format, store size category): encode with an approach appropriate to the number of distinct categories and the model type (one-hot for a modest number of categories feeding a linear model is usually fine). For missing values (a store with a gap in its revenue history, perhaps from a temporary closure): decide explicitly whether to impute, exclude that period from training, or flag it, since a naive fill (like zero) would badly distort a revenue-forecasting target specifically.

Worked example: A retailer's holiday-season stores show a large, predictable revenue spike every November-December; a model trained without any seasonal feature will consistently underpredict this period every year, a systematic and avoidable error, whereas including month and a holiday flag as inputs lets the linear model learn and correctly apply that seasonal adjustment going forward.

Trade-offs and pitfalls: A store with a data gap from a temporary closure needs a deliberate decision, not a default: naively imputing a "normal" revenue value for a period the store was actually closed would teach the model an artificially smoothed pattern that doesn't reflect what real closures do to revenue, which matters if closures (planned or unplanned) are a realistic scenario the model needs to handle correctly in the future.

Direct answer. BigQuery's on-demand model charges per byte scanned by each query with no upfront commitment, which is simple and cost-effective at low or unpredictable volume. Slot-based capacity reservations, sold today as BigQuery Editions (Standard, Enterprise, and Enterprise Plus) rather than the older flat-rate purchase model Google retired in 2023, let you commit to a fixed amount of query-processing capacity (slots) for a predictable, discounted rate, which becomes cheaper and more performance-predictable once query volume is high and steady. For an organization mixing heavy periodic workloads with dashboards that must stay responsive, the right answer is usually a reservation strategy that isolates the two, not a single global choice.

Structured elaboration. BigQuery's overall pricing has three components: storage cost (charged per GB stored regardless of query model), query compute cost (either on-demand per-byte-scanned or Editions-based slot capacity), and streaming-insert cost (charged separately when you stream rows in rather than batch-load them). Under on-demand pricing, a query that scans 1TB costs a fixed dollar amount regardless of how long it takes; under a capacity commitment, you buy a reserved number of slots (with per-second or longer commitment terms depending on the edition and commitment length you choose) and queries draw from that pool for no additional per-byte charge. The crossover point where a capacity commitment becomes cheaper than on-demand depends on your total monthly bytes scanned, but as a rule of thumb, teams running consistently heavy analytical workloads (data science exploration, ML feature computation, large ETL) tend to save money moving to reservations, while teams with light, bursty usage are usually better off on-demand.

To make that crossover concrete, walk it with simplified, illustrative numbers (not an actual current price list; check live pricing for real decisions): suppose on-demand billing works out to $6 per TB scanned, and a team scans 40TB in a typical month, so on-demand costs about $240 that month ($6 x 40). If a slot reservation sized for that team's steady workload is quoted at $1,500/month, the reservation only becomes the cheaper option once the team's actual monthly on-demand-equivalent spend would have exceeded $1,500, which happens at $1,500 / $6 = 250TB scanned in a month. Below 250TB of steady monthly usage, staying on-demand is cheaper; only once usage is consistently well past that volume does the fixed reservation price win out, which is the general shape of the rule of thumb above, made concrete with one traceable number.

Worked example. For an organization with several analytic teams running periodic heavy batch workloads (say, nightly feature computation over terabytes of data) alongside interactive BI dashboards that need to stay responsive during business hours, propose reservation assignments rather than one shared pool: create a dedicated reservation with guaranteed slots for the BI/dashboard workload, sized to keep p95 dashboard latency low even when other work is running, and a separate reservation (or on-demand billing) for the batch/data-science workload, which can tolerate queueing during traffic spikes. This isolation is the point: without it, a single heavy nightly batch job competing for the same shared slot pool as an executive dashboard can starve the dashboard exactly when someone is watching it live. Google's slot-assignment mechanism lets you map specific projects or folders to specific reservations, so the BI team's project draws only from its guaranteed pool regardless of what the data-science team is doing concurrently.

Trade-offs and pitfalls. Reservations only pay off if utilization stays high; a reservation sized for peak load that sits mostly idle outside of that peak wastes money compared to on-demand. Conversely, under-sizing a reservation for genuinely heavy, steady usage causes queries to queue and can make dashboards feel slower than they would on unconstrained on-demand pricing. Monitor slot utilization over at least a few weeks of real traffic before committing to a reservation size, and revisit it as usage grows, since a reservation that was well-sized at launch can become a bottleneck a year later.

Direct answer

A novelty effect is a temporary inflation of an early treatment effect: users explore or click on something purely because it is new, and that extra engagement fades once the feature stops being novel, biasing an early readout upward. A primacy effect (sometimes called a change-aversion or resistance-to-change effect) is the opposite pattern: a change disrupts a habitual workflow, so users are temporarily worse off while they relearn it, biasing an early readout downward, then the effect climbs toward its true level as users adapt. Both biases fade over roughly the same kind of horizon, so trusting a week-one number without checking its trajectory can make you launch a fad or kill a genuine win too early.

Structured elaboration

Mechanism and direction

Diagnostics to tell a spike from a persistent effect

• Time-windowed effect plot: daily or weekly treatment effect with confidence intervals, ideally with a smoothed trend line (LOESS or a spline), not a single pooled average. A genuine effect looks like a roughly flat band around a nonzero value; novelty looks like a spike that decays toward that band; primacy looks like a trough that rises toward it.
• Exposure-age cohorts, not calendar time: plot the effect against days since each user's first exposure for a fixed cohort of users first exposed on the same day, rather than calendar date. A calendar-time plot mixes newly exposed users (still novel-biased) with long-exposed users (already stabilized) every single day, which can mask a real decay curve as a flat line.
• New vs. returning user split: novelty is usually concentrated in users encountering the feature for the first time; if the effect is similar in a segment already exposed for weeks, that argues against novelty as the explanation.
• Change-point or decay model on the daily series: fit a time-varying effect model, effect as a function of exposure age, and test whether the transient component is statistically distinguishable from zero, separately from the asymptotic (persistent) component.
• Placebo check: run the same time-windowed analysis on a pre-launch period with no real treatment; if spike-like patterns appear there too, the "decay" you see in the real experiment may just be normal week-to-week noise, not a novelty artifact.

Adjusting duration and analysis

• Pre-register the analysis window before launch rather than reading the metric the moment it looks good; a fixed rule such as "primary read is the average effect over exposure-days 21 to 35" prevents cherry-picking the peak or the trough.
• Extend the experiment until the exposure-age curve visibly plateaus, or the fitted transient component's confidence interval crosses zero, rather than for a fixed calendar duration chosen in advance.
• Report both the early-window and late-window effect side by side rather than a single blended number; a launch decision based only on the blended average silently averages a fading spike with a stabilizing floor.

Worked example

Two hypothetical (illustrative, not real study data) weekly average-treatment-effect readings for the same nominal conversion metric:

Both curves are converging toward roughly the same persistent level, one from above and one from below, which is exactly the signature that separates them from a flat, genuine effect that would show roughly the same number every week within noise.

If the transient component decays exponentially, $\Delta(t) = C + A e^{-\lambda t}$, where $A$ is the size of the initial novelty or primacy spike above the persistent effect $C$ (the extra amount present at $t=0$ that fades away over time), and the illustrative decay rate is $\lambda = 0.2$ per week, its half-life is:
$t_{1/2} = \frac{\ln 2}{\lambda} = \frac{0.693}{0.2} \approx 3.5 \text{ weeks}$
That is the kind of number worth pre-registering as a decision rule: run at least three half-lives (about 10 to 11 weeks here) before reading the persistent effect $C$, rather than picking an arbitrary duration.

Trade-offs and pitfalls

• Waiting out a full decay curve costs calendar time and opportunity cost on other experiments; for low-stakes features, teams sometimes accept the risk of a novelty-inflated launch decision rather than run for months.
• Segmenting by exposure age needs per-user first-exposure timestamps captured in the assignment log; if you only log calendar-date rollups, you cannot separate calendar effects from exposure-age effects after the fact.
• A curve that looks like decay can just as easily reflect unrelated seasonality (marketing pushes, holidays) that correlates with launch timing; a decay-shaped curve is suggestive, not conclusive, on its own.
• Don't assume every early spike is novelty and every early trough is resistance to change: an early spike can be a genuine effect solving a pent-up need immediately, and an early trough can be a real bug that later gets patched. The pattern is evidence, not proof, and should be paired with qualitative checks (support tickets, session recordings) before concluding the mechanism.

Direct answer

Building trust with a stakeholder group that starts out skeptical is a compounding process best run as a 90-day plan with visible, honestly-reported quick wins early, structural changes to how you communicate in the middle, and a track record you can point back to by the end, rather than a single persuasive pitch.

Structured elaboration

• Days 1 to 30: quick, honest wins. Deliver something small, useful, and verifiable quickly, and be transparent about limitations rather than overselling it. A modest result honestly reported builds more trust than an impressive one that later turns out to be overstated.
• Days 30 to 60: structural transparency. Introduce practices that make your work checkable, not just trustworthy on your word: sharing underlying data or methodology on request, inviting review before finalizing conclusions, or a regular office-hours slot where skeptics can raise concerns directly.
• Days 60 to 90: track record and calibration. Look back at what was predicted versus what happened, including any misses, and share that honestly. A pattern of accurate, appropriately-hedged predictions, openly reviewed, is what actually earns durable trust rather than a single good outcome.
• Throughout: consistency matters more than any single gesture. Trust erodes faster than it builds; one instance of overselling a result or hiding a caveat can undo several honest ones.

Worked example

Building trust with a stakeholder group skeptical of an analytics team's recommendations, an early quick win might be a small, verifiable finding delivered with an explicit statement of its limitations rather than an overconfident pitch. A recurring open office-hours session where anyone can ask "how was this number calculated" introduces structural transparency. By day 90, reviewing three earlier predictions against what actually happened, including one that was off and explaining why, does more for durable credibility than three unqualified successes presented without any scrutiny.

Trade-offs and pitfalls

A 90-day plan this deliberate can feel slow to stakeholders who want faster proof, and some quick wins chosen for their safety (low risk of being wrong) can look unambitious. Balance early wins that are genuinely safe to promise with at least one that demonstrates real capability, not just caution.

Direct answer

The right way to handle a column's missing values depends first on WHY the data is missing, not just on how much is missing; understanding whether the missingness is completely random (MCAR), related to observed data (MAR), or related to the missing value itself (MNAR) should drive the choice between dropping, imputing, or explicitly flagging missingness as its own category.

Structured elaboration

1. MCAR (missing completely at random): the fact that a value is missing has nothing to do with any variable, observed or not. Dropping rows is relatively safe here since it doesn't systematically bias the remaining data, though it still costs sample size.
2. MAR (missing at random, conditional on observed data): missingness correlates with something you CAN observe (e.g. income is more often missing for self-employed respondents, and you know who's self-employed). Imputation conditioned on the observed correlate (model-based, or at least stratified by the correlate) is more defensible than a single global mean/median.
3. MNAR (missing not at random): the missingness itself depends on the unobserved value (e.g. very high earners are more likely to decline to state income). This is the hardest case, naive imputation can systematically bias results, and an explicit "missing" indicator/category, or a domain-informed model of the missingness mechanism itself, is often more honest than pretending you can fill it in accurately.
4. When to add an indicator instead of imputing a value at all: if the fact that a value is missing is itself informative (which is common under MAR/MNAR), an explicit missing-indicator feature can carry real signal that a filled-in value would erase.
5. Communicating the choice: document which strategy was used per column and why, since a stakeholder consuming a dashboard or a model built on imputed data needs to know how much of what they're looking at is real versus filled-in, especially for columns with high missingness rates.

Worked example

A churn dataset with missing values in income, last_login, and plan_type, where the missingness patterns differ:

• plan_type missing only for very old accounts predating a schema migration: this looks close to MCAR (the migration is unrelated to any account's actual behavior), dropping or imputing with the overall mode is comparatively low-risk.
• last_login missing specifically for accounts that never logged in after signup: this is closer to MNAR-adjacent (the missingness IS the signal, "never logged in"), so an explicit "never logged in" category, not any numeric imputation, is the appropriate handling.
• income missing more often among certain segments who chose not to disclose it: closer to MAR/MNAR, a global mean/median fill would understate the real variation and should be flagged to stakeholders as a known limitation, or handled with a segment-conditioned estimate instead.

Trade-offs and pitfalls

• Defaulting to "just impute with the mean/median" for every column regardless of the missingness mechanism is the most common mistake; it's the easy answer but not always the correct one.
• An explicit missing-indicator column can leak information in a modeling context if the indicator itself was constructed using information not actually available at prediction time; treat it with the same point-in-time discipline as any other feature.
• Communicating "this metric is based on N% imputed values" honestly changes how much a stakeholder should trust a headline number, saying so is uncomfortable but is exactly the trust-preserving move a senior analyst makes.

Situation: At a previous company, a product lead asked me to "just surprise me with insights" after we acquired a new dataset. That open request risked wasted effort and unverifiable results.

Task: I needed to convert exploratory curiosity into a reproducible, valuable deliverable with measurable outcomes.

Action:

• I asked clarifying questions to set scope and success metrics:
  • What business decisions could change based on findings? (pricing, retention, feature roadmap?)
  • Who is the audience and preferred delivery format? (execs: top-line, analysts: notebooks/dashboards)
  • What is “surprising” vs. “actionable”? Do you want hypotheses tested or new hypotheses generated?
  • Any forbidden analyses or compliance constraints? Data freshness, update cadence, SLAs?
  • Preferred KPIs to impact (e.g., increase retention by X%, reduce churn by Y)?
• I stated assumptions to align expectations:
  • I’ll prioritize reproducibility: code in a notebook, parameterized pipeline, and versioned data snapshots.
  • Initial deliverable = 2-week exploratory report + 3 ranked opportunities with expected impact estimates and confidence levels.
  • Follow-up: handover dashboard or automated weekly report if an insight is adopted.
• I produced deliverables: EDA notebook (cleaning steps, visualizations), a short slide deck with 3 recommended experiments, estimated ROI/impact, and a reproducible pipeline on Git with tests.

Result: Stakeholder accepted the 3 prioritized experiments; one A/B test increased conversion 4% (estimated impact validated). The reproducible pipeline enabled quarterly reruns and established clear metrics for future exploratory requests.

This approach turns vague asks into focused, measurable, and repeatable analysis while preserving room for serendipity.

Direct answer

Pick a project that genuinely needed more than one function, and be specific about two things: what YOU owned (not what 'the team' did), and the one concrete mechanism that determined whether the collaboration worked, such as a shared definition of done, a clear handoff point, or clarity on who decided what when opinions differed. Vague answers ('we communicated well') sound rehearsed; specific answers sound lived-in.

What the story needs to show

Your specific contribution. Interviewers are listening for what you personally decided or built, distinct from what your collaborators did. If every sentence is 'we', the interviewer cannot tell what you'd do differently on the next team.

A mechanism-level explanation. Organize the story around one of three lenses:

• Shared goal: did every function agree on what 'done' looked like and how success would be measured, or was each function quietly optimizing for its own definition?
• Interface or handoff: was there a clear point where work crossed from one function to another, and was that point actually defined, or did people guess?
• Decision rights: when functions disagreed, was it clear whose call it was, or did disagreement just stall until someone got tired of arguing?

Honesty if it's a struggle story. The question explicitly allows 'succeed or struggle'. A good struggle story ends on what you changed about the collaboration, not on who was at fault.

Worked example

Situation: [your team] needed to deliver [a feature or initiative] that required real work from [Team A, for example a design or research function] and [Team B, for example a data or infra function], against a fixed external date.
Task: your role was the one connecting the three groups, for example owning the shape of the interface between design and engineering, or owning how data requirements got translated into a schema.
Action: early on, each function had a different idea of what 'done' meant for their piece, which caused rework when the pieces met. You wrote a short one-page agreement naming the shared definition of done and who would sign off on each handoff, and used it to resolve the next two disagreements without a meeting.
Result: the project shipped on the revised date, and the agreement itself became something the group reused on the next cross-functional piece of work, which is the real marker of a story about redesigning the collaboration rather than just pushing through it.

To make that skeleton concrete rather than a fill-in-the-blank: picture a checkout redesign that needed real work from the design function and the payments engineering function, against a fixed external date tied to a promotional campaign launch. The specific disagreement was about what 'done' meant for the new payment-method selector: design considered the screen done once every state (loading, error, empty) matched the approved mockups pixel-for-pixel, while payments engineering considered it done once the integration correctly handled every payment-provider response code, even ones with no mockup drawn yet. That mismatch caused two rounds of rework when a payment-provider error state shipped without a design pass. The one-page agreement that resolved it included this line: 'A screen is done when it matches an approved mockup for every state the payments API can return, and any new state discovered after mockups are drawn triggers a joint 15-minute review before either side builds it.' That single sentence is what let the two functions stop re-litigating 'done' every time a new edge case appeared, and both sides signed off on it before the next round of work began.

Trade-offs and pitfalls

• A generic 'we all communicated well' answer with no mechanism is the single most common weak version of this story, avoid it.
• Over-crediting the team at the expense of your own specific contribution leaves the interviewer unable to evaluate you.
• If you pick a struggle story, resist framing it as the other function's fault. The senior version of this answer explains what you changed about how the groups worked together, not who dropped the ball.
• The strongest answers show you redesigning a structure (a handoff, a shared definition, a decision rule), not just working harder inside a broken one.

Situation: I'm responsible for measuring inference latency for a production model and must report p50, p95, p99 including cold-starts and warm requests.

Process (measurement-driven):

1. Define events & labels

• Instrument at ingress (request receive) and egress (response send).
• Record timestamps for: arrival, model queue start, model exec start, exec end.
• Tag requests with context: model version, instance id, request type, and a "cold_start" boolean (true if instance was just started or containerless request).

2. Instrumentation & storage

• Emit per-request latency spans to tracing/metrics: total_latency = egress - ingress; queue_latency; exec_latency.
• Use an HDR histogram library (or Prometheus histograms + exemplars) to record latencies with high dynamic range and to compute p50/p95/p99 accurately.
• Send both per-instance and aggregated histograms to monitoring (Datadog/Prometheus/Cloud Monitoring).

3. Sampling strategy

• Record all cold-starts (they're rare); sample warm requests (e.g., 1–5% or rate-limited) but ensure sampling is stratified by traffic source and instance type to avoid bias.
• For high-volume endpoints, use deterministic sampling (hash of request id) so sampled data is consistent across services/traces.

4. Handling noisy outliers

• Keep raw histograms and a cleaned view: compute percentiles on both raw and truncated data.
• Define outlier rules (e.g., requests > mean + 10*sigma OR > configurable absolute cap like 30s) and mark—not drop—them.
• Report p50/p95/p99 on:
  a) all requests,
  b) warm-only,
  c) cold-only,
  d) clipped-to-cap (for operational SLAs).
• Surface sample counts and confidence intervals (bootstrap or streaming confidence from HDR) so stakeholders see estimate uncertainty.

5. Diagnostics & dashboards

• Dashboards with p50/p95/p99 for total/warm/cold, plus breakdowns by model version and instance.
• Links to traces for p99 latencies, and alerts when p95/p99 or cold-start rate increases.

Why this works:

• Timestamp granularity separates queue vs exec causes.
• HDR histograms and stratified sampling give accurate tails with bounded storage.
• Marking outliers preserves visibility while preventing a few noisy events from misleading SLA metrics.

Direct answer. Approximate techniques (most commonly HyperLogLog for distinct counts, and similar probabilistic sketches for other aggregates) trade a small, quantifiable, and tunable error rate for a dramatic reduction in the memory and computation an exact count would require, which is the right trade when the business decision the number feeds doesn't actually hinge on exact precision.

Structured elaboration. An exact DISTINCT count over a massive dataset generally has to track every unique value seen, memory or disk cost scaling with the number of distinct values, which becomes genuinely expensive at high cardinality and high volume. A probabilistic cardinality sketch instead maintains a small, fixed-size summary (independent of how many distinct values there actually are) that can estimate the true distinct count within a known, tunable error bound, commonly around 1-2% for HyperLogLog at practical configurations, in exchange for that summary using a small constant amount of memory rather than growing with the data.

Worked example. A "distinct visitors this month" metric computed nightly for an internal dashboard, where a 1-2% error is invisible to anyone reading the number and completely irrelevant to any decision it informs, is a strong candidate for an approximate technique; a count feeding a legal or financial reconciliation process, where every unit matters and the number needs to tie out exactly against an external source, is not, regardless of how expensive the exact computation is.

Trade-offs and pitfalls. Presenting this trade-off honestly to a stakeholder means being explicit about both the error bound and what it does and doesn't affect: the sketch is well-calibrated (the true value falls within the stated bound with known probability), but stakeholders who are used to seeing exact numbers may reasonably want that distinction called out clearly rather than silently swapped in, especially the first time a number they're used to being exact stops matching a manually-computed spot check by a small amount. A good practice is to label approximate metrics as approximate in the dashboard or report itself, not just in an internal engineering doc, so the distinction is visible to whoever's making decisions with the number.

Complexity

An exact distinct count costs memory proportional to the number of distinct values (in the worst case, proportional to the row count); a cardinality sketch costs a small, FIXED amount of memory regardless of how many distinct values exist, which is the entire source of its scalability advantage.

Edge cases

Extremely low-cardinality columns (very few distinct values) get little practical benefit from a probabilistic sketch, since an exact count there is already cheap; the technique earns its keep specifically at high cardinality and high data volume, where the exact approach's cost genuinely becomes a problem.

Direct answer

Build one boolean mask per condition, amount > 100, status.isin([...]), created_at.between(...), and combine them with & for AND / | for OR, wrapping every individual comparison in parentheses. Parentheses are required because Python's & and | bind tighter than comparison operators like > and ==, so without them the expression groups incorrectly and pandas raises rather than silently misevaluating.

Approach

import pandas as pd

orders = pd.DataFrame({
    'order_id': [1, 2, 3, 4],
    'user_id': [10, 11, 12, 13],
    'amount': [50, 150, 200, 90],
    'status': ['complete', 'shipped', 'pending', 'complete'],
    'created_at': ['2024-01-15', '2024-02-20', '2024-02-25', '2024-04-01'],
})
orders['created_at'] = pd.to_datetime(orders['created_at'])

mask_amount = orders['amount'] > 100
mask_status = orders['status'].isin(['complete', 'shipped'])
mask_date = orders['created_at'].between('2024-01-01', '2024-03-31')

result = orders[mask_amount & mask_status & mask_date]

# Equivalent with .query()
result_q = orders.query(
    "amount > 100 and status in ['complete', 'shipped'] "
    "and created_at >= '2024-01-01' and created_at <= '2024-03-31'"
)
# result.equals(result_q) -> True

Output (only order_id 2 satisfies all three conditions: amount 150 > 100, status "shipped", created_at 2024-02-20 in range):

   order_id  user_id  amount   status created_at
1         2       11     150  shipped 2024-02-20

Key points

• Use & / | for elementwise boolean-Series logic, never the Python keywords and / or, which only work on single scalar truth values and raise on a Series.
• Wrap each comparison, (orders['amount'] > 100), in parentheses before combining with &/|; the operator-precedence trap is the single most common bug in hand-written boolean masks.
• .query() reads more like SQL and lets you write and/or/in directly as keywords inside the string, since the expression is parsed and evaluated separately from normal Python operator precedence.

Complexity

Each comparison, isin, or between call is a single vectorized O(n) pass over the column. Combining k masks with & is O(k*n) total. .query() compiles the expression once and evaluates it in a comparable O(n) pass (and can use numexpr under the hood for large frames to reduce the number of intermediate boolean arrays materialized). Memory: each intermediate boolean mask is O(n) at 1 byte per element, and the final result is O(m) for the m matching rows.

Edge cases

• NaN in a compared column: any comparison against NaN (not-a-number) evaluates to False, so rows with missing amount are silently excluded, never raised, which matches how NaN comparisons work generally.
• Missing parentheses: orders['amount'] > 100 & orders['status'] == 'complete' raises a TypeError at the &, because & binds to 100 and orders['status'] before the comparisons resolve; it fails loudly rather than returning a wrong-but-silent mask.
• Timezone-aware vs timezone-naive values mixed in created_at: comparing them raises a TypeError, so normalize timezone handling before filtering.
• Duplicate index labels in orders: boolean masking is positional in effect (aligned by index, but each row is independently True/False), so duplicates don't break the filter itself, though a later .loc lookup by label on the result could return more rows than expected.

Trade-offs and pitfalls

For very large frames, .query() can be more memory-efficient because it can avoid materializing every intermediate boolean mask (numexpr evaluates the whole expression in a more fused fashion), which matters if you are chaining many conditions. For readability with column names that are valid Python identifiers, .query() also tends to be easier to review at a glance than a long &-chained boolean expression. Prefer .loc[mask] over df[mask] when you also need to select specific columns in the same step, since df[mask][cols] = ... reintroduces exactly the chained-indexing risk that plain boolean filtering for reading avoids.