Spotify Data Analyst (Staff Level) - Comprehensive Interview Preparation Guide

Direct answer

Designing a streaming pipeline to compute a rolling metric with meaningful late arrivals means combining event-time windowing with an explicitly chosen watermark and allowed-lateness strategy, an exactly-once (or safely idempotent) write path to the serving store, and a defined policy for how a late correction gets reflected once it's already been served.

Structured elaboration

The pipeline shape is: ingest from a durable, replayable source (a commit-log platform); window the aggregation by event time (tumbling for a clean per-period number, sliding if the metric needs to update more often than its own window length); choose a watermark and allowed-lateness setting sized against your real observed lateness distribution, not guessed; write results via an idempotent upsert keyed by the window's identity so a late-triggered recomputation of an already-served window overwrites cleanly rather than double-counting; and decide, deliberately, whether a correction after the fact re-fires and overwrites the previously served value (accepting that consumers see a number change) or is captured separately for reconciliation without disturbing what was already shown.

Worked example

Computing daily active users where up to 10% of events can arrive as much as 24 hours late: ingest from Kafka, window by calendar day (event time), set the watermark's allowed lateness wide enough to cover the bulk of that 24-hour tail (accepting that the very last, rarest late arrivals beyond that are handled by a separate side-output-and-reconciliation path rather than blocking the main pipeline indefinitely), and write the daily count to the serving store as an idempotent upsert keyed by (date), so a late-triggered recompute of yesterday's count safely overwrites the earlier, less-complete number rather than creating a duplicate entry. At 1 million events per minute, the state backend and checkpoint strategy need to be sized to hold roughly a day's worth of in-flight, not-yet-closed windows, which is the concrete capacity-planning number this design has to account for.

Trade-offs and pitfalls

A wider allowed-lateness setting sized to catch "most" late data delays every window's first result and grows in-flight state accordingly; the residual small fraction of even-later data that misses even a generous allowed-lateness window still needs an explicit, deliberate policy (silently drop it, or reconcile it later), not an accidental gap nobody decided on. The most common mistake in this exact design pattern is building the happy-path windowing and forgetting to design the idempotent-write and late-correction behavior with the same rigor, leaving a pipeline that looks correct in a demo but silently double-counts or drops data the first time a real late-arrival burst occurs in production.

Direct answer

Build the case on the specific, evidenced risk, not on general discomfort with the status quo, and bring a plan for the transition, not just an argument for stopping. When leadership pushes back with a business argument you genuinely cannot fully counter, for example real revenue currently tied to the thing you want to sunset, don't try to win the argument outright: shrink the ask to something they can say yes to, a bounded pilot, a partial rollback, or an explicit, documented risk acceptance with a named owner and a revisit date, so the risk becomes a managed, visible decision instead of a debate you lose and the risk disappearing from view.

Structured elaboration

• Ground the case in specific evidence, not a general sense that something old or risky should go. Usage data, cost trend, incident history, or a concrete compliance exposure. "This system is a decade old" is not a reason to sunset it; "it has caused three incidents this year and its underlying dependency reaches end-of-support in six months" is.
• Separate the technical/ethical risk from the business value it currently generates, and be honest that both are real. A feature that is generating revenue while carrying real risk is a genuine trade-off, not a case where one side is simply wrong; pretending otherwise makes your case less credible, not more.
• Bring a transition plan, not just a stop request. Leadership is far more willing to act on risk when the ask includes how affected users or revenue are protected during the transition: a migration path, a parallel run, or a phased reduction rather than an abrupt cutoff.
• When you hit a business argument you cannot fully counter, do not manufacture a counter-argument you don't actually believe. Instead:
  • Shrink the ask. Propose a bounded pilot or a partial mitigation (reduce blast radius, add a specific control) rather than full sunset, so leadership can say yes to something real without betting the whole revenue line.
  • Make the residual risk an explicit, named decision, not a default. Write down what risk is being accepted, why, and who is accepting it. This is not a threat, it's honesty: if the risk materializes, everyone remembers it was a deliberate, informed call, not something nobody flagged.
  • Set a concrete revisit trigger. A specific metric, incident, or date that reopens the decision, so "we'll look at it later" has an actual mechanism instead of quietly becoming never.
