Google Data Engineer Interview Preparation Guide - Staff Level

Direct answer

Running dbt transformations inside Airflow across dev, staging, and prod comes down to three coordination problems: making the same DAG definition behave differently per environment without duplicating it, keeping concurrent runs (a scheduled prod run and a developer's manual staging run, say) from stepping on each other, and sequencing dbt models correctly alongside the non-dbt tasks (file ingestion, for example) and schema migrations they actually depend on, since dbt's own internal dependency graph only covers dbt models, not the ingestion step that has to land raw data before dbt can transform it.

Structured elaboration

Environment-specific variables. Parameterize the DAG with an environment identifier (dev, staging, prod) passed at the Airflow connection or Airflow Variable level, not hardcoded into the DAG file, and have the dbt invocation select its target (dbt run --target {env}) from dbt's own profiles.yml, which maps each named target to its own connection details. One DAG definition then serves all three environments; only the resolved variables differ per run, which avoids maintaining three near-duplicate DAG files that inevitably drift apart.

Concurrent runs. Give each environment its own Airflow pool (or its own dedicated set of dbt-invoking tasks with environment-scoped concurrency limits), so a long-running prod run and a developer's ad hoc staging run never compete for the same worker slots, and, more importantly, so dbt's own internal state (its manifest, its run results written to the target schema) for one environment is never overwritten mid-run by a concurrent run against a different environment's schema.

Dependency management between dbt models and non-dbt tasks. dbt's own dependency graph (built from ref() calls between models) only covers dbt models; it has no visibility into an upstream file-ingestion task that has to land raw source data before any dbt model can read it. Model that ingestion step as its own Airflow task, upstream of the dbt run task, with an explicit Airflow dependency edge (ingest_files >> dbt_run), so Airflow's dependency graph is the one true source of truth for "must happen before," spanning both dbt and non-dbt work, rather than expecting dbt's own graph to somehow know about a task outside of it.

Schema migrations requiring coordination with data producers. A schema change on an upstream source table (a data producer team adding or renaming a column) can break a dbt model that references it. Coordinate this the same way any cross-team contract needs coordinating: a data producer proposing a schema change signals it in advance (a pull request against the dbt project's source definitions, for instance, or a shared schema-registry entry), dbt's own source freshness and schema tests catch a mismatch as a failing test run rather than a silent wrong result, and the DAG's own failure-handling routes that test failure to an alert naming the specific model and the specific upstream source, not a generic "dbt run failed."

Worked example

The DAG uses an Airflow Variable target_env resolved per run. For a scheduled prod run, target_env=prod; the dbt task executes dbt run --target prod --profiles-dir /opt/dbt, referencing a prod target block in profiles.yml that points at the production warehouse schema. For a developer's manual staging run of the same DAG, triggered with a run configuration overriding target_env=staging, the exact same DAG and the exact same dbt project run instead against the staging target block, writing to a separate staging schema, with no code change required, only the run configuration differs.

The DAG's dependency chain is ingest_raw_files >> dbt_run (which internally sequences staging models, then mart models, per dbt's own ref() graph) >> dbt_test >> notify_on_failure (only if dbt_test fails). If a data producer renames a source column without warning, dbt_test catches it as a failing schema test for the specific downstream model that referenced the old column name, and notify_on_failure pages the owning team with that model's name in the message, rather than a downstream business-intelligence dashboard silently rendering a null column with no alert at all.

Trade-offs and pitfalls

Parameterizing environments through a single DAG definition is more maintainable long-term, but it means a bug in the shared DAG logic affects all three environments at once; a genuinely risky change to the DAG itself, not the dbt models it invokes, deserves its own staged rollout discipline, the same as any other shared infrastructure change.

Environment-scoped pools prevent concurrent runs from clobbering each other's dbt state, but they do not by themselves prevent a developer's staging run from reading stale upstream data if the staging environment's own ingestion has not run recently; staging environments commonly lag production data freshness, and that gap should be visible, not silently assumed away.

Relying solely on dbt's own tests to catch upstream schema drift means the failure is only caught after the fact, when the scheduled run actually executes; a producer team that wants earlier warning needs a separate, proactive schema-change-review process, since dbt tests are a safety net for this specific pipeline, not a substitute for the upstream coordination itself.

Approach
I would use a recursive CTE. A recursive CTE is a query that repeatedly joins a result back to the base table, which is a standard way to walk a tree. I would start from each category, climb to its parent, and carry a path string plus a cycle check so bad data cannot recurse forever.

WITH RECURSIVE ancestry AS (
  SELECT
    c.category_id,
    c.category_id AS ancestor_id,
    c.parent_id,
    0 AS depth,
    CAST('>' || c.category_id || '>' AS text) AS path
  FROM categories c

  UNION ALL

  SELECT
    a.category_id,
    p.category_id AS ancestor_id,
    p.parent_id,
    a.depth + 1,
    a.path || p.category_id || '>'
  FROM ancestry a
  JOIN categories p
    ON p.category_id = a.parent_id
  WHERE POSITION('>' || p.category_id || '>' IN a.path) = 0
)
SELECT *
FROM ancestry;

How I would use it

• Build the full ancestor path by grouping the rows for each category_id.
• Roll sales up by joining facts to category_id, then summing again by ancestor_id.
• Exclude or quarantine orphans, where a parent is missing.
• Add a max-depth guard if the warehouse supports it.

Worked example
For Shoes -> Apparel -> Root, the CTE emits three rows with depths 0, 1, 2. That gives a safe path and lets sales from Shoes roll up to Apparel and Root without double counting.

Direct answer

Match the line against a regex anchored on the expected shape (timestamp, level, message as named groups), then convert the timestamp string to a real datetime inside a try/except. If either the regex fails to match or the timestamp string is syntactically date-shaped but not a real date, the parser returns None (or is skipped by the caller) instead of raising, so one bad line never takes down a batch job.

Structured elaboration

Robust line parsing needs two independent layers of defense, because "looks like a timestamp" and "is a valid timestamp" are different checks:

1. Structural match (regex). Anchor the pattern with ^/$ and use named groups so the caller gets a dict-like result, not positional groups it has to remember the order of. This layer rejects lines that do not have the TIMESTAMP - LEVEL - MESSAGE shape at all (garbage lines, truncated writes, stack traces that got interleaved into the log).
2. Semantic validation (real date/time construction). A string like 2024-13-40T99:99:99Z can satisfy a naive \d{4}-\d{2}-\d{2}... regex while being calendar-nonsense. Only constructing the actual datetime (via datetime.strptime or datetime.fromisoformat) and catching ValueError confirms the value is real.

For the timestamp format itself (2024-11-02T13:45:30Z), this is ISO 8601 with a trailing Z meaning UTC. Since Python 3.11, datetime.fromisoformat understands this directly, so datetime.strptime(ts, "%Y-%m-%dT%H:%M:%SZ") and datetime.fromisoformat(ts) are both valid choices; fromisoformat is less to type but only handles ISO 8601 variants, while strptime handles arbitrary formats if the log format ever drifts (e.g. no Z, a different separator).

"Robust to lines that don't match" means: never let one malformed line raise and crash the whole parse. A generator that silently skips bad lines (or, in a stricter pipeline, yields a sentinel/logs the raw line for later inspection) keeps the stream flowing.

Worked example

import re
from datetime import datetime, timezone

LOG_RE = re.compile(
    r'^(?P<timestamp>\d{4}-\d{2}-\d{2}T\d{2}:\d{2}:\d{2}Z)'
    r'\s*-\s*(?P<level>[A-Za-z]+)\s*-\s*(?P<message>.*)$'
)

def parse_log_line(line: str) -> dict | None:
    m = LOG_RE.match(line.rstrip("\n"))
    if m is None:
        return None
    try:
        ts = datetime.strptime(m.group("timestamp"), "%Y-%m-%dT%H:%M:%SZ").replace(
            tzinfo=timezone.utc
        )
    except ValueError:
        return None
    return {"timestamp": ts, "level": m.group("level").upper(), "message": m.group("message")}

def parse_log_lines(lines):
    for raw in lines:
        record = parse_log_line(raw)
        if record is not None:
            yield record

sample = [
    "2024-11-02T13:45:30Z - ERROR - failed to load model\n",
    "not a log line at all\n",
    "2024-13-40T99:99:99Z - ERROR - bad calendar values\n",
    "2024-11-02T13:45:32Z - WARNING - retrying - attempt 2\n",
]
for rec in parse_log_lines(sample):
    print(rec)

Output:

{'timestamp': datetime.datetime(2024, 11, 2, 13, 45, 30, tzinfo=datetime.timezone.utc), 'level': 'ERROR', 'message': 'failed to load model'}
{'timestamp': datetime.datetime(2024, 11, 2, 13, 45, 32, tzinfo=datetime.timezone.utc), 'level': 'WARNING', 'message': 'retrying - attempt 2'}

The garbage line and the calendar-invalid line are both silently dropped: the first fails the regex, the second passes the regex (digits in range for \d{2}) but fails strptime with ValueError on the impossible month/hour values.

Trade-offs & pitfalls

• Silent skip vs. logging the reject. Dropping malformed lines quietly is fine for a quick script; in a production pipeline you usually want to count or log rejected lines (a dead-letter list) so a sudden format change upstream is noticed, not just silently absorbed forever.
• Regex range-limiting is not validation. It is tempting to write [0-1]\d for the hour to reject 99, but chasing every calendar edge case in the regex (leap years, days-per-month) duplicates what datetime already does correctly. Let the regex do structural shape-matching and let datetime do calendar correctness.
• .* for the message is greedy but safe here because it is the last group anchored to end-of-line; it will happily swallow embedded - sequences in the message itself (as shown by the "retrying - attempt 2" example), which is usually the desired behavior since the message shouldn't be split further.
• Common wrong turn: matching level with a fixed set like (ERROR|WARNING|INFO) instead of [A-Za-z]+. That breaks the moment a new level (DEBUG, CRITICAL, a custom level) appears upstream, silently rejecting valid lines instead of gracefully passing the level through.

Direct answer. SELECT * pulls every column regardless of what the query actually needs, which increases network transfer, defeats the possibility of an index-only scan (since the index almost never contains every column), and silently breaks if the table's column set changes; rewrite it to name only the columns the caller actually uses.

Structured elaboration. Three distinct costs stack up. First, I/O and network: every extra column is extra bytes read from storage and sent over the wire, even for columns the caller immediately discards, which matters most for wide tables or ones with large text/JSON columns. Second, index eligibility: an index-only scan requires every needed column to be present in the index; asking for every column in the table makes that essentially impossible for any index narrower than the full row, forcing a heap visit that a narrower SELECT might have avoided. Third, fragility: if the table gains a column later, every SELECT * consumer starts receiving it whether or not it's ready to, which has broken more than one downstream integration in ways that are hard to trace back to the schema change that caused it.

Worked example. For transactions(transaction_id, user_id, amount, currency, created_at, status, metadata jsonb), a dashboard that only needs the four most recent completed transactions' amount and date has no business fetching the metadata JSONB column at all:

-- anti-pattern: pulls every column, including a large JSONB payload
SELECT * FROM transactions
WHERE status = 'completed'
ORDER BY created_at DESC
LIMIT 100;

-- production-safe: only the columns the caller actually uses
SELECT transaction_id, amount, created_at
FROM transactions
WHERE status = 'completed'
ORDER BY created_at DESC
LIMIT 100;

The rewrite reduces network payload substantially (dropping metadata, currency, status, and user_id from the wire format) and makes it possible, if status and created_at were part of a covering index that also included transaction_id and amount, for the query to be served entirely from that index.

Trade-offs and pitfalls. Naming columns explicitly is marginally more code to write and to keep in sync as requirements change, which is the entire reason SELECT * remains tempting; treat that maintenance cost as strictly smaller than the recurring, compounding cost of over-fetching on every single execution of a query that runs often.

Complexity

The change doesn't alter the query's algorithmic shape; it changes the constant factor on I/O and network transfer per row, and can change whether an index-only path is even available at all.

Edge cases

A table with a genuinely small number of columns, all of which the caller uses anyway, gets little practical benefit from this rewrite; the cost matters most on wide tables or ones with large variable-length columns like JSON or text blobs.

Direct answer
Give the listener a short roadmap up front (what you're about to walk through and in how many steps), narrate one idea at a time in order, and periodically restate where you are relative to that roadmap, rather than free-associating through your thought process.

Structured elaboration

1. State the roadmap before diving in: "There are two things going on here: first the root cause, then the fix I'd propose. Let me start with the root cause." This gives the listener a mental container to place what follows.
2. Narrate conclusions and reasons, not raw stream-of-consciousness. Say what you're checking and why, not just what you're doing: "I'm checking the logs because I suspect this is a timeout, not a crash," rather than silently scrolling and occasionally muttering.
3. Signal transitions explicitly: "okay, that rules out X, so now let's look at Y," so the listener can track your position in the reasoning instead of having to reconstruct it after the fact.
4. Pause at natural checkpoints to check the listener is still following, especially before switching to a new sub-problem, rather than only checking in at the very end.
5. Name your assumptions out loud as you make them, since an unstated assumption is invisible to the listener and, if wrong, can make the rest of your reasoning look wrong for a reason they can't see.

Worked example
Unstructured: "Okay so let me look at this... hmm... yeah so there's this function... wait, let me check something else... okay so actually I think the issue might be... let's see... yeah I think it's the caching."

Structured: "I'm going to check three possible causes in order of likelihood: caching, a race condition, or a bad config value. Starting with caching, since it's the most common cause of this symptom... [checks] ...that rules out caching, the values are fresh. Moving to the race condition..."

The second version gives the listener the plan up front, tells them which hypothesis is being tested and why, and explicitly states when a hypothesis is ruled out, so they can follow the reasoning instead of just watching an unexplained sequence of actions.

Trade-offs and pitfalls

• Over-narrating every micro-step can slow you down and annoy a listener who just wants the conclusion; calibrate the level of narration to whether the audience needs to follow the reasoning (an interview, a mentoring session) or just wants the answer (a peer who trusts you and is short on time).
• It's easy to silently switch approaches mid-thought without saying so; if you change direction, say so explicitly ("actually, let me back up") rather than leaving the listener to notice on their own.
• This is a skill that degrades under real pressure or unfamiliar problems; it's worth practicing the "state the roadmap first" habit specifically, since it's the cheapest part to do consistently even when the rest of your thinking is genuinely uncertain.

Direct answer. When ingestion p99 latency jumps sharply overnight while throughput stays roughly flat, the priority is a fast, structured triage that restores the SLA with a safe mitigation before root-causing fully, because a latency spike under load is exactly the situation where slow, exploratory debugging risks turning a degradation into an outage.

Structured elaboration.

1. Confirm the shape of the problem first. Throughput flat + latency up (not down) rules out a simple capacity shortfall (which would show throughput dropping too) and points toward a specific stage getting slower per-record, or a queue building up somewhere downstream that is not yet visible in the top-line throughput number.
2. Check the four classic causes in parallel, not sequentially, given the urgency: CPU saturation/GC (host and JVM/runtime metrics), network (NIC utilization, cross-AZ/region latency), disk I/O (iowait, queue depth), and backpressure from a downstream sink (consumer lag, sink write latency). A shared on-call dashboard covering all four at once is the right tool here, not a one-at-a-time investigation.
3. Check what changed. A latency jump "overnight" strongly suggests a discrete trigger: a deploy, a config change, a scheduled batch job starting to compete for the same cluster, an upstream schema or volume change, or a certificate/DNS/infra event. Correlate the exact timestamp of the jump against deploy and change logs before assuming an organic capacity issue.
4. Apply a safe, reversible mitigation while root-causing continues. Options in rough order of safety: scale out consumers/workers if the bottleneck looks compute-bound; temporarily relax a non-critical batching/flush interval to trade a little latency for stability; if a specific downstream sink is the slow point, apply backpressure or shed non-critical traffic rather than letting the queue grow unbounded.
5. Watch for correctness side effects, not just the latency number. A p99 spike that also produces duplicate records at the consumer usually indicates retries firing under load (a request timing out and being retried while the original still completes) -- if so, the mitigation must address the retry/timeout tuning, not just the raw latency, or the fix will mask the symptom while leaving duplicate writes in place.

Worked example. p99 end-to-end latency moves from 200ms to 2s with throughput unchanged. The on-call dashboard shows CPU and network are normal, but disk iowait on the stream-processor hosts has tripled starting at the exact minute a separate nightly compaction job began running on the same storage volumes. The mitigation is to either move the compaction job to a different time window or a separate storage tier, immediately restoring the prior latency profile, while the longer-term fix (isolating batch and streaming I/O onto separate volumes, or scheduling compaction to avoid the peak window) is tracked as a follow-up.

Trade-offs & pitfalls. The instinct to "restore the SLA first, understand later" is correct under time pressure, but any mitigation that involves shedding or dropping data (rather than just adding capacity or removing a competing workload) needs an explicit, stated data-loss trade-off communicated to stakeholders, not applied silently. If duplicate records appear alongside the latency spike, resist the temptation to treat them as a separate, lower-priority issue -- they are frequently a symptom of the SAME root cause (retries under load) and will often disappear once the true bottleneck is fixed rather than needing a separate deduplication effort.

Direct answer

The two-pointer technique replaces a nested loop (checking every pair, which costs $O(n^2)$) with two indices moving through the data in one linear pass. There are two common shapes: pointers starting at opposite ends and moving toward each other, useful when you are looking for a pair with some combined property on sorted data, and pointers moving in the same direction at different speeds, useful when you are compacting or scanning data in place. Both work because sorted order (or some other structural guarantee) lets you rule out a whole range of possibilities with a single comparison, instead of checking each pair individually.

Structured elaboration

Converging pointers (opposite ends, moving inward): on a sorted array, if you are looking for two elements that sum to a target, start left at index 0 and right at the last index. If the current sum is too small, left must move right, since every element left of it is even smaller; if the sum is too large, right must move left, for the mirror reason. Every step rules out one candidate for good, so the whole array is covered in $O(n)$ steps instead of $O(n^2)$ pairs.

Same-direction pointers, different speeds: here both pointers move forward, but one (call it slow) only advances when it finds a genuinely new value to keep, while the other (fast) scans ahead over everything. This shape shows up when compacting a sorted array to remove duplicates in place, or in cycle detection on a linked list, where a slow pointer advances one node at a time and a fast pointer advances two, so the fast pointer laps the slow one if and only if there is a cycle.

Worked example

Converging pointers, sorted-array two-sum:

def two_sum_sorted(arr: list[int], target: int) -> tuple[int, int]:
    left, right = 0, len(arr) - 1
    while left < right:
        s = arr[left] + arr[right]
        if s == target:
            return left, right
        if s < target:
            left += 1
        else:
            right -= 1
    return -1, -1

nums = [2, 7, 11, 15]
print(two_sum_sorted(nums, 9))

Running this prints:

(0, 1)

arr[0] + arr[1] = 2 + 7 = 9, found on the very first comparison.

Same-direction, different speeds, removing duplicates from a sorted array in place:

def dedupe_sorted_in_place(arr: list[int]) -> int:
    if not arr:
        return 0
    slow = 0
    for fast in range(1, len(arr)):
        if arr[fast] != arr[slow]:
            slow += 1
            arr[slow] = arr[fast]
    return slow + 1

data = [1, 1, 2, 2, 2, 3, 4, 4]
new_len = dedupe_sorted_in_place(data)
print(new_len, data[:new_len])

Running this prints:

4 [1, 2, 3, 4]

slow only advances (and writes) when fast finds a value different from what slow last kept, so the array is compacted to its four distinct values in one forward pass.

Trade-offs & pitfalls

Two-pointer techniques generally require the input to already have exploitable structure, most often sorted order; on an unsorted array, you either sort first ($O(n \log n)$ time, then two pointers in $O(n)$, for $O(n \log n)$ total) or use a hash set to find complements in one pass ($O(n)$ time, $O(n)$ extra space), trading the sort's time cost for the hash set's memory cost. A common mistake with the converging pattern is moving the wrong pointer, for example moving right when the sum is too small; since arr[right] is already the largest remaining candidate, only advancing left can plausibly increase the sum. The same-direction pattern generalizes into the sliding window (a window defined by two same-direction pointers that expands and contracts to maintain some invariant, such as "at most k distinct values"), which is worth naming as a related but distinct technique: the window's two edges are still two pointers moving the same direction, but both may move independently rather than one always chasing the other.

Edge cases

• Empty array: two_sum_sorted([], target) sets left=0, right=-1, so left < right is already false and the loop body never runs, returning (-1, -1); dedupe_sorted_in_place([]) hits its explicit if not arr: return 0 guard.
• Single element: two_sum_sorted([x], target) sets left=right=0, so left < right is false immediately, correctly returning (-1, -1) since one element cannot pair with itself; dedupe_sorted_in_place([x]) has range(1, 1) empty, so the loop never runs and it returns 1 (the single element is already deduplicated).
• No match: when no pair sums to target, left and right keep converging (one moves per step) until left == right, at which point the loop exits and (-1, -1) is returned, without ever needing a separate "not found" check.

Direct answer

This shows up because pandas cannot always tell, once you have filtered a DataFrame down to a subset, whether that subset shares memory with the original DataFrame (a "view") or is an independent block of memory (a "copy"). Which one you get depends on the exact operation and the internal memory layout, it is a pandas implementation detail, not something your code controls. When you then assign into that subset, pandas historically had to guess whether you meant to edit the original data too. On pandas 3.0, Copy-on-Write (CoW) removes the guesswork by making every indexing result behave as independent for write purposes, but that changes what the old textbook example actually does when you run it today, which is worth walking through precisely.

Why this used to be a silent correctness trap, not just a noisy warning

The problem was never the warning itself, it was that the outcome silently depended on which one pandas happened to pick:

• If the subset happened to be a view, editing it also edited the original DataFrame, even though you may only have meant to change your local copy.
• If the subset happened to be a copy (the more common outcome for boolean-mask filtering), your edit only landed on that temporary object and was discarded once it went out of scope. The original DataFrame stayed untouched, with no error, the code ran, it just did not do what you thought.

Concrete example:

import pandas as pd

df = pd.DataFrame({'a': [1, 2, 3], 'b': [10, 20, 30]})
subset = df[df['a'] > 1]        # rows where a=2 and a=3
subset['b'] = subset['b'] * 2   # pre-2.0: often a SettingWithCopyWarning; pandas 3.0: silent, no warning

print(subset['b'].tolist())     # looks correct in isolation
print(df['b'].tolist())         # df was never actually updated

Verified output on pandas 3.0.3:

[40, 60]
[10, 20, 30]

subset['b'] shows [40, 60], df['b'] is still [10, 20, 30]. This is exactly the silent-trap outcome: the code runs cleanly and subset looks right, so nothing draws your attention to the fact that df was never touched.

What changed under Copy-on-Write, and what did not

A version note worth knowing precisely: this exact demo behaves differently depending on your pandas version, but the values it produces do not. Copy-on-Write became opt-in in pandas 2.0 and mandatory in pandas 3.0 (it can no longer be disabled), and under Copy-on-Write, pandas.errors.SettingWithCopyWarning has been removed from the library entirely. Running the exact code above on pandas 3.0.3 (verified in sandbox) still prints subset['b'] as [40, 60] and df['b'] as [10, 20, 30], the same silently-unchanged result as always, but with no warning at all now, not even a noisy one. Copy-on-Write closes the loophole structurally rather than just flagging it: every filter or slice is now treated as copy-on-write, so a write through a chained reference simply never reaches the original, deterministically, instead of pandas guessing and warning about it. The mechanism underneath (chained indexing being two separate operations, and why .loc or an explicit .copy() fixes it) is still exactly right, and still what you will run into in any pre-2.0 codebase still in production, you just cannot rely on a warning to catch it on current pandas, which is one more reason to make .loc-based writes a habit rather than something a warning will flag for you after the fact.

The underlying bug is chained indexing: subset['b'] = ... is really two separate operations under the hood, first df.__getitem__ produces subset, then a later __setitem__ tries to write into it. Because those are two separate steps, pandas cannot guarantee the write propagates back to df even on a version where subset happens to be a view, and the intermediate object can be garbage collected right after, so the edit just vanishes.

Rewriting it to be unambiguously safe, on any pandas version

Do the filter and the assignment as one expression with .loc, directly on df:

df.loc[df['a'] > 1, 'b'] = df.loc[df['a'] > 1, 'b'] * 2
print(df['b'].tolist())

Verified output: [10, 40, 60], df is correctly updated in place. .loc[row_selector, col_selector] = value is a single indexing operation, so pandas always knows you intend to modify df itself, no guessing involved, on pre-2.0 pandas, 2.x with or without Copy-on-Write, and pandas 3.0 alike.

If you genuinely want an independent object to experiment with, one that is not meant to affect df, make that explicit:

subset = df[df['a'] > 1].copy()
subset['b'] *= 2   # safe: subset is a real, independent DataFrame, no ambiguity

Trade-offs and pitfalls

• On pandas versions where SettingWithCopyWarning still exists (pre-2.0, or 2.x with Copy-on-Write not enabled), you can silence it with pd.options.mode.chained_assignment = None, but that only hides the ambiguity, it does not fix it, the silent no-op or accidental view-mutation is still possible underneath. On pandas 3.0+, this option is effectively a no-op, since Copy-on-Write means there is no more SettingWithCopyWarning left to silence.
• Prefer rewriting with .loc (when you intend to modify the original) or an explicit .copy() (when you intend an independent object) on any pandas version, rather than depending on version-specific warning behavior to catch the mistake for you.
• The version-dependent framing matters for interviews specifically: candidates who learned pandas from material written before 2.0 will describe a SettingWithCopyWarning that current pandas will never show them for this exact code, stating the mechanism (chained indexing loses the write) without pinning it to "you'll see a warning" is the version-safe way to answer this.

Owning a small ETL/ELT pipeline end to end for the first time is mostly about establishing ground truth before touching anything, then closing the gaps you find in priority order.

Weeks 1 to 2: understand before you change anything
Read the pipeline's code and current run history. Answer three questions before doing anything else: what does it actually compute (not what the name suggests), who consumes the output and for what decisions, and what's the current failure behavior (does it alert anyone, does it retry, does it silently produce stale data on failure). This phase often surfaces the first real risk: an undocumented pipeline someone else built and left, where the biggest early win is just writing down what it does.

Weeks 3 to 4: instrument and stabilize
Add or fix the basics that let you know the pipeline is healthy without checking manually: a freshness check, a row-count sanity check against a rolling baseline, and an alert if the job fails or its output looks anomalous. Fix any known-broken behavior found in the discovery phase (an alert that fires into a channel nobody reads, a retry policy that silently gives up after one attempt).

Weeks 5 to 6: define success and plan the handoff
Define concrete success metrics: data freshness (how stale can the output be before it's a problem), error rate (what fraction of runs fail or need manual intervention), and run time (is it trending up as data volume grows). Document a runbook for the two or three most likely failure modes you found in discovery, and decide how ownership will continue after this initial six weeks: full-time ownership by you, a rotation across the team, or a handoff to an operations function, with the runbook and the instrumentation from weeks 3 and 4 being exactly what makes that handoff possible without you being the only person who understands the pipeline.

Risk mitigation throughout: treat every change as reversible until proven otherwise. Land instrumentation and alerting before touching the transformation logic itself, so you have visibility into whether a later change actually helped or quietly made things worse.

The habit that actually distinguishes a strong answer here: naming that the biggest risk in "owning a small pipeline" usually isn't the pipeline's code, it's that a single person becomes a silent single point of failure for something the business depends on. The six-week plan above is deliberately structured so that by the end, the pipeline's health is visible to more than one person and the runbook exists independent of whoever wrote it.

Direct answer: Compute each user's rank with RANK() so tied scores share a rank, then paginate by a cursor on the score value itself (keyset pagination), and expand the page past its nominal size whenever the cut-off score is tied with rows just past it. A plain cursor without that lookahead has the same failure as OFFSET/LIMIT: if you take exactly page_size rows below the cursor, a tie sitting right at the cut can still get split, and because the next page's cursor becomes "everything strictly below the last score shown," the split-off row is skipped by every later page too, not just misplaced. The fix is to look one step past the nominal page size, find the score value sitting there, and pull in every row tied with it, so the page can grow past page_size but a tie group is never divided across pages.

Approach

-- naive OFFSET/LIMIT pagination (breaks on ties)
SELECT user_id, score,
  RANK() OVER (ORDER BY score DESC) AS rnk
FROM scores
ORDER BY score DESC, user_id
LIMIT 3 OFFSET 3;

-- keyset pagination with boundary-tie lookahead: the page includes every
-- row tied with the row sitting at the page_size cut-off, so the page can
-- grow past page_size but a tie group is never split across pages
WITH ranked AS (
  SELECT user_id, score,
    RANK() OVER (ORDER BY score DESC) AS rnk
  FROM scores
  WHERE score < :last_score_cursor   -- omit this predicate for the first page
),
boundary AS (
  SELECT MIN(score) AS boundary_score
  FROM (SELECT score FROM ranked ORDER BY score DESC LIMIT :page_size) t
)
SELECT r.user_id, r.score, r.rnk
FROM ranked r, boundary b
WHERE r.score >= b.boundary_score
ORDER BY r.score DESC;

The boundary CTE finds the score value sitting at the page_size-th position (or, if fewer rows remain than page_size, the lowest score still available). The outer query then returns every row scoring at least that high, which pulls in any rows tied with the one at the cut point, even though that pushes the page past page_size. The next page's cursor is the lowest score value actually returned on the current page, so its WHERE score < :cursor predicate always starts at the next strictly lower distinct score, never mid-tie.

Key points

• RANK() (not ROW_NUMBER()) is what makes tied scores actually show the same rank; ROW_NUMBER() would silently assign a different number to each tied user, hiding the tie from the leaderboard's display.
• The cursor for the next page is the lowest score value returned on the current page, not a row offset; the next page's WHERE score < :cursor predicate always starts at the next lower distinct score, never mid-tie.
• A page's row count is a soft target, not an exact one: the boundary CTE is what actually enforces this, by looking at the score sitting at the page_size cut-off and pulling in every row tied with it. Without that lookahead, a bare WHERE score < :cursor ... LIMIT :page_size cursor query has exactly the same tie-splitting failure as OFFSET/LIMIT, and it is worse: once the cursor moves past a split-off row, that row is permanently unreachable, not just displaced to a later page.

Worked example

Verified in DuckDB against scores(user_id, score) = (1,100), (2,90), (3,90), (4,90), (5,80), (6,70), (7,70), (8,60), with page_size = 3:

Naive OFFSET 0 LIMIT 3 (page 1) returns users 1, 2, 3 (scores 100, 90, 90; ranks 1, 2, 2). Naive OFFSET 3 LIMIT 3 (page 2) returns users 4, 5, 6 (scores 90, 80, 70): user 4, tied at score 90 with users 2 and 3, lands on page 2 while its tie group already appeared on page 1. A client rendering "rank 2" once at the bottom of page 1 and again at the top of page 2 has effectively shown the same rank position split across two pages, which is exactly the "tie spans a page boundary" failure. A bare cursor query without the lookahead (WHERE score < :cursor ORDER BY score DESC LIMIT 3, no boundary step) makes it worse: page 1 still returns users 1, 2, 3, and page 2's cursor becomes score < 90, which returns users 5, 6, 7 and never returns user 4 at all. The union of pages 1 and 2 is {1,2,3,5,6,7}, permanently missing user 4.

The boundary-lookahead query fixes this. Page 1 (WHERE predicate omitted, page_size = 3): the boundary CTE looks at the top 3 rows by score (100, 90, 90) and finds boundary_score = 90; the outer query then returns every row with score >= 90, which is users 1, 2, 3, and 4 (scores 100, 90, 90, 90). The page grew from 3 to 4 rows specifically because the tie group at the cut point is kept whole. The next cursor is the lowest score just returned, 90.

Page 2 uses WHERE score < 90. The remaining rows are users 5, 6, 7, 8 (scores 80, 70, 70, 60); the boundary CTE looks at the top 3 of those (80, 70, 70) and finds boundary_score = 70, so the outer query returns users 5, 6, 7 (scores 80, 70, 70): the entire score=70 tie group (users 6, 7) stays together, and the page happens to land at exactly 3 rows this time because the tie group didn't straddle the cut. The next cursor is 70.

Page 3 uses WHERE score < 70 and returns only user 8 (score 60), fewer than page_size because it's the last page. The union across all three pages is {1,2,3,4,5,6,7,8}, the complete table, each user exactly once: no row is lost or duplicated.

Why naive OFFSET/LIMIT breaks down here: OFFSET counts physical output rows, with no concept of which rows are tied on the ranking key; it simply draws a line after N rows regardless of whether row N and row N+1 have identical scores. RANK() computing the display value doesn't change this: the ranking and the pagination mechanism are two separate concerns, and OFFSET/LIMIT operates purely on row position, blind to what RANK() assigned. A bare score-cursor without the boundary lookahead inherits the identical blindness. It just relocates where the cut happens; it still cuts through a tie, and because the cursor only ever moves forward, the row stranded on the wrong side of that cut is gone for good, not merely reordered.

Trade-offs & pitfalls

Keyset pagination with boundary lookahead trades a guaranteed exact page size for correctness on ties: a page can come back larger than requested (occasionally much larger, if a huge number of users are tied on one score), so client code has to treat page_size as a maximum-ish target rather than a hard contract. If a product requirement genuinely needs exactly N rows per page no matter what, the only way to keep that guarantee without splitting ties is to accept ambiguity elsewhere (e.g., breaking ties with a secondary, always-unique column like user_id, and accepting that users with identical scores are shown in an arbitrary but stable order, which is a different trade-off than the one this question asks for). Keyset pagination is also more resilient to new rows arriving between page loads than OFFSET-based pagination: since a new higher score only affects pages earlier than the cursor position, it never reshuffles rows already shown on a page a user has already viewed, avoiding the classic "duplicate or skipped row" bug that plain OFFSET pagination has under concurrent writes, independent of the tie-splitting problem this question focuses on.