Airbnb Senior Data Engineer Interview Preparation Guide (2026)

Direct answer: Aggregate revenue per product, rank within each category with RANK() (not ROW_NUMBER(), so ties share a rank), then filter to rnk <= 3 in an outer query, never inside the same SELECT the window function runs in, since window functions cannot appear in a WHERE clause at all. For the order-of-operations bug: a window function evaluates after FROM/JOIN, WHERE, GROUP BY, and HAVING have already run, so any filter placed in that earlier WHERE clause has already shrunk (or changed) the rowset the window function sees, before the window function's own partition and ranking logic gets to run over what's left. If the intent was "rank across everyone, but only display a subset," that filter has to move to an outer query wrapped around the ranking, applied after the rank is computed, not before.

Approach: top-3-per-category with ties included

WITH product_revenue AS (
  SELECT p.product_id, p.category_id, SUM(s.revenue) AS total_revenue
  FROM products p
  JOIN sales s ON s.product_id = p.product_id
  GROUP BY p.product_id, p.category_id
),
ranked AS (
  SELECT *, RANK() OVER (PARTITION BY category_id ORDER BY total_revenue DESC) AS rnk
  FROM product_revenue
)
SELECT * FROM ranked WHERE rnk <= 3
ORDER BY category_id, rnk;

Key points

• RANK() is required, not ROW_NUMBER(): it's what lets a tie at the 3rd-place cutoff produce 4 (or more) output rows for that category instead of arbitrarily dropping one of the tied products.
• The rnk <= 3 filter has to be a WHERE on the outer query reading from the ranked common table expression (CTE, a named subquery written with WITH ... AS (...)), not folded into the same SELECT as the RANK() call; attempting WHERE RANK() OVER (...) <= 3 in the same query fails outright, since window functions are evaluated after WHERE and cannot be referenced by it.
• Verified in DuckDB against a category with revenues 100, 90, 80, 80, 10 (a genuine tie at the 3rd-place cutoff): ranks come out 1, 2, 3, 3, 5, and rnk <= 3 correctly returns 4 rows, not 3, since both rank-3 products are included.

