SQL reference

ObeliskDB speaks the Snowflake dialect, so the SQL an agent already knows runs unchanged against the local data plane.

Statements are parsed with sqlglot, rewritten, and executed on DuckDB. Identifiers normalize like Snowflake: unquoted → UPPERCASE, "quoted" → verbatim. Anything not listed here that DuckDB supports in a SELECT (window functions, CTEs, QUALIFY, etc.) generally works.

Watch out: // starts a comment in the Snowflake dialect. Use

FLOOR(a / b) for integer division.

Databases, schemas, tables

CREATE [OR REPLACE] DATABASE d;            DROP DATABASE [IF EXISTS] d;  UNDROP DATABASE d;
CREATE [OR REPLACE] SCHEMA s;              DROP SCHEMA s;                UNDROP SCHEMA s;
CREATE [OR REPLACE] TABLE t (col TYPE, …); DROP TABLE t;                 UNDROP TABLE t;
CREATE [OR REPLACE] TABLE t AS SELECT …;                 -- CTAS
CREATE [OR REPLACE] TABLE t2 CLONE t [AT|BEFORE (…)];    -- zero-copy
CREATE [OR REPLACE] VIEW v AS SELECT …;    DROP VIEW v;
USE DATABASE d;  USE SCHEMA s;  USE WAREHOUSE w;  USE ROLE r;
SHOW DATABASES | SCHEMAS | TABLES | VIEWS | WAREHOUSES | STREAMS | TASKS | PIPES | ROLES | GRANTS;
DESCRIBE TABLE t;

Types: NUMBER(p,s), INT, FLOAT, VARCHAR(n), BOOLEAN, DATE, TIME, TIMESTAMP[_NTZ|_LTZ|_TZ], VARIANT (stored as JSON).

DML (copy-on-write)

INSERT INTO t [(cols)] VALUES (…), (…);
INSERT INTO t SELECT …;
UPDATE t SET c = expr, … [WHERE cond];
DELETE FROM t [WHERE cond];
MERGE INTO t USING src|(subquery) s ON cond
  WHEN MATCHED [AND cond] THEN UPDATE SET … | DELETE
  WHEN NOT MATCHED [AND cond] THEN INSERT [(cols)] VALUES (…);
TRUNCATE TABLE t;                          -- metadata-only

DML rewrites only the zone-map-pruned partitions; every result reports number of rows inserted/updated/deleted. Because writes are copy-on-write over immutable files, an agent's changes are diffable and reversible — see Branching and The guarantees.

Time travel

SELECT … FROM t AT(TIMESTAMP => '2026-08-19 12:00:00');
SELECT … FROM t AT(OFFSET => -3600);              -- seconds ago
SELECT … FROM t BEFORE(STATEMENT => '<query_id>'); -- ids in History / !queries
CREATE TABLE t_restored CLONE t BEFORE(STATEMENT => '<query_id>');
UNDROP TABLE t;

Warehouses

CREATE WAREHOUSE wh WITH WAREHOUSE_SIZE='MEDIUM' AUTO_SUSPEND=300
  AUTO_RESUME=TRUE INITIALLY_SUSPENDED=TRUE;
ALTER WAREHOUSE wh SUSPEND | RESUME | SET WAREHOUSE_SIZE='LARGE';
DROP WAREHOUSE wh;

Sizes XSMALL→X4LARGE double credits/hour (XS = 1). Billing is per-second with a 60-second minimum per resume.

Streams & tasks

CREATE STREAM s ON TABLE t [APPEND_ONLY = TRUE];
SELECT *, "METADATA$ACTION" FROM s;         -- INSERT / DELETE change rows
SELECT SYSTEM$STREAM_HAS_DATA('S');         -- metadata-only check
INSERT INTO tgt SELECT … FROM s;            -- consuming DML advances the offset

CREATE TASK tk [WAREHOUSE = w] [SCHEDULE = '5 MINUTES'] [AFTER parent]
  [WHEN SYSTEM$STREAM_HAS_DATA('S')] AS <one statement>;
ALTER TASK tk RESUME | SUSPEND;
EXECUTE TASK tk;                            -- runs tk + its AFTER-children
EXECUTE PIPE my_pipe;                       -- Obelisk extension: run an EL pipe

Governance

CREATE ROLE analyst;  DROP ROLE analyst;
GRANT ROLE analyst TO ROLE sysadmin;
GRANT SELECT ON TABLE t TO ROLE analyst;
GRANT USAGE ON DATABASE d TO ROLE analyst;   -- container USAGE is required
GRANT USAGE ON SCHEMA s TO ROLE analyst;
GRANT USAGE ON WAREHOUSE w TO ROLE analyst;
REVOKE SELECT ON TABLE t FROM ROLE analyst;
SHOW GRANTS TO ROLE analyst;

CREATE MASKING POLICY me AS (v VARCHAR) RETURNS VARCHAR ->
  CASE WHEN CURRENT_ROLE() IN ('HR') THEN v ELSE '***' END;
ALTER TABLE t MODIFY COLUMN email SET MASKING POLICY me;
ALTER TABLE t MODIFY COLUMN email UNSET MASKING POLICY;

CREATE ROW ACCESS POLICY rp AS (region VARCHAR) RETURNS BOOLEAN ->
  CURRENT_ROLE() = 'ADMIN' OR region = 'EMEA';
