If a schema contains two tables with different data retention times, what happens when you run ALTER SCHEMA SET DATA_RETENTION_TIME_IN_DAYS = 20?

• A. Data retention time cannot be set at schema level, hence it will fail.
• B. The retention time on MY_TABLE_A does not change; MY_TABLE_B will be set to 20 days.
• C. The retention time on both the tables will be set to 20 days.
• D. The command resets to 0 days for both.

Answer: (B)

Data retention time is applied at two levels: a schema default and per-table overrides. Setting the schema’s data_retention_time_in_days defines the default for objects in that schema, but it does not override a table that already has an explicit per-table retention value.

When you set the schema to 20 days, Snowflake updates the default for future objects and for any existing tables that do not have an explicit retention. If a table already has its own retention configured, that explicit value stays in effect. In this scenario, one table with an explicit retention remains unchanged, while the other table that did not have an explicit setting adopts the new 20-day default. So the table with no explicit setting will be 20 days, and the one with an explicit setting stays as it was.