Showing posts with label snowflake. Show all posts
Showing posts with label snowflake. Show all posts

Tuesday, 24 March 2026

Snowflake - Notes

WPRKSPACES - In September 2025, Snowflake introduced Workspaces, which combines the functionality of Worksheets, Notebooks, File Manager, Query History, Results / Output, and the Database Explorer into a new, integrated development environment.

Workspaces will be the new default editor and replace Worksheets.

SHOW SCHEMAS IN ACCOUNT - All schemas from all databases are shown (based on current role).

SHOW TABLES IN ACCOUNT - All tables from all databases are shown (based on current role).

After table creation, Snowflake made some changes to the SQL behind the scenes are,

  • Snowflake converted the TEXT data type to VARCHAR
  • Snowflake added a comma and digit to represent the number of decimals in each NUMBER column.

VALIDATE IF THERE IS NO TYPO - e.g. sql as below

select count(*) as schemas_found, '3' as schemas_expected 
from GARDEN_PLANTS.INFORMATION_SCHEMA.SCHEMATA
where schema_name in ('A','AA','AAA'); 

Monday, 23 March 2026

Snowflake - Warehouse

In Snowflake, data is held in databases and any processing of data is done by something called a "warehouse."

Default three compute warehouses,

  1. COMPUTE_WH owned by ACCOUNTADMIN.
  2. SNOWFLAKE_LEARNING_WH owned by ACCOUNTADMIN
  3. SYSTEM$STREAMLIT_NOTEBOOK_WH. That warehouse will be used by Snowflake to do any work required by streamlit apps and notebooks you create and run. You will not use this warehouse directly, only Snowflake will use it, on your behalf.

Scaling Up & Down : Changing the size of an existing warehouse is called scaling up or scaling down

Scaling In & Out : Warehouse is capable of scaling out in times of increased demand


Note : 
  • Snowflake Warehouses do not hold data
  • Opposite of scaling out is snapping back
  • Cluster just means a "group" of servers.
  • The number of servers in a warehouse is different, based on size (XS, S, M, etc)
  • A cluster can hold multiple servers.

Snowflake - Authentication & Authorization

 Authentication  (Identity) : Proven through username & password

Authorization (Access) : Access through RBAC role assignments

Account Admin (See & Do Everything)

                Security Admin (Security administrator can manage security aspects of the account.)

                    User Admin (User administrator can create and manage users and roles)

                Sys Admin (Create DB, Warehouses, Schemas, Views)


           Public

Note: 

  • Other than this, ORG ADMIN is the most powerful
  • Discretionary Access Control (DAC)
  • If you change your system role to another role, when you log out and log back in, your role will revert to the default

Snowflake - Databases

Every time you create a database, Snowflake will automatically create two schemas for you.

  • The INFORMATION_SCHEMA schema holds a collection of views.  
  • The INFORMATION_SCHEMA schema cannot be deleted (dropped), renamed, or moved.

  • The PUBLIC schema is created empty and you can fill it with tables, views and other things over time.
  • The PUBLIC schema can be dropped, renamed, or moved at any time. 
Note: 
  • By default the database created with ACCOUNTADMIN role.
  • ACCOUNTADMIN owns the SYSADMIN role, so it has ownership rights also, but indirectly.

Wednesday, 10 September 2025

Snowflake - Cost Optimization

  1. Reduce auto-suspend to 60 seconds
  2. Reduce virtual warehouse size
  3. Ensure minimum clusters are set to 1
  4. Consolidate warehouses
    • Separate warehouse by workload, requirement & not by domain
  5. Reduce query frequency
    • At many organizations, batch data transformation jobs often run hourly by default. But do downstream use cases need such low latency? Check with business before set up the frequency.
  6. Only process new or updated data
  7. Ensure tables are clustered correctly
  8. Drop unused tables
  9. Lower data retention
    • The time travel (data retention) setting can result in added costs since it must maintain copies of all modifications and changes to a table made over the retention period.
  10. Use transient tables
  11. Avoid frequent DML operations
  12. Ensure files are optimally sized
    • To ensure cost effective data loading, a best practice is to keep your files around 100-250MB. 
    • To demonstrate these effects, 
      • If we only have one 1GB file, we will only saturate 1/16 threads on a Small warehouse used for loading. 
      • If you instead split this file into ten files that are 100 MB each, you will utilize 10 threads out of 16. This level parallelization is much better as it leads to better utilisation of the given compute resources
  13. Leverage access control
  14. Enable query timeouts
  15. Configure resource monitors

Tuesday, 19 August 2025

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;

Monday, 18 August 2025

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

Sunday, 17 August 2025

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
TYPEString
RECORD_DELIMITERString
FIELD_DELIMITERString
FILE_EXTENSIONString
SKIP_HEADERInteger
DATE_FORMATString
TIME_FORMATString
TIMESTAMP_FORMATString
BINARY_FORMATString
ESCAPEString
ESCAPE_UNENCLOSED_FIELDString
TRIM_SPACEBoolean
FIELD_OPTIONALLY_ENCLOSED_BYString
NULL_IFList
COMPRESSIONString
ERROR_ON_COLUMN_COUNT_MISMATCHBoolean
VALIDATE_UTF8Boolean
SKIP_BLANK_LINESBoolean
REPLACE_INVALID_CHARACTERSBoolean
EMPTY_FIELD_AS_NULLBoolean
SKIP_BYTE_ORDER_MARKBoolean
ENCODINGString

Tuesday, 12 August 2025

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;

Monday, 11 August 2025

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;

 

 

 

Stored Procedures

         

            Typically performs database operations, usually administrative operations like INSERT, UPDATE or DELETE.

Doesn't need to return value.

Supported languages are,

  • Snowflake scripting (Snowflake SQL + procedural logic)
  • Javascript
  • Snowpark API (Python, Scala & Java)

General Syntax:

CREATE PROCEDURE proc_name (table_name varchar ,n1 int)
EXECUTE as caller 
RETURN int
LANGUAGE sql
AS
BEGIN
UPDATE IDENTIFIER(:table_name) SET col_name = :n1;
END;

NOTE: 

  • If argument is used in sql statement to refer to as an object then use IDENTIFIER(:argument)
  • If argument is used in sql statement then use :argument

PRIVILEGES:

  • Runs either with caller's or owner's rights.
  • By default, will run with owner's privileges


UDF - User Defined Functions

Typically calculate and return a value.

Supported  Languages:

  • SQL
  • Python
  • Java
  • Javascript


Scalar functions: Returns one output row per input row

Tabular functions: Returns a tabular value for each row

        

Function is a securable-schema level object 


 

General syntax for function:

CREATE FUNCTION (n int)

returns int

AS

$$

n+2;

$$;

Kiro - Core Features

What is Kiro Kiro is an innovative AI-powered IDE that revolutionizes software development through intelligent assistance and structured wor...