Senior Data Analyst Interview Preparation Guide - FAANG Standards

Direct answer

Compare each incoming staging row to the current dimension row by business key: if a tracked attribute changed, close out the current row (current_flag = false, effective_to = today) and insert a new row with a new surrogate key and current_flag = true. In SQL this is usually written as a two-step UPDATE-then-INSERT, or as a single MERGE where supported.

Structured elaboration

• Step 1, detect and close changed rows: UPDATE product_dim SET effective_to = CURRENT_DATE - 1, current_flag = false WHERE current_flag = true AND product_id IN (SELECT product_id FROM stg_products s WHERE s.price <> product_dim.price OR s.name <> product_dim.name). Compare every tracked attribute, not just one, or you will miss changes.
• Step 2, insert new versions: INSERT INTO product_dim (product_key, product_id, name, price, effective_from, effective_to, current_flag) SELECT new_key(), s.product_id, s.name, s.price, CURRENT_DATE, '9999-12-31', true FROM stg_products s LEFT JOIN product_dim d ON s.product_id = d.product_id AND d.current_flag = true WHERE d.product_id IS NULL OR s.price <> d.price OR s.name <> d.name. This also handles brand-new products (the LEFT JOIN finds no current row).
• Why two steps, not one blind INSERT: you must close the old row before, or in the same transaction as, inserting the new one, or you will briefly have two current_flag = true rows for the same business key.

Worked example

product_dim(product_key, product_id, name, price, effective_from, effective_to, current_flag) starts with (1, 'P100', 'Widget', 9.99, '2026-01-01', '9999-12-31', true). Staging row ('P100', 'Widget', 12.99) arrives with a price change. Step 1 updates key 1 to effective_to = today-1, current_flag = false. Step 2 inserts (2, 'P100', 'Widget', 12.99, today, '9999-12-31', true). Any fact loaded before today still resolves to key 1 (old price) via the as-of join; any fact from today onward resolves to key 2.

Trade-offs and pitfalls

On engines that support a native MERGE statement, wrap the same UPDATE/INSERT logic in one MERGE for atomicity and to avoid a race between the two steps. Always compare ALL tracked attributes in the change-detection predicate; comparing only one column silently misses changes to the others. If the dimension is large, batch the detection query so it uses an index or clustering key on business_key rather than scanning the whole table on every load.

Direct answer

The interviewer wants to see whether you treat pushback as a signal to investigate rather than an obstacle to argue past. A strong answer names the specific objection, what you did to address it (more evidence, a smaller reversible test, surfacing a hidden concern), and the actual outcome, including if the recommendation still wasn't adopted.

Structured elaboration

Use a simple frame to structure the story:

• Situation: what the recommendation was and who pushed back, and roughly what their stated objection was.
• Task: what needed to happen next given that pushback.
• Action: the concrete steps you took, for example diagnosing the real underlying concern, bringing a smaller or lower-risk test instead of re-presenting the same evidence louder, or looping in someone the stakeholder trusts.
• Result: what actually happened, plus what you'd do differently, even if the honest answer is that they still said no.

Worked example

Example (illustrative, adapt to your own experience): a category manager dismissed a recommendation to shift ad spend away from a channel, saying "that channel builds our brand, the model doesn't capture that." Instead of re-presenting the same chart, the analyst asked what evidence would actually change the manager's mind, proposed a two-week holdout in a single region as a low-risk test, and came back with a direct regional comparison. The manager agreed to a partial reallocation for one quarter rather than the full change.

If you haven't faced this in a professional analytics role, use a project or coursework example where someone disagreed with a data-based conclusion, and focus the story on how you diagnosed why they disagreed rather than on the size of the business outcome.

Trade-offs and pitfalls

Avoid a story where the stakeholder is a strawman who simply came around, interviewers discount that. Also avoid defaulting to "let's run a pilot" for every disagreement, sometimes speed matters more than certainty and the right move is a smaller concession, not a new experiment.

