Snowflake Stages, Storage Integrations and Stage Encryption

1.Internal and external stages: where staged files live

File-based ingestion in Snowflake starts with a stage. Snowflake stores the files itself in an internal stage, and there are three kinds. Every user and every table automatically gets one, and you can also create named internal stages. A named external stage points at your own Amazon S3, Google Cloud Storage or Microsoft Azure location instead.

With an internal stage, the stage shows up in both steps of a load. You name it in the PUT command that uploads local files, and you name the same stage again in COPY INTO <table>.

The user stage suits files that only one user will touch but that need to go into several tables. You reference it as @~ (for example LIST @~). It has two limits that the exam likes to test. You cannot alter or drop it, and it does not support file format options, so the format and copy options have to go on the COPY INTO <table> statement instead. A named stage has neither limit. If you put a file format in a named stage's definition, queries against that stage no longer need to specify one.

Sources12

2.Storage integrations: credentials kept out of stage definitions

A named external stage has to authenticate to the cloud bucket somehow. The recommended way is a storage integration, a named, first-class Snowflake object that removes the need to pass secret keys or access tokens in SQL. On AWS, Snowflake creates one IAM user for your account, and every S3 storage integration in the account references that same user. An AWS administrator grants that IAM user permissions on the buckets. Many external stages can share one integration while pointing at different buckets and paths.

The integration also sets boundaries. STORAGE_ALLOWED_LOCATIONS lists the buckets and optional paths that stage creators may use, and STORAGE_BLOCKED_LOCATIONS can exclude locations. The cloud-specific parameters depend on the provider: STORAGE_AWS_ROLE_ARN for S3, AZURE_TENANT_ID for Azure, and only STORAGE_PROVIDER = 'GCS' for Google Cloud Storage.

CREATE [ OR REPLACE ] STORAGE INTEGRATION [IF NOT EXISTS]
  <name>
  TYPE = { EXTERNAL_STAGE | POSTGRES_EXTERNAL_STORAGE | POSTGRES_INTERNAL_STORAGE }
  cloudProviderParams
  ENABLED = { TRUE | FALSE }
  STORAGE_ALLOWED_LOCATIONS = ('<cloud>://<bucket>/<path>/' [ , '<cloud>://<bucket>/<path>/' ... ] )
  [ STORAGE_BLOCKED_LOCATIONS = ('<cloud>://<bucket>/<path>/' [ , '<cloud>://<bucket>/<path>/' ... ] ) ]
  [ COMMENT = '<string_literal>' ]

On the AWS side, the IAM policy decides what Snowflake can do with the files. To read from a bucket and folder, Snowflake needs s3:GetBucketLocation, s3:GetObject, s3:GetObjectVersion and s3:ListBucket. Unloading to the bucket also requires s3:PutObject. Purging files after a load, or removing them with REMOVE, requires s3:DeleteObject. So a read-only policy is enough for loading, but not for unloading or purging. Each time someone loads or unloads through the stage, Snowflake checks the IAM user's permissions on the bucket.

Sources3

3.Internal stage encryption: SNOWFLAKE_FULL versus SNOWFLAKE_SSE

The ENCRYPTION clause of CREATE STAGE sets how files on an internal stage are encrypted, and you only get to choose once. SNOWFLAKE_FULL, the default, encrypts on the client during PUT. It uses a 128-bit key by default, or 256 bits if you set CLIENT_ENCRYPTION_KEY_SIZE. The files are then encrypted again on the server with AES-256. SNOWFLAKE_SSE uses server-side encryption only: the cloud service hosting your account encrypts files as they arrive.

The default works for loading, but it gets in the way of unstructured-data access. Snowflake owns the client-side keys, so client-side encrypted files cannot be read by users or external tools through pre-signed, file or scoped URLs. Specify SSE when you create the stage if you plan to serve files through URLs. There's a compliance trade-off: if you need Tri-Secret Secure, you have to use SNOWFLAKE_FULL, because SSE doesn't support it.

CREATE STAGE my_int_stage
  ENCRYPTION = (TYPE = ' ? ')
  DIRECTORY = ( ENABLE = true );

Sources45

4.Unstructured data: directory tables and the three URL types

Unstructured files such as images, documents and audio are not parsed into rows. They stay on the stage, and Snowflake gives you ways to catalogue them and hand them out. A directory table is a catalogue of the staged files. Roles with enough privileges can query it to get file URLs. TO_FILE returns a FILE object for a file on an internal or external stage.

There are three ways to hand a file to someone else, and the exam tests the differences:

