2025 Tables and Views Flashcards
What are the four types of tables in Snowflake
Permanent
Transient
Temporary
External
How long is the Fail Safe period
7 days immediately after the Time Travel retention period.
What is the max number of days Time Travel can be set for
90 days
In the Snowflake standard addition, what is the Time Travel period allowed to be set for?
0 - 1 day
What is the minimum edition to set Time Travel to more than 1 day?
Enterprise
What is the parameter to set Time Travel?
DATA_RETENTION_TIME_IN_DAYS
What is the difference between a Transient Table and a Permanent Table
Transient Tables have no Fail-Safe and limit time travel, max 1 day which is the default
Can a transient table be converted to another type of table after being created
No
When would you use a Transient Table
- When you have a lot of data but protection is not a concern.
- When data can be recovered from external means if required.
- Can be used to store data that should be maintained between sessions, but can be recovered in case of a system failure
What is the difference between Temporary Tables and other tables
These tables are only for the duration of the session and are only visible to the user. Once the session completes, the table is purce and nonrecoverable.
How long is the Time Travel retention period on a Temporary Table?
1 day or the reminder of the session, whichever is smaller
Once created, can a temporary table be changed?
No
For an External Table, what is stored in Snowflake?
The metadata, like the name of the file, the row number of each record, etc
Can you perform DML operations on an External Table?
No
What can be done to improve performance when querying an external table?
You can build a Materialized View on top of the table
What columns are included in external tables?
VALUE: the VARIANT field representing a single record in the file
METADATA$FILENAME: name and path of each staged file
METADATA$FILE_ROW_NUMBER: the row number of each staged file record
What does the command ALTER EXTERNAL TABLE … REFRESH do?
Once completed, the following activities are supported:
* Adds new files to table metadata
* Updates file changes in table metadata
* Removes files no longer available from table metadata
What is an option for triggering automatic refreshes of an External Table’s metadata?
Event Notification for Cloud Storage.
There is a charge of .06 per 1,000 event notifications
Can the types permanent/transient/temporary be applied to other database objects?
Yes, like Schemas and Databases. Everything created in those objects will inherit the type.
What happens when a temporary table and a non-temporary table have the same name?
Both are available but the temporary one takes precedence.
Is CREATE TABLE required to create Temporary Tables
No
What is the minimum edition required for 0-1 days of time travel on Temporary or Transient Tables
Enterprise
What is MAX_DATA_EXTENSION_TIME_IN_DAYS
Max number of days Snowflake can extended the data retention period for tables to prevent streams on the tables from becoming stale.
What is the default for MAX_DATA_EXTENSION_TIME_IN_DAYS
14 days, can be set 0 to 90
On a CREATE TABLE statement, what does this mean:
ERROR_ON_COLUMN_COUNT_MISMATCH = TRUE
Of the number of delimited columns in a n input file does not match the number of columns in the Snowflake table, it generates a parsing error
What is NULL_IF used for
Used to convert to and from SQL NULL.
This option can be used for string conversion from SQL NULL to the first value in the list
On a CREATE TABLE statement, what does this mean:
STRIP_OUTER_ARRAY = TRUE
Allows the JSON parser to remove the outer array and load records in separate rows. Used often with VARIANT data types when the data exceeds 16 MB
What are some examples of reserved keywords that should not be used as column identifiers
CURRENT_DATE
CURRENT_ROLE
CURRENT_TIME
How do you disable Time Travel for a table
Set DATA_RETENTION_TIME_IN_DAYS=0
What does the Top<n> do in a SELECT statement</n>
Returns the top n rows, remember to use ORDER BY
How do you convert from JSON NULL to SQL NULL on a SELECT statement
STRIP_NULL_VALUE(<col>)
Where can STATEMENT_TIMEOUT_IN_SECONDS be set
At the Session or the Warehouse level, the lowest takes precedence
What is the default for STATEMENT_TIMEOUT_IN_SECONDS
48 hours
What is the purpose of RESULT_SCAN
Returns the previous command as a table. It should be executed within 24 hours of query execution.
What is the syntax of RESULT_SCAN
RESULT_SCAN(‘query_id’|last_query_id())
What are the different data types for date/time information
DATE
DATETIME
TIME
TIMESTAMP
What are the three variations of TIMESTAMP
TIMESTAMP_LTZ
TIMESTAMP_NTZ
TIMESTAMP_TZ
Define TIMESTAMP_LTZ
Stores UTC time with a defined precision. All operations are performed in the current session’s time zone.
Define TIMESTAMP_NTZ
Stores wall clock time with specified precision. All operations are performed without taking any time zone
TIMESTAMP_TZ
Stores UTC time with a time zone offset. All operations are performed with a record specific time zone offset
What are the three type of VIEWS
Regular (nonmaterialized)
Materialized
Secure
Why are Views slower than Materialized Views
Regular view results are not stored for future use. Associated SQL queries should be executed again every time a view is accessed
What costs are associated with Materialized Views
Storage and Compute
Does Snowflake automatically maintain Materialized Views
Yes, because of this, Materialized Views always have access to the latest table data
How can you tell when a Materialized View is refreshed
The view has the columns REFRESHED_ON and BEHIND_BY
Where can you find Materialized Views in the Information_Schema
Tables
NOT VIEWS
T/F Materialized Views are more flexible than cached results and faster than tables because they use the “cache”
True
Can Materialized Views also support data to be hidden at the row and column levels
Yes
If the base table data changes before a query on the materialized view is updated, does the user experience an impact?
No, Snowflaker will either first update the materialized view or use updated portions of the materialized view and retrieve any new data from the base table.
What are some valid use cases for a materialized view
- Smaller subset of the base table
- The SQL requires significant processing
- Based on an external table
- Base table does not change frequently
If a user has access to a view or materialized view, do they also need access to the base table(s) to see results
No
What cannot be queried in a materialized view?
Any other view/user-defined table functions
Materialized views are limited to their own data and cannot reference other views or user-defined functions.
Which SQL clauses are not allowed in a materialized view?
UDFs, HAVING, JOIN, ORDER BY, LIMIT, GROUP BY
These restrictions limit the complexity of the queries that can be used to define materialized views.
What operations are generally not allowed in a materialized view?
Many aggregate functions or DML operations
This limitation affects the ability to perform certain calculations or data modifications directly within the view.
Can users truncate materialized views?
No
Truncating a materialized view is not permitted, which affects data management strategies.
What happens when a schema containing a materialized view is cloned?
The materialized view will be cloned and included in the new schema or database
This ensures that materialized views are preserved during schema cloning.
Is Time Travel allowed on materialized views?
No
Time Travel functionality does not apply to materialized views, limiting historical data access.
What happens if the base columns of a Materialized view are dropped?
All materialized views on the bas table are suspended and users must re-create the view again.
When would you use a materialized view instead of a regular view
The SQL is computationally expensive and is repeatedly executed on relatively stable data.
When would you use a view instead of a materialized view
Views are more flexible and are best when the results change often, the view isn’t used often, or the query is not intensive.
Why might you experience an impact on performance for Secure Views
While evaluating them, the query optimizer bypasses a few optimizations.
What command can be used to identify secure views?
SHOW MATERIALIZED VIEWS
What makes a secure view different
Designed for data privacy and limit access to the data definition of the view
How do you change a view to a secure view
ALTER VIEW <view-name> SET SECURE;</view-name>
If a user only has access the secure view and not the underlying table, where are they limited
They can’t see the SQL definition, this includes in the INFORMATION_SCHEMA and the SHOW commands
When would you use FLATTEN
When you need to access elements with JSON arrays and convert them to rows
When would you use PARSE_JSON
When you have JSON stored as strings that need to be converted to VARIANT
When you want to convert hierarchical data into a relational format, which would you use FLATTEN or PARSE JSON
FLATTEN
How can you organize your underlying data for external tables if you want to partition it
Using logical paths that include date, time, country or similar dimensions in the poath
How can you add partitions to your external table
Automatically by refereshing the external table
Manually
When are partition columns defined for an external table
When created using PATITION BY. After creations, the method by which partitions are added cannot be changed
When a SNS topic that is associated with a pipe is deleted, does the pipe autosuspend
No, it will attempt to ingest data and fail.
What edition is required for materialize views
Enterprise
Which table only exists within the user’s session, Transient or Temporary
Temporary
All external tables include what columns
VALUE
METADATA$FILENAME
METADATA$FILE_ROW_NUMBER
What type of tables can be cloned
Transient
Permanent
What is the difference between JSON NULL and SQL NULL
In JSON a null value represents a string null, which prevents the extraction of those elements.
SQL null is an empty value
JSON NULL can lead to performance issues.
