ARA-C01 Question 211
Single answerDatabasesA global retail company uses a single Snowflake account with separate databases for DEV, TEST, and PROD. The PROD database contains several schemas with shared dimensions and regulated customer data. The architecture team wants to create a new UAT database that must meet these requirements: it should be provisioned quickly, preserve the exact object structure of PROD at a point in time, avoid duplicating storage for unchanged data, and allow testers to modify data without affecting PROD. Which approach should the architect recommend?
- A
Create the UAT database as a zero-copy clone of the PROD database
- B
Create the UAT database using CTAS statements for each table in PROD
- C
Replicate the PROD database into UAT using database replication within the same account
- D
Create secure views in a new UAT database that reference objects in the PROD database
Show answer and explanation
Correct answer: A
Explanation
The best answer is to create the UAT database as a zero-copy clone of the PROD database. Snowflake cloning is designed for exactly this type of use case: quickly creating isolated environments for development, testing, or UAT from production data at a specific point in time. Because cloning is metadata-based at creation time, it is fast and storage-efficient. Storage is only consumed for changed data through Snowflake's copy-on-write mechanism. This makes cloning a core best practice for database environment management in Snowflake.
By contrast, CTAS-based approaches are slower, consume more storage immediately, and require more operational effort to reproduce database objects accurately. Replication addresses cross-account or cross-region/cloud business continuity scenarios rather than same-account writable UAT creation. Secure views are useful for controlled access to sensitive data but do not provide an isolated test database with independent write behavior.
Relevant Snowflake documentation and best practices include guidance on zero-copy cloning for databases, schemas, and tables, as well as documentation distinguishing cloning from replication and data sharing/view-based access patterns.
- A. Correct.
Correct. A zero-copy clone of a database creates a point-in-time copy of the database and its contained objects without initially duplicating the underlying micro-partition storage. Snowflake uses copy-on-write behavior, so unchanged data continues to share storage, while changes made in UAT are isolated from PROD. This directly satisfies the requirements for rapid provisioning, point-in-time consistency, storage efficiency, and independent testing changes.
- B. Incorrect.
Incorrect. CTAS (CREATE TABLE AS SELECT) would require recreating objects manually and copying data into new tables, which increases provisioning time and immediately consumes additional storage. It also does not preserve the full database object structure as cleanly as cloning, especially for non-table objects such as grants, sequences, and other metadata-dependent structures. This is a common misconception when teams think only about copying data rather than cloning environments.
- C. Incorrect.
Incorrect. Database replication is intended for replicating databases across accounts and/or regions/clouds for business continuity, disaster recovery, or data distribution scenarios. It is not the appropriate mechanism for creating a fast, writable test copy inside the same account for isolated modifications. In addition, replicated databases are generally read-only on the secondary side until failover, which does not meet the requirement for testers to make changes.
- D. Incorrect.
Incorrect. Secure views can expose PROD data into another database, but they do not create an independent point-in-time copy of the object structure or data. Testers modifying data through this approach would either be unable to do so or risk affecting PROD depending on the implementation. This option confuses data sharing/access abstraction with environment provisioning.