Snowflake Directory Tables: Managing Stage File Metadata with SQL

Directory Tables are a Snowflake feature that lets you query file metadata (path, size, last-modified time, checksum, and more) on stages using SQL. They integrate data lake file management into Snowflake SQL operations, enabling file listing, diff detection, and Stream-driven file arrival event processing.

Enabling Directory Tables

-- Enable Directory Tables on an existing stage
ALTER STAGE raw_data_stage SET DIRECTORY = (ENABLE = TRUE);

-- Enable when creating a new stage (External Stage / S3 example)
CREATE OR REPLACE STAGE landing_stage
  URL = 's3://my-bucket/landing/'
  STORAGE_INTEGRATION = s3_integration
  DIRECTORY = (ENABLE = TRUE);

-- Enable auto-refresh at the same time
CREATE OR REPLACE STAGE landing_stage
  URL = 's3://my-bucket/landing/'
  STORAGE_INTEGRATION = s3_integration
  DIRECTORY = (
    ENABLE = TRUE
    AUTO_REFRESH = TRUE
  );

-- Internal Stage example
CREATE OR REPLACE STAGE internal_landing
  DIRECTORY = (ENABLE = TRUE);

Directory Tables Metadata Columns

Querying Directory Tables

-- List all files in the stage
SELECT * FROM DIRECTORY(@landing_stage);

-- Filter by file extension
SELECT relative_path, size, last_modified
FROM DIRECTORY(@landing_stage)
WHERE relative_path LIKE '%.parquet';

-- Files added in the last 24 hours
SELECT relative_path, size, last_modified
FROM DIRECTORY(@landing_stage)
WHERE last_modified >= DATEADD(HOUR, -24, CURRENT_TIMESTAMP());

-- Aggregate file count and size
SELECT
  SPLIT_PART(relative_path, '.', -1) AS file_extension,
  COUNT(*) AS file_count,
  SUM(size) / (1024 * 1024) AS total_size_mb
FROM DIRECTORY(@landing_stage)
GROUP BY file_extension
ORDER BY total_size_mb DESC;

Manual Metadata Refresh

-- Refresh metadata for the whole stage
ALTER STAGE landing_stage REFRESH;

-- Refresh only a specific subpath (efficient for many files)
ALTER STAGE landing_stage REFRESH SUBPATH = '/2026/03/';

When auto-refresh (AUTO_REFRESH = TRUE) is enabled, metadata is updated automatically using cloud storage event notifications as the trigger. Auto-refresh is not available on Internal Stages, so manual refresh is required there.

Cloud-Specific Auto-Refresh Setup

Stream Integration for File Arrival-Driven Processing

-- Create a Stream on a Directory Table
CREATE OR REPLACE STREAM landing_file_stream
  ON STAGE landing_stage;

-- Detect newly arrived files via the Stream
SELECT *
FROM landing_file_stream
WHERE METADATA$ACTION = 'INSERT';

-- Use a Task to auto-process new files
CREATE OR REPLACE TASK process_new_files
  WAREHOUSE = etl_wh
  SCHEDULE = '5 MINUTE'
  WHEN SYSTEM$STREAM_HAS_DATA('landing_file_stream')
AS
  COPY INTO raw_data
  FROM @landing_stage
  FILES = (
    SELECT RELATIVE_PATH
    FROM landing_file_stream
    WHERE METADATA$ACTION = 'INSERT'
  )
  FILE_FORMAT = (TYPE = 'PARQUET');

File URL Functions

-- Scoped URL (temporary URL valid within the session)
SELECT BUILD_SCOPED_FILE_URL(@landing_stage, relative_path)
FROM DIRECTORY(@landing_stage)
WHERE relative_path LIKE '%.pdf';

-- Stage URL (for internal Snowflake references)
SELECT BUILD_STAGE_FILE_URL(@landing_stage, relative_path)
FROM DIRECTORY(@landing_stage);

-- Presigned URL (for external sharing, with expiration)
SELECT GET_PRESIGNED_URL(@landing_stage, relative_path, 3600)
FROM DIRECTORY(@landing_stage)
WHERE relative_path = 'reports/2026-Q1.pdf';

Cost Estimation

-- Estimate auto-refresh cost
SELECT SYSTEM$ESTIMATE_AUTOMATIC_REFRESH_COST('landing_stage');

Best Practices

• Enable auto-refresh only on External Stages: for Internal Stages, a common pattern is to run manual refresh via a scheduled Task
• Use subpath-scoped refresh for efficiency: specify REFRESH SUBPATH per date partition to avoid unnecessary scans
• Combine with Streams to build a CDC pattern: use a Stream on a Directory Table to detect file arrivals and run auto-loading via a Task
• Choosing between LIST @stage and Directory Tables: LIST @stage returns a real-time listing but is not cached. Directory Tables cache metadata for faster queries, at the cost of refresh lag.

Check Your Understanding

Frequently Asked Questions