# Snowflake Schema Design

> Design a Snowflake schema in Schemity: import a GET_DDL script, edit with Snowflake's own types, export a CREATE script, and convert to or from PostgreSQL.

Source: https://schemity.com/doc/snowflake/

A design-only Snowflake diagram is where you design a schema: you draw it or import the script `GET_DDL` returns, edit it with Snowflake's own types, and export a full `CREATE` script to run in a worksheet. A design-only diagram needs no credentials and no warehouse.

To read a live schema instead, connect the diagram to your account: see [Connect to Snowflake](https://schemity.com/doc/connect-snowflake/). A connected diagram shows what the warehouse has and cannot be edited structurally, so designing changes happens here, on a design-only diagram.

Snowflake diagrams are a licensed feature of the desktop app, available during the trial. They are not offered in Schemity Lite or after a trial expires.

![A Snowflake diagram in Schemity with 20 tables for an event booking system, including ACCOUNT, BOOKING, EVENT, PAYMENT and STAFF, with NUMBER(38,0) keys, VARCHAR, TIMESTAMP_LTZ, VARIANT, ARRAY and GEOGRAPHY columns, and defaults such as SYSDATE(), UUID_STRING() and OBJECT_CONSTRUCT()](https://schemity.com/images/doc/snowflake/snowflake-diagram.webp)

## How do I create a Snowflake diagram?

Click **+** above the diagram list, set **DB type** to **Snowflake**, give the diagram a name, and click **Create**. **Naming** switches to `UPPER_SNAKE_CASE`, the case Snowflake stores unquoted names in. The diagram starts design only; draw it or import a script from `GET_DDL`, or click **Connect to a database** to [read a live schema](https://schemity.com/doc/connect-snowflake/) instead.

![The New diagram form for a Snowflake diagram named Event Booking, with the Snowflake logo in the title, the ID under Name, Naming set to UPPER_SNAKE_CASE, Open on, and a Connect to a database link above the Cancel and Create buttons](https://schemity.com/images/doc/snowflake/new-snowflake-diagram.webp)

## How do I import an existing Snowflake schema?

Run `GET_DDL` in a Snowflake worksheet and import its output:

1. Run `SELECT GET_DDL('SCHEMA', 'MY_DB.MY_SCHEMA', TRUE);` and copy the result.
2. In the Snowflake diagram, open **Import SQL**, paste the script or click **Open file...** for a saved `.sql` file, and click **Preview import**.
3. Check the tables listed, then import.

![The Import SQL drawer on a Snowflake diagram, saying it reads CREATE TABLE and ALTER TABLE statements as Snowflake, updates tables already on the diagram without removing any, and skips and counts views, grants and other statements](https://schemity.com/images/doc/snowflake/import-sql.webp)

The script mixes tables with views, sequences, file formats, procedures, and tasks. Import SQL reads the tables and counts the rest as skipped, except sequences: each `CREATE SEQUENCE` is kept, with its start and step, as setup SQL that the diagram's SQL runs first, and a default such as `ORDER_SEQ.NEXTVAL` reads as that sequence, without the original database and schema `GET_DDL` puts in front of it. A dynamic table is not read as a table, and it is listed by name instead of disappearing without a word. Table and column `COMMENT`s become descriptions, and primary keys, unique constraints, foreign keys, and `CHECK` constraints come in with their tables.

How the warehouse stores each table comes in too: a `TRANSIENT` table stays transient, and a clustering key is kept as written, an expression such as `CLUSTER BY (TO_DATE(ORDERED_AT))` included, wherever `GET_DDL` puts it. An identity keeps its start, step and order (`AUTOINCREMENT START 100 INCREMENT 5 NOORDER`), a whole-number column without one is recorded as having none, and a column's `COLLATE` is kept.

A diagram holds one schema, so `GET_DDL('DATABASE', ...)` on a database with several schemas makes Import SQL ask which one to import. To keep a diagram current after the warehouse changes, run `GET_DDL` again and import the new script: tables are matched and listed as **New**, **Updated**, or **Unchanged**, and nothing the script leaves out is removed. See [reverse engineering a database](https://schemity.com/doc/reverse-engineer-database/) for how Import SQL matches tables.

## How does Schemity read Snowflake's types?

Snowflake keeps one name per type family and reports only that one, and the diagram does the same, so a design matches what `GET_DDL` prints and importing it again shows no false changes:

| You write | Snowflake stores, and the diagram shows |
|---|---|
| `INT`, `INTEGER`, `BIGINT`, `SMALLINT` | `NUMBER(38,0)` |
| `DECIMAL(10,2)`, `NUMERIC(10,2)` | `NUMBER(10,2)` |
| `TEXT`, `STRING`, `VARCHAR(16777216)` | `VARCHAR` |
| `CHAR(2)` | `VARCHAR(2)` |
| `DOUBLE`, `REAL` | `FLOAT` |

`GET_DDL` prints every text column at the maximum length, `VARCHAR(16777216)`. That is Snowflake's default, not a length anyone chose, so the diagram shows plain `VARCHAR`. Snowflake also stores a `DEFAULT SYSDATE()` expanded into a long `CONVERT_TIMEZONE` expression, and it reads back as `SYSDATE()`.

The field editor offers Snowflake's types: `NUMBER`, `FLOAT`, `VARCHAR`, `BOOLEAN`, `DATE`, `TIME`, `TIMESTAMP_NTZ`, `TIMESTAMP_LTZ`, `TIMESTAMP_TZ`, `VARIANT`, `OBJECT`, `ARRAY`, `BINARY`, `GEOGRAPHY`, and `GEOMETRY`. Unquoted names are upper case, as Snowflake stores them, so `orders` in a script becomes `ORDERS` in the diagram. A quoted name keeps its case, like the `group` table in the diagram above.

## What does the exported Snowflake SQL contain?

Choose **Export > SQL file** for the whole diagram, or **Get SQL** on a selection. The script creates every table with its columns, defaults, `NOT NULL`, primary keys, unique constraints, foreign keys, and `CHECK` constraints. It follows what Snowflake does with each of them:

- **Keys are written but not enforced.** Snowflake records primary, unique, and foreign keys on standard tables without enforcing them, as its [constraints overview](https://docs.snowflake.com/en/sql-reference/constraints-overview) says. They are still worth declaring: they are how the diagram documents its relationships, and how BI and catalog tools reading the warehouse find them.
- **`ON DELETE` and `ON UPDATE` are left out.** Snowflake accepts the syntax but never runs the action, so a `CASCADE` in the script would promise something that never happens.
- **`NOT NULL` and `CHECK` are enforced**, and both are written.
- **No indexes.** Standard Snowflake tables have none, so the editor hides indexes on a Snowflake diagram and the script writes none.
- **Table options and identity are written as imported:** `CREATE TRANSIENT TABLE`, the clustering key after the column list, `COLLATE`, and each identity with its start and step. A whole-number key drawn in the editor, with nothing recorded, gets `AUTOINCREMENT`; one imported without an identity gets none, so a key the data brings is never numbered by the warehouse. The field editor's **Auto increment** tick sets it, with its start, step and `ORDER` or `NOORDER`.
- **Names are left bare** when Snowflake reads them as written, so the tables are created in upper case and every later query finds them with or without quotes. A name with spaces or other characters, or a reserved word, is quoted: the `ACCOUNT` table is written as `"ACCOUNT"`, since `ACCOUNT` is reserved in Snowflake.

![The Diagram SQL dialog on a Snowflake diagram, with Snowflake (this diagram) selected in Database and a 220-line script: CREATE TABLE "ACCOUNT" with an AUTOINCREMENT key, SYSDATE() and CURRENT_TIMESTAMP(3) defaults, primary key, unique and check constraints, and ACCOUNT_PROFILE with a foreign key and no ON DELETE clause](https://schemity.com/images/doc/snowflake/snowflake-sql-export.webp)

Mermaid, PlantUML, D2, OpenAPI, the data dictionary, and image exports work on a Snowflake diagram as on any other. See [Export SQL & DDL](https://schemity.com/doc/export-sql-ddl/).

## Can I convert a Snowflake schema to PostgreSQL, or PostgreSQL to Snowflake?

Yes, both ways. In the SQL dialog, pick another database in **Database** and the script is rewritten for it, with a report of every type it changed. From Snowflake, `NUMBER(38,0)` becomes a 64-bit integer, `VARIANT`, `OBJECT`, and `ARRAY` become JSON, and `TIMESTAMP_LTZ` becomes a timestamp with time zone. Into Snowflake, a `UUID` becomes `VARCHAR(36)` and an array becomes `ARRAY`, which does not declare an element type. The diagram itself stays Snowflake, so you can hand a PostgreSQL version of a warehouse schema to an application team without forking the design.

## What does Schemity not do on Snowflake?

Schemity never writes to a Snowflake account: there is no impact analysis and no **Migrate**, on a design-only diagram or a [connected](https://schemity.com/doc/connect-snowflake/) one. To change a warehouse, export the script or the converted SQL and run it yourself.

[Schema lint](https://schemity.com/doc/schema-lint/) skips its rules about enforced foreign keys and referential actions, since Snowflake enforces neither, and checks names against Snowflake's 255-character limit. A table with no primary key, or a link table with no uniqueness over its pair, is still reported, with what declaring the key does on Snowflake: it records the key for readers and modeling tools, and duplicates still have to be kept out by the load or `MERGE`.

## Next

Start from tables you already have with [reverse engineering a database](https://schemity.com/doc/reverse-engineer-database/), or see the other database types in the [connections overview](https://schemity.com/doc/connections-overview/).