ADA-C01 Question 558
Single answerEstablish data retention periodsA financial services company stores regulated transaction data in a permanent Snowflake table named PROD_DB.FINANCE.TRADES. The compliance team requires the ability to recover deleted or updated rows for up to 30 days. At the same time, the Snowflake administrator wants to avoid unnecessarily increasing retention on transient staging tables used by ETL jobs. Which action should the administrator take to meet these requirements with the least impact on other objects?
- A
Set DATA_RETENTION_TIME_IN_DAYS = 30 on the PROD_DB.FINANCE schema so all current and future tables in the schema inherit the requirement.
- B
Set MAX_DATA_EXTENSION_TIME_IN_DAYS = 30 on the PROD_DB.FINANCE.TRADES table to ensure 30 days of row recovery.
- C
Set DATA_RETENTION_TIME_IN_DAYS = 30 directly on the PROD_DB.FINANCE.TRADES table.
- D
Convert the transient staging tables to temporary tables, then set DATA_RETENTION_TIME_IN_DAYS = 30 at the database level.
Show answer and explanation
Correct answer: C
Explanation
The correct answer is to set DATA_RETENTION_TIME_IN_DAYS = 30 directly on the specific permanent table that has the regulatory requirement. In Snowflake, Time Travel retention is controlled by the DATA_RETENTION_TIME_IN_DAYS parameter, which can be set at the account, database, schema, or table level. When the goal is to meet a retention requirement for one table without increasing retention for other objects, the most targeted and operationally sound approach is to configure the table itself. This aligns with Snowflake best practices of applying the narrowest effective scope for object parameters.
It is also important to distinguish DATA_RETENTION_TIME_IN_DAYS from MAX_DATA_EXTENSION_TIME_IN_DAYS. The latter is associated with Snowflake's ability to extend retention in support of features such as streams, and it does not replace the configured Time Travel retention period for standard data recovery. Additionally, transient and temporary tables have different retention characteristics and are not the right mechanism to solve the requirement described. Refer to Snowflake documentation on Time Travel and object parameters, especially DATA_RETENTION_TIME_IN_DAYS and MAX_DATA_EXTENSION_TIME_IN_DAYS, for the exact behavior and supported scopes.
- A. Incorrect.
Incorrect. Setting DATA_RETENTION_TIME_IN_DAYS at the schema level would affect objects in that schema by default, including other tables that may not need extended Time Travel. The scenario explicitly asks to satisfy the requirement with the least impact on other objects, so changing only the regulated table is the better choice. Schema-level settings are useful for broad policy application, but they are not the most targeted solution here.
- B. Incorrect.
Incorrect. MAX_DATA_EXTENSION_TIME_IN_DAYS is not the parameter used to define standard Time Travel retention for recovering deleted or updated rows. It is related to how Snowflake can extend retention in certain situations, such as protecting streams from staleness, not to establish the base retention period required for row recovery. A candidate might choose this option by confusing stream-related retention behavior with table Time Travel configuration.
- C. Correct.
Correct. DATA_RETENTION_TIME_IN_DAYS controls the Time Travel retention period for a table. Setting it directly on PROD_DB.FINANCE.TRADES to 30 days meets the compliance requirement for that specific regulated table while avoiding unnecessary retention changes on transient staging tables or other objects. This is the most precise administrative action for the stated requirement.
- D. Incorrect.
Incorrect. Temporary tables are session-scoped and not appropriate as a general solution for persistent staging design changes in order to manage retention requirements on an unrelated regulated table. In addition, setting DATA_RETENTION_TIME_IN_DAYS = 30 at the database level would broadly affect objects in the database rather than minimizing impact. This option mixes an unnecessary object redesign with an overly broad retention change.