Performance Optimization, Querying and Transformation — Snowflake SnowPro Core practice questions

A stream has become stale. The controlling principle behind staleness is that:

1. Streams expire on a fixed schedule regardless of the base table's activity
2. A stream goes stale as soon as its base table is cloned into another schema
3. Staleness is triggered only when the stream's own storage quota is exceeded
4. A stream is stale once its offset falls outside the base table's retention period

Answer: D — A stream is stale once its offset falls outside the base table's retention period

D) Correct — a stream depends on the base table's change records within data retention, so an unconsumed offset older than that window can no longer be resolved. A) There is no fixed expiry independent of consumption and retention. B) Cloning creates an independent object and does not invalidate the source stream. C) Streams do not have a separate storage quota that governs staleness.

Clustering is LEAST likely to be worth its cost on a table that is:

1. Several terabytes and queried with selective range filters on one column
2. Large, rarely updated, and scanned with the same narrow date predicate
3. Modest in size and rewritten in full by a nightly ordered load
4. Very large, append-only, and filtered on a moderate-cardinality region column

Answer: C — Modest in size and rewritten in full by a nightly ordered load

C) Correct — a smaller table that is fully rewritten in sorted order each night already arrives well organized, so paying for continuous maintenance adds cost with little gain. A) That is the canonical case where clustering pays off. B) Large and stable with a consistent predicate is also a strong fit, since maintenance cost is low. D) Append-only large tables with a repeated filter benefit clearly from clustering.

APPEND_ONLY = TRUE on a stream means:

1. Tracks delete operations only, ignoring inserts and updates
2. Tracks all DML including inserts, updates, and deletes
3. Tracks inserts only, with lower overhead and storage cost
4. Automatically purges source rows once they have been consumed

Answer: C — Tracks inserts only, with lower overhead and storage cost

C) Correct — an append-only stream records inserts and skips updates and deletes, which makes it cheaper to maintain. A) Deletes are exactly what append-only omits. B) That describes the default standard stream. D) Streams never delete data from the source table.

A VARIANT field holding numeric strings is compared against an integer literal and no rows match. The BEST correction is:

1. Rebuild the table with the column typed as OBJECT rather than VARIANT
2. Apply a FLATTEN over the field so the comparison is evaluated per element
3. Enable the search optimization service so the predicate is resolved by index
4. Cast the extracted value to a numeric type before comparing it to the literal

Answer: D — Cast the extracted value to a numeric type before comparing it to the literal

D) Correct — values pulled from a VARIANT retain VARIANT typing, so an explicit cast is needed for the comparison to behave numerically. A) Changing the declared type does not fix an untyped extraction. B) FLATTEN expands collections; it does not resolve a type mismatch. C) Search optimization accelerates predicates but does not change comparison semantics.

Stored procedures in Snowflake CAN:

1. Only run a single SELECT statement and return its first row
2. Run DML but cannot COMMIT; the calling session must commit afterwards
3. Run multi-statement logic in SQL Scripting or Snowpark languages
4. Run only on warehouses of size Medium or smaller, by design

Answer: C — Run multi-statement logic in SQL Scripting or Snowpark languages

C) Correct — stored procedures execute programmatic logic with branching, loops, and transactions. A) Their scope is much wider than one SELECT. B) A procedure can open and commit transactions itself. D) There is no warehouse-size restriction.

A multi-statement transaction is opened with BEGIN, and the session disconnects before COMMIT. The outcome is that the transaction:

1. Remains open until another session explicitly commits or aborts it
2. Commits automatically, since Snowflake treats disconnects as completion
3. Is converted into a series of autocommitted statements retroactively
4. Is rolled back, so none of its uncommitted changes become visible

Answer: D — Is rolled back, so none of its uncommitted changes become visible