Confirming the hard WHERE-with-window-function error directly: running ... WHERE RANK() OVER (PARTITION BY category_id ORDER BY total_revenue DESC) <= 3 in the same SELECT raises Binder Error: WHERE clause cannot contain window functions! in DuckDB (Postgres and most standard-conforming engines reject the same construct for the same reason: the standard's logical query-processing order runs WHERE before the SELECT list, where window functions live, so at the point WHERE is evaluated the window function's result doesn't exist yet).

Approach: the subtler order-of-operations bug

This second failure doesn't error; it silently changes which rows the window function's partition sees. Say the intent is "rank sales reps by total company-wide revenue, but only display West-region reps' ranks":

-- WRONG: filters to West BEFORE the window function runs, so RANK() only
-- ever sees West rows and ranks reps against each other, not against the company
SELECT rep_id, region, total_revenue,
  RANK() OVER (ORDER BY total_revenue DESC) AS company_rank
FROM rep_revenue
WHERE region = 'West';

-- CORRECT: rank over the full, unfiltered set first, then filter in an outer query
WITH ranked AS (
  SELECT rep_id, region, total_revenue,
    RANK() OVER (ORDER BY total_revenue DESC) AS company_rank
  FROM rep_revenue
)
SELECT * FROM ranked WHERE region = 'West';

Verified in DuckDB against rep_revenue = (1,West,900), (2,West,500), (3,East,800), (4,East,700), (5,West,300): the wrong version ranks rep 2 as company_rank = 2, since WHERE region = 'West' already removed the two East reps before RANK() ever ran, so it only ever compared West reps against each other. The correct version, which computes RANK() over all 5 reps first and filters afterward, gives rep 2 company_rank = 4 (correctly behind both East reps at 800 and 700), and rep 5 goes from a wrong 3 to a correct 5. Same query shape, same filter, different result, purely because of when the filter runs relative to the window function.

Key points

• This bug never errors: the query executes and returns plausible-looking numbers, which is what makes it dangerous; nothing about the output signals that the ranks were computed against a filtered subset instead of the intended full set.
• The fix is structural, not syntactic: move the filter to an outer query or a later WHERE clause that reads from a CTE or subquery where the window function has already run, so the filter only removes rows after ranking, not before it.
• A related, adjacent issue: if ORDER BY total_revenue DESC alone doesn't fully determine row order (ties, or an equivalent secondary sort key not included), the same query can return a different, equally "valid" ranking on a re-run or across replicas; add a deterministic tiebreaker column (ORDER BY total_revenue DESC, rep_id) so the ranking output is reproducible, independent of whichever physical row order the engine happened to scan in.

Edge cases

• A category (or region) with only one product/rep: ranking and filtering both degenerate correctly to that single row; no special-casing needed.
• Filtering that's actually intended to happen before the window function (e.g., WHERE order_status = 'completed' to exclude cancelled orders from the revenue base entirely) is completely correct in the same position; the bug only exists when the filter is meant to restrict the display of an already-computed ranking, not the population the ranking is computed over. Getting this distinction right is a requirements question, not a syntax one.

Trade-offs & pitfalls

The common wrong turn is treating "the query runs without error" as proof it's correct; the order-of-operations bug specifically produces plausible, wrong numbers with no error signal at all. When reviewing a ranking query, explicitly ask: is every WHERE/JOIN condition meant to change the population being ranked, or meant to filter the display of an already-computed rank? Any condition in the second category belongs in an outer query, never alongside the window function itself.

Direct answer

A hub stores only a business key and its metadata (never descriptive attributes); a link stores a relationship between hubs; a satellite stores the actual descriptive, time-variant attributes for a hub or a link. The raw vault is insert-only because that is what makes it a faithful, reproducible record of everything ever received: a change becomes a new satellite row with a later load timestamp, not an overwrite of the old one. That buys you an audit trail a dimensional model does not give you by default, plus resilience to source-system change, at the cost of needing a business vault or dimensional layer before an analyst can use it comfortably.

Structured elaboration

Hub. Columns: a hashed business key (customer_hk), the natural business key itself (customer_bk), a load timestamp, and the record source. A hub never holds descriptive attributes like name or email: it exists purely to give every business entity a single, stable identity that every satellite and link can reference.

Link. Represents a relationship or an event that connects two or more hubs (a specific order connecting a customer hub and a product hub, for instance). It stores the hashed keys of the hubs it relates, a load timestamp, and the source. Links are also insert-only: a relationship that no longer holds is not deleted, a new fact about the relationship is layered on top through its own satellite.

Satellite. Stores the descriptive attributes for a hub or a link, tagged with a load timestamp and (once superseded) a load-end timestamp, plus a hash of the tracked attributes so a loader can cheaply detect "did anything actually change" before inserting a new row. A hub can have several satellites split by source system or by how often the attributes change, which is one of Data Vault's genuine advantages over a single wide dimension: a fast-changing "current status" satellite and a slow-changing "demographic" satellite for the same hub do not force each other's load cadence.

Why insert-only, and what it buys you. In a dimensional Type 2 slowly changing dimension, history is preserved too, but the row still lives inside the ONE table that also has to serve current-state lookups, joins to facts, and business intelligence (BI) tooling all at once. A Data Vault satellite's only job is to be a faithful, append-only record of what a specific source said about a specific entity at a specific time: nothing is ever corrected in place, so re-deriving any historical state, or proving to an auditor exactly what the system held on a given date, is a pure read, never a question of whether an UPDATE silently lost something. The cost is that a satellite alone is not a convenient shape for BI: an analyst asking "what is this customer's current email" has to filter for the open-ended row (load_end_dts IS NULL), and a query spanning many hubs and satellites needs several joins that a single conformed dimension would not.

Worked example

Below is a minimal, runnable sketch: one hub, one satellite tracking a customer's email across a change, and the point-in-time query pattern.

CREATE TABLE hub_customer (
    customer_hk   VARCHAR PRIMARY KEY,
    customer_bk   VARCHAR NOT NULL,
    load_dts      TIMESTAMP NOT NULL,
    record_source VARCHAR NOT NULL
);

CREATE TABLE sat_customer_details (
    customer_hk   VARCHAR NOT NULL REFERENCES hub_customer(customer_hk),
    load_dts      TIMESTAMP NOT NULL,
    load_end_dts  TIMESTAMP,          -- NULL = still the current value
    hash_diff     VARCHAR NOT NULL,
    email         VARCHAR,
    record_source VARCHAR NOT NULL,
    PRIMARY KEY (customer_hk, load_dts)
);

INSERT INTO hub_customer VALUES ('hk_c1', 'CUST-100', '2026-01-01', 'crm_system');
-- original email, later superseded (load_end_dts closed once the new row lands)
INSERT INTO sat_customer_details VALUES
  ('hk_c1', '2026-01-01', '2026-03-15', 'hash_v1', 'ada@old-domain.com', 'crm_system');
-- the new email, current (load_end_dts IS NULL)
INSERT INTO sat_customer_details VALUES
  ('hk_c1', '2026-03-15', NULL, 'hash_v2', 'ada@new-domain.com', 'crm_system');

-- what was the email as-of 2026-02-01 (before the change)?
SELECT email FROM sat_customer_details
WHERE customer_hk = 'hk_c1'
  AND load_dts <= TIMESTAMP '2026-02-01'
  AND (load_end_dts IS NULL OR load_end_dts > TIMESTAMP '2026-02-01');

Executed against DuckDB, the "as-of 2026-02-01" query returns ada@old-domain.com, and the identical query with the date changed to 2026-04-01 returns ada@new-domain.com. Nothing was ever overwritten: both rows still exist, and which one answers "what was true then" is purely a matter of the load_dts/load_end_dts filter, which is exactly the auditability property that makes a Data Vault defensible to a regulator asking "prove what you knew, and when."

Trade-offs and pitfalls

A hub/link/satellite model for a regulated organization onboarding several frequently-changing source systems is the right fit specifically because the insert-only satellite gives a complete, provable history per source without ever having to touch an existing hub or fact table when a sixth source system appears; you add a hub, a link, and a satellite, you do not redesign anything that already exists. But it is not the end state: you still need a business vault (derived, conformed, business-rule-applied tables sitting on top of the raw vault) or a dimensional layer built from the vault before an analyst can query it without knowing what a hub or a satellite is. Skipping that layer is the most common real-world mistake: a raw vault handed directly to BI tooling produces correct but nearly unusable multi-join queries, and defeats the entire point of having done the modeling work.

Direct answer

The merge key has to be a value the retry itself can recompute identically: either the source's natural business key combined with a change sequence or event identifier, or a deterministic hash of the record's stable fields, never something regenerated per attempt like a random identifier or the write's own timestamp. A partial write is handled by landing the batch in a staging area first and promoting it into the target with a single atomic merge or replace operation, so the target either reflects the whole batch or none of it, and a crash mid-write leaves nothing to reconcile beyond simply retrying the same staged batch again.

Structured elaboration

What the merge key needs. It must be derivable from the event itself rather than from the write attempt, so a retry computes the exact same key every time; it must be unique per logical record (a business key, or a business key paired with a change sequence number for records that carry multiple versions); and it must exist before the write happens, so it cannot depend on something the target assigns, like an autoincrementing surrogate key.

Options and their failure modes. An idempotency token or event identifier attached upstream is the cleanest, if the upstream can guarantee it. A composite business key (for example customer identifier plus event time plus event type) works without upstream cooperation but can collide if the business key genuinely repeats. A deterministic hash of stable fields is a fallback when neither is available, with a (small) collision risk to weigh against how much precision the use case needs.

Partial-write safety. Never write row-by-row directly into the target with autocommit; land the batch in staging first, then run one transactional merge that promotes it. If the loader dies between "staged" and "merged," the target is untouched, retrying is just re-running the same staging-to-merge path from scratch. If it dies mid-merge on an engine where merge is itself transactional, the engine's own commit semantics mean the merge either fully lands or fully rolls back, so again nothing is left half-applied.

What actually happens on a partial write. Because the merge key is deterministic, re-running an already-committed batch through the same path is a no-op, the merge matches existing rows and updates them to identical values (or skips them), and reprocessing a batch that never committed is now simply its first successful application. No bespoke cleanup step exists or is needed as long as the key is deterministic and the promote step is atomic.

Worked example

A batch of 500,000 events lands in staging keyed by a deterministic dedupe key (a hash of source table, business key, and source log position). The loader crashes after writing 300,000 of the 500,000 rows to staging:

On retry, staging for this batch is fully rewritten (all 500,000 rows, not appended to the partial 300,000), because the batch is treated as one idempotent unit. One merge statement then runs, matching target.dedupe_key = staging.dedupe_key. If a prior, unrelated attempt had actually gotten all 500,000 rows merged into the target before a different failure occurred elsewhere, this retry's merge simply re-matches and no-ops those rows; if the target had nothing from this batch yet, the merge inserts all 500,000. Either way, the target ends with exactly one row per dedupe key for this batch:

Trade-offs & pitfalls

A merge key built from something the target assigns can never be idempotent, because the retry doesn't know what value the previous attempt got. A key that omits ordering information (a bare business key with no version or sequence) means the last writer for that key silently wins; if a retry applies an older payload after a newer one has already landed, the row regresses unless the merge's update condition explicitly compares versions and skips out-of-order writes. At large scale, running the merge against the entire staging set every retry gets expensive; pre-deduplicating staging to one row per key (keeping the latest by ingestion time) before the merge keeps the match step cheap. This matters most concretely in a payments context: a duplicate row there means double-counting a charge or a payout, not just a slightly noisy dashboard number, so the idempotency discipline described here is the difference between a rounding error and a real financial discrepancy.

Direct answer. Treat the backlog as a funnel with six weekly stages. Merge duplicates first, clarify only what survives, give every item a named owner, score with a light rubric, then match the top of the list to real analyst capacity. Everything not funded is published as "not this quarter" with a reason.

Week 1: inventory and de-duplicate. Export all 600 into one sheet. Normalize titles (lowercase them, strip prefixes like "URGENT:" and expand abbreviations, so "Rgn churn rpt" and "Regional churn report" look the same), then group by dataset or dashboard touched and by requester. Merge exact duplicates mechanically and near-duplicates after a quick read. Example of a near-duplicate pair: Sales asks for "weekly churn by region" and Finance asks for "regional churn report, weekly". Same data, same grain, same cadence, so they become one item and both requesters stay listed as stakeholders. Budget: 600 requests at about 3 minutes each is 30 hours of first-pass reading, so split it between two people. Also tag requests that an existing dashboard already answers and close them with a link.

Week 2: clarify. Send each requester a five-field template: the decision this supports, the deadline and why, who uses the result, how often, and the current workaround. Send the template on day one of Week 2, remind non-responders midweek, and close anything still unanswered at the end of that week with "reopen if still needed", so Week 3 starts with only surviving items (a longer wait would push owner assignment and scoring into the weeks reserved for staffing and review). Silence is information about priority.

Week 3: assign owners. Every surviving item gets a requesting business owner (the business-side person who confirms the value and will use the result) and a delivery owner on the data team (the analyst or lead who estimates and builds it). Items nobody will own are closed.

Week 4: score. Use a lightweight RICE-style rubric. RICE is a scoring method: score = Reach x Impact x Confidence / Effort. Map your factors onto it: Reach is people affected, Impact is the value of the decision it supports (0.25 minimal, 0.5 low, 1 medium, 2 high, 3 massive), Confidence is how sure you are of the data and the estimate (100%, 80% or 50%), and Effort is analyst-weeks (one analyst working one full week). A hard deadline or legal need is not scored; it goes straight into a must-do list. Keep scoring to about 10 minutes per item.

Illustrative scoring of three surviving requests:

The dashboard is funded first. The forecast model has high impact but low confidence and high effort, so it goes to the deferred list with a note on what would raise its score.

Week 5: staff. Capacity is the constraint. Illustrative: 4 analysts, 13 weeks, 60% of time on planned projects is 4 x 13 x 0.6 = 31.2 analyst-weeks. Fill the ranked list until that runs out, keeping a reserve (for example 20%) for urgent work. Applying it: 31.2 x 0.8 = 24.96, so plan for about 25 analyst-weeks of ranked work and hold roughly 6 in reserve.

Week 6: review and publish. Present the funded list, the deferred list with reasons, and the closed list to stakeholders, then lock it for the quarter.

Pitfalls. Do not clarify 600 items before de-duplicating; do not score items without owners; do not let the funded list exceed measured capacity.

Direct answer

Three independent, stackable changes cut per-object overhead for millions of small fixed-shape records: replace per-instance __dict__-based classes with __slots__, replace dicts with tuples (or namedtuple) for records whose shape never changes, and replace a Python list of numeric objects with a typed, contiguous buffer (array.array or a numpy array) once the data is uniformly numeric. Each targets a different source of overhead: object-header and dict machinery, per-field boxing, and per-element Python-object indirection, respectively, and the three combine rather than compete.

Structured elaboration

__slots__ removes the per-instance __dict__. By default, every instance of an ordinary class carries its own dict to hold instance attributes; declaring __slots__ tells the class not to create that dict at all, storing attributes in fixed slots on the object itself instead.

class RecordSlots:
    __slots__ = ("id", "value", "flag")
    def __init__(self, id_, value, flag):
        self.id, self.value, self.flag = id_, value, flag

class RecordPlain:
    def __init__(self, id_, value, flag):
        self.id, self.value, self.flag = id_, value, flag

Tuples or namedtuple instead of dicts for a fixed-shape record. A dict pays for a hash table sized for its keys even when the key set never changes; a tuple has no hash table at all, and namedtuple recovers named-field access at a small cost over a raw tuple.

from collections import namedtuple
Rec = namedtuple("Rec", ["id", "value", "flag"])

array.array (or numpy) for a dense numeric column. A Python list of floats stores pointers to individually-boxed float objects; a typed array stores the raw C values contiguously, with no per-element object at all.

from array import array
N = 100_000
values = array("d", (float(i) for i in range(N)))

Worked example

Verified on CPython 3.12. __slots__ versus a plain instance:

import sys
r_plain = RecordPlain(1, 2.0, True)
r_slots = RecordSlots(1, 2.0, True)
total_plain = sys.getsizeof(r_plain) + sys.getsizeof(r_plain.__dict__)
total_slots = sys.getsizeof(r_slots)
print(total_plain, total_slots)

This printed 344 56: a plain instance costs 48 bytes for the object header plus 296 bytes for its three-entry __dict__, versus 56 bytes total with __slots__, roughly a 6x reduction per instance on this record shape.

Python list of floats versus a typed array:

lst = [float(i) for i in range(100_000)]
arr = array("d", (float(i) for i in range(100_000)))
print(sys.getsizeof(1.0) * 100_000, sys.getsizeof(arr))

This printed 2400000 816640: the 100,000 individual Python float objects a list of floats holds cost roughly 24 bytes each (2,400,000 bytes total), beyond the list's own separate pointer array, versus array("d") storing the same values as packed 8-byte C doubles in one contiguous buffer (816,640 bytes, essentially 8 bytes/element plus a small fixed header), roughly a 3x reduction from the object cost alone.

Complexity: both changes are $O(1)$ per-object savings applied $n$ times, so a preprocessing pass over $n$ records that used to allocate $n$ dict-backed objects now allocates $n$ slot-backed objects (or zero per-record Python objects at all, in the array/numpy case), a constant-factor memory win, not a change in the algorithm's asymptotic time complexity. The practical effect for "millions of small objects causing memory spikes" is a proportional drop in peak resident memory, plus less work for the cyclic garbage collector (GC, CPython's mechanism for reclaiming reference cycles) since there are far fewer container objects for it to track.

