Free Snowflake SnowPro Associate: Platform (SOL-C01) Sample Questions

Correct answer: D — `CREATE SCHEMA DEV_V2 CLONE PROD AT(TIMESTAMP => '... 08:00:00');`

• A. Incorrect. The `COPY OF` syntax is not a valid clause for the `CREATE SCHEMA` command in Snowflake. This statement would result in a SQL syntax error.
• B. Incorrect. While `CLONE` is the correct operation, this command omits the time-travel clause (`AT` or `BEFORE`). As a result, it would create a clone of the `PROD` schema in its *current* state, not its state at the specified time yesterday.
• C. Incorrect. The `FROM` keyword is not a valid part of the `CREATE SCHEMA` syntax in Snowflake for copying or cloning another schema. This command would result in a SQL syntax error.
• D. Correct. This command correctly uses the `CLONE` keyword to perform an efficient, metadata-only (zero-copy) clone. Crucially, it includes the `AT(TIMESTAMP => ...)` clause, which leverages Snowflake's Time Travel feature to create the `DEV_V2` schema as an exact copy of the `PROD` schema as it existed at the specified point in time.

Correct answer: C — Highlight the entire text of the second `SELECT` statement, then click the 'Run' button

• A. Incorrect. While commenting out the first statement using `--` or `/* */` would prevent it from executing, this approach requires modifying the original script and is less efficient than selective execution methods available in Snowsight. This method also requires additional steps to uncomment the statement later if needed.
• B. Incorrect. Simply placing the cursor within a statement does not isolate that specific statement for execution in Snowsight worksheets. When you click 'Run' with just cursor placement, Snowsight may execute the entire worksheet or multiple statements, which would include the preceding DDL statement that the analyst wants to avoid running.
• C. Correct. In Snowsight worksheets, highlighting or selecting the entire text of a specific SQL statement allows you to execute only that selected portion when you click the 'Run' button. This is the most efficient method as it provides precise control over which statement executes without requiring script modification, copying to new worksheets, or other workarounds, and is a built-in feature designed specifically for this use case.
• D. Incorrect. While copying the statement to a new worksheet would work, this approach is inefficient as it requires creating additional worksheets, managing multiple worksheet contexts, and potentially losing the original script structure. Snowsight provides more direct methods for selective statement execution within the same worksheet.

Correct answer: B — `RESULT_SCAN()`

• A. Incorrect. `LAST_QUERY_ID()` is a function that returns the query ID of a previously executed query in the current session (e.g., `SELECT LAST_QUERY_ID(-2);` returns the ID of the second-to-last query), but it does not return the actual query results. This function is typically used as input to `RESULT_SCAN()` to retrieve the cached results of that specific query.
• B. Correct. `RESULT_SCAN()` is the built-in function that allows you to query the cached results of a previous query without re-executing it. It accepts a query ID as input and returns the result set from that query, which Snowflake stores for 24 hours. You can use it with `LAST_QUERY_ID()` like `SELECT * FROM TABLE(RESULT_SCAN(LAST_QUERY_ID()));` or directly with a query ID to access previous results within the same session.
• C. Incorrect. `QUERY_HISTORY()` is a table function that returns metadata about queries executed within a specified time range, including query text, execution time, and status information. It provides information about query execution history but does not return the actual result sets from those queries. The `QUERY_HISTORY` family includes functions like `QUERY_HISTORY_BY_SESSION()` and `QUERY_HISTORY_BY_USER()` for different filtering dimensions.
• D. Incorrect. `GET_RESULT_SET()` is not a valid Snowflake built-in function. This appears to be a distractor option that does not exist in Snowflake's function library. The correct function for accessing previous query results is `RESULT_SCAN()`.

Correct answer: C — Run

• A. Incorrect. The `View` permission in Snowsight dashboards allows users to see the dashboard and its visualizations but does not provide the ability to interact with the data, such as drilling down into charts, filtering data dynamically, or refreshing tiles. This permission level is too restrictive for analysts who need to interact with the data while viewing it.
• B. Incorrect. The `Edit` permission allows users to modify the dashboard structure, including adding, removing, or modifying tiles, changing queries, updating filters, and altering the dashboard layout. This level of access exceeds the requirements since the team lead specifically wants to prevent analysts from modifying the dashboard's structure, tile queries, or filters.
• C. Correct. The `Run` permission in Snowsight dashboards provides the ideal balance for this scenario, allowing users to view the dashboard, interact with the data (such as drilling down, applying dynamic filters, and refreshing data), and execute the underlying queries without being able to modify the dashboard's structure, tile configurations, or saved filters. This permission level enables analysts to perform their analytical work while maintaining the integrity of the dashboard design.
• D. Incorrect. The `Manage` permission grants full administrative control over the dashboard, including the ability to modify structure, queries, filters, share the dashboard with others, and delete the dashboard entirely. This is the highest level of permission and far exceeds what is needed for analysts who should only be able to view and interact with the data without making structural changes.

Correct answer: B — Use a separate, appropriately sized virtual warehouse for each distinct workload

• A. Incorrect. While a single large multi-cluster warehouse can handle high concurrency, mixing different workload types (operational ELT, analytical BI, and ad-hoc queries) on the same warehouse prevents independent scaling and optimal resource allocation. The documentation specifically states that for mixed hybrid workloads with operational and analytical components, it is beneficial to separate them into different warehouses to enable independent scaling.
• B. Correct. The documentation explicitly recommends isolating workloads in separate warehouses to enable independent scaling, especially for mixed hybrid workloads with operational and analytical components. Each workload type (nightly ELT, high-concurrency BI dashboard, ad-hoc analysis) has different performance requirements and usage patterns, making separate warehouses the optimal approach for both performance and cost management.
• C. Incorrect. While the documentation states that an `X-Small` warehouse is sufficient for many operational workloads, running three distinct workload types with different requirements on a single small warehouse would create resource contention and suboptimal performance. The documentation recommends using multi-cluster warehouses or separate warehouses for different workload patterns, not relying solely on the query optimizer to manage mixed workload types.
• D. Incorrect. Creating separate databases addresses data isolation but does not solve compute resource allocation challenges. Databases are logical containers for data objects, while virtual warehouses provide the compute resources for query execution. The performance and cost management issues described in the question require warehouse-level separation, not database-level separation.

