Understanding Internal and External Stages in Snowflake

In this article, let’s explore Snowflake stages, the locations where data is temporarily or permanently stored before loading into tables or unloading for use elsewhere.

We’ll walk through the differences between internal and external stages, and to make things simple, we’ll compare them to something you already know: a laptop’s internal hard drive versus an external USB hard drive.

Features change from time to time with new functionality added regularly, so it’s recommended to review the Snowflake documentation ↗ for the latest on stage capabilities and storage options.

• What Are Snowflake Stages?
• Internal vs External Stages
  • Internal Stages
  • External Stages
• Quick Comparison Table
• When to Use Which?
• Practical Analogy Recap
• Best Practices
• Conclusion

What Are Snowflake Stages?#

A stage in Snowflake is essentially a storage location where you put your data files before loading them into Snowflake tables, or where you place data you’ve unloaded from Snowflake.

Stages help with:

• Temporary storage for files in transit.
• Integration with external storage systems.
• Performance during bulk loads and unloads.

Snowflake stages come in two main types: internal and external.

Internal vs External Stages#

Think of internal stages like your laptop’s built-in hard drive — the storage is inside your machine (or in Snowflake’s case, inside its environment). You don’t have to connect anything else or worry about where it physically lives — it’s right there, part of Snowflake.

External stages, on the other hand, are like plugging in an external hard drive — the files live in another storage system such as Amazon S3, Azure Blob Storage, or Google Cloud Storage. Snowflake connects to these external systems via a configured stage.

Internal Stages (Laptop’s Internal Drive Analogy)#

• Location: Storage space within Snowflake’s infrastructure.
• Usage: Easiest to set up — no external credentials or integrations required.
• Performance: Often faster for smaller, quick-turnaround jobs because there’s no external network hop.
• Management: Fully managed by Snowflake; you don’t deal with storage provisioning.
• Cost: Snowflake storage rates apply.

Example: Creating and using an internal named stage

CREATE STAGE my_internal_stage;

PUT file://data.csv @my_internal_stage;

COPY INTO my_table
FROM @my_internal_stage
FILE_FORMAT = (TYPE = CSV);

External Stages (External Hard Drive Analogy)#

• Location: Files live in cloud storage you manage — like AWS S3, Azure Blob, or GCP Storage.
• Usage: Great for integrating Snowflake with existing data lakes or sharing data across systems.
• Performance: Dependent on network transfer speeds between Snowflake and your storage service.
• Management: You manage the storage account, permissions, and lifecycle.
• Cost: You pay your cloud provider for the storage.

Example: Creating and using an external stage with S3

CREATE STAGE my_external_stage
URL = 's3://my-bucket/data/'
CREDENTIALS = (
  AWS_KEY_ID = 'xxxxx'
  AWS_SECRET_KEY = 'yyyyy'
);

COPY INTO my_table
FROM @my_external_stage
FILE_FORMAT = (TYPE = CSV);

Quick Comparison Table#

When to Use Which?#

• Use Internal Stages when:

  • You need a quick, Snowflake-only workflow.
  • Data comes from local files or small batch uploads.
  • You want Snowflake to handle all the storage complexity.

• Use External Stages when:

  • Data already lives in cloud object storage.
  • You want to share data across multiple tools or platforms.
  • You need to keep data in your own managed storage for compliance.

Practical Analogy Recap#

• Internal Stage = Laptop’s Built-in Drive: Fast, simple, and managed by Snowflake. You just save files there and use them.
• External Stage = External USB Drive: The files are stored elsewhere, but you connect to them when needed. More flexible, but you manage the storage yourself.

Best Practices#

• Keep file sizes optimal: For bulk loads, aim for files between 100–250 MB compressed for efficient parallel loading.
• Use compression: GZIP or similar to speed up transfers.
• Secure credentials: For external stages, use Snowflake storage integrations instead of embedding credentials in SQL where possible.
• Clean up: Delete or expire staged files you no longer need to save on storage costs.

Conclusion#

Snowflake stages are your data’s “waiting room” before it enters or leaves the warehouse. By understanding the difference between internal and external stages — and keeping in mind the internal vs. external hard drive analogy — you can make smarter choices about where to stage your data for the best mix of speed, cost, and convenience.