Trade-offs & pitfalls

• Edge case: __slots__ classes cannot get new attributes assigned dynamically (obj.new_field = 1 raises AttributeError), and by default do not support multiple inheritance from more than one class that also defines non-empty __slots__; a class with __slots__ also loses the default __dict__-based support some serializers assume, so pickling or introspection that expects obj.__dict__ needs adaptation (__getstate__/__setstate__).
• Plain tuples lose named-field readability; namedtuple restores field names at a small overhead versus a raw tuple, usually worth paying unless the record count is extreme and even that small overhead matters.
• Edge case: array.array only holds a single, fixed C type per array ("d" for double, "i" for signed int); a heterogeneous record shape needs one array per field (columnar layout) rather than one array of mixed-type records, a design change, not just a swap.
• Converting existing code from dict-style access (row["value"]) to tuple-index or attribute access touches every call site that reads the record; this migration cost, not the change itself, is worth profiling first (with tracemalloc or sys.getsizeof sweeps across representative object types) to confirm the actual memory hot spot before rewriting broadly.
• Object pooling (reusing pre-allocated objects instead of allocating fresh ones per record) is a fourth lever, but it adds real complexity (resetting stale state between reuses) and is usually not worth it once __slots__ and columnar/array storage are already applied; reach for it only if profiling still shows allocation churn as the bottleneck after the simpler changes.

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
Build the rubric around whether the writing actually works for its reader: does it state its point clearly, fit the audience it's for, give the reader something to do with it, and use a tone appropriate to its purpose, then justify each criterion by what a failure on it costs the reader.

