SnowPro Advanced: Security Engineer Question 163
Single answerManage Time Travel settings at the table, schema, and account levelsA security engineer is reviewing data retention settings after an internal audit. The Snowflake account has DATA_RETENTION_TIME_IN_DAYS set to 1. In the FINANCE schema, DATA_RETENTION_TIME_IN_DAYS is set to 7. A table named PAYROLL in the FINANCE schema was created without explicitly setting retention. The audit requires reducing the PAYROLL table's Time Travel retention to 2 days immediately, without changing retention for any other existing or future objects in the schema. Which action should the engineer take?
- A
Run ALTER ACCOUNT SET DATA_RETENTION_TIME_IN_DAYS = 2;
- B
Run ALTER SCHEMA FINANCE SET DATA_RETENTION_TIME_IN_DAYS = 2;
- C
Run ALTER TABLE FINANCE.PAYROLL SET DATA_RETENTION_TIME_IN_DAYS = 2;
- D
Recreate the PAYROLL table with a new retention setting because inherited Time Travel values cannot be overridden after creation.
Show answer and explanation
Correct answer: C
Explanation
Snowflake supports setting DATA_RETENTION_TIME_IN_DAYS at multiple levels, including account, schema, and table. The effective value for a table follows object parameter precedence: a table-level setting overrides a schema-level setting, which overrides an account-level setting. In this scenario, PAYROLL currently inherits 7 days from the FINANCE schema because no explicit table-level retention was set. To change only PAYROLL, the engineer should set the parameter directly on that table. This is the least disruptive and most precise approach. As a best practice, use the narrowest scope necessary when adjusting retention-related settings, especially in security and governance scenarios, to avoid unintended impact on other datasets. Refer to Snowflake documentation for object parameters and Time Travel retention, specifically DATA_RETENTION_TIME_IN_DAYS and parameter inheritance rules.
- A. Incorrect.
Incorrect. Changing the account-level DATA_RETENTION_TIME_IN_DAYS would affect the account default used by objects that do not have a more specific setting, which is broader than required. It would not be the right choice when the requirement is to change only one existing table and leave all other existing and future objects in the schema unchanged.
- B. Incorrect.
Incorrect. Changing the schema-level DATA_RETENTION_TIME_IN_DAYS would affect the FINANCE schema default and can impact other objects in that schema, including future objects and potentially existing objects that rely on the schema setting. This violates the requirement to change only the PAYROLL table.
- C. Correct.
Correct. ALTER TABLE ... SET DATA_RETENTION_TIME_IN_DAYS = 2 applies the retention setting directly at the table level, which is the most specific level among account, schema, and table. This meets the requirement to reduce Time Travel retention only for the PAYROLL table immediately, without altering retention behavior for other objects.
- D. Incorrect.
Incorrect. Snowflake allows DATA_RETENTION_TIME_IN_DAYS to be modified on existing supported objects using ALTER statements. Recreating the table is unnecessary and would introduce avoidable operational risk, such as migration complexity and possible disruption.