Snowflake Snowflake Certifications ARA-C01 Questions & Answers

• Question 11:

  What is a valid object hierarchy when building a Snowflake environment?

  A. Account --> Database --> Schema --> Warehouse

  B. Organization --> Account --> Database --> Schema --> Stage

  C. Account --> Schema > Table --> Stage

  D. Organization --> Account --> Stage --> Table --> View

  Correct Answer: B

  Explanation: This is the valid object hierarchy when building a Snowflake environment, according to the Snowflake documentation and the web search results. Snowflake is a cloud data platform that supports various types of objects, such as

  databases, schemas, tables, views, stages, warehouses, and more. These objects are organized in a hierarchical structure, as follows:

  Organization: An organization is the top-level entity that represents a group of Snowflake accounts that are related by business needs or ownership. An organization can have one or more accounts, and can enable features such as cross-

  account data sharing, billing and usage reporting, and single sign-on across accounts12.

  Account: An account is the primary entity that represents a Snowflake customer. An account can have one or more databases, schemas, stages, warehouses, and other objects. An account can also have one or more users, roles, and

  security integrations. An account is associated with a specific cloud platform, region, and Snowflake edition34.

  Database: A database is a logical grouping of schemas. A database can have one or more schemas, and can store structured, semi-structured, or unstructured data. A database can also have properties such as retention time, encryption,

  and ownership56.

  Schema: A schema is a logical grouping of tables, views, stages, and other objects. A schema can have one or more objects, and can define the namespace and access control for the objects. A schema can also have properties such as

  ownership and default warehouse .

  Stage: A stage is a named location that references the files in external or internal storage. A stage can be used to load data into Snowflake tables using the COPY INTO command, or to unload data from Snowflake tables using the COPY

  INTO LOCATION command. A stage can be created at the account, database, or schema level, and can have properties such as file format, encryption, and credentials .

  The other options listed are not valid object hierarchies, because they either omit or misplace some objects in the structure. For example, option A omits the organization level and places the warehouse under the schema level, which is

  incorrect. Option C omits the organization, account, and stage levels, and places the table under the schema level, which is incorrect. Option D omits the database level and places the stage and table under the account level, which is

  incorrect.

  References:

  Snowflake Documentation: Organizations

  Snowflake Blog: Introducing Organizations in Snowflake Snowflake Documentation: Accounts

  Snowflake Blog: Understanding Snowflake Account Structures Snowflake Documentation: Databases

  Snowflake Blog: How to Create a Database in Snowflake [Snowflake Documentation: Schemas]

  [Snowflake Blog: How to Create a Schema in Snowflake] [Snowflake Documentation: Stages]

  [Snowflake Blog: How to Use Stages in Snowflake]

• Question 12:

  An Architect runs the following SQL query:

  

  How can this query be interpreted?

  A. FILEROWS is a stage. FILE_ROW_NUMBER is line number in file.

  B. FILEROWS is the table. FILE_ROW_NUMBER is the line number in the table.

  C. FILEROWS is a file. FILE_ROW_NUMBER is the file format location.

  D. FILERONS is the file format location. FILE_ROW_NUMBER is a stage.

  Correct Answer: A

  A stage is a named location in Snowflake that can store files for data loading and unloading. A stage can be internal or external, depending on where the files are stored.

  The query in the question uses the LIST function to list the files in a stage named FILEROWS. The function returns a table with various columns, including FILE_ROW_NUMBER, which is the line number of the file in the stage. Therefore, the

  query can be interpreted as listing the files in a stage named FILEROWS and showing the line number of each file in the stage.

  References:

  : Stages

  : LIST Function

