Spotify Data Scientist Interview Preparation Guide - Mid Level (2-5 Years)

Direct answer

Don't declare the old number wrong and switch overnight. Explain the change in terms partners can verify for themselves, run both definitions side by side for a defined period so people can reconcile the gap at their own pace, and give a concrete accounting of why the numbers differ before asking anyone to adopt the new one as their working reality.

Structured elaboration

1. Find out what's actually anchored to the old number. It's rarely the number itself that people resist, it's the targets, dashboards, or comp plans built on top of it. Identify those dependencies before you talk about the redefinition in the abstract.
2. Show a concrete case where the old definition misled someone. An abstract "this is more accurate" argument doesn't land. A specific example where the old calculation gave a wrong or misleading answer does.
3. Run dual reporting, don't hard-cutover. Publish both the old and new metric side by side for a fixed window so partners can watch the two track each other (or diverge) and build intuition for the new number before they have to rely on it alone.
4. Break the gap into named components. Instead of "the number moved," account for the difference: how much of the change comes from the new inclusion/exclusion criteria, how much from a data-quality fix, how much from a genuine behavior shift. A gap people can decompose feels explainable; an unexplained gap feels arbitrary.
5. Set an explicit cutover date and update every downstream artifact by name, dashboards, target-setting docs, comp formulas, rather than assuming people will notice and adjust on their own.
6. Keep the old metric available, read-only, for a grace period after cutover instead of deleting it immediately, so people can still check their own prior conclusions against it while they adjust.

Worked example

Suppose "active users" currently counts anyone who logs in during the month. The new definition additionally requires at least one core in-product action during that session, because the team found that a meaningful share of logins were automated health-checks or bounced sessions that didn't reflect real engagement. If the old metric counted 10,000 monthly logins, and historically about 30% of logins involve no core action (a figure pulled from existing session logs, not asserted), the new definition would show roughly 10,000 x (1 - 0.30) = 7,000 active users, a drop of 3,000 driven entirely by the new inclusion criterion, not by an actual usage decline. Dual reporting both numbers for a month, with that 3,000-user gap explicitly labeled "removed for lacking a core action, not a real drop," lets a marketing partner whose Q3 target was set against the old 10,000-count number understand exactly why their dashboard changed before they have to defend it to their own leadership.

Trade-offs & pitfalls

• Pitfall: cutting over immediately without a dual-reporting window. It looks like the number was changed to hit or dodge a target, even when it wasn't.
• Pitfall: mandating adoption from authority ("this is the new source of truth, use it") without walking anyone through the why. Technically correct, but it burns trust and invites people to quietly keep using their own old tracking.
• Pitfall: deleting the old metric immediately, which strands anyone mid-adjustment and turns a change-management problem into an access problem.
• Senior differentiator: treating a metric redefinition as a change-management effort you own end to end (explanation, parallel run, decomposition, migration of dependents), not just a technical correction you announce and move on from.

Direct answer
Open the file using your language's scoped resource-management construct (Python's with, Java's try-with-resources), read the lines, and let that construct guarantee the file handle closes when the block exits, whether it exits normally or because an exception was raised partway through, then handle a missing file and a permissions error as distinct, expected outcomes rather than letting either crash the caller.

Structured elaboration

• Why a scoped construct instead of manual open/close: if you close the file with an ordinary line of code after the read, an exception raised during the read skips that line entirely and the handle leaks (file descriptors are a finite OS resource; leaking enough of them eventually breaks the whole process, not just this call). with/try-with-resources are sugar over exactly the try/finally pattern from the try/except/finally discussion: the close happens in the equivalent of a finally block, so it runs on every exit path, success, expected error, or unexpected error.
• Handling a missing file: catch the specific exception the platform raises for this (Python's FileNotFoundError, Java's NoSuchFileException/FileNotFoundException), and decide deliberately what the caller should see, an empty result, a re-raised application-specific error, or propagation, rather than letting a generic exception surface with no context about which file or why.
• Handling a permissions error: similarly catch PermissionError (Python) / the platform equivalent specifically, this is a genuinely different failure mode from 'file doesn't exist' (the caller might want to alert an operator rather than silently treat it as an empty result) and conflating the two loses information a caller might need to act correctly.
• Why relying on garbage collection to eventually close the handle is not good enough: even in a garbage-collected language, GC timing is not guaranteed or immediate, an unclosed handle can sit open for an unpredictable amount of time (or effectively forever, if something keeps a reference alive), during which it holds an OS resource and, for a file opened for writing, may leave buffered data unflushed. The scoped construct closes deterministically at a known point in the code, GC-triggered cleanup does not.

