Why do filters on a Snowflake VARIANT path sometimes prune as well as a typed column and sometimes not?

## The thing most candidates get wrong The intuition is that `VARIANT` is a JSON blob and every query re-parses it. That is not how Snowflake stores it. At load time the document is parsed once, and Snowflake extracts as much of it as it can into an internal columnar form — effectively hidden sub-columns, each with the same per-micro-partition statistics (min, max, distinct-ish counts, null counts) that ordinary columns get. That is why `WHERE payload:status::string = 'PAID'` can prune micro-partitions and read a narrow slice of storage, while looking, from the SQL, exactly like an expensive document scan. ## When extraction works well Extraction favours regularity. Paths that behave like columns get treated like columns: - the path appears in most or all documents; - its value has a **consistent type** across rows — always a string, always a number; - it is a scalar at a stable position in the structure, not a value whose location shifts between documents. A payload that is really a flat record with ten well-known fields, delivered as JSON because that is what the producer emits, behaves close to a normal table. ## When it degrades Extraction cannot help when the shape fights it: - **Mixed types on one path.** `payload:id` numeric in some rows and a string in others. Type inconsistency is the classic blocker. - **Sparse or exploding key spaces.** Documents that use values as keys — `{"metrics": {"cpu_9f3a": 0.4}}` — generate an unbounded set of paths that cannot each become a sub-column. - **Values buried inside arrays.** Elements are reached positionally at query time via `FLATTEN`, and per-element values are far less amenable to the same column-style treatment than stable top-level scalars. - **Very large documents.** With a 16 MB per-value ceiling, big documents mean fewer rows per micro-partition and more bytes touched per qualifying row. When a path is not extracted, evaluating a predicate on it means reading the stored document and walking it — no pruning benefit, and CPU proportional to document size times rows scanned. ## Diagnosing it You cannot list the sub-columns; there is no catalogue view for them. You diagnose empirically: - Run the predicate against the `VARIANT` path and look at the Query Profile's **partitions scanned versus partitions total** on the table scan. A ratio close to 1 on a selective predicate means no pruning happened. - Compare against the same predicate on a typed column materialised from the same path — the difference in bytes scanned is the cost of non-extraction. - Watch for a scan whose time is dominated by processing rather than remote I/O; that shape suggests per-row document parsing. Be careful not to blame extraction for a self-inflicted wound. Wrapping the path in a function (`LOWER(payload:status::string) = 'paid'`) defeats pruning regardless of how well the path is stored, exactly as it would on a native column. ## The production pattern Keep the raw document and project the hot fields: ```sql CREATE TABLE orders ( order_id NUMBER, status STRING, order_ts TIMESTAMP_NTZ, payload VARIANT -- the original, kept for replay ); INSERT INTO orders SELECT payload:order_id::number, payload:status::string, payload:created_at::timestamp_ntz, payload FROM orders_raw; ``` This buys three things: predictable pruning on the typed columns, the option to define a clustering key on one of them, and a stable contract for analysts who should not have to know the payload's shape. It costs some storage — the field is stored twice — which compression makes cheap relative to the scan savings on a hot dashboard. The alternative extremes are both worse in practice. Storing only the `VARIANT` leaves query performance at the mercy of a rule you cannot inspect. Storing only the flattened columns throws away the original, so a field nobody projected is gone and any schema-drift correction requires a re-ingest from the source files. ## The trade-off to voice Semi-structured storage buys tolerance for schema drift: a producer adding a field breaks nothing, and the new field is queryable immediately. What it costs is predictability — you cannot see whether a given path is extracted, and the answer can change as the data's shape drifts under you. So take the flexibility at the landing zone, and pay for determinism on the small set of fields that dashboards actually filter on. ## What interviewers are checking That you know `VARIANT` is stored columnar rather than as text; that you can name the shapes that defeat the optimisation; that you diagnose with partitions-scanned rather than folklore; and that your recommendation is the pragmatic hybrid rather than a dogmatic "never use VARIANT" or "VARIANT is free".