ADA-C01 Question 208
Single answerDomain 3.0: Data and Object Management (15%)A Snowflake administrator is asked to reduce storage costs for a large fact table named SALES_FACT in a permanent schema. The table is loaded daily and queried heavily for the most recent 14 days, but data older than 90 days is rarely accessed except during occasional audits. The business also requires the ability to recover dropped or changed data for up to 7 days. Which action should the administrator take to best balance cost and recovery requirements?
- A
Convert SALES_FACT to a transient table and set DATA_RETENTION_TIME_IN_DAYS = 1 to reduce Fail-safe costs.
- B
Keep SALES_FACT as a permanent table, set DATA_RETENTION_TIME_IN_DAYS = 7, and use a lifecycle process to move data older than 90 days to a separate transient archival table.
- C
Keep SALES_FACT as a permanent table, set MAX_DATA_EXTENSION_TIME_IN_DAYS = 7, and rely on that setting for Time Travel recovery.
- D
Create a temporary table for data older than 90 days because temporary tables do not incur storage charges after sessions end.
Show answer and explanation
Correct answer: B
Explanation
The best answer is to keep the actively used SALES_FACT table as a permanent table with DATA_RETENTION_TIME_IN_DAYS set to 7, then offload cold data older than 90 days to a transient archival table. This aligns with Snowflake best practices for balancing recoverability and storage cost. Permanent tables provide both Time Travel and Fail-safe, making them appropriate for business-critical datasets. Transient tables are useful for less critical or reproducible data because they eliminate Fail-safe storage costs, but they provide less protection overall. DATA_RETENTION_TIME_IN_DAYS is the relevant parameter for Time Travel retention; MAX_DATA_EXTENSION_TIME_IN_DAYS is related to retention extension behavior, not the primary recovery window. Temporary tables are not appropriate for audit archives because they are session-bound. Relevant Snowflake documentation includes guidance on table types (permanent, transient, temporary), Time Travel, Fail-safe, and object parameters such as DATA_RETENTION_TIME_IN_DAYS and MAX_DATA_EXTENSION_TIME_IN_DAYS.
- A. Incorrect.
Incorrect. Transient tables do reduce storage costs by eliminating Fail-safe, but they are not the best fit here because the business explicitly requires the ability to recover dropped or changed data for up to 7 days on the primary fact table. While transient tables can support Time Travel with configurable retention, using a transient table for an actively queried production fact table weakens recoverability compared to a permanent table and removes Fail-safe protection entirely. This is generally not the best balance for critical production data.
- B. Correct.
Correct. A permanent table supports Time Travel and Fail-safe, which is appropriate for a critical production fact table with a stated 7-day recovery requirement. Setting DATA_RETENTION_TIME_IN_DAYS = 7 satisfies the recovery requirement for changed or dropped data through Time Travel. Moving older, rarely accessed data to a separate transient archival table is a common cost-optimization pattern because transient tables do not incur Fail-safe storage costs. This preserves stronger protection for hot production data while reducing long-term storage overhead for cold data.
- C. Incorrect.
Incorrect. MAX_DATA_EXTENSION_TIME_IN_DAYS is not the parameter used to define the table's standard Time Travel retention period. It controls how long Snowflake can extend retention in certain situations, such as preventing streams from becoming stale. It does not replace DATA_RETENTION_TIME_IN_DAYS as the mechanism for meeting a 7-day recovery requirement.
- D. Incorrect.
Incorrect. Temporary tables are session-scoped and are dropped automatically at the end of the session. They are unsuitable for archival data that must remain available for occasional audits. Although they can reduce persistence-related storage over time, they do not meet the requirement for durable, long-term access to older data.