Introduction to Directory Tables

This topic introduces key concepts, provides ancillary information, and links to instructions for using directory tables.

What are Directory Tables?

A directory table is not a separate database object; rather, it is an implicit object layered on a stage. Both external (external cloud storage) and internal (i.e. Snowflake) stages support directory tables. A directory table has no grantable privileges of its own. A directory table can be added explicitly to a stage when the stage is created (using CREATE STAGE) or later (using ALTER STAGE).

Conceptually, directory tables are similar to external tables in that they store file-level metadata about the data files in a stage. Query a directory table to retrieve the Snowflake-hosted file URL to each file in the stage. A file URL permits prolonged access to a specified file. That is, the file URL does not expire. The same file URL is returned by calling the BUILD_STAGE_FILE_URL function.

Automatically Refreshing Directory Table Metadata

The metadata for a directory table can be refreshed automatically using the event notification service for your cloud storage service. The refresh operation synchronizes the metadata with the latest set of associated files in the external stage and path, i.e.:

  • New files in the path are added to the table metadata.

  • Changes to files in the path are updated in the table metadata.

  • Files no longer in the path are removed from the table metadata.

For more information, see Automated Directory Table Metadata Refreshes.

Billing for Directory Tables

An overhead to manage event notifications for the automatic refreshing of directory table metadata is included in your charges. This overhead increases in relation to the number of files added in cloud storage for your stages that include directory tables. This overhead charge appears as Snowpipe charges in your billing statement because Snowpipe is used for event notifications for the automatic directory table refreshes. You can estimate this charge by querying the PIPE_USAGE_HISTORY function or examining the Account Usage PIPE_USAGE_HISTORY View.

In addition, a small maintenance overhead is charged for manually refreshing the directory table metadata (using ALTER STAGE … REFRESH). This overhead is charged in accordance with the standard cloud services billing model, like all similar activity in Snowflake.

Users with the ACCOUNTADMIN role, or a role with the global MONITOR USAGE privilege, can query the AUTO_REFRESH_REGISTRATION_HISTORY table function to retrieve the history of data files registered in the metadata of specified objects and the credits billed for these operations.

Access Control Requirements for Directory Tables

The following table summarizes the stage privileges that you need to execute common SQL commands when you work with directory tables.

Operation

Object Type

Privilege Required

Retrieve file URLs from a directory table using a SELECT FROM DIRECTORY statement.

Stage

One of the following, depending on the type of stage:

  • Internal stage: An account role or database role with the READ privilege on the stage.

  • External stage: An account role or database role with the USAGE privilege on the stage.

Upload data using the PUT command.

Stage (internal only)

An account role or database role with the WRITE privilege on the stage.

Remove files using the REMOVE command.

Stage

One of the following, depending on the type of stage:

  • Internal stage: An account role or database role with the WRITE privilege on the stage.

  • External stage: An account role or database role with the USAGE privilege on the stage.

Refresh the metadata using the ALTER STAGE command.

Stage

One of the following, depending on the type of stage:

  • Internal stage: An account role or database role with the WRITE privilege on the stage.

  • External stage: An account role or database role with the USAGE privilege on the stage.

Information Schema

The Snowflake Snowflake Information Schema includes table functions you can query to retrieve information about your directory tables.

Table Functions

AUTO_REFRESH_REGISTRATION_HISTORY

Retrieve the history of data files registered in the metadata of specified objects and the credits billed for these operations.

STAGE_DIRECTORY_FILE_REGISTRATION_HISTORY

Retrieve information about the metadata history for a directory table, including any errors found when refreshing the metadata.