Views Flashcards
How are materialized views refreshed
Materialized Views are automatically and transparently maintained by Snowflake when the base table is updated
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.
True
What objects cannot be cloned
External tables and Internal Stages
What are some objects that can be cloned
- Permanent Table
- Transient Table
- Temp Table
T/F Internal optimizations can indirectly expose data for a non-secure view
True
T/F Non-secure views execute more slowly than Secure views
False - Secure is slower. Will have to weigh the need for data privacy/security to query performance
When cloning, when is the source sequence referenced vs a cloned sequence
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
If you don’t want the cloned table to be using the source sequence, what is the command
ALTER TABLE <table_name> ALTER COLUMN <column_name> SET DEFAULT <new_sequence>.nextval;</new_sequence></column_name></table_name>
T/F when cloning a table with clustering keys, by default Automatic Clustering is suspended for the new table
True, to re-enable, use the following command
ALTER TABLE <name> RESUME RECLUSTER</name>
T/F Internal named stages can be cloned
False
T/F Individual external named stages can be cloned
True
Name 4 instances when creating a materialized view is useful
- 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
T/F The automatic maintenance of materialized views consumes credits.
True
Three reasons to use a materialized view instead of a view
- Results of the query does not change often
- The results are used a lot
- to re-run the query takes a lot of resources
Which is faster, materialized views or cached results
Cached
