Airbnb Mid-Level Data Analyst Interview Preparation Guide

Direct answer

I design the smallest test that could actually prove me wrong, write down what I expect to see before I run it, and keep the blast radius small enough that being wrong doesn't cost anything real while I find out.

Structured elaboration

Choosing the smallest falsifying experiment: not the smallest experiment that would confirm what I already believe, but the smallest one that could show my understanding is incomplete or wrong. Stating the expectation and acceptance criteria first: I write down what I expect to happen before running it, so I can't quietly reinterpret an ambiguous result afterward as agreeing with me.

Isolating blast radius: a sandbox, a lab setup, or a separate account, with a cost or scope I've deliberately bounded in advance, so a wrong understanding is cheap to discover rather than expensive.

Representative rather than toy data: using data or conditions close to the real failure pattern, not an artificially clean case that would pass regardless of whether my understanding is actually right.

Making the result reproducible: documenting the exact setup and outcome so it holds up to scrutiny, and so I can redo the check later if the underlying system changes, rather than relying on memory of what happened.

Reproducing claims instead of trusting them: if my understanding came from a vendor's or a blog's claim, I try to reproduce that specific claim myself rather than taking it as already proven.

Staged progression before it matters: an isolated experiment first, then something closer to an integration test, then one small, low-risk, production-adjacent change, rather than jumping straight from a lab result to something that matters.

Worked example

I'd read that a specific retry and backoff configuration would fix a flaky downstream call, but hadn't verified it myself. I set up a throwaway environment and replayed real traffic that reproduced the actual failure pattern, rather than a clean synthetic case. Before running anything, I wrote down the falsifiable claim: the new configuration should reduce failures without increasing load on the downstream service, not just "it'll work." I ran it isolated, checked both halves of that prediction, and both held. I rolled it out on one non-critical path first, watched it for a defined period, then extended it further once that held up too.

Trade-offs and pitfalls

The most common failure mode is designing a gentle test that only confirms the claim rather than one that could genuinely falsify it, especially when the claim came from a source you already want to trust. The other is skipping the staged rollout because the lab result felt convincing enough, and jumping straight from an isolated test to full production.

Direct answer
I'll use Amazon's "Customer Obsession" as the example: in plain terms it means starting from the customer's actual experience and working backward to the decision, rather than starting from what's easiest or cheapest for the team and working forward to how it will land on the customer. In day-to-day work that shows up as a specific, repeatable habit: before finalizing a decision, explicitly write down what the customer will experience as a result, not just what the team will ship.

Structured elaboration

1. State the value in plain language first, in one or two sentences, before layering on any nuance. A stated value is only useful if you can restate it without jargon; if you can't, you probably don't understand it well enough to apply it.
2. Trace two or three concrete decisions the value would actually change, not just decisions it would be compatible with. The test is not "does this decision fit the value" (almost any reasonable decision can be described as fitting almost any value after the fact); the test is "would I have decided differently without this value in mind."
3. Be specific about the mechanism, not just the outcome. It's not enough to say "I'd focus on the customer"; describe the actual practice (writing the customer-facing consequence down explicitly, reviewing a metric that measures customer impact rather than only internal effort, asking a specific question in a design review) that operationalizes the value day to day.
4. Acknowledge the value has a cost or a trade-off, because a value with no real cost usually is not being taken seriously. A genuinely operative value changes what you'd otherwise have done, which means it sometimes means doing the harder or slower thing.
5. Connect it back to your own role specifically, since the same value plays out differently for different functions; the mechanism for a backend engineer, a designer, and an analyst are all different concrete practices in service of the same underlying value.