Structured elaboration
Proposed criteria, with the reasoning for each:

1. Clear main point: can a reader state the document's core message in one sentence after reading it? Justification: this is the single biggest failure mode in unclear writing, so it anchors the rubric.
2. Appropriate structure: does the important information come early, with supporting detail after, rather than requiring the reader to read to the end to find the point?
3. Audience fit: is the level of jargon and assumed background knowledge appropriate for who's actually going to read this, rather than written for the author's own level of familiarity?
4. Concision: is there padding, hedging, or restatement that could be cut without losing meaning?
5. Actionable next step: if the document implies an action or a decision, is that action stated explicitly, rather than left for the reader to infer?
6. Precision: are claims specific and checkable, or do vague quantifiers stand in for actual numbers where numbers were available?
7. Tone fit: is the tone appropriate to the stakes and relationship, neither over-casual for a high-stakes audience nor needlessly formal for a quick internal note?
8. Honesty about caveats: does the document surface real limitations or risks, rather than smoothing them over to look cleaner?

Worked example
Applying this to a short vendor-status email: main point ("vendor is delayed two weeks") is clear in the first sentence; structure is fine; audience fit is appropriate (no unnecessary jargon for a business reader); concision is good at three sentences; the next step (approve a revised deadline) is explicitly stated; precision holds (a specific date is given, not "soon"); tone is appropriately direct without being alarmist; and the caveat (a small risk of a further one-week slip) is honestly included rather than hidden. That's 8 for 8, which is a genuinely well-written status update by this rubric.