• Question 13:

  Which of the following are characteristics of how row access policies can be applied to external tables? (Choose three.)

  A. An external table can be created with a row access policy, and the policy can be applied to the VALUE column.

  B. A row access policy can be applied to the VALUE column of an existing external table.

  C. A row access policy cannot be directly added to a virtual column of an external table.

  D. External tables are supported as mapping tables in a row access policy.

  E. While cloning a database, both the row access policy and the external table will be cloned.

  F. A row access policy cannot be applied to a view created on top of an external table.

  Correct Answer: ABC

  Explanation: These three statements are true according to the Snowflake documentation and the web search results. A row access policy is a feature that allows filtering rows based on user-defined conditions. A row access policy can be applied to an external table, which is a table that reads data from external files in a stage. However, there are some limitations and considerations for using row access policies with external tables. An external table can be created with a row access policy by using the WITH ROW ACCESS POLICY clause in the CREATE EXTERNAL TABLE statement. The policy can be applied to the VALUE column, which is the column that contains the raw data from the external files in a VARIANT data type1. A row access policy can also be applied to the VALUE column of an existing external table by using the ALTER TABLE statement with the SET ROW ACCESS POLICY clause2. A row access policy cannot be directly added to a virtual column of an external table. A virtual column is a column that is derived from the VALUE column using an expression. To apply a row access policy to a virtual column, the policy must be applied to the VALUE column and the expression must be repeated in the policy definition3. External tables are not supported as mapping tables in a row access policy. A mapping table is a table that is used to determine the access rights of users or roles based on some criteria. Snowflake does not support using an external table as a mapping table because it may cause performance issues or errors4. While cloning a database, Snowflake clones the row access policy, but not the external table. Therefore, the policy in the cloned database refers to a table that is not present in the cloned database. To avoid this issue, the external table must be manually cloned or recreated in the cloned database4. A row access policy can be applied to a view created on top of an external table. The policy can be applied to the view itself or to the underlying external table. However, if the policy is applied to the view, the view must be a secure view, which is a view that hides the underlying data and the view definition from unauthorized users5. References: CREATE EXTERNAL TABLE | Snowflake Documentation ALTER EXTERNAL TABLE | Snowflake Documentation Understanding Row Access Policies | Snowflake Documentation Snowflake Data Governance: Row Access Policy Overview Secure Views | Snowflake Documentation

• Question 14:

  A table contains five columns and it has millions of records. The cardinality distribution of the columns is shown below:

  

  Column C4 and C5 are mostly used by SELECT queries in the GROUP BY and ORDER BY clauses. Whereas columns C1, C2 and C3 are heavily used in filter and join conditions of SELECT queries.

  The Architect must design a clustering key for this table to improve the query performance.

  Based on Snowflake recommendations, how should the clustering key columns be ordered while defining the multi-column clustering key?

  A. C5, C4, C2

  B. C3, C4, C5

  C. C1, C3, C2

  D. C2, C1, C3

  Correct Answer: C

  Explanation: According to the Snowflake documentation, the following are some considerations for choosing clustering for a table1:

  Clustering is optimal when either:

  Clustering is most effective when the clustering key is used in the following types of query predicates:

  Clustering is less effective when the clustering key is not used in any of the above query predicates, or when the clustering key is used in a predicate that requires a function or expression to be applied to the key (e.g. DATE_TRUNC,

  TO_CHAR, etc.).

  For most tables, Snowflake recommends a maximum of 3 or 4 columns (or expressions) per key. Adding more than 3-4 columns tends to increase costs more than benefits.

  Based on these considerations, the best option for the clustering key columns is C. C1, C3, C2, because:

  These columns are heavily used in filter and join conditions of SELECT queries, which are the most effective types of predicates for clustering. These columns have high cardinality, which means they have many distinct values and can help

  reduce the clustering skew and improve the compression ratio. These columns are likely to be correlated with each other, which means they can help co-locate similar rows in the same micro-partitions and improve the scan efficiency.

  These columns do not require any functions or expressions to be applied to them, which means they can be directly used in the predicates without affecting the clustering.

  References: 1: Considerations for Choosing Clustering for a Table | Snowflake Documentation