• Escalate on the evidence, not on your own certainty. If you still believe the risk outweighs the upside after making the strongest possible case, escalating with the documented evidence and the accepted-risk record is legitimate; escalating on "I really think I'm right" without new evidence usually just burns trust.

This same structure applies whether the thing at risk is a decade-old reporting suite with real dependents, a legacy model integrated into multiple downstream systems, a launch the CEO wants to keep on schedule that you believe needs delay to implement proper ML governance first, a launch a fairness analysis flagged for disparate impact despite executive pressure to proceed, an LLM feature with a known hallucination risk, a cross-functional analytics program an executive wants to cancel under budget pressure, a fraud-reduction change business leaders want reverted because it dented revenue, a product pivot that trades short-term revenue for long-term retention, pushing back on a business request because of a genuine technical risk, a newly discovered regulatory risk you need to present to legal, product, and executives together, a new regulation requiring data-lineage controls that collides with leadership's push for a fast end-of-year dashboard rollout, re-planning a long-running AI platform initiative after its funding is put at risk mid-flight, a regulated launch on a hard deadline carrying a small residual failure-mode risk that could trigger fines, and product leadership pushing for rapid launches despite reliability concerns you have already raised: name the specific evidence, separate the real value from the real risk, and bring a transition path rather than a bare stop request.

Worked example

A ten-year-old reporting suite was costing real money to run and had low measured active use, but it was still the system of record for a handful of teams, including one that used it to produce a report tied to a partner contract. Leadership's pushback was fair: "this system generates a report our biggest partner relies on, and you're asking us to touch it." I could not fully counter that; the report genuinely mattered and I had no way to guarantee a migration would be flawless.

Instead of pushing for a full sunset, I shrank the ask to a bounded pilot: migrate the three lowest-risk, lowest-usage reports first, run them in parallel with the legacy system for a defined period, and only propose migrating the partner-facing report once that pilot showed the pattern worked. I also wrote down explicitly what we were accepting by not touching the partner report yet: continued maintenance cost on the whole legacy platform until that piece migrated, with the CFO as the named owner of that trade-off since it was their budget line funding the extra maintenance cost. We set a revisit date six weeks after the pilot to decide on the partner report specifically, rather than leaving it indefinitely deferred.

The pilot succeeded on the three lower-risk reports, which gave leadership real evidence (not a projection) that the migration pattern worked, and that evidence, not a stronger argument, is what got the partner-facing report onto the roadmap the following quarter.

Trade-offs and pitfalls

• Treating every pushback as something to argue past rather than something to actually listen to means you sometimes push to sunset something that genuinely should stay; the business argument you can't counter is sometimes correct.
• A documented risk-acceptance record can read as covering yourself rather than as genuine transparency if you only produce one after losing an argument; make it a habit for any accepted risk, not just the ones where you disagreed.
• Shrinking the ask repeatedly without ever closing the loop on the deferred, higher-risk piece just delays the real decision indefinitely; the revisit trigger has to be a real date or metric, not a soft "later."
• Escalating too early, before you've built the evidence base, reads as going over someone's head rather than raising a legitimate concern; escalation works when it's backed by data leadership hasn't seen, not by repetition of the same argument louder.

Direct answer. Each physical operator represents a different strategy for reading or combining rows, and the optimizer's whole job is to pick a combination of these that minimizes total estimated cost for your specific query and data. Scans get rows out of storage; joins, sorts, and aggregates combine or reorganize the rows those scans produced.

Structured elaboration.

• A sequential scan reads every row of a table (or heap) in physical order. It's chosen when a large fraction of the table is needed, or when there's no useful index; its cost scales with table size regardless of how selective the filter is.
• An index scan walks an index structure to find matching rows, then fetches the full row from the table for each match. It's chosen when the predicate is selective enough that visiting the index plus a handful of table rows beats reading the whole table; the trade-off is that each match costs a separate (often random) I/O against the table.
• An index-only scan answers the query entirely from the index, with no visit to the table, when every column the query needs is present in the index and the storage engine's visibility bookkeeping allows it. This is the cheapest access method when it applies.
• Nested loop, hash, and merge join each combine two row sets differently: nested loop probes the inner side once per outer row (cheap for a small outer side with a cheap way to probe the inner side); hash join builds an in-memory hash table from one side and probes it with the other (good for large, unsorted inputs); merge join walks two already-sorted inputs in lockstep (good when both sides are cheaply available in sorted order).
• Sort materializes rows in a required order, which costs memory or disk depending on volume; aggregate collapses rows into groups, either via a hash table (unsorted input) or by exploiting already-sorted input.

