COF-C03 Question 26
Single answerStagesA data engineering team loads daily CSV files from an Amazon S3 bucket into Snowflake. The files are delivered by another department, and the Snowflake team must not manage or store cloud credentials inside Snowflake objects. The team also wants to avoid creating a separate stage for every ad hoc load because different prefixes in the same bucket are used by different business units. Which approach best meets these requirements while allowing the team to load files from specific S3 paths when needed?
- A
Create an external stage that uses a storage integration, and reference subpaths under the stage location in COPY INTO commands when loading different business unit folders.
- B
Create an internal named stage and configure it to point to the S3 bucket, then use PUT to upload the external files into Snowflake before loading.
- C
Create separate user stages for each business unit and grant each team access to its own user stage for direct loading from S3.
- D
Create an external stage with AWS key ID and secret key embedded in the stage definition, because external stages require credentials stored directly in Snowflake.
Show answer and explanation
Correct answer: A
Explanation
The best solution is to use an external stage backed by Amazon S3 with a storage integration. Storage integrations provide a secure, centralized way for Snowflake to access external cloud storage without embedding access keys in stage definitions or COPY statements. This aligns with Snowflake security best practices. Also, a single external stage does not have to be recreated for each business unit folder; loads can reference subpaths relative to the stage URL or use file selection techniques such as PATTERN. Internal stages, including named stages and user stages, store data in Snowflake rather than referencing S3 directly. Relevant Snowflake documentation includes guidance on creating external stages, using storage integrations for S3, and loading data with COPY INTO from stage locations and paths.
- A. Correct.
Correct. An external stage is the right choice for data already stored in S3. Using a storage integration is the Snowflake-recommended approach because it avoids embedding cloud credentials in stage definitions. A single external stage can reference a bucket or common prefix, and COPY INTO can target deeper subpaths or use PATTERN to load files from specific folders as needed. This satisfies both the security requirement and the operational need to avoid many separate stages.
- B. Incorrect.
Incorrect. Internal named stages store files inside Snowflake-managed storage and are used with PUT to upload local files from a client machine, not to point directly to an S3 bucket. If the source files already exist in S3, an external stage is more appropriate. This option also adds unnecessary movement of data.
- C. Incorrect.
Incorrect. User stages are internal stages automatically associated with individual users. They do not connect directly to S3, so they cannot be used for direct loading from an S3 bucket. This distractor reflects a common confusion between internal stages and external stages.
- D. Incorrect.
Incorrect. External stages can be defined with inline credentials, but they do not require that approach. In fact, when the requirement is to avoid managing or storing credentials in Snowflake objects, a storage integration is the preferred best practice. This option conflicts with the stated security requirement.