Data transformations: free practice questions

Option A is correct. Zero-copy cloning works by sharing the existing micro-partitions between the source and clone — no data is physically copied at clone creation time. When new data is inserted into PROD after the clone is created, those inserts produce new micro-partitions that belong exclusively to PROD. The clone retains a reference to the original shared partitions and its storage cost only grows when data within the clone itself is modified (copy-on-write). Option B is incorrect because zero-copy cloning does NOT duplicate storage at creation time; that is the fundamental value proposition. Option C is incorrect because clones are independent objects — changes made to PROD after cloning are not visible in the clone, and vice versa (with the exception of shared unchanged partitions). Option D is incorrect because Snowflake supports zero-copy cloning at the table, schema, and database levels.

Option A is correct. When a stored procedure is defined with EXECUTE AS CALLER, it runs within the security context of the calling user. All privilege checks — including SELECT, INSERT, and other object-level permissions — are evaluated against the caller's active role. If the caller lacks the required privileges on any referenced object, the procedure will raise an error. Option B is incorrect because it describes EXECUTE AS OWNER behavior, where the procedure runs with the owner's privileges regardless of who calls it. Option C is the opposite of the truth; EXECUTE AS CALLER means standard privilege checks apply to the caller, so row-level security and access controls enforced by the caller's role are fully in effect. Option D is incorrect because stored procedures never automatically elevate a session role to SYSADMIN or any other role; role elevation requires explicit USE ROLE within the procedure body, which would be governed by role hierarchy rules.

Option B is correct. Snowflake's colon (:) notation is used to traverse VARIANT paths, and dot notation can be chained for nested keys, making RAW_DATA:order.total_amount the idiomatic way to access nested JSON. Casting with ::FLOAT extracts a usable numeric value. Option A uses bracket notation which is valid for accessing array elements or keys with special characters, but chaining brackets like ['order']['total_amount'] on a VARIANT is not standard Snowflake SQL — the colon notation is required to start a path traversal. Option C would be correct if RAW_DATA were a STRING (requiring PARSE_JSON first), but since the column is already VARIANT, wrapping it in PARSE_JSON is redundant and actually causes an error. Option D is incorrect because FLATTEN is a table function used to explode arrays into rows; it does not accept dot-path strings to extract scalar values.

Option A is correct. In Snowflake, all newly created tasks start in a SUSPENDED state by default. The engineer must explicitly run ALTER TASK PROCESS_SALES RESUME before the task will execute on its schedule. This is one of the most common operational mistakes with Snowflake tasks. Option B is incorrect because both warehouse-based and serverless tasks can reference streams; streams can be used as a trigger condition (via WHEN SYSTEM$STREAM_HAS_DATA()) or simply queried within any task. Option C is incorrect because '10 MINUTE' (or '10 MINUTES') is a valid interval syntax for the SCHEDULE parameter alongside CRON expressions. Option D is incorrect because virtual warehouse tasks and serverless tasks are both valid compute options for tasks that process streams; the choice affects cost model and auto-scaling, not the ability to reference streams.

Option A is correct. Snowflake hosts a curated Anaconda package repository, and third-party libraries like pandas are made available to Snowpark stored procedures (and UDFs) by listing them in the PACKAGES clause of the CREATE PROCEDURE statement. Snowflake handles the dependency installation in the sandbox environment automatically. Option B is incorrect for pandas specifically — uploading a wheel file via IMPORTS is used for custom or private Python packages not available in the Anaconda channel. Pandas is available in the channel and does not need to be manually staged. Option C is incorrect because stored procedures run in a managed Snowflake sandbox environment; there is no shell access to run pip install commands inside the procedure body. Option D is incorrect because Snowpark Python stored procedures explicitly support a wide range of third-party libraries (hundreds of packages) through the Snowflake Anaconda integration.