Correct answer: B — By allowing virtual warehouses to `AUTO_SUSPEND` when idle, consuming no compute credits during inactive periods

• A. Incorrect. Snowflake uses a consumption-based pricing model where you pay for compute credits used by virtual warehouses, not a flat monthly fee for unlimited compute. This pay-per-use model is actually what makes Snowflake cost-effective for intermittent workloads, as you only pay for what you consume.
• B. Correct. Snowflake's virtual warehouses support `AUTO_SUSPEND` functionality, which automatically suspends warehouses after a specified period of inactivity (configurable from 60 seconds to several hours). When suspended, warehouses consume zero compute credits while maintaining all cached data, making this ideal for the described usage pattern with 20 hours of inactivity. The warehouses can `AUTO_RESUME` instantly when new queries arrive.
• C. Incorrect. While Snowflake does use columnar storage and compression to optimize storage costs, this addresses storage pricing rather than compute costs. For the described workload pattern with significant idle time, the primary cost optimization comes from compute resource management, not storage compression.
• D. Incorrect. Snowflake does not offer traditional reserved capacity or long-term compute reservation pricing models like some cloud providers. Instead, Snowflake's cost-effectiveness for intermittent workloads comes from its ability to scale compute resources up and down dynamically and suspend warehouses when not in use.

Correct answer: B — Add the required libraries to the notebook's environment configuration, which is then automatically resolved by the Snowflake Anaconda channel.

• A. Incorrect. While `!pip install` is a common approach in traditional Jupyter notebooks, Snowflake Notebooks manages Python packages differently through its integration with the Snowflake Anaconda channel. The documentation specifically states that Snowflake Notebooks manages the Python packages used in your notebook environment and that you can import third-party packages listed in the Snowflake Anaconda channel, not through direct pip installation commands.
• B. Correct. According to the documentation, Snowflake Notebooks manages Python packages through its integration with the Snowflake Anaconda channel, and you can import third-party packages listed in this channel by adding them to the notebook's environment configuration. The documentation states that after your organization administrator accepts the terms, you can import libraries to use in Snowflake Notebooks, and these packages are automatically resolved through the managed environment system.
• C. Incorrect. The documentation indicates that individual data scientists can add packages to their notebooks without requiring administrator pre-installation at the account level. The documentation states that 'packages that you add to a notebook are available only to that notebook' and recommends adding packages 'at the top of your notebook at the start of your analysis,' indicating user-level control rather than administrator-only installation.
• D. Incorrect. While Snowflake Notebooks do come with pre-installed packages including common libraries like `pandas`, `numpy`, and `snowflake-snowpark-python`, the documentation does not indicate that all popular ML libraries like `xgboost`, `imbalanced-learn`, and `statsmodels` are included by default. The documentation specifically mentions the need to import third-party packages from the Snowflake Anaconda channel for additional libraries beyond the pre-installed ones.

Correct answers: A, B — The notebook is waiting for the virtual warehouse to provision or resume.; The Python kernel is currently busy executing a previous cell in the same notebook.

• A. Correct. When a cell shows 'Queued' status, it often indicates that the notebook is waiting for compute resources to become available, specifically when the assigned virtual warehouse needs to provision (start up) or resume from a suspended state. This is a common scenario when warehouses are configured with auto-suspend policies and need time to become active before executing queries.
• B. Correct. The 'Queued' status occurs when the Python kernel is busy processing another cell in the same notebook, as notebooks typically execute cells sequentially within the same kernel session. The current cell must wait in the queue until the previous cell completes execution, which is standard behavior for notebook environments.
• C. Incorrect. Invalid SQL syntax would result in an immediate error status rather than a 'Queued' status. When syntax errors occur, Snowflake's SQL parser quickly identifies the issue and returns an error message without placing the query in a queue for execution.
• D. Incorrect. Insufficient privileges would cause an immediate access denied error rather than a 'Queued' status. Snowflake's security layer validates permissions before placing queries in the execution queue, so privilege issues are detected and reported immediately upon submission.
• E. Incorrect. A timed-out notebook session would typically show a disconnected or error status rather than 'Queued'. Session timeouts prevent new executions from being submitted to the queue entirely, and users would need to reconnect or restart their session before cells could enter a queued state.

Correct answer: C — `SELECT * FROM my_table WHERE id IN (SELECT value FROM TABLE(FLATTEN(INPUT => :my_list)));`

• A. Incorrect. The syntax `:my_list` attempts to bind a Python list directly to the `IN` clause without proper conversion. Snowflake's parameter binding with the colon syntax (`:parameter`) expects scalar values or properly formatted arrays, but a Python list cannot be directly used in an `IN` clause without conversion to individual expressions or table format.
• B. Incorrect. Similar to option A, wrapping `:my_list` in parentheses `(:my_list)` still attempts to bind a Python list directly to the `IN` clause. The parentheses don't resolve the fundamental issue that Snowflake cannot directly expand a Python list into the comma-separated values required by the `IN` operator without proper transformation.
• C. Correct. This syntax uses the `FLATTEN` function with `TABLE()` to convert the Python list into a table format that can be used with a subquery in the `IN` clause. The `FLATTEN(INPUT => :my_list)` function transforms the array into rows with a `value` column, and `SELECT value FROM TABLE(FLATTEN(...))` creates a proper subquery that returns individual values for the `IN` operation, following Snowflake's recommended approach for handling arrays in SQL.
• D. Incorrect. This syntax `{{ ','.join(map(str, my_list)) }}` appears to use template/macro syntax (double curly braces) which is not standard SQL or Snowflake parameter binding. This would be string interpolation that could lead to SQL injection vulnerabilities and is not a secure method for parameterizing queries in Snowflake's SQL environment.

