COF-C03 Question 214
Single answerInternal stagesA data engineering team needs to load daily CSV files into a Snowflake table. For security reasons, the files must remain inside Snowflake-managed storage and should only be accessible to users who have privileges on the target table. The team also wants to minimize setup and avoid managing separate stage objects. Which approach best meets these requirements?
- A
Upload the files to the table stage for the target table and load them with COPY INTO
- B
Create an external stage that points to Amazon S3 and restrict access with a storage integration
- C
Create a named internal stage and grant USAGE on the stage to all users who need to query the table
- D
Upload the files to the user stage of the data engineer who owns the load process and let analysts query from there
Show answer and explanation
Correct answer: A
Explanation
The best answer is to use the table stage. Snowflake provides three internal stage types: user stages, table stages, and named internal stages. In this scenario, the key requirements are: files must stay in Snowflake-managed storage, access should align with the target table's privileges, and setup should be minimal. A table stage satisfies all three. It is automatically available for each table, requires no separate CREATE STAGE object, and is appropriate for loading data intended for that specific table. By contrast, external stages store data outside Snowflake, named internal stages add separate administration and privilege management, and user stages are scoped to an individual user rather than a shared table-centric workflow. This aligns with Snowflake documentation on internal stages, especially the distinctions among user, table, and named stages and their common use cases.
- A. Correct.
Correct. A table stage is an internal stage automatically associated with a table and uses Snowflake-managed storage. It is a good fit when staged files are specifically for loading into that table and the team wants minimal administrative overhead. Access to a table stage is tied to privileges on the table, which aligns with the requirement that only users with privileges on the target table can access the staged files.
- B. Incorrect.
Incorrect. An external stage uses cloud storage outside Snowflake-managed storage, which violates the requirement that files remain inside Snowflake-managed storage. Although storage integrations are a best practice for securing external stages, they do not satisfy the stated storage requirement.
- C. Incorrect.
Incorrect. A named internal stage does use Snowflake-managed storage, but it is a separate object that requires explicit stage management and privilege administration. The scenario specifically wants to avoid managing separate stage objects. Also, granting broad USAGE on the stage to all users who query the table may expose staged files more widely than necessary.
- D. Incorrect.
Incorrect. A user stage is tied to a specific user and is typically used for that user's own file loading or unloading workflows. It is not appropriate when access should be governed by table privileges for a shared production loading process. Analysts also do not query files directly from a user stage as a replacement for controlled table-based access.