Lyft Staff Data Engineer Interview Preparation Guide 2026

Direct answer

A sustained 10,000 writes-per-second, low-latency, stateful workload pushes you away from a design tuned for reads (a single write primary, heavy indexing, read replicas) and toward one built for write scaling: a storage engine optimized for sequential writes, a partitioning scheme that spreads writes across many nodes, and a replication model with an explicit, tunable durability-versus-latency trade-off rather than a single write bottleneck.

Structured elaboration

Why a read-optimized design breaks down here. Traditional B-tree storage engines perform random-access writes and update every index on every insert, each additional index roughly adds another write per record. A single-writer relational primary caps total write throughput at whatever one node's disk and CPU can sustain, and read replicas do nothing for write capacity, they only copy the primary's write stream.

What changes for write-heavy:

• Storage engine: log-structured merge (LSM) tree engines (used by databases like Cassandra, HBase, and the storage layer behind DynamoDB-style stores) append writes sequentially and merge them in the background, trading some read amplification (a single logical read may have to check several separate on-disk files before it can answer, since recent and older writes land in different segments) for much higher sustained write throughput than a B-tree.
• Partitioning: writes are sharded across many nodes by a partition key. The key must be chosen for even cardinality, a monotonically increasing key (like a timestamp or auto-increment ID) concentrates all new writes on one shard regardless of how many nodes exist.
• Replication and durability: instead of one primary with no built-in fan-out, use a quorum-based replication scheme, writes are acknowledged once a majority of replicas confirm, giving a tunable point between "acknowledge on one node" (fast, risks data loss) and "acknowledge on all nodes" (safest, slowest).
• Indexing discipline: keep secondary indexes to the minimum the write path can afford, every index is a write, this is the opposite instinct from a read-heavy design where more indexes are usually free wins.

Worked example

Assume, illustratively, that a single write-optimized node sustains 2,000 writes per second at the target latency.

Nodes needed for raw throughput: $10{,}000 / 2{,}000 = 5$ shards.

For durability, replicate each shard three ways (tolerate one node failure without data loss): $5 \times 3 = 15$ total storage nodes.

A quorum write with $N=3$ replicas and a write quorum of $W=2$ means the client waits only for the second-fastest replica to acknowledge, not the slowest, bounding tail write latency while still guaranteeing the write survives a single node failure.

Cost contrast, provisioned versus per-operation pricing. At an illustrative $0.00001 per write operation under a consumption-priced managed service:

$\text{ops/day} = 10{,}000 \times 86{,}400 = 864{,}000{,}000 \text{ writes/day}$

$\text{daily cost} = 864{,}000{,}000 \times \$0.00001 = \$8{,}640/\text{day} \approx \$259{,}200/\text{month}$

Against 15 provisioned nodes at an illustrative $400/node/month: $15 \times \$400 = \$6{,}000/\text{month}$. At this sustained write rate the per-operation model costs roughly 40 times more, which is why sustained high-volume writes usually favor provisioned or self-managed clusters, and why consumption pricing fits bursty, low-average workloads instead.

Trade-offs & pitfalls

• Carrying over every index from a read-heavy design roughly multiplies write cost by the number of indexes, audit which indexes the write path can actually afford.
• A low-cardinality or monotonically increasing partition key creates a hot shard that caps total throughput no matter how many nodes you add, this is the single most common write-scaling mistake.
• Waiting for all replicas ($W=N$) is the safest durability setting but the slowest; a majority quorum balances safety and latency, the exact quorum size is itself a trade-off decision, not a default.
• High write concurrency needs connection pooling and write batching, naive one-connection-per-request patterns hit connection limits long before they hit the storage engine's real capacity.

flowchart LR
Client --> Router[Write Router]
Router --> ShardA[Shard A Leader]
Router --> ShardB[Shard B Leader]
Router --> ShardC[Shard C Leader]
ShardA --> ShardARep[Shard A Replicas x2]
ShardB --> ShardBRep[Shard B Replicas x2]
ShardC --> ShardCRep[Shard C Replicas x2]

