# DB Snowflake SQL

> Generate, review, and understand Snowflake SQL. Use for Snowflake queries, DDL, DML, MERGE, semi-structured data, stages, COPY operations, materialized views, profiling, transfers, and rewrites where identifiers, namespaces, data types, functions, constraints, or loading syntax differ from other dialects.

- Skill: `datus-ai/db-snowflake-sql` (Agent Skill)
- Install (CLI): `npx skillmds@latest add datus-ai/db-snowflake-sql`
- Raw SKILL.md: https://api.skillmd.com/api/skills/datus-ai/db-snowflake-sql/raw
- Safety review: pending
- Works with: Claude Code, Claude.ai, OpenAI Codex
- Category: Data & Analytics
- Author: datus-ai (https://skillmd.com/u/datus-ai)
- Updated: 2026-09-17
- Page: https://skillmd.com/skills/datus-ai/db-snowflake-sql

---


# Snowflake SQL

Generate Snowflake-compatible SQL from metadata-provided object and column names. Preserve Snowflake object resolution, type, and semi-structured data semantics.

## Namespaces and identifiers

- Qualify schema objects as `database.schema.object` when the current database and schema are not guaranteed; otherwise use the shortest unambiguous name.
- Treat warehouses, roles, and databases as account-level objects rather than schema objects.
- Remember that unquoted identifiers are stored and resolved in uppercase, while double-quoted identifiers preserve exact case and characters.
- Reuse metadata names exactly. Use double quotes for case-sensitive, reserved, spaced, or special-character identifiers; do not use backticks or square brackets.

## Queries and expressions

- Use `LIMIT` or ANSI `FETCH` for row limiting and add a deterministic `ORDER BY` for pagination.
- Use `QUALIFY` to filter window-function results without an extra subquery when it makes the query clearer.
- Use Snowflake functions such as `DATEADD`, `DATEDIFF`, `DATE_TRUNC`, `IFF`, and `TRY_TO_*` with their Snowflake argument order and return semantics.
- Use `ILIKE` for case-insensitive pattern matching when intended. Do not import another dialect's date formatting or interval syntax without conversion.
- Treat session settings such as timezone, week policy, and timestamp mapping as part of date/time semantics when interpreting results.

## Types and semi-structured data

- Use Snowflake types such as `NUMBER(p,s)`, `FLOAT`, `VARCHAR`, `BINARY`, `BOOLEAN`, `DATE`, `TIME`, `TIMESTAMP_NTZ`, `TIMESTAMP_LTZ`, and `TIMESTAMP_TZ`.
- Distinguish `TIMESTAMP_NTZ` from session-timezone-aware `TIMESTAMP_LTZ` and offset-preserving `TIMESTAMP_TZ`; do not use generic `TIMESTAMP` when the distinction matters.
- Use `VARIANT`, `OBJECT`, and `ARRAY` for semi-structured data. Parse text with functions such as `PARSE_JSON` before storing it in `VARIANT`.
- Access semi-structured paths with Snowflake path syntax and cast scalar results to the required SQL type.
- Use `LATERAL FLATTEN(INPUT => expression)` to expand `VARIANT`, `OBJECT`, or `ARRAY` values into rows.

## DDL and writes

- Use `CREATE OR REPLACE` only when replacing the object and its dependent behavior is acceptable; otherwise use `IF NOT EXISTS` or inspect metadata.
- Use `CLUSTER BY` for clustering where justified. Do not generate traditional indexes for ordinary Snowflake tables.
- Treat primary-key, unique, and foreign-key constraints on standard tables primarily as metadata; do not rely on them to enforce integrity. Account for enforced `NOT NULL` and supported `CHECK` constraints, and distinguish hybrid-table enforcement.
- Account for DDL transaction boundaries; do not assume DDL participates in a surrounding DML transaction.
- Use `MERGE` for matched update/delete and unmatched insert logic. Preserve the possibility of multiple source rows matching one target row when reasoning about correctness.

## Data loading and unloading

- Use `COPY INTO table` to load from named, user, or table stages and `COPY INTO location` to unload query or table data.
- Use existing stages and file formats from metadata when available. Otherwise make the required stage, path, file format, pattern, column mapping, and copy options explicit.
- Use a `SELECT` transform inside `COPY INTO table` when files require column reordering, casting, or semi-structured extraction.
- Treat `VALIDATION_MODE`, `ON_ERROR`, `MATCH_BY_COLUMN_NAME`, `PURGE`, and `FORCE` as explicit capabilities with data-quality and idempotency consequences; do not enable them implicitly.
- Never place secrets directly in generated SQL when a storage integration, external stage, or configured credential is available.

## Avoid common dialect leaks

Before returning SQL, reject backticks, PostgreSQL/MySQL upsert syntax, Oracle `FROM DUAL`, SQL Server `TOP`, traditional index DDL, and unqualified objects whose database and schema cannot be inferred.