Skip to content

SQL

  • LATERAL FLATTEN (similar to UnNest) for flattening XML or JSON data
  • ALTER SCHEMA X SWAP Y swaps schemas, eg dev with prod
  • time-travel specify with AT or BEFORE and time as:
    • TIMESTAMP: select * from my_table at(timestamp => '2021-01-01 16:20:00.0700'::timestamp)
    • OFFSET: select * from my_table at(offset => -60*5)
    • STATEMENT: query-id select * from my_table before(statement => '2333333-33499-3432')
  • UNDROP {TABLE,SCHEMA,DATABASE} x
    • use SHOW {TABLE,SCHEMA,DATABASE} HISTORY to see all versions within retention
    • to UNDROP versions before the latest one, undrop and rename until the desired version is reached
  • VARIANT for loading semi-structured (eg JSON) or OBJECT or ARRAY data
  • CREATE STAGE TEST_STAGE URL='s3://bucket/path1/path2/ to create external staging area
  • primary key, unique index, foreign key and not null are supported, but only not null is enforced
  • TRUNCATE TABLE removes load metadata, thus allowing same file to be loaded again
  • TOP n always returns the same results, whereas LIMIT and FETCH return the same results only if ORDER BY is specified
  • Accessing JSON variant columns:
    • use col:<element>, column name is case-insensitive, but element names are not
    • JSON objects can be referenced either by dot or bracket notation. E.g. col:elem.sub_elem or col['elem']['sub_elem']
    • JSON arrays can be referenced by bracket notation. E.g. col:elem[0]
  • SHOW GRANTS ON {<type> <obj>|ACCOUNT}: show all privileges granted on the object
  • SHOW GRANTS TO ROLE ...: show all privileges granted to the role.
  • SHOW GRANTS OF ROLE ...: show users and roles that have been granted this role
  • TXN_MAX_WAITERS and TXN_MAX_TABLE_LOCK_WAITERS limit outstanding write requests on a single table to 20 by default.
  • VARIANT column can be up to 16MB binary encoded (but uncompressed otherwise)
  • Schema level FUTURE GRANT takes precedence and overrides any database-level FUTURE GRANTs

Multi table INSERT

  • conditional: insert all when <cond> then into ...
  • unconditional: insert all into ...

Admin

action affects who notes
system$abort_transaction running transactions user or AccountAdmin show transactions or show locks
system$abort_session active session AccountAdmin
system$cancel_query running query 1
system$cancel_all_queries all session queries 1
alter warehouse .. abort ... all running queries on WH
alter user .. abort ... all running queries of user
alter user .. ser disabled all running+future queries of user
  1. User, user's owner, or operate ownership on warehouse

Variables

  • set (a,b,c) = (1,2,3)
  • dereference values by $ eg select $a;
  • show variables to list all variables
  • SnowSQL variables are different from SQL variables.
    • define -D var=value or !define var=value
    • Use -o variable_substitution=true and &<var> to access