Stages (internal and external) in Snowflake - Deep Dive

Overview - Stages (internal and external)

What is it?

In Snowflake, stages are storage locations used to hold data files temporarily before loading them into tables or after unloading data from tables. There are two main types: internal stages, which are managed by Snowflake within its cloud storage, and external stages, which link to external cloud storage services like Amazon S3, Microsoft Azure Blob Storage, or Google Cloud Storage. Stages help organize and move data efficiently between Snowflake and external systems.

Why it matters

Stages solve the problem of moving large amounts of data into and out of Snowflake in a controlled, efficient way. Without stages, loading or unloading data would be slow, error-prone, and hard to manage. They act like a mailbox or a temporary holding area, making data transfer smoother and more reliable, which is crucial for data analysis and business decisions.

Where it fits

Before learning about stages, you should understand basic Snowflake concepts like databases, schemas, and tables. After mastering stages, you can explore data loading and unloading commands, file formats, and data pipelines that automate data movement.

Mental Model

Core Idea

Stages are temporary storage spots that hold data files so Snowflake can efficiently load or unload data between its tables and external storage.

Think of it like...

Think of stages like a post office mailbox where you drop off or pick up packages (data files) before they reach their final destination (tables) or after they leave it.

┌───────────────┐       ┌───────────────┐       ┌───────────────┐
│ External Cloud│──────▶│   External    │       │               │
│ Storage (S3,  │       │   Stage       │──────▶│ Snowflake     │
│ Azure, GCS)   │       │ (Pointer to   │       │ Tables        │
└───────────────┘       │ external data)│       │               │
                        └───────────────┘       └───────────────┘

┌───────────────┐       ┌───────────────┐       ┌───────────────┐
│               │       │ Internal      │       │               │
│ Snowflake     │──────▶│ Stage         │──────▶│ Snowflake     │
│ User Uploads  │       │ (Managed by   │       │ Tables        │
│ Files         │       │ Snowflake)    │       │               │
└───────────────┘       └───────────────┘       └───────────────┘

Build-Up - 7 Steps

1

Foundation

What is a Snowflake Stage

Concept: Introduce the basic idea of a stage as a storage location for data files in Snowflake.

A stage in Snowflake is a place where data files are stored temporarily. These files can be loaded into tables or unloaded from tables. Stages help organize data movement and can be internal (managed by Snowflake) or external (pointing to cloud storage).

Result

You understand that stages are like holding areas for data files before or after they interact with Snowflake tables.

Understanding stages as temporary storage clarifies how Snowflake manages data flow efficiently.

2

Foundation

Types of Stages: Internal vs External

Concept: Explain the two main types of stages and their differences.

Internal stages are storage areas inside Snowflake's cloud environment. They are easy to use because Snowflake manages them fully. External stages link to external cloud storage services like Amazon S3, Azure Blob Storage, or Google Cloud Storage. External stages require credentials and configuration to connect.

Result

You can distinguish between internal stages (Snowflake-managed) and external stages (cloud storage pointers).

Knowing the types helps decide where to store data files based on control, security, and integration needs.

3

Intermediate

Using Internal Stages for Data Loading

🤔Before reading on: Do you think internal stages require manual setup or are ready to use by default? Commit to your answer.

Concept: Learn how to use internal stages to upload files and load data into tables.

Snowflake provides default internal stages for each user and table. You can upload files to these stages using commands like PUT. Then, you use COPY INTO to load data from the stage into a table. This process is simple and secure because Snowflake manages the storage.

Result

You can upload data files to internal stages and load them into tables efficiently.

Understanding default internal stages reduces setup time and simplifies data loading workflows.

4

Intermediate

Configuring External Stages with Cloud Storage

🤔Before reading on: Do you think external stages store data inside Snowflake or just point to external locations? Commit to your answer.

Concept: Learn how to create and configure external stages that connect Snowflake to cloud storage services.

To use an external stage, you create it with a URL pointing to your cloud storage bucket or container. You must provide credentials like access keys or roles so Snowflake can access the files. External stages allow Snowflake to read or write data directly to your cloud storage.

Result

You can set up external stages to integrate Snowflake with your existing cloud storage securely.

Knowing how to configure external stages enables flexible data pipelines across cloud platforms.

5

Intermediate

File Formats and Stage Usage

Concept: Understand how file formats relate to stages and data loading.

When loading data from a stage, you specify the file format (like CSV, JSON, Parquet) so Snowflake knows how to interpret the files. You can define named file formats and reuse them. This ensures data is parsed correctly during loading or unloading.

Result

You can control how data files are read or written by associating file formats with stages.

Recognizing the role of file formats prevents data errors and improves pipeline reliability.

6

Advanced

Managing Stage Security and Access Controls

🤔Before reading on: Do you think anyone with Snowflake access can read all stages by default? Commit to your answer.

Concept: Learn about controlling who can access stages and their data files.

Snowflake uses roles and privileges to control access to stages. You can grant or revoke permissions to read, write, or manage stages. For external stages, credentials are stored securely and can be rotated. Proper security ensures data privacy and compliance.

Result

You can secure stages so only authorized users and processes access sensitive data.

Understanding stage security is critical to protect data and meet organizational policies.

7

Expert

Optimizing Data Pipelines with Stages

🤔Before reading on: Do you think stages only hold data temporarily or can they be part of long-term workflows? Commit to your answer.

Concept: Explore how stages fit into automated, scalable data pipelines and performance tuning.

Stages can be used as buffers in data pipelines, enabling batch or streaming data loads. Using internal stages reduces network latency, while external stages allow integration with other cloud services. Experts optimize file sizes, compression, and parallel loading to improve throughput. Monitoring stage usage helps detect bottlenecks or failures.

