DAA-C01 Question 6
Single answerRetrieve data from a sourceA retail analytics team needs to enrich a Snowflake sales fact table with daily foreign exchange rates that are published by an external provider as JSON files in cloud object storage. New files arrive each day, and analysts want the data available in Snowflake with minimal manual effort. The team also wants to avoid repeatedly reloading the same files. Which approach should the data analyst recommend?
- A
Create an external stage pointing to the cloud storage location, define a JSON file format, create a table for the exchange rates, and use Snowpipe to automatically load new files into the table as they arrive.
- B
Query the JSON files directly from the cloud storage location in every dashboard by using a regular internal stage so the latest files are always read at runtime.
- C
Create a materialized view over the cloud storage location and let Snowflake automatically refresh it whenever new JSON files are added.
- D
Use a one-time COPY INTO command from the cloud storage location each morning without tracking load history, because Snowflake will not load duplicate files into a table more than once under any circumstance.
Show answer and explanation
Correct answer: A
Explanation
The best answer is to use an external stage plus a JSON file format and Snowpipe to continuously ingest new JSON files from cloud storage into a Snowflake table. This pattern is practical and aligns with Snowflake best practices for retrieving data from external sources when new files arrive regularly and analysts need queryable table data with low operational overhead. Snowpipe is designed for automated loading of new files and works with cloud event notifications or auto-ingest integrations depending on the platform. Snowflake documentation for stages, file formats, COPY INTO, and Snowpipe explains that staged file loading maintains metadata to help prevent reprocessing the same files during standard ingestion workflows. By contrast, internal stages do not point to external cloud storage, materialized views cannot be created directly over files in object storage, and a manual daily COPY process is operationally weaker for this scenario.
- A. Correct.
Correct. This is the standard Snowflake pattern for ingesting semi-structured files that arrive incrementally in cloud storage. An external stage points to the source location, a JSON file format tells Snowflake how to interpret the files, and Snowpipe provides continuous, automated ingestion of newly arrived files. Snowflake also maintains load metadata for staged files, which helps prevent reloading the same files during normal COPY/Snowpipe operations.
- B. Incorrect.
Incorrect. A regular internal stage is for files stored within Snowflake-managed storage, not for directly referencing files in external cloud object storage. Also, querying raw files from storage for every dashboard is not an efficient or robust retrieval pattern for analytics workloads that need governed, reusable table data.
- C. Incorrect.
Incorrect. Snowflake materialized views are created on Snowflake tables or views, not directly on files in cloud storage. To use the JSON data efficiently, the files must first be made queryable through staged file access or, more commonly for recurring analytics, loaded into a table.
- D. Incorrect.
Incorrect. While COPY INTO does keep load history and typically avoids reloading files that were already loaded successfully, the statement is too broad and the approach does not meet the requirement for minimal manual effort. A manually scheduled one-time COPY process is less suitable than Snowpipe for ongoing daily arrivals. Also, duplicate avoidance is based on Snowflake's load metadata behavior and retention windows, so saying duplicates can never be loaded 'under any circumstance' is inaccurate.