Data Sharing

 

1. Create Share

CREATE SHARE my_share;

---

2. Grant privileges to share

GRANT USAGE ON DATABASE my_db TO SHARE my_share;
GRANT USAGE ON SCHEMA my_schema.my_db TO SHARE my_share;
GRANT SELECT ON TABLE my_table.myschema.my_db TO SHARE my_share;

---

3. Add consumer account(s)

ALTER SHARE my_share ADD ACCOUNT a123bc;

---

4. Import share

CREATE DATABASE my_db FROM SHARE my_share;

Materialized View & Warehouse

Materialized View

To know the usage history,

    SELECT * FROM information_schema.materialized_view_refresh_history();

    SELECT * FROM information_schema.materialized_view_refresh_history(materialized_view_name             =>  'mname'));

    SELECT * FROM snowflake.account_usage.materialized_view_refresh_history;

Warehouse

Resizing: Warehouses can be resized even when query is running or when suspended.

It impact only future queries, not the running one.

Scale Up vs Scale Out: 

    Scale Up (Resize) - More complex queries

    Scale Out - More User (More queries)

Micro - Partitions & Clustering

Micro - Partitions

• Immutable - Can't be changed
• New data - Added in new partitions

Clustering

Get the clustering key details from the existing tables,

    SELECT * FROM information_schema.tables WHERE clustering_key IS NOT NULL;

To know about the cluster in detail,

    SELECT SYSTEM$CLUSTERING_INFORMATION ('table_name');

To know about the particular column cluster in detail,

    SELECT SYSTEM$CLUSTERING_INFORMATION ('table_name','(column_name)');

To know about the clustering depth,

    SELECT SYSTEM$CLUSTERING_DEPTH ('table_name');

CACHING in Snowflake

Result Cache

• Stores the results of a query (Cloud Services)
• Same queries can use that cache in the future
• Table data has not changed
• Micro-partitions have not changed
• Query doesn't include UDFs or external functions
• Sufficient privileges & results are still available
• Very fast result (persisted query result)
• Avoids re-execution
• Can be disabled by using
• USE_CACHED_RESULT parameter
• If query is not re-used purged after 24 hours
• If query is re-used can be stored up to 31 days

Tip : Result cache is resides in the CLOUD SERVICES layer

Data Cache

• Local SSD cache
• Cannot be shared with other warehouses
• Improve performance of subsequent queries that use the same data
• Purged if warehouse is suspended or resized
• Queries with similar data ⇒ same warehouse
• Size depends on warehouse size

Tip : Data cache is resides in the QUERY PROCESSING layer

Metadata Cache

• Stores statistics and metadata about objects
• Properties for query optimization and processing
• Range of values in micro-partition
• Count rows, count distinct values, max/min value
• Without using virtual warehouse
• DESCRIBE + system-defined functions
• Called as "Metadata store"
• Virtual Private Edition: Dedicated metadata store

Tip : Result cache is resides in the CLOUD SERVICES layer

Query History

In 3 ways we will ab able to view the query history,

1. Using SNOWSIGHT( Web UI)

2. Using INFORMATION_SCHEMA

    SELECT * FROM TABLE (information_schema.query_history()) ORDER BY start_time;

3. Using ACCOUNT_USAGE

    SELECT * FROM snowflake.account_usage.query_history;

UNLOADING

Syntax:

COPY INTO @stage_name FROM (SELECT col1, col2, col3 FROM table_name)

FILE_FORMAT = (TYPE = CSV)

HEADER = TRUE

Additional Parameters:

SINGLE

Use the SINGLE parameter to specify whether the file will be split into multiple files. The default is set to FALSE which means the data will be split across multiple files

MAX_FILE_SIZE

To define the file size

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