Course contents

SnowPro Core COF-C03 Study GuidePart 4: Data loading and storageDirectory Tables

Directory Tables

Directory Tables

In shortA directory table is a catalog of the files in a stage: one row per file, with path, size, dates and a URL. It describes the files and never reads what's inside.

The problem

Safia's team scans supplier invoices as PDFs into a stage. They don't want rows parsed from the PDFs yet. They want a list of files they can query, join with other tables and link to.

How it works

Same stage, two different questions. In the middle, one stage holds part-0.parquet with 2 records, part-1.parquet with 1 record and inv_01.pdf. On the left, an external table reads the records inside the two Parquet files and returns one row per record: S-1 and S-2 from part-0, S-3 from part-1. On the right, a directory table lists the files themselves and returns one row per file with path and size, for any file type, the PDF included. Values are illustrative.ONE STAGEFILEpart-0.parquet2 recordsFILEpart-1.parquet1 recordFILEinv_01.pdfa documentExternal tableone row per recordDIRECTORY(@stage)one row per fileSHIPMENT_IDWEIGHT_KGRELATIVE_PATHSIZES-112S-28S-330part-0.parquet2.1 MBpart-1.parquet1.4 MBinv_01.pdf0.3 MBthe PDF has no records
External tableone row per record
SHIPMENT_IDWEIGHT_KG
S-112
S-28
S-330
the PDF has no records

ONE STAGE

FILEpart-0.parquet2 records

FILEpart-1.parquet1 record

FILEinv_01.pdfa document

DIRECTORY(@stage)one row per file
RELATIVE_PATHSIZE
part-0.parquet2.1 MB
part-1.parquet1.4 MB
inv_01.pdf0.3 MB
Figure 4.14 Same stage, two different questions. An external table answers "what records are inside these data files?" with one row per record. A directory table answers "which files are in this stage?" with one row per file, and works for any file type, PDFs included. Values are illustrative.

Enable it with DIRECTORY = (ENABLE = TRUE) on the stage. It shows what it knew at its last refresh. On an external stage, DIRECTORY = (ENABLE = TRUE AUTO_REFRESH = TRUE) keeps it in sync through the cloud's event notifications. On an internal stage, automatic refresh is a preview feature on AWS-hosted accounts; elsewhere, schedule ALTER STAGE … REFRESH. Two Information Schema table functions show the refresh history: STAGE_DIRECTORY_FILE_REGISTRATION_HISTORY (refreshes and their errors) and AUTO_REFRESH_REGISTRATION_HISTORY (files registered by automatic refresh and the credits charged).

Example: the invoice catalog

CREATE STAGE hr_db.docs.invoice_stage
  DIRECTORY = (ENABLE = TRUE)
  ENCRYPTION = (TYPE = 'SNOWFLAKE_SSE');      -- server-side encryption: required for presigned URLs

ALTER STAGE hr_db.docs.invoice_stage REFRESH;  -- sync the catalog with the stage

SELECT relative_path, size, last_modified,
       GET_PRESIGNED_URL(@hr_db.docs.invoice_stage, relative_path, 3600) AS link_1h
FROM DIRECTORY(@hr_db.docs.invoice_stage)
WHERE last_modified > DATEADD('day', -7, CURRENT_TIMESTAMP());

Expected resultOne row per invoice uploaded in the last week, with a link anyone can open for one hour. Put together with AI_EXTRACT (Part 7), the app can show the vendor and amount next to each link, and no binary was ever loaded into a table.

External tableDirectory table
DescribesRecords inside structured or semi-structured filesThe files themselves
One row perRecordFile
Typical useQuery a data lake in placeCatalog unstructured files (PDF, images), generate URLs, feed AI extraction
ObjectIts own schema objectA property of a stage, queried with DIRECTORY(@stage)
StagesExternal stages onlyInternal and external stages
RefreshEvent notifications or manual REFRESHEvent notifications (AUTO_REFRESH) or ALTER STAGE … REFRESH

File URL functions

FunctionWho can use the URLHow long
GET_PRESIGNED_URLAnyone holding the link, with no Snowflake login1 hour by default and up to 7 days, except that 1 hour is the maximum for a stage that reaches S3 through an IAM role
BUILD_SCOPED_FILE_URLOnly the user who generated it, through SnowflakeAbout 24 hours, the time Snowflake keeps a query result
BUILD_STAGE_FILE_URLAny authenticated user whose role has privileges on the stagePermanent
GET_STAGE_LOCATIONNot a file URL: returns the stage's base locationn/a
Exam takeaway
  • Directory table = file catalog of a stage, enabled with DIRECTORY = (ENABLE = TRUE).
  • It lists files and URLs; it never parses contents.
  • Presigned = anyone, until expiry · scoped = only the generating user, about 24 h · file URL = permanent, needs privileges.