What the interviewer probes next

Expect a follow-up on what you'd do if the additional evidence still hadn't changed their mind, or a question turned around to ask about a time the stakeholder's pushback turned out to be right.

Situation: At my previous company I supported Sales and Operations dashboards used weekly by VPs. A junior analyst (new to our team, 3 months in) built key dashboards with slow-running SQL and many nested joins; refreshes took 20–30 minutes and stakeholders complained. We had a product quarterly review in three weeks, so this was urgent.

Task: My goal was to coach them to optimize queries and dashboard design so refreshes were under 5 minutes, and to build their SQL skills for future independence.

Action:

• Technical coaching:
  • Reviewed their queries together line-by-line, identifying expensive patterns (SELECT *, unnecessary joins, missing join predicates, and lack of indexes).
  • Showed them how to rewrite joins (moving filters before joins, using EXISTS vs JOIN for filtering), add appropriate WHERE clauses, and leverage CTEs for clarity.
  • Introduced query profiling: explain plans, runtime sampling, and how to read index usage.
  • Built a checklist for performance (limit columns, predicate pushdown, avoid cross joins).
  • Pair-programmed a refactor session where I led changes and they typed, then they repeated on a different query.
• Non-technical coaching:
  • Taught them how to set expectations with stakeholders (communicate ETA and provide interim CSVs).
  • Gave feedback on documenting assumptions and writing clear comments.
  • Scheduled short follow-ups and encouraged questions, fostering psychological safety.

Result:

• Within one week we reduced average dashboard refresh from ~25 minutes to 3–4 minutes; the product review used the updated dashboards without issues.
• The analyst went from relying on me for optimizations to independently optimizing similar queries in subsequent sprints; their PR turnaround time dropped by 40%.
• Team impact: stakeholder satisfaction improved (measured via quick survey) and engineering time spent on ticketed performance issues decreased by ~60% over the quarter.

This taught me that combining hands-on technical walkthroughs with process checklists and communication coaching accelerates skill growth and delivers immediate business value.

Situation: After a mobile release, a cohort’s analytics show missing events. As the on-call data scientist, I need a reproducible, prioritized debugging & mitigation plan to find root cause and reduce further data loss.

Plan (prioritized steps):

1. Triage & scope

• Confirm affected cohort (app version, OS, device models, region, user IDs, timeframe).
• Compare event volumes vs baseline and percent drop; determine if complete loss or partial.

2. Fast mitigation

• If release likely, roll-back toggle/feature-flag for event emission or disable new SDK change.
• Enable high-priority sampling or temporary alternative event path for the cohort (e.g., batched upload to backup endpoint).

3. SDK checks (mobile)

• Check changelog/commit diff for SDK changes around event batching, buffering, flush triggers, or exception handling.
• Inspect device logs / in-app telemetry for SDK errors, exceptions, crash reports, or rate-limit responses.
• Verify SDK configuration: API keys, endpoint URLs, session handling, opt-out logic, consent flags.

4. Network path

• Check mobile network error logs: DNS failures, SSL/TLS handshake errors, HTTP 4xx/5xx, timeouts, proxy rules.
• Reproduce calls from affected devices / emulators; capture packet traces or use Charles/mitmproxy to view requests and payloads.
• Validate CDN or load balancer health and recent config changes.

5. Ingestion brokers & pipeline

• Inspect API/gateway logs for received requests and error rates. Are requests reaching edge?
• Check broker metrics (Kafka/RabbitMQ): incoming message rate, rejected messages, partition errors.
• Look for schema validation failures - malformed events might be dropped.

6. Consumer lag & downstream

• Monitor consumer group lag (Kafka offsets): if lag spikes, consumers may be slow or crashed.
• Check downstream processing errors (parsers, enrichment, storage) and DLQ/backpressure.
• Verify storage write errors (e.g., failed inserts to warehouse, rate limits, quota).

7. Telemetry & observability