Worked example. For SELECT customer_id, SUM(amount) FROM orders WHERE created_at >= '2025-01-01' GROUP BY customer_id, a reasonable plan is: an index scan on created_at to find recent rows cheaply (assuming that predicate is selective), feeding a hash aggregate that groups by customer_id using an in-memory hash table, since there's no reason to expect the rows already arrive sorted by customer.

Trade-offs and pitfalls. None of these operators is unconditionally "the good one" or "the bad one." A sequential scan is the CORRECT choice, not a mistake, when a query needs most of a table's rows, because the per-row overhead of index lookups would cost more in aggregate. The signal worth watching for in a plan is a mismatch between the operator and the actual selectivity or volume involved, for example a nested loop join running many more iterations than its own estimate expected.

Direct answer: With a 2% baseline conversion rate, 100k eligible users/day, 80% power, and alpha 0.05, and assuming a 15% relative minimum detectable effect (the question does not state one explicitly, so I am stating this assumption up front, a common convention for this kind of sizing exercise; new baseline = 2.30%), the design needs roughly 18,346 users PER CELL to power the main effects, or about 36,693 users per cell to ALSO power the interaction at the same MDE. Traffic should be split evenly, 25% into each of the 4 cells (control/control, pricing-B/layout-old, pricing-A/layout-new, pricing-B/layout-new), randomized independently on pricing and layout so every user has an equal chance of any of the 4 combinations.

Sample-size computation (executed, two-proportion z-test):

For a MAIN EFFECT comparison (pooling the 2 cells that share a pricing level against the other 2), $p_1=0.02$, $p_2=0.023$ (15% relative lift):

gives n = 36,693 per POOLED arm (i.e. per 2-cell group). Since each pooled arm is 2 of the 4 cells, that is 18,346 users per CELL for main-effect power, 73,386 total across all 4 cells, which at 100k/day takes about 0.73 days.

For the INTERACTION contrast (an unpooled, single-cell-vs-single-cell comparison, since the interaction estimate uses the +1/-1/-1/+1 contrast across all four individual cells rather than pooled halves), the SAME formula applied per cell (no pooling) requires 36,693 users per cell, 146,771 total, about 1.47 days at 100k/day, exactly 2.00x the main-effect total. This is the general result cited in S0: interaction detection at the same MDE costs about 2x a main effect's sample size.

Traffic allocation: randomize pricing (A vs B) and layout (old vs new) INDEPENDENTLY via two separate hash-based coin flips per user (verified orthogonal in S9), producing four roughly-equal 25% cells automatically; do not manually stratify into 4 named buckets, since independent-factor randomization already balances the design and keeps it robust to later adding a third factor.

Analysis and interpretation: fit

