SnowPro Advanced: Security Engineer Question 174
Single answerApply retention settings using DDL and governance tools (for example, tagging and policies)A financial services company stores customer support records in Snowflake. The Security Engineer must enforce the following requirement: tables containing columns tagged as SENSITIVE must retain historical data for 90 days to support investigations, while non-sensitive tables should keep the default lower retention period to control storage costs. The solution must be applied consistently across newly created and existing tables with minimal manual effort. Which approach best meets this requirement?
- A
Create a tag named DATA_CLASSIFICATION, assign the value SENSITIVE to relevant columns, and use a tag-based masking policy to set DATA_RETENTION_TIME_IN_DAYS = 90 for any table containing a tagged column.
- B
Set DATA_RETENTION_TIME_IN_DAYS = 90 at the database level, then rely on object inheritance so only tables with SENSITIVE-tagged columns keep 90 days of retention while other tables use a lower default.
- C
Identify tables that contain columns tagged as SENSITIVE by querying tag references, then use ALTER TABLE ... SET DATA_RETENTION_TIME_IN_DAYS = 90 on those tables and automate the process for new or changed objects with governance metadata checks.
- D
Apply a row access policy to tables with SENSITIVE-tagged columns and configure the policy body to preserve deleted and updated rows for 90 days.
- E
Use a projection policy on SENSITIVE-tagged columns so Snowflake automatically extends Time Travel retention for the parent table to 90 days.
Show answer and explanation
Correct answer: C
Explanation
The key distinction is that retention in Snowflake is enforced through object parameters such as DATA_RETENTION_TIME_IN_DAYS, typically set with DDL like ALTER TABLE, ALTER SCHEMA, or ALTER DATABASE. Tags and policies are governance tools that help classify and control access to data, but they do not directly set retention periods on objects. Therefore, the best-practice approach is to use tags to identify sensitive data and then automate DDL changes based on that metadata. In practice, teams often query Snowflake metadata views/functions related to tags and object references, determine which tables contain sensitive columns, and apply ALTER TABLE ... SET DATA_RETENTION_TIME_IN_DAYS = 90 to those specific tables. This satisfies the requirement for selective retention, supports both existing and newly created objects when automated, and avoids unnecessarily increasing retention on non-sensitive tables. Relevant Snowflake documentation areas include Time Travel and data retention parameters, object parameter inheritance behavior, tags and tag references, and governance policy capabilities such as masking, row access, and projection policies.
- A. Incorrect.
Incorrect. Tag-based masking policies can dynamically control how data is presented, but they do not set object parameters such as DATA_RETENTION_TIME_IN_DAYS. Snowflake tags are governance metadata, and while they are useful for classification and automation, they do not directly change Time Travel retention through a masking policy.
- B. Incorrect.
Incorrect. Setting DATA_RETENTION_TIME_IN_DAYS at the database level affects objects in that scope by default, but Snowflake does not selectively vary inherited retention based on whether a table contains tagged columns. Inheritance is based on object hierarchy, not tag values on child columns. This approach would over-retain non-sensitive tables and increase storage cost.
- C. Correct.
Correct. DATA_RETENTION_TIME_IN_DAYS is configured through DDL at the table, schema, or database level. Because the requirement is conditional based on governance metadata, the practical pattern is to use tags to classify sensitivity, query Snowflake metadata such as tag references to identify affected tables, and then apply ALTER TABLE statements to set retention only where required. Automating this workflow provides consistent coverage for existing and new objects while keeping non-sensitive tables at lower retention.
- D. Incorrect.
Incorrect. Row access policies restrict which rows a role can see at query time; they do not control Time Travel or Fail-safe retention. A common misconception is to treat governance policies as general lifecycle controls, but retention settings are managed separately through object parameters.
- E. Incorrect.
Incorrect. Projection policies govern how columns can be projected in queries for privacy and governance use cases. They do not modify Time Travel retention for a table. This option confuses data access governance controls with storage/history retention settings.