ADA-C01 Question 233
Single answerImplement Snowflake table structures and typesA retail company loads clickstream events into a large fact table in Snowflake. Analysts primarily query the most recent 7 days of data, while compliance requires time travel access only for the latest 24 hours. Data older than 90 days must remain queryable, but it changes extremely rarely and should incur the lowest possible storage cost without redesigning downstream SQL that references the table name. The administrator wants to optimize storage and query performance using Snowflake table structures and types.
Which approach should the administrator choose?
- A
Create the table as a temporary table so that inactive micro-partitions are removed automatically and storage costs are minimized.
- B
Convert the table to a transient table and rely on a longer Fail-safe period for older data while keeping the same retention settings.
- C
Use a permanent table for the active dataset, set DATA_RETENTION_TIME_IN_DAYS to 1, and enable automatic clustering on the event timestamp column.
- D
Replace the table with an external table over files in cloud storage so historical data remains queryable without Snowflake storage charges and existing SQL continues to work unchanged.
Show answer and explanation
Correct answer: C
Explanation
The best answer is to keep the dataset as a permanent table, reduce Time Travel retention to the minimum required 1 day, and optimize query access patterns with clustering on the commonly filtered timestamp column. In Snowflake, permanent tables include Time Travel and Fail-safe, making them appropriate for durable production datasets. DATA_RETENTION_TIME_IN_DAYS can be tuned to match business and compliance requirements, which helps limit storage consumed by historical table versions. For very large tables with predictable filter predicates, clustering can improve micro-partition pruning and reduce scan costs.
The other choices misuse table types. Temporary tables are session-bound and unsuitable for persistent shared fact data. Transient tables reduce storage cost by removing Fail-safe, but the option incorrectly states they provide a longer Fail-safe period. External tables are useful for querying data in external stages, but they are not equivalent to internal tables for this use case and typically are not the simplest way to preserve downstream behavior and optimize hot-query performance.
These conclusions align with Snowflake documentation on table types (temporary, transient, permanent), Time Travel and Fail-safe retention behavior, and clustering best practices for large tables with selective filter patterns.
- A. Incorrect.
Incorrect. Temporary tables exist only for the session that created them and are not appropriate for shared analytical fact data that must remain queryable for 90+ days. They also would not satisfy compliance or multi-user access requirements. A common misconception is that temporary tables are a general storage-cost optimization tool for production data, but they are intended for short-lived session-scoped data.
- B. Incorrect.
Incorrect. Transient tables reduce storage costs by eliminating Fail-safe, not by providing a longer Fail-safe period. This option is factually wrong because transient tables have Time Travel but no Fail-safe. In addition, converting a compliance-sensitive shared fact table to transient may conflict with recovery requirements. Candidates may choose this because transient tables are often associated with lower storage cost, but the reasoning here is inaccurate.
- C. Correct.
Correct. A permanent table supports durable shared access and required recovery semantics. Setting DATA_RETENTION_TIME_IN_DAYS to 1 aligns Time Travel with the stated 24-hour compliance requirement, reducing storage overhead from historical versions. Enabling automatic clustering on the event timestamp can improve pruning and query performance when analysts frequently filter on recent dates, especially for a large continuously loaded fact table. This approach preserves the table name and downstream SQL while optimizing both retention and performance.
- D. Incorrect.
Incorrect. External tables can expose data in cloud storage, but they are metadata objects over externally managed files and are not drop-in replacements for internal Snowflake tables in this scenario. Existing downstream SQL may require changes due to differences in capabilities and performance characteristics. External tables also do not provide the same behavior for frequently queried recent data and are generally not the best fit when the requirement is to keep the same table interface while optimizing an internal analytical table.