Intermediate Snowflake test (Intermediate)

Sample questions

5 sample questions from the Intermediate Snowflake test, with the answers. Samples are taken out of the live test, so you will not meet these when you sit it.

1. Which statement is correct regarding the use of the UNDROP TABLE command in Snowflake when the DATA_RETENTION_TIME_IN_DAYS is set to 0?

   1. The table can be restored using UNDROP TABLE only if it was dropped within the DATA_RETENTION_TIME_IN_DAYS period.
   2. The table can always be restored using UNDROP TABLE regardless of the DATA_RETENTION_TIME_IN_DAYS setting.
   3. The table can be restored using the Fail-safe feature if it was dropped within the DATA_RETENTION_TIME_IN_DAYS period.
   4. The table can be restored using the Fail-safe feature even if it was dropped outside the DATA_RETENTION_TIME_IN_DAYS period.

   Answer: A. The table can be restored using UNDROP TABLE only if it was dropped within the DATA_RETENTION_TIME_IN_DAYS period.

   If a table is dropped and the DATA_RETENTION_TIME_IN_DAYS is set to 0, the table cannot be restored using UNDROP TABLE. This is a common mistake because users may not realize that setting the retention period to 0 permanently removes the table from Time Travel, making it impossible to recover using UNDROP.

2. Which statement accurately describes the fail-safe data recovery process and how it compares to Time Travel?

   1. Fail-safe and Time Travel are identical mechanisms that can be used interchangeably for data recovery, regardless of table type.
   2. Fail-safe is a recovery mechanism for all tables and can be accessed by users, while Time Travel is only available for temporary tables and cannot be queried.
   3. Fail-safe is a recovery mechanism for permanent tables only and can only be accessed by Snowflake support, while Time Travel can be used by users to query, clone, or restore data within the retention period.
   4. Fail-safe is used for querying historical data, while Time Travel is used for restoring dropped tables and is available for all table types.

   Answer: C. Fail-safe is a recovery mechanism for permanent tables only and can only be accessed by Snowflake support, while Time Travel can be used by users to query, clone, or restore data within the retention period.

   Fail-safe is an automatic backup mechanism for permanent tables that is not accessible by users and can only be accessed by Snowflake support. In contrast, Time Travel allows users to query, clone, or restore data as it was in the past within the retention period. Fail-safe is only available for permanent tables, while Time Travel applies to both permanent and transient tables. Transient and temporary tables do not have Fail-safe protection.

3. Which of the following statements is correct regarding the performance optimization of Snowpipe compared to bulk COPY in Snowflake?

   1. Snowpipe is better for ingestion of small files, while bulk COPY is better for ingestion of large files.
   2. Snowpipe is better for one-time ingestion of large volumes of data, while bulk COPY is better for continuous ingestion of files.
   3. Snowpipe is better for continuous ingestion of files, while bulk COPY is better for one-time ingestion of large volumes of data.
   4. Snowpipe and bulk COPY are equally efficient for both continuous and one-time ingestion of data.

   Answer: C. Snowpipe is better for continuous ingestion of files, while bulk COPY is better for one-time ingestion of large volumes of data.

   Snowpipe is designed for continuous, real-time ingestion of files, making it ideal for scenarios where data is constantly being added to a stage. It is billed per-second and supports auto-ingestion via cloud storage events. On the other hand, bulk COPY is more efficient for one-time ingestion of large volumes of data, as it is optimized for batch processing and does not require the same level of continuous monitoring or integration with cloud storage events. Bulk COPY is also more cost-effective for large, one-time data loads.

4. What will the following SQL query return in Snowflake: `SELECT * FROM orders AT(OFFSET => -60*5);`?

   1. The query will return an error because the OFFSET parameter is not supported in Snowflake.
   2. The query will return the current state of the 'orders' table, as the OFFSET parameter is ignored.
   3. The query will return the state of the 'orders' table at the time of the last transaction, regardless of the OFFSET parameter.
   4. The query will return the state of the 'orders' table 5 minutes ago, as it uses the OFFSET parameter with a timestamp offset.

   Answer: D. The query will return the state of the 'orders' table 5 minutes ago, as it uses the OFFSET parameter with a timestamp offset.

   The SQL query `SELECT * FROM orders AT(OFFSET => -60*5);` uses Snowflake's Time Travel feature to query the state of the 'orders' table 5 minutes ago. The OFFSET parameter specifies the number of seconds to go back in time, and in this case, it is set to -60*5, which equals -300 seconds or 5 minutes. This is a valid and correct usage of Time Travel syntax. The other options describe incorrect behaviors or misunderstandings of how Time Travel works in Snowflake.

5. What happens to a Snowflake stream's offset and its changes if a user runs a SELECT query on the stream but does not consume the changes with a DML operation that commits?

   1. The stream's offset will advance, and the changes will be removed from the stream.
   2. The stream will be marked as stale, and the changes will be permanently lost.
   3. The stream's offset will not advance, and the changes will remain in the stream until it is consumed.
   4. The stream's offset will reset to the beginning, and all changes will be reprocessed.

   Answer: C. The stream's offset will not advance, and the changes will remain in the stream until it is consumed.

   In Snowflake, querying a stream with a SELECT statement does not advance the stream's offset. This means that the changes remain in the stream until they are consumed via a DML operation that commits, such as an INSERT, UPDATE, or DELETE. If a user only runs a SELECT on the stream and does not perform a consuming operation, the offset remains at its previous position, and the changes are not removed from the stream.