How to Export a Schema in Snowflake

Galaxy Glossary

How do I export a schema in Snowflake?

Generate and save the full DDL of a Snowflake schema so it can be recreated elsewhere.

Welcome to the Galaxy, Guardian!

Oops! Something went wrong while submitting the form.

Description

Example H2

Example H3

What does “export schema” mean in Snowflake?

Exporting a schema means extracting every CREATE statement—tables, views, sequences, functions, procedures, and grants—so you can rebuild the schema in another account, database, or environment.

Which Snowflake command exports a schema?

The GET_DDL system function returns the exact DDL for a specified object. Passing a schema name plus the ‘schema’ object type returns DDL for every object inside the schema.

How does GET_DDL work?

GET_DDL(object_type, object_name [, show_semi_qualified]) outputs a single VARCHAR value containing the CREATE statements. Use ‘schema’ as object_type and a fully-qualified schema name (e.g., ‘ECOMMERCE.PUBLIC’).

Basic usage

SELECT GET_DDL('schema', 'ECOMMERCE.PUBLIC');

Save DDL to a stage with COPY INTO

CREATE OR REPLACE STAGE admin.ddl_stage; COPY INTO @admin.ddl_stage/ddl.sql FROM (SELECT GET_DDL('schema', 'ECOMMERCE.PUBLIC')) FILE_FORMAT = (TYPE = CSV COMPRESSION = GZIP FIELD_DELIMITER = NONE);

This writes a gzip-compressed file called ddl.sql.gz to the stage.

Download the file

GET @admin.ddl_stage/ddl.sql.gz file://./

Can I export a single table instead?

Yes. Replace ‘schema’ with ‘table’ in GET_DDL and supply a fully-qualified table name.

Best practices for exporting schemas

Create a dedicated internal stage for DDL exports.
Use GZIP compression to minimize transfer time.
Version DDL files in source control for CI/CD pipelines.
Run as a role with sufficient USAGE and OWNERSHIP privileges.

How do I re-import the schema?

Unzip the file, then run the DDL in your target Snowflake environment using SnowSQL or the UI worksheet.

Why use GET_DDL over INFORMATION_SCHEMA?

GET_DDL builds fully-syntactic CREATE statements—including clustering keys, constraints, masking policies, and grants—saving manual work.

Why How to Export a Schema in Snowflake is important

How to Export a Schema in Snowflake Example Usage


-- Export the DDL for all ecommerce tables (Customers, Orders, Products, OrderItems)
CREATE OR REPLACE STAGE admin.ddl_stage;
COPY INTO @admin.ddl_stage/ecommerce_schema.sql.gz
FROM (
    SELECT GET_DDL('schema', 'ECOMMERCE.PUBLIC')
)
FILE_FORMAT = (
    TYPE = CSV
    COMPRESSION = GZIP
    FIELD_DELIMITER = NONE
);

-- Download to local machine
GET @admin.ddl_stage/ecommerce_schema.sql.gz file://./

How to Export a Schema in Snowflake Syntax


-- Extract full schema DDL
SELECT GET_DDL (
    'schema',                -- object_type
    '<DB_NAME>.<SCHEMA_NAME>', -- object_name
    TRUE                     -- semi-qualified names (optional)
);

-- Export to stage and compress
COPY INTO @<stage>/ddl.sql
FROM (SELECT GET_DDL('schema', '<DB>.<SCHEMA>'))
FILE_FORMAT = (
    TYPE = CSV               -- one large VARCHAR column
    COMPRESSION = GZIP
    FIELD_DELIMITER = NONE   -- no extra delimiters
);

-- Example with ecommerce tables
COPY INTO @admin.ddl_stage/ddl.sql
FROM (
    SELECT GET_DDL('schema', 'ECOMMERCE.PUBLIC')
);

Common Mistakes

Running GET_DDL without a fully-qualified schema name. If multiple databases contain the same schema name, Snowflake raises an error. Always reference DATABASE.SCHEMA.
Copying GET_DDL output directly to a text editor, which truncates long VARCHAR values. Use COPY INTO or SnowSQL’s -o output_file to capture the complete DDL safely.