Snowflake Dialect

Snowflake Dialect Overview Snowflake is a cloud-native, columnar, analytical data warehouse. Storage and compute are separated: table data lives in automatically managed micro-partitions in cloud object storage, while queries run on virtual warehouses — elastic compute clusters you size, suspend, and resume independently of the data. That architecture shapes almost everything about this ORM's SnowflakeDialect (src/dialects/snowflake/index.ts). It talks to Snowflake through the official snowflake-sdk Node driver, and several concepts that are first-class in row-oriented OLTP databases simply do not exist here: No traditional B-tree indexes. Micro-partitioning and clustering keys replace them. All index-shaped methods (addIndex, createIndex, removeIndex, dropIndex, createFulltextIndex, createSpatialIndex) are documented no-ops that emit a console warning rather than throwing, so generic ORM code that calls them after createTable keeps working. No native RETURNING. INSERT/UPDATE do not return the affected rows; re-SELECT if you need them. No user-facing table partitioning. createPartitionedTable and friends throw a clear error pointing you at clustering keys instead. Semi-structured data uses VARIANT/OBJECT/ARRAY with PARSEJSON() and :-path access — not JSON/JSONB. Upserts use standard-SQL MERGE INTO. Key facts (from src/dialects/snowflake/index.ts): Dialect name is 'snowflake'; the driver library is 'snowflake-sdk'. Identifiers are double-quoted (escapeId('mycol') → "mycol"), with embedded quotes doubled. Pagination is standard LIMIT/OFFSET (unlike Oracle/MSSQL's OFFSET...FETCH-only style). Connection Snowflake connections are keyed by an account identifier rather than a host/port, and typically name a warehouse, database, and schema. Notes: If warehouse is set, the dialect issues USE WAREHOUSE "COMPUTEWH" immediately after the connection is established — a warehouse must be active (and resumed) for queries to run. If schema is configured, quoteTable('users') qualifies unqualified table names with it: "PUBLIC"."users". Key-pair authentication is supported instead of password: pass privateKey (PEM contents) or privateKeyPath, optionally with privateKeyPass for an encrypted key. authenticator, application, and clientSessionKeepAlive are also forwarded to the driver. Connection retries query() wraps calls in a retry loop tuned for Snowflake's compute lifecycle. The default match list retries connection errors plus transient warehouse states — warehouse is currently suspended, warehouse is resuming, warehouse is being resized — but deliberately not the bare word warehouse, so permanent errors like "warehouse does not exist" fail fast instead of wasting retries. Override with the retry option. Data types getDataTypeSql maps the ORM's type descriptors to Snowflake types. Raw string types are passed through verbatim. Verified in tests/dialects/snowflake.test.ts: ORM type descriptor | Generated SQL | --------------------------------------------- | ------------------- | { key: 'STRING' } | VARCHAR(255) | { key: 'STRING', length: 100 } | VARCHAR(100) | { key: 'TEXT' } | VARCHAR | { key: 'CHAR', length: 4 } | CHAR(4) | { key: 'INTEGER' } | NUMBER(38,0) | { key: 'BIGINT' } | NUMBER(38,0) | { key: 'DECIMAL', precision: 12, scale: 4 } | NUMBER(12,4) | { key: 'FLOAT' } | FLOAT | { key: 'DOUBLE' } | DOUBLE | { key: 'BOOLEAN' } | BOOLEAN | { key: 'DATE' } | TIMESTAMPNTZ | { key: 'DATEONLY' } | DATE | { key: 'TIME' } | TIME | { key: 'JSON' } / { key: 'JSONB' } | VARIANT | { key: 'ARRAY' } | ARRAY | { key: 'UUID' } | VARCHAR(36) | { key: 'BLOB' } | BINARY | { key: 'GEOMETRY' } / { key: 'GEOGRAPHY' }| GEOGRAPHY | Two notable approximations reflecting real Snowflake gaps: INTEGER/BIGINT both become NUMBER(38,0) — Snowflake has a single fixed-point numeric type; the aliases INT, INTEGER, BIGINT, etc. are all synonyms for NUMBER(38,0). ENUM becomes VARCHAR — there is no native ENUM; add a CHECK constraint at the table level if you need value restriction. Semi-structured data (VARIANT) JSON/JSONB map to VARIANT. When you escape() a plain object or array, it is wrapped in PARSEJSON(...) so it lands as semi-structured data: Query into a VARIANT column with buildJsonQuery, which uses Snowflake's :-path access and :: casts instead of JSONEXTRACT: Dotted paths become nested accessors ('address.city' → "data":address.city::STRING). Upsert — MERGE INTO Upserts compile to standard-SQL MERGE INTO, which Snowflake fully supports. Unlike the MySQL-family dialects, conflictFields is required: it forms the ON clause that identifies an existing row. Omitting it would match on all columns and never find a row to update, so every upsert would insert a duplicate — the dialect throws instead of silently doing that. updateOnDuplicate restricts which columns the WHEN MATCHED branch updates: CRUD Standard CRUD builders produce parameterized SQL with ? placeholders and double-quoted identifiers. Verified in tests/dialects/snowflake.test.ts: buildWhereClause supports the usual operator set ($eq, $ne, $gt, $gte, $lt, $lte, $in, $notIn, $between, $like, $startsWith, $endsWith, $substring, $isNull, and logical $and/$or/$not). Snowflake case-insensitive matching uses its ILIKE(...) function form, so $iLike emits ILIKE("col", ?) rather than a plain ILIKE operator. buildUpdateQuery/buildDeleteQuery accept a limit option for interface-compatibility but warn and ignore it — Snowflake's UPDATE/ DELETE do not support LIMIT. Analytical SELECT extras buildSelectQuery accepts Snowflake-friendly analytical extensions: QUALIFY — filter on window-function results without a wrapping subquery, passed as a raw expression: Attributes containing whitespace or parentheses (window functions, expr AS alias) are passed through unescaped so analytical SQL is not mangled by identifier quoting. cte — WITH [RECURSIVE] ... prefixes; if any entry is recursive the whole clause is emitted as WITH RECURSIVE. Time Travel — atTimestamp, atOffset, atStatement, or beforeStatement append an AT(...)/BEFORE(...) clause after the table reference, e.g. FROM "T" AT(OFFSET => -60). Storage, clustering, and Snowflake-native objects Since there are no indexes, use a clustering key to influence micro-partition pruning on large tables: The dialect also wraps a broad set of Snowflake-native DDL. These are exposed as dedicated methods (not standard-SQL shapes) because they have no analogue in other dialects: Zero-copy clone — cloneTable, cloneSchema, cloneDatabase (CREATE ... CLONE ...), optionally combined with Time Travel. Bulk load/unload — createFileFormat, createStage, copyInto (COPY INTO TARGETTABLE FROM @stage), unloadTo (COPY INTO @stage FROM TARGETTABLE). Warehouses — createWarehouse, alterWarehouse, dropWarehouse, suspendWarehouse, resumeWarehouse. Resource monitors — createResourceMonitor/alterResourceMonitor to cap credit usage with NOTIFY/SUSPEND/SUSPENDIMMEDIATE triggers. Streams & tasks — createStream (CREATE STREAM ... ON TABLE) and createTask for change tracking and scheduled DAGs. Sequences — createSequence/nextSequenceValue via CREATE SEQUENCE / NEXTVAL (note: MINVALUE/MAXVALUE/CYCLE are warned-and-ignored — not supported by Snowflake sequences). Stored procedures — createStoredProcedure emits CREATE PROCEDURE ... AS $$ ... $$ with a configurable LANGUAGE (SQL/JavaScript/Python/etc.); dropStoredProcedure targets a specific overload via paramTypes. Auto-increment columns use Snowflake's AUTOINCREMENT property (describeTable also recognizes IDENTITY). Not supported / throws These interface methods throw a clear error rather than emitting invalid SQL: Extensions (createExtension, etc.) — PostgreSQL-only. Foreign Data Wrappers (servers, user mappings, foreign tables) — PostgreSQL-only; use external tables/stages/integrations instead. Table partitioning — micro-partitioning is automatic; use a clustering key. User / role / privilege management builders — Snowflake has real user/role management, but it is not implemented in this dialect, so these throw "not implemented" rather than emitting incorrect SQL. Row-level security (createSecurityPolicy) — use Snowflake row access policies directly. Savepoints are also unsupported: createSavepointSQL / rollbackToSavepointSQL return inert SQL comments (and warn) rather than real savepoint statements — use COMMIT/ROLLBACK for transactional control. startTransaction/commitTransaction/rollbackTransaction emit plain BEGIN/COMMIT/ROLLBACK. Caveats / not-yet-verified SQL generation is unit-tested; the live-execution tests use a mocked driver. The build builders and their SQL strings are asserted directly in tests/dialects/snowflake.test.ts, and the "executes against a real table" tests run against a stubbed snowflake-sdk connection, not a live Snowflake account. End-to-end behavior against a real warehouse (that MERGE, COPY INTO, CLONE, clustering keys, streams/tasks, and Time Travel behave as intended) has not been exercised in this suite. Index/fulltext/spatial methods are no-ops. They only warn; nothing is created. Reach for a clustering key (or SEARCH OPTIMIZATION / Cortex Search in real Snowflake) instead. No RETURNING. After an insert/upsert, re-SELECT the row if you need the stored values back. ENUM is emulated as VARCHAR with no automatic CHECK constraint. Related reading All dialects — what each engine supports Data types — how each type maps to this engine Querying — how a FindOptions becomes SQL