Trade-offs and pitfalls

• A rubric with too many criteria becomes tedious to apply consistently; six to eight, as here, is usually enough to catch the failure modes that matter most without turning review into a lengthy checklist exercise.
• Some criteria trade off against each other (concision versus caveats, for instance); the rubric should make clear that cutting a genuine caveat to satisfy concision is a failure, not a win, on this rubric.
• A rubric like this works best as a discussion tool during review, not as a rigid pass/fail gate; a document can reasonably fail one criterion (say, tone) for a good reason specific to its context.

Direct answer

LinkedHashMap in ACCESS-ORDER mode is the direct Java tool for this: overriding removeEldestEntry to return true once size exceeds capacity turns it into a bounded LRU cache with O(1) get/put, and using it purely as a "seen set" (values unused, just presence) tracks the most-recently-seen capacity event IDs. Iterate the input once in order, skip any event whose ID is already in the LRU set, otherwise keep it and add its ID; this preserves original order and bounds memory to capacity entries regardless of total input size.

Structured elaboration

Why LinkedHashMap, not a plain HashMap plus a separate queue. LinkedHashMap constructed with accessOrder=true maintains its own internal doubly-linked list of entries in access order, and both get() and put() automatically move the touched entry to the "most recently used" end, giving true LRU semantics (not FIFO) with no extra bookkeeping in the calling code. Overriding removeEldestEntry(eldest) to return size() > capacity after every put lets the map evict its own least-recently-used entry automatically, again O(1), no manual linked-list management needed.