• Correlate logs with APM traces, mobile SDK telemetry, and business metrics.
• Inspect alerting dashboards for anomalies aligned to release time.
• Search for feature-flag or rollout logs that map users to cohort.

8. Root cause hypothesis & test

• Form hypotheses (e.g., SDK bug causing buffered events not flushed; endpoint changed; schema rejection).
• Create tests: instrument a canary build, replay events, and simulate network conditions.

9. Recovery & data repair

• If events were buffered on devices, trigger a forced flush via SDK update/remote config or instruct clients to reopen session.
• If ingestion accepted but downstream failed, replay from broker or reprocess backups.
• If irrecoverable, estimate volume loss and backfill where possible using alternative sources (server logs, third-party SDKs).

10. Post-incident actions

• Document timeline, root cause, and action items.
• Add tests: e2e mobile-to-warehouse integration, schema validation, consumer resilience.
• Improve telemetry: SDK health metrics (flush success, queue length), network failure counters, per-version event rates, and automated alerts on cohort-level drops.

Key reasoning: prioritize quick mitigation to stop ongoing loss, gather evidence across SDK → network → ingestion → consumers, validate with reproducible tests, and plan recovery and preventive observability improvements.

Direct answer
For many correlated metrics (or predictors showing multicollinearity), use a correlation heatmap or a scatterplot matrix (pairplot) to get a first overview of redundancy, then more targeted tools like partial dependence or residual-vs-predictor plots to distinguish genuinely independent relationships from ones that are just correlated with something else already in the view.

Structured elaboration

1. Correlation heatmap: a matrix of pairwise correlation coefficients, color-coded (diverging palette centered at zero), gives a fast first read of which metric pairs move together; it's the cheapest starting point for spotting groups of redundant metrics.
2. Scatterplot matrix (pairplot): shows every pairwise relationship as a small scatterplot in a grid, revealing NON-linear relationships a correlation coefficient alone would miss (correlation only captures linear association).
3. Interactive controls for discovery: linked brushing across the matrix (selecting a range in one panel highlights the corresponding points in all others) helps an analyst trace whether an apparent correlation is driven by a specific subgroup rather than the whole population; a filter to isolate a time window or segment helps check whether the correlation is stable or an artifact of a specific period.
4. Diagnosing genuine redundancy vs. confounding: partial dependence plots and residual-vs-predictor plots go a step further than raw correlation, showing whether a predictor's apparent relationship with an outcome persists after accounting for other predictors, which is how you separate "these two metrics are redundant" from "these two metrics are both driven by a third factor."
5. Principal components (PCA): for a genuinely large set of correlated metrics, PCA compresses them into a small number of orthogonal components (each new component capturing a different, non-overlapping pattern in the data, so none of them double-count the same signal); a scree plot showing how much variance the first 2-3 components explain quickly reveals how much real redundancy exists across the full set, complementing (not replacing) the correlation heatmap and scatterplot-matrix views.
6. Avoiding overwhelm: for a genuinely large number of correlated metrics, cluster them first (e.g. hierarchical clustering on the correlation matrix) and order the heatmap by cluster, so visually adjacent rows/columns are the ones most likely to be redundant, rather than presenting an unordered wall of numbers.
7. Which of these to reach for day to day: the correlation heatmap and scatterplot matrix are the everyday first-pass tools, cheap to build and read in seconds; linked brushing and filtering are the next step once the heatmap raises a question worth chasing interactively; partial dependence, residual-vs-predictor plots, PCA, and hierarchical clustering are deeper, less frequent tools worth reaching for only when the heatmap and pairplot leave a real ambiguity (genuine redundancy versus a shared confound) unresolved.

