Snowflake SQL cheat sheet

USE ROLE analyst;
USE WAREHOUSE transform_wh;
USE DATABASE analytics;
USE SCHEMA marts;
SELECT CURRENT_ROLE(), CURRENT_WAREHOUSE();

CREATE WAREHOUSE bi_wh
  WAREHOUSE_SIZE = 'XSMALL'
  AUTO_SUSPEND = 60 AUTO_RESUME = TRUE;
ALTER WAREHOUSE bi_wh SET WAREHOUSE_SIZE = 'SMALL';
ALTER WAREHOUSE bi_wh SUSPEND;

CREATE STAGE raw_stage
  FILE_FORMAT = (TYPE = CSV SKIP_HEADER = 1);
LIST @raw_stage;
COPY INTO raw.orders FROM @raw_stage
  PATTERN = '.*orders.*[.]csv'
  ON_ERROR = 'CONTINUE';

COPY INTO @raw_stage/export/sales_
FROM (SELECT * FROM marts.fct_sales)
FILE_FORMAT = (TYPE = PARQUET)
HEADER = TRUE;

SELECT v:id::NUMBER             AS id,
       v:customer.name::STRING  AS name,
       i.value:sku::STRING      AS sku
FROM raw.events,
     LATERAL FLATTEN(input => v:items) i;
PARSE_JSON('{"a": 1}')

SELECT *
FROM orders
QUALIFY ROW_NUMBER() OVER (
  PARTITION BY customer_id
  ORDER BY order_date DESC) = 1;

IFF(amount > 100, 'big', 'small')
DATEADD(month, -1, CURRENT_DATE)
DATEDIFF('day', start_date, end_date)
DATE_TRUNC('week', order_date)
TRY_TO_NUMBER('12a')     -- NULL, not an error
SPLIT_PART('a-b-c', '-', 2)   -- 'b'

SELECT * FROM t AT(OFFSET => -60*60);  -- 1 hour ago
SELECT * FROM t AT(TIMESTAMP =>
  '2026-09-29 09:00'::TIMESTAMP_LTZ);
SELECT * FROM t BEFORE(STATEMENT => '<query_id>');
UNDROP TABLE t;

CREATE DATABASE analytics_dev CLONE analytics;
CREATE TABLE t_backup CLONE t;
CREATE TABLE t_yday CLONE t
  AT(OFFSET => -60*60*24);

CREATE STREAM orders_s ON TABLE raw.orders;
CREATE TASK nightly WAREHOUSE = transform_wh
  SCHEDULE = 'USING CRON 0 2 * * * Europe/London'
AS CALL refresh_marts();
ALTER TASK nightly RESUME;

CREATE ROLE analyst;
GRANT USAGE ON WAREHOUSE bi_wh TO ROLE analyst;
GRANT USAGE ON DATABASE analytics TO ROLE analyst;
GRANT USAGE ON SCHEMA analytics.marts TO ROLE analyst;
GRANT SELECT ON ALL TABLES IN SCHEMA analytics.marts
  TO ROLE analyst;
GRANT ROLE analyst TO USER maria;

SELECT warehouse_name,
       SUM(credits_used) AS credits
FROM snowflake.account_usage.warehouse_metering_history
WHERE start_time >= DATEADD(day, -30, CURRENT_TIMESTAMP())
GROUP BY 1 ORDER BY 2 DESC;