ADA-C01 Question 209
Single answerDomain 3.0: Data and Object Management (15%)A Snowflake administrator needs to retire a reporting table named SALES_MONTHLY in the PROD.REPORTING schema. Business users have many existing queries and dashboards that reference the table by its current fully qualified name, but the data engineering team wants to replace the table with a newly modeled version built from a different pipeline. The administrator must minimize downtime, preserve object references for consumers, and keep the old version recoverable for a short validation period in case rollback is needed. Which approach best meets these requirements?
- A
Use ALTER TABLE PROD.REPORTING.SALES_MONTHLY RENAME TO SALES_MONTHLY_OLD, then rename the new replacement table to SALES_MONTHLY.
- B
Drop PROD.REPORTING.SALES_MONTHLY and immediately undrop the new replacement table with the original name after loading completes.
- C
Clone PROD.REPORTING.SALES_MONTHLY to SALES_MONTHLY_BACKUP, drop the original table, and recreate it from the new pipeline output using CREATE OR REPLACE TABLE.
- D
Create a view named PROD.REPORTING.SALES_MONTHLY over the new table, and keep the old table with the same name until consumers finish validation.
Show answer and explanation
Correct answer: A
Explanation
The best answer is to use a rename-based cutover: rename the existing table to a backup name, then rename the replacement table into the original name. In Snowflake, object renames are metadata operations and are commonly used to minimize downtime during cutovers. This approach preserves the fully qualified object name expected by downstream consumers while keeping the previous version available under a different name for validation or rollback.
This is generally preferable to dropping and recreating the object because dropping introduces avoidable risk and service interruption. UNDROP is intended to restore a dropped object during its Time Travel retention period, not to swap identities between separate objects. Similarly, while zero-copy cloning is useful for backup or testing, it does not by itself solve the requirement to preserve the original production object name with minimal interruption.
Relevant Snowflake documentation and best practices include object renaming with ALTER ... RENAME TO, Time Travel and UNDROP behavior, and zero-copy cloning concepts. Administrators should also validate dependent objects and privileges during cutover planning, especially if the replacement object was created separately and must match required grants and structure.
- A. Correct.
Correct. Renaming the current table to a temporary backup name and then renaming the new table to the original object name is a practical swap pattern when you need to preserve the object name used by downstream queries. This minimizes downtime because metadata renames are fast, and the original table remains recoverable during validation by retaining it under the backup name. Existing consumers continue to reference PROD.REPORTING.SALES_MONTHLY once the replacement is renamed into place.
- B. Incorrect.
Incorrect. UNDROP restores a dropped object to its original state and original name within Time Travel retention, but it does not apply to making a different replacement table become the old table's name. Also, dropping the production table first introduces unnecessary risk and avoidable downtime. This option reflects a misconception that UNDROP can be used like a name-swap mechanism between distinct objects.
- C. Incorrect.
Incorrect. Cloning the original provides a backup, but dropping and recreating the production table is more disruptive than a rename-based swap. CREATE OR REPLACE TABLE creates a new object and can break assumptions around object continuity; more importantly, this approach introduces a period where the original object name may not exist or may be recreated only after pipeline completion. It does not preserve availability as well as a coordinated rename sequence.
- D. Incorrect.
Incorrect. A table and a view cannot coexist with the same name in the same schema, so keeping the old table with the same name while creating a view of that same name is not possible. This distractor targets a common misunderstanding about using views as transparent replacements without accounting for object naming constraints.