Free SNOWPRO-CORE sample questions

Correct answer: Two sequential `LATERAL FLATTEN` clauses in the `FROM` part of the query.

To flatten a nested structure with an array inside another array, you must use multiple `LATERAL FLATTEN` clauses. The first `FLATTEN` expands the outer array (users), and the second `FLATTEN` operates on the output of the first to expand the inner array (addresses). This creates the desired Cartesian product, resulting in one row per address. A single flatten function cannot handle multi-level arrays in one step. `PARSE_JSON` is for converting strings to VARIANT, not for flattening. `ARRAY_FLATTEN` is a different function for arrays.

Correct answer: False

This statement is false. When a virtual warehouse is resized, the existing server resources continue to be used by any queries that are already running. The newly provisioned, larger resources are used only for queries that are queued or submitted after the resize operation completes. Running queries are not migrated.

Correct answer: Create three separate virtual warehouses: one Medium multi-cluster warehouse for BI, one Small warehouse for Finance, and one Snowpark-optimized warehouse for DS. Set aggressive auto-suspend timers on all.

This strategy provides the best workload isolation by dedicating separate compute resources to each team, preventing contention. The multi-cluster warehouse for BI handles concurrency, the Snowpark-optimized warehouse is tailored for DS workloads, and the small warehouse is cost-effective for Finance's ad-hoc queries. Aggressive auto-suspend timers ensure that warehouses shut down when not in use, directly addressing the cost management goal. This is the most robust and aligned solution to the stated requirements.

Correct answer: Retrieve the Partner Connect IP address ranges by calling the `SYSTEM$ALLOWLIST` function and include both these ranges and the corporate IP addresses in the `ALLOWED_IP_LIST`.

To allow access from both internal corporate IPs and Snowflake services like Partner Connect, you must explicitly add the IP addresses for both to the `ALLOWED_IP_LIST`. The correct way to get the current IP address ranges for Snowflake services is by calling the `SYSTEM$ALLOWLIST` function. The result of this function should be combined with the corporate IP list when creating the network policy.

Correct answer: The consumer's query includes additional filters or joins that prevent the Snowflake optimizer from pushing computations down to the provider's side.

Secure views are designed to prevent the consumer from seeing the underlying query logic. This security measure can limit the Snowflake optimizer's ability to rearrange operations. If the consumer adds filters or joins to the query on the secure view, the optimizer may be forced to pull a larger-than-necessary dataset from the provider's side and then apply the filters on the consumer's side, leading to poor performance. This is a known trade-off for the enhanced security of secure views.

Correct answer: PUT file:///path/to/data.csv @~;

The `PUT` command is used in SnowSQL to upload files from a local file system to a Snowflake stage. The `@~` syntax is a shorthand for the current user's stage. `COPY INTO` is used to load data from a stage into a table, not to upload a file to a stage.

Correct answer: To coordinate the overall system, managing sessions, security, metadata, and query optimization.

The Cloud Services layer is the 'brain' of Snowflake. It is a collection of services that coordinate activities across the platform. Its key responsibilities include authentication, infrastructure management, access control, metadata management, and query parsing and optimization. It does not execute the queries (that's the Query Processing layer) or store the data (that's the Database Storage layer).

Correct answer: The stream's offset does not advance, and the same changes will be visible in the next query.

A stream's offset only advances when the DML transaction that consumes its data commits successfully. If the transaction is rolled back or fails before committing, the offset remains unchanged. Therefore, the change data will still be available in the stream for the next consumption attempt, ensuring at-least-once processing semantics.

Correct answers: TRANSIENT; TEMPORARY; PERMANENT

Transient tables persist until explicitly dropped but do not have a Fail-safe period, which helps reduce storage costs.

Temporary tables exist only within the session in which they were created and are automatically dropped at the end of the session.

Permanent tables are the default table type. They persist until explicitly dropped and are protected by both Time Travel and Fail-safe.

Correct answer: Dynamic Data Masking

Dynamic Data Masking is the ideal feature for this use case. A masking policy can be applied to the `SSN` column that returns a masked value (e.g., '***-**-****') for users with the junior analyst role, while authorized roles can see the plain text. The underlying data is not changed, so operations like joins still work correctly on the original values. A Row Access Policy filters rows, and a Secure View would require creating a separate object and managing grants to it.