ADA-C01 Question 472
Single answerCreate objectsA Snowflake administrator needs to provision a new analytics area for a finance team. The requirement is to create a database and a schema where all future tables in that schema must automatically use a 14-day Time Travel retention period whenever possible, without requiring developers to specify the setting on each CREATE TABLE statement. The finance team will create standard permanent tables in the schema. Which action should the administrator take to meet this requirement with the least ongoing administrative effort?
- A
Create the database with DATA_RETENTION_TIME_IN_DAYS = 14 and rely on all schemas and tables created inside it to inherit that value automatically unless explicitly overridden.
- B
Create the schema with MAX_DATA_EXTENSION_TIME_IN_DAYS = 14 so that all future tables in the schema use a 14-day Time Travel retention period.
- C
Alter each table after creation to set DATA_RETENTION_TIME_IN_DAYS = 14, because Time Travel retention cannot be inherited from a parent object.
- D
Create the schema as a transient schema and set DATA_RETENTION_TIME_IN_DAYS = 14 so that all future tables inherit the setting and storage cost is minimized.
Show answer and explanation
Correct answer: A
Explanation
Snowflake supports object parameter inheritance for settings such as DATA_RETENTION_TIME_IN_DAYS. For permanent objects, administrators can set this parameter at the account, database, schema, or table level, with the more specific object overriding inherited values. To ensure future permanent tables in a finance schema automatically receive a 14-day Time Travel retention period without per-table administration, setting the parameter on a parent object is the best practice. Among the options provided, setting it on the database is valid and ensures inheritance for objects created beneath it unless overridden. MAX_DATA_EXTENSION_TIME_IN_DAYS is a different parameter and does not configure standard Time Travel retention. Transient objects are also inappropriate here because they are intended for lower-cost storage with reduced data protection characteristics compared with permanent tables. This aligns with Snowflake documentation on parameter hierarchy and Time Travel behavior for permanent versus transient objects.
- A. Correct.
Correct. DATA_RETENTION_TIME_IN_DAYS can be set at the database, schema, or table level. Setting it at the database level allows child objects such as schemas and permanent tables to inherit the retention period by default unless a more specific value is set lower in the hierarchy. This meets the requirement with minimal administrative effort because developers do not need to specify the parameter on each CREATE TABLE statement.
- B. Incorrect.
Incorrect. MAX_DATA_EXTENSION_TIME_IN_DAYS is not the parameter that controls standard Time Travel retention. It is related to extending retention for streams to prevent staleness in certain cases, not to defining the default Time Travel period for tables. A candidate might choose this option by confusing retention-related object parameters.
- C. Incorrect.
Incorrect. Table-level configuration would work technically, but it does not meet the requirement of least ongoing administrative effort. Snowflake supports parameter inheritance, so setting DATA_RETENTION_TIME_IN_DAYS at a higher level such as the database or schema is the preferred approach when a consistent default is required.
- D. Incorrect.
Incorrect. Transient schemas are designed for objects with reduced data protection and do not support the same Time Travel behavior as permanent objects. In practice, transient objects have limited Time Travel retention and are not appropriate when the requirement is for standard permanent tables to use a 14-day retention period. This option mixes cost optimization with an object type that conflicts with the stated requirement.