Worked example

def read_lines(path):
    try:
        with open(path, 'r') as f:
            return f.readlines()
    except FileNotFoundError:
        print(f"file not found: {path}, returning empty list")
        return []
    except PermissionError:
        print(f"permission denied: {path}, returning empty list")
        return []

Verified by execution: reading an existing file with lines "line1\n", "line2\n", "line3\n" returns exactly ['line1\n', 'line2\n', 'line3\n']; calling it on a path that doesn't exist returns [] without raising. A separate check confirmed the resource-closing guarantee specifically: wrapping a file object so that reading it raises mid-operation, and confirming the wrapper's close() still ran (wrapper.closed was True) even though the read itself failed, exactly the guarantee with/try-with-resources provides. The equivalent in Java is try (BufferedReader r = new BufferedReader(new FileReader(path))) { ... }, the resource declared in the try (...) parentheses is closed automatically when the block exits, by any path.

Trade-offs & pitfalls
The choice to return an empty list versus re-raising an application-specific exception on a missing/unreadable file is a real design decision, not a default: returning empty silently is convenient for the caller but can hide a real problem (a misconfigured path, a permissions regression) behind what looks like 'the file was just empty'. Whichever you choose, do it deliberately and log enough context (the path, the specific exception) that a missing file and a permissions problem are distinguishable in your logs even if the function's return type can't distinguish them for the caller.

Direct answer

Maintain three running numbers per stream, a count n, the running mean, and M2 (the running sum of squared deviations from the mean-so-far), updated with Welford's one-pass recurrence; this is what keeps the variance numerically stable even after an arbitrarily long run, unlike accumulating sum(x) and sum(x*x) separately. Two such accumulators, one built independently on each machine, combine losslessly with Chan et al.'s parallel-merge formula: combine the counts, take the count-weighted mean, and add a correction term to M2 that accounts for how far apart the two machines' means were.

Structured elaboration

Why not just track sum and sum-of-squares

The textbook variance formula $\mathrm{Var}(X) = E[X^2] - (E[X])^2$ looks like a natural one-pass accumulator: keep sum_x and sum_x2, divide at the end. It is numerically unstable whenever the values share a large common offset relative to their spread (subtracting two large, nearly equal numbers loses precision, a catastrophic-cancellation problem), and the loss compounds as the stream grows. Welford's algorithm sidesteps this entirely by never squaring raw values; it only ever tracks deviations from a mean that is itself updated incrementally.