ALTER TABLE t ADD ROW ACCESS POLICY rp ON (region);
ALTER TABLE t DROP ROW ACCESS POLICY;

RBAC, masking, and row access policies are enforced in the data plane on every query — an agent runs under a role, not around it. See The guarantees.

Session & misc

ALTER SESSION SET USE_CACHED_RESULT = FALSE;
SELECT CURRENT_ROLE(), CURRENT_USER(), CURRENT_DATABASE(),
       CURRENT_SCHEMA(), CURRENT_WAREHOUSE();
SELECT COUNT(*) FROM t;    -- answered from metadata, no warehouse

Semi-structured data (VARIANT)

CREATE TABLE J (ID INT, V VARIANT);
INSERT INTO J SELECT 1, PARSE_JSON('{"user": {"name": "ada"}, "tags": ["x"]}');

SELECT V:user.name::VARCHAR, V:tags[0]::VARCHAR, V:user.age::INT FROM J;

VARIANT columns store JSON; Snowflake's colon-path syntax and PARSE_JSON work everywhere (transpiled to DuckDB's JSON operators). Ingested files with nested objects/arrays land as JSON automatically.

Clustering

ALTER TABLE events CLUSTER BY (day_num);   -- future inserts sort by the key
SELECT SYSTEM$CLUSTERING_INFORMATION('EVENTS');   -- depth/overlap metrics
ALTER TABLE events RECLUSTER;              -- rewrite files sorted by the key

average_depth (1.0 = perfect) measures how many micro-partitions a point lookup on the key must scan — watch it drop after RECLUSTER, and pruning (partitions x/y) improve with it.

Storage GC

VACUUM;   -- delete partition files outside every table's retention window

Honors DATA_RETENTION_TIME_IN_DAYS per table, keeps files any clone still references, and protects recently dropped (UNDROP-able) tables.

Python UDFs & stored procedures

CREATE OR REPLACE FUNCTION DOUBLE_IT(X INT) RETURNS INT
  LANGUAGE PYTHON HANDLER='d'
AS $$
def d(x):
    return None if x is None else x * 2
$$;

SELECT N, DOUBLE_IT(N) FROM T;    -- runs inside the engine, usable anywhere
SHOW FUNCTIONS;  DROP FUNCTION DOUBLE_IT;

Stored procedures receive a Snowpark session (remember .collect() executes):

CREATE PROCEDURE ADD_ROW(V INT) RETURNS VARCHAR
  LANGUAGE PYTHON HANDLER='run'
AS $$
def run(session, v):
    session.sql(f"INSERT INTO D.X.T VALUES ({v})").collect()
    return "ok"
$$;

CALL ADD_ROW(42);  SHOW PROCEDURES;

Divergence: UDF code runs in-process without Snowflake's sandbox, registered engine-wide by bare name.

RESULT_SCAN & LAST_QUERY_ID

SELECT * FROM EVENTS LIMIT 10;
SELECT COUNT(*) FROM TABLE(RESULT_SCAN(LAST_QUERY_ID()));
SELECT "name" FROM TABLE(RESULT_SCAN(LAST_QUERY_ID()));   -- works on SHOW too
SELECT * FROM TABLE(RESULT_SCAN('<query_id>'));           -- ids from History

Every statement's result (≤100k rows) is retained and replayable by query id.

ACCOUNT_USAGE

Zero-latency observability views over the metadata store:

SELECT QUERY_TEXT, PARTITIONS_SCANNED, PARTITIONS_TOTAL, TOTAL_ELAPSED_TIME
  FROM SNOWFLAKE.ACCOUNT_USAGE.QUERY_HISTORY ORDER BY START_TIME DESC LIMIT 20;

SELECT * FROM SNOWFLAKE.ACCOUNT_USAGE.ACCESS_HISTORY;          -- object lineage
SELECT * FROM SNOWFLAKE.ACCOUNT_USAGE.TABLE_STORAGE_METRICS;   -- active vs time-travel bytes
SELECT * FROM SNOWFLAKE.ACCOUNT_USAGE.WAREHOUSE_METERING_HISTORY;
SELECT * FROM SNOWFLAKE.ACCOUNT_USAGE.GRANTS_TO_ROLES;
SELECT * FROM SNOWFLAKE.ACCOUNT_USAGE.TASK_HISTORY;
SELECT * FROM SNOWFLAKE.ACCOUNT_USAGE.PIPE_USAGE_HISTORY;

Every statement an agent runs lands here, so its work is fully auditable after the fact.

Vectors

CREATE TABLE DOCS (ID INT, EMB VECTOR(FLOAT, 384));
INSERT INTO DOCS VALUES (1, [0.12, 0.98, ...]);
SELECT ID, VECTOR_COSINE_SIMILARITY(EMB, [/* query embedding */]) AS SIM
  FROM DOCS ORDER BY SIM DESC LIMIT 10;

VECTOR columns store float lists; VECTOR_COSINE_SIMILARITY, VECTOR_L2_DISTANCE, and VECTOR_INNER_PRODUCT execute natively in the engine — the local RAG substrate (generate embeddings with any model via a Python UDF or app).

Cost simulation

obelisk cost --price 3.0

Prices your actual local workload as if it ran on real Snowflake: metered credits (wall-clock × size rate, with 60s minimums and idle — a realistic bill) vs the active-only floor (pure query time), utilization per warehouse, a monthly projection, and your most expensive queries. The gap between metered and active is your optimization roadmap.