Data Transformation · Topic 7 of 14
Unstructured Data - URLs, Directory Tables and the REST API
Work with files in Snowflake stages: scoped, file and pre-signed URLs, directory tables and refresh, processing files in UDFs, and the REST APIs.
- 5 min
- Unstructured data stays in stages; access it through URLs, directory tables and code.
- Scoped URL: caller only, up to 24 hours. File URL: permanent, privilege based. Pre-signed URL: anyone, expires by argument.
- Internal stages need
ENCRYPTION = (TYPE = 'SNOWFLAKE_SSE')for URL access; it cannot be changed later.
What it is
Unstructured data is files with no table shape: images, PDFs, audio, video and documents. Snowflake does not load them into table columns. They stay as files in an internal or external stage, and you reach them through URLs, a directory table and code that reads the file.
The data engineer's tasks are to make the files discoverable, hand out access safely, and run code or functions that extract information from them.
How it works
Set up the stage
To use URLs on an internal stage, create it with Snowflake server side encryption. Internal stages are client side encrypted by default, and those files cannot be read through scoped, file or pre-signed URLs.
CREATE STAGE docs
ENCRYPTION = (TYPE = 'SNOWFLAKE_SSE')
DIRECTORY = (ENABLE = TRUE);
The encryption type cannot be changed after the stage is created.
URL types
There are three ways to reference a staged file by URL. Know their differences cold.
| URL type | Generated with | Lifetime | Who can use it |
|---|---|---|---|
| Scoped URL | BUILD_SCOPED_FILE_URL(@stage, 'path') | Until the query result cache expires, currently 24 hours | Only the user who generated it; access is logged in query history |
| File URL | BUILD_STAGE_FILE_URL(@stage, 'path'), or the FILE_URL column of a directory table | Permanent | A role with READ on an internal stage or USAGE on an external stage, sending an authorization token |
| Pre-signed URL | GET_PRESIGNED_URL(@stage, 'path', expiration_time) | Set by the expiration argument in seconds | Anyone who holds the URL, with no Snowflake authentication, until it expires |
Scoped URLs and pre-signed URLs can be shared with consumer accounts through secure views; file URLs cannot be shared that way.
"Give an application a link that works for 60 seconds without logging in" is a pre-signed URL. "Let each user only open files through a governed view or procedure" is a scoped URL. "A permanent reference stored in a table" is a file URL.
Directory tables
A directory table is an implicit layer on a stage that stores file level metadata. It is not a separate object and has no grantable privileges of its own. You turn it on with DIRECTORY = (ENABLE = TRUE) at creation, or ALTER STAGE ... SET DIRECTORY = (ENABLE = TRUE) later.
SELECT relative_path, size, last_modified, file_url
FROM DIRECTORY(@docs)
WHERE relative_path LIKE '%.pdf';
It returns one row per file with the relative path, size, last modified time, checksums and the file URL.
| Refresh method | How |
|---|---|
| Manual | ALTER STAGE docs REFRESH; registers file changes |
| Automatic | AUTO_REFRESH = TRUE on a stage in cloud storage, driven by event notifications that Snowpipe manages |
Directory table metadata is not live until refreshed. Reading file URLs needs READ on an internal stage, or USAGE on an external stage. A stream on a directory table (CREATE STREAM s ON STAGE docs) shows which files are new, so a task can process each file once.
Processing files
| Approach | Use it for |
|---|---|
| Python or Java UDF, UDTF or procedure, including Snowpark | Read a file with SnowflakeFile and extract text, metadata or model scores |
| External function | Send files or URLs to a service such as Amazon Textract or Azure Computer Vision |
FILE data type | Build a file object with TO_FILE or TRY_TO_FILE that some built in functions accept in place of a stage and path |
| Snowflake Cortex features | AI based parsing and analysis of documents, covered on later pages of this chapter |
The usual pattern passes a scoped URL into a function.
CREATE FUNCTION file_bytes(url STRING)
RETURNS NUMBER
LANGUAGE PYTHON
RUNTIME_VERSION = '3.11'
PACKAGES = ('snowflake-snowpark-python')
HANDLER = 'run'
AS $$
from snowflake.snowpark.files import SnowflakeFile
def run(url):
with SnowflakeFile.open(url, 'rb') as f:
return len(f.read())
$$;
SELECT relative_path, file_bytes(BUILD_SCOPED_FILE_URL(@docs, relative_path))
FROM DIRECTORY(@docs);
SnowflakeFile.open expects a scoped URL by default, so the handler never gets broader access than the caller.
The REST APIs
Snowflake offers REST endpoints for files and for control.
- Files through a URL. A scoped URL or file URL is sent in an HTTP
GETrequest to the file support REST endpoint, with an authorization token, to download the file from outside Snowflake. File URLs need a role with privileges on the stage. - Snowflake REST APIs. Resource endpoints under
/api/v2manage objects. Stage endpoints list files (likeLIST @stage) and can return a pre-signed URL for uploading or downloading a file. - SQL API.
POST /api/v2/statementssubmits SQL, andGET /api/v2/statements/<handle>returns the status and result. - Authentication. Key pair (a signed JWT), OAuth or a programmatic access token.
A pre-signed URL needs no authentication. Anyone who gets the link can read the file until it expires, so never log it, and keep the expiry short.
Common mistakes
Creating the stage with default encryption and expecting URLs to work. Internal stages need SNOWFLAKE_SSE for scoped, file and pre-signed URLs.
Querying a directory table that was never refreshed. New files do not appear until ALTER STAGE ... REFRESH or automatic refresh.
Sharing a file URL. Only scoped and pre-signed URLs can be shared, through secure views.
What to remember for the exam
- Unstructured data stays in stages; access it through URLs, directory tables and code.
- Scoped URL: caller only, up to 24 hours. File URL: permanent, privilege based. Pre-signed URL: anyone, expires by argument.
- Internal stages need
ENCRYPTION = (TYPE = 'SNOWFLAKE_SSE')for URL access; it cannot be changed later. - Directory tables hold file metadata, are queried with
DIRECTORY(@stage), and need manual or automatic refresh. SnowflakeFilereads files in Python and Java handlers, usually from a scoped URL.- Files are fetched over REST with a GET and an authorization token; the SQL API runs statements with
/api/v2/statements.
Check yourself
Which URL type expires after the query result cache period and works only for its creator?
The scoped URL.
How do you make new files appear in a directory table?
Run ALTER STAGE ... REFRESH, or enable AUTO_REFRESH on a cloud storage stage.
Why does a URL function fail on an internal stage with default encryption?
The stage needs ENCRYPTION = (TYPE = 'SNOWFLAKE_SSE') for scoped, file and pre-signed URLs.
Topic 7 of 14 in Data Transformation
Test yourself on Data Transformation
The full Snowflake DEA-C02 question bank has 709 questions, with timed practice exams and an explanation for every answer.
This study guide is independent and is not affiliated with, endorsed by, or sponsored by Snowflake. Vendor names, certification names and exam codes are trademarks of their respective owners and are used for identification only.