SnowPro Associate: Platform Question 47
Single answerData loadingA retail company receives hourly CSV files from a partner in an Amazon S3 bucket. The files are added continuously throughout the day, and analysts need the data loaded into a Snowflake table with minimal operational effort. The company wants to avoid reloading files that were already processed. Which solution best meets these requirements?
- A
Create an external stage on the S3 bucket, define a pipe with AUTO_INGEST enabled, and use COPY INTO the target table from the stage.
- B
Schedule a task to run INSERT statements every hour against the files in S3 using the SELECT statement on the stage.
- C
Use PUT to upload the files from S3 into an internal stage, then run COPY INTO manually whenever analysts need fresh data.
- D
Create a materialized view on top of the external stage so new files are automatically available in the target table.
Show answer and explanation
Correct answer: A
Explanation
The best answer is to use Snowpipe with an external stage on Amazon S3 and AUTO_INGEST. This is the standard Snowflake pattern for near-continuous ingestion of files that arrive in cloud object storage. Snowpipe executes a COPY INTO statement automatically as new files are detected through cloud event notifications, reducing operational overhead compared with polling or manual loading. Snowflake also maintains metadata about loaded files, helping prevent accidental duplicate loads under normal loading patterns. By contrast, tasks and ad hoc queries can be used in some ingestion designs, but they are less aligned with the requirement for minimal effort and automatic detection of new files. According to Snowflake best practices and documentation, use COPY INTO for bulk loading and Snowpipe for continuous file ingestion from supported cloud storage locations.
- A. Correct.
Correct. Snowpipe with AUTO_INGEST is designed for continuous data loading from cloud storage such as Amazon S3. By creating an external stage and a pipe that uses COPY INTO, Snowflake can automatically load newly arrived files with minimal administration. Snowpipe also tracks load history to help prevent the same file from being loaded multiple times unintentionally, which aligns with the requirement to avoid reprocessing files.
- B. Incorrect.
Incorrect. While Snowflake can query files in stages with SELECT and tasks can be scheduled, this approach is not the best fit for continuous, low-maintenance ingestion of arriving files. It also shifts more operational burden to scheduling and logic management. In practice, COPY INTO is the standard bulk-loading mechanism, and Snowpipe is the preferred automated option for incremental file arrival.
- C. Incorrect.
Incorrect. PUT uploads local files to an internal stage; it does not move files directly from Amazon S3 into Snowflake. This option also requires manual execution, which conflicts with the requirement for minimal operational effort and continuous loading.
- D. Incorrect.
Incorrect. Materialized views cannot be used to automatically ingest staged files into a physical target table. External stages store file references, not table data, and materialized views are not the mechanism for loading files from cloud storage into Snowflake tables.