Network Policies, Private Connectivity & Transactions/Locking

Secure network-level access to Snowflake and understand its transactional concurrency model.

This page covers two unrelated things: who can reach your account over the network, and how Snowflake handles concurrent writes.

A network policy is like a building's front desk that only lets in visitors from a pre-approved list of addresses, regardless of whether they have a valid ID; private connectivity is a dedicated private tunnel between two buildings that never uses the public street; and Snowflake's transaction model is like a busy shared kitchen where chefs working on different dishes (rows/partitions) never get in each other's way, but two chefs both grabbing the same pot (same table/partition) at once will have one of them wait their turn.

Key Concepts

Network policies restrict where connections may come from. Valid credentials and correct roles are not enough if the IP address is not allowed:

    CREATE NETWORK POLICY office_only
      ALLOWED_IP_LIST = ('203.0.113.0/24', '198.51.100.7');

Apply it to the account, or to individual users — which is the more useful form. A service account for a CI pipeline can be locked to that pipeline's addresses, so a leaked key is unusable from anywhere else.

Lock yourself out carefully. An account-level policy that omits your own address takes effect immediately. Snowflake will not let the current user apply a policy that blocks them, but it is still worth testing on a single user first.

Private connectivity removes the public internet entirely. AWS PrivateLink, Azure Private Link and Google Private Service Connect route traffic between your cloud network and Snowflake over the provider's own backbone.

Traffic was already encrypted; this is about never traversing the public internet at all, which is usually a compliance requirement rather than a security improvement in itself.

Now the unrelated half: concurrency. Snowflake is built for analytics, and its transaction model shows it.

Because micro-partitions are immutable, a statement reads a consistent snapshot as of when it started, and writers create new partitions rather than modifying rows. So readers never block writers and writers never block readers — no shared locks, no row locks, no deadlock detector.

Concurrent writes to the same table do conflict. Two statements updating the same table can collide, and Snowflake resolves it by making the second wait, or aborting it:

    two concurrent UPDATEs on the same table
    -> one proceeds; the other waits, and may abort

There is no row-level locking to make them independent. For an OLAP warehouse that is the right trade — but it means Snowflake is a poor fit for many small concurrent updates, which is an OLTP pattern.

The practical consequence: batch your DML. One statement updating 100,000 rows is normal here; 100,000 statements updating one row each will conflict, run slowly, and cost far more.

Explicit transactions work as expected. BEGIN, COMMIT, ROLLBACK, with autocommit on by default, so a single statement needs no block. Use one when several statements must land together — and keep it short, since an open transaction holds its conflict scope for its whole duration.

BEGINCOMMITROLLBACK

What the interviewer is probing.1. "What does a network policy do and not do?" Probing: the scope. Stalls: "Blocks IPs." Moves up: allows or blocks by IP at account or user level — it is not encryption and not private networking.

2. "What does PrivateLink add?" Probing: the difference. Stalls: "Security." Moves up: traffic stays off the public internet entirely, which is usually a compliance requirement rather than a performance one.

3. "What is the transaction isolation model?" Probing: the semantics. Stalls: "Serialisable." Moves up: read committed, with each statement seeing data committed before it began; DDL is auto-committed.

4. "What causes lock contention here?" Probing: the practical cause. Stalls: "Many readers." Moves up: concurrent UPDATE, DELETE or MERGE on the same table — partition-level locks mean these serialise, so batch them rather than running many small ones.