Worked example
A correlation heatmap across 12 marketing-spend channels reveals three channels, Paid Search, Paid Social, and Display, forming a tight, mutually-correlated cluster (pairwise correlations of r=0.91 between Paid Search and Paid Social, r=0.88 between Paid Search and Display), likely because they're all driven by the same seasonal campaign calendar; a scatterplot matrix confirms the relationships are roughly linear rather than curved, and a residual analysis (regressing Display's spend against Paid Search and Paid Social) shows Display's incremental explanatory power (its added R-squared) drops from 0.77 to 0.04 once Paid Search and Paid Social are already in the model, flagging Display as the most redundant of the three and a candidate to drop from the standalone dashboard view.

Trade-offs and pitfalls
Correlation alone can miss non-linear redundancy (two metrics could be perfectly related in a U-shape and show near-zero linear correlation), which is exactly why a scatterplot matrix or a non-linear diagnostic should follow, not replace, the initial correlation heatmap.

Direct answer

I order early initiatives using a small, consistent set of criteria, impact on something stakeholders already track, effort or time to deliver, and risk or dependency if delayed, and I get buy-in by making that scoring visible and inviting stakeholders to challenge the inputs, not just the ranking. People argue with an unexplained priority call a lot more than they argue with a number they helped set.

Structured elaboration

• Pick two to four criteria maximum: typically impact, effort, risk, and how much the item depends on other unfinished work. More than that turns the exercise into a spreadsheet nobody trusts.
• Score candidates with the actual people who'd push back if they disagreed, in the room or in doc comments, so disagreement happens on the inputs ("is this really high-impact") rather than on the final order after the fact.
• Sequence dependencies explicitly: something high-impact but blocked on another team's unfinished work moves down the list even if its raw score is high, because sequencing is about what's doable next, not just what scores well in isolation.
• To get agreement rather than just present a ranking: share the scoring approach before you have an answer, ask stakeholders what they'd weight differently, and be willing to change the ranking when someone has real information you didn't have.

Worked example

A wins clearly for the next two weeks. B is real and valuable but sequenced once the dependency clears. I'd bring both scores to the stakeholder who championed B and say explicitly: "B scores well on impact, but it's blocked on a team that's six weeks out. Here's what I'd do in the meantime that doesn't waste that time," rather than just telling them it's deprioritized.

Trade-offs and pitfalls

A purely numeric scoring exercise can hide that the criteria weights themselves are a judgment call; two reasonable people can weight "impact" completely differently, so treat the score as a conversation starter, not an objective verdict. The common early-tenure mistake is presenting a finished ranking as a fait accompli instead of showing the reasoning, which reads as "trust me" from someone with no track record yet.

1. First Response Time (median & P95)

• Decision: Reallocate agents across channels or extend shift coverage if first-touch lagging.
• Trigger: Median > 30 min or P95 > 4 hours for high-priority tickets → alert to staffing lead.

2. Time to Resolution (median & P95)

• Decision: Change SLAs, escalate to specialist teams, or add knowledge-base articles.
• Trigger: Median > SLA (e.g., 24 hrs for standard) or P95 > 72 hrs → investigate root cause.

3. SLA Compliance Rate (by priority)

• Decision: Adjust SLA targets, hire/shift resources, or reprioritize ticket routing.
• Trigger: SLA compliance < 95% for P1 or < 90% for P2 over rolling 7 days.

4. Tickets by Channel & Channel Load %

• Decision: Reallocate agents across email/chat/phone; prioritize channel-specific hiring/training.
• Trigger: One channel > 60% of total load and service levels dropping.

5. Backlog (tickets older than threshold) and Aging Distribution

• Decision: Triage backlog for bug vs process issues; create backlog burn-down plans.
• Trigger: Tickets > SLA-age increasing week-over-week by >20%; alert when >5% are >30 days.

6. Reopen Rate / Escalation Rate

• Decision: Improve quality training, update KB, or escalate product defects to engineering.
• Trigger: Reopen rate > 5% or escalation rate > 3% (product bugs) → product triage meeting.

7. Customer Satisfaction (CSAT) & NPS by issue type

• Decision: Targeted coaching, product improvements, or policy changes.
• Trigger: CSAT rolling 7-day avg < 80% for a category or NPS drop > 10 pts month-over-month.

