Snowflake ARA-C01 Online Practice Questions and Exam Preparation

Snowflake ARA-C01 Online Questions & Answers

• Question 41:

  A Data Engineer is designing a near real-time ingestion pipeline for a retail company to ingest event logs into Snowflake to derive insights. A Snowflake Architect is asked to define security best practices to configure access control privileges for the data load for auto- ingest to Snowpipe.

  What are the MINIMUM object privileges required for the Snowpipe user to execute Snowpipe?

  A. OWNERSHIP on the named pipe, USAGE on the named stage, target database, and schema, and INSERT and SELECT on the target table
  B. OWNERSHIP on the named pipe, USAGE and READ on the named stage, USAGE on the target database and schema, and INSERT end SELECT on the target table
  C. CREATE on the named pipe, USAGE and READ on the named stage, USAGE on the target database and schema, and INSERT end SELECT on the target table
  D. USAGE on the named pipe, named stage, target database, and schema, and INSERT and SELECT on the target table
  B. OWNERSHIP on the named pipe, USAGE and READ on the named stage, USAGE on the target database and schema, and INSERT end SELECT on the target table

  ---

  Explanation/Reference:

  According to the SnowPro Advanced: Architect documents and learning resources, the minimum object privileges required for the Snowpipe user to execute Snowpipe are:

  OWNERSHIP on the named pipe. This privilege allows the Snowpipe user to create, modify, and drop the pipe object that defines the COPY statement for loading data from the stage to the table1.

  USAGE and READ on the named stage. These privileges allow the Snowpipe user to access and read the data files from the stage that are loaded by Snowpipe2. USAGE on the target database and schema. These privileges allow the

  Snowpipe user to access the database and schema that contain the target table3. INSERT and SELECT on the target table. These privileges allow the Snowpipe user to insert data into the table and select data from the table4. The other

  options are incorrect because they do not specify the minimum object privileges required for the Snowpipe user to execute Snowpipe. Option A is incorrect because it does not include the READ privilege on the named stage, which is required

  for the Snowpipe user to read the data files from the stage. Option C is incorrect because it does not include the OWNERSHIP privilege on the named pipe, which is required for the Snowpipe user to create, modify, and drop the pipe object.

  Option D is incorrect because it does not include the OWNERSHIP privilege on the named pipe or the READ privilege on the named stage, which are both required for the Snowpipe user to execute Snowpipe. References : CREATE PIPE |

  Snowflake Documentation, CREATE STAGE | Snowflake Documentation, CREATE DATABASE | Snowflake Documentation, CREATE TABLE | Snowflake Documentation

• Question 42:

  An Architect is designing a pipeline to stream event data into Snowflake using the Snowflake Kafka connector. The Architect's highest priority is to configure the connector to stream data in the MOST cost-effective manner.

  Which of the following is recommended for optimizing the cost associated with the Snowflake Kafka connector?

  A. Utilize a higher Buffer.flush.time in the connector configuration.
  B. Utilize a higher Buffer.size.bytes in the connector configuration.
  C. Utilize a lower Buffer.size.bytes in the connector configuration.
  D. Utilize a lower Buffer.count.records in the connector configuration.
  A. Utilize a higher Buffer.flush.time in the connector configuration.

  ---

  Explanation/Reference:

  The minimum value supported for the buffer.flush.time property is 1 (in seconds). For higher average data flow rates, we suggest that you decrease the default value for improved latency. If cost is a greater concern than latency, you could increase the buffer flush time. Be careful to flush the Kafka memory buffer before it becomes full to avoid out of memory exceptions.https://docs.snowflake.com/en/user-guide/data-load- snowpipe-streaming-kafka