• Question 15:

  A healthcare company is deploying a Snowflake account that may include Personal Health Information (PHI). The company must ensure compliance with all relevant privacy standards.

  Which best practice recommendations will meet data protection and compliance requirements? (Choose three.)

  A. Use, at minimum, the Business Critical edition of Snowflake.

  B. Create Dynamic Data Masking policies and apply them to columns that contain PHI.

  C. Use the Internal Tokenization feature to obfuscate sensitive data.

  D. Use the External Tokenization feature to obfuscate sensitive data.

  E. Rewrite SQL queries to eliminate projections of PHI data based on current_role().

  F. Avoid sharing data with partner organizations.

  Correct Answer: ABD

  A healthcare company that handles PHI data must ensure compliance with relevant privacy standards, such as HIPAA, HITRUST, and GDPR. Snowflake provides several features and best practices to help customers meet their data

  protection and compliance requirements1.

  One best practice recommendation is to use, at minimum, the Business Critical edition of Snowflake. This edition provides the highest level of data protection and security, including end-to-end encryption with customer-managed keys,

  enhanced object-level security, and HIPAA and HITRUST compliance2. Therefore, option A is correct.

  Another best practice recommendation is to create Dynamic Data Masking policies and apply them to columns that contain PHI. Dynamic Data Masking is a feature that allows masking or redacting sensitive data based on the current user's

  role. This way, only authorized users can view the unmasked data, while others will see masked values, such as NULL, asterisks, or random characters3.

  Therefore, option B is correct.

  A third best practice recommendation is to use the External Tokenization feature to obfuscate sensitive data. External Tokenization is a feature that allows replacing sensitive data with tokens that are generated and stored by an external

  service, such as Protegrity. This way, the original data is never stored or processed by Snowflake, and only authorized users can access the tokenized data through the external service4. Therefore, option D is correct. Option C is incorrect,

  because the Internal Tokenization feature is not available in Snowflake. Snowflake does not provide any native tokenization functionality, but only supports integration with external tokenization services4. Option E is incorrect, because

  rewriting SQL queries to eliminate projections of PHI data based on current_role() is not a best practice. This approach is error- prone, inefficient, and hard to maintain. A better alternative is to use Dynamic Data Masking policies, which can

  automatically mask data based on the user's role without modifying the queries3.

  Option F is incorrect, because avoiding sharing data with partner organizations is not a best practice. Snowflake enables secure and governed data sharing with internal and external consumers, such as business units, customers, or

  partners. Data sharing does not involve copying or moving data, but only granting access privileges to the shared objects. Data sharing can also leverage Dynamic Data Masking and External Tokenization features to protect sensitive data5.

  References: : Snowflake's Security and Compliance Reports : Snowflake Editions : Dynamic Data Masking : External Tokenization : Secure Data Sharing

• Question 16:

  A large manufacturing company runs a dozen individual Snowflake accounts across its business divisions. The company wants to increase the level of data sharing to support supply chain optimizations and increase its purchasing leverage with multiple vendors.

  The company's Snowflake Architects need to design a solution that would allow the business divisions to decide what to share, while minimizing the level of effort spent on configuration and management. Most of the company divisions use Snowflake accounts in the same cloud deployments with a few exceptions for European-based divisions.

  According to Snowflake recommended best practice, how should these requirements be met?

  A. Migrate the European accounts in the global region and manage shares in a connected graph architecture. Deploy a Data Exchange.

  B. Deploy a Private Data Exchange in combination with data shares for the European accounts.

  C. Deploy to the Snowflake Marketplace making sure that invoker_share() is used in all secure views.

  D. Deploy a Private Data Exchange and use replication to allow European data shares in the Exchange.

  Correct Answer: B

  Explanation: According to Snowflake recommended best practice, the requirements of the large manufacturing company should be met by deploying a Private Data Exchange in combination with data shares for the European accounts. A Private Data Exchange is a feature of the Snowflake Data Cloud platform that enables secure and governed sharing of data between organizations. It allows Snowflake customers to create their own data hub and invite other parts of their organization or external partners to access and contribute data sets. A Private Data Exchange provides centralized management, granular access control, and data usage metrics for the data shared in the exchange1. A data share is a secure and direct way of sharing data between Snowflake accounts without having to copy or move the data. A data share allows the data provider to grant privileges on selected objects in their account to one or more data consumers in other accounts2. By using a Private Data Exchange in combination with data shares, the company can achieve the following benefits: The business divisions can decide what data to share and publish it to the Private Data Exchange, where it can be discovered and accessed by other members of the exchange. This reduces the effort and complexity of managing multiple data sharing relationships and configurations. The company can leverage the existing Snowflake accounts in the same cloud deployments to create the Private Data Exchange and invite the members to join. This minimizes the migration and setup costs and leverages the existing Snowflake features and security. The company can use data shares to share data with the European accounts that are in different regions or cloud platforms. This allows the company to comply with the regional and regulatory requirements for data sovereignty and privacy, while still enabling data collaboration across the organization. The company can use the Snowflake Data Cloud platform to perform data analysis and transformation on the shared data, as well as integrate with other data sources and applications. This enables the company to optimize its supply chain and increase its purchasing leverage with multiple vendors. The other options are incorrect because they do not meet the requirements or follow the best practices. Option A is incorrect because migrating the European accounts to the global region may violate the data sovereignty and privacy regulations, and deploying a Data Exchange may not provide the level of control and management that the company needs. Option C is incorrect because deploying to the Snowflake Marketplace may expose the company's data to unwanted consumers, and using invoker_share() in secure views may not provide the desired level of security and governance. Option D is incorrect because using replication to allow European data shares in the Exchange may incur additional costs and complexity, and may not be necessary if data shares can be used instead. References: Private Data Exchange | Snowflake Documentation, Introduction to Secure Data Sharing | Snowflake Documentation

