A financial services company uses a transient table named STG_CUSTOMER_PII to temporarily process sensitive data. A junior administrator accidentally executes a DROP TABLE command on this table. The data retention period for the account is set to the default of 1 day. What is the most direct and appropriate method to recover this table and its data, assuming the drop occurred less than an hour ago?
Answer and explanation
Correct answer: B
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.
Question 2
Multiple answers
A data engineering team is loading a large dataset composed of 10,000 small JSON files (each under 10MB) from an S3 bucket into a Snowflake table. The initial load process using a COPY INTO command is performing poorly. Which TWO actions are recommended best practices to improve the performance of this bulk loading operation? (Select TWO)
Answer and explanation
Correct answers: B, D
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.
Question 3
A data analyst needs to transform and flatten a deeply nested JSON structure stored in a VARIANT column named RAW_PAYLOAD. The structure contains an array of user objects, and each user object contains an array of address objects. The goal is to produce a flat table with one row per address for each user.
Which combination of functions is required to achieve this multi-level flattening?
Answer and explanation
Correct answer: B
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.
Question 4
True or False: When a virtual warehouse is resized from a Small to a Large, any queries currently running on the warehouse are immediately migrated to the new, larger resources and will complete faster.
Answer and explanation
Correct answer: B
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.
Question 5
Case Study:
A global e-commerce company, ShopSphere, is centralizing its analytics on Snowflake. They have three distinct user groups with different workload patterns. The Business Intelligence (BI) team runs complex, long-running analytical queries against large fact tables, primarily during business hours (9 AM - 5 PM). The Data Science (DS) team runs intensive machine learning training jobs using Snowpark, which are unpredictable in timing and duration. The Finance team runs lightweight, ad-hoc queries for daily reporting throughout the day.
ShopSphere's primary goals are to ensure workload isolation to prevent query contention, maintain predictable performance for the BI team, and manage costs effectively. The current setup uses a single, large multi-cluster warehouse for all three teams, which has led to performance degradation for the BI team when DS jobs are running, and higher-than-expected costs due to the warehouse running constantly.
The Director of Analytics has tasked you with redesigning the virtual warehouse strategy to meet the company's goals. Which of the following strategies provides the best combination of performance isolation, predictability, and cost-efficiency for ShopSphere?
Answer and explanation
Correct answer: B
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.
Question 6
A security administrator needs to configure a network policy that allows access from a specific range of corporate IP addresses but also permits the Snowflake Partner Connect services to access the account. Which statement accurately describes how to achieve this?
Answer and explanation
Correct answer: C
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.
Question 7
A data provider shares a secure view from their database with a data consumer. The consumer's query against the secure view is running much slower than expected. The provider confirms the underlying query for the view is highly optimized on their end. What is the most likely reason for the performance degradation on the consumer side?
Answer and explanation
Correct answer: B
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.
Question 8
A developer is using SnowSQL to load a local CSV file named data.csv into a user stage. Which command should be used for this purpose?
Answer and explanation
Correct answer: C
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.
Question 9
What is the primary function of the Cloud Services layer in the Snowflake architecture?
graph TD
subgraph Snowflake_Architecture
A[Cloud Services Layer] --> B[Query Processing Layer]
A --> C[Database Storage Layer]
B --> C
end
subgraph A_Responsibilities
direction LR
A1[Authentication] --> A2[Access Control]
A2 --> A3[Query Optimization]
A3 --> A4[Metadata Management]
end
A --- A_Responsibilities
Answer and explanation
Correct answer: C
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).
Question 10
A data pipeline uses a stream object to capture changes on a source table. A downstream task consumes the data from the stream within an explicit transaction (BEGIN/COMMIT). If the task fails after consuming the stream data but before the transaction commits, what is the state of the stream?
Answer and explanation
Correct answer: C
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.