• Question 43:

  A company has a Snowflake account named ACCOUNTA in AWS us-east-1 region. The company stores its marketing data in a Snowflake database named MARKET_DB. One of the company's business partners has an account named PARTNERB in Azure East US 2 region. For marketing purposes the company has agreed to share the database MARKET_DB with the partner account.

  Which of the following steps MUST be performed for the account PARTNERB to consume data from the MARKET_DB database?

  A. Create a new account (called AZABC123) in Azure East US 2 region. From account ACCOUNTA create a share of database MARKET_DB, create a new database out of this share locally in AWS us-east-1 region, and replicate this new database to AZABC123 account. Then set up data sharing to the PARTNERB account.
  B. From account ACCOUNTA create a share of database MARKET_DB, and create a new database out of this share locally in AWS us-east-1 region. Then make this database the provider and share it with the PARTNERB account.
  C. Create a new account (called AZABC123) in Azure East US 2 region. From account ACCOUNTA replicate the database MARKET_DB to AZABC123 and from this account set up the data sharing to the PARTNERB account.
  D. Create a share of database MARKET_DB, and create a new database out of this share locally in AWS us-east-1 region. Then replicate this database to the partner's account PARTNERB.
  C. Create a new account (called AZABC123) in Azure East US 2 region. From account ACCOUNTA replicate the database MARKET_DB to AZABC123 and from this account set up the data sharing to the PARTNERB account.

  ---

  Explanation/Reference:

  Snowflake supports data sharing across regions and cloud platforms using account replication and share replication features. Account replication enables the replication of objects from a source account to one or more target accounts in the same organization. Share replication enables the replication of shares from a source account to one or more target accounts in the same organization1. To share data from the MARKET_DB database in the ACCOUNTA account in AWS useast-1 region with the PARTNERB account in Azure East US 2 region, the following steps must be performed: Therefore, option C is the correct answer. References: : Replicating Shares Across Regions and Cloud Platforms : Working with Organizations and Accounts : Replicating Databases Across Multiple Accounts : Replicating Shares Across Multiple Accounts

• Question 44:

  A company has a table with that has corrupted data, named Data. The company wants to recover the data as it was 5 minutes ago using cloning and Time Travel.

  What command will accomplish this?

  A. CREATE CLONE TABLE Recover_Data FROM Data AT(OFFSET => -60*5);
  B. CREATE CLONE Recover_Data FROM Data AT(OFFSET => -60*5);
  C. CREATE TABLE Recover_Data CLONE Data AT(OFFSET => -60*5);
  D. CREATE TABLE Recover Data CLONE Data AT(TIME => -60*5);
  C. CREATE TABLE Recover_Data CLONE Data AT(OFFSET => -60*5);

  ---

  Explanation/Reference:

  This is the correct command to create a clone of the table Data as it was 5 minutes ago using cloning and Time Travel. Cloning is a feature that allows creating a copy of a database, schema, table, or view without duplicating the

  data or metadata. Time Travel is a feature that enables accessing historical data (i.e. data that has been changed or deleted) at any point within a defined period. To create a clone of a table at a point in time in the past, the syntax is:

  CREATE TABLE CLONE AT (OFFSET => );

  The OFFSET parameter specifies the time difference in seconds from the present time. A negative value indicates a point in the past. For example, -60*5 means 5 minutes ago. Alternatively, the TIMESTAMP parameter can be used to specify

  an exact timestamp in the past. The clone will contain the data as it existed in the source table at the specified point in time12.

  References:

  Snowflake Documentation: Cloning Objects

  Snowflake Documentation: Cloning Objects at a Point in Time in the Past

• Question 45:

  Which of the below commands will use warehouse credits? (Choose three.)

  A. SHOW TABLES LIKE 'SNOWFL%';
  B. SELECT MAX(FLAKE_ID) FROM SNOWFLAKE;
  C. SELECT COUNT(*) FROM SNOWFLAKE;
  D. SELECT COUNT(FLAKE_ID) FROM SNOWFLAKE GROUP BY FLAKE_ID;
  B. SELECT MAX(FLAKE_ID) FROM SNOWFLAKE;
  C. SELECT COUNT(*) FROM SNOWFLAKE;
  D. SELECT COUNT(FLAKE_ID) FROM SNOWFLAKE GROUP BY FLAKE_ID;

  ---

  Explanation/Reference:

  Warehouse credits are used to pay for the processing time used by each virtual warehouse in Snowflake. A virtual warehouse is a cluster of compute resources that enables executing queries, loading data, and performing other DML

  operations. Warehouse credits are charged based on the number of virtual warehouses you use, how long they run, and their size1. Among the commands listed in the question, the following ones will use warehouse credits:

  The command that will not use warehouse credits is:

  References: : Understanding Compute Cost : MAX Function : COUNT Function : GROUP BY Clause : SHOW TABLES