Correct answer: B — Combine the queries into a single SQL script using a `BEGIN...END` block.

• A. Incorrect. Simply highlighting multiple queries and clicking 'Run' does not guarantee sequential execution or background processing in Snowsight. This approach would execute queries but may not provide the background execution capability needed for the analyst to close their browser and check results later.
• B. Correct. Using a `BEGIN...END` block in Snowflake Scripting allows the analyst to group all five queries into a single executable unit that runs sequentially. As documented, `BEGIN...END` blocks can contain multiple SQL statements that execute in order, and when executed as a single script, it can run in the background allowing the analyst to close their browser and return later to check results. The syntax `BEGIN <statement>; [<statement>; ...] END;` ensures all queries execute as one cohesive unit.
• C. Incorrect. While running queries asynchronously might allow background execution, this approach does not guarantee sequential execution of the five queries. Asynchronous execution typically means queries run independently and potentially in parallel, which contradicts the requirement that the queries must be executed sequentially for the month-end report.
• D. Incorrect. While creating a stored procedure would allow for sequential execution and could be called later, this approach requires additional steps of procedure creation and management. The `BEGIN...END` block provides a more direct solution for this specific use case without the overhead of stored procedure creation and maintenance.

Correct answers: B, D — The global Query History page shows all queries executed in your Snowflake account over the last 14 days, accessible via Monitoring » Query History.; Within a worksheet, you can view the query history specifically for queries that have been run in that particular worksheet using the `View query history` feature.

• A. Incorrect. While worksheets do have query history functionality, the documentation indicates that query history is managed at the account level through the global Query History page accessible via `Monitoring » Query History`, not as independent histories per worksheet. The worksheet history feature shows queries run within that specific worksheet, but it's part of the broader account-level query history system.
• B. Correct. According to the documentation, 'The Query History page lets you explore queries executed in your Snowflake account over the last 14 days' and is accessed by navigating to `Monitoring » Query History` in Snowsight. This global page provides comprehensive visibility into all account queries, with filtering capabilities to view specific subsets of query activity.
• C. Incorrect. The documentation does not indicate that deleting a worksheet removes queries from the global Query History page. Query history is maintained at the account level for 14 days regardless of worksheet status. The global Query History page tracks all executed queries independently of individual worksheet lifecycle management.
• D. Correct. The documentation explicitly states that 'Within a worksheet, you can see the query history for queries that have been run in that worksheet' using the `View query history` feature. This functionality became generally available and allows users to review queries and results specific to each worksheet context while maintaining connection to the broader query history system.
• E. Incorrect. The documentation specifies that query history is retained for 14 days at the account level, not 24 hours. Additionally, the 90-day retention mentioned in the documentation refers to worksheet version history and stored query results, which is different from the basic query history retention period of 14 days.

Correct answers: B, D — They prevent users from seeing the view's underlying definition via `GET_DDL` or the `VIEWS` information schema view (unless the user is the owner).; Snowflake's query optimizer will not 'push down' predicates from the user's query into the view definition, preventing users from inferring underlying data values.

• A. Incorrect. This describes an owner's rights view, which is a separate concept. A secure view's primary purpose is to hide its definition and prevent optimizer-based data inference, not to change the privilege context of its execution.
• B. Correct. According to Snowflake documentation, the definition of a secure view is only exposed to authorized users (i.e., the role that owns the view). This is a primary security feature to protect proprietary or sensitive business logic from being viewed by unauthorized users through commands like `GET_DDL()` or by querying `INFORMATION_SCHEMA.VIEWS`.
• C. Incorrect. This describes the behavior of a materialized view. Secure views are logical constructs and do not pre-compute or store their results. In fact, secure views can sometimes execute more slowly than non-secure views because certain query optimizations are disabled to protect the underlying data.
• D. Correct. For secure views, Snowflake's optimizer does not reorder predicates. This prevents users from potentially inferring information about the underlying data by observing query performance or errors that might arise from predicate pushdown. This behavior is a crucial aspect of how secure views protect data privacy.
• E. Incorrect. Secure views and row access policies are independent security features in Snowflake. A secure view can be created on any table, regardless of whether a row access policy is applied. While they can be used together for a multi-layered security approach, one is not a prerequisite for the other.

Correct answers: B, C, D — Create a new virtual warehouse.; Apply a masking policy to a column in an existing table.; Create a new user and grant them a default role.

• A. Incorrect. Defining or changing a clustering key on a table must be done using the `ALTER TABLE ... CLUSTER BY` SQL command. There is no graphical interface in Snowsight to perform this action.
• B. Correct. Snowsight provides a dedicated user interface for creating and configuring virtual warehouses. This can be done by navigating to Admin > Warehouses and using the '+ Warehouse' button, which opens a dialog to specify all warehouse properties without needing to write a `CREATE WAREHOUSE` statement.
• C. Correct. While this can be done with SQL, Snowsight also provides a direct UI method. By navigating to a table's details, selecting the 'Columns' tab, and choosing a specific column, an administrator can use the interface to select and apply an existing masking policy.
• D. Correct. Snowsight allows for the creation of new users through a graphical interface under Admin > Users & Roles. During the user creation process, an administrator can assign a default role directly within the UI form, fulfilling both actions without writing `CREATE USER` or `GRANT ROLE` commands.
• E. Incorrect. While you can perform basic schema management like creating a schema via the UI, transferring ownership of any object requires executing a `GRANT OWNERSHIP` SQL statement. This action is not available as a direct button or menu option in the Snowsight interface.

