Explain Stages: Internal, External & Named Stages in Databases

49 / 66

Snowflake

Stages: Internal, External & Named Stages

Understand where and how files are staged before loading into Snowflake tables.

Data does not go straight from a file into a Snowflake table. It goes to a stage first, and COPY INTO loads from there.

A stage is like a loading dock next to a warehouse — goods (files) are dropped there first, inspected/labeled (file format), and then moved onto the warehouse shelves (tables) in a controlled, repeatable way.

Key Concepts

A stage is just a location Snowflake can read files from.

Internal stages live inside Snowflake, and there are three.

    @~              user stage    one per user, private scratch space
    @%orders        table stage   one per table, tied to that table
    @my_stage       named stage   an object you create, with grants

The user and table stages already exist and need no setup, which makes them convenient for a one-off load. Neither can be granted to anyone else, so anything a team shares needs a named stage.

    CREATE STAGE my_stage FILE FORMAT = (TYPE = CSV SKIP_HEADER = 1);
    PUT file:///data/orders.csv @my_stage;
    COPY INTO orders FROM @my_stage;

External stages point at your own bucket. The files stay in S3, Azure Blob or GCS, and Snowflake reads them where they are:

    CREATE STAGE lake_stage
      URL = 's3://my-bucket/orders/'
      STORAGE_INTEGRATION = my_s3_int
      FILE FORMAT = (TYPE = PARQUET);

This is the normal choice when a data lake already exists. Nothing is uploaded, other tools keep their access, and Snowflake becomes one more reader of the same files.

Use a storage integration, not keys in the stage. A stage can hold credentials directly. A storage integration instead references a cloud IAM role, so no secret is stored in Snowflake and the cloud side controls access.

Credentials written into a stage definition are visible to anyone who can describe it, and they have to be rotated by hand. Integrations are the production answer.

File format objects stop the repetition. Rather than restating delimiters and compression in every COPY INTO:

COPY INTO

    CREATE FILE FORMAT csv_std TYPE = CSV FIELD_DELIMITER = ',' SKIP_HEADER = 1;

Attach it to the stage and every load through that stage parses consistently. A mismatched file format is the most common cause of a load that succeeds and produces nonsense.

Two practical points. LIST @my_stage shows what is actually there, which settles most "why did nothing load" questions in one step. And files loaded from an internal stage are not removed automatically — PURGE = TRUE on the copy, or a later REMOVE, or you pay storage for files you have already consumed.

LIST @my_stagePURGE = TRUEREMOVE

What the interviewer is probing.1. "What are the three kinds?" Probing: the vocabulary. Stalls: "Just stages." Moves up: user, table and named stages internally, plus external stages pointing at S3, Blob or GCS.

2. "Why prefer a named external stage?" Probing: the practical answer. Stalls: "Convenience." Moves up: it holds the location, credentials and file format once, so COPY statements and pipes stay clean and the credential lives in one place.

3. "How should credentials be handled?" Probing: security. Stalls: "In the stage definition." Moves up: a storage integration using a cloud IAM role, so no keys are stored in Snowflake at all.

4. "How do you inspect files before loading?" Probing: the workflow. Stalls: "Load and check." Moves up: LIST the stage and SELECT directly from it with a file format, which catches schema problems before the COPY.