Skip to main content

Internal Stages, Table Stages, User Stages — Deep Explanation

Internal Stages, Table Stages, User Stages — Deep Explanation​

Welcome to RetailCo, our fictional retail company.
Your data engineer, Alice, is preparing to load raw sales and inventory data into Snowflake. Before she can load anything, she needs to understand stages, which are temporary storage areas for files in Snowflake.

“Think of stages as your ‘landing pad’ before data enters the warehouse,” she explains to the team.

Let’s dive into the three main types of Snowflake stages: Internal, Table, and User stages.

🏗️ What Are Stages in Snowflake?​

A stage is a location to store files temporarily before loading them into a table.
Files could be:

  • CSV
  • JSON
  • Parquet
  • ORC

Stages help in:

  • Organizing files
  • Securing access
  • Simplifying bulk loads

RetailCo scenario: Alice wants to stage daily CSV sales files before loading them into the SALES table.

🔹 1️⃣ Internal Stage​

An internal stage lives inside Snowflake.
Snowflake provides three options:

  • User stage → default storage for individual users
  • Table stage → automatically created per table
  • Named internal stage → explicitly created by the user

Internal stages are simple, secure, and fast because they reside within Snowflake.

🔹 2️⃣ Table Stage​

A table stage is automatically created when you create a table.
It is tied directly to the table:

  • No need to manually create a stage
  • Files loaded here are usually specific to the table
  • Convenient for small or per-table loads

RetailCo example:

CREATE OR REPLACE TABLE SALES (
    SALE_ID INT,
    PRODUCT_ID INT,
    QUANTITY INT,
    SALE_DATE DATE
);
-- Snowflake automatically creates a stage at @%SALES

Alice can now upload files directly to the table stage:

PUT file://sales_jan.csv @%SALES;

Then load it with:

COPY INTO SALES;

🔹 3️⃣ User Stage​

A user stage is unique to each Snowflake user.

  • Location: @~
  • Useful for personal or temporary files
  • Great for testing or development

RetailCo example:

PUT file://new_products.csv @~;
COPY INTO PRODUCTS FROM @~ FILE_FORMAT = (TYPE = CSV);

Alice can safely experiment without affecting shared table stages.

🔹 Internal Stage Best Practices​

  1. Use table stages for table-specific loads → keeps things organized
  2. Use user stages for personal testing → avoids conflicts
  3. Clean up staged files after loading → reduces storage cost
REMOVE @%SALES PATTERN='.*\.csv';
  1. Leverage named internal stages for reusable or shared loads:
CREATE STAGE RAW_SALES_STAGE;
PUT file://*.csv @RAW_SALES_STAGE;
COPY INTO SALES FROM @RAW_SALES_STAGE;

🧠 Story Recap — RetailCo in Action​

  1. Daily sales CSVs arrive at RetailCo
  2. Alice uploads them to table stage @%SALES
  3. QA team tests new inventory file in user stage @~
  4. Shared loads use named internal stage @RAW_SALES_STAGE for consistent access

Result: Data is staged efficiently, securely, and ready for fast loading into Snowflake tables.

🏁 Quick Summary​

  • Stages = temporary storage locations in Snowflake for files before loading into tables
  • Table Stage (@%table_name) → tied to a specific table, automatically created
  • User Stage (@~) → personal stage for testing and development
  • Named Internal Stage (@stage_name) → reusable, shared internal stage
  • Best practices: organize by stage type, clean up after load, and use named stages for shared workflows

🚀 Coming Next

👉 External Stages (S3, Azure, GCS) — Real Company Setup Example