Summary of Commands

Snowflake supports most of the commands and statements defined in SQL:1999.

DDL (Data Definition Language) Commands

Account-based and Session-based DDL

Commands

Notes

ALTER ACCOUNT

Used to set account-level parameters (only by users with ACCOUNTADMIN role).

ALTER SESSION

Used to set parameters in the current session.

SHOW FUNCTIONS

Displays system-defined functions and user-defined functions, including external functions.

SHOW PARAMETERS

Used to view parameters settings for the account or current session. Also can be used to view parameter settings for objects that have parameters.

SHOW VARIABLES

Used to view SQL variables in the current session.

SET , UNSET

Used to set and unset SQL variables in the current session.

USE { DATABASE | ROLE | SCHEMA | WAREHOUSE }

The USE command sets the database, role, schema, or warehouse for the current session.

Account Object DDL

Commands

Notes

{ ALTER | CREATE | DROP } CONNECTION

{ ALTER | CREATE | DESC | DROP | UNDROP | USE } DATABASE

CREATE also supports cloning existing databases.

{ ALTER | CREATE } { API | NOTIFICATION | SECURITY | STORAGE } INTEGRATION

{ DESC | DROP } INTEGRATION

{ ALTER | CREATE | DESC | DROP } NETWORK POLICY

{ ALTER | CREATE | DESC | DROP } RESOURCE MONITOR

{ ALTER | CREATE | DESC | DROP | USE } ROLE

{ ALTER | CREATE | DESC | DROP } SHARE

{ ALTER | CREATE | DESC | DROP } USER

{ ALTER | CREATE | DESC | DROP | USE } WAREHOUSE

COMMENT

This command can be used with any account object; also, comments be set using CREATE or ALTER.

{ GRANT | REVOKE } PRIVILEGE

Privileges are not strictly a first-class object; however, these commands are used for enabling and managing access control for all first-class objects in Snowflake.

SHOW { DATABASES | INTEGRATIONS | NETWORK POLICIES | PRIVILEGES | RESOURCE MONITORS | ROLES | SHARES | USERS | WAREHOUSES }

Database / Schema Object DDL

Commands

Notes

{ ALTER | CREATE | DESC | DROP } ALERT

Used for creating Snowflake alerts.

{ ALTER | CREATE | DESC | DROP } EVENT TABLE

Used for creating and managing event tables.

CREATE EXTERNAL FUNCTION

Used for creating external functions.

{ ALTER | CREATE | DESC | DROP } FILE FORMAT

CREATE also supports cloning existing file formats.

{ ALTER | CREATE | DESC | DROP } FUNCTION

Used for creating UDFs (user-defined functions) and managing UDFs and external functions.

{ ALTER | CREATE | DESC | DROP } MASKING POLICY

Used for creating and managing masking policies.

{ ALTER | CREATE | DESC | DROP | UNDROP } MATERIALIZED VIEW

Used for creating and managing materialized views.

{ ALTER | CREATE | DESC | DROP } PASSWORD POLICY

Used for creating and managing password policies.

{ ALTER | CREATE | DESC | DROP } PIPE

{ ALTER | CREATE | DESC | DROP } PROCEDURE

Used for creating and managing stored procedures.

{ ALTER | CREATE | DESC | DROP } ROW ACCESS POLICY

Used for creating and managing row access policies.

{ ALTER | CREATE | DESC | DROP } SECRET

Used for creating and managing secrets.

{ ALTER | CREATE | DESC | DROP } SEQUENCE

CREATE also supports cloning existing sequences.

{ ALTER | CREATE | DESC | DROP } SESSION POLICY

Used for creating and managing session policies.

{ ALTER | CREATE | DESC | DROP } STAGE

CREATE also supports cloning existing named stages (external only).

{ ALTER | CREATE | DESC | DROP } STREAM

CREATE also supports cloning existing named streams.

{ ALTER | CREATE | DESC | DROP | UNDROP | USE } SCHEMA

CREATE also supports cloning existing schemas.

{ ALTER | CREATE | DESC | DROP | TRUNCATE | UNDROP } TABLE

CREATE also supports cloning existing tables, creating populated tables (using CTAS), and creating empty tables (from existing tables).

{ ALTER | CREATE | DROP | UNDROP } TAG

DESCRIBE is not supported for the tag object.

{ ALTER | CREATE | DESC | DROP } TASK

{ ALTER | CREATE | DESC | DROP | UNDROP } VIEW

COMMENT

This command can be used with any database/schema object; also, comments be set using CREATE or ALTER.

SHOW { ALERTS | EVENT TABLES | EXTERNAL FUNCTIONS | FILE FORMATS | MASKING POLICIES | MATERIALIZED VIEWS | PASSWORD POLICIES | PIPES | PROCEDURES | ROW ACCESS POLICIES | SCHEMAS | SECRETS | SEQUENCES | SESSION POLICIES | STAGES | STREAMS | TABLES | TAGS | TASKS | USER FUNCTIONS | VIEWS }

DML (Data Manipulation Language) Commands

Commands

Notes

DELETE , INSERT , MERGE , REPLACE , UPDATE

Used for general DML. INSERT includes support for multi-table inserts.

COPY INTO { <table> | <location> }

Used for loading/unloading data (i.e. bulk import/export).

GET , LIST , PUT , REMOVE

These commands do not perform DML; they are used to stage and manage files for data loading and unloading.

Query Syntax & Operators

Category

Constructs/Operators

Notes

Query Syntax

[ WITH ]

SELECT [ DISTINCT ]

Required.

[ INTO ]

For setting Snowflake Scripting variables to values in result columns.

[ FROM ]
[ AT | BEFORE ]

For querying using Time Travel.
[ CHANGES ]

For querying the change tracking metadata for a table.
[ CONNECT BY ]

For joining a table to itself to process hierarchical data in the table.
[ JOIN ]

Support for inner, left outer, right outer, and full joins.
[ MATCH_RECOGNIZE ]

For finding sequences of rows that match a pattern.
[ PIVOT | UNPIVOT ]
[ VALUES ]
[ SAMPLE ] / [ TABLESAMPLE ]

TABLESAMPLE is a synonym for SAMPLE.

[ WHERE ]

[ GROUP BY ]

Columns used in GROUP BY must be in SELECT list.
[ HAVING ]

[ ORDER BY ]

[ LIMIT ]

Arithmetic Operators

+ , - , * , \ , %

Comparison Operators

= , != , < > , > , >= , < , <=

Logical/Boolean Operators

AND , NOT , OR

Set Operators

INTERSECT [ ALL ] , MINUS [ ALL ] / EXCEPT , UNION [ ALL ]

Subquery Operators

ANY / ALL , [ NOT ] EXISTS , [ NOT ] IN

Predicates

BETWEEN , [ NOT ] EXISTS , [ NOT ] IN , LIKE

TCL (Transaction Control Language) Commands

Commands

Notes

BEGIN , COMMIT , ROLLBACK

Used for multi-statement transactions in the current session.

DESC TRANSACTIONS

SHOW { LOCKS | TRANSACTIONS }
Language: English