Skip to content
LakeBench
ProblemsCommunityPricing
Sign inStart practicing
Back
  1. Home
  2. Interview prep
  3. Two pipelines writing to the same table

Pipelines & scenarios · Production Scenarios

Two pipelines writing to the same table

Mediumpipelines-52
scenarioconcurrencytable-designownershipmerge

Question

Two different jobs write to the same table and sometimes overwrite each other's data. How do you fix the design?

Solution

Two writers on one table leads to overwritten data because neither knows what the other is doing. The best fix is a design rule: every table has one writer. When that is not possible, make the writes safe by construction, and not by hope.

Understand how it goes wrong

Typical patterns: both jobs do INSERT OVERWRITE on the same partition, so the later one erases the earlier one's rows. Or one does a full table replace while the other appends. Or two jobs do read-modify-write on the same rows, and the last write wins with stale data. Look at the run times in the orchestrator to see how the jobs overlap.

Preferred fix: one owner per table or partition

  • Give each table a single owning pipeline. If two sources feed one logical table, write each to its own table (orders_web, orders_store), and expose the combination as a view or a downstream model that has one writer.
  • If you must share a table, split the ownership by partition or key range (job A writes region = 'EU', job B writes region = 'US'), and never overwrite partitions you do not own.

Make shared writes safe

  • Use a table format with transactions and optimistic concurrency (Delta, Iceberg, Hudi), so conflicting writes fail loudly instead of corrupting data, and use MERGE by key rather than overwrite, so jobs update only their own rows.
  • Write to staging tables first, and have a single publish step apply the data to the final table.
  • Orchestrate dependencies so the jobs cannot overlap: job B waits for job A, or both run inside the same DAG, with a pool or lock limiting concurrency to one.

Locks

Explicit locking (a lock table, or an advisory lock) is the last resort. It is hard to get right, because a crashed job can hold a lock forever, and it hides a design problem. If you use one, add timeouts and make stale locks expire.

Detect it

Add a check that the row count and key uniqueness after each load match expectations, and log the writer id in each row (a _job_id column), so you can see who wrote what. Alert on commit conflicts.

Say in an interview

"One writer per table" is the principle. Transactions and MERGE are the safety net, and orchestration dependencies stop the overlap.

PreviousNext