Snowflake: Why would I choose to create an external stage when I can simply copy values into a table directly?

Vincent_Fazio_2890 0
Viewed 42

Why would I choose to create an external stage in Snowlflake when I can simply copy values into a table directly?

For instance, if I can do this:

create or replace TABLE EXAMPLE_DB.PUBLIC.USERS (
    ID INT,
    FIRST_NAME VARCHAR,
    LAST_NAME VARCHAR,
    EMAIL VARCHAR
);

copy into EXAMPLE_DB.PUBLIC.USERS
    from s3://mybucket/users.csv
    file_format = (
        type = csv
        field_delimiter = ','
        skip_header = 1
    );

Why would I ever decide to take the intermediate step of creating an external stage as in:

create or replace STAGE EXAMPLE_DB.PUBLIC.USER_STAGE
    url='s3://mybucket';

copy into EXAMPLE_DB.PUBLIC.USERS
    FROM @STAGE EXAMPLE_DB.PUBLIC.USER_STAGE
    file_format= (type = csv field_delimiter=',' skip_header=1)
    files = ('users.csv');
snowflake-cloud-data-platform
1 Answers

Two reasons are maintenance and DRY rule:

Creating an Azure Stage - External Stages:

In addition to loading directly from files in Azure containers, Snowflake supports creating named external stages, which encapsulate all of the required information for staging files, including:

  • The Azure container where the files are staged.

  • The named storage integration object or Azure credentials for the container (if it is protected).

  • An encryption key (if the files in the container have been encrypted).

Named external stages are optional, but recommended when you plan to load data regularly from the same location

Similiar applies for S3:

Creating an S3 Stage - External Stage:

In addition to loading directly from files in S3 buckets, Snowflake supports creating named external stages, which encapsulate all of the required information for staging files, including:

  • The S3 bucket where the files are staged.

  • The named storage integration object or S3 credentials for the bucket (if it is protected).

  • An encryption key (if the files in the bucket have been encrypted).

Named external stages are optional, but recommended when you plan to load data regularly from the same location.

user_50708790
Related