ARA-C01 Question 11
Single answerObject parametersA Snowflake architect is standardizing how transient workspaces are created for multiple analytics teams. The requirement is that any new table created in schema ANALYTICS.SANDBOX should automatically have a 1-day Time Travel retention period, while tables created in other schemas in the same database should continue using the current database-level setting of 7 days. The architect wants the solution to be enforced through Snowflake object parameter inheritance with minimal ongoing administration. Which action should the architect take?
- A
Set DATA_RETENTION_TIME_IN_DAYS = 1 on schema ANALYTICS.SANDBOX.
- B
Set DATA_RETENTION_TIME_IN_DAYS = 1 on database ANALYTICS and override other schemas to 7 days.
- C
Set DATA_RETENTION_TIME_IN_DAYS = 1 at the account level and set ANALYTICS database to 7 days.
- D
Set MIN_DATA_RETENTION_TIME_IN_DAYS = 1 on schema ANALYTICS.SANDBOX.
Show answer and explanation
Correct answer: A
Explanation
Snowflake object parameters inherit from broader scopes to narrower scopes, with the most specific setting taking precedence. For Time Travel retention, DATA_RETENTION_TIME_IN_DAYS can be set at the account, database, schema, and object levels depending on the object type. In this scenario, the cleanest design is to keep the existing 7-day retention at the database level and set the SANDBOX schema to 1 day. That way, tables created in ANALYTICS.SANDBOX inherit the schema-level value, while tables created in other schemas continue inheriting the database-level value. This aligns with Snowflake best practices for using parameter hierarchy to reduce operational overhead and target settings only where needed.
- A. Correct.
Correct. DATA_RETENTION_TIME_IN_DAYS is an object parameter that can be set at multiple levels, including schema and table, and Snowflake applies the most specific setting in the inheritance chain. By setting DATA_RETENTION_TIME_IN_DAYS = 1 on schema ANALYTICS.SANDBOX, newly created tables in that schema inherit 1 day of Time Travel retention unless explicitly overridden at the table level. Other schemas in the ANALYTICS database continue to inherit the database-level setting of 7 days, which satisfies the requirement with minimal administration.
- B. Incorrect.
Incorrect. Setting the database to 1 day and then overriding every other schema to 7 days reverses the desired inheritance model and increases administrative effort. It would require maintaining overrides on all non-sandbox schemas, which is the opposite of the stated goal of minimal ongoing administration.
- C. Incorrect.
Incorrect. Account-level settings are broader than necessary and would affect objects outside the ANALYTICS database unless they are explicitly overridden. While parameter inheritance does allow lower-level overrides, using the account level here introduces unnecessary blast radius and complexity for a requirement limited to one schema in one database.
- D. Incorrect.
Incorrect. MIN_DATA_RETENTION_TIME_IN_DAYS is not the parameter used to define standard Time Travel retention for tables in a schema. The relevant parameter for configuring object Time Travel retention is DATA_RETENTION_TIME_IN_DAYS. A common misconception is confusing a minimum-retention governance concept with the actual object-level retention setting used by tables.