Spotify Data Scientist Interview Preparation Guide - Junior Level

A strong candidate writes this directly:

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

Structured elaboration

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

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

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

Worked example

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

Trade-offs and pitfalls

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

Direct answer

Use a sampling strategy that preserves the distributional properties you actually care about: simple random sampling is fine when the population is fairly homogeneous, but stratified sampling (deliberately sampling proportionally from known subgroups) is much better when a rare-but-important subgroup would otherwise be barely represented in a plain random sample. Reservoir sampling is the right tool specifically when you're pulling a sample from a stream or a source you can't randomly index into directly.

What you give up with each

Simple random sampling is easy to reason about and unbiased in expectation, but its estimates for any rare subgroup can be noisy, since a rare group that's only 1% of the population might contribute just a handful of rows to a modest-sized sample. Stratified sampling fixes that by guaranteeing a minimum, proportional (or deliberately oversampled) representation of each subgroup, at the cost of needing to know the subgroups in advance and sample separately within each one. Reservoir sampling solves a different problem entirely (sampling from a stream of unknown or unbounded length with a single pass) rather than a variance problem, so it's the right tool when the CONSTRAINT is "I can't hold the whole population in memory," not when the constraint is "I need a rare subgroup well represented."

Worked example

A population is 90% one homogeneous, low-variance group and 10% a smaller group with much higher variance and a very different mean. Simple random sampling of 200 rows produces mean estimates that, across repeated draws, vary with a standard deviation of about 1.34 around the true population mean; a stratified sample of the same total size, drawing 90% of the sample from the large group and 10% from the small group to match their true population shares, shrinks that variability to a standard deviation of about 0.36, nearly a fourfold reduction, precisely because the high-variance minority group is now reliably represented in every draw rather than showing up inconsistently.

Trade-offs and pitfalls

Stratified sampling requires knowing the strata in advance, which isn't always available; when it isn't, a sketch-based summary (approximate quantiles or distinct counts computed directly against the full population) can sometimes get you a representative picture without sampling at all, at the cost of losing the ability to inspect individual rows.

Nested cross-validation is a protocol for unbiasedly estimating a model’s generalization performance when hyperparameters are tuned. It prevents information leakage from test data into the hyperparameter selection step, which otherwise yields optimistically biased performance estimates.

Roles of loops

