DoorDash Data Scientist Interview Preparation Guide - Junior Level

Direct answer

OLTP systems are optimized for many small, concurrent transactions on current data, so their schemas are normalized to keep writes cheap and consistent; OLAP systems are optimized for large, read-only aggregations over historical data, so their schemas are denormalized (often star-schema-shaped) to keep scans and joins cheap.

Structured elaboration

Worked example

For an e-commerce orders domain: the OLTP schema is customers, orders, order_items, payments, all normalized with foreign keys, so that placing an order is a small, fast transaction with ACID guarantees (atomicity, consistency, isolation, durability) touching only the rows involved and never risking an update anomaly on shared data. The OLAP schema built from the same domain, feeding a revenue dashboard, is typically a denormalized fact table like fact_order_line(date_key, customer_key, product_key, quantity, revenue) joined to small dimension tables, so that "total revenue by product category last quarter" is one scan and aggregation over a purpose-built table rather than a multi-way join across the live transactional tables (which would also risk slowing down the transactional workload itself).

Trade-offs and pitfalls

• Running analytical queries directly against the OLTP schema is the most common mistake this comparison is testing for: heavy scans compete for the same locks and buffer-pool space as live transactional writes, so most teams replicate or ETL the OLTP data into a separate OLAP store (a warehouse) rather than analyzing production directly.
• ETL/ELT is the bridge between the two: batch or change-data-capture (CDC) pipelines periodically transform the normalized OLTP shape into the denormalized OLAP shape, meaning the OLAP side is always somewhat stale relative to the OLTP source, by however long that pipeline's latency is.
• The typical enterprise reporting source of truth is the OLAP side, precisely because the OLTP schema is optimized for correctness-under-concurrency, not for the kind of ad hoc, multi-dimensional slicing that reporting needs.

Direct answer: Frame the story around the specific evidence that changed your mind, not just the outcome: what the feature was doing when you called it, what data made you confident enough to recommend rolling it back, and what pushback (if any) you navigated to make the call stick.

Structured elaboration

• Situation: set up what the feature was and what it was supposed to do, briefly, so the listener understands the stakes of the decision.
• Task: state what you were responsible for (owning the metric, or being the person who had to make the recommendation) and what made the call non-obvious (early positive signal, organizational excitement, sunk cost).
• Action: describe the specific analysis or signal that convinced you the feature was net-negative (a guardrail metric breaking, a longer-horizon metric turning negative once enough time had passed, a segment where the harm concentrated), and how you communicated the recommendation, including handling any disagreement.
• Result: state what happened after the rollback (the guardrail recovering, or a quantified cost avoided) and, ideally, what changed in how the team measures or launches features afterward as a result of this experience.

Worked example: A candidate describes owning analytics for a checkout redesign that showed a clean, statistically real lift in conversion in its first two weeks. By week four, the candidate noticed refund requests within 14 days had climbed from a 3% baseline to 5.5% among users exposed to the new flow, driven by a confusing step that made users complete purchases they had not fully intended to finish. The candidate brought the refund data to the team, quantified the net revenue effect once refunds were included (turning a nominal conversion win into a net loss once refunds were netted out), and recommended a rollback despite pushback from stakeholders who were excited about the conversion number. The team rolled back within a week, refund rate returned to baseline, and the team adopted a standing rule to track a 14-day refund guardrail on every checkout experiment going forward.