• Question 46:

  An Architect has a VPN_ACCESS_LOGS table in the SECURITY_LOGS schema containing timestamps of the connection and disconnection, username of the user, and summary statistics.

  What should the Architect do to enable the Snowflake search optimization service on this table?

  A. Assume role with OWNERSHIP on future tables and ADD SEARCH OPTIMIZATION on the SECURITY_LOGS schema.
  B. Assume role with ALL PRIVILEGES including ADD SEARCH OPTIMIZATION in the SECURITY LOGS schema.
  C. Assume role with OWNERSHIP on VPN_ACCESS_LOGS and ADD SEARCH OPTIMIZATION in the SECURITY_LOGS schema.
  D. Assume role with ALL PRIVILEGES on VPN_ACCESS_LOGS and ADD SEARCH OPTIMIZATION in the SECURITY_LOGS schema.
  C. Assume role with OWNERSHIP on VPN_ACCESS_LOGS and ADD SEARCH OPTIMIZATION in the SECURITY_LOGS schema.

  ---

  Explanation/Reference:

  According to the SnowPro Advanced: Architect Exam Study Guide, to enable the search optimization service on a table, the user must have the ADD SEARCH OPTIMIZATION privilege on the table and the schema. The privilege can be granted explicitly or inherited from a higher-level object, such as a database or a role. The OWNERSHIP privilege on a table implies the ADD SEARCH OPTIMIZATION privilege, so the user who owns the table can enable the search optimization service on it. Therefore, the correct answer is to assume a role with OWNERSHIP on VPN_ACCESS_LOGS and ADD SEARCH OPTIMIZATION in the SECURITY_LOGS schema. This will allow the user to enable the search optimization service on the VPN_ACCESS_LOGS table and any future tables created in the SECURITY_LOGS schema. The other options are incorrect because they either grant excessive privileges or do not grant the required privileges on the table or the schema. References: SnowPro Advanced: Architect Exam Study Guide, page 11, section 2.3.1 Snowflake Documentation: Enabling the Search Optimization Service

• Question 47:

  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.
  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.

  ---

  Explanation/Reference:

  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 48:

  How do Snowflake databases that are created from shares differ from standard databases that are not created from shares? (Choose three.)

  A. Shared databases are read-only.
  B. Shared databases must be refreshed in order for new data to be visible.
  C. Shared databases cannot be cloned.
  D. Shared databases are not supported by Time Travel.
  E. Shared databases will have the PUBLIC or INFORMATION_SCHEMA schemas without explicitly granting these schemas to the share.
  F. Shared databases can also be created as transient databases.
  A. Shared databases are read-only.
  C. Shared databases cannot be cloned.
  D. Shared databases are not supported by Time Travel.

  ---

  Explanation/Reference:

  According to the SnowPro Advanced: Architect documents and learning resources, the ways that Snowflake databases that are created from shares differ from standard databases that are not created from shares are: Shared databases are read-only. This means that the data consumers who access the shared databases cannot modify or delete the data or the objects in the databases. The data providers who share the databases have full control over the data and the objects, and can grant or revoke privileges on them1. Shared databases cannot be cloned. This means that the data consumers who access the shared databases cannot create a copy of the databases or the objects in the databases. The data providers who share the databases can clone the databases or the objects, but the clones are not automatically shared2. Shared databases are not supported by Time Travel. This means that the data consumers who access the shared databases cannot use the AS OF clause to query historical data or restore deleted data. The data providers who share the databases can use Time Travel on the databases or the objects, but the historical data is not visible to the data consumers3. The other options are incorrect because they are not ways that Snowflake databases that are created from shares differ from standard databases that are not created from shares. Option B is incorrectbecause shared databases do not need to be refreshed in order for new data to be visible. The data consumers who access the shared databases can see the latest data as soon as the data providers update the data1. Option E is incorrect because shared databases will not have the PUBLIC or INFORMATION_SCHEMA schemas without explicitly granting these schemas to the share. The data consumers who access the shared databases can only see the objects that the data providers grant to the share, and the PUBLIC and INFORMATION_SCHEMA schemas are not granted by default4. Option F is incorrect because shared databases cannot be created as transient databases. Transient databases are databases that do not support Time Travel or Fail-safe, and can be dropped without affecting the retention period of the data. Shared databases are always created as permanent databases, regardless of the type of the source database5. References : Introduction to Secure Data Sharing | Snowflake Documentation, Cloning Objects | Snowflake Documentation, Time Travel | Snowflake Documentation, Working with Shares | Snowflake Documentation, CREATE DATABASE | Snowflake Documentation

