COF-C03 Question 25
Single answerStagesA data engineering team loads daily CSV files from an Amazon S3 bucket into a Snowflake table. They want to avoid hardcoding cloud credentials in every COPY INTO statement and also want Snowflake to remember the file format settings for reuse across multiple loads. Which approach best meets these requirements?
- A
Create an external stage that references the S3 location and includes a named file format and storage integration, then use COPY INTO from that stage.
- B
Create an internal stage and configure it to point to the S3 bucket, then use PUT to load files from S3 into the internal stage before copying.
- C
Use COPY INTO with the S3 URL directly in each command and specify the AWS access key, secret key, and file format options inline.
- D
Create a temporary table with stage properties for the S3 bucket and file format, then run COPY INTO from the table.
Show answer and explanation
Correct answer: A
Explanation
The best solution is to create an external stage on the S3 bucket and associate it with both a named file format and a storage integration. External stages are intended for data files kept in cloud storage, while internal stages are for files stored within Snowflake. A storage integration allows Snowflake to securely access cloud storage without embedding cloud credentials in SQL statements. A named file format lets teams reuse parsing settings such as CSV delimiters, headers, and compression across multiple COPY operations. This aligns with Snowflake best practices for secure, reusable, and maintainable data loading. Relevant Snowflake documentation areas include CREATE STAGE, storage integrations for S3, named file formats, and COPY INTO
.- A. Correct.
Correct. An external stage is designed to reference cloud storage such as Amazon S3. It can store the URL, a named file format, and authentication via a storage integration so credentials do not need to be embedded in each COPY INTO command. This is the recommended and maintainable approach for repeated loads from cloud storage.
- B. Incorrect.
Incorrect. Internal stages store data inside Snowflake-managed storage and do not point directly to an external S3 bucket. Also, PUT uploads files from a local client to an internal stage; it is not used to transfer files from S3 into an internal stage.
- C. Incorrect.
Incorrect. This approach can work technically, but it does not meet the requirement to avoid hardcoding credentials in every COPY statement or to centralize reusable file format settings. Inline credentials are also less secure and less maintainable than using a storage integration and stage definition.
- D. Incorrect.
Incorrect. Tables do not store stage connection properties for external cloud storage. Stages are separate database objects used to reference data loading locations and optional file format definitions.