Workspace operations using SQL¶

This topic provides a step-by-step walkthrough of workspace lifecycle operations using SQL. You can run these SQL statements from any Snowflake client, including Snowsight, SnowSQL, or the Snowflake CLI.

Workspaces can be shared or private. In a shared workspace, changes made with PUT must be committed (published) before other users can see them. In a private workspace, no commit is needed because only the owner can access the workspace.

Prerequisites¶

Required privileges vary by operation and are noted in each section below.

Reference workspace files in SQL¶

Use the same snow://workspace/ path format for reading and modifying files in a workspace:

snow://workspace/<database>.<schema>.<workspace>/versions/head/<path>

The database, schema, and workspace identify the workspace object. The optional path after /versions/head/ identifies a file or folder within the workspace. Omit that path to reference the workspace root.

Your default My Workspace uses the object name USER$.PUBLIC.DEFAULT$, not My Workspace. USER$ refers to your personal database. For example, list files in My Workspace:

LIST 'snow://workspace/USER$.PUBLIC.DEFAULT$/versions/head/';

For another private workspace, replace DEFAULT$ with the workspace’s object name. Enclose case-sensitive names or names containing spaces in double quotes, and enclose the full path in single quotes. For example, for a workspace named my_workspace:

LIST 'snow://workspace/USER$.PUBLIC."my_workspace"/versions/head/';

For a shared workspace, use the database and schema that contain it. For example, list files in a shared workspace:

LIST 'snow://workspace/MY_DB.WORK.MY_WORKSPACE/versions/head/';

For full LIST syntax and options, see LIST. The following sections show how to use workspace paths to upload, download, and remove files.

Create a workspace¶

CREATE OR REPLACE WORKSPACE MY_DB.WORK.MY_WORKSPACE;

Grant privileges to another role¶

GRANT { READ | WRITE } ON WORKSPACE MY_DB.WORK.MY_WORKSPACE TO ROLE <role_name>;

Granting WRITE also grants READ. For more information, see Access control privileges and Shared workspaces.

Add a live version¶

A mutable live version must exist before you can upload or remove files. If an operation fails because no live version exists, run the following command and retry the operation with the same /versions/head/ path:

ALTER WORKSPACE MY_DB.WORK.MY_WORKSPACE ADD LIVE VERSION FROM LAST;

Upload a file¶

PUT file:///path/to/report.sql
    snow://workspace/MY_DB.WORK.MY_WORKSPACE/versions/head/
    AUTO_COMPRESS=false OVERWRITE=true;

The destination folder comes after /versions/head/:

• /versions/head/ places the file at the workspace root.
• /versions/head/reports/monthly/ places the file in a subdirectory.

For full PUT syntax and options, see PUT.

Download a file¶

GET snow://workspace/MY_DB.WORK.MY_WORKSPACE/versions/head/report.sql
    file:///path/to/download/;

For full GET syntax and options, see GET.

Remove a file¶

REMOVE snow://workspace/MY_DB.WORK.MY_WORKSPACE/versions/head/report.sql;

For full REMOVE syntax and options, see REMOVE.

List files¶

LIST 'snow://workspace/MY_DB.WORK.MY_WORKSPACE/versions/head/';

For full LIST syntax and options, see LIST.

Commit (publish all changes)¶

Committing publishes all file changes, making them available to other users. Users with their own live version might still see their unpublished changes.

ALTER WORKSPACE MY_DB.WORK.MY_WORKSPACE COMMIT;

Abort live version¶

Discards all unpublished changes, removing the entire live version.

ALTER WORKSPACE MY_DB.WORK.MY_WORKSPACE ABORT;

Rename a workspace¶

ALTER WORKSPACE MY_DB.WORK.MY_WORKSPACE
    RENAME TO MY_DB.WORK.MY_WORKSPACE_V2;

Drop a workspace¶

DROP WORKSPACE MY_DB.WORK.MY_WORKSPACE;

Restore a dropped workspace¶

Use UNDROP to restore a workspace that was dropped, as long as it is still within the retention period.

UNDROP WORKSPACE MY_DB.WORK.MY_WORKSPACE;