Snowflake cheat sheet

-- Hierarchy: ACCOUNT → DATABASE → SCHEMA → TABLE
SELECT * FROM my_db.public.orders;
SELECT * FROM "MY_DB"."PUBLIC"."ORDERS";       -- case-preserving identifiers (uppercased by default)

-- Set context.
USE ROLE     analyst;
USE WAREHOUSE my_wh;
USE DATABASE  my_db;
USE SCHEMA    my_db.public;

-- Show what you're in.
SELECT CURRENT_ACCOUNT(), CURRENT_DATABASE(), CURRENT_SCHEMA(), CURRENT_ROLE(), CURRENT_WAREHOUSE();

-- Numerics: NUMBER(38, s) is the universal — INT, BIGINT, DECIMAL all alias to NUMBER.
NUMBER(38, 0)                               -- integer
NUMBER(10, 2)                               -- money
FLOAT, DOUBLE, REAL                         -- all 64-bit IEEE 754 (no real distinction)

-- Strings: VARCHAR(n) — n is hint only; underlying storage same up to ~16 MB.
VARCHAR, TEXT, STRING                       -- all aliases for VARCHAR

-- Dates and timestamps.
DATE, TIME, TIMESTAMP_NTZ, TIMESTAMP_LTZ, TIMESTAMP_TZ
-- TIMESTAMP alone is configurable via TIMESTAMP_TYPE_MAPPING — be explicit.

-- Semi-structured: VARIANT, OBJECT, ARRAY.
VARIANT                                     -- can hold JSON, OBJECT, ARRAY, anything

-- Auto-increment.
id NUMBER AUTOINCREMENT START 1 INCREMENT 1 PRIMARY KEY
-- Or use a sequence:
CREATE SEQUENCE my_seq;
INSERT INTO t (id) VALUES (my_seq.NEXTVAL);

-- Path access — colon for top-level, dot for nested.
SELECT payload:user_id::INTEGER             AS user_id,
       payload:user.email::STRING           AS email,
       payload:items::ARRAY                 AS items
FROM   events;

-- Get an array element (0-based).
SELECT payload:items[0]::STRING FROM events;

-- Test if a path exists.
SELECT * FROM events WHERE payload:event_type IS NOT NULL;

-- Build VARIANT.
SELECT OBJECT_CONSTRUCT('id', id, 'email', email) AS user_obj FROM users;
SELECT TO_VARIANT(some_value);

-- Explode an array column to one row per element.
SELECT e.id, f.value AS item
FROM   events e,
       LATERAL FLATTEN(input => e.payload:items) f;

-- Explode an object: one row per key/value pair.
SELECT e.id, f.key, f.value
FROM   events e, LATERAL FLATTEN(input => e.payload) f;

-- FLATTEN columns: SEQ, KEY, PATH, INDEX, VALUE, THIS.

-- Filter on window function results without a subquery wrapper.
SELECT *
FROM   orders
QUALIFY ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY order_date DESC) = 1;

-- Top 5 per region.
SELECT *
FROM   user_revenue
QUALIFY ROW_NUMBER() OVER (PARTITION BY region ORDER BY revenue DESC) <= 5;

MERGE INTO target AS t
USING source AS s
   ON t.id = s.id
WHEN MATCHED AND s.is_deleted THEN DELETE
WHEN MATCHED THEN UPDATE SET t.value = s.value, t.updated_at = CURRENT_TIMESTAMP
WHEN NOT MATCHED THEN INSERT (id, value) VALUES (s.id, s.value);

-- Query a table as it was N minutes / hours / days ago.
SELECT * FROM orders AT(OFFSET => -60 * 5);                        -- 5 minutes ago
SELECT * FROM orders AT(TIMESTAMP => '2026-04-25 09:00:00'::TIMESTAMP);
SELECT * FROM orders BEFORE(STATEMENT => 'query_id_here');

-- Undo an accidental delete (within retention period).
INSERT INTO orders SELECT * FROM orders BEFORE(OFFSET => -300);     -- 5 minutes back

-- Zero-copy clone (instant, no storage cost initially).
CREATE TABLE orders_clone CLONE orders;
CREATE DATABASE prod_clone CLONE prod;

-- Load from S3 / Azure / GCS via a stage.
CREATE STAGE my_stage URL='s3://bucket/path/' FILE_FORMAT = (TYPE = CSV);

COPY INTO orders
FROM @my_stage
FILE_FORMAT = (TYPE = CSV SKIP_HEADER = 1)
ON_ERROR = 'CONTINUE';

-- Unload (export).
COPY INTO @my_stage/exports/orders.csv
FROM orders
FILE_FORMAT = (TYPE = CSV)
HEADER = TRUE;

CURRENT_DATE, CURRENT_TIMESTAMP, SYSDATE()

DATE_TRUNC('MONTH', d)             -- standard arg order
DATEDIFF('day', start, end)        -- (unit, start, end)
DATEADD('day', 7, d)
LAST_DAY(d, 'MONTH')

YEAR(d), MONTH(d), DAY(d)          -- shortcuts
QUARTER(d), DAYOFWEEK(d)

-- Parse / format.
TO_DATE('26/04/2026', 'DD/MM/YYYY')
TO_CHAR(d, 'YYYY-MM-DD')

SHOW DATABASES;
SHOW SCHEMAS;
SHOW TABLES;
SHOW TABLES IN SCHEMA my_db.public;
SHOW COLUMNS IN TABLE orders;
SHOW WAREHOUSES;
SHOW ROLES;
SHOW GRANTS TO ROLE analyst;
SHOW GRANTS ON TABLE orders;

-- Recent queries.
SELECT query_id, query_text, total_elapsed_time, bytes_scanned
FROM   TABLE(INFORMATION_SCHEMA.QUERY_HISTORY())
ORDER  BY start_time DESC
LIMIT  20;

APPROX_COUNT_DISTINCT(col)                  -- HLL-based, fast
APPROX_PERCENTILE(col, 0.95)
APPROX_TOP_K(col, 10)                       -- approx top-N

-- Sample rows.
SELECT * FROM events SAMPLE (1);            -- 1% sample (system / random)
SELECT * FROM events SAMPLE (1000 ROWS);    -- fixed size