Snowflake ARA-C01 Online Practice Questions and Exam Preparation

Snowflake ARA-C01 Online Questions & Answers

• Question 31:

  How does a standard virtual warehouse policy work in Snowflake?

  A. It conserves credits by keeping running clusters fully loaded rather than starting additional clusters.
  B. It starts only if the system estimates that there is a query load that will keep the cluster busy for at least 6 minutes.
  C. It starts only f the system estimates that there is a query load that will keep the cluster busy for at least 2 minutes.
  D. It prevents or minimizes queuing by starting additional clusters instead of conserving credits.
  D. It prevents or minimizes queuing by starting additional clusters instead of conserving credits.

  ---

  Explanation/Reference:

  A standard virtual warehouse policy is one of the two scaling policies available for multi-cluster warehouses in Snowflake. The other policy is economic. A standard policy aims to prevent or minimize queuing by starting additional clusters as soon as the current cluster is fully loaded, regardless of the number of queries in the queue. This policy can improve query performance and concurrency, but it may also consume more credits than an economic policy, which tries to conserve credits by keeping the running clusters fully loaded before starting additional clusters. The scaling policy can be set when creating or modifying a warehouse, and it can be changed at any time. References: Snowflake Documentation: Multi-cluster Warehouses Snowflake Documentation: Scaling Policy for Multi-cluster Warehouses

• Question 32:

  What is a characteristic of loading data into Snowflake using the Snowflake Connector for Kafka?

  A. The Connector only works in Snowflake regions that use AWS infrastructure.
  B. The Connector works with all file formats, including text, JSON, Avro, Ore, Parquet, and XML.
  C. The Connector creates and manages its own stage, file format, and pipe objects.
  D. Loads using the Connector will have lower latency than Snowpipe and will ingest data in real time.
  C. The Connector creates and manages its own stage, file format, and pipe objects.

  ---

  Explanation/Reference:

  According to the SnowPro Advanced: Architect documents and learning resources, a characteristic of loading data into Snowflake using the Snowflake Connector for Kafka is that the Connector creates and manages its own stage, file format, and pipe objects. The stage is an internal stage that is used to store the data files from the Kafka topics. The file format is a JSON or Avro file format that is used to parse the data files. The pipe is a Snowpipe object that is used to load the data files into the Snowflake table. The Connector automatically creates and configures these objects based on the Kafka configuration properties, and handles the cleanup and maintenance of these objects1. The other options are incorrect because they are not characteristics of loading data into Snowflake using the Snowflake Connector for Kafka. Option A is incorrect because the Connector works in Snowflake regions that use any cloud infrastructure, not just AWS. The Connector supports AWS, Azure, and Google Cloud platforms, and can load data across different regions and cloud platforms using data replication2. Option B is incorrect because the Connector does not work with all file formats, only JSON and Avro. The Connector expects the data in the Kafka topics to be in JSON or Avro format, and parses the data accordingly. Other file formats, such as text, ORC, Parquet, or XML, are not supported by the Connector3. Option D is incorrect because loads using the Connector do not have lower latency than Snowpipe, and do not ingest data in real time. The Connector uses Snowpipe to load data into Snowflake, and inherits the same latency and performance characteristics of Snowpipe. The Connector does not provide real-time ingestion, but near real-time ingestion, depending on the frequency and size of the data files4. References: Installing and Configuring the Kafka Connector | Snowflake Documentation, Sharing Data Across Regions and Cloud Platforms | Snowflake Documentation, Overview of the Kafka Connector | Snowflake Documentation, Using Snowflake Connector for Kafka With Snowpipe Streaming | Snowflake Documentation

• Question 33:

  What are some of the characteristics of result set caches? (Choose three.)

  A. Time Travel queries can be executed against the result set cache.
  B. Snowflake persists the data results for 24 hours.
  C. Each time persisted results for a query are used, a 24-hour retention period is reset.
  D. The data stored in the result cache will contribute to storage costs.
  E. The retention period can be reset for a maximum of 31 days.
  F. The result set cache is not shared between warehouses.
  B. Snowflake persists the data results for 24 hours.
  C. Each time persisted results for a query are used, a 24-hour retention period is reset.
  E. The retention period can be reset for a maximum of 31 days.

  ---

  Explanation/Reference:

  Comprehensive and Detailed According to the SnowPro Advanced: Architect documents and learning resources, some of the characteristics of result set caches are: Snowflake persists the data results for 24 hours. This means that the result set cache holds the results of every query executed in the past 24 hours, and can be reused if the same query is submitted again and the underlying data has not changed1. Each time persisted results for a query are used, a 24-hour retention period is reset. This means that the result set cache extends the lifetime of the results every time they are reused, up to a maximum of 31 days from the date and time that the query was first executed1. The retention period can be reset for a maximum of 31 days. This means that the result set cache will purge the results after 31 days, regardless of whether they are reused or not. After 31 days, the next time the query is submitted, a new result is generated and persisted1. The other options are incorrect because they are not characteristics of result set caches. Option A is incorrect because Time Travel queries cannot be executed against the result set cache. Time Travel queries use the AS OF clause to access historical data that is stored in the storage layer, not the result set cache2. Option D is incorrect because the data stored in the result set cache does not contribute to storage costs. The result set cache is maintained by the service layer, and does not incur any additional charges1. Option F is incorrect because the result set cache is shared between warehouses. The result set cache is available across virtual warehouses, so query results returned to one user are available to any other user on the system who executes the same query, provided the underlying data has not changed1. References: Using Persisted Query Results | Snowflake Documentation, Time Travel | Snowflake Documentation