• Outer loop: Provides an unbiased estimate of generalization. Split data into K outer folds; for each outer fold, hold it out as a test set that never participates in tuning or training.
• Inner loop: Performs hyperparameter selection and model training. For each outer training partition, run an inner cross-validation (e.g., k'-fold) to evaluate different hyperparameter settings (grid/random/Bayes). Choose the best hyperparameters based only on inner-CV results, then retrain on the entire outer training set and evaluate on the outer test fold.

Typical workflow

1. For each outer fold: split into training and test.
2. On outer training, run inner CV to select hyperparameters.
3. Retrain final model with chosen hyperparameters on full outer training.
4. Evaluate on outer test; record performance.
5. Aggregate outer test metrics (mean ± std) as the nested-CV estimate.

Trade-offs

• Bias/variance: Nested CV yields nearly unbiased performance estimates for tuned models (reduces optimistic bias). It can have higher variance if outer K is small; using more outer folds reduces variance but costs more compute.
• Computational cost: Multiplicative: K_outer × K_inner × (cost per model fit). Can be expensive with large datasets or heavy models. Worked example: with K_outer=5, K_inner=5, and 10 hyperparameter candidates tried in each inner fold, each outer fold does K_inner * 10 = 50 inner-loop fits plus 1 final refit on the winning hyperparameters = 51 fits, times 5 outer folds = 255 model fits total. A single plain k-fold pass with fixed, already-chosen hyperparameters trains only K=5 models (one per fold). That's 255/5 ≈ 51x more compute for nested CV's unbiased generalization estimate versus a single k-fold pass, which is the concrete trade-off behind the abstract formula above.
• Practical mitigations: use fewer inner folds (e.g., 3–5), randomized search or Bayesian optimization, parallelize, or use a single holdout for very large data when compute is constrained.

Recommendation: Use nested CV for model selection reporting and when you need reliable performance estimates; for final deployment, retrain on full data with chosen hyperparameters.

The medallion architecture organizes a lakehouse or warehouse into three progressive layers: bronze holds raw data close to how it arrived, silver holds cleaned and conformed data, and gold holds business-ready, aggregated data. Each promotion step adds quality and structure, and the layering makes it possible to trace a bad number in a report back to the exact stage where it went wrong.

What lives in each layer

Bronze (raw). Data as it arrived from the source, with minimal changes: maybe a timestamp added, but no real cleaning, deduplication, or business logic applied. It's kept close to raw specifically so you can always reprocess it later if downstream logic changes. A data scientist would come here to inspect what the source actually looks like, or to debug why a downstream number seems wrong.

Silver (cleaned and conformed). Deduplicated, validated, typed data with obvious errors filtered out or flagged, and different sources joined into a consistent, entity-centric shape (one clean 'customer' table instead of three inconsistent ones). This is usually where a data scientist does most exploratory work and feature engineering, because it's reliable enough to trust but not yet flattened into business-specific aggregates.

Gold (curated, business-ready). Aggregated, denormalized tables built for a specific business purpose: a metric, a dashboard, a feature table for a production model. This is what business intelligence (BI) tools and dashboards query directly, and what a data scientist would use to validate that a model's output lines up with the business's own numbers.

Worked example: what happens at each promotion

Going from bronze to silver typically involves the validations that catch structural problems: rejecting or quarantining rows with impossible values, deduplicating records that arrived twice, and resolving type mismatches. Going from silver to gold applies business logic: computing the aggregates and joins a specific report or model actually needs, which is where a subtle bug (a wrong join key, double-counting a category) would surface as a wrong number in a dashboard.

Because each layer's boundary is explicit, when a gold-layer number looks wrong, you can check whether the problem is in the gold aggregation logic, in the silver cleaning logic, or in the bronze data itself, rather than having to untangle one monolithic transformation.

Trade-offs and pitfalls

The most common mistake is treating the layer names as a rigid rulebook instead of a communication convention: not every pipeline genuinely needs three distinct physical layers, and forcing a small dataset through all three stages for the sake of following the pattern adds pipeline complexity without adding real quality. The second pitfall is skipping the boundary discipline the pattern is meant to provide: if the same job does raw ingestion, cleaning, and business aggregation all at once with no clear layer boundary, you lose the exact debugging benefit (isolating which stage broke) that's the whole point of adopting the pattern in the first place.

Approach: Binary search over the SMALLER array's partition point (not over the values themselves) to find a split of both arrays such that everything on the left of the combined split is <= everything on the right, and the split sizes are balanced. This gives O(log(min(n, n))) = O(log n) time, dramatically better than the obvious O(n) merge-and-index approach.

def find_median_sorted(a, b):
    if len(a) > len(b):
        a, b = b, a  # ensure a is the shorter array
    n, m = len(a), len(b)
    lo, hi = 0, n
    half = (n + m + 1) // 2
    while lo <= hi:
        i = (lo + hi) // 2       # partition point in a
        j = half - i             # partition point in b
        a_left = a[i - 1] if i > 0 else float('-inf')
        a_right = a[i] if i < n else float('inf')
        b_left = b[j - 1] if j > 0 else float('-inf')
        b_right = b[j] if j < m else float('inf')
        if a_left <= b_right and b_left <= a_right:
            if (n + m) % 2 == 1:
                return max(a_left, b_left)
            return (max(a_left, b_left) + min(a_right, b_right)) / 2
        elif a_left > b_right:
            hi = i - 1
        else:
            lo = i + 1
    raise ValueError("input arrays not sorted")

Key points: A valid partition splits the combined 2n elements into a left half and right half of (near-)equal size, such that every element in the left half is <= every element in the right half. Because both a and b are individually sorted, once you fix a partition index i in a, the corresponding partition index j in b is DETERMINED (j = half - i) - so binary search only needs to search over i, not both indices independently, which is what collapses the search to O(log n).

Complexity: O(log(min(n, m))) time (binary searching over the shorter array), O(1) extra space.

Edge cases: one array empty (partition trivially puts everything from the other array on the appropriate side); arrays of very different lengths (handled by always binary-searching the shorter one, keeping the search space small); combined odd vs even total length (changes whether the median is a single middle value or an average of two).

Worked example / execution verification

tests = [
    ([1, 3], [2], 2.0),
    ([1, 2], [3, 4], 2.5),
    ([0, 0], [0, 0], 0.0),
    ([], [1], 1.0),
    ([1, 3, 5, 7, 9], [2, 4, 6, 8, 10, 12], 6.0),
]
for a, b, expected in tests:
    got = find_median_sorted(a, b)
    print(a, b, "->", got, "expected", expected, "OK" if got == expected else "MISMATCH")

Executed: all five test cases pass, including a hand-checkable case ([1,3,5,7,9] merged with [2,4,6,8,10,12] gives the sorted sequence [1,2,3,4,5,6,7,8,9,10,12]... wait, that's 11 elements from lengths 5+6=11, an odd total, so the median is the 6th element; the combined sorted array is [1,2,3,4,5,6,7,8,9,10,12], whose middle (6th of 11) element is 6 - matching the function's returned 6.0).

Trade-offs & pitfalls

• The naive O(n) merge-and-index approach is simpler to implement correctly and is perfectly fine unless the O(log n) bound is specifically required (this is a classic "know the better bound exists and can derive it" interview question, not necessarily a "always use this in production" recommendation - the O(n) version is more readable and the constant-factor difference rarely matters at realistic array sizes).
• The partition-based binary search is notoriously easy to get subtly wrong (off-by-one on partition boundaries, forgetting the +/-infinity sentinels for out-of-range partitions) - the sentinel values for empty-partition edges are what make the boundary conditions uniform and are worth deriving explicitly rather than memorizing.
• This generalizes to finding the k-th smallest element across two sorted arrays (not just the median) with the same O(log(min(n,m))) technique, adjusting the target partition size from half to the desired k.

Direct answer
Before presenting, think through the questions a skeptical, informed listener would actually ask, prioritizing the ones that probe your weakest assumption or your most surprising claim, and prepare a short, direct answer for each rather than hoping you'll improvise well.

Structured elaboration

1. Look for your weakest link first. Every piece of work has at least one assumption, data limitation, or judgment call that's more debatable than the rest; that's almost always where a sharp question comes from.
2. Look for your most surprising or counterintuitive claim. Anything that contradicts what people expected invites a "how do you know that's really true?" question.
3. Prepare a one-to-two sentence answer, not a rehearsed speech. A concise, direct answer reads as confident; a long, defensive one reads as though you're worried about the question.
4. It's fine to prepare an honest "we don't know yet" answer for a genuine gap, rather than inventing a more impressive-sounding answer under pressure; a confident admission of a limitation is usually better received than an unconvincing dodge.
5. Practice saying the answers out loud, not just thinking through them mentally; the gap between a mentally-rehearsed answer and one you can actually say smoothly under pressure is often bigger than expected.

Worked example
Presenting a recommendation to shift budget from one marketing channel to another based on eight weeks of data: anticipated tough questions might be "how confident are you this isn't just seasonal?", "what happens if the trend reverses next month?", and "did you control for the pricing change that happened in week 5?" Prepared answers: "We checked against the same period last year and saw a similar pattern, though eight weeks is admittedly a short window;" "if it reverses, the downside is limited since we're proposing a 20% shift, not the full budget;" "we did exclude the two weeks around the pricing change specifically to avoid conflating the two effects."

Each answer is short, direct, and, where there's a genuine limitation (the short time window), honestly acknowledged rather than glossed over.

Trade-offs and pitfalls

• Over-preparing for every conceivable question can lead to over-rehearsed, stiff-sounding answers; focus on the two or three questions most likely to actually come up, not an exhaustive list.
• Being defensive about a genuinely fair question damages credibility more than the limitation itself would; a calm, honest acknowledgment of a real gap usually lands better than an unconvincing justification.
• If a question comes up that you genuinely didn't anticipate and don't know the answer to, saying so plainly and offering to follow up is stronger than guessing in the moment.

Direct answer: Mentoring a team building features for a credit-risk model where regulatory explainability and fairness are non-negotiable requires a process that treats interpretability and fairness evaluation as first-class requirements alongside predictive performance from the very start of feature creation, not a compliance review bolted on at the end.

Structured elaboration:
A workable process: (1) at feature-creation time, prefer features with a clear, explainable business rationale over an opaque derived signal, even at some cost to raw predictive lift, since a feature that can't be explained to a regulator or a rejected applicant is a liability regardless of its accuracy contribution; (2) at selection time, weigh interpretability explicitly alongside predictive performance (favoring feature selection over dimensionality reduction, as discussed elsewhere in this topic, specifically for this reason); (3) evaluate fairness across demographic groups as a standard, required step (not an optional afterthought) for every candidate feature, checking specifically for the proxy-attribute risk discussed elsewhere in this topic; (4) document every feature's business rationale and fairness evaluation as part of its required metadata, so an auditor doesn't need to reconstruct reasoning after the fact; (5) involve legal/compliance stakeholders EARLY, during feature design, not only at a final pre-launch review, since a feature discovered to be legally risky after significant investment is a far more expensive problem to fix than one flagged during initial design.

Worked example: A candidate feature capturing a nuanced behavioral pattern shows strong offline lift but is difficult to explain in plain terms and shows an association with a protected attribute once tested. Rather than either shipping it (accepting the fairness and explainability risk) or discarding it outright (losing potentially real signal), the process requires an explicit trade-off discussion involving legal/compliance BEFORE the feature reaches production, documented as part of its record, so the eventual decision (whichever way it goes) is a deliberate, defensible one rather than a decision made silently by whoever happened to be building the feature that week.

Trade-offs and pitfalls: A team new to this level of process rigor may initially experience it as slowing down feature development; the counter-argument worth making explicitly is that a fairness or explainability problem discovered AFTER a model ships in a regulated domain is dramatically more expensive (in cost, in regulatory risk, in trust) than the same problem caught during design, so the added process is properly understood as risk reduction, not pure overhead.

Direct answer

A technical trade-off in applied ML is a decision where improving one property of a system necessarily costs you some of another, forcing a deliberate choice rather than a free win. These show up at both the model level and the infrastructure level.

Structured elaboration

Three concrete examples, one purely model-level, one purely infrastructure-level, and one that spans both:

1. Model complexity versus interpretability. A more expressive model (a large ensemble or deep network) often fits the data better, but a simpler model (a linear model or shallow tree) is easier to explain to a regulator, a stakeholder, or a debugging engineer. The decision driver is usually whether anyone downstream NEEDS to understand individual predictions, not just their aggregate quality.
2. Centralized versus edge (on-device) inference. Running inference centrally gives you easier monitoring, easier updates, and access to more compute per request, but costs network latency and requires connectivity; running on-device gives instant response and works offline, but limits model size and makes updates slower to roll out. The decision driver is the latency and connectivity requirements of the actual use case.
3. Accuracy versus latency. A bigger, slower model may score higher offline but miss a hard serving deadline; a smaller, faster model fits the deadline but scores lower. The decision driver is whatever the product's actual latency budget is, since a model that's "more accurate" but too slow to serve delivers zero value in production.

Worked example

A voice-assistant wake-word detector is a clean case of the centralized-versus-edge trade-off: it must run on-device (no network round-trip for something that needs to respond in milliseconds), which caps the model at a size that fits on the device's chip, even though a much larger cloud-hosted model would classify the wake word more accurately in isolation. The decision driver here (a hard, non-negotiable latency and offline requirement) makes the trade-off resolve clearly in favor of the smaller edge model despite the accuracy cost.

Trade-offs and pitfalls

The mistake to avoid is treating any of these as a one-time decision made in the abstract; the right point on each trade-off depends entirely on the specific product's constraints, and a decision that was right for one use case (a search-ranking model that can afford 200ms) is often wrong for a superficially similar one (a real-time bidding model with a 10ms budget).

Direct answer

At minimum, a pipeline dashboard should show freshness (how old the newest data is), completeness or volume (whether the expected amount of data actually arrived), latency (how long the pipeline took, per stage and end to end), error or failure rate, and a couple of data-quality signals (like null rates or schema drift on key fields). Together these answer "is the data here, is it on time, and is it trustworthy" without anyone needing to read logs first; a job reporting "success" alone can't answer any of those three questions.

Structured elaboration

What decides what belongs here versus what belongs to a full alerting design. The dashboard's job is to let someone without deep pipeline knowledge tell, at a glance, whether the data currently on it is trustworthy. Deciding exactly which breach pages someone and which is quietly logged is a separate design question about thresholds and escalation; for an entry-level "what goes on the dashboard" answer, naming the six signals above and why each matters is the proportionate depth.

Worked example

Take a daily sales pipeline expected to finish by 02:00 UTC with a typical row count around 500,000.

• Freshness: data landed at 01:40, 20 minutes ahead of the deadline. Fine.
• Completeness: 494,200 rows landed against the 500,000 baseline, a shortfall of

which is within a normal day-to-day variance band, so it reads fine on the dashboard.

• Latency: the run took 55 minutes against a typical 40 to 50 minute range, mildly elevated, worth a glance but not alarming on its own.
• Error rate: 3 of 40 tasks needed a retry:

against a normal baseline around 2 percent, which is the one figure on this dashboard that stands out enough to warrant a closer look.

• Null rate on the revenue column: 0.02 percent, well within tolerance.

This is what "why each signal matters" looks like in practice: four of the five numbers are unremarkable, and the dashboard's job was to make the one that isn't (the error rate) visible at a glance, without anyone needing to dig through task logs first.

Trade-offs & pitfalls

• Overloading the dashboard with every metric the system can emit defeats its purpose; someone should be able to judge overall health in under a minute, not scroll through fifty panels.
• Treating job-succeeded status as sufficient is the main pitfall: a job can report success against empty or truncated input, and only the completeness and data-quality signals would catch that.
• A static, hardcoded threshold ages badly against seasonal patterns (weekday versus weekend volume, for example); comparing against a rolling historical baseline is more proportionate than picking one fixed number, without needing to design the full paging and escalation policy that decision requires elsewhere.
• Leading with infrastructure metrics (CPU or memory usage) instead of these data-centric signals answers a different question than the one most stakeholders actually have, which is whether their data is present and correct.

Direct answer

A $(1-\alpha)$ confidence interval and a two-sided hypothesis test at level $\alpha$ are two views of the same procedure: the CI is exactly the set of null values that the test would fail to reject. So a 95% CI for a mean difference that excludes zero is equivalent to a two-sided test of "difference = 0" rejecting at $\alpha = 0.05$. That controls the long-run Type I error rate at 5%, not the probability that this particular result is a false positive.

Structured elaboration

The formal duality

For a parameter $\theta$, a $(1-\alpha)$ CI is built by inverting the test: it contains every value $\theta_0$ for which the two-sided test of $H_0: \theta = \theta_0$ against $H_a: \theta \neq \theta_0$ fails to reject at level $\alpha$.

Equivalently: $\theta_0$ falls outside the CI if and only if the test rejects. This is why testing "difference = 0" and checking whether 0 is inside the 95% CI give identical accept/reject conclusions, for any $\theta_0$, not just zero.

What "excludes zero" tells you (and doesn't)

• What it tells you: under the sampling model and its assumptions (independence, correctly estimated standard error, and either normal errors or a large enough sample for the CLT to apply), if the true difference were actually zero, an interval this far from zero would arise no more than 5% of the time across repeated samples. That is what "Type I error controlled at 5%" means: a property of the long-run procedure, not a 5% chance that this specific null is true.
• What it does not tell you: it says nothing about effect size in practical terms, and it is silent on Type II error or power for other effect sizes. A narrow CI that barely excludes zero and a wide CI that excludes zero by a mile both "reject," but they carry very different information.

Reporting to stakeholders

Lead with the point estimate and interval together, translate the interval's low end into business terms (does it still matter if the true effect is at the boundary?), and only mention the p-value as a secondary confirmation, since it adds no information beyond "0 is outside the CI" for a two-sided test at matching $\alpha$.

Worked example

Suppose an A/B test on checkout conversion produces a mean difference of 0.014 (1.4 percentage points) with standard error 0.006, and you want a 95% CI.

This excludes 0, so the two-sided test at $\alpha = 0.05$ rejects. Confirming with the test statistic directly:

from scipy import stats
diff, se = 0.014, 0.006
z_crit = stats.norm.ppf(0.975)          # 1.959963984540054
lo, hi = diff - z_crit*se, diff + z_crit*se   # (0.0022402, 0.0257598)
z_stat = diff/se                         # 2.3333333333333335
p_value = 2*(1 - stats.norm.cdf(abs(z_stat)))  # 0.01963065725729063

Both routes agree: CI excludes 0, p < 0.05, reject $H_0$. To stakeholders: "Conversion improved by 1.4 percentage points (95% CI: 0.22 to 2.58 points), a statistically significant result at the 5% level."

Trade-offs & pitfalls

• The overlapping-CI fallacy. Two group-level CIs that overlap do not imply the difference between the groups is non-significant, and non-overlapping CIs are a stricter (overly conservative) condition than a significant difference test. The duality above is specifically about the CI of the difference versus the null value, not about comparing two separate CIs by eye.
• "Excludes zero" is not "matters." With enough sample size, trivially small differences produce CIs that exclude zero. Always report the interval's practical range, not just significance.
• Assumption sensitivity. The duality and the stated Type I error rate both depend on the SE being estimated correctly (proper variance formula, no unmodeled clustering/dependence). Get the SE wrong and both the CI and the p-value are wrong together.
• Multiple comparisons. If you are constructing many CIs or testing many hypotheses, the 5% per-interval guarantee does not extend to "5% chance any of them is wrong" - that requires a correction (e.g., Bonferroni, Benjamini-Hochberg) on top of the per-test $\alpha$.