Snowflake

Overview

What the platform encodes about Snowflake, and where Snowflake's own documentation is. It is the target in both documented corridors, so most of what our code says about it is about what it will not enforce.

Product Overview — the product in its own words: what it replaces, and the six features it leads with. Start there if Snowflake is new to you.

Editions — the five tiers and what each adds. Worth reading before trusting the matrix, which is keyed by dialect and knows nothing about editions.

Architecture — the three layers, micro-partitions and virtual warehouses. This is where the matrix below gets its reasons: no indexes, time travel, edition-gated concurrency.

Databases and Schemas — the object hierarchy, the defaults every account ships with, and where identifier case starts causing load failures.

Table Types — permanent, temporary, transient and external, and the Time Travel and Fail-Safe windows behind each. Read it before deciding what staging tables land as.

View Types — standard, secure and materialized. Also where the classifier gap lives: any keyword between CREATE and VIEW routes the statement away from the path that would validate its definition.

Stages — table, user and named stages, and the COPY INTO load metadata. Every row we load passes through one, inside write_pandas, which is where the timestamp corruption of TC-SF-7 happens.

Official Docs — the verified link set, by topic.

What the capability matrix records

State	Features
`ENFORCED`	`sequence, identity_column, materialized_view`
`UNENFORCED`	`primary_key, unique_constraint, foreign_key — accepted, stored, never checked`
`ABSENT`	`trigger, check_constraint, partition, package, custom_type, synonym`

Those three rows are where every blocker and every enforcement gap in both corridors comes from. Nothing else in the platform decides it.

What the DDL profile records

app/ai/ddl/dialects.py

python

DialectProfile(
    name="snowflake",
    enforces=frozenset({"not_null"}),
    identity_explicit_insert="accepted",
    sqlglot="snowflake",
    max_identifier_length=255,
    supports_create_index=False,
    supports_check=False,
    notes=[
        "No CREATE INDEX of any kind. If the source had one, say so in "
        "notes; a clustering key is the closest equivalent.",
        "PRIMARY KEY, UNIQUE and FOREIGN KEY are accepted and stored but "
        "NOT enforced — emit them anyway, for tooling and lineage.",
        "AUTOINCREMENT START n INCREMENT n rather than IDENTITY syntax.",
    ],
)

Three of those fields have a visible consequence in generated output: a CHECK is never emitted because emitting it would read as a guarantee; every source index is written into 02_indexes.sql as a comment and added to the phase's dropped list rather than omitted; and an explicit value into an autoincrement column is accepted, so a load can carry source keys through without a session switch.

What counts as translatable into it

constants/translatable_classes.py keys translatability by target, because it is a property of what the target can express. For Snowflake, triggers, trigger functions and packages are subtracted from the code-bearing set — trigger logic moves to the ELT layer and package routines flatten into standalone procedures, and a redesign is not a translation.

Two emit surfaces, and why both are seeded

The compiler emits to SNOWFLAKE_SQL or SNOWPARK_PYTHON, and the learned-mapping cache is keyed by (source, target, surface, fingerprint) — so the same rewrite is stored twice, once per surface. The seeder's own comment gives the reason: without seeding both, a procedure routed to Snowpark would miss the catalogue for every curated mapping and leak source-side names like XACT_STATE() into Python output.

One validation stage exists only for that surface: python_ast_no_sql_leak.

The load rules, and the incident behind them

The Snowflake target block in app/ai/etl/dialect_rules.py carries seven numbered type rules and one recorded failure. The failure is worth reading in full because it is the kind nothing warns you about:

python

"""**NEVER upper-case the table, schema or database name.** This block used to
upper-case the table name, with a comment saying Snowflake tables are typically
uppercase. A run duly loaded into `AGENT_OFFICE` while the DDL had created
`"agent_office"` — a different object, so every load failed."""

The rule with the worst failure mode is use_logical_type=True, which must be passed wherever a timestamp column exists. At the default, a TIMESTAMP_NTZ(0) target fails loudly and a TIMESTAMP_NTZ(9) target succeeds while storing the year 462488916 — with row counts that reconcile.

A note on its prose rule file

app/db-rules/snowflake.md is 1,524 lines, and much of its supplemental knowledge is indexed by the source construct rather than the Snowflake one — entries titled GETDATE, ISNULL, NOLOCK, TOP, OUTPUT_CLAUSE. It is a file about translating T-SQL into Snowflake as much as one about Snowflake, which is reasonable given the corridors and a real gap if you arrive from PostgreSQL.