• Question 34:

  What integration object should be used to place restrictions on where data may be exported?

  A. Stage integration
  B. Security integration
  C. Storage integration
  D. API integration
  B. Security integration

  ---

  Explanation/Reference:

  According to the SnowPro Advanced: Architect documents and learning resources, the integration object that should be used to place restrictions on where data may be exported is the security integration. A security integration is a Snowflake object that provides an interface between Snowflake and third-party security services, such as Okta, Duo, or Google Authenticator. A security integration can be used to enforce policies on data export, such as requiring multi-factor authentication (MFA) or restricting the export destination to a specific network or domain. A security integration can also be used to enable single sign-on (SSO) or federated authentication for Snowflake users1. The other options are incorrect because they are not integration objects that can be used to place restrictions on where data may be exported. Option A is incorrect because a stage integration is not a valid type of integration object in Snowflake. A stage is a Snowflake object that references a location where data files are stored, such as an internal stage, an external stage, or a named stage. A stage is not an integration object that provides an interface between Snowflake and third-party services2. Option C is incorrect because a storage integration is a Snowflake object that provides an interface between Snowflake and external cloud storage, such as Amazon S3, Azure Blob Storage, or Google Cloud Storage. A storage integration can be used to securely access data files from external cloud storage without exposing the credentials, but it cannot be used to place restrictions on where data may be exported3. Option D is incorrect because an API integration is a Snowflake object thatprovides an interface between Snowflake and third-party services that use REST APIs, such as Salesforce, Slack, or Twilio. An API integration can be used to securely call external REST APIs from Snowflake using the CALL_EXTERNAL_API function, but it cannot be used to place restrictions on where data may be exported4. References: CREATE SECURITY INTEGRATION | Snowflake Documentation, CREATE STAGE | Snowflake Documentation, CREATE STORAGE INTEGRATION | Snowflake Documentation, CREATE API INTEGRATION | Snowflake Documentation

• Question 35:

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

  ---

  Explanation/Reference:

  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

• Question 36:

  Which steps are recommended best practices for prioritizing cluster keys in Snowflake? (Choose two.)

  A. Choose columns that are frequently used in join predicates.
  B. Choose lower cardinality columns to support clustering keys and cost effectiveness.
  C. Choose TIMESTAMP columns with nanoseconds for the highest number of unique rows.
  D. Choose cluster columns that are most actively used in selective filters.
  E. Choose cluster columns that are actively used in the GROUP BY clauses.
  A. Choose columns that are frequently used in join predicates.
  D. Choose cluster columns that are most actively used in selective filters.

  ---

  Explanation/Reference:

  According to the Snowflake documentation, the best practices for choosing clustering keys are:

  Choose columns that are frequently used in join predicates. This can improve the join performance by reducing the number of micro-partitions that need to be scanned and joined.

  Choose columns that are most actively used in selective filters. This can improve the scan efficiency by skipping micro-partitions that do not match the filter predicates.

  Avoid using low cardinality columns, such as gender or country, as clustering keys. This can result in poor clustering and high maintenance costs. Avoid using TIMESTAMP columns with nanoseconds, as they tend to have very high

  cardinality and low correlation with other columns. This can also result in poor clustering and high maintenance costs.

  Avoid using columns with duplicate values or NULLs, as they can cause skew in the clustering and reduce the benefits of pruning. Cluster on multiple columns if the queries use multiple filters or join predicates. This can increase the chances

  of pruning more micro-partitions and improve the compression ratio.

  Clustering is not always useful, especially for small or medium-sized tables, or tables that are not frequently queried or updated. Clustering can incur additional costs for initially clustering the data and maintaining the clustering over time.

  References:

  Clustering Keys and Clustered Tables | Snowflake Documentation [Considerations for Choosing Clustering for a Table | Snowflake Documentation]