Result

You can design efficient, reliable data workflows using stages as key components.

Knowing how to optimize stages transforms them from simple storage to powerful pipeline tools.

Under the Hood

Internally, Snowflake stages are cloud storage buckets or containers managed by Snowflake or linked externally. When you upload files to an internal stage, Snowflake stores them in its secure cloud storage. For external stages, Snowflake uses APIs and credentials to access files in external cloud storage. During data loading, Snowflake reads files from the stage, parses them according to the file format, and inserts data into tables. Unloading reverses this process. Access controls and encryption protect data at rest and in transit.

Why designed this way?

Stages were designed to separate data storage from compute, enabling scalable, parallel data loading and unloading. Managing internal stages allows Snowflake to optimize performance and security. Supporting external stages lets users leverage existing cloud storage investments and integrate with other systems. This design balances ease of use, flexibility, and security.

┌───────────────┐       ┌───────────────┐       ┌───────────────┐
│ User Uploads  │──────▶│ Internal Stage│──────▶│ Snowflake     │
│ or External   │       │ (Snowflake    │       │ Compute Node  │
│ Cloud Storage │       │ Managed Cloud │       │ Loads Data    │
│               │       │ Storage)      │       │ into Tables   │
└───────────────┘       └───────────────┘       └───────────────┘

┌───────────────┐       ┌───────────────┐       ┌───────────────┐
│ External Cloud│──────▶│ External Stage│──────▶│ Snowflake     │
│ Storage      │       │ (Pointer +    │       │ Compute Node  │
│ (S3, Azure)  │       │ Credentials)  │       │ Loads Data    │
└───────────────┘       └───────────────┘       └───────────────┘

Myth Busters - 4 Common Misconceptions

Quick: Do you think internal stages store data outside Snowflake's control? Commit yes or no.

Common Belief: Internal stages store data outside Snowflake, so you must manage their security separately.

Reality: Internal stages are fully managed by Snowflake within its secure cloud environment, including encryption and access control.

Why it matters: Believing internal stages are external can lead to unnecessary security work or misconfigurations.

Quick: Do you think external stages automatically sync data with Snowflake tables? Commit yes or no.

Common Belief: External stages keep Snowflake tables automatically updated with the latest files.

Reality: External stages only store files; loading data into tables requires explicit commands like COPY INTO.

Why it matters: Assuming automatic sync can cause stale data or missing updates in tables.

Quick: Do you think you must always create a named stage before loading data? Commit yes or no.

Common Belief: You must create a named stage before uploading or loading any data files.

Reality: Snowflake provides default internal stages for users and tables that can be used without creating named stages.

Why it matters: Not knowing about default stages can slow down development and add unnecessary complexity.

Quick: Do you think file format settings are optional and don't affect data loading? Commit yes or no.

Common Belief: File formats don't matter much; Snowflake can guess how to read any file automatically.

Reality: File formats must be specified correctly to parse data files properly during loading or unloading.

Why it matters:

Expert Zone

1

Internal stages are optimized for low-latency access within Snowflake, making them ideal for temporary data during transformations.

2

External stages can leverage cloud provider features like lifecycle policies and versioning, which Snowflake does not control directly.

3

Using named external stages with role-based access control allows fine-grained security and easier credential rotation.

When NOT to use

Avoid using internal stages for very large datasets that need to be shared across multiple Snowflake accounts or external systems; instead, use external stages. Also, for real-time streaming data ingestion, consider Snowpipe or other streaming services rather than manual stage management.

Production Patterns

In production, teams often automate data uploads to external stages via cloud ETL tools, then trigger Snowflake COPY commands. Internal stages are used for intermediate data during complex transformations. Monitoring stage usage and file retention policies prevents storage bloat and cost overruns.

Connections

Data Pipelines

Stages act as buffers or checkpoints within data pipelines.

Understanding stages helps design reliable pipelines that handle data movement and transformation in steps.

Cloud Storage Services

External stages connect Snowflake to cloud storage APIs and security models.

Knowing cloud storage concepts clarifies how external stages work and how to secure them.

Logistics and Supply Chain

Stages are like warehouses or distribution centers holding goods temporarily before final delivery.

This cross-domain view highlights the importance of temporary storage for efficient flow and error handling.

Common Pitfalls

#1 Uploading files to a stage without specifying the correct file format during loading.

Wrong approach:

COPY INTO my_table FROM @my_stage FILE_FORMAT = (TYPE = 'CSV'); -- but files are actually JSON

Correct approach:

COPY INTO my_table FROM @my_stage FILE_FORMAT = (TYPE = 'JSON');

Root cause:

Misunderstanding that file format must match the actual data file type to parse correctly.

#2 Granting overly broad permissions on stages to all users.

Wrong approach:

GRANT ALL ON STAGE my_stage TO ROLE public;

Correct approach:

GRANT USAGE ON STAGE my_stage TO ROLE data_loader_role;

Root cause:

Not applying the principle of least privilege leads to security risks.

#3 Assuming external stage files are automatically deleted after loading.

Wrong approach:

Loading data from external stage and expecting files to be removed automatically.

Correct approach:

Manually delete files from external storage or configure lifecycle policies; Snowflake does not delete external files.

Root cause:

Confusing internal stage behavior with external stage management.

Key Takeaways

Stages in Snowflake are temporary storage locations that hold data files for loading and unloading operations.

Internal stages are managed by Snowflake and easy to use, while external stages connect to cloud storage services requiring configuration.

Correct file format specification is essential for successful data loading from stages.

Security and access control on stages protect sensitive data and prevent unauthorized access.

Stages are key components in building efficient, scalable, and secure data pipelines in Snowflake.