Correct answers: A, B — `MANAGE GRANTS`; `OWNERSHIP`

• A. Correct. The global `MANAGE GRANTS` privilege allows a role to grant privileges on any object to other roles. According to the documentation, only the `SECURITYADMIN` and `ACCOUNTADMIN` system roles have the `MANAGE GRANTS` privilege by default, but it can be granted to custom roles. This privilege provides comprehensive access control management capabilities across all database objects.
• B. Correct. The `OWNERSHIP` privilege on a specific object allows the owning role to grant privileges on that object to other roles. `OWNERSHIP` is automatically granted to the role that created the object and can be transferred using the `GRANT OWNERSHIP` command. When granting privileges on schema objects like tables, the role must also have the `USAGE` privilege on the parent database and schema.
• C. Incorrect. The `USAGE` privilege allows access to use an object but does not provide the authority to grant privileges on that object to other roles. While `USAGE` is required on parent databases and schemas when granting privileges on schema objects, it is not sufficient by itself to grant privileges. `USAGE` is typically the minimum privilege needed to access and use database objects.
• D. Incorrect. `MODIFY` is not a standard Snowflake privilege related to granting access control permissions on database objects. The documentation specifies that either `MANAGE GRANTS` (global privilege) or `OWNERSHIP` (object-specific privilege) are the privileges required to grant permissions on objects to other roles.

Correct answer: B — `SELECT` on the source table `other_db.prod.source_table`

• A. Incorrect. While `OWNERSHIP` provides full control over an object, it is not required for cloning operations. The `SELECT` privilege is sufficient to read the source table's metadata and data for cloning purposes, making ownership unnecessarily restrictive for this operation.
• B. Correct. To clone a table using `CREATE TABLE ... CLONE`, the user must have `SELECT` privilege on the source table. This privilege allows Snowflake to read the source table's structure, data, and metadata to create an independent copy with the same data at the point in time when the clone operation executes.
• C. Incorrect. The `USAGE` privilege on a warehouse is required for compute operations like running queries, but table cloning using `CREATE TABLE ... CLONE` is a metadata operation that doesn't require active warehouse compute resources. The cloning process operates at the storage layer using Snowflake's zero-copy cloning technology.
• D. Incorrect. There is no `MODIFY` privilege in Snowflake's access control system. The standard object privileges include `SELECT`, `INSERT`, `UPDATE`, `DELETE`, `TRUNCATE`, `REFERENCES`, and `OWNERSHIP`, but `MODIFY` is not a valid privilege type in Snowflake's security model.

Correct answer: B — To provide a data dictionary with metadata about all objects within that specific database

• A. Incorrect. The `INFORMATION_SCHEMA` does not store user-defined table data or application workload data. User-defined tables and views are stored in user-created schemas within the database, while `INFORMATION_SCHEMA` is a built-in, read-only schema that contains only metadata views and table functions about database objects.
• B. Correct. According to the documentation, `INFORMATION_SCHEMA` is automatically created in every database and serves as the "Data Dictionary" consisting of system-defined views and table functions that provide extensive metadata information about objects created in your account. It contains views for all objects in the database as well as account-level objects like roles, warehouses, and databases, plus table functions for historical and usage data.
• C. Incorrect. Query result caching is handled by Snowflake's result cache feature, not by `INFORMATION_SCHEMA`. The `INFORMATION_SCHEMA` is a metadata repository containing views and table functions that describe database objects, schemas, tables, columns, and account-level information, but does not store or manage cached query results.
• D. Incorrect. Virtual warehouse configurations and credit usage are managed through Snowflake's compute layer and billing system, not through `INFORMATION_SCHEMA`. While `INFORMATION_SCHEMA` may contain some metadata views about warehouses, it does not manage warehouse configurations or track credit consumption - that functionality is handled by Snowflake's resource management and billing systems.

Correct answer: D — `ALTER DATABASE DEV_DB SET DATA_RETENTION_TIME_IN_DAYS = 5;`

• A. Incorrect. The `ALTER ACCOUNT SET DATA_RETENTION_TIME_IN_DAYS = 5` command would change the account-level default data retention period to 5 days for all databases and objects across the entire account. This is too broad and would affect all databases, not just the specific `DEV_DB` database that the developer wants to configure.
• B. Incorrect. The parameter `DEFAULT_SCHEMA_RETENTION_TIME` does not exist in Snowflake. The correct parameter for setting data retention time is `DATA_RETENTION_TIME_IN_DAYS`, which can be applied at the account, database, schema, or table level to control Time Travel retention periods.
• C. Incorrect. While `CREATE DATABASE DEV_DB WITH DATA_RETENTION_TIME_IN_DAYS = 5` is valid syntax for setting retention during database creation, the question states that the database has already been created. This command would attempt to create a new database rather than modify the existing `DEV_DB` database's retention settings.
• D. Correct. The `ALTER DATABASE DEV_DB SET DATA_RETENTION_TIME_IN_DAYS = 5` command sets the data retention period specifically for the existing `DEV_DB` database to 5 days. According to the documentation, when a retention period is specified for a database, it is inherited by default for all objects (including schemas) created within that database, which satisfies the requirement for new schemas to automatically have 5 days retention.

Correct answers: A, B, C — Use `SHOW TABLES IN SCHEMA LEGACY_DB.FINANCE_V1;` to check for existing tables and their metadata.; Query `LEGACY_DB.INFORMATION_SCHEMA.TABLES` to verify the `ROW_COUNT` and identify all tables in the schema.; Clone the schema using `CREATE SCHEMA FINANCE_V1_BACKUP CLONE LEGACY_DB.FINANCE_V1;` to create a backup copy.

