COF-C03 Question 216
Single answerExternal stagesA data engineering team stores daily CSV files in an Amazon S3 bucket managed by another department. The team needs Snowflake users to load only files from the s3://company-data/sales/daily/ path into a table. Security policy requires avoiding embedded cloud credentials in SQL objects whenever possible, and the S3 bucket owner is willing to grant access to a Snowflake-managed identity. Which approach best meets these requirements when creating the external stage?
- A
Create an external stage that points to s3://company-data/sales/daily/ and uses a STORAGE INTEGRATION configured for the S3 bucket.
- B
Create an internal stage and use PUT to upload files directly from the S3 bucket into Snowflake before loading.
- C
Create an external stage for the entire bucket s3://company-data/ and rely on the COPY INTO command to prevent access to other prefixes.
- D
Create an external stage that points to s3://company-data/sales/daily/ and includes AWS_KEY_ID and AWS_SECRET_KEY in the stage definition.
Show answer and explanation
Correct answer: A
Explanation
The best answer is to create an external stage scoped to the required S3 path and use a STORAGE INTEGRATION. In Snowflake, external stages reference files in external cloud storage such as Amazon S3, Google Cloud Storage, or Azure Blob Storage. For S3, a storage integration is the recommended approach because Snowflake manages the trust relationship to AWS without requiring access keys to be hard-coded in SQL objects. This improves security and simplifies credential rotation. Additionally, defining the stage URL at the specific prefix, such as s3://company-data/sales/daily/, supports least-privilege access and reduces the chance of users loading unintended files. These practices are consistent with Snowflake documentation for CREATE STAGE and storage integrations for external stages.
- A. Correct.
Correct. For Amazon S3, Snowflake best practice is to use a STORAGE INTEGRATION so credentials are not embedded directly in the stage definition. The stage URL can be scoped to a specific prefix such as s3://company-data/sales/daily/, which helps limit access to the required path. The bucket owner can grant access to the IAM user or role associated with the Snowflake storage integration, aligning with the requirement to avoid embedded credentials.
- B. Incorrect.
Incorrect. An internal stage does not read directly from an S3 bucket. The PUT command uploads files from a local client machine to an internal stage; it is not used to transfer data from S3 into Snowflake. This option confuses internal stages and PUT behavior with external stage access patterns.
- C. Incorrect.
Incorrect. Although an external stage can point to a bucket root, this does not best satisfy the requirement to limit access to only the daily sales path. Scoping the stage URL to the specific prefix is the more appropriate design. Relying only on COPY INTO patterns is weaker from a least-privilege perspective because the stage itself would reference a broader location than necessary.
- D. Incorrect.
Incorrect. Snowflake does support external stages that use cloud credentials directly, but embedding AWS keys in the stage definition is not preferred when a STORAGE INTEGRATION can be used. This option violates the stated security requirement to avoid embedded credentials whenever possible.