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.

Organization Object DDL

Commands

Notes

{ CREATE | DROP | UNDROP } ACCOUNT

Executed by users with the ORGADMIN role. ALTER ACCOUNT is used to set account-level parameters, and is executed by a user with the ACCOUNTADMIN role.

Account Object DDL

Commands

Notes

{ ALTER | CREATE | DESC | DROP } APPLICATION

{ ALTER | CREATE | DROP } APPLICATION PACKAGE

{ ALTER | CREATE | DROP } CONNECTION

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

CREATE also supports cloning existing databases.

{ ALTER | CREATE | DESC | DROP } EXTERNAL VOLUME

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

{ CREATE } CATALOG INTEGRATION

ALTER is not supported for catalog integrations.

{ DESC | DROP } [ API | CATALOG | EXTERNAL ACCESS | NOTIFICATION | SECURITY | STORAGE ] 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 { APPLICATION PACKAGES | APPLICATIONS | DATABASES | EXTERNAL VOLUMES | [ API | CATALOG | EXTERNAL ACCESS | NOTIFICATION | SECURITY | STORAGE ] INTEGRATIONS | NETWORK POLICIES | PRIVILEGES | RESOURCE MONITORS | ROLES | SHARES | USERS | WAREHOUSES }

Database / Schema Object DDL

Commands

Notes

{ ALTER | CREATE | DESC | DROP } AGGREGATION POLICY

Used for creating and managing aggregation policies.

{ ALTER | CREATE | DESC | DROP } ALERT

Used for creating Snowflake alerts.

{ ALTER | CREATE | DESC | DROP } AUTHENTICATION POLICY

Used for creating and managing authentication policies.

{ 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.

{ CREATE | DESC | DROP } HYBRID TABLE

Used for creating and managing Hybrid Tables.

{ ALTER | CREATE | DESC | DROP } ICEBERG TABLE

Used for creating and managing Iceberg tables.

{ 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 } NETWORK RULE

{ ALTER | CREATE | DROP } MODEL

DESCRIBE is not supported for model objects. ALTER is also used for creating and modifying model versions.

{ ALTER | CREATE | DESC | DROP } PACKAGES POLICY

Used for creating and managing packages policies.

{ 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 } PROJECTION POLICY

Used for creating and managing projection policies.

{ 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 { AGGREGATION POLICIES | ALERTS | AUTHENTICATION POLICIES | EVENT TABLES | EXTERNAL FUNCTIONS | FILE FORMATS | HYBRID TABLE | ICEBERG TABLES | MASKING POLICIES | MATERIALIZED VIEWS | NETWORK RULES | PACKAGES POLICIES | PASSWORD POLICIES | PIPES | PROCEDURES | PROJECTION POLICIES | ROW ACCESS POLICIES | SCHEMAS | SECRETS | SEQUENCES | SESSION POLICIES | STAGES | STREAMS | TABLES | TAGS | TASKS | USER FUNCTIONS | VIEWS }

Classes

Command

Notes

{ CREATE | DROP | SHOW } SNOWFLAKE.ML.ANOMALY_DETECTION

Used for managing anomaly detection> models that detect outliers in your time series data.

{ CREATE | DROP } SNOWFLAKE.CORE.BUDGET

Used for managing budgets.

{ ALTER | CREATE | DROP | SHOW } SNOWFLAKE.ML.CLASSIFICATION

Used for managing classification models.

{ CREATE | DROP } SNOWFLAKE.CORE.CUSTOM_CLASSIFIER

SHOW CUSTOM_CLASSIFIERS

Used for managing custom classifiers.

{ CREATE | DROP | SHOW } FORECAST

Used for managing forecast models.

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).

COPY FILES , 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.

[ ASOF JOIN ]

Support for ASOF 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 }