• A. Correct. The `SHOW TABLES IN SCHEMA` command provides essential information about all tables within the specified schema, including table names, database, schema, kind, and other metadata. This allows the DBA to inventory what objects exist before dropping the schema, which is a critical step in preventing accidental data loss.
• B. Correct. Querying the `INFORMATION_SCHEMA.TABLES` view provides comprehensive metadata about tables including `ROW_COUNT`, `TABLE_TYPE`, `CREATED`, and other important details. This system view gives the DBA visibility into the data volume and table characteristics within the schema before deletion, helping assess the impact of the drop operation.
• C. Correct. The `CREATE SCHEMA ... CLONE` command creates an exact copy of the source schema including all tables, views, and data at a specific point in time. Cloning the schema before dropping it provides a complete backup that can be used to restore the data if needed, making this the most comprehensive data protection measure.
• D. Incorrect. Transferring ownership using `GRANT OWNERSHIP` changes who controls the schema but does not protect against data loss. While ownership transfer might be part of administrative procedures, it does not create backups or prevent accidental deletion of the schema and its contents.
• E. Incorrect. Setting `DATA_RETENTION_TIME_IN_DAYS` to 0 actually increases the risk of data loss by disabling Time Travel functionality. This parameter controls how long historical data is retained for recovery purposes, so setting it to 0 eliminates the ability to recover dropped objects using `UNDROP` commands or query historical data.

Correct answer: B — It will fail the row with a 'found more columns than expected' error.

• A. Incorrect. By default, without a `FIELD_OPTIONALLY_ENCLOSED_BY` character defined (like a double quote), Snowflake's parser cannot distinguish a comma used as a delimiter from a comma that is part of the data. It will treat every instance of the `FIELD_DELIMITER` character as a field separator.
• B. Correct. The parser will split the row `123,Doe, John,NY` into four distinct fields: '123', 'Doe', ' John', and 'NY'. Assuming the target table is designed for three columns (e.g., ID, NAME, STATE), the `COPY` command will detect a mismatch between the number of fields in the source file (4) and the number of columns in the target table (3). This will cause the command to fail for this row, generating an error like "Number of columns in file (4) does not match that of the corresponding table (3)".
• C. Incorrect. While the parser does split 'Doe, John' into two separate fields, this results in a total of four fields for the row. If the target table does not have exactly four columns, the `COPY` command will not proceed to load the shifted data. Instead, it will fail the row due to the column count mismatch, making option B the correct outcome.
• D. Incorrect. The `ESCAPE` file format option is used for escaping special characters within a field, typically one that is already enclosed. The primary solution for this scenario is to properly enclose fields containing the delimiter by setting the `FIELD_OPTIONALLY_ENCLOSED_BY` option (e.g., to '"'). Using `ESCAPE` alone would not resolve this fundamental parsing issue.

Correct answer: C — The statement will fail due to insufficient privileges on the warehouse.

• A. Incorrect. Snowflake does not automatically use a serverless compute pool for standard DML statements like `INSERT`. Serverless compute is used for specific features like Snowpipe, Replication, Automatic Clustering, and Serverless Tasks, not as a fallback for user-managed warehouses.
• B. Incorrect. A statement is queued if the warehouse is busy or suspended, not because of a user's lack of privileges. Snowflake performs a permissions check before a query is sent to a warehouse. Since the user lacks the necessary `USAGE` privilege, the query will fail this check and will not be queued.
• C. Correct. In Snowflake, to execute any query that requires compute resources (including DML statements like `INSERT`), the user's active role must have the `USAGE` privilege on the active virtual warehouse. Without this privilege, the access control check fails, and the statement is immediately rejected with an 'insufficient privileges' error.
• D. Incorrect. Snowflake does not automatically switch to a different warehouse, such as a system default, if a user lacks privileges on the session's currently active warehouse. The statement will simply fail based on the context of the current session.

Correct answer: D — Rely on the default behavior of `COPY INTO`, as it uses load metadata to prevent reloading the same files.

• A. Incorrect. The `VALIDATION_MODE` option is used to validate data files and the `COPY` statement without actually loading data. It helps identify issues before a real load but does not track or prevent the reloading of files.
• B. Incorrect. The `ON_ERROR` option defines the action to take when errors are encountered *during* a data load for a specific file (e.g., due to data type mismatches). It does not prevent the reloading of files that have already been successfully loaded in previous runs.
• C. Incorrect. While the `information_schema.load_history` view contains a record of loaded files, querying it manually before each run to build an exclusion list is an inefficient, error-prone, and non-automatic process. The question requires an automatic solution.
• D. Correct. By default, Snowflake maintains load metadata for each table, which tracks the files that have been successfully loaded. When a `COPY INTO` command runs, it checks this metadata and automatically skips any files listed in the stage that have already been loaded, ensuring idempotency and preventing data duplication. This history is retained for 64 days.

Correct answer: A — `WHERE v:status IS NOT NULL`

• A. Correct. The colon notation (`v:status`) is the standard and most direct way to query semi-structured data in Snowflake. If the key `status` does not exist in the JSON object, the expression evaluates to a SQL `NULL`. Therefore, `v:status IS NOT NULL` correctly and efficiently identifies all rows where the key exists, regardless of its value (even if the value is a JSON `null`).
• B. Incorrect. The `IS_OBJECT()` function checks if the *value* associated with the `status` key is a JSON object. This is too specific and does not meet the requirement of simply checking for the key's existence. For example, a row where `v` is `{'status': 'active'}` would be incorrectly excluded.
• C. Incorrect. While `GET_PATH(v, 'status') IS NOT NULL` is functionally equivalent to `v:status IS NOT NULL`, the colon path notation is more concise, idiomatic, and generally considered the most direct method for simple key lookups in Snowflake.
• D. Incorrect. The `OBJECT_KEYS(v)` function returns an array of keys. Using the `LIKE` operator on the string representation of this array is inefficient and unreliable, as it could falsely match keys that contain 'status' as a substring (e.g., 'previous_status'). The proper way to check for an element in the array would be `ARRAY_CONTAINS('status'::variant, OBJECT_KEYS(v))`, but this is more complex and less direct than option A.

