COF-C03 Question 246
Single answerStorage integrationA data engineering team wants Snowflake to load daily CSV files from an Amazon S3 bucket into a table. The security team requires that Snowflake access the bucket without embedding long-term AWS keys in the stage definition, and they want access limited to only the approved S3 location. Which approach should the Snowflake administrator use?
- A
Create a STORAGE INTEGRATION object for S3, grant it access to the approved bucket path, and reference the integration in the external stage.
- B
Create an external stage with AWS_KEY_ID and AWS_SECRET_KEY credentials directly in the stage so that Snowflake can read the files.
- C
Create a FILE FORMAT object and attach an IAM policy to it so Snowflake can assume the AWS role when loading data.
- D
Create an internal stage, copy the files from S3 into the internal stage, and then configure a storage integration on the internal stage.
Show answer and explanation
Correct answer: A
Explanation
The best solution is to use a Snowflake storage integration for Amazon S3 and reference it from an external stage. Storage integrations let Snowflake securely access external cloud storage without exposing long-term cloud credentials in SQL objects. They also support restricting access to approved locations through allowed storage locations, which helps satisfy least-privilege security requirements. In practice, the setup includes creating the STORAGE INTEGRATION, retrieving the generated Snowflake IAM user and external ID information, configuring the AWS IAM trust and permissions for the approved bucket path, and then creating the external stage with the STORAGE_INTEGRATION parameter. This follows Snowflake best practices for secure external stage access documented under storage integrations and external stages.
- A. Correct.
Correct. A storage integration is the recommended way to allow Snowflake to access cloud storage such as Amazon S3 without storing long-term cloud credentials in stage definitions. For S3, the administrator creates a STORAGE INTEGRATION, configures allowed storage locations, and then uses that integration in a CREATE STAGE statement. This aligns with least-privilege access and centralizes credential management.
- B. Incorrect.
Incorrect. Although older patterns used explicit cloud credentials in stage definitions, this does not meet the stated security requirement to avoid embedding long-term AWS keys. Storage integrations are designed specifically to replace this approach with a more secure and manageable mechanism.
- C. Incorrect.
Incorrect. A FILE FORMAT object only defines how Snowflake should interpret file contents, such as CSV delimiters, compression, and header handling. It has no role in cloud authentication, IAM trust, or bucket authorization. This option reflects a misunderstanding of the separation between file parsing and storage access.
- D. Incorrect.
Incorrect. Storage integrations are used with external cloud storage, not internal stages. Internal stages are managed by Snowflake and do not require a cloud storage integration for access. This option also adds unnecessary data movement and does not address the direct, secure access requirement for the S3 bucket.