Direct answer. EXCEPT (comparing the new snapshot against the old one) directly returns the rows that are new or changed, which is exactly the set you need to process for an incremental load, without touching or reprocessing anything that stayed the same.

Structured elaboration. EXCEPT compares whole rows, so a row that changed in ANY column between the two snapshots will show up as "different" and get selected, since its full row no longer matches anything in the old snapshot. That's precisely what you want for change detection: you don't need to know WHICH column changed to know THAT the row changed. For null-safety, if any of the compared columns can be NULL, you don't need special handling for EXCEPT itself (most engines already treat two NULLs in the same column position as equal for this row-comparison purpose, unlike an ordinary equality condition), but it's worth explicitly verifying that behavior for your specific engine before relying on it.

Worked example. old_snapshot(id, col1, col2): (1,'a',10), (2,'b',20). new_snapshot(id, col1, col2): (1,'a',10) (unchanged), (2,'b',99) (col2 changed), (3,'c',30) (a brand new row).

SELECT * FROM new_snapshot
EXCEPT
SELECT * FROM old_snapshot
ORDER BY id;

Result: (2, 'b', 99), (3, 'c', 30). Row 1 is correctly excluded (it's identical in both snapshots), while both the genuinely-changed row (2) and the genuinely-new row (3) are returned as needing to be loaded.

Trade-offs and pitfalls. This approach gives you "new or changed" as one undifferentiated set; if your load logic needs to treat inserts and updates differently (an upsert that behaves differently depending on which one it is), you'd typically follow this EXCEPT with a check against the OLD snapshot's key alone to classify each returned row as new (key absent from old_snapshot) versus changed (key present, but the row differs). It's also worth noting EXCEPT alone doesn't tell you about DELETIONS (rows present in old_snapshot but entirely gone from new_snapshot); that's the mirror query, old_snapshot EXCEPT new_snapshot, run separately. Idempotency comes from the fact that re-running this same comparison against the same two snapshots always produces the identical result set, so a retried or repeated load doesn't produce duplicate or inconsistent processing.

The business case starts from a specific, costed pain point, such as recurring incidents from silent schema breaks, not a generic "we should have better data quality." It proposes a narrow initial scope on the highest-incident datasets with a phased rollout rather than a company-wide mandate, and it commits to tracking outcome metrics, like incidents prevented and breaking changes caught before shipping, rather than adoption metrics alone, since teams can technically adopt a tool while still shipping breaking changes around it.

Naming the pain point

Before proposing a tool, establish the cost of the status quo in terms leadership already tracks: count schema-related incidents over the last two or three quarters from an incident tracker or postmortem log, not memory, and for each one estimate the engineering hours spent on detection and remediation plus any business impact, such as a wrong number in a report or a broken model feature. This turns "we should have contracts" into "schema breaks cost us N incidents and roughly M engineer-hours last quarter, concentrated on these five high-fan-out datasets," which is the sentence that gets budget.

Scope and rollout phases

Do not propose registering every dataset company-wide on day one; that is a multi-year effort with no visible win for months. A realistic phased plan:

1. Pilot, one quarter: pick the two or three highest-incident, highest-fan-out datasets identified above, with the producing team as a willing co-sponsor rather than a mandated participant. Prove the CI-compatibility-check flow catches a real breaking change before it ships.
2. Expand to the next tier of datasets and make registration the default path for any newly created shared dataset or event.
3. Backfill the remaining legacy datasets opportunistically, prioritized by consumer count, not attempted as a big-bang migration.

What to track over the following year, and why adoption alone is the wrong metric

Adoption metrics, such as datasets registered or teams onboarded, are necessary but not sufficient, because a team can register a schema and still ship a breaking change if the CI gate is not actually blocking, or if they route around it under deadline pressure. Track outcome metrics alongside adoption:

• Breaking changes caught by CI before merge, a direct, attributable win.
• Schema-related production incidents, quarter over quarter, expected to trend down on registered datasets specifically.
• Time-to-detect for the schema breaks that do still happen, which should shrink once contracts and lineage make "who is downstream" answerable in minutes rather than days.
• Registration coverage on new datasets specifically, whether registering a new dataset has actually become the default, not just something the original high-priority datasets did.

Worked example

Pulling the last twelve postmortems, six were caused by an unannounced schema or type change on a shared table; each took roughly a day of a senior engineer's time to trace and fix, spread across two engineers on average, which is about a dozen engineer-days a year on preventable incidents, concentrated on the five datasets with the most downstream consumers. The pitch proposes a three-month pilot on those five, measured at the three-month mark by two things: how many would-be-breaking changes the CI gate actually blocked, a leading indicator it is working, and whether the eleventh and twelfth postmortem-worthy break of the year still happens on a registered dataset, which it should not if the pilot is working, versus an unregistered one, which is expected and is the argument for phase two.

Trade-offs and pitfalls

A pilot that is too small to matter, a single low-traffic dataset, proves nothing and will not earn budget for phase two; a pilot that is too big, mandating everyone on day one, burns goodwill before the tool has proven itself. The most common way this initiative dies quietly is measuring only adoption, for example "80% of datasets registered," a year in, without ever checking whether registered datasets actually see fewer incidents; that number can rise while the problem it is supposed to fix keeps happening.

Direct answer

Group the rows by the columns that define "duplicate" for your entity, then filter with HAVING COUNT(*) > 1 to keep only the groups with more than one row. GROUP BY collapses rows into candidate groups, and HAVING (not WHERE) filters on the aggregate, since COUNT(*) does not exist until after grouping.

Structured elaboration

• WHERE vs HAVING: WHERE filters individual rows before aggregation. HAVING filters groups after aggregation. COUNT(*) is a post-aggregation value, so the duplicate filter has to be in HAVING.
• Define the duplicate key precisely: whichever columns must match for two rows to count as "the same thing" (name and domain for accounts, event_id for events inserted twice by a retried client). Too narrow a key misses true duplicates; too broad a key lumps unrelated rows together.
• "Groups and their counts" is the base ask: GROUP BY <key> HAVING COUNT(*) > 1. If you also need to see which specific rows are duplicated (to decide which to keep), add the grouped row ids with array_agg as a follow-on step, not a replacement for the count.

Worked example

Example 1: sample data (accounts), tested in DuckDB 1.5:

SELECT
  name,
  domain,
  COUNT(*) AS n_rows,
  array_agg(account_id ORDER BY account_id) AS account_ids
FROM accounts
GROUP BY name, domain
HAVING COUNT(*) > 1
ORDER BY n_rows DESC, name;

Result:

Acme Corp / acme.io (account 3) is correctly excluded: same name, different domain, so it is not a duplicate of the acme.com rows.

Example 2: sample data (events), same connection:

SELECT event_id, COUNT(*) AS n_inserts
FROM events
GROUP BY event_id
HAVING COUNT(*) > 1
ORDER BY n_inserts DESC;

Result: evt-100 with n_inserts = 3 (1 row). This is the classic retried-write signature: the same event_id was inserted three times, seconds apart.

Trade-offs & pitfalls

• NULLs in the grouping key: standard SQL GROUP BY treats all NULLs as one group, so rows with a NULL name or domain lump together into a single "duplicate" group even though NULL <> NULL logically. Decide whether to exclude NULL keys with a WHERE clause before grouping.
• array_agg is Postgres/DuckDB syntax. MySQL uses GROUP_CONCAT, SQL Server uses STRING_AGG; name the dialect if it matters to the interviewer.
• COUNT(*) > 1 finds the group, it does not say which row is "the original." Deciding which duplicate survives needs an explicit tiebreak rule (earliest created_at, lowest id), typically layered on with ROW_NUMBER().

Site Reliability Engineering (SRE) is the practice of running production systems by applying software-engineering discipline to operations work: SREs write code to automate operational tasks that would otherwise be done by hand, and they use quantitative reliability targets, service-level objectives (SLOs), to decide how much operational risk is acceptable. DevOps is the broader cultural movement that pushes development and operations to collaborate closely and ship continuously. SRE is best understood as one concrete, opinionated implementation of DevOps's goals, with specific mechanisms that DevOps as a philosophy does not itself prescribe.

What SRE adds on top of DevOps culture

• A numeric target for reliability. SRE ties reliability to an explicit SLO tied to user-facing outcomes (for example, 99.9% successful requests over 30 days), and treats the gap between that target and perfect reliability, the error budget, as a resource that both feature work and operations spend from.
• A cap on manual operations work. Google's original SRE model caps "toil" (repetitive, manual, automatable operational work) at roughly half of an SRE's time; when toil creeps above that, the team's job is to automate it away, not just staff up.
• A defined engagement model. An SRE team does not silently inherit operational ownership of a service. It takes ownership only after a production readiness review confirms the service meets a reliability bar (monitoring, runbooks, capacity headroom).
• Specific incident and postmortem practices. Blameless postmortems, error-budget policies, and incident command roles are concrete SRE mechanisms, not general DevOps prescriptions.

Where they overlap

Both push for automation over manual toil, shared ownership of what happens in production, continuous delivery, and breaking down the wall between building software and running it.

The relationship

An organization can practice DevOps culture (developers carry their own pagers, ship continuously, collaborate closely with operations) without ever standing up a dedicated SRE function. SRE formalizes the operations side of that culture into a discipline with its own title, headcount, and measurable contract with the teams it serves.

Worked example

A payment API team moves from "everyone carries the pager" to a dedicated SRE model. They agree an SLO of 99.9% successful requests over a 30-day window. If the API serves 10,000,000 requests in that window, the error budget is 0.1% of 10,000,000 = 10,000 failed requests for the month. Once the API meets the production-readiness bar, SRE takes over the pager, and any month where actual failures exceed 10,000 triggers the error-budget policy (for example, freezing non-critical releases until reliability recovers).

Trade-offs and pitfalls

A common failure mode is renaming the existing operations or sysadmin team "SRE" without adopting the underlying practices (automation, a toil cap, blameless postmortems, an error-budget policy). That produces SRE in name only and loses the actual benefit. A second pitfall is assuming SRE replaces DevOps culture: SRE only works on top of a functioning collaborative relationship between development and operations; without that trust, an SRE team just becomes a new operations silo with better vocabulary. The trade-off of standing up a dedicated SRE function is real: it costs headcount and adds a hand-off interface (the readiness review) that a fully "you build it, you run it" model avoids, which is why many smaller organizations keep operational ownership with the feature teams instead of creating a separate SRE org.

Direct answer

LAG(expr) reaches back to the previous row within a partition without a self-join. For day-over-day (or week-over-week) percent change, partition by the entity (symbol, store, user), order by the date column, and compute (current - LAG(current)) / LAG(current) * 100. Two guards make this production-safe: the first row per entity has no prior row, so LAG returns NULL, and that NULL must stay NULL in the output rather than being defaulted to 0 or 100%; and a prior value of exactly 0 must be guarded explicitly, or the division throws a divide-by-zero error in most engines. The identical offset idea, run in reverse, turns a table that only stores a running/cumulative balance into daily deltas: delta = current_balance - LAG(current_balance).

Structured elaboration

• LAG(expr, offset, default) reads the value from offset rows back (default 1) within PARTITION BY ... ORDER BY ...; an explicit third argument can substitute a value for missing rows, but for a percent-change calculation you almost always want the true NULL (missing prior data), not a substituted default that would print as a fake 0% change.
• Guard the divide: CASE WHEN prev IS NULL OR prev = 0 THEN NULL ELSE (current - prev) / prev * 100 END. NULLIF(prev, 0) is an equally valid, more compact way to turn a zero denominator into NULL before dividing.
• Week-over-week is the same shape at a coarser grain: either pre-aggregate to one row per (entity, week) and use LAG(weekly_value) with an offset of 1, or use LAG(daily_value, 7) only if you are certain every calendar day already has exactly one row (see the leap-year/offset trap in the sibling year-over-year question for why a fixed row offset and a fixed calendar offset are not the same guarantee).
• Cumulative-to-delta is LAG used to undo a running total: LAG on the balance column, then subtract, rather than LAG on an already-incremental value.

Worked example

-- day-over-day pct change with NULL-first-row and zero-prev guards
SELECT
  symbol,
  trade_date,
  close_price,
  LAG(close_price) OVER (PARTITION BY symbol ORDER BY trade_date) AS prev_close,
  CASE
    WHEN LAG(close_price) OVER (PARTITION BY symbol ORDER BY trade_date) IS NULL THEN NULL
    WHEN LAG(close_price) OVER (PARTITION BY symbol ORDER BY trade_date) = 0 THEN NULL
    ELSE ROUND(
      (close_price - LAG(close_price) OVER (PARTITION BY symbol ORDER BY trade_date))
      / LAG(close_price) OVER (PARTITION BY symbol ORDER BY trade_date) * 100, 2)
  END AS pct_change
FROM stock_prices
ORDER BY trade_date;

Executed against stock_prices(symbol, trade_date, close_price) = ('AAA','2024-01-01',100), ('AAA','2024-01-02',110), ('AAA','2024-01-03',0), ('AAA','2024-01-04',50):

The cumulative-to-delta direction, executed against account_balances(account_id, as_of_date, cumulative_balance) = (1,'01-01',1000), (1,'01-02',1250), (1,'01-03',1250), (1,'01-04',900):

SELECT account_id, as_of_date, cumulative_balance,
  cumulative_balance - LAG(cumulative_balance) OVER (PARTITION BY account_id ORDER BY as_of_date) AS daily_delta
FROM account_balances ORDER BY as_of_date;

gives deltas NULL, 250, 0, -350 (the third day is flat, the fourth day the balance actually dropped, which a naive "always positive" assumption about deltas would miss).

Complexity

Computing LAG requires the engine to sort each partition by the ORDER BY column before it can walk it: O(n log n) over the table if there's no supporting index, or effectively O(n) if an index already covers (entity, date) and the engine can read rows in that order without a separate sort step. Once sorted, LAG itself is one linear pass per partition, and because it only ever looks a fixed number of rows back, it only has to buffer that many rows at a time rather than the whole partition, so it doesn't carry the memory cost of an unbounded running aggregate like SUM() OVER (... UNBOUNDED PRECEDING). The sort is the part that can spill to disk at real scale: sorting hundreds of millions of rows by (entity, date) without a supporting index can exceed the engine's working-memory budget and force an external, disk-backed sort, which is the actual cost driver here, not the LAG computation itself.

Trade-offs & pitfalls

• Defaulting the first row's percent change to 0 (instead of NULL) is a common, easy-to-miss bug: it silently tells a dashboard "no change" when the truthful answer is "no data to compare against yet."
• Week-over-week built on LAG(x, 7) over a daily table is only correct if the table has zero missing days; if any day is absent, the 7-row offset lands on the wrong calendar week without erroring, so validate row-count-per-entity or pre-aggregate to weekly grain first.
• ROUND on the percent value is cosmetic and should be applied last, after the guard logic, not baked into the guard comparisons.
• The cumulative-to-delta direction assumes the balance table is strictly one row per (entity, date); duplicate rows for the same date will make the delta double-count or zero out depending on ordering, so the same tie-breaker discipline used for running totals applies here too.

Direct answer. Default per-column statistics assume each column's values are distributed independently of every other column, so when two columns are actually correlated, the optimizer multiplies their individual selectivities together and ends up with an estimate far lower (often wildly so) than the true combined selectivity; extended statistics explicitly capture how those columns co-vary, letting the optimizer estimate the combined predicate's selectivity directly rather than assuming independence.

Structured elaboration. If a city column and a state column are each individually somewhat selective on their own, but every value of city in the data only ever co-occurs with one specific state (a real-world correlation: cities belong to exactly one state), the independence assumption badly overestimates how selective city = X AND state = Y really is: multiplying each column's standalone selectivity together implies the combined predicate is far MORE selective than it actually is, since knowing the city already tells you the state with certainty. Extended (multi-column) statistics record the actual joint distribution across a specified set of correlated columns, so the optimizer can look up (or closely approximate) the real combined selectivity instead of computing a product that assumes independence.

Worked example. Two predicates might each independently match 10% of rows on their own; if they were truly independent, the combined predicate would match roughly 1% (10% times 10%). If the columns are perfectly correlated (every row matching one predicate also matches the other), the true combined match rate is still 10%, not 1%, a ten-fold estimation error that can easily push the optimizer toward an index-heavy plan appropriate for a genuinely rare combination, when the real combination is common enough that a scan-based plan would have been the better choice.

Trade-offs and pitfalls. Extended statistics have to be explicitly created and targeted at the specific column combination that's actually correlated, most engines don't infer correlation automatically and build multi-column statistics for every possible column pair on their own, since that would be prohibitively expensive to maintain; you generally need to notice the estimation error first (via an estimate-actual mismatch on a predicate involving those columns) before you know which specific column combination is worth the investment. Once created, verify the fix by re-checking that predicate's estimated-vs-actual row count, rather than assuming creating the statistics automatically resolved the issue, since the statistics also need to be genuinely representative of the current data to help.

Direct answer

Data mesh is trying to solve an organizational bottleneck, not a technical one: in a centralized warehouse, one platform team becomes the sole gatekeeper for every domain's data, and as the company grows, that team cannot scale its domain knowledge or its throughput fast enough to keep every business unit unblocked. Data mesh fixes this by making each domain team responsible for publishing its own data as a well-defined, discoverable, quality-guaranteed "data product," coordinated only by shared interoperability standards rather than a single team's backlog. What you give up is exactly the thing centralization was good at: one team enforcing consistent modeling discipline and conformed dimensions everywhere, which a mesh instead has to achieve through governance and standards that are far easier to state than to actually enforce across many independent teams.

Structured elaboration

The problem data mesh solves. As a company adds domains (finance, marketing, logistics, each with deep, changing domain knowledge), a single centralized platform team increasingly becomes a queue: every new mart, every schema change, every new source integration waits on that one team's capacity, and that team's members are rarely domain experts in all the areas they are modeling data for. Data mesh addresses this by pushing ownership of the DATA out to the domain teams that already understand it best, while the platform team's job shifts to building and operating shared self-serve infrastructure (a common cataloging, access-control, and quality-testing layer) rather than owning every dataset itself.

What a well-run centralized warehouse already solves, and does not need mesh to fix. If an organization is small enough, or disciplined enough, that a single platform team can genuinely keep up with every domain's needs, a centralized warehouse's core advantage, one team enforcing conformed dimensions and one place to look for the "official" definition of a metric, is not a problem data mesh needs to solve, it is a benefit you would be trading away.

What you give up. Conformance discipline is the main casualty: in a centralized model, one team can simply refuse to publish a customer dimension that does not match the existing conformed one. In a mesh, each domain team owns and can independently evolve its own data product, so achieving the same cross-domain consistency requires the interoperability standards (naming conventions, shared identifiers, data contracts, quality SLAs) to be genuinely enforced, typically through automated checks in the shared platform rather than a single team's review, and that enforcement machinery is itself a significant, ongoing investment.

When to recommend against it. A small or early-stage company (the kind of organization discussed when deciding whether it needs a formal warehouse at all) does not have enough distinct domains or organizational scale for a mesh's coordination overhead to pay for itself; a single platform team can still serve everyone directly and faster than standing up domain teams and shared self-serve infrastructure would. A company without the engineering maturity to build and operate the shared interoperability platform a mesh depends on will end up with the worst of both worlds: decentralized ownership without the standards that were supposed to keep it consistent, which is functionally the four-un-conformed-dimensions governance failure, just organized around teams instead of ad-hoc mart builds.

Worked example

A company with one warehouse team serving five departments starts taking two weeks to review and approve every new mart request, and departments start building their own disconnected spreadsheets to route around the bottleneck, exactly the fragmentation a warehouse was meant to prevent in the first place. Moving to a mesh does not remove the need for a customer identifier every domain agrees on: it moves the enforcement of that agreement from "the one team reviews everything" to "the shared platform automatically validates every published data product against a data contract that specifies the agreed identifier, format, and quality checks," which only works if that contract-validation infrastructure actually gets built and maintained, not just proposed.

Trade-offs and pitfalls

The most common mistake is adopting the language of data mesh (domain ownership, data products) without building the shared self-serve platform underneath it, which produces decentralization with none of the standards enforcement that made the model viable in the literature; the result is usually worse consistency than the centralized warehouse it replaced. The second common mistake is treating this as an all-or-nothing choice: many organizations run a hybrid where a small number of genuinely cross-cutting dimensions (customer, date, product) stay centrally owned and conformed exactly as in a Kimball bus architecture, while domain-specific facts and less-shared dimensions are pushed out to domain ownership.

Direct answer. An incremental ETL design for a nightly 5TB transformation over a production table needs to read only the CHANGED data since the last successful run (not the full table), stage that in an isolated area before touching the target, and carry a rollback plan for both the CODE (revert to the prior job version) and the DATA (revert to the prior successful output) as two independent, separately-testable capabilities.

Structured elaboration.

1. Ingest/CDC. Capture only rows changed since the last watermark (a change-data-capture stream, or a updated_at > last_watermark query against the source if CDC is unavailable), with the watermark itself stored durably and updated only after the run fully succeeds, so a failed run does not advance the watermark and silently skip data on the next attempt.
2. Staging. Write incremental transform output to a STAGING location, never directly to the production target -- this makes both validation (compare row counts/checksums against expectations before promoting) and rollback (the staging area can simply be discarded) cheap and safe.
3. Transform. Process only the incremental slice; because incremental processing volume is much smaller than a full nightly reload, this stage should complete well within the SLA even as total table size grows, which is the entire point of moving off a full-reload design.
4. Write, with minimal production-DB impact. Promote from staging to production via an atomic operation (a table swap, a partition-level replace, or a transactional merge) rather than row-by-row writes against the live table, minimizing lock contention and the window during which production queries could see a partially-updated state.
5. Schema migration approach. Treat a schema change to the source or target table as a controlled, separate deployment step ahead of the pipeline code that depends on it: additive, backward-compatible changes (a new nullable column) are applied to the target table BEFORE the code that populates it ships, so the incremental job never has to handle a column that does not exist yet. Breaking changes (a renamed or retyped column) go through a versioned dual-write window -- the new schema is written alongside the old for a transition period, consumers migrate to the new column, and the old one is dropped only once nothing reads it -- never a single atomic cutover applied directly to a live production table. The staging validation from item 2 doubles as a schema-mismatch safety net: a source-side schema drift the migration process missed shows up as a validation failure in staging, before it ever reaches production, rather than as a silent corruption.
6. Rollback plan -- code. Keep the prior job version deployable via standard rollback (version pinning in the orchestrator), tested independently of any specific data issue.
7. Rollback plan -- data. Retain the PRIOR successful staged/promoted output (at minimum the previous day's, ideally a short window of days) so a bad run can be rolled back to a known-good state without needing to re-derive it from raw history, which could itself be slow or, worse, impossible if the raw source data has since changed or aged out.

Worked example. A 5TB source table normally sees roughly 2-3% row-level change per day (a stated, measured assumption from monitoring the actual change rate). Incremental processing therefore handles roughly 100-150GB/night instead of 5TB, a >30x reduction in processing volume, directly translating to proportionally lower cluster size and runtime for the SAME SLA. If tonight's run produces an output that fails a post-write validation check (e.g., unexpected row-count delta, a data-quality rule violation), the atomic promotion step means production still serves LAST NIGHT's good output, and the bad run's staged output is simply discarded rather than needing a live rollback against a partially-corrupted production table.

Trade-offs & pitfalls. Incremental processing trades simplicity for efficiency: a full-reload job is easier to reason about (every run starts from the same clean state) and self-heals from certain classes of bugs (an incorrect intermediate value from three nights ago cannot silently persist, since everything is recomputed from source each time), whereas an incremental job can accumulate subtle drift from a missed or partially-applied update if the watermark/CDC mechanism has an edge case -- periodic full-reconciliation runs (even if run less frequently than nightly) are a common mitigation, not an admission the incremental design failed.

Direct answer

The operational response to a split-brain has three phases: contain (stop the divergence from getting worse), determine the authoritative state (figure out what actually happened on each side), and reconcile (merge or discard the diverged writes and get back to one consistent state). The goal throughout is to minimize both further data loss and the blast radius of whatever reconciliation you choose, and to keep a human in the loop for any conflict that automated ranking cannot resolve safely.

Structured elaboration

1. Contain: as soon as the partition is detected (or heals), stop accepting new writes on at least one side, or route all traffic to a single side, so the divergence window has a hard stop instead of continuing to widen while you investigate.
2. Determine authoritative data: for each diverged key, compare the two sides' versions using whatever causality information you have (vector clocks, version numbers, timestamps). Writes where one side's version strictly dominates the other (the causal "before" case) are not real conflicts, just replication lag, and resolve themselves trivially: keep the newer one. Writes where neither side's version dominates the other are true concurrent conflicts and need an explicit resolution policy.
3. Reconcile: apply an automated policy (last-write-wins by timestamp, a merge function for mergeable data like a set or counter, or a business rule like "larger cart wins") to the majority of conflicts, and route anything the policy cannot resolve safely (for example, two different final states for the same financial balance) to a human for manual review rather than guessing.
4. Restore: once every diverged key is resolved, resume normal write acceptance on both sides and verify convergence (a checksum or Merkle-tree comparison across the previously-diverged range) before declaring the incident closed.

A closely related but distinct failure shape is worth naming: instead of a live, ongoing partition, a primary region can go down while its secondaries keep accepting writes in its absence, then the primary returns. The reconciliation approach is the same in spirit (determine what happened while the primary was gone, then merge), but the failure looks asymmetric rather than symmetric: only one side was ever "authoritative" before the outage, so the natural default is to treat the secondaries' writes during the outage as the ones needing review against what the returning primary still has, rather than treating both sides as equally authoritative from the start.

Worked example

Using the causal-dominance check from vector clocks to separate real conflicts from mere lag:

def vc_compare(vc1, vc2):
    le = all(vc1.get(k,0) <= vc2.get(k,0) for k in set(vc1)|set(vc2))
    ge = all(vc1.get(k,0) >= vc2.get(k,0) for k in set(vc1)|set(vc2))
    if le and not ge: return "before"
    if ge and not le: return "after"
    if le and ge: return "equal"
    return "concurrent"

For a write on side A with clock {A:2, B:0, C:0} and a write on side C with clock {A:1, B:0, C:1}, the function returns "concurrent" (executed; confirmed): A's clock does not dominate C's and vice versa, so this is a genuine conflict, not just one side being behind, and it needs the resolution policy from step 3 rather than a simple "keep the newer one."

Trade-offs and pitfalls

The common mistake is applying an automated resolution policy (like last-write-wins) uniformly to everything, including conflicts where a wrong automated guess is expensive (a financial balance, an inventory count near zero). Route those specific cases to a human, even though it is slower, because the cost of guessing wrong there is much higher than the cost of a short manual-review delay. The other common mistake is not containing the divergence first: continuing to accept writes on both sides while you investigate only grows the set of conflicts you eventually have to reconcile.