Why order-preserving iteration, not a Set-based filter. The method must return events in ORIGINAL order (a Set alone would lose this), so the implementation iterates the input list once, appending each non-duplicate event to a separate output ArrayList in the order encountered, while the LRU map only tracks WHICH ids have been seen recently, not the output ordering itself.

LRU eviction changing behavior, not just bounding memory. Because eviction is real, a previously-seen ID that ages out of the capacity-sized window is correctly treated as NEW again on a later occurrence, this is not a bug, it is the deliberate, stated trade-off of using a BOUNDED memory structure: perfect, unbounded deduplication is not possible within fixed memory, so old entries must eventually be forgotten, and LRU forgets the LEAST RECENTLY relevant ones first, a reasonable policy for a stream where recent activity is more likely to matter.

Worked example

import java.util.*;

class Event {
    String id; long ts; String payload;
    Event(String id, long ts, String payload) { this.id = id; this.ts = ts; this.payload = payload; }
    public String toString() { return id; }
}

public class Dedup {

    public static List<Event> deduplicate(List<Event> events, int capacity) {
        LinkedHashMap<String, Boolean> seen = new LinkedHashMap<String, Boolean>(capacity, 0.75f, true) {
            protected boolean removeEldestEntry(Map.Entry<String, Boolean> eldest) {
                return size() > capacity;
            }
        };
        List<Event> result = new ArrayList<>();
        for (Event e : events) {
            if (seen.containsKey(e.id)) {
                seen.get(e.id);  // touch: moves to MRU position, but do NOT add to result
                continue;
            }
            seen.put(e.id, Boolean.TRUE);
            result.add(e);
        }
        return result;
    }

    static void assertEquals(Object expected, Object actual, String msg) {
        if (!Objects.equals(expected, actual))
            throw new AssertionError(msg + " -- expected=" + expected + " actual=" + actual);
    }
    static List<String> idsOf(List<Event> events) {
        List<String> ids = new ArrayList<>();
        for (Event e : events) ids.add(e.id);
        return ids;
    }

    public static void main(String[] args) {
        // Test 1: basic dedup, order preserved, first occurrence kept.
        List<Event> input1 = Arrays.asList(
            new Event("a", 1, "p1"), new Event("b", 2, "p2"),
            new Event("a", 3, "p1-dup"), new Event("c", 4, "p3"), new Event("b", 5, "p2-dup"));
        List<Event> out1 = deduplicate(input1, 10);
        System.out.println("Test 1 (basic dedup, capacity=10): " + idsOf(out1));
        assertEquals(Arrays.asList("a", "b", "c"), idsOf(out1), "Test 1 failed");

        // Test 2: capacity SMALLER than distinct IDs, forcing LRU eviction --
        // proves eviction actually changes behavior (evicted ID treated as new).
        List<Event> input2 = Arrays.asList(
            new Event("x1", 1, "p"), new Event("x2", 2, "p"),
            new Event("x3", 3, "p"), new Event("x1", 4, "p-again"));
        List<Event> out2 = deduplicate(input2, 2);
        System.out.println("Test 2 (capacity=2, forces eviction): " + idsOf(out2));
        assertEquals(Arrays.asList("x1", "x2", "x3", "x1"), idsOf(out2), "Test 2 failed");

        // Test 3: negative control -- SAME input, capacity=10 (no eviction),
        // proving x1 IS correctly deduped when capacity is not exceeded.
        List<Event> out3 = deduplicate(input2, 10);
        System.out.println("Test 3 (same input, capacity=10, no eviction): " + idsOf(out3));
        assertEquals(Arrays.asList("x1", "x2", "x3"), idsOf(out3), "Test 3 failed");

        // Test 4: accessing an ID refreshes its LRU position (true LRU, not FIFO).
        List<Event> input4 = Arrays.asList(
            new Event("y1", 1, "p"), new Event("y2", 2, "p"),
            new Event("y1", 3, "p-dup"), new Event("y3", 4, "p"), new Event("y1", 5, "p-dup2"));
        List<Event> out4 = deduplicate(input4, 2);
        System.out.println("Test 4 (LRU refresh-on-access, capacity=2): " + idsOf(out4));
        assertEquals(Arrays.asList("y1", "y2", "y3"), idsOf(out4), "Test 4 failed");

        System.out.println("\nAll assertions passed.");
    }
}

Output (actually compiled with javac and run with java, OpenJDK 17):

Test 1 (basic dedup, capacity=10): [a, b, c]
Test 2 (capacity=2, forces eviction): [x1, x2, x3, x1]
Test 3 (same input, capacity=10, no eviction): [x1, x2, x3]
Test 4 (LRU refresh-on-access, capacity=2): [y1, y2, y3]

All assertions passed.