• Question 49:

  An Architect uses COPY INTO with the ON_ERROR=SKIP_FILE option to bulk load CSV files into a table called TABLEA, using its table stage. One file named file5.csv fails to load. The Architect fixes the file and re-loads it to the stage with the exact same file name it had previously.

  Which commands should the Architect use to load only file5.csv file from the stage? (Choose two.)

  A. COPY INTO tablea FROM @%tablea RETURN_FAILED_ONLY = TRUE;
  B. COPY INTO tablea FROM @%tablea;
  C. COPY INTO tablea FROM @%tablea FILES = ('file5.csv');
  D. COPY INTO tablea FROM @%tablea FORCE = TRUE;
  E. COPY INTO tablea FROM @%tablea NEW_FILES_ONLY = TRUE;
  F. COPY INTO tablea FROM @%tablea MERGE = TRUE;
  B. COPY INTO tablea FROM @%tablea;
  C. COPY INTO tablea FROM @%tablea FILES = ('file5.csv');

  ---

  Explanation/Reference:

  Option A (RETURN_FAILED_ONLY) will only load files that previously failed to load. Since file5.csv already exists in the stage with the same name, it will not be considered a new file and will not be loaded.

  Option D (FORCE) will overwrite any existing data in the table. This is not desired as we only want to load the data from file5.csv.

  Option E (NEW_FILES_ONLY) will only load files that have been added to the stage since the last COPY command. This will not work because file5.csv was already in the stage before it was fixed.

  Option F (MERGE) is used to merge data from a stage into an existing table, creating new rows for any data not already present. This is not needed in this case as we simply want to load the data from file5.csv. Therefore, the architect can

  use either COPY INTO tablea FROM @%tablea or COPY INTO tablea FROM @%tablea FILES = ('file5.csv') to load only file5.csv from the stage. Both options will load the data from the specified file without overwriting any existing data or

  requiring additional configuration

• Question 50:

  Company A would like to share data in Snowflake with Company B. Company B is not on the same cloud platform as Company A.

  What is required to allow data sharing between these two companies?

  A. Create a pipeline to write shared data to a cloud storage location in the target cloud provider.
  B. Ensure that all views are persisted, as views cannot be shared across cloud platforms.
  C. Setup data replication to the region and cloud platform where the consumer resides.
  D. Company A and Company B must agree to use a single cloud platform: Data sharing is only possible if the companies share the same cloud provider.
  C. Setup data replication to the region and cloud platform where the consumer resides.

  ---

  Explanation/Reference:

  According to the SnowPro Advanced: Architect documents and learning resources, the requirement to allow data sharing between two companies that are not on the same cloud platform is to set up data replication to the region and cloud platform where the consumer resides. Data replication is a feature of Snowflake that enables copying databases across accounts in different regions and cloud platforms. Data replication allows data providers to securely share data with data consumers across different regions and cloud platforms by creating a replica database in the consumer's account. The replica database is read-only and automatically synchronized with the primary database in the provider's account. Data replication is useful for scenarios where data sharing is not possible or desirable due to latency, compliance, or security reasons1. The other options are incorrect because they are not required or feasible to allow data sharing between two companies that are not on the same cloud platform. Option A is incorrect because creating a pipeline to write shared data to a cloud storage location in the target cloud provider is not a secure or efficient way of sharing data. It would require additional steps to load the data from the cloud storage to the consumer's account, and it would not leverage the benefits of Snowflake's data sharing features. Option B is incorrect because ensuring that all views are persisted is not relevant for data sharing across cloud platforms. Views can be shared across cloud platforms as long as they reference objects in the same database. Persisting views is an option to improve the performance of querying views, but it is not required for data sharing2. Option D is incorrect because Company A and Company B do not need to agree to use a single cloud platform. Data sharing is possible across different cloud platforms using data replication or other methods, such as listings or auto- fulfillment3. References: ReplicatingDatabases Across Multiple Accounts | Snowflake Documentation, Persisting Views | Snowflake Documentation, Sharing Data Across Regions and Cloud Platforms | Snowflake Documentation