Trade-offs and pitfalls: A weak version of this story stops at "the metric went down so I said roll it back," without showing the specific evidence and reasoning that made the call defensible under pushback; the strongest version shows a metric that looked good getting reinterpreted once a longer window or a guardrail surfaced the real cost. Avoid overstating your personal authority in the decision if you were one voice among several; credit the process (the guardrail existing, the team's willingness to act on it) rather than framing it as a unilateral heroic call.

Unnest every top-level key across all rows in one pass, and aggregate per key: its inferred type, how often it's present, and a small sample of actual values, so you understand the real shape of inconsistent JSON before designing a stricter schema for it.

Approach

```sql
WITH keys AS (
SELECT json_keys(payload) AS ks, payload FROM raw_events
),
unnested AS (
SELECT UNNEST(ks) AS key_name, payload FROM keys
)
SELECT key_name,
COUNT(*) AS non_null_occurrences,
MODE(json_type(json_extract(payload, '$.' || key_name))) AS inferred_type,
ARRAY_AGG(json_extract(payload, '$.' || key_name))[1:3] AS sample_values
FROM unnested
GROUP BY key_name
ORDER BY non_null_occurrences DESC;
```
`json_keys` lists every top-level key present in a given row's payload, so unnesting across all rows and grouping by key name gives you, in one pass, exactly which keys exist across the whole dataset and how consistently.

Worked example

Across 1,000 rows, a key like `user_id` might appear in all 1,000 (always typed as an integer), while a key like `promo_code` appears in only 40 rows and shows a MIXED inferred type (sometimes a string, sometimes null-typed because it's absent), immediately telling you `promo_code` is optional and needs type-coercion handling wherever it's consumed downstream.

Trade-offs and pitfalls

`MODE()` on the inferred type picks the MOST COMMON type per key, which can mask real type inconsistency within that key (a numeric field that's occasionally sent as a string); if type consistency itself is what you're validating, report the DISTINCT set of observed types per key, not just the mode, so a key with genuinely mixed types is visible rather than silently summarized away. This schema-inference query is a useful first pass before designing a stricter, declared validation (checking specific required keys exist and specific values cast correctly), which is a separate, narrower query once you know what "correct" should look like for this payload.

Definition: Bias is error from incorrect assumptions underfitting the true pattern; variance is error from sensitivity to training data overfitting.
Concrete examples:

• High-bias model: linear regression on nonlinear data (e.g., quadratic relationship). Remedy: increase model capacity — add polynomial features or switch to a nonlinear model (tree-based or neural net). In production, implement feature engineering or deploy a higher-capacity model and validate with cross-validation and holdout tests.
• High-variance model: deep neural network with small dataset causing overfitting. Remedy: regularization — L2/dropout, data augmentation, early stopping, or collect more labeled data. In production, add regularization hyperparameters, implement robust validation (k-fold), and use monitoring for generalization drift.
  Practical pipeline steps:
• For bias: add features, choose richer model family, monitor train vs validation error gap to confirm reduced bias.
• For variance: apply regularization, early stopping, ensembling, and expand dataset; track validation stability and reduce model complexity until variance is controlled.

Direct answer. INTERSECT returns only the rows that appear in BOTH result sets. EXCEPT (MINUS in Oracle) returns the rows that appear in the FIRST result set but not the second. Both compare whole rows, need the same number of columns with compatible types on each side, and both dedup their output.

Structured elaboration. Think of them as set arithmetic over two same-shaped result sets rather than as joins: there's no join key to specify, the entire row IS the comparison unit. That makes them a natural fit for comparing two snapshots or two populations directly, without writing an explicit join condition.

Worked example. customers_2023(customer_id): 1, 2, 3. customers_2024(customer_id): 2, 3, 4.

-- customers present in BOTH years (returning customers)
SELECT customer_id FROM customers_2023
INTERSECT
SELECT customer_id FROM customers_2024;
-- returns 2, 3

-- customers present in 2023 but not 2024 (lost customers)
SELECT customer_id FROM customers_2023
EXCEPT
SELECT customer_id FROM customers_2024;
-- returns 1

Trade-offs and pitfalls. Both operations dedup by default (like UNION, not UNION ALL), so if you need to know HOW MANY TIMES a row appeared rather than just whether it appeared, INTERSECT/EXCEPT are the wrong tool and you need an explicit join or GROUP BY instead. NULLs in the compared columns are handled specially: unlike an ordinary equality comparison, most engines treat two NULLs in the same position as equal for the purposes of this row comparison, which is the opposite of how NULL behaves in a join predicate, and is a common source of confusion when you've internalized "NULL never equals NULL" from working with joins.

Direct answer

Ownership should follow causal control, not who asked for the metric. A number that only one team's actions actually move belongs to that team as a leading indicator. A number that several teams jointly move needs to be treated as a shared outcome with exactly one canonical definition that everyone points to, not each team computing its own version of 'the same' number.

Structured elaboration

1. Decide ownership by who controls the number

Ask: if this metric moved tomorrow, whose decisions would most plausibly explain it? If the answer is one team, it's team-owned. If the honest answer is 'several teams, depending on the week,' it's a shared outcome metric and needs shared governance, not a single team's dashboard.

2. Give every shared metric one canonical definition

Store the computation (the query or transformation logic) in one place, documented with an owner, a last-updated date, and the exact filters and date logic used. Any dashboard or report showing that metric should read from that canonical source, not recompute it independently.

3. When two teams report different numbers, reconcile, don't debate

The canonical definition is the tiebreaker by default. If a mismatch appears, the fix is a reconciliation step: compare the two calculations side by side, find where the logic diverges (a different date window, a different filter, a stale cache), and correct the deviating one, or update the canonical definition itself if it turns out to be wrong. Either way, log the decision so the same disagreement doesn't restart from zero next quarter.

4. Put governance around who can change a shared definition

A shared metric's definition should not change because one team unilaterally decides a different cohort or window looks better. Route changes through a lightweight review involving everyone who reports on that metric, and version the definition so historical numbers can be explained if they shift after a redefinition.

Worked example

A dashboard spans data engineering, product, and marketing for a signup-to-paid-conversion initiative. Splitting ownership this way keeps the dashboard honest:

When marketing's report shows a different sign-up count than the shared dashboard, the reconciliation step finds that marketing's number excluded a promo-code cohort by mistake. The canonical query is correct; marketing's ad hoc report is fixed to match it, and the discrepancy is logged so the next person who notices a mismatch can find the resolution instead of reopening the debate.

Trade-offs and pitfalls

• Centralizing every metric, including team-level leading indicators, slows down the teams that need to iterate quickly on their own signals; only the genuinely shared outcomes need the heavier canonical-definition process.
• Fully decentralizing shared outcome metrics guarantees mismatched dashboards eventually, which quietly erodes trust in the data even when the underlying numbers are directionally fine.
• A 'single source of truth' only works if using an alternate calculation is treated as a defect to fix, not a valid difference of opinion; without that enforcement, teams drift back to their own numbers within a quarter.
• Late-arriving corrections that change historical values need an explicit policy (do dashboards restate history, or only apply corrections going forward) decided in advance, or every correction becomes its own dispute.

Direct answer. Short of full per-tenant partitioning, you have per-tenant or filtered statistics (where the engine supports collecting statistics scoped to a subset of the data), query-time hints or per-tenant query variants that account for known skew, and, more structurally, isolating the most extreme outlier tenants onto their own physical partition while leaving the rest on shared, generically-planned infrastructure; validate whichever you choose by checking plan quality for BOTH the affected outlier tenants and the unaffected majority, not just the one you noticed complaining.

Structured elaboration. Generic, table-wide statistics describe an "average" distribution across every tenant, which works fine for typical tenants but can badly misdescribe an unusually large or unusually skewed tenant's actual data shape, leading the optimizer to a plan tuned for the average case rather than that specific tenant's reality. Filtered or extended statistics, where available, can capture a more nuanced picture (for instance, statistics conditioned on a tenant-identifying column) without requiring full physical separation. Where that's not available or not granular enough, a smaller, more surgical structural change, moving only the handful of genuinely extreme tenants onto dedicated partitions or even dedicated infrastructure, while leaving the long tail of typical tenants on the shared, generically-planned setup, often captures most of the benefit of full per-tenant partitioning at a fraction of the operational cost.

Testing the chosen approach means checking plan quality and query latency for BOTH ends of the distribution: the outlier tenants you're specifically trying to help, AND a sample of typical tenants, to confirm you haven't traded one group's problem for the other group's regression, since a fix tuned too specifically for the outliers' shape can sometimes make plans worse for the previously-fine majority.

Worked example. A multi-tenant analytics platform where 95% of tenants have a few thousand rows each but a handful of enterprise tenants have tens of millions, generic table-wide statistics will describe something close to "typical tenant size," badly misleading the optimizer for both ends: plans tuned for the (statistically dominant, small) typical tenant can be catastrophic for the enterprise tenants, and, less obviously, statistics skewed by including the enterprise tenants' data can occasionally produce suboptimal plans for the typical tenants too.

Trade-offs and pitfalls. Full physical per-tenant partitioning is the most complete fix but also the most operationally expensive, more partitions to manage, more complexity in every cross-tenant query; reach for it only once you've confirmed lighter-weight options (filtered statistics, isolating just the extreme outliers) genuinely aren't sufficient, rather than defaulting to the heaviest solution first.

Situation: The model's error rate has jumped for a specific user segment (e.g., users in Region X). I would use SHAP to find which features' contributions changed for that segment versus baseline and identify the likely root cause.

Steps:

1. Compute instance-level SHAP

• Use the same explainer used in production (TreeExplainer/DeepExplainer/KernelExplainer) and compute SHAP values for: (a) all instances in the failing segment, (b) a baseline set (historical population or recent non-failing users).

2. Aggregate across the segment

• Compute per-feature metrics: mean(SHAP), mean(|SHAP|), median, and the sign proportion (fraction positive vs negative). Capture the distribution (violin/histogram) to see heterogeneity.
• Optionally compute interaction SHAP for top features to detect feature-pair effects.

3. Compare to baseline

• For each feature compute delta = metric_segment minus metric_baseline. Rank features by change in mean(|SHAP|) and change in signed mean to see which features increased in influence or flipped direction.
• Run statistical tests (bootstrap CIs, permutation test, or a t-test if assumptions hold) to verify significance and compute effect sizes.

4. Distinguishing the four possible root causes explicitly

• Real population change: the segment's INPUT feature distributions genuinely shifted (a new user cohort, a seasonal effect, a new device type). Confirm by comparing feature distributions for the segment across time using the raw, non-model data, independent of SHAP. If the SHAP shift tracks a distribution shift that's externally explainable (a marketing campaign, a product launch, a new region), this is the likely cause, and the fix is usually retraining or reweighting rather than a bug hunt.
• Preprocessing bug: the FEATURE VALUES going into the model changed because of a code or pipeline defect (a join breaking, a null-fill changing, a unit change), not because the real world changed. Confirm by re-running the exact preprocessing code on raw logs captured before and after the suspected change and diffing the outputs field by field; a feature that silently changed shape (a categorical suddenly encoded differently, a numeric field's scale shifted by a constant factor) with no real-world reason is a bug, not a population shift.
• Newly introduced leakage: a feature that was NOT available at the true prediction time is now present, or a previously-masked feature briefly saw future or label-correlated information, causing the model to appear to lean on it more heavily. Confirm by explicitly checking the feature's computation window against the label's timestamp for the affected segment (does it use any event at or after the point the label became known?), and by checking whether the SHAP-flagged feature correlates suspiciously strongly with the label -- near-perfect separation is a leakage tell, not a real signal.
• Training artifact: nothing changed in production data at all; the apparent shift is an artifact of HOW SHAP was computed (a different background/reference dataset between the two runs, a different model version, or a small-sample-size segment producing high-variance attributions). Confirm by recomputing SHAP for both time periods with identical explainer settings, background dataset, and model version, and by bootstrapping the segment's SHAP estimates to see if the 'shift' is within noise.
  Only after ruling out the preprocessing-bug, leakage, and training-artifact explanations (all fixable infrastructure issues, not real signal) should the SHAP shift be treated as reflecting a genuine change in the world that the model needs to be retrained or reweighted for.

Caveats when interpreting SHAP changes:

• SHAP is descriptive, not causal: a change in SHAP points to association with model output, not that the feature caused the errors.
• Background/baseline choice matters: SHAP values depend on the background dataset; compare using a consistent baseline or test sensitivity to different baselines.
• Correlated features & interactions: SHAP can apportion importance across correlated inputs but may produce unstable attribution; use interaction SHAP and conditional analyses.
• Sample size and variance: small segments yield high variance; use bootstrap CIs.
• Model changes and explainer consistency: if the model version changed, recompute SHAP with the same explainer settings.
• Computation & approximation: kernel approximations may be noisy; prefer exact explainers for trees or validated approximations.

This process links SHAP-driven signals to a specific, testable root cause among the four candidates above, enabling prioritized, testable remediation (relabeling, retraining, feature fixes, or targeted monitoring) instead of treating every SHAP shift as automatically meaningful.

Direct answer

For tree ensembles specifically, class weighting is usually the right first move: it's simple, requires no change to the training data, and both random forests and gradient boosting frameworks support it natively (class_weight, scale_pos_weight). Balanced subsampling and resampling change what data each tree actually sees and can help further, but at the cost of discarding majority-class information or duplicating minority-class examples; focal loss is a more surgical tool for boosting frameworks when the imbalance is compounded by many "easy" majority examples drowning out gradient signal from hard ones.

Structured elaboration

Why class weighting is the default starting point for tree ensembles. It leaves the training data itself untouched, so there's no risk of the duplication-driven overfitting that oversampling introduces, or the information loss from undersampling. Both bagged and boosted tree frameworks support it as a first-class option, so it's a one-parameter change rather than a new pipeline step. The main risk is that extreme weight ratios can make optimization noisy: a rare class weighted very heavily can cause a boosting model to overfit the handful of minority examples it does see, since each of their gradient contributions is now large.

When to reach further. If class weighting alone still leaves the model insensitive to the minority class (common under severe imbalance, e.g. beyond roughly 100:1), balanced subsampling or targeted resampling can help by directly changing what fraction of each tree's or boosting round's training signal comes from each class, rather than just reweighting a fixed dataset. Focal loss is worth reaching for specifically when the problem isn't just "too few minority examples" but "too many easy majority examples burying the gradient signal from the hard cases," which plain class weighting doesn't address, since it reweights by class label alone, not by how hard an individual example currently is to classify.

Worked example

Take a dataset with 10,000 negative examples and 500 positive examples (a 20:1 imbalance ratio). For gradient boosting's scale_pos_weight, the standard recommendation is the ratio of negative to positive counts:

For scikit-learn's class_weight='balanced' scheme, each class's weight is $w_c = \frac{n_{\text{total}}}{n_{\text{classes}} \times n_c}$. With $n_{\text{total}} = 10{,}500$, $n_{\text{classes}}=2$:

Note the two schemes give different numbers (20 vs. 10.5 for the positive class) because they normalize differently, scale_pos_weight is a raw ratio applied only to the positive class in a binary boosting objective, while class_weight='balanced' normalizes both classes' weights to average to 1 across the dataset; mixing up which convention a given library expects is a common, silent source of over- or under-weighting in practice.

Trade-offs & pitfalls

• Class weighting changes what the model optimizes for but not what data it sees; if the minority class has very few examples in absolute terms (say, under a few dozen), no amount of reweighting compensates for the model simply never having encountered enough variety in that class to learn robust patterns from it.
• Oversampling (including SMOTE) evaluated with plain k-fold CV is a common leakage trap: if resampling happens before the train/validation split, synthetic or duplicated minority points can leak into the validation fold, inflating the reported score; resampling must happen only inside each fold's training portion.
• Whatever imbalance-handling method is used during training, predicted probabilities are no longer calibrated to the true class balance (they now reflect the reweighted/resampled training distribution), so a separate threshold-tuning or recalibration step (e.g., Platt scaling or isotonic regression against a held-out set with the true class balance) is usually needed before deploying probability outputs to a business rule.
• Prefer the simplest method that closes the gap: class weighting first, escalate to subsampling or resampling only if weighting alone underperforms on the metric that matters (typically AU-PR or F-beta for imbalanced problems, not raw accuracy), and reserve focal loss for cases where the hard-example-mining behavior specifically, not just class rebalancing, is the diagnosed problem.

Direct answer: Frequency encoding replaces each category with how often it occurs (as a raw count or a normalized proportion), which is cheap, fixed-size regardless of cardinality, and needs no training-time-vs-serving-time complexity, but the same simplicity is exactly what causes its production pitfalls at high cardinality or when the training and serving populations differ.

Structured elaboration:
The implementation: group by the categorical column, compute each category's count (or count divided by total rows, for a normalized frequency), and map each row to its category's computed value. This is a simple, deterministic, table-lookup-style transform once fit on training data.

Worked example: Verified: applying frequency encoding to a small categorical column (['a','a','b','c','a','b']) correctly assigns 'a' a frequency of 0.5 (occurring 3 of 6 times), 'b' a frequency of 0.333 (2 of 6), and 'c' a frequency of 0.167 (1 of 6), confirming the encoding correctly reflects each category's relative prevalence in the fitted data.

Trade-offs and pitfalls: At high cardinality, frequency encoding suffers the same COLLAPSING problem discussed for other encodings in this topic: two entirely distinct categories that happen to occur with the same frequency become indistinguishable to the model, purely by coincidence of prevalence, not any real similarity between them. More importantly for production: if the training-time and serving-time populations differ (a category that was rare in training suddenly becomes common in a later period, or vice versa), the frequency encoding computed once at training time becomes STALE, silently misrepresenting a category's current true prevalence; this is a genuine, easy-to-miss instance of training-serving skew specific to this encoding, and argues for periodically refreshing the fitted frequencies (with a defined, deliberate cadence) rather than treating them as a one-time, forever-fixed lookup.