ARA-C01 Exam Dumps : SnowPro Advanced: Architect Certification Exam

Snowflake supports cloning of various objects, such as databases, schemas, tables, stages, file formats, sequences, streams, tasks, and roles. Cloning creates a copy of an existing object in the system without copying the data or metadata. Cloning is also known as zero-copy cloning1.

Among the objects listed in the question, the following ones can be cloned in Snowflake:

Permanent table: A permanent table is a type of table that has a Fail-safe period and a Time Travel retention period of up to 90 days. A permanent table can be cloned using the CREATE TABLE … CLONE command2. Therefore, option A is correct.

Transient table: A transient table is a type of table that does not have a Fail-safe period and can have a Time Travel retention period of either 0 or 1 day. A transient table can also be cloned using the CREATE TABLE … CLONE command2. Therefore, option B is correct.

External table: An external table is a type of table that references data files stored in an external location, such as Amazon S3, Google Cloud Storage, or Microsoft Azure Blob Storage. An external table can be cloned using the CREATE EXTERNAL TABLE … CLONE command3. Therefore, option D is correct.

The following objects listed in the question cannot be cloned in Snowflake:

Temporary table: A temporary table is a type of table that is automatically dropped when the session ends or the current user logs out. Temporary tables do not support cloning4. Therefore, option C is incorrect.

Internal stage: An internal stage is a type of stage that is managed by Snowflake and stores files in Snowflake’s internal cloud storage. Internal stages do not support cloning5. Therefore, option E is incorrect.

 Cloning Considerations : CREATE TABLE … CLONE : CREATE EXTERNAL TABLE … CLONE : Temporary Tables : Internal Stages

The correct answer is D because the CURRENT_TIME function returns the current timestamp at the start of the statement execution, not at the time of the record insertion. Therefore, if the load operation takes some time to complete, the CURRENT_TIME value may be earlier than the actual load time.

Option A is incorrect because the parameter setup mismatches do not affect the timestamp values. The parameters are used to control the behavior and performance of the load operation, such as the file format, the error handling, the purge option, etc.

Option B is incorrect because the Snowflake timezone parameter and the cloud provider’s parameters are independent of each other. The Snowflake timezone parameter determines the session timezone for displaying and converting timestamp values, while the cloud provider’s parameters determine the physical location and configuration of the storage and compute resources.

Option C is incorrect because the localtimestamp and systimestamp functions are not relevant for the Snowpipe load operation. The localtimestamp function returns the current timestamp in the session timezone, while the systimestamp function returns the current timestamp in the system timezone. Neither of them reflect the actual load time of the records. References:

Snowflake Documentation: Loading Data Using Snowpipe: This document explains how to use Snowpipe to continuously load data from external sources into Snowflake tables. It also describes the syntax and usage of the COPY INTO command, which supports various options and parameters to control the loading behavior.

Snowflake Documentation: Date and Time Data Types and Functions: This document explains the different data types and functions for working with date and time values in Snowflake. It also describes how to set and change the session timezone and the system timezone.

Snowflake Documentation: Querying Metadata: This document explains how to query the metadata of the objects and operations in Snowflake using various functions, views, and tables. It also describes how to access the copy history information using the COPY_HISTORY function or the COPY_HISTORY view.

Zero-copy cloning in Snowflake creates a metadata-only snapshot of databases, schemas, or tables, enabling rapid environment refreshes without duplicating data. However, not all dependent objects behave identically during cloning.

Pipes that reference external stages are not cloned because external stages depend on credentials and integrations that may not exist or be valid in the cloned environment (Answer C). This is a documented Snowflake behavior and an important consideration when cloning production environments into development.

Regarding privileges, a cloned database inherits privileges granted on child objects (such as schemas and tables), but it does not inherit privileges granted directly on the source database itself (Answer E). This design prevents unintended access escalation and ensures that security must be explicitly re-granted at the database level.

Understanding these nuances is essential for SnowPro Architect candidates when designing environment promotion strategies and managing data lifecycle processes using cloning.

=========

QUESTION NO: 40 [Performance Optimization and Monitoring]

Two SQL queries perform the same task, but one consistently outperforms the other in reports, even though the SQL logic appears equivalent.

What step ensures the comparison is accurate?

A. Eliminate compilation time metrics.

B. Ensure both queries use the same clustering keys.

C. Disable result cache usage for the faster query.

D. Suspend and resume the virtual warehouse between queries.

Answer: C

Snowflake’s result cache can cause misleading performance comparisons. If one query benefits from cached results while another does not, execution time metrics will not reflect true performance differences. Disabling result cache usage for the faster query ensures both queries execute fully and fairly (Answer C).

Suspending and resuming a warehouse clears the local data cache but does not necessarily control result cache usage. Compilation time is generally minimal compared to execution time and does not explain consistent performance differences. Clustering keys are unrelated if both queries access the same data.

This question reinforces a core SnowPro Architect principle: accurate performance testing requires controlling for caching effects. Architects must understand Snowflake’s caching layers to correctly interpret query performance metrics.