Snowflake - Important one word questions and answers

Maximum length of a VARIANT data type ::: 16 MB uncompressed

-#-#-#-

What is unstructured data ::: Does not fit into any pre-defined data models,

• video files
• audio files
• documents

-#-#-#-

Snowflake share URL ::: below are the supported,

• Scoped URL 
• Temporary URL, expires in 24 hours
• e.g. SELECT BUILED_SCOPED_FILE_URL(@stage_name,'logo.png');
• File URL 
• Permanent one
• e.g. SELECT BUILD_STAGE_FILE_URL(@stage_name,'logo.png');
• Pre Signed URL 
• HTTPS URL used to access file via a web browser
• e.g. SELECT GET_PRESIGNED_URL(@stage_name,'logo.png',60);
• here, 60 denotes the seconds to expiry

-#-#-#-

Directory table ::: Stored metadata about staged files

By default it is not enabled, enabling syntax as below

e.g. CREATE OR REPLACE STAGE my_internal_stage

  FILE_FORMAT = my_json_format

  DIRECTORY = ( ENABLE = TRUE );

e.g. To query the directory table

    SELECT * FROM DIRECTORY(@my_internal_stage);

Note: At first the data will not be visible, you need to manually refresh and see the data,

ALTER STAGE my_internal_stage REFRESH;

-#-#-#-

Streams ::: Record (DML) changes made to a table

3 columns will be newly added,

1. metadata$action
2. metadata$update
3. metadata$row_id

STALE ::: 

Stream becomes stale(no longer available) when offset is outside the data retention period of the source table.

The column STALE_AFTER indicating when the stream is predicted to become stale.

TIME TRAVEL ::: Undrop fails if an object with the same name already exists.

FAIL SAFE :::

• Protection of historical data in case of disaster
• No user interaction & recoverable only by snowflake
• Non configurable 7 day period
• Period starts immediately after Time Travel period ends
• Contributes to storage cost

TIME TRAVEL & FAIL SAFE - STORAGE COST

Use below queries to get the information,

SELECT * FROM snowflake.account_usage.storage_usage;

SELECT * FROM snowflake.account_usage.table_storage_metrics;