COF-C03 Question 107
Single answerExternalA data engineering team needs to load daily CSV files from an Amazon S3 bucket into Snowflake. The files are managed by another team, so the Snowflake team must not store long-term cloud credentials in code. The solution should allow Snowflake to securely read only from a specific S3 location and support ongoing automated loads. Which approach should the architect recommend?
- A
Create an external stage that uses a STORAGE INTEGRATION mapped to the specific S3 bucket/prefix, then use that stage for loading
- B
Create an internal named stage and configure it with the S3 bucket URL so Snowflake can read the files without cloud credentials
- C
Embed AWS access key and secret key directly in each COPY INTO command because Snowflake requires inline credentials for automated S3 loads
- D
Create a file format object only, and reference the S3 URL directly in queries because file formats also manage cloud authentication
Show answer and explanation
Correct answer: A
Explanation
The best answer is to use an external stage with a STORAGE INTEGRATION. In Snowflake, storage integrations are the recommended method for granting Snowflake access to external cloud storage such as Amazon S3 while avoiding hard-coded credentials. They allow administrators to restrict access to specific storage locations and support secure, repeatable loading patterns with commands like COPY INTO and automated ingestion workflows. Internal stages are only for Snowflake-managed storage, and file formats do not handle authentication. This aligns with Snowflake best practices and documentation for external stages and storage integrations.
- A. Correct.
Correct. A STORAGE INTEGRATION is the recommended Snowflake mechanism for accessing external cloud storage without embedding long-term credentials in SQL or application code. For Amazon S3, the integration creates a secure trust relationship between Snowflake and AWS IAM. An external stage can then reference the integration and a specific URL, allowing controlled access to only approved locations. This is the preferred approach for recurring and automated data loads.
- B. Incorrect.
Incorrect. An internal named stage stores files within Snowflake-managed storage, not in Amazon S3. It cannot be pointed at an S3 bucket URL to read external files directly. This option confuses internal stages with external stages.
- C. Incorrect.
Incorrect. While Snowflake supports credentials in stage definitions or commands in some cases, embedding AWS keys directly in COPY statements is not the recommended best practice for ongoing automated access. It increases security risk, complicates rotation, and violates the requirement to avoid long-term credentials in code. STORAGE INTEGRATION is designed specifically to address this concern.
- D. Incorrect.
Incorrect. A file format object defines how to parse file contents, such as CSV delimiters, compression, and header handling. It does not provide authentication or authorization to cloud storage. Access to S3 must be configured through an external stage using credentials or, preferably, a STORAGE INTEGRATION.