Snowflake SnowPro Core Recertification Exam Dumps & Practice Test Questions
Question 1:
Which subquery types are supported by Snowflake? Select the two correct options.
A. Scalar subqueries that are uncorrelated in the WHERE clause
B. Scalar subqueries that are uncorrelated, usable anywhere a value expression is allowed
C. EXISTS, ANY / ALL, and IN subqueries in the WHERE clause that can only be uncorrelated
D. EXISTS, ANY / ALL, and IN subqueries in the WHERE clause that must be correlated
E. EXISTS, ANY / ALL, and IN subqueries in the WHERE clause that can either be correlated or uncorrelated
Correct Answer: B, E
Explanation:
Snowflake supports various types of subqueries, and the options presented here touch on different ways subqueries can be used. In Snowflake, subqueries can be classified based on their correlation and the context in which they are allowed.
Scalar Subqueries
A scalar subquery returns a single value, typically used where a value expression is expected. In option A, it specifies that scalar subqueries that are uncorrelated are allowed in the WHERE clause. While scalar subqueries can indeed be used in the WHERE clause, the statement is incomplete as it limits the use to just the WHERE clause. Therefore, A is not the best answer.
Option B is the correct statement. Scalar subqueries that are uncorrelated are indeed supported by Snowflake, and they can be used anywhere a value expression is allowed, not just in the WHERE clause. This makes B a correct option.
EXISTS, ANY / ALL, and IN Subqueries
These types of subqueries can appear in the WHERE clause and are typically used to test for the existence of certain conditions or compare values across different datasets. Option C is incorrect because EXISTS, ANY, ALL, and IN subqueries in the WHERE clause can be either correlated or uncorrelated, not just uncorrelated as specified. This option oversimplifies the relationship and is therefore not valid.
Option D is also incorrect. EXISTS, ANY, ALL, and IN subqueries do not necessarily need to be correlated in Snowflake. They can be uncorrelated as well, so the requirement that they must be correlated in the WHERE clause is wrong.
Finally, E is the correct option. EXISTS, ANY, ALL, and IN subqueries in the WHERE clause can indeed be either correlated or uncorrelated, depending on the query's structure and requirements. This flexibility makes E the most accurate choice.
In summary, the correct options are B and E because they correctly describe the behavior of scalar subqueries and the existence, comparison, and inclusion subqueries in Snowflake, which can be either correlated or uncorrelated.
Question 2:
Which feature in Snowflake allows auditing of column data accessed by user queries? Select the correct option.
A. Query Access History
B. Column Data Classification
C. Column-level Security
D. Object Relationship Tracking
Correct Answer: A
Explanation:
Snowflake offers several features that help with security, data access control, and auditing. The specific feature for auditing column data accessed by user queries is Query Access History.
Query Access History provides a detailed record of all the queries executed on the Snowflake platform. This feature allows administrators to review queries and determine which data was accessed, including the specific columns within tables. By tracking query execution over time, administrators can determine if any sensitive or restricted data was accessed. The information provided includes the query itself, execution details, and the objects involved, enabling audit capabilities to meet compliance and governance requirements.
Column Data Classification, as mentioned in option B, is used to label or tag columns based on their data type or sensitivity (e.g., identifying personal data like social security numbers). While this feature can help with organizing data and defining access policies, it does not directly facilitate auditing of data accessed by user queries.
Column-level Security (option C) refers to controlling access to specific columns within a table based on user roles. This feature is focused on restricting access to certain parts of data rather than auditing it. While it helps prevent unauthorized access, it does not track or record access to the data itself.
Object Relationship Tracking (option D) is related to tracking the relationships between various objects in Snowflake, such as tables, views, and schemas. It helps maintain an understanding of dependencies, but it does not track column-level data access, which is required for auditing the columns accessed in user queries.
Thus, the correct answer is A, as Query Access History is the feature that allows the auditing of column data accessed by user queries in Snowflake.
Question 3:
Which command in Snowflake can be used to view all available network policies in your account? Select the correct option.
A. DISPLAY SESSION POLICY
B. DISPLAY NETWORK POLICY
C. SHOW POLICIES ON SESSIONS
D. LIST NETWORK POLICIES
Correct Answer: D
Explanation:
In Snowflake, network policies are used to control and restrict network access to the Snowflake platform, particularly by specifying allowed IP addresses and enforcing restrictions for enhanced security. To view all available network policies in your account, the correct command to use is LIST NETWORK POLICIES.
The LIST NETWORK POLICIES command retrieves and displays a list of all the network policies currently configured in your Snowflake account. This includes details about each network policy, such as its name, description, and rules regarding IP address ranges and network restrictions. It allows administrators to quickly assess the network policies set up in their environment.
A. DISPLAY SESSION POLICY is incorrect. This command would not retrieve network policies but rather would be focused on session-related policies, which deal with session-level configurations rather than network-level access controls.
B. DISPLAY NETWORK POLICY is also incorrect. Snowflake does not have a specific DISPLAY NETWORK POLICY command. The correct command for listing network policies is LIST NETWORK POLICIES, not DISPLAY.
C. SHOW POLICIES ON SESSIONS is incorrect. This command is not designed to list network policies either. Instead, it focuses on showing session policies, which control various session-related aspects like roles, timeouts, and other session-level configurations. It is unrelated to viewing network policies.
Thus, the correct answer is D, as LIST NETWORK POLICIES is the command in Snowflake that will show all available network policies in your account.
Question 4:
Which Snowflake service enhances performance for complex queries involving analytical lookups and conditional filtering? Select the correct option.
A. Data Tagging
B. Query Acceleration Service
C. Search Optimization Service
D. Data Classification
Correct Answer: C
Explanation:
Snowflake offers a variety of services designed to improve query performance, particularly for complex queries. The Search Optimization Service is the service that enhances performance specifically for complex queries involving analytical lookups and conditional filtering.
The Search Optimization Service improves the performance of queries that use conditions such as LIKE or ILIKE on string columns, and also helps speed up queries with complex filters. It creates search optimization indexes on the data, which are particularly useful for workloads where filtering on large datasets is common. By reducing the amount of data scanned, the service can greatly improve the performance of queries with filters and lookups on large volumes of data, especially when searching for specific substrings within text-based columns. This service is ideal for scenarios where queries involve a lot of analytical filtering and lookup operations on non-key columns.
A. Data Tagging is used to label and categorize data in Snowflake for purposes like governance, security, and compliance. It is not designed to directly enhance query performance. Instead, its focus is on organizing and tagging data for easier tracking and management.
B. Query Acceleration Service is another service in Snowflake, but it is specifically designed to speed up the execution of queries by temporarily optimizing the query processing using specialized compute resources. However, its focus is on accelerating the entire query execution, rather than enhancing the performance of specific query types like analytical lookups or conditional filtering.
D. Data Classification refers to the process of categorizing and labeling data based on its sensitivity or content type (e.g., personal data, financial data). While it can help with data management, security, and compliance, it does not directly influence the performance of complex queries involving lookups and filtering.
Thus, the correct answer is C, as the Search Optimization Service specifically enhances performance for complex queries that involve analytical lookups and conditional filtering in Snowflake.
Question 5:
Which minimum Snowflake Edition is required to support the secure storage of Protected Health Information (PHI) data? Select the correct option.
A. Standard Edition
B. Enterprise Edition
C. Business Critical Edition
D. Virtual Private Snowflake Edition
Correct Answer: C
Explanation:
Snowflake provides different editions with varying levels of features and security measures to meet the needs of different industries, including those that handle sensitive data like Protected Health Information (PHI).
The Business Critical Edition is the minimum Snowflake edition required to support the secure storage of PHI data. This edition includes advanced security features such as end-to-end encryption, dedicated virtual warehouses, and enhanced controls that meet industry-specific regulatory standards such as the Health Insurance Portability and Accountability Act (HIPAA). HIPAA compliance is essential when working with PHI, and the Business Critical Edition is specifically designed to meet these compliance requirements.
This edition also offers additional layers of security, including features like multi-factor authentication (MFA), dedicated data storage, and enhanced logging to ensure that sensitive data like PHI is securely stored and handled. These features make the Business Critical Edition the correct choice for enterprises dealing with healthcare data and other sensitive information.
A. Standard Edition does not meet the compliance standards necessary for securely handling PHI. It lacks the advanced security and privacy controls required to protect sensitive health information.
B. Enterprise Edition provides more advanced features than the Standard Edition, but it still does not provide the level of security and regulatory compliance necessary to securely store PHI. The Enterprise Edition is geared toward broader enterprise use but falls short of the dedicated features required by regulations like HIPAA.
D. Virtual Private Snowflake Edition (VPS) provides a higher level of isolation and control over your Snowflake environment, but it is a more specialized edition that offers features like private connectivity and increased isolation, rather than directly focusing on PHI compliance. While VPS is highly secure, it is not the minimum edition required for secure PHI storage.
Therefore, the Business Critical Edition (option C) is the correct answer, as it is the minimum required Snowflake edition to securely store PHI data in compliance with regulations like HIPAA.
Question 6:
Which two authentication methods does Snowflake support for securing REST API access? Select the two correct options.
A. OAuth
B. Authentication via Key Pairs
C. Federated Authentication
D. Multi-factor Authentication (MFA)
E. Username and Password
Correct Answer: A, B
Explanation:
Snowflake provides several authentication methods for securing REST API access, ensuring that sensitive data is protected during interactions with the Snowflake platform. The two methods that specifically secure REST API access are OAuth and Authentication via Key Pairs.
A. OAuth is a widely used authentication method that allows third-party applications to securely access Snowflake's REST API without requiring users to expose their credentials. OAuth relies on the concept of authorization tokens, which are granted by an identity provider (IDP) after successful user authentication. This method is preferred for modern applications because it minimizes the risks associated with storing user credentials and provides fine-grained access control. Snowflake supports OAuth for securing API access, making it a valid option for this question.
B. Authentication via Key Pairs is another supported method in Snowflake. It involves the use of public and private key pairs for authentication when accessing Snowflake through the REST API. In this method, a private key is stored securely on the client side, and the corresponding public key is registered within Snowflake. When making an API request, the private key is used to sign the request, and Snowflake uses the public key to verify the authenticity of the request. This method is highly secure and ideal for automated processes or integrations that need a secure way of accessing the API.
C. Federated Authentication is a method that allows users to authenticate with Snowflake using an identity provider (IDP) such as Okta, Azure AD, or other SAML-based IDPs. However, while this method is useful for user authentication within Snowflake, it does not directly secure REST API access in the same way as OAuth or key pair authentication. Federated Authentication typically applies to user logins and role-based access rather than securing API calls.
D. Multi-factor Authentication (MFA) adds an extra layer of security by requiring additional verification steps (e.g., an OTP or authentication app) alongside the user's regular credentials. However, MFA is more relevant for user authentication and does not directly apply to securing REST API access. While MFA is important for user login security, it does not specifically control API access methods for third-party integrations.
E. Username and Password is a traditional method of authentication that involves sending a user's credentials with each API request. While this method can be used for API access, it is not as secure as OAuth or key pair authentication, especially in cases where credentials might be exposed or compromised. Snowflake's preferred methods for API security are OAuth and key pair authentication due to their higher security and better management of access tokens.
Therefore, the correct answers are A and B: OAuth and Authentication via Key Pairs are the two authentication methods that Snowflake supports for securing REST API access.
Question 7:
Which operation is allowed for Snowflake external tables? Select the correct option.
A. Insert Data
B. Join with Other Tables
C. Rename the Table
D. Alter Table Schema
Correct Answer: B
Explanation:
In Snowflake, external tables allow you to query data stored outside of Snowflake, such as in cloud storage like Amazon S3, Microsoft Azure, or Google Cloud Storage. These tables are read-only in nature, and certain operations are restricted when working with them.
A. Insert Data is not allowed for external tables. External tables are designed to allow Snowflake to read data stored externally but not to modify it directly. Since the data resides outside of Snowflake’s internal storage, you cannot perform insert, update, or delete operations directly on external tables.
B. Join with Other Tables is allowed for external tables. You can perform joins between external tables and internal Snowflake tables. This allows you to combine the data from external sources with data that is natively stored within Snowflake. Snowflake allows this operation because external tables are essentially read-only data representations of external datasets, which can be queried and joined with other tables for analysis.
C. Rename the Table is not allowed for external tables. Since external tables reference data that is stored outside of Snowflake, renaming them is not supported as a direct operation. If you need to rename an external table, you would likely need to drop and recreate it with the new name.
D. Alter Table Schema is also not allowed for external tables. While you can alter certain properties of internal Snowflake tables, such as adding or removing columns, altering the schema of an external table is not possible because the data schema is controlled externally (in the cloud storage). Any changes to the structure of the data would need to be handled at the source, not within Snowflake.
Thus, the correct answer is B, as Join with Other Tables is the operation allowed for Snowflake external tables.
Question 8: What Snowflake feature allows you to track changes made to objects like databases, tables, and views? Select the correct option.
A. Object Dependencies
B. Data Retention
C. Access History
D. Change Data Capture (CDC)
Correct Answer: D
Explanation:
Snowflake provides a range of features that help track and manage changes made to data and objects within the platform. The feature specifically designed for tracking changes to objects such as databases, tables, and views is Change Data Capture (CDC).
Change Data Capture (CDC) is a feature in Snowflake that allows you to track and capture changes (inserts, updates, and deletes) made to data in tables. It is useful when you need to monitor how data evolves over time and helps in use cases like data replication, ETL processes, and auditing. CDC tracks changes at the row level and can be applied to tables to capture incremental changes, making it the best option for tracking changes made to database objects.
A. Object Dependencies refers to tracking relationships and dependencies between Snowflake objects, such as which views depend on which tables. While this helps understand the structure and interrelationships within the database, it does not specifically track changes made to objects like databases, tables, or views. It is more about understanding how different objects in the database are related to one another.
B. Data Retention deals with how long Snowflake retains historical data for tables and views, especially in the context of time travel and fail-safe. While it plays a role in enabling you to query historical versions of data, it is not designed for tracking changes to objects themselves. Data retention focuses more on the ability to recover data from past states, rather than providing a detailed audit trail of changes made to database structures.
C. Access History provides insights into who accessed the Snowflake platform, what queries were run, and when the access occurred. It is primarily focused on security and auditing access to the data and doesn't track changes to the objects themselves (like creating, altering, or dropping databases or tables).
Therefore, the correct answer is D, as Change Data Capture (CDC) is the feature that specifically tracks changes made to objects like databases, tables, and views within Snowflake.
Question 9:
Which of the following services in Snowflake automatically manages and monitors storage performance and scaling? Select the correct option.
A. Virtual Warehouse
B. Auto-Scaling
C. Storage Optimization
D. Query Acceleration Service
Correct Answer: C
Explanation:
Snowflake provides several services that help manage performance, scaling, and optimization of data storage and queries. The service that specifically handles the automatic management and monitoring of storage performance and scaling is Storage Optimization.
Storage Optimization in Snowflake automatically manages how data is stored to optimize performance and reduce costs. This includes automatically clustering and reorganizing large tables over time to improve query performance, as well as optimizing data storage by reducing the size of stored data. This service is crucial for maintaining the efficiency of the data warehouse as it grows, ensuring that performance does not degrade and that storage costs are kept in check.
A. Virtual Warehouse refers to the compute resources in Snowflake used to execute queries. Virtual warehouses are separate, scalable clusters of compute resources that Snowflake uses to process queries and perform other tasks. While they handle query execution and performance, they are not responsible for directly managing storage performance or scaling.
B. Auto-Scaling is a feature that automatically adjusts the compute resources (virtual warehouses) to match the workload. It ensures that the right amount of compute power is allocated to handle the load, but it does not directly manage storage performance or scaling. Auto-scaling adjusts for compute-related tasks, such as query processing and workload distribution, but not for storage optimization.
D. Query Acceleration Service speeds up complex queries by optimizing how data is processed. It works by caching intermediate query results and executing specific types of queries more efficiently. However, it focuses primarily on query performance and does not directly manage storage scaling or performance.
Therefore, the correct answer is C, as Storage Optimization is the service that automatically manages and monitors storage performance and scaling in Snowflake.
Question 10:
Which Snowflake feature is used for automatically managing data storage based on usage patterns to save costs? Select the correct option.
A. Data Clustering
B. Data Retention Timeouts
C. Auto-Suspend and Auto-Resume
D. Time Travel
Correct Answer: A
Explanation:
The correct answer is A. Data Clustering. Snowflake’s Data Clustering feature helps automatically manage the data storage based on usage patterns to optimize both performance and cost. By clustering large tables, Snowflake can organize the data in such a way that queries perform more efficiently, reducing the cost of data scans and improving query performance. When tables are clustered efficiently, Snowflake can also reduce storage costs by optimizing the way data is stored, ensuring that only relevant data is read during queries, thus saving on storage costs and improving efficiency.
A. Data Clustering works by grouping related data together on storage, allowing Snowflake to better optimize for query performance and storage usage. Clustering is especially useful for large datasets and can be done automatically through Snowflake’s built-in features, significantly saving on costs by reducing unnecessary storage space used for querying.
B. Data Retention Timeouts refer to the period for which Snowflake retains data. While this is important for managing the historical data, especially in the context of Time Travel, it doesn't directly manage storage costs based on usage patterns.
C. Auto-Suspend and Auto-Resume are features related to virtual warehouses (compute resources) in Snowflake. They automatically suspend warehouses when not in use and resume them when queries are executed. This feature is aimed at optimizing compute costs, not storage costs. It helps save costs on compute resources, but it doesn't directly manage how data is stored or optimize storage costs based on usage.
D. Time Travel allows you to query historical versions of your data, providing the ability to recover lost data or view the state of data at a specific point in time. While Time Travel is a useful feature for data recovery, it does not specifically optimize or manage storage costs based on usage patterns.
Therefore, the correct answer is A, as Data Clustering is the feature that optimizes data storage based on usage patterns to reduce costs.