The update rule (Welford's algorithm)

For each new value $x$, with running count $n$, mean $\bar{x}$, and M2:

The sample variance is $M2 / (n - 1)$ (population variance is $M2 / n$).

Merging two accumulators (Chan, Golub, LeVeque)

Given accumulator $a$ (from one machine) and $b$ (from another), with counts $n_a, n_b$, means $\bar{x}_a, \bar{x}_b$, and $M2_a, M2_b$:

The $\delta^2 n_a n_b / n$ term is the "between-group" variance contribution: it accounts for the two machines' local means disagreeing, which the naive M2_a + M2_b alone would miss.

The exponential-moving-average variant, as a simpler special case, and where it stops being the same idea

A fixed-weight exponential moving average, $\bar{x}_t \leftarrow \bar{x}_{t-1} + \alpha (x_t - \bar{x}_{t-1})$, is the same one-pass, constant-memory update shape as Welford's mean term, specialized to a fixed decay rate $\alpha$ instead of the shrinking weight $1/n$. It is the right choice when you want to weight recent values more than old ones (e.g. tracking a metric that drifts over time) rather than a true all-time average. It does not, however, inherit the clean two-way merge above: each machine's exponential moving average encodes an implicit, ongoing recency-weighting of its own history, and there is no single count you can use to combine two such weighted means correctly, unlike Welford's exact, count-weighted merge. Combining two exponential-moving-average accumulators correctly generally requires tracking (or approximating) an effective sample size per side or aligning them by timestamped decay, a materially different problem from the exact merge above.

Worked example

class OnlineStats:
    def __init__(self):
        self.n = 0
        self.mean = 0.0
        self.M2 = 0.0

    def add(self, x):
        x = float(x)
        self.n += 1
        delta = x - self.mean
        self.mean += delta / self.n
        delta2 = x - self.mean
        self.M2 += delta * delta2

    def variance(self, ddof=1):
        if self.n <= ddof:
            return float('nan')
        return self.M2 / (self.n - ddof)

    @staticmethod
    def merge(a, b):
        if a.n == 0:
            return b
        if b.n == 0:
            return a
        out = OnlineStats()
        out.n = a.n + b.n
        delta = b.mean - a.mean
        out.mean = a.mean + delta * b.n / out.n
        out.M2 = a.M2 + b.M2 + delta * delta * a.n * b.n / out.n
        return out


import random
random.seed(7)
data = [random.gauss(10, 3) for _ in range(2000)]

whole = OnlineStats()
for x in data:
    whole.add(x)

mid = 837
left = OnlineStats()
for x in data[:mid]:
    left.add(x)
right = OnlineStats()
for x in data[mid:]:
    right.add(x)
merged = OnlineStats.merge(left, right)

print("one-pass mean:", whole.mean, "one-pass variance:", whole.variance())
print("merged mean:  ", merged.mean, "merged variance:  ", merged.variance())

This prints:

one-pass mean: 10.050413560803356 one-pass variance: 9.234143479024658
merged mean:   10.050413560803364 merged variance:   9.234143479024652

The two rows agree to within floating-point rounding (differences on the order of $10^{-15}$), confirming the merge formula reconstructs the same statistics as processing all 2000 samples in one pass.

Trade-offs & pitfalls

Complexity

add: $O(1)$ time, $O(1)$ space per call. merge: $O(1)$ time and space regardless of how many samples either side has already seen, this is the whole point of carrying only three numbers instead of the raw data.

Edge cases

• n=0: a fresh OnlineStats() has n=0; variance() returns nan since n <= ddof
  (0 <= 1); merge(a, b) treats an n=0 accumulator as the identity element
  (if a.n == 0: return b), so merging with an empty accumulator is a safe no-op that returns
  the other side unchanged.
• n=1: after one add(), n=1, mean=x, M2=0; variance() under the default
  ddof=1 still returns nan (1 <= 1), correctly reflecting that sample variance is
  undefined for a single point; population variance (ddof=0) would return 0.
• Single-element merge: merging an n=1 accumulator into another one needs no special
  case beyond the n=0 guards above; the standard Chan formula folds the single point into
  the aggregate correctly via the delta * delta * a.n * b.n / out.n cross term.
• Sample variance ($n-1$ denominator) is undefined for $n \le 1$; decide up front which convention (sample vs population) the accumulator reports and guard the edge case.
• Welford's method is far better conditioned than naive sum/sum-of-squares, but it is not infinitely immune to floating-point drift over an astronomically long run; if that matters, periodic re-basing (subtracting off a running offset) or higher-precision accumulation are options, at additional cost.
• A tempting shortcut, re-summing the whole stored history whenever precision looks suspect, defeats the entire "too large to store in full" constraint by silently reintroducing $O(n)$ memory or $O(n)$ per-update time.
• Reaching for the exponential-moving-average variant when the task actually needs the true all-time mean and variance (or an exact cross-machine merge) trades away exactness for recency-weighting you did not ask for.

Direct answer

Random measurement error adds noise in both directions with no consistent pattern, while systematic measurement error shifts values consistently in one direction, like a sensor that slowly drifts upward or a tracking pixel whose definition changed mid-series. Detect a systematic shift during EDA by looking for a change in a metric's baseline or trend that lines up with a specific known event (a firmware update, a tool change) rather than looking like ordinary noise, and estimate its magnitude by comparing behavior clearly before and after that event.

Detecting drift versus noise

Random noise looks the same in every time window: roughly the same spread, centered around the same baseline, no systematic direction. A systematic shift shows up as a level change or a slow trend that persists rather than averaging out, and it often lines up suspiciously well with a specific date you can independently verify (a deployment log, a hardware-replacement record). The key diagnostic is comparing the metric's behavior in a window clearly before a suspected change against a window clearly after it, rather than looking at the whole series at once, which can average the shift away and make it look like just a bit more noise than usual.

Worked example

A fleet of IoT sensors shows a temperature-reading baseline that drifts upward gradually over several months. Checking against the firmware-update log for the fleet shows deployments rolling out gradually across that same window, and comparing sensors right before and right after their individual update dates shows each one jumps by a consistent amount immediately at its own update, not gradually before it, confirming the update as the cause rather than a coincidental environmental trend. Estimating the magnitude: the average post-update reading is consistently about 1.2 degrees higher than the same sensor's average pre-update reading, which is the correction factor worth investigating (and potentially applying to historical data, with appropriate caution).

Trade-offs and pitfalls

Correcting historical data for a detected systematic shift is sometimes appropriate and sometimes not: if you're not confident the correction factor is the same for every affected unit, or if the "correction" itself introduces new uncertainty that isn't clearly documented, it may be safer to flag the affected period as unreliable rather than silently rewrite it.

Direct answer

A metric governance framework needs four pieces working together: a versioned metric registry as the single source of truth, a naming convention, an explicit ownership model, and an approval workflow sized to the severity of the change. Without all four, "active user" (or any widely used metric) will drift into several silently inconsistent definitions, which is exactly the failure case to design against.

Structured elaboration

Metric registry and versioning

A central registry (a metadata layer over the warehouse, or a dedicated catalog tool) stores, per metric: a canonical name, its definition (the actual query logic), owners, lineage, a semantic version, and a change log. Every approved change increments the version: a major bump for a semantic change (what the metric actually measures), a minor bump for a non-semantic change (aggregation window, formatting). Old versions stay queryable so historical reports remain reproducible.

Naming convention

A pattern such as domain.entity.metric.aggregation (for example, checkout.order_conversion.rate.30d) with the unit and window recorded in metadata, plus reserved prefixes for experimental and deprecated metrics, so nobody has to guess what a name means from its string alone.

Ownership model

• Metric owner (product): accountable for the business meaning.
• Data owner (analytics or data engineering): implements and maintains the underlying logic and tests.
• Steward (central governance): enforces the process and runs periodic audits.

Approval workflow, sized to severity

graph TD
  A[Propose a definition change] --> B[Automated checks: syntax, lineage, tests]
  B --> C{Change severity}
  C -->|Minor: window or aggregation tweak| D[Data owner review, fast turnaround]
  C -->|Major: semantic or logic change| E[Metric owner plus data owner plus downstream stakeholders, dual-reporting period]
  D --> F[Merge, version bump, changelog]
  E --> F
  F --> G[Notify subscribers, deprecate superseded dashboards]

Worked example

Suppose "active user" is discovered to have five inconsistent definitions across dashboards, and one of them has been used in executive reporting for six months.

1. Contain immediately: mark the metric "disputed" in the registry, flag every dashboard deriving from it, and stop using any variant in external or board-facing reporting until it is re-certified.
2. Inventory the five variants: pull the actual query logic behind each dashboard and classify the differences, for example one counts app-open only, one requires a core action, one includes anonymous sessions, one uses a 7-day window mislabeled as monthly, and one double-counts users across devices.
3. Determine the gap on the executive-reporting variant: recompute the affected historical periods under the corrected definition and produce a restated series; do not silently patch history going forward only. Disclose the correction, its cause, and its direction to the stakeholders who relied on the original numbers.
4. Find the process root cause: identify why the wrong definition went unnoticed, for example because no registry entry existed for this metric before the incident, or a local edit was made and never propagated. Fix that gap, not just the number.
5. Remediate: push one canonical definition through the major-change approval path above (cross-functional sign-off, dual-reporting period), migrate all five dashboards to reference the single registry entry, and deprecate the other four implementations.
6. Prevent recurrence: require every new dashboard metric to reference the registry by ID rather than re-deriving logic locally, and add an automated lineage check that flags any metric-shaped column not traced back to a registered definition.

Trade-offs & pitfalls

• A registry with heavy approval overhead invites shadow reporting: teams route around it with ad hoc dashboards under time pressure. Keep the minor-change path genuinely fast, or the fast path becomes the workaround.
• Restating months of executive-reported history is reputationally costly either way: quietly fixing it going forward erodes trust further once the discrepancy is discovered independently, while disclosing it costs an uncomfortable conversation now but preserves credibility afterward.
• Ownership without enforcement (an assigned metric owner who isn't actually notified of downstream consumers) doesn't prevent drift; lineage tooling that shows every consumer of a definition is what makes ownership actionable.
• Versioning without a hard deprecation date lets old definitions linger indefinitely; pair every major version bump with a sunset date for the dashboards still on the prior version.

Direct answer

An alerting strategy that balances sensitivity against fatigue needs severity tiers so not everything pages the same way, aggregation windows so a single noisy blip doesn't fire, and thresholds derived from your own historical data rather than guessed round numbers.

Structured elaboration

1. Severity tiers: informational (logged, visible on a dashboard, no page) for small drifts within normal variance; warning (Slack/ticket, next-business-day triage) for a moderate, sustained signal; critical (immediate page) reserved for signals strongly correlated with real business impact historically.
2. Aggregation windows: require a signal to persist across multiple consecutive evaluation windows (not just one) before escalating: this alone eliminates most single-window statistical noise without losing real, sustained drift.
3. Threshold selection from historical data, not guessing: pull a year of historical PSI (or whichever metric) values for a feature during periods you KNOW were healthy, and set your warning/critical thresholds using something like a control chart (mean plus 2 or 3 standard deviations of that healthy period) rather than an arbitrary round number like "0.25 for everyone": different features have genuinely different natural variance, and a single global threshold either under-alerts on a naturally-stable feature or over-alerts on a naturally-noisy one.
4. Suppression during known events: a holiday, a planned data-pipeline migration, or a marketing campaign predictably shifts distributions; suppressing or down-weighting alerts during a pre-registered known-event window avoids training your on-call team to ignore alerts that fire during Black Friday every single year.

Worked example

Three concrete example rules: (1) latency: page if p99 exceeds 500ms for more than 5 cumulative minutes in a 15-minute window; (2) prediction-distribution shift on a key feature: warn if that feature's PSI exceeds its own control-chart threshold for 3 consecutive daily evaluations, escalate to critical if it also coincides with a measured accuracy drop; (3) accuracy: page if rolling 24-hour accuracy drops more than 2 standard deviations below its 90-day trailing mean. Separately: flag inputs that look adversarial rather than merely drifted (a sudden burst of feature values sitting exactly at a decision boundary, or an unusual spike in a single user/session generating an implausible volume of requests) as its OWN alert category, since the response (rate-limit, block, investigate for abuse) is different from the response to ordinary drift (investigate, consider retraining).

Trade-offs & pitfalls

The single biggest driver of alert fatigue in practice isn't any one bad threshold, it's failing to distinguish "worth investigating" from "worth waking someone up for": routing every signal through the SAME severity path guarantees the team eventually tunes out the channel entirely. The fix is disciplined tiering plus a regular retrospective on alert precision (of the pages that fired this month, how many led to a real action?) so thresholds get tuned based on evidence, not left static forever.

Direct answer. Propose a functional (expression) index built on the exact same expression the query sorts or filters by, so the index itself already stores values in the transformed order the query needs; the limitation is that only a query using that identical expression, written the same way, can actually benefit from it.

Structured elaboration. A standard index on a bare column stores values in that column's own natural order, which doesn't help a query that sorts or filters by some FUNCTION of that column, since the function's output order generally isn't the same as the column's own order. A functional index instead indexes the OUTPUT of the expression directly, so the stored order matches exactly what the query needs, letting the planner avoid both an unindexed scan and a separate explicit sort step.

Worked example. I verified the underlying case-insensitive-sort scenario with three customer emails in mixed case ('Bob@Example.com', 'alice@example.com', 'Carl@Example.com'), sorting by lower(email):

CREATE INDEX idx_customers_email_lower ON customers (lower(email));

SELECT id FROM customers ORDER BY lower(email) LIMIT 3;

Running the equivalent query against that data correctly returns the rows in case-insensitive alphabetical order (Alice, then Bob, then Carl), confirming the logic is right; with the functional index in place, a real database can serve that ORDER BY directly from the index's own stored order, avoiding both a plain-column index (which would sort by the RAW, case-sensitive value, giving the wrong order) and an unindexed sequential-scan-plus-sort.

Trade-offs and pitfalls. The index only helps a query that uses the EXACT same expression, written the exact same way; lower(email) and LOWER(email) are typically fine (case-insensitive function names), but lower(trim(email)) would NOT match an index built on just lower(email), and would silently fall back to not using it at all rather than raising any warning. This makes functional indexes best suited to an expression that's used consistently, ideally enforced through a single shared query-building layer or view, rather than one that different parts of a codebase might express in slightly different but logically-equivalent ways.

Complexity

With the functional index in place, this ORDER BY becomes a direct index walk (no separate sort operator), same asymptotic improvement as any other case where an index removes an otherwise-necessary sort.

Edge cases

NULL handling in the underlying column needs to be considered explicitly: depending on the engine's default NULL-ordering behavior, rows where the base column is NULL may sort first or last under the functional index, which is worth confirming matches the query's actual requirement rather than assuming.

Direct answer: Compute this in two separate stages because a "7-session moving average" and a window over raw events are aggregating at two different grains that cannot be collapsed into one window function call. Stage 1 collapses each session down to a single number (its average event value) with a plain GROUP BY. Stage 2 treats those per-session averages as the new rows and applies a ROWS BETWEEN 6 PRECEDING AND CURRENT ROW window, ordered by session start, partitioned by user, to average the last 7 of them. A single window function over the raw events cannot do this because its frame boundary (ROWS, RANGE, or GROUPS, the third and least common frame unit, which counts by peer-group: rows tied on the same ORDER BY value count as one step instead of one row each) always counts something about the current row's grain, rows, values, or peer-groups, and none of those map onto "the last 7 sessions" when the current grain is individual events, not sessions.

Structured elaboration

WITH session_avg AS (
  -- Stage 1: collapse events to one row per session
  SELECT
    s.session_id,
    s.user_id,
    s.session_start,
    AVG(e.event_value) AS avg_event_value_per_session
  FROM events e
  JOIN sessions s ON e.session_id = s.session_id
  GROUP BY s.session_id, s.user_id, s.session_start
)
SELECT
  user_id, session_id, session_start, avg_event_value_per_session,
  -- Stage 2: window over the session-level rows, not the event-level rows
  ROUND(AVG(avg_event_value_per_session) OVER (
    PARTITION BY user_id
    ORDER BY session_start, session_id
    ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
  ), 4) AS moving_avg_last_7_sessions
FROM session_avg
ORDER BY user_id, session_start, session_id;

Why two stages, concretely: ROWS BETWEEN 6 PRECEDING AND CURRENT ROW counts physical rows in whatever the current FROM clause produces. If that FROM clause is events JOIN sessions, one row per event, then "6 preceding rows" means 6 preceding events, which has nothing to do with 7 sessions unless every session happens to contain exactly one event. A session with 50 events and a session with 2 events contribute wildly different numbers of rows to that frame, so a naive single-stage window silently computes a moving average weighted by how chatty each session was, not an equal-weighted average of 7 session averages, a different, and wrong, metric for the stated question. Collapsing to one row per session in Stage 1 is what makes "7 rows" and "7 sessions" the same thing by the time Stage 2's window runs.

ORDER BY session_start, session_id in both the GROUP BY and the window's ORDER BY matters for the same reason ties matter in any window function: if two sessions for the same user share an identical session_start timestamp, session_id is the deterministic tiebreaker that keeps the window's row order (and therefore which 7 sessions are "the last 7") reproducible across re-runs.

Worked example

Executed against a small sessions(session_id, user_id, session_start) and events(event_id, session_id, event_value) pair: user 1 has 3 sessions. Session 1 (2 events: 10, 20) averages 15. Session 2 (2 events: 30, 50) averages 40. Session 3 (2 events: 5, 15) averages 10. The two-stage query correctly produced a moving average of 15 after session 1 (only one session so far), 27.5 after session 2 ((15+40)/2), and 21.6667 after session 3 ((15+40+10)/3), each is the plain average of all sessions seen so far since fewer than 7 sessions exist yet.

Running a naive single-stage window directly over the joined events/sessions rows, AVG(event_value) OVER (PARTITION BY user_id ORDER BY session_start, session_id ROWS BETWEEN 6 PRECEDING AND CURRENT ROW), on the exact same underlying data produced a different sequence: 10, then 15, then 20, then 27.5, then 23, then 21.6667, one number per event, six numbers total instead of three. None of those six numbers is the "moving average of session averages" the question asked for; the window is silently averaging raw event values in event order, giving sessions with more events more influence over the result purely because they contributed more rows to the frame. This is the concrete, executed proof that the single-stage approach computes a genuinely different, incorrect metric, not just a less elegant version of the right one.

Key points

• A window function's frame counts rows (or a value-range, or peer-groups) at whatever grain the query's FROM clause currently produces; changing the intended grain requires changing what rows exist first, which is what the Stage 1 GROUP BY does.
• The bug in the single-stage attempt is not a syntax error, the query runs and returns plausible-looking numbers, which is what makes it a dangerous trap rather than an obvious one.
• ROUND is cosmetic; the moving average itself works identically without it, useful to strip out when double-checking exact values against hand-computed expectations.

Complexity

Stage 1 is a straightforward aggregation: a scan of events joined to sessions, grouped by session, O(n) for n events plus the cost of the join. Stage 2 sorts the (much smaller) session-level rows by user_id, session_start, O(m log m) for m sessions, then computes the moving average in a single pass with an incrementally maintained running sum over a fixed 7-row frame, O(m). Because m (sessions) is typically orders of magnitude smaller than n (events), Stage 2 is cheap relative to Stage 1 regardless of table size.

Edge cases

• A user with fewer than 7 sessions total (as in the worked example): the window simply averages however many sessions exist so far, ROWS BETWEEN 6 PRECEDING AND CURRENT ROW never demands exactly 7 rows to exist, it caps at 7 but is content with fewer.
• A session with zero events: the JOIN in Stage 1 drops it entirely (an inner join), so it never contributes a row to session_avg; switching to a LEFT JOIN and deciding whether a sessionless average should be NULL or 0 is a deliberate choice, not a default to leave implicit.
• Two sessions sharing an identical session_start: resolved deterministically by the session_id tiebreaker in ORDER BY, without it which session counts as "more recent" in the moving window is undefined and can vary between runs.

Trade-offs & pitfalls

The single-stage version is the trap most candidates walk into under interview pressure because it looks like it should work, PARTITION BY user_id ORDER BY session_start ROWS BETWEEN 6 PRECEDING AND CURRENT ROW reads exactly like "the last 7 things," and the query runs without error. The fix is not a more clever window clause, it is recognizing that the question is really "aggregate, then aggregate the aggregate," which SQL expresses as two window/group stages chained through a CTE (common table expression, a named, reusable subquery introduced with WITH), not one window clause with a cleverer frame. If the requirement changes from "last 7 sessions" to "last 7 calendar days of sessions," Stage 2's frame changes from ROWS BETWEEN 6 PRECEDING to a RANGE BETWEEN INTERVAL '7' DAY PRECEDING on session_start, the two-stage structure itself does not need to change.

Plan: collect profiles, identify roots, mitigate with code/data changes.

Collecting memory profiles:

• Use tracemalloc (a built-in module that records which line of code allocated each block of memory, and lets you snapshot total/peak Python-level allocations at any point) for Python allocations
• Use memory_profiler (@profile) (a decorator-driven tool that reports memory usage line by line for a decorated function, run via python -m memory_profiler script.py) to trace per-line memory
• Use psutil / top for process-level peaks (psutil: a library that reads OS-level process stats, including RSS, resident set size, the actual physical RAM a process is currently using, directly from the operating system)
• For NumPy, use objgraph (a tool for visualizing and counting live Python object references, useful for finding what is unexpectedly still holding a reference to something large) / np.ndarray.nbytes sampling
• Take periodic heap dumps and track peak RSS

Which to reach for first: tracemalloc is the default starting point for almost any Python-level memory investigation, cheap to turn on, built in, and line-level. psutil/RSS is the next check when the growth seems to be happening outside pure Python objects entirely (a C extension, or a numpy allocation that tracemalloc's Python-object view does not fully capture). memory_profiler and objgraph are for harder follow-up cases once the first two have narrowed down roughly where the growth is, not where most investigations should start.

A worked trace with tracemalloc, verified on CPython 3.12 with numpy: creating a float64 array and then converting it with .astype(np.float32) (a common, easy-to-miss source of an avoidable extra allocation) shows up as two distinct, measurable jumps:

import tracemalloc
import numpy as np

tracemalloc.start()
baseline, _ = tracemalloc.get_traced_memory()

a = np.zeros(1_000_000, dtype=np.float64)
after_a, _ = tracemalloc.get_traced_memory()

b = a.astype(np.float32)   # a full, avoidable copy if a float32 array was all that was ever needed
after_b, _ = tracemalloc.get_traced_memory()

print("a allocation bytes:", after_a - baseline)
print("b (astype copy) allocation bytes:", after_b - after_a)

Output:

a allocation bytes: 8000496
b (astype copy) allocation bytes: 4000096

The dominant numbers, 8,000,000 and 4,000,000, are exactly 1_000_000 * 8 bytes (float64) and 1_000_000 * 4 bytes (float32); the small remainder (496 and 96 bytes) is bookkeeping overhead from numpy's own array object and can vary slightly by numpy version, the part worth remembering is the dtype-size arithmetic, not the exact overhead constant. If a was only ever needed as float32, allocating it directly as np.zeros(1_000_000, dtype=np.float32) avoids the second, redundant 4,000,000-byte allocation entirely, this is exactly the kind of concrete before/after number a real profiling pass surfaces that a purely descriptive plan does not.

Common causes:

• Creating many temporaries (slicing / copying)
• Unnecessary copies from dtype promotion (dtype promotion: when an operation combines two arrays of different dtypes, e.g. float32 and float64, numpy silently produces a result in the wider of the two dtypes, float64, which can quietly double the memory of a computation that was only ever intended to stay in float32)
• Holding references to large arrays (accumulators, list append)
• Parallel workers duplicating memory

Mitigations:

• Use in-place operations (out=...) and views (a view: a new array object that points at the same underlying memory as another array, e.g. a[::2], versus a copy, which allocates and fills an entirely separate block of memory) where safe
• Use appropriate dtypes (float32 vs float64)
• Process data in chunks / streaming
• Reuse preallocated arrays and buffer pools
• Avoid building large Python lists; use arrays or write to disk-backed arrays (memmap)
• Release references and call gc.collect when necessary for deterministic release

Validation: write microbenchmarks for peak RSS (RSS: resident set size, the actual physical memory a process currently occupies, as reported by the OS), compare before/after; run under production-like data sizes.

Direct answer

Kimball, Inmon, and Data Vault are three different bets about where risk lives in a warehouse build. Kimball (bottom-up: build conformed dimensions and business-process-scoped fact tables directly, tied together by a "bus" of shared dimensions) optimizes for fast delivery of business intelligence (BI)-ready data. Inmon (top-down: model the whole enterprise in a normalized model first, then derive dependent data marts from it) optimizes for enterprise-wide consistency. Data Vault (hub, link, and satellite tables, insert-only) optimizes for auditability and the ability to onboard volatile source systems without redesigning the model every time a source changes.

Structured elaboration

Kimball: bus architecture. You start from a business process (orders, shipments, support tickets) and build a star schema for it directly: a fact table at a declared grain, surrounded by dimensions. Cross-process consistency comes from conformed dimensions: the same dim_customer and dim_date are reused, unchanged, across every mart, so a customer or a date means the same thing whether you are looking at the sales mart or the support mart. The risk this reduces is slow time-to-value: business users get a usable, understandable star schema for one process quickly, without waiting for a full enterprise model.

Inmon: top-down, corporate information factory. You start by modeling the enterprise's core entities and their relationships in a normalized (typically 3NF) model, independent of any single reporting need. Departmental data marts are then built as dependent, derived views on top of that single source of truth. The risk this reduces is inconsistency at enterprise scale: because every mart derives from the same normalized core, you cannot end up with two departments quietly defining "customer" or "active order" differently. The cost is a longer time to the first delivered report, because the enterprise model has to exist before a mart can be derived from it.

Data Vault: hub, link, satellite. A hub stores just a business key (hashed) and where it came from. A link stores a relationship between hubs (an order-to-customer relationship, say). A satellite stores the descriptive, time-variant attributes for a hub or link, and it is insert-only: a change never overwrites a row, it appends a new one with a later load timestamp. This buys you two things a dimensional model does not give you as directly: a full, source-system-faithful audit trail (nothing is ever lost or rewritten, which matters for regulators), and structural resilience to source-system churn (a new source system for the same business entity just adds new satellites and links, it does not force you to redesign an existing star schema). The cost is that a Data Vault is not BI-friendly by itself: you still build a business vault or a dimensional layer on top of it before an analyst can use it comfortably.

When to use each. If the dominant risk is "the business needs a dashboard this quarter and cannot wait for an enterprise model," lean Kimball. If the dominant risk is "five departments will each build their own definition of the same entity if we let them," lean Inmon. If the dominant risk is "our source systems change constantly and auditors need to see exactly what we received and when," lean Data Vault. In practice the most common real-world answer is a hybrid: use Data Vault as the raw, auditable integration layer that absorbs source-system churn, then build Kimball-style conformed dimensional marts on top of it for BI consumption. That combination gets you Data Vault's auditability and onboarding agility underneath, and Kimball's BI-readiness on top, at the cost of an extra transformation layer between raw and consumption.

Worked example

A payments company onboards a new regional processor every few months, each with a slightly different schema for the same underlying "transaction" concept, and its regulator requires it to reproduce exactly what was received from each processor at any past date. A pure Kimball build would mean re-touching the transaction fact table's extract-transform-load (ETL) pipeline every time a new processor's schema differs even slightly. A pure Inmon build would require the enterprise model to anticipate every future processor's fields before onboarding even one of them, which is not realistic for a fast-growing integration surface. The Data Vault answer: model hub_transaction and hub_processor, link them, and give each processor's raw fields their own satellite. Onboarding processor six adds a new satellite, not a schema migration on the existing fact table, and the insert-only history satisfies the regulator's reproducibility requirement directly. A conformed fact_transaction star schema is then built on top of the vault for the finance team's dashboards, so analysts never see hub/link/satellite tables directly.

Trade-offs and pitfalls

A common mistake is treating this as a purely technical choice; it is at least as much an organizational one. Inmon's enterprise model requires enterprise-wide agreement on entity definitions before anyone gets value, which stalls in organizations without the authority to enforce that agreement. Kimball's bus architecture requires genuine discipline about NOT letting a "just this once" un-conformed dimension leak into a mart, or you end up with the exact inconsistency Inmon was designed to prevent. Data Vault's insert-only satellites grow fast: without a defined retention or archiving policy, the raw vault becomes large purely from history that few queries ever touch, so a business vault or a materialized "current state" view over the vault is not optional in practice, it is what makes the vault usable at all.