• Question 37:

  How can an Architect enable optimal clustering to enhance performance for different access paths on a given table?

  A. Create multiple clustering keys for a table.
  B. Create multiple materialized views with different cluster keys.
  C. Create super projections that will automatically create clustering.
  D. Create a clustering key that contains all columns used in the access paths.
  B. Create multiple materialized views with different cluster keys.

  ---

  Explanation/Reference:

  According to the SnowPro Advanced: Architect documents and learning resources, the best way to enable optimal clustering to enhance performance for different access paths on a given table is to create multiple materialized views with different cluster keys. A materialized view is a pre-computed result set that is derived from a query on one or more base tables. A materialized view can be clustered by specifying a clustering key, which is a subset of columns or expressions that determines how the data in the materialized view is co-located in micro-partitions. By creating multiple materialized views with different cluster keys, an Architect can optimize the performance of queries that use different access paths on the same base table. For example, if a base table has columns A, B, C, and D, and there are queries that filter on A and B, or on C and D, or on A and C, the Architect can create three materialized views, each with a different cluster key: (A, B), (C, D), and (A, C). This way, each query can leverage the optimal clustering of the corresponding materialized view and achieve faster scan efficiency and better compression. References: Snowflake Documentation: Materialized Views Snowflake Learning: Materialized Views https://www.snowflake.com/blog/using-materialized-views-to-solve-multi-clustering- performance-problems/

• Question 38:

  How is the change of local time due to daylight savings time handled in Snowflake tasks? (Choose two.)

  A. A task scheduled in a UTC-based schedule will have no issues with the time changes.
  B. Task schedules can be designed to follow specified or local time zones to accommodate the time changes.
  C. A task will move to a suspended state during the daylight savings time change.
  D. A frequent task execution schedule like minutes may not cause a problem, but will affect the task history.
  E. A task schedule will follow only the specified time and will fail to handle lost or duplicated hours.
  A. A task scheduled in a UTC-based schedule will have no issues with the time changes.
  B. Task schedules can be designed to follow specified or local time zones to accommodate the time changes.

  ---

  Explanation/Reference:

  According to the Snowflake documentation1 and the web search results2, these two statements are true about how the change of local time due to daylight savings time is handled in Snowflake tasks. A task is a feature that allows scheduling and executing SQL statements or stored procedures in Snowflake. A task can be scheduled using a cron expression that specifies the frequency and time zone of the task execution. A task scheduled in a UTC-based schedule will have no issues with the time changes. UTC is a universal time standard that does not observe daylight savings time. Therefore, a task that uses UTC as the time zone will run at the same time throughout the year, regardless of the local time changes1. Task schedules can be designed to follow specified or local time zones to accommodate the time changes. Snowflake supports using any valid IANA time zone identifier in the cron expression for a task. This allows the task to run according to the local time of the specified time zone, which may include daylight savings time adjustments. For example, a task that uses Europe/London as the time zone will run one hour earlier or later when the local time switches between GMT and BST12. References: Snowflake Documentation: Scheduling Tasks Snowflake Community: Do the timezones used in scheduling tasks in Snowflake adhere to daylight savings?

• Question 39:

  An Architect would like to save quarter-end financial results for the previous six years.

  Which Snowflake feature can the Architect use to accomplish this?

  A. Search optimization service
  B. Materialized view
  C. Time Travel
  D. Zero-copy cloning
  E. Secure views
  D. Zero-copy cloning

  ---

  Explanation/Reference:

  Zero-copy cloning is a Snowflake feature that can be used to save quarter- end financial results for the previous six years. Zero-copy cloning allows creating a copy of a database, schema, table, or view without duplicating the data or metadata. The clone shares the same data files as the original object, but tracks any changes made to the clone or the original separately. Zero-copy cloning can be usedto create snapshots of data at different points in time, such as quarter-end financial results, and preserve them for future analysis or comparison. Zero-copy cloning is fast, efficient, and does not consume any additional storage space unless the data is modified1. References: Zero-Copy Cloning | Snowflake Documentation

• Question 40:

  When loading data into a table that captures the load time in a column with a default value of either CURRENT_TIME () or CURRENT_TIMESTAMP() what will occur?

  A. All rows loaded using a specific COPY statement will have varying timestamps based on when the rows were inserted.
  B. Any rows loaded using a specific COPY statement will have varying timestamps based on when the rows were read from the source.
  C. Any rows loaded using a specific COPY statement will have varying timestamps based on when the rows were created in the source.
  D. All rows loaded using a specific COPY statement will have the same timestamp value.
  D. All rows loaded using a specific COPY statement will have the same timestamp value.

  ---

  Explanation/Reference:

  According to the Snowflake documentation, when loading data into a table that captures the load time in a column with a default value of either CURRENT_TIME () or CURRENT_TIMESTAMP(), the default value is evaluated once per COPY statement, not once per row. Therefore, all rows loaded using a specific COPY statement will have the same timestamp value. This behavior ensures that the timestamp value reflects the time when the data was loaded into the table, not when the data was read from the source or created in the source. References: Snowflake Documentation: Loading Data into Tables with Default Values Snowflake Documentation: COPY INTO table