COF-C03 Question 106
Single answerExternalA data engineering team needs to load CSV files from an Amazon S3 bucket into Snowflake every hour. The bucket is managed by another team, and the Snowflake administrators want to avoid storing long-term cloud credentials directly in COPY statements. They also want a reusable object that can be referenced by multiple file formats and stages. Which Snowflake object should be created to meet these requirements?
- A
An external stage that directly embeds the AWS access key ID and secret key in the stage definition
- B
A storage integration that securely associates Snowflake with the S3 bucket using an IAM trust relationship
- C
A database role with privileges on the target table and the S3 bucket
- D
An internal stage with directory tables enabled
Show answer and explanation
Correct answer: B
Explanation
The correct choice is a storage integration. In Snowflake, storage integrations are designed to allow Snowflake to access external cloud storage securely without hardcoding cloud provider credentials in SQL statements or stage definitions. For Amazon S3, this is typically done by creating an IAM role in AWS and configuring a trust relationship so Snowflake can assume that role. External stages can then reference the storage integration and the bucket path. This is the recommended best practice for secure, reusable access to external storage. By contrast, embedding credentials in a stage is less secure and harder to manage, database roles do not grant cloud storage access, and internal stages are for Snowflake-managed storage rather than external S3 locations. Relevant Snowflake documentation includes guidance on CREATE STORAGE INTEGRATION, external stages, and loading data from Amazon S3.
- A. Incorrect.
Incorrect. An external stage can reference S3, but embedding AWS credentials directly in the stage definition does not meet the stated goal of avoiding long-term cloud credentials in object definitions. While this approach can work technically, Snowflake best practice is to use a storage integration for external cloud storage access when possible.
- B. Correct.
Correct. A storage integration is the recommended Snowflake object for securely delegating access to external cloud storage such as Amazon S3. It creates a reusable security object that stages can reference, avoids placing cloud credentials in COPY commands or stage definitions, and uses a trust relationship with an AWS IAM role.
- C. Incorrect.
Incorrect. A database role controls access to Snowflake objects, not to cloud storage resources like an S3 bucket. Roles are necessary for authorization inside Snowflake, but they do not establish secure connectivity or authentication between Snowflake and Amazon S3.
- D. Incorrect.
Incorrect. An internal stage stores files inside Snowflake-managed storage, not in Amazon S3. Directory tables are relevant for listing and tracking files for stages, but they do not solve the requirement to access externally managed files in S3 without embedding credentials.