For which workloads is Snowflake's separated storage-and-compute architecture the wrong choice?

## What the architecture is tuned for Every design choice in Snowflake points at one workload shape: read a large amount of columnar data from shared object storage, in parallel, with the plan compiled once centrally. Immutable files, per-file statistics for pruning, elastic stateless compute, a shared result cache — all of it pays off when queries touch many rows and few columns. The corollary is that the model is misaligned when the unit of work is one row and the latency budget is tens of milliseconds. ## Where it is the wrong tool **High-concurrency point lookups behind an application.** A user-facing endpoint doing `SELECT ... WHERE customer_id = ?` thousands of times per second is asking a scan engine to behave like a key-value store. Even with clustering and Snowflake's search optimization service reducing the data touched, you still pay per-query compilation in cloud services and a network hop to object storage on a cold cache — and you are paying warehouse credits continuously to keep compute warm for it. A PostgreSQL replica, a key-value store, or a cache fed from Snowflake will serve that traffic for a fraction of the cost and latency. **Row-at-a-time OLTP writes.** Small, frequent `INSERT`s and `UPDATE`s fight the storage model twice. Small writes create small files, which hurts later scan efficiency until data is consolidated. Row-modifying DML rewrites files and serializes on a table lock, so a workload of many concurrent single-row updates queues rather than scaling. Batch or micro-batch ingestion is the intended pattern. **Workloads that need enforced referential integrity.** Snowflake accepts `PRIMARY KEY`, `UNIQUE` and `FOREIGN KEY` declarations for documentation and for optimizer hints, but does **not** enforce them — `NOT NULL` is the exception. If your correctness model assumes the database will reject a duplicate key, you must move that enforcement into the pipeline or into an upstream transactional system. This is a genuine architectural constraint, not a tuning detail, and interviewers like it because candidates who have only read about Snowflake usually miss it. **Iterative, row-by-row procedural logic.** Cursor loops and per-row procedure calls run against an engine built for set-at-a-time vectorized work. Rewrite as set operations, or do the work elsewhere. **Very small, very frequent queries where fixed overhead dominates.** If the query itself touches megabytes, compilation and dispatch can be a large fraction of total time, and the elasticity you are paying for buys nothing. ## The cost dimension of the same argument The billing model rewards bursty, heavy work: run a big warehouse briefly, suspend it, pay nothing while idle. It punishes thin, continuous work: a warehouse that must never suspend because a trickle of latency-sensitive queries keeps arriving is billing continuously for compute that is mostly idle, while delivering worse latency than a much cheaper purpose-built store. When you catch a team asking for lower auto-suspend *and* lower latency at once, that is usually the signal that the workload belongs somewhere else. ## Where it is emphatically the right tool Stating the boundary in both directions is what makes this a judgment answer rather than a criticism. Snowflake is strong for: large scan-and-aggregate analytics; many independent teams needing isolated compute over one governed copy of the data; unpredictable or bursty demand where paying only for what runs beats provisioning a peak-sized cluster; semi-structured data landed and queried without a rigid up-front schema; and sharing data across teams, accounts or organizations without shipping copies. ## The architecture to propose instead The common resolution is not "pick one" but a split with a defined boundary: - Snowflake as the analytical system of record and transformation engine. - A serving store — relational, key-value, or search — populated from it for user-facing low-latency access. - A defined refresh path and freshness SLA between them, so the serving layer's staleness is an explicit product decision rather than an accident. And note the moving target honestly: Snowflake has been extending into row-oriented, point-access workloads with Hybrid Tables, so the boundary is not fixed. Verify current capability and availability for your region and edition rather than repeating an old rule of thumb — and say exactly that in an interview, because acknowledging that a vendor boundary moves is itself a senior signal. ## How to answer this well Name the workload shapes, not the vendor. "Sub-100 ms single-row reads at high concurrency, high-frequency small writes, and enforced referential integrity" is a crisp answer that generalizes to any scan-oriented warehouse. Then give the split architecture and the freshness contract between the halves.