SnowPro Advanced: Security Engineer Question 162
Single answerManage Time Travel settings at the table, schema, and account levelsA Snowflake security engineer is asked to reduce historical data retention for sensitive customer tables to minimize recoverable history, while keeping longer retention for a finance schema used for audit investigations. The account is on Enterprise Edition. The engineer also wants any new table created in the finance schema to inherit the longer retention automatically. Which action best meets these requirements with the least ongoing administrative effort?
- A
Set DATA_RETENTION_TIME_IN_DAYS = 1 at the ACCOUNT level, set DATA_RETENTION_TIME_IN_DAYS = 30 on the FINANCE schema, and set DATA_RETENTION_TIME_IN_DAYS = 0 on each sensitive customer table.
- B
Set DATA_RETENTION_TIME_IN_DAYS = 0 at the ACCOUNT level, set MAX_DATA_EXTENSION_TIME_IN_DAYS = 30 on the FINANCE schema, and rely on schema inheritance for all existing finance tables.
- C
Set DATA_RETENTION_TIME_IN_DAYS = 30 on the FINANCE database, set DATA_RETENTION_TIME_IN_DAYS = 1 on the ACCOUNT, and sensitive customer tables will automatically inherit 0 because they contain regulated data.
- D
Set DATA_RETENTION_TIME_IN_DAYS = 1 on the ACCOUNT level, set DATA_RETENTION_TIME_IN_DAYS = 30 on the FINANCE schema, and move sensitive customer tables into a separate schema with DATA_RETENTION_TIME_IN_DAYS = 0.
Show answer and explanation
Correct answer: D
Explanation
Snowflake Time Travel retention is controlled primarily through the DATA_RETENTION_TIME_IN_DAYS parameter. This parameter can be configured at multiple levels, including account, database, schema, and table, with lower-level object settings overriding inherited higher-level defaults. For Enterprise Edition, retention can be extended beyond the Standard Edition default limits, which makes schema-level differentiation practical for business areas such as finance. In a real-world security design, using schema boundaries to group objects with similar retention requirements reduces operational overhead and lowers the risk of inconsistent settings on newly created objects. Option 4 best applies this principle: a short account-level default, a longer FINANCE schema policy for audit use cases, and a dedicated schema with 0-day retention for sensitive customer tables. This aligns with Snowflake documentation on object parameters and inheritance for DATA_RETENTION_TIME_IN_DAYS, while avoiding confusion with MAX_DATA_EXTENSION_TIME_IN_DAYS, which is a separate parameter used to help prevent streams from becoming stale rather than to define standard Time Travel retention.
- A. Incorrect.
Incorrect. Although object-level settings can override inherited defaults, setting DATA_RETENTION_TIME_IN_DAYS = 0 on each sensitive table would work technically, but it does not meet the requirement of least ongoing administrative effort. Every new sensitive table would need to be individually configured unless additional process controls were added. Also, using a schema-level setting is the more scalable administrative pattern when multiple objects need the same retention behavior.
- B. Incorrect.
Incorrect. MAX_DATA_EXTENSION_TIME_IN_DAYS is not the parameter used to configure Time Travel retention. It is related to extending retention to prevent streams from becoming stale, not to setting the standard Time Travel period for tables or schemas. In addition, changing a schema parameter does not retroactively guarantee all existing tables will adopt that setting if they already have explicit object-level values.
- C. Incorrect.
Incorrect. Snowflake does not automatically set a table's Time Travel retention to 0 based on data sensitivity or regulatory classification. DATA_RETENTION_TIME_IN_DAYS must be explicitly set or inherited from the containing schema, database, or account. While a database-level setting can provide inheritance, this option incorrectly assumes automatic behavior tied to regulated data.
- D. Correct.
Correct. DATA_RETENTION_TIME_IN_DAYS can be set at the account, database, schema, and object levels, with more specific levels overriding broader defaults. Setting the account to 1 establishes a reduced default retention baseline. Setting the FINANCE schema to 30 ensures new tables in that schema inherit the longer retention automatically. Placing sensitive customer tables in a separate schema configured with DATA_RETENTION_TIME_IN_DAYS = 0 is the most scalable way to minimize recoverable history for that group of tables without manually altering each table.