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
In a nutshell
  • 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.

FilesPDFs, images, audioPUT or cloud uploadStagewith directory tablemetadata and URLsSQL querypasses URL toUDF, procedure or functionreads and extractswritesStructured table

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 typeGenerated withLifetimeWho can use it
Scoped URLBUILD_SCOPED_FILE_URL(@stage, 'path')Until the query result cache expires, currently 24 hoursOnly the user who generated it; access is logged in query history
File URLBUILD_STAGE_FILE_URL(@stage, 'path'), or the FILE_URL column of a directory tablePermanentA role with READ on an internal stage or USAGE on an external stage, sending an authorization token
Pre-signed URLGET_PRESIGNED_URL(@stage, 'path', expiration_time)Set by the expiration argument in secondsAnyone 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.

Exam tip

"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 methodHow
ManualALTER STAGE docs REFRESH; registers file changes
AutomaticAUTO_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

ApproachUse it for
Python or Java UDF, UDTF or procedure, including SnowparkRead a file with SnowflakeFile and extract text, metadata or model scores
External functionSend files or URLs to a service such as Amazon Textract or Azure Computer Vision
FILE data typeBuild 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 featuresAI 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.

New filelands in stagerefreshDirectory tablestream shows new rowsTaskruns on the streamscoped URLUDFextracts contentINSERTResults table

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 GET request 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/v2 manage objects. Stage endpoints list files (like LIST @stage) and can return a pre-signed URL for uploading or downloading a file.
  • SQL API. POST /api/v2/statements submits SQL, and GET /api/v2/statements/<handle> returns the status and result.
  • Authentication. Key pair (a signed JWT), OAuth or a programmatic access token.
Watch out

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.
  • SnowflakeFile reads 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.