SNOWPRO-CORE Snowpro Core Practice Questions

For processing unstructured data with Python UDFs, Snowflake allows specifying third-party packages (available in the Snowflake Anaconda channel) in the PACKAGES clause of the function definition. The UDF can then read the file from a stage by accepting a scoped URL or file URL as an argument, which provides secure, temporary access to the file for processing within the function. This is the standard, secure, and most integrated way to handle this scenario.

Direct data sharing is only supported between accounts in the same region. To share data across regions, the provider account must first set up database replication to create a read-only copy of the database in the consumer's region (eu-west-1). The share can then be created from this replicated database.

The provider account is responsible for all costs associated with making the data available in the remote region. This includes the storage costs for the replicated database and any data transfer fees incurred for the replication process.

The result cache is a service in the Cloud Services layer that stores the results of previously executed queries for 24 hours. If an identical query is submitted and the underlying data has not changed, Snowflake returns the cached result directly. This operation does not require a virtual warehouse and therefore consumes no compute credits, offering significant performance and cost savings.

This statement is true. According to Snowflake's recommended role-based access control (RBAC) hierarchy, the SYSADMIN role is responsible for object creation and management within the account. It has the permissions to create warehouses, databases, and other objects. The ACCOUNTADMIN role manages account-level settings, and the SECURITYADMIN role manages users and roles.

Snowflake provides metadata pseudocolumns (METADATA$FILENAME, METADATA$FILE_ROW_NUMBER, etc.) that can be queried directly from staged files during a COPY operation. To load this metadata into the target table, you use a SELECT statement within the COPY INTO command to specify the columns from the source file and the metadata columns to be loaded.

Transient tables have a Time Travel retention period of 0 or 1 day but are not protected by Fail-safe. Since the account's retention period is 1 day and the table was dropped within this window, the UNDROP TABLE command is the correct and most direct way to restore it. Contacting support for Fail-safe is incorrect because transient tables are excluded from Fail-safe. Restoring from a clone is not possible as a clone must be made before the drop. Re-running the ETL job would work but is not the most direct recovery method.

Snowflake's bulk loader works most efficiently with larger, compressed files. Aggregating many small files into fewer large ones (100MB-250MB is a common recommendation) reduces the overhead of file metadata processing and allows for better parallelization.

Data loading is a compute-intensive task. Using a larger warehouse provides more threads to process files in parallel, which significantly improves performance for bulk loads. The warehouse can be scaled down after the operation is complete to manage costs.

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.

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.

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.