Stages in Snowflake

Published: None

Source: https://www.linkedin.com/pulse/stages-snowflake-arabinda-mohapatra-p4q5c?trackingId=ux5WBiGsSi2CFJi4kUVf3A%3D%3D

---

Stages in Snowflake

Arabinda Mohapatra

Running Kafka streams after dark, diving into genetic code by daylight, and wrestling with Databricks and Tableflow in every spare moment—sleep is optional

February 9, 2025

Stages in Snowflake play a crucial role in data loading and unloading processes. They serve as intermediary storage locations that make it easier to manage and transfer data between Snowflake and external sources or targets

📍User Stage (@~[LOGIN])

• Each user is allocated a default stage for storing files with no storage limit.
• Only the user can see this stage (not even account admin and security admin).
• All your worksheets, including worksheet metadata, are stored in the user's stage.
• These stages are not associated with any file format.
• We cannot alter or drop these stages.
• STAGES and STAGE_STORAGE_USAGE_HISTORY do not provide any information about them.

Use Case: This stage is convenient if your files will only be accessed by a single user but need to be copied into multiple tables.

📍Table Stage (@%[TABLE_NAME])

• Created automatically for all tables with no storage limit.
• Only the table owner can access this stage.
• These stages are also not associated with any file format.
• Table stages do not support transforming data while loading it.

Use Case: Use a table stage if you only need to copy files into a single table but want to make the files accessible to multiple users.

📍Internal Stage and External Stage in Snowflake

In Snowflake, stages are used to load and unload data. There are two main types of stages: Internal Stages and External Stages. Each type has its specific use cases and commands for managing data.

🚀Internal Stage

Internal Stages are managed by Snowflake and are used to store data within the Snowflake environment. There are two types of internal stages:

1. Named Internal Stage: This is a user-created stage for storing files within Snowflake.
2. Table Stage: This is automatically created for each table.

🚀Use Case:

• Use internal stages when you want to manage data within Snowflake without relying on external storage services.
• Ideal for temporary storage during ETL (Extract, Transform, Load) processes.

External Stage

External Stages are used to store data in external cloud storage services such as Amazon S3, Google Cloud Storage, or Microsoft Azure Blob Storage.

Use Case:

1. Use external stages when you need to manage data stored in cloud storage services.
2. Ideal for integrating Snowflake with data stored in external cloud environments for long-term storage or sharing data between systems.

Declartion: The views and opinions expressed in this presentation are those of the author and do not necessarily reflect the official policy or position of Novartis or any of its affiliates or officers