SnowPro Advanced Architect

Question

An Architect must clone the STAGING schema as it appeared one week earlier, on Tuesday, June 1st at 8:00 AM, in order to recover some objects.

The STAGING schema has 50 days of retention.

The Architect runs this statement:

CREATE SCHEMA STAGING_CLONE CLONE STAGING at (timestamp =&gt; '2021-06-01 08:00:00');

The Architect receives this error: Time travel data is not available for schema STAGING. The requested time is either beyond the allowed time travel period or before the object creation time.

The Architect then checks the schema history and sees the following:

CREATED_ON|NAME|DROPPED_ON -  
2021-06-02 23:00:00 | STAGING | NULL  
2021-05-01 10:00:00 | STAGING | 2021-06-02 23:00:00

How can the STAGING schema be cloned?

Explanation

Re-creating a dropped Snowflake schema with the same name creates a new schema version, whose history begins at its own creation time. The June 1 timestamp belongs to the prior schema version, which remains recoverable within its 50-day retention period. Since UNDROP SCHEMA fails when a schema of the same name exists, rename the current schema, restore the prior STAGING version with UNDROP, and then run the Time Travel CLONE statement against that restored version.

Learn more

SnowPro Advanced Architect

AstroTutor

Notes

Readiness

QuestionQ42

Community Discussion