ADA-C01 Question 474
Single answerDetermine if there is a need to store data (CREATE DATABASE)A Snowflake administrator is onboarding a new analytics use case. A data science team needs to explore 18 months of historical clickstream data from Amazon S3 using external tables, and they do not want Snowflake to store copies of the raw files. At the same time, the finance team needs curated monthly revenue tables that must support Time Travel and Fail-safe for audit investigations. The administrator wants to minimize unnecessary Snowflake storage costs while still meeting each team's requirements. Which action should the administrator take?
- A
Create one database for both teams and load all clickstream and finance data into permanent Snowflake tables so that all users get consistent performance and retention features.
- B
Do not create a database because external tables read data outside Snowflake, and create only schemas when finance needs managed tables.
- C
Create a database for the finance team's managed tables, and for the data science team use an external stage and external tables that reference the S3 files without loading the raw clickstream data into Snowflake storage.
- D
Create transient databases for both teams because transient databases eliminate all storage charges while still providing full Fail-safe support.
Show answer and explanation
Correct answer: C
Explanation
The key administrative decision is whether Snowflake needs to store the data or whether metadata-only access is sufficient. If a workload requires Snowflake-managed tables with retention and recovery capabilities, then creating a database to hold those objects is appropriate. In this scenario, the finance team needs curated tables with Time Travel and Fail-safe, so Snowflake storage is required. By contrast, the data science team only needs to analyze raw S3 clickstream files and explicitly does not want Snowflake to store copies, so external stages and external tables are the better fit. Snowflake documentation distinguishes between data stored in Snowflake-managed tables and externally managed data accessed through stages and external tables. It also notes that transient objects do not have Fail-safe, making them unsuitable for strict audit recovery requirements.
- A. Incorrect.
Incorrect. Loading both datasets into permanent Snowflake tables would store the raw clickstream data inside Snowflake, which the scenario explicitly says the data science team does not want. Permanent tables are appropriate when Snowflake-managed storage, Time Travel, and Fail-safe are needed, but using them for externally stored clickstream data would increase storage consumption unnecessarily.
- B. Incorrect.
Incorrect. A database is still required to contain schemas and objects such as external tables in Snowflake. Even though the raw data remains in S3, external tables are Snowflake metadata objects and must exist inside a database and schema. The misconception is assuming that because the files are external, no database is needed at all.
- C. Correct.
Correct. The finance team has a clear need for Snowflake-managed storage because curated revenue tables must support audit-oriented data protection features such as Time Travel and Fail-safe, which are associated with Snowflake tables stored in databases. For the data science team, external stages and external tables allow querying data in S3 without copying the raw files into Snowflake-managed storage. This approach aligns storage choices with business requirements and avoids unnecessary CREATE DATABASE usage solely for storing raw external data.
- D. Incorrect.
Incorrect. Transient databases reduce data protection by removing Fail-safe; they do not eliminate all storage charges. Stored data in transient objects still incurs storage costs, and they are not suitable when full Fail-safe support is required for finance audit investigations. This option reflects a common misunderstanding about transient storage behavior.