SNOWPRO-CORE Sample Questions & Answers

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.

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.

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.

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).

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.

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.

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.

This statement is true. The purpose of VALIDATION_MODE is to parse the data files and return any errors without actually loading the data. It's a dry run to check for issues before committing to the load. No rows are inserted into the target table when this mode is used.

Data spilling occurs when an operation (like a large sort or join) requires more memory than is available on the virtual warehouse worker nodes. To proceed, Snowflake temporarily writes, or 'spills', the intermediate data to local SSD storage. This is a very slow operation compared to in-memory processing and is a clear indicator that the warehouse may be undersized for the complexity of the query.

This solution correctly maps each requirement to the most appropriate Snowflake feature. A Row Access Policy is designed for requirement #1, filtering rows based on the user's role and a mapping table. Dynamic Data Masking is the perfect fit for requirement #2, applying column-level security based on role. The ACCESS_HISTORY view in the ACCOUNT_USAGE schema is the standard for requirement #3, providing detailed audit logs. This combination is scalable, secure, and avoids the management overhead of multiple views.