D) Correct — an uncommitted transaction abandoned by a lost session is rolled back, preserving all-or-nothing semantics. A) Transactions are scoped to their session and cannot be adopted by another. B) Treating a disconnect as a commit would make partial work durable, which the model forbids. C) Statements inside an explicit transaction do not revert to autocommit after the fact.

Regarding task trees, which statement is accurate?

1. Every task in a tree must share one schedule expression to stay aligned
2. Child tasks may each define their own independent schedule as well
3. A task tree executes its members in parallel regardless of dependencies
4. Only the root task carries a schedule; children run after their predecessors

Answer: D — Only the root task carries a schedule; children run after their predecessors

D) Correct — dependency is expressed with AFTER, so children are triggered by predecessor completion while only the root is scheduled. A) Children have no schedule of their own to align. B) Defining a schedule on a child conflicts with its predecessor relationship. C) Execution honors the declared dependency order rather than running everything at once.

Which is TRUE about Snowflake Streams?

1. Streams copy the changed source rows into a duplicate table
2. Streams track CDC offsets; consuming DML advances the offset
3. Streams provide compute, removing the need for a warehouse
4. Streams capture inserts and deletes but never row updates

Answer: B — Streams track CDC offsets; consuming DML advances the offset

B) Correct — a stream records an offset on the source table and advances it when DML that reads the stream commits. A) Streams store change metadata, not duplicated rows. C) Queries on a stream still need a warehouse. D) With APPEND_ONLY = FALSE, updates are captured.

A query profile shows most execution time in a single Sort operator with local spilling. Aside from resizing, the BEST tuning step is:

1. Add a clustering key matching the sort column so no sort is needed
2. Replace the ORDER BY with a materialized view carrying the sort order
3. Enable the search optimization service so sorts are served from its index
4. Reduce the rows or columns entering the sort by filtering and projecting earlier

Answer: D — Reduce the rows or columns entering the sort by filtering and projecting earlier

D) Correct — sorts are memory-bound, so cutting cardinality and width before the sort attacks the cause directly. A) Clustering influences pruning and data locality but does not remove a required global sort. B) Materialized views do not guarantee a stored global ordering that eliminates the operator. C) Search optimization targets selective lookups, not sorting.

A session runs several DML statements without an explicit BEGIN. One statement fails. The result is that:

1. All statements in the session roll back, since the session is one transaction
2. The failed statement's changes remain, to be cleaned up by the next run
3. Snowflake retries the failed statement automatically before rolling back
4. Each statement was its own autocommitted transaction, so earlier ones persist

Answer: D — Each statement was its own autocommitted transaction, so earlier ones persist

D) Correct — with autocommit on, each statement commits independently, so a later failure leaves earlier committed statements in place. A) A session is not implicitly one transaction; that requires an explicit BEGIN. B) A failed statement's own changes are rolled back, not left behind. C) Snowflake does not silently retry failed DML.

VARIANT data type?

Semi-structured (JSON, Avro, XML). Stored efficiently with auto-detected schema. Query with dot/bracket notation.

What are the key restrictions and cost characteristics of a Snowflake materialized view?

A materialized view can reference only a single table, cannot use joins, most window functions, or UDFs, and cannot include ORDER BY. It is maintained automatically by serverless compute, so it incurs both storage and background maintenance credits, and it is available in Enterprise Edition and above.

What is the essential difference between a UDF and a stored procedure in Snowflake?

A UDF returns a value and is called inside a SQL expression; scalar and table UDFs can be written in SQL, JavaScript, Python, Java or Scala. A stored procedure is called with CALL, is used for procedural control flow and administrative work, and may perform DDL and DML as side effects rather than being usable in a SELECT list.

What is a clustering key, and when is defining one justified?

A clustering key is one or more columns Snowflake uses to co-locate related rows across micro-partitions, with automatic clustering maintaining it in the background using serverless credits. It is justified on very large tables (typically multi-terabyte) queried with selective filters or joins on that column where pruning is currently poor. Small or frequently fully-rewritten tables do not benefit.