Views Flashcards

1
Q

How are materialized views refreshed

A

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

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
2
Q

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.

A

True

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
3
Q

What objects cannot be cloned

A

External tables and Internal Stages

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
4
Q

What are some objects that can be cloned

A
  • Permanent Table
  • Transient Table
  • Temp Table
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
5
Q

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

A

True

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
6
Q

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

A

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

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
7
Q

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

A

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

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
8
Q

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

A

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

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
9
Q

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

A

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

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
10
Q

T/F Internal named stages can be cloned

A

False

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
11
Q

T/F Individual external named stages can be cloned

A

True

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
12
Q

Name 4 instances when creating a materialized view is useful

A
  • 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
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
13
Q

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

A

True

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
14
Q

Three reasons to use a materialized view instead of a view

A
  • Results of the query does not change often
  • The results are used a lot
  • to re-run the query takes a lot of resources
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
15
Q

Which is faster, materialized views or cached results

A

Cached

How well did you know this?
1
Not at all
2
3
4
5
Perfectly