Two Snowflake virtual warehouses write to one table concurrently — what does each session see?
answer
- isolation of compute is not isolation of state
- one brain above all the clusters
- each statement takes its own snapshot
- appends behave differently from rewrites
- two MERGEs into one table do not run at once
basics
~20 sBoth warehouses go through one transaction manager in Snowflake's cloud services layer, so the table has a single serialized version history. Each statement reads a consistent snapshot of data committed before it started, and concurrent UPDATE, DELETE or MERGE on the same table serialize behind table-level locks.
solid answer
~50 sWarehouses are isolated for **compute**, not for **consistency**. Metadata and transactions live in the cloud services layer, so the table has one version history no matter how many warehouses touch it. Snowflake supports **READ COMMITTED** isolation: each statement sees a snapshot of data committed before that statement began, so a long multi-statement transaction can see different data in successive statements. Appending writes from different warehouses generally proceed in parallel because each writes new files. Row-modifying DML — `UPDATE`, `DELETE`, `MERGE` — takes a lock on the target table, so two of them against the same table serialize; the waiter blocks up to `LOCK_TIMEOUT` and then errors. There is no stale-cache hazard across warehouses: cached files are immutable, and a new version means new files, so a reader either sees the old version or the new one, never a torn mix.
code
sql · 13 lines-- Session on warehouse A
USE WAREHOUSE etl_a;
MERGE INTO fact_orders t USING stg_a s ON t.id = s.id
WHEN MATCHED THEN UPDATE SET t.amount = s.amount
WHEN NOT MATCHED THEN INSERT (id, amount) VALUES (s.id, s.amount);
-- Session on warehouse B, at the same time
USE WAREHOUSE etl_b;
ALTER SESSION SET LOCK_TIMEOUT = 300; -- seconds to wait for the table lock
MERGE INTO fact_orders t USING stg_b s ON t.id = s.id
WHEN MATCHED THEN UPDATE SET t.amount = s.amount
WHEN NOT MATCHED THEN INSERT (id, amount) VALUES (s.id, s.amount);
-- queues behind the first MERGE; errors if the wait exceeds LOCK_TIMEOUTgo deeper
Know that all warehouses read the same data and that Snowflake is transactional — a query never sees a half-finished write. The locking details are above your tier.
Explain that transactions and metadata live in cloud services, that READ COMMITTED means a snapshot per statement, and that appends and row-modifying DML behave differently under concurrency.
Diagnose the real production case: concurrent MERGEs into one table serializing on a table lock, LOCK_TIMEOUT errors, and why adding warehouses did not help. Offer the restructuring that actually fixes it.
Design around it — one writer per target table, staged appends with scheduled consolidation, and a convention for long-running reports that need a stable snapshot. Set expectations that compute isolation never buys write parallelism on a shared target.
## The key idea Snowflake gives you compute isolation between virtual warehouses and *no* isolation of data state — deliberately. Transaction management and metadata are centralized in the cloud services layer, so every warehouse in the account operates on one shared, serialized version history per table. This is what makes "one copy of the data, many compute clusters" honest rather than a marketing line. ## Read semantics Snowflake's supported transaction isolation level is **READ COMMITTED**. Concretely: - A statement reads a consistent snapshot of the table as of the moment the statement began. It will not see writes committed by anyone else while it is running, and it will not see a half-applied write. - Inside an explicit multi-statement transaction (`BEGIN` … `COMMIT`), each *statement* takes its own snapshot. So two identical `SELECT`s in one transaction can return different results if someone committed in between. This is READ COMMITTED behaviour and is the classic surprise for people expecting repeatable reads. - A transaction always sees its own uncommitted changes. Because files are immutable, a reader on warehouse B whose local SSD cache holds files from the previous table version is in no danger: those files still hold exactly the bytes they always held, and the version metadata tells the reader which file list is current. There is nothing to invalidate and no coherency protocol to run. ## Write semantics Writes split into two behaviours: **Appends.** `INSERT` and `COPY INTO` produce brand-new files and add them to the table's file list. Multiple loaders on different warehouses can generally proceed in parallel, which is why fan-out ingestion works. **Row-modifying DML.** `UPDATE`, `DELETE` and `MERGE` must rewrite existing files, so Snowflake takes a lock on the target table for the duration of the statement (or the transaction, if inside an explicit one). A second such statement against the same table queues. It waits up to the session's `LOCK_TIMEOUT` and then fails with a lock-timeout error rather than waiting forever. This is the single most common concurrency complaint in production Snowflake: two pipelines both running `MERGE` into the same target, from two warehouses, discovering that adding a second warehouse bought them nothing because the bottleneck was the lock, not the CPU. ## What this means operationally - **Adding a warehouse does not parallelize writes to one table.** Compute isolation is real; write serialization on a target table is also real. If two jobs must both merge into `fact_orders`, they contend regardless of warehouse topology. Fix it by merging once from a union of the sources, by partitioning the work across separate target tables, or by staging appends and consolidating in a single scheduled merge. - **Keep explicit transactions short.** Holding a table lock across several statements, or across a slow statement, multiplies the blast radius. Beware of a transaction left open by a client that then goes idle. - **Do not assume repeatable reads.** If a report must be internally consistent across several statements, either read from a clone (a fixed file list), use Time Travel at a fixed timestamp, or restructure it into one statement. - **Readers are never blocked by writers.** A `SELECT` on warehouse B is not held up by a `MERGE` on warehouse A; it just reads the last committed version until the merge commits. ## Worked scenario Warehouse A runs a 10-minute `MERGE` into `fact_orders`. Simultaneously: - A dashboard on warehouse B runs `SELECT SUM(amount) FROM fact_orders`. It succeeds immediately, returning the pre-merge state, because it snapshots at statement start. - An ELT job on warehouse C runs `INSERT INTO fact_orders SELECT ...`. In general this can proceed, since it writes new files. - A second pipeline on warehouse D runs another `MERGE INTO fact_orders`. It waits on the table lock and eventually errors if the wait exceeds `LOCK_TIMEOUT`. Naming the three different outcomes is what distinguishes a candidate who has run this in production from one who has read the marketing page. ## The framing to lead with "Warehouses isolate compute, not data. There is one transaction manager and one version history in cloud services. Statements read a committed snapshot; row-modifying DML on the same table serializes." Then give the merge-contention example, because that is the real-world consequence interviewers are probing for.
- Two identical SELECTs inside one explicit transaction return different results. Is that a bug?No — it is READ COMMITTED behaviour. Each statement takes its own snapshot of committed data, so a commit by another session between the two statements is visible to the second one. If you need a stable view across several statements, read from a clone or pin a Time Travel timestamp, or collapse the work into one statement.
- A team splits two MERGE pipelines onto separate warehouses and sees no speedup. Why?Because the contention is a table lock, not CPU. Row-modifying DML serializes per target table regardless of which warehouse issues it, so the second merge simply waits. Fix it upstream: merge once from a union of both sources, target different tables, or stage appends and consolidate on a schedule.
- Can a virtual warehouse serve stale data from its local cache after another warehouse commits a change?No. Cached files are immutable, and a commit produces new files plus a new table version in metadata. A query resolves the current file list first, so it either reads the new version or, if it started earlier, a consistent older one — never a mix of both.
saying these in an interview costs you the question
- Says each warehouse has its own copy of the data that syncs later
- Claims warehouses can be inconsistent with each other
- Thinks a long MERGE blocks readers on other warehouses
- Assumes repeatable reads inside a multi-statement transaction
- Believes adding warehouses parallelizes writes to one table