COPY - Parameters

CopyOption	Description	Values
ON_ERROR	Specifies the error handling for the load operation	CONTINUE | SKIP_FILE | SKIP_FILE_num | 'SKIP_FILE_num%' | ABORT_STATEMENT
SIZE_LIMIT	Specifies the maximum size (in bytes) of data to be loaded	<num>
PURGE	Remove files after successful load	TRUE | FALSE
RETURN_FAILED_ONLY	Return only files that have failed to load	TRUE | FALSE
MATCH_BY_COLUMN_NAME	Load semi-structured data into columns in matching the columns names	CASE_SENSITIVE | CASE_INSENSITIVE | NONE
ENFORCE_LENGTH	Truncate text strings that exceed the target column length	TRUE | FALSE
TRUNCATECOLUMNS	Truncate text strings that exceed the target column length	TRUE | FALSE
FORCE	Load files even if loaded before	TRUE | FALSE
LOAD_UNCERTAIN_FILES	Load files even if load status unknown	TRUE | FALSE

INSERT OVERWRITE

• Specifies that the target table should be truncated before inserting the values into the table.
• To use the OVERWRITE option on INSERT, you must use a role that has DELETE privilege on the table because OVERWRITE will delete the existing records in the table.

E.g.

INSERT OVERWRITE INTO table_name

  SELECT * FROM src_table_name

  WHERE city = 'abcd';

[COPY] File Format Parameters

Property	Property Type
TYPE	String
RECORD_DELIMITER	String
FIELD_DELIMITER	String
FILE_EXTENSION	String
SKIP_HEADER	Integer
DATE_FORMAT	String
TIME_FORMAT	String
TIMESTAMP_FORMAT	String
BINARY_FORMAT	String
ESCAPE	String
ESCAPE_UNENCLOSED_FIELD	String
TRIM_SPACE	Boolean
FIELD_OPTIONALLY_ENCLOSED_BY	String
NULL_IF	List
COMPRESSION	String
ERROR_ON_COLUMN_COUNT_MISMATCH	Boolean
VALIDATE_UTF8	Boolean
SKIP_BLANK_LINES	Boolean
REPLACE_INVALID_CHARACTERS	Boolean
EMPTY_FIELD_AS_NULL	Boolean
SKIP_BYTE_ORDER_MARK	Boolean
ENCODING	String

Combining Streams & Tasks

CREATE TASK my_task 

WAREHOUSE = my_wh

SCHEDULE = '15 MINUTE'

WHEN SYSTEM$STREAM_HAS_DATA('my_stream_name')

AS 

INSERT INTO my_tgt_table (time_col) VALUES (CURRENT_TIMESTAMP);

Data Sampling Methods

"Data sampling" refers to selecting a subset of data from a larger dataset, typically for testing, analysis, or performance purposes.

• ROW or BERNOULLI
• Every ROW is chosen with percentage p
• More "Randomness"
• Smaller tables
• e.g. SELECT * FROM table_name SAMPLE ROW (<p>) SEED(15); 
• BLOCK or SYSTEM
• Every BLOCK is chosen with percentage p
• More "Effectiveness"
• Larger tables
• e.g. SELECT * FROM table_name SAMPLE SYSTEM(<p>) SEED(15);

    Here, <p> Returns approximately p% of the table rows randomly.

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;

Stage with JSON load

CREATE FILE FORMAT:

  CREATE OR REPLACE FILE FORMAT my_json_format

  TYPE = 'JSON'

  COMPRESSION = 'AUTO'

  ENABLE_OCTAL = FALSE

  ALLOW_DUPLICATE = FALSE

  STRIP_OUTER_ARRAY = TRUE

  IGNORE_UTF8_ERRORS = FALSE;

CREATE INTERNAL NAMED STAGE:

  CREATE OR REPLACE STAGE my_internal_stage

  FILE_FORMAT = my_json_format;

CREATE TABLE:

CREATE OR REPLACE TABLE json_data_raw (

  id NUMBER AUTOINCREMENT,

  raw_json VARIANT,

  load_time TIMESTAMP_NTZ DEFAULT CURRENT_TIMESTAMP

);

LIST THE FILES IN THE STAGE:

LIST @my_internal_stage; 

REMOVE THE FILES FROM STAGE:

REMOVE @my_internal_stage; 

COPY COMMAND TO LOAD THE DATA (INTO A PARTICULAR COLUMN IN THE TABLE)

COPY INTO json_data_raw(raw_json)

FROM @my_internal_stage/samplejson.json

FILE_FORMAT = (FORMAT_NAME = 'my_json_format');

OUTPUT OF THE LOAD:

 QUERY THE TABLE:

SELECT

RAW_JSON:name::string AS emp_name,

RAW_JSON:email::string AS emp_email,

RAW_JSON:isActive::boolean AS emp_status

FROM json_data_raw;