ARA-C01 Question 220
Single answerStagesA data engineering team loads daily CSV files from an Amazon S3 bucket into Snowflake. The files contain sensitive customer data, and the security team requires that cloud storage credentials not be embedded in ad hoc SQL statements or exposed to individual users. Multiple ETL jobs across schemas must reuse the same external location, and the architecture team wants a design that simplifies ongoing credential rotation. Which approach should the architect recommend?
- A
Create named external stages that reference a STORAGE INTEGRATION, and grant the ETL roles USAGE on those stages as needed.
- B
Have each ETL job use COPY INTO with a direct S3 URL and AWS key ID/secret key in the CREDENTIALS clause so the jobs remain self-contained.
- C
Create internal named stages for each schema and use PUT to upload the files from S3 into Snowflake before loading them.
- D
Create temporary stages for each ETL session and store the S3 credentials in session variables to avoid persisting them in database objects.
Show answer and explanation
Correct answer: A
Explanation
The best answer is to use named external stages with a STORAGE INTEGRATION. In Snowflake, external stages define reusable references to cloud storage locations such as Amazon S3, and STORAGE INTEGRATION allows Snowflake to authenticate securely to that storage without hardcoding cloud credentials in COPY commands or application code. This design improves security, supports centralized administration, and simplifies credential rotation. It also fits real-world enterprise patterns where multiple pipelines need to reuse the same storage location. Snowflake documentation and best practices for data loading emphasize using storage integrations for secure access to cloud storage and using named stages to improve reusability and governance.
- A. Correct.
Correct. A named external stage backed by a STORAGE INTEGRATION is the recommended pattern for securely accessing cloud storage from Snowflake without embedding cloud credentials in SQL. STORAGE INTEGRATION creates a secure trust relationship between Snowflake and the cloud provider, allowing centralized management and simpler credential rotation. Named stages are reusable across loads and can be granted to ETL roles with appropriate privileges, which aligns well with the requirement for shared access across multiple ETL jobs and schemas.
- B. Incorrect.
Incorrect. While Snowflake supports loading from direct cloud URLs with credentials in SQL, this approach conflicts with the stated security requirement because the credentials appear in SQL statements or job definitions. It also creates operational overhead because credential rotation would require updating every ETL process that embeds the keys. This is a common but less secure pattern compared with using a STORAGE INTEGRATION.
- C. Incorrect.
Incorrect. Internal stages are for files stored inside Snowflake-managed storage. Using PUT is intended to upload local files to internal stages, not to transfer files directly from S3 into Snowflake. In practice, if the source data is already in S3, creating an external stage is the more appropriate architecture. This option also adds unnecessary movement of sensitive data.
- D. Incorrect.
Incorrect. Temporary stages are session-scoped and are not suitable for shared, reusable enterprise ETL patterns across schemas and jobs. Storing cloud credentials in session variables still does not meet the requirement to avoid exposing credentials to users or jobs. It also makes credential management and rotation more complex rather than simpler.