Test 2 is the deliberately non-vacuous proof for eviction: with capacity=2, x1 gets evicted once x3 arrives (the map is full at {x1, x2}, and x3 evicts the LRU entry, x1), so x1's later reoccurrence is correctly treated as NEW, appearing a second time in the output. Test 3 is the negative control proving this is genuinely eviction-driven, not a bug: the IDENTICAL input with capacity=10 (never full) correctly deduplicates x1 normally, appearing only once. Test 4 proves true LRU (not FIFO) semantics: y1's SECOND appearance (offset 3) refreshes its position, so when y3 arrives and forces an eviction, y2 (now the actual LRU entry) is evicted instead of y1, and y1's third appearance (offset 5) is still correctly recognized as a duplicate.

Complexity

• Time: O(n) for n input events. Each event does one containsKey/get or put on the LinkedHashMap, both O(1) amortized (hash table lookup plus O(1) linked-list-position update for the access-order maintenance).
• Space: O(min(capacity, n)) for the LRU map, plus O(n) for the output list (which, in the worst case of no duplicates, holds all n events); the BOUNDED part of the memory footprint, the part this design exists to control, is exactly the capacity-sized seen-set, independent of total stream length.

Trade-offs and pitfalls

• Common mistake: using a plain HashMap with manual eviction bookkeeping (a separate queue tracking insertion order) instead of LinkedHashMap's built-in access-order mode; this is more code, more places to introduce a bug, and typically no faster, LinkedHashMap already provides exactly this mechanism natively.
• Common mistake: forgetting to call .get() (or otherwise touching the entry) on a detected duplicate. If a duplicate check only does containsKey without an access that updates LRU order, the map degrades toward FIFO- rather than LRU-eviction behavior, since frequently-seen duplicates would not be kept "fresh"; Test 4 above specifically exercises this detail.
• Capacity sizing is a real trade-off, not a default. Too small, and legitimately spaced-apart duplicates (arriving further apart than capacity distinct IDs) are missed (treated as new, per Test 2's deliberate demonstration); too large, and memory use grows without the bound the LRU design exists to provide.
• This design assumes event IDs are the sole correctness signal for "duplicate." If two events could share an ID but represent genuinely different logical events (a reused ID after a long gap), this bare ID-based approach would incorrectly treat them as the same, a compound key (ID plus a version/epoch) would be needed instead.

Direct answer
A mentoring relationship going badly is rarely one big failure; it's usually a slow accumulation of choices, like taking on too much of the work yourself to protect the outcome, that quietly undercut the mentee's growth. The honest answer names a specific relationship, is candid about what you did (not just what the mentee did), and shows what changed in how you mentor afterward.

What "went badly" usually looks like

• Common patterns: being too directive and doing the hard parts yourself to protect delivery; giving feedback too infrequently or too late to be actionable; misjudging the mentee's actual gap (treating a confidence problem as a skill problem, or the reverse); or disengaging when the relationship got effortful.
• A strong answer picks one specific pattern and owns your part in it, rather than a vague "they weren't a good fit."

What separates a senior answer from a junior one

• Junior answers blame the mentee ("they just weren't receptive") or stay abstract ("communication could have been better"). Senior answers identify a decision you made and trace its actual effect: what you did, what it produced, and why it made sense to you at the time even though it was wrong.
• Senior answers also show what changed structurally afterward, not just an apology or a resolution to "communicate better." Concrete changes: an explicit mentoring agreement up front, checkpoints instead of open-ended availability, deliberately handing over ownership even when it's slower.

How to close it out

• End on what you'd do differently now, stated specifically enough that it's clear you'd actually behave differently in the next relationship, not just that you feel bad about the last one.

Worked example
During a stretch project with a hard deadline, I mentored a junior engineer by taking over the riskiest parts myself rather than coaching them through it, to keep the timeline safe. That worked in the short term, but it meant they never built confidence handling ambiguity or incidents on their own, and toward the end of the project they told me directly that they felt sidelined rather than developed. That was the moment it became clear the relationship hadn't done what I'd intended, even though the project itself shipped fine.

What I changed afterward: instead of stepping in when something got risky, I started requiring myself to narrate my reasoning out loud and have the mentee drive, only taking over if there was a genuine, immediate risk. I also set an explicit checkpoint (a short regular sync, not just "come find me") so growth stalls would surface early instead of only becoming visible at the end of a project. The relationship after that wasn't measured by how smoothly the project went; it was measured by whether the mentee could handle the next similar situation without me in the room, which is a slower thing to build but the actual point of mentoring.

Trade-offs and pitfalls

• The tempting failure mode is optimizing for the deliverable (visible and rewarded) at the expense of the mentee's growth (slower and less visible), especially under deadline pressure.
• Being self-critical is necessary but insufficient; an answer that's all remorse with no concrete process change reads as unreflective in a different way.
• Watch for over-correcting into never stepping in, which just replaces one failure mode (too directive) with another (abandoning someone to a mistake they can't yet recover from alone).

Direct answer

Join skew shows up in the Spark UI as a small number of tasks in the join's shuffle stage with dramatically longer duration and dramatically larger shuffle-read size than their siblings, the same straggler-task signature, but specifically localized to a JOIN stage rather than a pure aggregation. To quantify skew BEFORE running the full join, sample both sides' join-key distributions cheaply (a groupBy(key).count() on a fraction of the data, or, more precisely, on the FULL key column alone without the expensive join itself) and compute a concentration statistic (the top key's share of total rows) to decide whether mitigation is needed before committing to the full, expensive run.

Structured elaboration

Spark UI signs of join skew specifically. In the join stage's task list: a small number of tasks with shuffle-read size (and correspondingly, duration) far above the median, the general skew signature; specific to a JOIN (versus a pure aggregation), also check whether the skew is coming from ONE side or BOTH sides of the join (a task fetching a disproportionate amount from the LEFT side's shuffle output versus the RIGHT side's can indicate which side's key distribution is actually the problem, informing which side needs salting or whether a broadcast of the OTHER, non-skewed side is the simpler fix); and disk spill on exactly the same slow tasks, confirming the oversized partition genuinely exceeded execution memory, not just took longer for an unrelated reason.

Job logs. Executor logs for the specific slow tasks may show explicit GC-time elevation (large partitions mean more live objects during the join's build/probe phase) or shuffle-fetch warnings; cross-referencing which HOST ran the slow tasks also rules out a node-level infrastructure cause before concluding the join key itself is skewed.

Programmatic sampling to quantify skew before running the full job. The key insight: computing the KEY DISTRIBUTION is far cheaper than running the actual join, since it needs only ONE side's key column (not both sides, not the full row width, not the join computation itself).

from pyspark.sql import functions as F

def quantify_skew(df, key_col, top_n=10):
    total = df.count()
    key_counts = (df.select(key_col).groupBy(key_col).count()
                    .orderBy(F.desc("count"))
                    .limit(top_n))
    rows = key_counts.collect()
    for r in rows:
        share = r["count"] / total
        print(f"{r[key_col]}: {r['count']} rows, {share:.1%} of total")
    return rows[0]["count"] / total if rows else 0.0

Running quantify_skew(large_side_df, "customer_id") before attempting the full join gives a concrete, cheap-to-compute number (the top key's SHARE of total rows) that can be checked against a threshold (threshold design in more depth) BEFORE committing to the full join's cost; a 40% top-key share is a strong, actionable signal to salt or otherwise mitigate proactively, while a 2% top-key share suggests the join is likely fine as-is and does not need proactive intervention.

Worked example

I ran the quantification approach against a synthetic dataset with the SAME 95%-skewed key used ("HOT" accounting for 950 of 1,000 rows, spread across 6 distinct keys):

quantify_skew(large_df, "key")

Output (actually executed with python3.12 + pyspark 3.5.1 + Java 17, local[2], rebuilding the same 1,000-row/95%-skewed dataset shape described above):

HOT: 950 rows, 95.0% of total
k1: 10 rows, 1.0% of total
k2: 10 rows, 1.0% of total
k3: 10 rows, 1.0% of total
k4: 10 rows, 1.0% of total
k5: 10 rows, 1.0% of total

A 95% top-key share is an extreme, unambiguous signal to mitigate BEFORE running the full join against this key; this is exactly the quantification step that should precede committing to the salting implementation, closing the loop between "detect proactively" (this question) and "mitigate" (the salting question) as two connected steps of one workflow, not two unrelated topics.

Trade-offs and pitfalls

• Common mistake: running df.groupBy(key).count() against the FULL row width (selecting all columns) instead of JUST the key column before aggregating; selecting only the key column first (df.select(key_col).groupBy(key_col).count()) lets Catalyst's projection pruning (the mechanism) avoid reading or shuffling any of the other, unneeded columns, making the sampling check itself meaningfully cheaper than a naive full-row count-by-key.
• Common mistake: sampling too small a FRACTION of the data when the skew is itself RARE-BUT-SEVERE (a key that is uncommon in a small sample but genuinely dominant at full volume); for a key distribution suspected to have this shape, computing the count over the FULL key column (cheap, since it excludes the other columns and the join itself) is more reliable than sampling a fraction, and is usually still far cheaper than running the actual join.
• A top-key-share threshold alone does not distinguish "one moderately hot key" from "many moderately warm keys collectively causing spread-out but real skew"; for the fullest picture, computing the coefficient of variation across ALL key counts (not just the single top key's share) gives a more complete signal than the top-1 share alone, though the top-1 share is a fast, useful FIRST check.
• Diagnosing which SIDE of a join is skewed matters for choosing the right mitigation: if only the large side has a skewed key, salting the large side plus replicating the small side is appropriate; if the "small" side turns out to itself have significant duplication on the join key (a data-quality issue, not the volume asymmetry salting is designed for), the real fix may be upstream deduplication rather than a join-strategy change at all.