Worked example
Say you're building a dashboard intended to help a seller reduce order defects. A team NOT applying customer obsession as a working discipline might ship the dashboard once the underlying data pipeline is stable and the metrics are technically correct, treating "the data is right" as the finish line. Applying the value changes the finish line: before shipping, you'd sit with two or three actual sellers using an early version and ask what decision they're trying to make when they open it, which might surface that they need same-day defect data to catch a bad batch before it ships further, not a metric that's accurate but a day stale. The concrete decision that changes: you invest in a same-day data refresh even though it's more engineering effort than the weekly batch job you'd planned, because the customer's real decision-making need, not the easier technical path, is what determines what "done" means. The cost is real (more pipeline complexity, tighter SLAs to maintain) which is exactly why it's evidence the value is actually operative rather than decorative.

Trade-offs & pitfalls
The most common failure is reciting the value's definition fluently and then giving an example so generic it would apply to any company with any stated value ("I always think about the user"), which demonstrates you've read the careers page rather than that you understand the mechanism. A second pitfall is picking an example where the value cost nothing: if every example you give was also simply the obviously correct engineering or business call regardless of the stated value, you haven't actually shown the value did any independent work in your reasoning. A third is over-indexing on one company's specific phrasing so heavily that the answer would sound out of place at any other employer; the goal is to show you can genuinely reason from a stated principle to a concrete decision, a transferable skill, not that you've memorized one company's vocabulary.

A normal (logical) view is just a stored query: each time you SELECT from it the database runs the underlying query against base tables. A materialized view stores the query result physically on disk and serves reads from that precomputed table until it’s refreshed.

When to choose a materialized view for a dashboard:

• The dashboard queries an expensive aggregation over millions of rows and is read-heavy.
• Near-real-time freshness is not strict (minutes/hours acceptable).
• The cost of computing on-demand is higher than storage + refresh cost.
• You want predictable, low-latency dashboard responses for many concurrent users.

Practical refresh strategies to balance freshness vs system load:

• Full scheduled refresh during off-peak windows (e.g., nightly 02:00) if data changes batch-wise.
• Incremental/fast refresh (only apply deltas) when supported — minimizes work and keeps freshness high.
• Hybrid: frequent lightweight incremental refreshes (every 5–15 minutes) and a daily full refresh to correct drift.
• Event-driven refresh: trigger refresh after major ETL job completes (ensures consistency).
• Stagger and rate-limit refreshes if multiple MVs exist to avoid thundering herd.
• Use asynchronous background workers and set a “stale” TTL; dashboards show last_update timestamp and optionally allow manual refresh for power users.

Example (Postgres-ish):

-- create materialized view
CREATE MATERIALIZED VIEW mv_sales_summary AS
SELECT date_trunc('hour', ts) as hour, region, SUM(amount) total
FROM sales
GROUP BY 1,2;
-- refresh nightly
REFRESH MATERIALIZED VIEW CONCURRENTLY mv_sales_summary;

Monitor refresh duration, CPU/IO, and dashboard latency; iterate on granularity (pre-aggregate by hour vs day) and refresh cadence until SLA for freshness/latency is met.

How I would investigate
I would compare the query profile, not just the SQL text. A CTE, or common table expression, is a named subquery. On some warehouses it is inlined, and on others it may be materialized or repeatedly scanned. I would look for repeated reads of the same large table, lost predicate pushdown, and big intermediate results.

First checks

• Run EXPLAIN or the warehouse query profile.
• See whether the same base table is scanned more than once.
• Check whether filters are applied after large joins instead of before them.
• Compare row counts at each step to find the explosion point.

What I would change first
If a suspicious CTE is referenced once, I would inline it or fold it into the parent query. If it is reused multiple times, I would stage the filtered result in a temp table or a materialized table so the expensive work happens once.

Rule of thumb
Readability is good, but if separate CTEs each reread a billion-row fact table, the query gets slower. The fix is usually to push filters earlier, keep only needed columns, and avoid recomputing the same join or aggregation in multiple branches.

A distinct-count-per-period metric like Daily Active Users needs COUNT(DISTINCT user_id) specifically, not a plain COUNT, because a plain COUNT would count every event, over-counting any user who was active more than once in the same period.

Structured elaboration

SELECT DATE(occurred_at) AS day, COUNT(DISTINCT user_id) AS dau
FROM events
GROUP BY day;