• Question 17:

  Which security, governance, and data protection features require, at a MINIMUM, the Business Critical edition of Snowflake? (Choose two.)

  A. Extended Time Travel (up to 90 days)

  B. Customer-managed encryption keys through Tri-Secret Secure

  C. Periodic rekeying of encrypted data

  D. AWS, Azure, or Google Cloud private connectivity to Snowflake

  E. Federated authentication and SSO

  Correct Answer: BD

  Explanation: According to the SnowPro Advanced: Architect documents and learning resources, the security, governance, and data protection features that require, at a minimum, the Business Critical edition of Snowflake are: Customer-managed encryption keys through Tri-Secret Secure. This feature allows customers to manage their own encryption keys for data at rest in Snowflake, using a combination of three secrets: a master key, a service key, and a security password. This provides an additional layer of security and control over the data encryption and decryption process1. Periodic rekeying of encrypted data. This feature allows customers to periodically rotate the encryption keys for data at rest in Snowflake, using either Snowflake- managed keys or customer-managed keys. This enhances the security and protection of the data by reducing the risk of key compromise or exposure2. The other options are incorrect because they do not require the Business Critical edition of Snowflake. Option A is incorrect because extended Time Travel (up to 90 days) is available with the Enterprise edition of Snowflake3. Option D is incorrect because AWS, Azure, or Google Cloud private connectivity to Snowflake is available with the Standard edition of Snowflake4. Option E is incorrect because federated authentication and SSO are available with the Standard edition of Snowflake5. References: Tri-Secret Secure | Snowflake Documentation, Periodic Rekeying of Encrypted Data | Snowflake Documentation, Snowflake Editions | Snowflake Documentation, Snowflake Network Policies | Snowflake Documentation, Configuring Federated Authentication and SSO | Snowflake Documentation

• Question 18:

  What built-in Snowflake features make use of the change tracking metadata for a table? (Choose two.)

  A. The MERGE command

  B. The UPSERT command

  C. The CHANGES clause

  D. A STREAM object

  E. TheCHANGE_DATA_CAPTURE command

  Correct Answer: CD

  Explanation: The built-in Snowflake features that make use of the change tracking metadata for a table are the CHANGES clause and a STREAM object. The CHANGES clause enables querying the change tracking metadata for a table or view within a specified interval of time without having to create a stream with an explicit transactional offset1. A STREAM object records data manipulation language (DML) changes made to tables, including inserts, updates, and deletes, as well as metadata about each change, so that actions can be taken using the changed data. This process is referred to as change data capture (CDC)2. The other options are incorrect because they do not make use of the change tracking metadata for a table. The MERGE command performs insert, update, or delete operations on a target table based on the results of a join with a source table3. The UPSERT command is not a valid Snowflake command. The CHANGE_DATA_CAPTURE command is not a valid Snowflake command. References: CHANGES | Snowflake Documentation, Change Tracking Using Table Streams | Snowflake Documentation, MERGE | Snowflake Documentation