8. Volume of Repeat Issues / Tags indicating potential product bug

• Decision: Initiate bug triage, create hotfix backlog, or update documentation.
• Trigger: Same-issue volume > 1% of weekly tickets or rapid spike (e.g., 3x baseline) → urgent bug review.

For each metric include trend, segment (priority, product, agent), and SLA baseline in the dashboard to enable drill-down diagnostics and automate alerts when thresholds breached.

Direct answer
Restate the instruction in your own words as a quick confirmation before acting, framed as checking your own understanding rather than doubting them, so it reads as diligence rather than not having listened.

Structured elaboration

• Frame it as confirming your own plan, not re-asking their request. "Just to make sure I act on the right thing, my plan is to do X, does that match what you meant?" reads very differently from "wait, what did you want again?"
• Be specific in the paraphrase, not generic. A vague paraphrase ("okay, got it, I'll handle it") gives them nothing to correct if you actually misunderstood; a specific one gives them an easy, fast way to say "actually, no" if needed.
• Do it briefly and move on. One sentence of confirmation, not a lengthy negotiation over wording; the goal is a fast check, not a renegotiation of the request.
• If genuinely rushed, confirm asynchronously right after rather than not at all: a one-line follow-up message restating what you understood, sent immediately after the quick instruction, still catches a misunderstanding before you've acted on it.

Worked example
Instruction given quickly in passing: "Can you get that report over to finance today?"

