SnowPro Advanced: Architect Practice Questions

SnowPro Advanced: Architect (ARA-C01) exam preparation

SnowPro Advanced: Architect practice questions in English: 171 questions with explanations. The mock exam has 65 questions and 115 minutes.

SnowPro Advanced: Architect free question 6 / 10 · Domain 2: Snowflake Architecture

Schema RAW contains table ORDERS, which has been in use for two days; table TMP, created one hour ago; and an auto-ingest pipe P_ORD (AUTO_INGEST = TRUE) that was re-created with CREATE OR REPLACE PIPE three hours ago. The retention period of every table is one day. The statement CREATE SCHEMA RAW_6H CLONE RAW AT(OFFSET => -63600) is run. Which outcome is consistent with the official documentation?

  1. It fails, because table TMP, which did not yet exist at the specified point, is now in the schema
  2. It fails, because the auto-ingest pipe P_ORD was re-created with CREATE OR REPLACE after the specified point in time
  3. It succeeds; TMP is skipped because it did not exist then, and P_ORD is included with the definition it had before it was re-created
  4. It succeeds; TMP is included as a table with no data, and P_ORD is included with its current definition
Show the answer and explanation

Answer: B

Why this is correct
The documentation on cloning with Time Travel lists the cases in which cloning a database or schema fails. One of them is that a pipe with AUTO_INGEST = TRUE was re-created with CREATE OR REPLACE PIPE, or dropped, after the point specified in AT | BEFORE. The restriction does not apply to pipes that are loaded manually through the REST API (AUTO_INGEST = FALSE). P_ORD is an auto-ingest pipe re-created three hours ago, which is after the point six hours ago, so the clone fails. A child object that did not exist at the specified point is simply left out of the clone; that does not cause a failure.

Why the other options are wrong
"It fails, because table TMP, which did not yet exist at the specified point, is now in the schema" … The documentation says that child objects of the cloned database or schema that did not exist at the specified point are not included in the clone. The presence of TMP, which did not exist then, is not a reason for failure.
"It succeeds; TMP is included as a table with no data, and P_ORD is included with its current definition" … Both parts are wrong. TMP did not exist at the point, so it is not included in the clone, and P_ORD is an auto-ingest pipe re-created after the point, so the clone fails altogether.
"It succeeds; TMP is skipped because it did not exist then, and P_ORD is included with the definition it had before it was re-created" … The handling of TMP is correct, but P_ORD is not. When an AUTO_INGEST = TRUE pipe has been re-created after the specified point, the clone does not take its earlier definition; the clone operation itself fails.

Key point
A Time Travel clone also fails when the specified point is older than the retention period of any current child object. Before you build a procedure that copies a schema as of a past time, check when auto-ingest pipes were re-created and the retention period of the child tables. A clone without AT | BEFORE internally uses the time the statement started as the AT value.

Go to the SnowPro Advanced: Architect practice app (10 free questions; purchase unlocks all questions)

See the full practice set