DML Commands - File Staging
Removes files from either an external (external cloud storage) or internal (i.e. Snowflake) stage.
For internal stages, the following stage types are supported:
Named internal stage
Stage for a specified table
Stage for the current user
REMOVE can be abbreviated to RM.
- See also:
REMOVE internalStage [ PATTERN = '<regex_pattern>' ]
internalStage ::= @[<namespace>.]<int_stage_name>[/<path>] | @[<namespace>.]%<table_name>[/<path>] | @~[/<path>]
Specifies the location in Snowflake where files are staged:
Files are removed from the specified named internal stage.
Files are removed from the stage for the specified table.
Files are removed from the stage for the current user.
namespaceis the database and/or schema in which the named internal stage or table resides. It is optional if a database and schema are currently in use within the session; otherwise, it is required.
pathis an optional case-sensitive path for files in the cloud storage location (i.e. files have names that begin with a common string) that limits access to a set of files. Paths are alternatively called prefixes or folders by different cloud storage services.
If the stage name or path includes spaces or special characters, it must be enclosed in single quotes (e.g.
'@"my stage"'for a stage named
PATTERN = 'regex_pattern'
Specifies a regular expression pattern for filtering files to remove. The command lists all files in the specified
pathand applies the regular expression pattern on each of the files found.
Removing files from an external stage requires granting the following role or permission to Snowflake in your cloud storage account:
Cloud Storage Service
Role or Permission
Google Cloud Storage
Microsoft Azure (Blob storage)
Storage Blob Data Contributor
The command removes all directories and files that match a specified path. For example, the following statement would match any of the following objects in the
To remove all files for a specific directory, include a forward-slash (
/) at the end of the path. For example:
Do not remove the
worksheet_datadirectory in the Snowflake user stage. The classic web interface stores metadata for worksheets in the Worksheets tab in this directory. Removing the directory removes access to the worksheets, which cannot be restored.
Note that this precaution does not apply to Snowsight worksheets in the new web interface, which are stored elsewhere and are not prone to accidental deletion.
If a REMOVE statement is interrupted before it has completed running, any files already removed by the statement are not restored.
Remove all files from the
path1/subpath2 path in a named internal stage named
Remove all files from the stage for the
Use the abbreviated form of the command to remove files whose names match the pattern
*jun* from the stage for the current user:
RM @~ pattern='.*jun.*';