# 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. Design only.

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

Schemity supports Snowflake for design, not as a live connection. You draw a Snowflake diagram 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. Schemity never signs in to a Snowflake account, so it needs no credentials and no warehouse, and nothing about your account leaves your machine.

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**. There is no **Connect to a database** button for Snowflake: the form says the diagram is design only, and that you can draw one or import a script from `GET_DDL`.

![The New diagram form with DB type set to Snowflake, the Name, Naming and Open on fields, and the note that Snowflake diagrams are design only, 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. 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.

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.
- **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?

With no live connection, nothing that compares against a database or writes to one applies: there is no re-sync from the warehouse, no impact analysis, no row counts, and no **Migrate**. 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.

## 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/).