Correct answer: D — 604800 (7 days)

• A. Incorrect. While 3600 seconds (1 hour) is a valid duration, it is the default expiration time used by the `GET_PRESIGNED_URL` function if no specific expiration time is provided, not the maximum.
• B. Incorrect. 21600 seconds (6 hours) is a valid duration for a pre-signed URL, but it falls short of the maximum allowed time.
• C. Incorrect. 86400 seconds (1 day) is a valid duration for a pre-signed URL, but it is not the maximum limit.
• D. Correct. According to the Snowflake documentation, the maximum expiration time that can be set for a pre-signed URL generated with the `GET_PRESIGNED_URL` function is 604800 seconds, which is equivalent to 7 days.

Correct answer: C — This is not directly possible; a pre-signed URL can only point to a single, existing file.

• A. Incorrect. The `GET_PRESIGNED_URL` function requires the path to a single, specific file as its second argument. Providing a directory path will result in an error, and the function does not have the capability to create a ZIP archive on the fly.
• B. Incorrect. Snowflake does not have a native, built-in function or procedure to zip files that reside in a stage. While it might be theoretically possible to build a complex custom solution using a Snowpark Python stored procedure with the `zipfile` library, this is not a direct, out-of-the-box feature and falls outside the scope of standard built-in capabilities.
• C. Correct. Snowflake's `GET_PRESIGNED_URL` function is designed to generate a temporary URL for a single, pre-existing file within a stage. There is no built-in functionality to dynamically create a ZIP archive from a collection of files in a stage directory and generate a single pre-signed URL for that archive. The required action must be performed outside of Snowflake or via a custom solution; it is not directly possible with standard features.
• D. Incorrect. This query would generate a separate pre-signed URL for each individual file in the subfolder and then concatenate those URLs into a single comma-separated string using `LISTAGG`. This does not fulfill the requirement of creating a ZIP archive or providing a single pre-signed URL that points to such an archive.

Correct answer: B — Increase the warehouse size to MEDIUM.

• A. Incorrect. The AUTO_SUSPEND parameter only controls how long a warehouse remains active after the last query has finished. It has no impact on the performance of a running query and will not address the 'Remote Disk I/O' issue.
• B. Correct. A large amount of 'Remote Disk I/O' indicates data spilling, which happens when the operation requires more memory and local SSD cache than the warehouse provides. The data is 'spilled' to remote storage, which is much slower. Increasing the warehouse size (scaling up) from SMALL to MEDIUM provides more compute resources, including more memory and a larger local SSD cache. This larger cache can hold the necessary data for the MERGE operation, reducing or eliminating spilling and directly addressing the performance bottleneck.
• C. Incorrect. Isolating the COPY and MERGE statements onto separate warehouses is a strategy for workload isolation, preventing different tasks from competing for resources. However, it does not provide more resources to the MERGE statement itself. The performance issue is with the single MERGE query's resource consumption, not with resource contention between the COPY and MERGE.
• D. Incorrect. A multi-cluster warehouse is a scaling-out strategy designed to handle high concurrency by automatically starting additional clusters to run simultaneous queries. A single, long-running MERGE statement will still execute on a single cluster within that warehouse. Therefore, enabling multi-cluster mode will not improve the performance of an individual query that is bottlenecked by its own resource requirements.

Correct answers: B, D, E — Individual query profiles show large amounts of data being spilled to remote disk.; A single, complex data transformation query is taking too long to complete.; The warehouse's local disk cache is being used effectively, with high hit rates, but queries still take a long time due to complex CPU-bound calculations.

• A. Incorrect. A high number of queued queries is the primary indicator of a concurrency bottleneck. The warehouse cannot handle the volume of simultaneous queries. The solution for this is to scale out (add clusters) to increase the number of queries that can be processed in parallel, not to scale up.
• B. Correct. When a query spills data to remote disk, it means the operation requires more memory than is available on the current warehouse size. Scaling up (e.g., from Medium to Large) directly increases the available memory and local SSD for temporary storage, which is the specific solution to reduce or eliminate disk spilling and improve query performance.
• C. Incorrect. A dashboard running many small, concurrent queries that is slow for all users points to a concurrency issue, similar to query queuing. The warehouse is saturated with requests. Scaling out by adding clusters will allow more of these small queries to execute simultaneously, improving the dashboard's responsiveness.
• D. Correct. A single, complex query taking a long time indicates that the query itself is resource-intensive and requires more computational power to complete efficiently. Scaling up provides more CPU cores, memory, and faster I/O to a single query, directly addressing the performance of that individual complex workload.
• E. Correct. A high cache hit rate means that data retrieval from storage is not the bottleneck. If queries are still slow due to 'complex CPU-bound calculations', it means the processing part of the query is the limiting factor. Scaling up the warehouse provides more CPU cores, giving a single query more processing power to execute these calculations faster.

Correct answer: B — `SELECT CASE WHEN source_lang = 'en' THEN log_text ELSE SNOWFLAKE.CORTEX.TRANSLATE(log_text, source_lang, 'en') END AS translated_text FROM chat_logs;`

