ADA-C01 Question 232
Single answerImplement Snowflake table structures and typesA retail company loads clickstream data into a fact table that grows by hundreds of millions of rows per day. Analysts primarily run highly selective queries for the most recent 7 days, while compliance teams occasionally need point-in-time access to historical versions of the data for up to 30 days after changes. The company wants to minimize storage costs for transient intermediate data used during daily ETL, but the final curated fact table must support recovery and historical query requirements. Which table design should the Snowflake administrator implement?
- A
Create the curated fact table as a permanent table with an appropriate DATA_RETENTION_TIME_IN_DAYS setting, and use transient tables for intermediate ETL staging objects.
- B
Create the curated fact table as a transient table because Time Travel is available on all table types for 90 days, and use temporary tables for the final reporting layer.
- C
Create the curated fact table as a temporary table because temporary tables have the lowest storage cost and still support Fail-safe for recovery after the session ends.
- D
Create the curated fact table as an external table over files in cloud storage, because external tables provide native historical version recovery for changed rows without storing data in Snowflake.
Show answer and explanation
Correct answer: A
Explanation
The key design decision is to align Snowflake table types with durability, recovery, and cost requirements. Permanent tables are intended for production data that needs full data protection features, including Time Travel and Fail-safe. That makes them appropriate for a curated fact table with historical access and recovery requirements. Transient tables are commonly used for ETL/intermediate data when reducing storage cost is important, because they avoid Fail-safe storage charges, but that also means they provide less protection than permanent tables. Temporary tables are session-scoped and unsuitable for persistent reporting or compliance use cases. External tables are useful for querying external data files but do not replace native Snowflake tables for transactional-style historical recovery. Snowflake documentation on table types, Time Travel, and Fail-safe supports this design approach and is a core administrator best practice when implementing table structures and types.
- A. Correct.
Correct. A permanent table is the right choice for the curated fact table because permanent tables support Time Travel and Fail-safe, making them suitable when the business requires recovery and historical access after data changes. Setting DATA_RETENTION_TIME_IN_DAYS appropriately helps meet the 30-day point-in-time requirement, subject to the account edition and object configuration. Using transient tables for intermediate ETL objects is also a best practice when reducing storage costs is important, because transient tables do not have Fail-safe and are intended for data that does not need the same level of protection as curated production data.
- B. Incorrect.
Incorrect. Transient tables do support Time Travel, but they do not provide Fail-safe, so they are not the best fit for a curated production fact table with recovery requirements. Also, the statement that Time Travel is available on all table types for 90 days is inaccurate. Retention limits depend on the object type and Snowflake edition/configuration, and temporary tables are not appropriate for a final reporting layer that must persist beyond a session.
- C. Incorrect.
Incorrect. Temporary tables exist only for the duration of the session in which they are created and are automatically dropped at session end. They are not suitable for a final curated fact table used by analysts and compliance teams. In addition, temporary tables do not provide Fail-safe for recovery after the session ends, so this option misunderstands both persistence and protection characteristics.
- D. Incorrect.
Incorrect. External tables are metadata objects that allow querying data stored externally, but they do not provide Snowflake-managed row-level historical version recovery comparable to Time Travel on native tables. They are useful for querying staged files without fully loading them into Snowflake, not for implementing a curated fact table that requires point-in-time recovery of changed table data.