SQL reference

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

The Obelisk-native keywords are the primary spelling — ENGINE for compute, CHANGE FEED for CDC objects, and JOB for scheduled SQL — and the Snowflake keywords (WAREHOUSE, STREAM, TASK) keep working as aliases.

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 ENGINE w;  USE ROLE r;
SHOW DATABASES | SCHEMAS | TABLES | VIEWS | ENGINES | CHANGE FEEDS | JOBS | 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;

Engines

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

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

Change feeds & jobs

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

CREATE JOB tk [ENGINE = w] [SCHEDULE = '5 MINUTES'] [AFTER parent]
  [WHEN SYSTEM$CHANGE_FEED_HAS_DATA('S')] AS <one statement>;
ALTER JOB tk RESUME | SUSPEND;
EXECUTE JOB 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 ENGINE 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_ENGINE();
SELECT COUNT(*) FROM t;    -- answered from metadata, no engine

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 an Obelisk DataFrames 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 OBELISK.ACCOUNT_USAGE.QUERY_HISTORY ORDER BY START_TIME DESC LIMIT 20;

SELECT * FROM OBELISK.ACCOUNT_USAGE.ACCESS_HISTORY;          -- object lineage
SELECT * FROM OBELISK.ACCOUNT_USAGE.TABLE_STORAGE_METRICS;   -- active vs time-travel bytes
SELECT * FROM OBELISK.ACCOUNT_USAGE.ENGINE_METERING_HISTORY;
SELECT * FROM OBELISK.ACCOUNT_USAGE.GRANTS_TO_ROLES;
SELECT * FROM OBELISK.ACCOUNT_USAGE.JOB_HISTORY;
SELECT * FROM OBELISK.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 engine, a monthly projection, and your most expensive queries. The gap between metered and active is your optimization roadmap.