COUNT(DISTINCT user_id) deduplicates within each day's group, so a user who logged three events on the same day still contributes exactly 1 to that day's count, correctly answering "how many distinct users were active", not "how many events happened". The GROUP BY-based alternative pushes the deduplication into an inner query instead of an aggregate function: SELECT day, COUNT(*) AS dau FROM (SELECT DISTINCT DATE(occurred_at) AS day, user_id FROM events) t GROUP BY day. The inner SELECT DISTINCT collapses each user down to at most one row per day first, so by the time the outer COUNT(*) runs there's nothing left to deduplicate, it's just counting already-unique rows.

Worked example

Given events for user 1 twice and user 2 once, all on 2025-01-01, and user 3 once on 2025-01-02: the query correctly returns DAU of 2 for 2025-01-01 (users 1 and 2, with user 1's repeat event not double-counted) and 1 for 2025-01-02 (user 3).

Trade-offs and pitfalls

Both forms are logically equivalent and, on the worked example below, return the identical numbers; which one an optimizer executes faster can differ by engine and data distribution, so it's worth trying both against real data rather than assuming one is always better.

COUNT(DISTINCT ...) is measurably more expensive than a plain COUNT at large scale, since the engine typically needs to sort or hash the values to deduplicate them; for very high-cardinality, very large-scale distinct counting (billions of events per day), an approximate distinct-count function (HyperLogLog-based, such as Postgres's hll extension or BigQuery's APPROX_COUNT_DISTINCT) trades a small, bounded error for dramatically better performance, a trade-off worth naming even though it's outside this topic's core scope.

Direct answer
A governance model for report and dashboard changes needs to answer, before anything ships, who has to approve it and what evidence they need to see, while keeping a fast lane for genuinely urgent fixes so the process doesn't become the reason a broken dashboard stays broken longer than necessary.

Structured elaboration
Roles: every report has a named owner (accountable for its correctness), a reviewer role for changes (someone other than the author who checks the change before it ships, catching what the author might miss from being too close to it), and, for the highest-stakes reports, an approver representing the business side (confirming the change reflects what the business actually needs, not just that it's technically correct).

Required artifacts before a change ships: test results (did the automated data-quality and regression checks pass), a description of what changed and why, evidence of dataset lineage if the change touches upstream data, and, for high-stakes reports, explicit business sign-off that the new numbers or new definition is what's intended.

Automated gates in CI: rather than every review being fully manual, automated checks (schema compatibility, data-quality thresholds, visual regression) act as a first filter, so the human reviewer's time goes toward judgment calls, not re-checking things a machine already verified.

Escalation path for urgent fixes: a genuine production incident (a dashboard showing a materially wrong number to executives right now) can't wait for the full review cycle; the model needs an explicit, pre-agreed fast path (a smaller set of required approvals, or a post-hoc review instead of a pre-approval) specifically for this case, so people aren't tempted to just bypass governance entirely under pressure.

Onboarding teams: the model only works if it's actually followed, which means making it the path of least resistance (templates, clear documentation, a lightweight default review for low-stakes reports) rather than a heavy process everyone dreads and route around.

Worked example
A change to how net_revenue is calculated on the company's flagship executive dashboard: because this is a high-stakes, widely-viewed report, it requires a named reviewer to check the CI results (schema tests, a parallel-run comparison against the old definition) plus explicit sign-off from the finance stakeholder who owns the business definition of revenue, before it merges. Two weeks later, a different dashboard (a low-traffic internal ops report) needs a minor filter fix; because it's low-stakes and the change is small, it goes through the lightweight default path, automated checks plus a single peer reviewer, no separate business sign-off required, shipping same-day. When a production incident hits, a dashboard is showing stale data due to a pipeline failure and executives are asking why, the fix goes through the pre-agreed urgent path: a single on-call approver signs off in minutes, the fix ships, and a full retrospective review happens the next business day rather than blocking the fix.

Trade-offs and pitfalls
A governance model that treats every change with the same level of ceremony either moves too slowly for the low-stakes cases (breeding resentment and workarounds) or too loosely for the high-stakes ones (letting a risky change through without real scrutiny), so calibrating required rigor to actual stakes, not applying one uniform process, is the real design work. The urgent-fix escalation path is also a common blind spot: if it's used too often, it becomes the way people route around normal governance entirely, which means the model needs a periodic audit of how often the fast lane gets used and why, not just a policy that exists on paper.

Direct answer

Small cohorts produce noisy retention rates because a rate computed from a handful of users has wide sampling variation even when nothing about underlying behavior has changed, and two standard techniques address this directly: bootstrapped confidence intervals, which quantify how much a small cohort's rate could plausibly have varied by chance, and empirical Bayes (beta-binomial) smoothing, which pulls each small cohort's estimate partway toward a shared, more stable estimate borrowed from other similar cohorts.

Structured elaboration

A bootstrapped confidence interval works by resampling the observed users within a cohort with replacement many times, recomputing the retention rate on each resample, and using the spread of those resampled rates to build an interval around the observed rate; it does not change the point estimate itself, only communicates how much uncertainty surrounds it, which is often enough on its own to stop a team from over-reacting to a single small cohort's number. Empirical Bayes smoothing goes further and actually adjusts the point estimate: it treats each cohort's true retention rate as drawn from a shared distribution across cohorts, estimates that shared distribution's parameters from the pooled data, and then shrinks each individual cohort's noisy observed rate toward the pooled mean, weighted by how much data that cohort has (a cohort with very few users gets pulled hard toward the pooled estimate; a cohort with many users is barely adjusted).

A minimum cohort-size threshold is the simplest and cheapest of the three: below some chosen size, a cohort's individual rate is simply not reported on its own, and is instead rolled into a combined bucket with neighboring cohorts until the combined size clears the threshold.

Worked example

import numpy as np
np.random.seed(3)

true_rate = 0.25
cohort_sizes = [30, 35, 40, 45, 50, 55, 60, 32, 48, 38]
observed = [np.random.binomial(n, true_rate) for n in cohort_sizes]
raw_rates = [o / n for o, n in zip(observed, cohort_sizes)]

pooled_rate = sum(observed) / sum(cohort_sizes)
k = 20  # prior strength, in "pseudo-users"
shrunk_rates = [(o + k * pooled_rate) / (n + k) for o, n in zip(observed, cohort_sizes)]

print('raw_rate range:', round(min(raw_rates), 3), '-', round(max(raw_rates), 3))
print('raw std:', round(np.std(raw_rates), 4), '  shrunk std:', round(np.std(shrunk_rates), 4))
print('pooled_rate:', round(pooled_rate, 3))

Simulating 10 small cohorts (sizes 30 to 60 users) that ALL share the same true 25% retention rate, with a fixed seed, the raw observed rates range from 14.6% to 32.7% purely from sampling noise, a spread of over 18 percentage points despite there being zero real difference in the underlying rate. Applying beta-binomial shrinkage (pooling the 10 cohorts to estimate a shared prior, then blending each cohort's own count with that prior) reduces the standard deviation of the estimates across the 10 cohorts from 0.058 to 0.041, and every shrunk estimate moves closer to the pooled rate of 24.0%, which itself is close to the true 25% rate used to generate the data. A team looking only at the raw rates might have concluded the 32.7% cohort was meaningfully outperforming the 14.6% cohort; the shrinkage result shows both were consistent with the same underlying rate once sampling noise is accounted for.

Trade-offs and pitfalls

Displaying a smoothed value rather than the raw observed rate is appropriate when the audience's decision depends on comparing many small cohorts against each other or against a benchmark, since the raw numbers would otherwise mislead by amplifying noise into apparent differences; displaying the raw value alongside a wide confidence interval is more appropriate when the audience needs to see the actual observed data and understand its uncertainty rather than a blended estimate. A pitfall specific to shrinkage is applying it uniformly without disclosing that it has happened, since a stakeholder unaware that a number has been smoothed may draw conclusions as if it were the raw observed rate.

An outlier is a real, legitimate data point that happens to be extreme; an anomaly is a data point that exists because something went wrong. The two categories overlap in appearance but call for opposite treatment.

Distinguishing them

A customer whose spending is genuinely 50 times the median because they run a large business is a legitimate outlier: keep it in the data, since a model or report that silently drops it distorts reality by pretending large customers don't exist. A sensor reading of `-999` because a device reset to its sentinel default is an anomaly: it doesn't describe anything real and should be quarantined or corrected, not treated as valid signal.

Worked example

A single day's revenue spiking 5x during a well-publicized flash sale is an outlier (a real business event); the exact same 5x spike appearing because a batch job accidentally double-counted every transaction that day is an anomaly (a data-quality defect) with an identical statistical signature but an opposite correct response.

Trade-offs and pitfalls

Because the two can look statistically identical, the fastest reliable triage checklist is: check for known instrumentation failure modes first (a clock reset, a sentinel default value, a timestamp misalignment between two systems), since these have a distinctive, checkable signature (repeated identical sentinel values, an impossible timestamp) that a genuine business event won't share. Only once those specific failure modes are ruled out should you treat an extreme value as a probably-legitimate outlier. Getting this triage wrong in either direction is costly: quarantining a real outlier hides a genuine business signal from decision-makers, while keeping a genuine anomaly in the data corrupts every downstream aggregate that includes it.

Direct answer

Strip currency symbols and separators with a rule that explicitly handles the ambiguous cases (which character is a thousands separator versus a decimal point, and parenthetical negatives), and treat anything you cannot confidently parse as NULL rather than guessing, since a silently wrong number is worse than a visibly missing one.

Structured elaboration

1. Strip presentation, keep meaning: remove currency symbols and any character that isn't a digit, separator, or sign, before attempting to parse the number itself.
2. Resolve the thousands-vs-decimal ambiguity: 1,234.56 (US-style, comma is thousands, period is decimal) and 1.234,56 (many European locales, reversed) look similar but mean different things; a heuristic based on which separator appears last and how many digits follow it can resolve the common cases, but truly ambiguous inputs (like a bare 1,234 with no decimal at all) may need an explicit locale hint from the source rather than a guess.
3. Parenthetical negatives: accounting notation writes a negative amount as (500.00); detect the wrapping parentheses and negate the value, don't let the parentheses become stray characters that break the parse.
4. NULL and invalid markers: an explicit NULL string, an empty string, or a genuinely unparseable value should all become a real NULL in the output, not a zero (zero is a real, different value) and not a parse exception that halts the batch.
5. Fail closed on ambiguity: when a format truly cannot be disambiguated (e.g. a space-separated European thousands format combined with a comma decimal, without knowing the source locale), the defensible choice is to flag it for review rather than silently picking an interpretation that might be wrong half the time.

Worked example

-- Postgres-style; strip symbols/commas, handle parenthetical negatives
SELECT
  CASE
    WHEN raw_amount IS NULL OR TRIM(raw_amount) IN ('', 'NULL') THEN NULL
    WHEN raw_amount LIKE '(%)' THEN
      -1 * REPLACE(REGEXP_REPLACE(raw_amount, '[^0-9.]', '', 'g'), ',', '')::NUMERIC
    ELSE
      REPLACE(REGEXP_REPLACE(raw_amount, '[^0-9.,]', '', 'g'), ',', '')::NUMERIC
  END AS amount_numeric
FROM transactions;

Verified in Python against representative cases: '$1,234.56' correctly parses to 1234.56; '(500.00)' correctly parses to -500.00 (negative); 'NULL' and '' correctly become NULL rather than 0 or an error; a plain '1234' correctly parses to 1234.0. A space-separated European format like '1 234,56' is flagged as a documented gap in the simple heuristic version above, exactly the kind of ambiguous input a careful answer calls out explicitly rather than silently mis-parsing.

Trade-offs and pitfalls

• A "best-effort" parser that never fails closed will, sooner or later, silently mis-parse an ambiguous format as a plausible-looking but wrong number; state explicitly which formats you're confident about and which you're not.
• Regex-based stripping of "everything except digits and separators" can accidentally strip a genuinely meaningful character if the input contains an unexpected symbol (a stray currency code letter, for instance); validate the result's shape (does it look like a number at all?) before casting, not just after.
• This same parsing competency generalizes directly to multi-currency normalization (converting the now-clean numeric amount into a single reporting currency using a historical exchange rate), which is a related but separate concern once the raw string is safely a number.

Direct answer
Autonomy should track demonstrated judgment in a specific domain, not tenure or title, and it should be granted and withdrawn through visible, structural mechanisms, not just a private mental model of how much you trust someone. As someone grows from junior to senior, both the default level of guidance and the criteria for changing it should become more explicit, not less.

What determines the level, not just the person's level

• Domain-specific, not global: someone can have earned full autonomy in one area (their core service) and need more guidance in an adjacent one (security-sensitive changes) they haven't touched before. Treating autonomy as a single dial per person rather than per domain misjudges both directions.
• Base it on evidence: track record of decisions in that specific domain, not just general seniority or how long they've been on the team.

The conversation isn't enough, structure it

• Guidance and autonomy shouldn't live only in how much you check in; they should be encoded in the system itself. Concretely: mandatory review gates on certain categories of change, feature flags that let risky work ship dark before it's fully trusted, and automated checks (tests, linting, policy gates) that catch the class of mistake a specific person is prone to, rather than relying on a human remembering to look for it.
• This matters especially early: a junior engineer with a mandatory review gate on production-config changes isn't being distrusted personally, the system is compensating for a domain they haven't yet built judgment in, and that's a much less fraught conversation than "I don't trust your judgment yet."

Moving the dial, in both directions

• Define, in advance, what "graduating" out of a guardrail looks like: a number of changes in that domain reviewed without a significant issue, or a specific type of decision made correctly under supervision. Vague criteria ("when I feel comfortable") makes the process feel arbitrary to the person on the other side of it.
• The dial also needs to move backward cleanly. If someone senior makes a judgment error in a domain, temporarily reintroducing a guardrail (an extra review, a smaller blast radius) shouldn't read as a permanent demotion; it should be scoped to the specific domain and have the same kind of explicit, objective path back out.

How this shifts junior to senior

• Junior: guidance is broad and mostly structural (required reviews, smaller scoped tasks, pairing), because there isn't yet enough track record to know where the real gaps are.
• Mid-level: guidance narrows to the specific domains where judgment hasn't been tested yet, while proven domains get real autonomy.
• Senior: guidance becomes mostly about the highest-blast-radius decisions (irreversible changes, cross-team commitments) rather than day-to-day execution, and the structural safeguards that remain exist because the stakes are higher, not because trust is lower.

Worked example
A mid-level engineer had strong judgment in their core service but hadn't touched the deployment pipeline before. Rather than a blanket "you need approval on everything" or "you're trusted, go ahead," the guidance was scoped to that specific gap: full autonomy on their usual work, a mandatory review plus a feature flag for anything touching the deploy pipeline, with an explicit criterion stated up front (three pipeline changes reviewed cleanly, then the mandatory review comes off for that category specifically). That made the guardrail feel like a scoped, temporary compensation for an actual gap rather than a general judgment about their competence, and removing it was a specific, visible moment rather than something that just quietly happened.

Trade-offs and pitfalls

• Treating autonomy as all-or-nothing per person, rather than per domain, either over-restricts someone who's earned trust in most areas or over-extends them into an area they haven't proven yet.
• Relying purely on personal judgment about who to trust, without structural backstops (review gates, flags, automated checks), doesn't scale past a small team and creates inconsistency that reads as favoritism.
• Leaving the criteria for regaining autonomy vague turns a guardrail into something that feels indefinite and punitive, even when it was scoped and reasonable at the start.