2025 Tables and Views Flashcards by Christine Carroll

What are the four types of tables in Snowflake

Permanent
Transient
Temporary
External

How well did you know this?

Not at all

Perfectly

How long is the Fail Safe period

7 days immediately after the Time Travel retention period.

How well did you know this?

Not at all

Perfectly

What is the max number of days Time Travel can be set for

90 days

How well did you know this?

Not at all

Perfectly

In the Snowflake standard addition, what is the Time Travel period allowed to be set for?

0 - 1 day

How well did you know this?

Not at all

Perfectly

What is the minimum edition to set Time Travel to more than 1 day?

Enterprise

How well did you know this?

Not at all

Perfectly

What is the parameter to set Time Travel?

DATA_RETENTION_TIME_IN_DAYS

How well did you know this?

Not at all

Perfectly

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

How well did you know this?

Not at all

Perfectly

Can a transient table be converted to another type of table after being created

How well did you know this?

Not at all

Perfectly

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

How well did you know this?

Not at all

Perfectly

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 well did you know this?

Not at all

Perfectly

How long is the Time Travel retention period on a Temporary Table?

1 day or the reminder of the session, whichever is smaller

How well did you know this?

Not at all

Perfectly

Once created, can a temporary table be changed?

How well did you know this?

Not at all

Perfectly

For an External Table, what is stored in Snowflake?

The metadata, like the name of the file, the row number of each record, etc

How well did you know this?

Not at all

Perfectly

Can you perform DML operations on an External Table?

How well did you know this?

Not at all

Perfectly

What can be done to improve performance when querying an external table?

You can build a Materialized View on top of the table

How well did you know this?

Not at all

Perfectly

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

How well did you know this?

Not at all

Perfectly

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

How well did you know this?

Not at all

Perfectly

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

How well did you know this?

Not at all

Perfectly

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.

How well did you know this?

Not at all

Perfectly

What happens when a temporary table and a non-temporary table have the same name?

Both are available but the temporary one takes precedence.

How well did you know this?

Not at all

Perfectly

Is CREATE TABLE required to create Temporary Tables

How well did you know this?

Not at all

Perfectly

What is the minimum edition required for 0-1 days of time travel on Temporary or Transient Tables

Enterprise

How well did you know this?

Not at all

Perfectly

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.

How well did you know this?

Not at all

Perfectly

What is the default for MAX_DATA_EXTENSION_TIME_IN_DAYS

14 days, can be set 0 to 90

How well did you know this?

Not at all

Perfectly

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 do in a SELECT statement

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()

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

What cannot be queried in a materialized view?

Any other view/user-defined table functions ## Footnote 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 ## Footnote 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 ## Footnote This limitation affects the ability to perform certain calculations or data modifications directly within the view.

Can users truncate materialized views?

No ## Footnote 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 ## Footnote This ensures that materialized views are preserved during schema cloning.

Is Time Travel allowed on materialized views?

No ## Footnote 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 SET SECURE;

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.

Will external tables refresh automatically

No, you need an sns topic hooked up the bucket