• A. Incorrect. This query calls `SNOWFLAKE.CORTEX.TRANSLATE()` on ALL rows including those already in English, which is unnecessary and increases costs. The function would attempt to translate English text from English to English, wasting compute resources and potentially introducing translation artifacts even when the source text is already in the target language.
• B. Correct. This query uses a `CASE` statement to conditionally apply translation only when needed - it returns the original `log_text` when `source_lang = 'en'` and only calls `SNOWFLAKE.CORTEX.TRANSLATE(log_text, source_lang, 'en')` for non-English records. This approach is most cost-effective because it avoids unnecessary translation calls on English text while ensuring accurate results by preserving original English content unchanged.
• C. Incorrect. While this query filters out English records with `WHERE source_lang <> 'en'`, it uses `'auto'` for automatic language detection instead of the known `source_lang` values. This is less accurate because automatic detection may misidentify languages, and the query doesn't include English records in the result set, making it incomplete for the centralized analysis requirement.
• D. Incorrect. This query has the `SNOWFLAKE.CORTEX.TRANSLATE()` parameters reversed - it should be `TRANSLATE(text, source_language, target_language)` but this uses `TRANSLATE(log_text, 'en', source_lang)` which attempts to translate from English to the source language instead of translating from the source language to English, producing the opposite of the desired result.

Correct answer: B — Add 'Do not include any other text or explanation. Output only the raw JSON object.' to the end of the prompt.

• A. Incorrect. Adding 'You are a helpful assistant.' to the beginning of the prompt establishes a conversational persona for the model, which actually encourages the model to provide explanatory text and conversational responses. This system message makes the model more likely to include phrases like 'Sure, here is the JSON:' or 'I'd be happy to help you with that,' which is the opposite of what's needed for strict JSON-only output.
• B. Correct. Adding explicit instructions like 'Do not include any other text or explanation. Output only the raw JSON object.' directly addresses the problem by providing clear constraints on the output format. This prompt engineering technique uses explicit formatting instructions to suppress conversational responses and ensure the model returns only the requested JSON structure without additional explanatory text or conversational elements.
• C. Incorrect. Increasing the temperature setting in the `options` argument actually makes the model's output more random and creative, not more precise. Higher temperature values (closer to 1.0) encourage more diverse and unpredictable responses, while lower temperature values (closer to 0.0) produce more deterministic and focused outputs. For strict formatting requirements, you would want lower temperature, not higher.
• D. Incorrect. While `TRY_PARSE_JSON` is a valid Snowflake function that attempts to parse a string as JSON and returns `NULL` if parsing fails, this approach treats the symptom rather than the cause. It requires additional post-processing logic to extract JSON from mixed content and doesn't prevent the model from generating unwanted conversational text in the first place, making it less efficient than proper prompt engineering.

Correct answer: C — `STRING`

• A. Incorrect. While the returned string can be parsed and stored in a `VARIANT` column, the `AI_PARSE_DOCUMENT` function itself does not directly return a `VARIANT` data type. An explicit conversion using `PARSE_JSON` is required.
• B. Incorrect. `JSON` describes the format of the data contained within the returned value, but it is not a native SQL data type in Snowflake. The function returns a `STRING` that is formatted as a JSON object.
• C. Correct. According to Snowflake documentation, the `AI_PARSE_DOCUMENT` function returns a `STRING` that contains a JSON object. To query the semi-structured data within this string, you must explicitly use the `PARSE_JSON` function to convert it to an `OBJECT` or `VARIANT`.
• D. Incorrect. The `AI_PARSE_DOCUMENT` function does not directly return an `OBJECT` data type. The function's output is a `STRING`, which must then be explicitly converted into an `OBJECT` using the `PARSE_JSON` function for further analysis.

Correct answers: A, B — SELECT * FROM CUSTOMER_EVENTS AT (OFFSET => -3600);; SELECT * FROM CUSTOMER_EVENTS AT (TIMESTAMP => '2023-10-27 13:30:00'::timestamp_ltz);

• A. This is a correct answer. Snowflake Time Travel allows using the `AT` clause with `OFFSET`. The `OFFSET` specifies a time difference in seconds from the current time. An offset of -3600 seconds correctly queries the state of the table exactly one hour before the query was executed.
• B. This is a correct answer. The `AT` clause can be used with a specific `TIMESTAMP` to retrieve the state of a table at an exact point in time. The timestamp '2023-10-27 13:30:00' is exactly one hour prior to the current time, making this a valid method.
• C. This is incorrect. The `BEFORE` clause is a valid part of Snowflake Time Travel, but it is used with the `STATEMENT` parameter (e.g., `BEFORE (STATEMENT => '<query_id>')`) to query data as it existed immediately before a specific statement was executed. It cannot be used with `OFFSET`.
• D. This is incorrect. `HISTORY` is not a valid clause for querying a table's historical data using Snowflake's Time Travel feature.
• E. This is incorrect. `VERSION` is not a valid clause for querying a table's historical data using Snowflake's Time Travel feature.

Correct answer: B — 1

• A. Incorrect. A value of 0 disables Time Travel for the table. While this is a configurable option for a permanent table in Standard Edition, it is not the maximum retention period.
• B. Correct. For Snowflake Standard Edition, the data retention period for permanent tables defaults to 1 day and can only be set to either 0 or 1. Therefore, the maximum configurable value for data retention is 1 day.
• C. Incorrect. A retention period of 7 days is not a configurable option for any table type in Snowflake Standard Edition.
• D. Incorrect. A data retention period of up to 90 days is a feature exclusive to Snowflake Enterprise Edition and higher. For Standard Edition, the maximum is 1 day.

Correct answers: B, C — The `UNDROP` command restores the table along with its previous Time Travel history.; A Time Travel query for 09:59 AM will show the table state before it was first dropped.