• Question 19:

  A healthcare company wants to share data with a medical institute. The institute is running a Standard edition of Snowflake; the healthcare company is running a Business Critical edition.

  How can this data be shared?

  A. The healthcare company will need to change the institute's Snowflake edition in the accounts panel.

  B. By default, sharing is supported from a Business Critical Snowflake edition to a Standard edition.

  C. Contact Snowflake and they will execute the share request for the healthcare company.

  D. Set the share_restriction parameter on the shared object to false.

  Correct Answer: D

  Explanation: By default, Snowflake does not allow sharing data from a Business Critical edition to a non-Business Critical edition. This is because Business Critical edition provides enhanced security and data protection features that are not available in lower editions. However, this restriction can be overridden by setting the share_restriction parameter on the shared object (database, schema, or table) to false. This parameter allows the data provider to explicitly allow sharing data with lower edition accounts. Note that this parameter can only be set by the data provider, not the data consumer. Also, setting this parameter to false may reduce the level of security and data protection for the shared data. References: Enable Data Share:Business Critical Account to Lower Edition Sharing Is Not Allowed From An Account on BUSINESS CRITICAL Edition to an Account On A Lower Edition SQL Execution Error: Sharing is Not Allowed from an Account on BUSINESS CRITICAL Edition to an Account on a Lower Edition Snowflake Editions | Snowflake Documentation

• Question 20:

  An Architect needs to grant a group of ORDER_ADMIN users the ability to clean old data in an ORDERS table (deleting all records older than 5 years), without granting any privileges on the table. The group's manager (ORDER_MANAGER) has full DELETE privileges on the table.

  How can the ORDER_ADMIN role be enabled to perform this data cleanup, without needing the DELETE privilege held by the ORDER_MANAGER role?

  A. Create a stored procedure that runs with caller's rights, including the appropriate "> 5 years" business logic, and grant USAGE on this procedure to ORDER_ADMIN. The ORDER_MANAGER role owns the procedure.

  B. Create a stored procedure that can be run using both caller's and owner's rights (allowing the user to specify which rights are used during execution), and grant USAGE on this procedure to ORDER_ADMIN. The ORDER_MANAGER role owns the procedure.

  C. Create a stored procedure that runs with owner's rights, including the appropriate "> 5 years" business logic, and grant USAGE on this procedure to ORDER_ADMIN. The ORDER_MANAGER role owns the procedure.

  D. This scenario would actually not be possible in Snowflake ?any user performing a DELETE on a table requires the DELETE privilege to be granted to the role they are using.

  Correct Answer: C

  Explanation: This is the correct answer because it allows the ORDER_ADMIN role to perform the data cleanup without needing the DELETE privilege on the ORDERS table. A stored procedure is a feature that allows scheduling and executing SQL statements or stored procedures in Snowflake. A stored procedure can run with either the caller's rights or the owner's rights. A caller's rights stored procedure runs with the privileges of the role that called the stored procedure, while an owner's rights stored procedure runs with the privileges of the role that created the stored procedure. By creating a stored procedure that runs with owner's rights, the ORDER_MANAGER role can delegate the specific task of deleting old data to the ORDER_ADMIN role, without granting the ORDER_ADMIN role more general privileges on the ORDERS table. The stored procedure must include the appropriate business logic to delete only the records older than 5 years, and the ORDER_MANAGER role must grant the USAGE privilege on the stored procedure to the ORDER_ADMIN role. The ORDER_ADMIN role can then execute the stored procedure to perform the data cleanup12. References: Snowflake Documentation: Stored Procedures Snowflake Documentation: Understanding Caller's Rights and Owner's Rights Stored Procedures