STAGE

Config path: /<database>/<schema>/stage/<name>.yaml

Example for internal stage:

file_format: test_csv_format
copy_options:
  on_error: ABORT_STATEMENT
  enforce_length: true

Example of external stage:

url: "gcs://test-bucket"
storage_integration: test_storage_integration
file_format: test_avro_format

Schema

• url (str) - bucket URL for external stage

• storage_integration (ident) - name of storage integration for external stage

• encryption (dict)

  • {key} (ident) - name of encryption parameter

  • {value} (bool, float, int, str) - value of encryption parameter

• directory (dict)

  • {key} (ident) - name of directory parameter

  • {value} (bool, float, int, str) - value of directory parameter

• file_format (ident) - file format for files in stage

• copy_options (dict)

  • {key} (ident) - COPY option name

  • {value} (bool, float, int, list, str) - COPY option value

• comment (str)

Usage notes

1. Stages with url and storage_integration are EXTERNAL. Stages without these parameters are INTERNAL.

2. When INTERNAL stage is being dropped, all files will be lost. When EXTERNAL stage is being dropped, nothing happens. EXTERNAL stage is just a metadata pointing to location in bucket.

3. Business users should only access buckets through stages. Business users should never use STORAGE INTEGRATION objects directly for security reasons.

4. SnowDDL is able to maintain not only stages in general, but also specific files in stages.
5. file_format can only be specified by name referencing to FILE_FORMAT object.

Additional privileges

In order for STAGE objects to operate properly, the following additional grants should be added to OWNER role in schema config:

• owner_integration_usage - please specify names of STORAGE INTEGRATION objects used by external stages. Stages may work without explicit INTEGRATION USAGE grant to OWNER role, but it is not guaranteed for Snowflake keep it this way forever.

Encryption updates

Encryption on existing EXTERNAL stages cannot be updated by SnowDDL. Snowflake does not provide meta-data, so it is not possible to compare config with existing object.

In order to update encryption information manually, you should use an additional CLI argument:

--refresh-stage-encryption

If you want to remove an encryption from EXTERNAL stage, you should set encryption type to NONE explicitly. It is not enough to remove encryption section from config, since default encryption settings are different for each cloud provider and may change in future.

Safe & unsafe operations

All operations on stages are considered as "safe", EXCEPT:

• Replace existing stage due to change of stage type (INTERNAL to EXTERNAL or vice versa) or due to change of INTERNAL stage encryption method.

• Drop existing stage, both INTERNAL and EXTERNAL.

"Unsafe" operations on stages lead to loss of data or meta-data. Also, it may affect other objects depending on stages, like PIPES or EXTERNAL TABLES.

Links

• CREATE STAGE
• ALTER STAGE
• SHOW STAGES
• DESC STAGE
• Parser & JSON Schema (GitHub)