Snowflake CREATE STAGE: Define Storage Spaces for Data
The CREATE STAGE command in Snowflake is used to define a storage space where you can load data files into Snowflake or unload data from Snowflake to files. This command can specify either an internal or external stage:
- Internal Stage: This stage stores data files within Snowflake’s managed environment. It’s fully integrated with Snowflake’s security features.
- External Stage: This stage references data files stored outside of Snowflake in cloud storage services like Amazon S3, Google Cloud Storage, or Microsoft Azure.
Syntax
For Internal Stages:
_10CREATE [ OR REPLACE ] [ TEMP | TEMPORARY ] STAGE [ IF NOT EXISTS ] <internal_stage_name>_10 [ COMMENT = '<string_literal>' ]_10 [ ENCRYPTION = (TYPE = 'SNOWFLAKE_FULL' | 'SNOWFLAKE_SSE') ]_10 [ TAG ( <tag_name> = '<tag_value>' [ , <tag_name> = '<tag_value>' , ... ] ) ];
For External Stages:
_10CREATE [ OR REPLACE ] [ TEMP | TEMPORARY ] STAGE [ IF NOT EXISTS ] <external_stage_name>_10 URL = '<cloud_specific_url>'_10 [ STORAGE_INTEGRATION = <integration_name> | CREDENTIALS = ( cloud_specific_credentials ) ]_10 [ ENCRYPTION = ( cloud_specific_encryption ) ]_10 [ COMMENT = '<string_literal>' ]_10 [ TAG ( <tag_name> = '<tag_value>' [ , <tag_name> = '<tag_value>' , ... ] ) ];
Best Practices
- ✅ Security: Employ
encrypted stagesto protect data at rest. - ✅ Combine Small Files: Instead of loading many small files, combine them into fewer large files, aiming for 100-250 MB per file after compression. This approach reduces overhead and improves data loading efficiency.
- ✅ Compression: Apply compression formats like
GZIPorBZ2to reduce file sizes and minimize storage and transfer costs. This also speeds up the data loading process. - ✅ File Formats: Prefer columnar formats like
Parquetfor analytics, as they provide efficient data compression and selective column loading. - ✅ Cost Management: Monitor external stage usage to control cloud storage costs.
- ✅ Automation: Use features like
auto-refreshandnotification integrationsto keep data synchronized efficiently.
Examples
Internal Stage
Create an internal stage with optional server-side encryption:
_10CREATE STAGE my_internal_stage_10 ENCRYPTION = (TYPE = 'SNOWFLAKE_SSE');
External Stage
Create an external stage that points to an Amazon S3 bucket:
_10CREATE STAGE my_external_stage_s3_10 URL = 's3://mybucket/myfolder/'_10 STORAGE_INTEGRATION = my_s3_integration;
