Views

Question 1

How are materialized views refreshed

Answer 1

Materialized Views are automatically and transparently maintained by Snowflake when the base table is updated

Question 2

T/F Secure views cannot take advantage of the internal optimizations which require access to the underlying data in the base tables for the view.

Answer 2

True

Question 3

What objects cannot be cloned

Answer 3

External tables and Internal Stages

Question 4

What are some objects that can be cloned

Answer 4

• Permanent Table
• Transient Table
• Temp Table

Question 5

T/F Internal optimizations can indirectly expose data for a non-secure view

Answer 5

True

Question 6

T/F Non-secure views execute more slowly than Secure views

Answer 6

False - Secure is slower. Will have to weigh the need for data privacy/security to query performance

Question 7

When cloning, when is the source sequence referenced vs a cloned sequence

Answer 7

If the database or schema is cloned where the table and sequence resides, the cloned sequence will be referenced. Otherwise it is the source serquence

Question 8

If you don’t want the cloned table to be using the source sequence, what is the command

Answer 8

ALTER TABLE <table_name> ALTER COLUMN <column_name> SET DEFAULT <new_sequence>.nextval;</new_sequence></column_name></table_name>

Question 9

T/F when cloning a table with clustering keys, by default Automatic Clustering is suspended for the new table

Answer 9

True, to re-enable, use the following command
ALTER TABLE <name> RESUME RECLUSTER</name>

Question 10

T/F Internal named stages can be cloned

Answer 10

False

Question 11

T/F Individual external named stages can be cloned

Answer 11

True

Question 12

Name 4 instances when creating a materialized view is useful

Answer 12

• The view would be a small number of rows or columns compared to the base table
• The query would require significant processing, like semi structured data or calculations
• query would be on an external table
• the base table does not change frequently

Question 13

T/F The automatic maintenance of materialized views consumes credits.

Answer 13

True

Question 14

Three reasons to use a materialized view instead of a view

Answer 14

• Results of the query does not change often
• The results are used a lot
• to re-run the query takes a lot of resources

Question 15

Which is faster, materialized views or cached results

Answer 15

Cached