• A. Incorrect. A Time Travel query can access the state of a table at a point in time even when it was dropped. As long as the table has been undropped and the query point is within the data retention period, the query will succeed. The `UNDROP` operation restores the table and its history, making past states accessible.
• B. Correct. A key feature of Snowflake's continuous data protection is that the `UNDROP` command restores a table to its state just before it was dropped. Crucially, it also restores the table's entire Time Travel history, allowing users to query data from before the drop occurred.
• C. Correct. Since the `UNDROP` command restores the table's complete Time Travel history, it is possible to execute a query against the table's state at a point in time before the `DROP` command was issued (e.g., 09:59 AM), provided that time is within the data retention period.
• D. Incorrect. This statement is the opposite of the correct behavior. The Time Travel history is not purged when a table is undropped; it is fully restored along with the table data. The history is retained and continues from where it left off.
• E. Incorrect. Snowflake does not limit the number of times an object can be dropped and undropped. As long as each `UNDROP` command is executed within the object's defined data retention period, it can be restored multiple times.

Correct answers: B, C, E — The database and its objects are read-only for the consumer; Shared data does not take up any storage in the consumer account and does not contribute to monthly data storage charges; No actual data is copied or transferred between accounts - all sharing uses Snowflake's services layer and metadata store

• A. Incorrect. When a consumer creates a database from a share, they cannot create new schemas and tables within that shared database. The documentation clearly states that "all database objects shared between accounts are read-only (i.e. the objects cannot be modified or deleted, including adding or modifying table data)". Consumers can only access and query the objects that were shared by the provider.
• B. Correct. According to the documentation, "on the consumer side, a read-only database is created from the share" and "all database objects shared between accounts are read-only (i.e. the objects cannot be modified or deleted, including adding or modifying table data)". This is a fundamental characteristic of Snowflake's Secure Data Sharing - consumers can query shared data but cannot modify the shared objects.
• C. Correct. The documentation explicitly states that "shared data does not take up any storage in a consumer account and therefore does not contribute to the consumer's monthly data storage charges. The only charges to consumers are for the compute resources (i.e. virtual warehouses) used to query the imported data". This is because no actual data is copied to the consumer account.
• D. Incorrect. This directly contradicts the read-only nature of shared databases. The documentation clearly states that "all database objects shared between accounts are read-only (i.e. the objects cannot be modified or deleted, including adding or modifying table data)". Consumers cannot modify, delete, or add data to shared objects - they can only query them.
• E. Correct. The documentation confirms that "with Secure Data Sharing, no actual data is copied or transferred between accounts. All sharing uses Snowflake's services layer and metadata store". This architecture enables near-instantaneous access to shared data and eliminates storage costs for consumers since no data is physically copied to their account.

Correct answers: B, D — They must create a provider profile.; They must accept the Snowflake Provider and Consumer Terms.

• A. Incorrect. Reader accounts are consumer accounts with limited functionality that cannot create or publish listings on Snowflake Marketplace. According to the documentation, providers must use a full Snowflake account and must not be using a Reader Account, as reader accounts can only consume shared data but cannot act as providers.
• B. Correct. Creating a provider profile is a mandatory prerequisite for offering any listings on the Snowflake Marketplace. The provider workflow specifically requires configuring your provider profile by navigating to Marketplace » Provider Studio » Profiles tab » + Create profile » External profile, and then submitting it for Snowflake's approval before you can begin publishing listings.
• C. Incorrect. While providers must use a full Snowflake account (not a trial account), there is no specific requirement for Enterprise Edition or higher. The documentation only specifies that you must use a full Snowflake account and cannot use trial accounts for Marketplace listings, but any full account edition that meets the basic requirements is sufficient.
• D. Correct. Accepting the Snowflake Provider and Consumer Terms is a legal requirement that must be completed before creating listings. This is listed as a prerequisite in the listing creation process, and providers must review and accept these terms to meet the legal requirements for offering listings on Snowflake Marketplace.
• E. Incorrect. A private Data Exchange is a separate collaboration feature for sharing data with a selected group of invited members, such as internal departments or specific business partners. Setting up a Data Exchange is not a prerequisite for becoming a Snowflake Marketplace provider, as these are two different data sharing mechanisms with distinct purposes.

Correct answer: B — The Data Exchange admin adds the member to the exchange. The provider adds the member's account to the share using `ALTER SHARE`. The consumer creates a database from the share using `CREATE DATABASE FROM SHARE`.

• A. Incorrect. This sequence skips the critical first step where the Data Exchange administrator must add the member to the exchange before any share access can be granted. While the provider does use `ALTER SHARE analyst_table_share ADD ACCOUNTS = consumer_account;` to add accounts to shares and consumers do create databases using `CREATE DATABASE mounted_db2 FROM SHARE provider_account.analyst_table_share;`, the Data Exchange membership must be established first.
• B. Correct. This represents the proper sequence for Data Exchange private share access: first, the Data Exchange admin adds the member to the exchange (establishing membership), then the provider uses `ALTER SHARE analyst_table_share ADD ACCOUNTS = consumer_account;` to grant share access to the member's account, and finally the consumer creates a database using `CREATE DATABASE mounted_db2 FROM SHARE provider_account.analyst_table_share;` after setting up appropriate roles with `GRANT IMPORT SHARE ON ACCOUNT TO ROLE r1;`.
• C. Incorrect. This describes a request-approval workflow that doesn't align with Snowflake's Data Exchange architecture. In Data Exchange environments, access is managed through administrative controls and direct account additions to shares using `ALTER SHARE` commands, not through consumer-initiated requests that require provider approval.
• D. Incorrect. Data Exchange administrators cannot directly add accounts to shares on behalf of providers. Share ownership and account management remain with the data provider, who must execute the `ALTER SHARE` command themselves. The Data Exchange admin's role is limited to managing exchange membership, not share-level permissions.