What will be the impact of running the following command?

Let’s say you have a schema name MY_SCHEMA. This schema contains two permanent tables as shown below

CREATE TABLE MY_TABLE_A (C1 INT)

DATA_RETENTION_TIME_IN_DAYS = 10;

CREATE TABLE MY_TABLE_B (C1 INT);

What will be the impact of running the following command?

ALTER SCHEMA MY_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 retention time will not change for both tables

View Answer

Answer: B

Explanation

https://docs.snowflake.com/en/user-guide/data-time-travel.html#changing-the-data-retention-period-for-an-object

Changing the retention period for your account or individual objects changes the value for all lower-level objects that do not have a retention period explicitly set. For example:

If you change the retention period at the account level, all databases, schemas, and tables that do not have an explicit retention period automatically inherit the new retention period.

If you change the retention period at the schema level, all tables in the schema that do not have an explicit retention period inherit the new retention period.

Keep this in mind when changing the retention period for your account or any objects in your account because the change might have Time Travel consequences that you did not anticipate or intend. In particular, we do not recommend changing the retention period to 0 at the account level.