- Scoped URL (BUILD_SCOPED_FILE_URL): encoded and time-limited. Only the user who generated it can use it, and it expires with the query results cache, currently 24 hours. Snowflake records in query history who used it and when. A view that returns scoped URLs lets you grant file access to whichever roles can query the view. - File URL (BUILD_STAGE_FILE_URL, or a directory table query): permanent. It is sent to the REST API with an authorization token, and the role in the call needs USAGE (external stage) or READ (internal stage) on the stage. Consumers of a secure data share cannot use file URLs. - Pre-signed URL (GET_PRESIGNED_URL): open. It needs no Snowflake authentication, so anyone who has the URL can download the file until the expiration_time you set runs out. This suits BI and reporting tools.

Sources56

5.Schema detection with INFER_SCHEMA: designing tables from staged files

Before you load staged files you often need to know what columns they contain. The INFER_SCHEMA table function scans staged files and returns their column definitions. You give it a LOCATION (a named internal or external stage, or the user stage, optionally with a path) and a FILE_FORMAT (the name of a file format object). It supports Parquet, Avro, ORC, JSON and CSV files. It does not support table stages.

Each row it returns describes one column: COLUMN_NAME, TYPE, NULLABLE, EXPRESSION (in the form $1:COLUMN_NAME::TYPE), FILENAMES and ORDER_ID. That makes it useful for data analysis, since you can see the detected names and types, and for table design, since you can feed the output straight into a CREATE TABLE.

-- Create a file format that sets the file type as Parquet.
CREATE FILE FORMAT my_parquet_format
  TYPE = parquet;

-- Query the INFER_SCHEMA function.
SELECT *
  FROM TABLE(
    INFER_SCHEMA(
      LOCATION=>'@mystage'
      , FILE_FORMAT=>'my_parquet_format'
      )
    );

The optional arguments tune the scan:

- FILES lists specific files to scan (up to 1000 names). If one cannot be found, the query is aborted. - MAX_FILE_COUNT caps how many files are scanned. It suits large numbers of files with identical schemas, but it cannot choose which files are scanned. Use FILES for that. - MAX_RECORDS_PER_FILE caps the records scanned per file. It applies only to CSV and JSON, and it might affect the accuracy of detection. - IGNORE_CASE => TRUE treats column names as case-insensitive and returns them in uppercase. - KIND => 'ICEBERG' returns Iceberg data types. Set it when you infer Parquet files to create Iceberg tables, otherwise the column definitions might be incorrect.

Some detection limits are worth remembering. For CSV, PARSE_HEADER = TRUE in the file format uses the first row as column names; the default FALSE returns names as c1, c2 and so on, and SKIP_HEADER is not supported together with it. For both CSV and JSON, all columns are identified as nullable. All timestamp variations are retrieved as TIMESTAMP_NTZ. For nested data, only the first level of nesting is supported.

To build the table, run CREATE TABLE ... USING TEMPLATE with a subquery that aggregates the INFER_SCHEMA output with ARRAY_AGG(OBJECT_CONSTRUCT(*)). The same clause works for external tables and Iceberg tables. GENERATE_COLUMN_DESCRIPTION builds on the INFER_SCHEMA output to simplify creating tables, external tables or views. Using * can fail on a very large result, so for large result sets select only the columns you need.

Sources7

Exam traps

Each one states something that sounds right. Open it to see what is actually true.

1. 1.You can switch an existing internal stage from SNOWFLAKE_FULL to SNOWFLAKE_SSE once you decide to serve files through pre-signed URLs.Why is that wrong?

   The encryption type is fixed when the stage is created. To serve files through URLs you need a stage that was created with SNOWFLAKE_SSE.

   Covered in Internal stage encryption: SNOWFLAKE_FULL versus SNOWFLAKE_SSE

2. 2.Each S3 storage integration gets its own dedicated IAM user, so you need a separate AWS grant for every integration.Why is that wrong?

   Snowflake creates one IAM user per account, and every S3 storage integration in that account references it.

   Covered in Storage integrations: credentials kept out of stage definitions

3. 3.The user stage is a normal stage object that you can alter, for example to attach a default file format.Why is that wrong?

   User stages cannot be altered or dropped, and they do not support file format options.

   Covered in Internal and external stages: where staged files live

4. 4.INFER_SCHEMA can scan the files in a table stage (@%mytable) just like a user stage or named stage.Why is that wrong?

   INFER_SCHEMA accepts only named stages (internal or external) and user stages.

   Covered in Schema detection with INFER_SCHEMA: designing tables from staged files