Weak version: "Yep, will do." (No confirmation of which report, which finance contact, or what today means if it's late in the day.)

Better version: "On it, I'll send the Q3 variance report to Priya in finance by end of day, that's the one you mean?"

This surfaces, in one sentence, exactly which report, which recipient, and what "today" means, giving them a fast chance to correct any of the three if you guessed wrong, without making them repeat the whole instruction.

Trade-offs and pitfalls

• Doing this for every trivial instruction can come across as needing excessive hand-holding; reserve the explicit paraphrase for instructions with real ambiguity or real consequences if you get it wrong.
• A paraphrase that's too close to a verbatim repeat of their words doesn't actually test whether you understood the intent, only whether you can repeat words back; try to restate it in language that shows you grasped the underlying goal, not just the surface phrasing.
• If they seem rushed or impatient with the confirmation, a very short version ("Q3 report to Priya today, correct?") gets the same benefit with almost no added time.

Direct answer

I would not decide from either metric alone. I would translate both into one comparable number (revenue per user over a horizon, adjusted for retention), test with a controlled experiment, and only then ship. A feature that raises engagement and lowers revenue per session can still be good if sessions rise by more than revenue per session falls, and bad if the extra engagement cannibalises revenue (replaces sessions that would have earned more) or shortens users' lifetime with the product.

The identity that settles most of the argument

Revenue per user = sessions per user x revenue per session.

Illustrative result from an A/B test (users randomly assigned to old or new experience), per week:

Break-even: revenue per session can fall by up to 1 - 4.0/4.5 = 11.1% before revenue per user drops. Check this is not only first-week novelty (people trying a feature because it is new): engagement bumps often fade.

Time horizon

• Short term (1 to 4 weeks): revenue per user and guardrails (metrics that must not get worse, such as errors, support contacts and refunds).
• Medium term (8 to 12 weeks): retention of the treated cohort, since engagement without retention is not a gain. Keep a small long-term holdout (for example 5% of users deliberately stay on the old experience for a quarter, as a comparison) to measure the delayed effect.
• Long term: lifetime value (LTV, total margin one user is expected to bring). A simple steady-state estimate is monthly ARPU (average revenue per user) x gross margin (the share of revenue left after the direct cost of serving it) / monthly churn (share of users lost each month). Dividing by churn works because 1 / 5% = 20: with 5% leaving each month, the average user stays about 20 months, so lifetime margin is 20 months x ($20 x 70% = $14 a month) = $280.

Choosing between a short-term revenue lift and a retention gain

Illustrative base: ARPU $20 a month, 70% gross margin, 5% monthly churn, so LTV = 20 x 0.7 / 0.05 = $280.

A loses despite the revenue gain: with +3% ARPU, churn can rise only to 5% x 1.03 = 5.15% before LTV falls. Segment the result (new vs established users, plan, region); a feature that helps new users and hurts loyal ones needs a different decision from one that moves everyone slightly.

Reconciling teams that optimise different goals

Suppose one team owns weekly active users and another revenue per user. Do not split the difference by negotiation. Agree on a shared decision rule before the test:

1. A shared top-line, such as revenue per user and retained users together (revenue = users x revenue per user).
2. Each team's metric becomes a guardrail on the other: the engagement team may not ship if revenue per user falls by more than an agreed amount; the revenue team may not ship if retention falls.
3. When the metrics conflict, the decision is made by whoever owns the shared top-line, using the LTV estimate and its stated assumptions.
4. Record the assumptions (horizon, margin, churn) so the decision can be revisited when results arrive.

Decision and pitfalls

Ship if treatment revenue per user is up by more than the noise (the confidence interval, the plausible range for the true effect, sits entirely above zero, so the gain is unlikely to be chance), retention is flat or better at the holdout checkpoint, and no key segment is harmed. Otherwise iterate on the design. Pitfalls: judging on weeks of novelty; mixing gross and net revenue (if a discount pushes gross revenue per session to $1.00 but refunds and discounts hand back $0.08, net is $0.92, so compare like with like); adding the two teams' percentage gains instead of multiplying (+12.5% sessions and -8% revenue per session do not make +4.5%; 1.125 x 0.92 = 1.035, which is +3.5%); ignoring cost to serve the extra sessions (support, servers and payment fees that each session adds).

Direct answer. When a metric drops and you need to find where it concentrates, the order of slicing should go from cheapest and highest-signal to most expensive: start with dimensions that are both easy to query and historically most likely to explain a change for this business (platform, geography, acquisition channel), then move to funnel step and cohort, and stop as soon as one slice explains most of the change rather than exhaustively checking every dimension.

Structured elaboration. Six segment dimensions worth having ready: platform (web/iOS/Android, catches client-specific regressions), geography or market, acquisition channel or campaign, funnel step (which stage of a multi-step flow lost users), user tenure or cohort (new versus returning), and a technical dimension like app version or API version. For each, the check is the same shape: compute the metric for that dimension in the affected window and in a comparable prior window, and look at each slice's CONTRIBUTION to the total change, not just whether its own rate moved, since a slice can have a big rate change but be too small in volume to matter, or a small rate change but be big enough in volume to explain most of the drop.

Under a hard time limit, prioritize by two criteria together: which dimension is cheapest to query (platform and geography are usually pre-aggregated in most reporting layers; funnel-step and cohort breakdowns often require a heavier query) and which dimension has the highest PRIOR probability of explaining a drop for this specific business (a mobile-heavy consumer product should check platform first; a B2B SaaS product with few large customers should check the largest accounts individually before broad segmentation even makes sense).

Worked example. A checkout conversion rate drops 12% week over week. Platform breakdown (cheap, first check): web is flat, iOS is down 40%, Android is flat. That single slice already explains almost the entire drop, since iOS is roughly a third of volume but is carrying nearly all of the decline; no further segmentation (geography, channel) is needed before moving to the next stage of investigation (checking what changed for iOS specifically: an app release, a software development kit (SDK) update, or a payment-provider issue affecting iOS).

Trade-offs and pitfalls. The failure mode to avoid is checking every dimension exhaustively and reporting 'no single segment explains it' when the real answer is an INTERACTION between two dimensions (the drop is only in iOS AND only in one country), which a one-dimension-at-a-time sweep misses; if the first pass doesn't cleanly localize the change, the next move is a two-way cross-tab on the two most suspicious dimensions rather than continuing down a longer list of single dimensions.