on the binary conversion outcome (linear probability model or logistic regression with an interaction term). $\beta_1$ is the pricing main effect averaged over both layouts, $\beta_2$ the layout main effect averaged over both pricing versions, and $\beta_3$ the interaction: how much the pricing effect CHANGES when layout changes (equivalently, how much the layout effect changes when pricing changes). Run the interaction test FIRST: if $\beta_3$ is significant, report cell-specific conversion rates (all 4 cells) rather than a single "average" pricing effect, since the average would be misleading if the effect flips sign across layouts. If the interaction is not significant, report the two main effects as if from independent tests and recommend shipping whichever combination of winning main effects performs best. Present results to stakeholders as: (1) the interaction test verdict first, in plain language, (2) a 2x2 table of observed conversion rates per cell with confidence intervals, (3) the recommended cell to ship, and (4) the guardrail-metric check on that winning cell (per this topic's sibling a-b-test-design-and-statistical-rigor, which owns guardrail-metric mechanics).

Direct answer
The underlying finding stays the same across audiences; what changes is which slice of it you lead with and how much supporting detail you include, based on what each audience is actually deciding. A CFO, a CTO, and a product VP reading the identical finding should each get a version that answers their specific decision, not three copies of the same generic summary.

Structured elaboration
A useful discipline is to name, before writing anything, what decision each audience is actually making and what they'd need to make it well:

• A CFO typically cares about cost, risk, and the number that ties to the P&L; lead with the dollar impact and keep technical mechanism to a minimum.
• A CTO cares about technical soundness and implementation risk; lead with the trade-off or the approach, and it's fine to include more mechanism.
• A product VP cares about roadmap and user impact; lead with what changes for the roadmap or the customer, not the underlying implementation.
  The underlying facts don't change between versions, and reusing the same evidence across all three keeps the story internally consistent if the different audiences ever compare notes; only the ordering, framing, and depth of supporting detail shift.

Worked example
A model change increases accuracy but doubles inference cost. To the CFO: "this change costs roughly $40K/month in additional compute but is projected to reduce fraud losses by $150K/month, net positive." To the CTO: "the accuracy gain comes from a heavier ensemble step; here's the latency and infra impact and the rollback plan if it underperforms in production." To the product VP: "customers should see meaningfully fewer false declines starting next release; no user-facing change required." Same underlying change, three entry points.

Trade-offs and pitfalls
The most common failure is writing one generic version and mentally hoping it works for everyone, which usually means it under-serves all three audiences at once. The second is letting the facts themselves drift between versions to make each one sound better to its specific audience, which is dishonest and eventually gets caught when the audiences compare notes.

Direct answer
Adapting mentoring to someone different from yourself means adjusting the mechanism (how directive vs. how hands-off you are, how direct the feedback is, how much structure you provide) while keeping the underlying goal the same, and it requires actively noticing when your default style is a poor fit rather than assuming your own preferences are universal.

Structured elaboration

Adapting by competence and confidence: situational leadership

A useful framework here is thinking in terms of directing, coaching, supporting, and delegating, mapped to how much competence and confidence the person currently has for the specific task at hand (not their seniority in general, since someone senior can still be low-confidence on something genuinely new to them):

• Directing: low competence, needs clear instruction on what to do.
• Coaching: some competence but still needs explanation and encouragement, not just instruction.
• Supporting: solid competence, mainly needs encouragement and a sounding board, not instruction.
• Delegating: high competence and confidence, needs autonomy more than involvement.

The same person can sit in different quadrants for different tasks at the same time, so this is applied per-skill, not as a single label for the whole relationship.

Adapting to feedback-culture differences

How directly to give feedback isn't purely a personal style preference; it's shaped by cultural norms the mentee brings, and treating it as pure style risks an equity failure, not just a communication mismatch. Someone from a background where direct, blunt feedback is the norm may find indirect feedback confusing or even read it as a lack of respect for their ability to handle it; someone from a background where direct public correction is genuinely unacceptable may experience the same blunt feedback as disrespectful or even shaming, regardless of intent. Noticing which context someone is bringing, and adjusting delivery accordingly while keeping the substance intact, is part of doing this well rather than an optional nicety.

Adapting by seniority of the mentee

A junior mentee usually needs more structure, more explicit scaffolding, and more frequent checkpoints. A senior mentee needs something different: less procedural guidance, more of a thinking partner, and often an explicit expectation that they take on some mentoring of others themselves, since developing that skill is frequently the actual next step in their own growth, not something to route around.

Worked example

Situation

I mentored someone who worked best from a fully worked-out plan before starting anything ambiguous, while my own instinct is to start acting and figure out the plan as I go. Early on, my default approach (throw them a loosely scoped problem and let them work it out) was clearly causing more anxiety than growth; they'd stall rather than experiment.

Action

Instead of pushing them toward my own style, I adjusted the mechanism while keeping the goal (building comfort with ambiguity) the same: gave them explicit structure up front for the first few tasks (a rough plan to react to and revise, rather than a blank page), and deliberately widened the ambiguity only gradually as their confidence grew, checking in on how it felt rather than assuming.

Result

Over time they needed less upfront structure and became noticeably more willing to start from a loosely scoped problem on their own, which was the real signal of the adaptation working: not that they'd adopted my style, but that they'd built their own comfort with ambiguity at a pace that actually worked for them.

Trade-offs & pitfalls

• Assuming your own learning style is the default. The single most common failure here is mentoring the way you'd want to be mentored, rather than the way the specific person in front of you actually learns.
• Treating feedback-culture adaptation as optional politeness rather than an equity issue. Delivering feedback the same blunt way to everyone regardless of their background isn't neutral, it systematically disadvantages people for whom that style reads as disrespect rather than directness.
• Over-adapting to the point of never stretching the person. Adapting to someone's current style is different from leaving them there permanently; part of growth is gradually building comfort outside their comfort zone, not just permanently accommodating it.
• Forgetting that senior mentees need a different kind of adaptation, not just less attention. Assuming a senior mentee needs nothing from you, rather than a different kind of engagement (including expecting them to mentor others), under-invests in someone who still has real room to grow.

Direct answer

Beyond CUPED (using a pre-experiment covariate to residualize the outcome), the two other standard variance-reduction levers are stratified (blocked) randomization, which forces balance on a known factor at assignment time instead of hoping random chance balances it, and covariate or regression adjustment, which is the general case of "adjust for a predictive covariate" that CUPED is one specific, pre-experiment-only instance of. Both work by removing a source of outcome variance that is not related to treatment, so the same true effect becomes easier to distinguish from noise; both reduce required sample size roughly in proportion to how much outcome variance the factor explains, and neither invents a new number, they trade a known, explainable source of variance for a smaller residual.

Structured elaboration

Stratified (blocked) randomization

Instead of randomizing the whole population as one pool, split the population into strata on a factor known before assignment (country, device type, new vs. returning user), then randomize independently within each stratum so each arm gets a matched share of every stratum. This removes between-stratum variance from the treatment-effect estimator's variance, because the strata are balanced by design rather than by luck: with plain randomization on a highly imbalanced population, an unlucky split (e.g., treatment skewing toward the low-baseline country) inflates the observed variance of the effect estimate even though the true effect is unaffected.

It is applicable whenever you have a discrete, pre-assignment factor that is known to correlate with the outcome and is stable at randomization time. It differs from covariate adjustment in when the correction happens: stratification acts at assignment time (balance is enforced), while regression adjustment acts at analysis time (balance is estimated and subtracted after the fact). The two are complementary, not substitutes: stratify at assignment for the factors you can, and adjust for continuous covariates at analysis.

Covariate / regression adjustment

This is the general technique of fitting a model for the outcome on one or more covariates (not restricted to pre-experiment-only, unlike CUPED) and using the model to remove predictable variance from the outcome before comparing arms, most simply via ANCOVA (analysis of covariance), a linear regression of $Y$ on the treatment indicator and covariates that removes the variance those covariates explain from the comparison, the same variance-reduction logic as CUPED and stratification, just carried out as a regression rather than a pre-experiment covariate or a balanced split. It is applicable whenever you have covariates, pre-experiment or otherwise as long as they cannot themselves have been affected by treatment, that are predictive of the outcome. CUPED is the special case where the covariate is restricted to a pre-experiment value of the outcome metric itself; regression adjustment generalizes this to any number of eligible covariates and lets you combine several weak predictors into one stronger adjustment.

Effect on sample size and power

For both techniques, if the factor being controlled for explains a fraction $R^2$ of the outcome's variance, the variance of the treatment-effect estimator shrinks by roughly that same factor, and required sample size for a fixed target precision shrinks proportionally, since sample size for a fixed effect and power scales with the variance of the metric. A factor that explains little of the outcome variance buys little; a strong, well-chosen factor can meaningfully shorten the required test duration for the same statistical bar.

Worked example: stratifying a multi-country test

A test is planned across three countries with very different baseline conversion rates: Country A at 4%, Country B at 12%, Country C at 22%, in roughly equal traffic shares (each about one third of total users). Without stratification, plain randomization can by chance send more of one country's traffic to one arm, and even without that bad luck, the pooled outcome variance includes the between-country spread of baseline rates as extra noise the estimator has to average out.

Using the law of total variance, the overall variance of the outcome decomposes as:

$\operatorname{Var}(Y) = \underbrace{\mathbb{E}\big[\operatorname{Var}(Y \mid \text{country})\big]}_{\text{within-country variance}} + \underbrace{\operatorname{Var}\big(\mathbb{E}[Y \mid \text{country}]\big)}_{\text{between-country variance}}$

Stratifying by country and analyzing as a weighted average of within-country treatment effects removes the second (between-country) term from the treatment-effect estimator's variance, since each stratum is separately balanced and the between-stratum spread no longer contributes noise to the comparison. Concretely: with baseline rates of 4%, 12%, 22% and equal stratum weights, the between-country component of variance is

$\bar p = \frac{0.04+0.12+0.22}{3} = 0.1267$

$\operatorname{Var}(\bar p) = \frac{1}{3}\Big[(0.04-\bar p)^2 + (0.12-\bar p)^2 + (0.22-\bar p)^2\Big] = \frac{1}{3}(0.00751 + 0.0000445 + 0.00871) = 0.00542$

That 0.00542 is exactly the between-country variance component the stratified analysis removes from the pooled estimator's variance, computed directly from the three stated baseline rates, not asserted; how large a share of total variance that is depends additionally on the within-country binomial variance at each rate, which you would combine with this term using the same decomposition to get the full picture before quoting an overall percentage reduction.

Implementation for the multi-country case

• Assign the stratum at randomization time using the same deterministic hash-bucketing approach as the overall unit assignment, but nest it: hash within each country separately (or include country in the hash key) so each country independently hits its target split ratio.
• At analysis time, estimate the treatment effect within each country and combine as a weighted average (weighted by stratum size or by inverse variance), rather than pooling raw counts across countries, which is what actually realizes the variance reduction shown above.

Trade-offs and pitfalls

• Stratifying on too many dimensions at once shrinks individual strata until some contain too few units to balance meaningfully, and can create empty or near-empty cells, especially when crossing multiple categorical factors (country times device times cohort).
• A stratification factor chosen because it is convenient rather than because it is predictive buys little variance reduction while adding real implementation complexity; check the factor's explanatory power on historical data before committing the assignment pipeline to it.
• Regression adjustment on covariates measured close to, but not strictly after, the treatment start needs the same scrutiny as CUPED's pre-experiment-only requirement: any covariate that could plausibly be influenced by treatment invalidates the adjustment's unbiasedness, not just its efficiency.

This is the load-time mechanics question, not the "what are SCD (Slowly Changing Dimension) types" theory question: given a staging table of the latest snapshot rows for a customer dimension, implement the MERGE (or MERGE-equivalent statement sequence) that correctly expires changed rows and inserts new current versions.

The implementation (as two statements, since a single MERGE can't both expire an old row and insert a new one for the same logical entity in one pass):

-- Step 1: expire current rows whose tracked attributes changed
UPDATE dim_customer
SET effective_to = '2026-01-05', is_current = false
WHERE is_current = true
  AND customer_id IN (
    SELECT d.customer_id
    FROM dim_customer d
    JOIN stg_customers s ON s.customer_id = d.customer_id
    WHERE d.is_current = true AND s.name != d.name
  );

-- Step 2: insert new current rows, for both changed customers and brand-new ones
INSERT INTO dim_customer (cust_key, customer_id, name, effective_from, effective_to, is_current)
SELECT nextval('cust_key_seq'), s.customer_id, s.name, '2026-01-05', '9999-12-31', true
FROM stg_customers s
LEFT JOIN dim_customer d ON d.customer_id = s.customer_id AND d.is_current = true
WHERE d.customer_id IS NULL;

Verified against a fixture with an unchanged customer, a customer whose tracked attribute changed, and a brand-new customer:

before: C1=Alice (current), C2=Bob (current)
staging: C1=Alice Smith (changed), C2=Bob (unchanged), C3=Carol (new)

after both steps:
  cust_key | customer_id | name        | effective_from | effective_to | is_current
  1        | C1          | Alice       | 2025-01-01     | 2026-01-05    | False
  3        | C1          | Alice Smith | 2026-01-05     | 9999-12-31    | True
  2        | C2          | Bob         | 2025-01-01     | 9999-12-31    | True   <- untouched
  4        | C3          | Carol       | 2026-01-05     | 9999-12-31    | True   <- new

invariant check (exactly one is_current=true row per customer_id): holds, 0 violations

re-running the same two statements again: identical result (confirmed idempotent, since step 1's
WHERE clause only matches rows that are STILL current and STILL differ, and step 2's WHERE only
matches customers with NO current row, both of which are already false after the first run)

What makes the merge atomic: the two statements above are not atomic against each other by default, a reader querying between step 1 committing and step 2 committing would briefly see zero current rows for a changed customer. Wrapping both statements in a single database transaction (BEGIN ... COMMIT) makes the pair atomic from any reader's perspective, so a concurrent query never observes the intermediate expired-but-not-yet-reinserted state, and a crash between the two statements rolls back to the pre-update state instead of leaving the dimension with a gap.

Why this can't be one MERGE statement: a MERGE clause matches one target row per source row and applies exactly one of INSERT/UPDATE/DELETE to it. SCD Type 2's core operation, "close out the old row AND open a new one for the same logical key," touches two rows (one existing, one new) for a single source row, which is structurally outside what one MERGE pass can express. This is why real SCD2 implementations are either the two-statement-in-a-transaction pattern shown here, or a changelog/append-only table with a view that computes "current" on read (avoiding the UPDATE entirely, at the cost of a more expensive read-time query).

Direct answer
For a high-cardinality categorical field (millions of distinct product IDs, or tens of thousands of SKUs), aggregate to a manageable set of top contributors plus an explicit "other" bucket, support interactive search or drill-down for anything not in the top set, and consider progressive loading or sampling so the interface stays responsive at that scale.

Structured elaboration

1. Top-N plus "other": rank categories by the metric of interest (revenue, volume) and show the top N (commonly 10-20) individually, rolling everything else into a single "other" bucket, so the chart stays readable regardless of how many total categories exist.
2. Interactive search/typeahead: for a user who needs a SPECIFIC category not in the visible top set, provide a search box with typeahead (server-side, since client-side search over millions of values isn't feasible) that lets them find and pin an arbitrary category into the view.
3. Trend thumbnails/sparklines: for the categories a user has selected or pinned (top-N or searched), show a small trend sparkline alongside the aggregate value, so relative performance over time is visible without a full separate chart per category.
4. Backend patterns: pre-aggregate top-N rollups (refreshed periodically) for the default view; route ad-hoc searches for a specific, less common category to a live (but appropriately indexed/limited) query rather than trying to pre-compute every possible category.
5. Privacy/performance note: when the categorical field could reveal individual-level detail at very fine grain (e.g. specific low-volume products tied to a single customer), apply a minimum-volume threshold before surfacing a category individually, similar to a privacy-preserving aggregation floor.

Worked example
A revenue-by-product-SKU view with 50,000 SKUs shows the top 20 SKUs by revenue individually, an "other (49,980 SKUs)" bar for the remainder, and a search box letting a merchandiser find and pin SKU #38442 specifically, with a sparkline showing that SKU's trailing 8-week trend once pinned.

Trade-offs and pitfalls
Setting the top-N cutoff too low hides genuinely important mid-tier categories; too high and the chart becomes unreadable again. Validate the cutoff against how much of the total metric the top-N set actually captures (e.g. "top 20 SKUs = 65% of revenue") rather than picking a round number arbitrarily.

Approach
I would first dedupe to one row per user per active day, because multiple events on the same day should not create extra streak length. Then I would use the gaps-and-islands pattern. An island is a run of consecutive dates. A simple way to label each island is to subtract the row number from the date, because consecutive dates keep the same shifted value.

WITH active_days AS (
  SELECT DISTINCT user_id, activity_date
  FROM raw_events
  WHERE activity_date IS NOT NULL
), islands AS (
  SELECT
    user_id,
    activity_date,
    activity_date - (ROW_NUMBER() OVER (
      PARTITION BY user_id
      ORDER BY activity_date
    ) * INTERVAL '1 day') AS island_key
  FROM active_days
)
SELECT user_id,
       MIN(activity_date) AS start_date,
       MAX(activity_date) AS end_date,
       COUNT(*) AS streak_length
FROM islands
GROUP BY user_id, island_key;

Handling bad data
If there are known missing source days, I would join to a data-quality calendar and exclude those outage dates before building streaks. That prevents a pipeline failure from looking like a user break.

Worked example
For dates 1, 2, 3, 5, the first three share one island. The 5 starts a new one. If day 4 was a confirmed outage, I would not count it as a true break.