Directory tables¶

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

What is a directory table?¶

A directory table is an implicit object layered on a stage (not a separate database object) and is conceptually similar to an external table because it stores file-level metadata about the data files in the stage. A directory table has no grantable privileges of its own.

Both external (external cloud storage) and internal (Snowflake) stages support directory tables. You can add a directory table to a stage when you create a stage (using CREATE STAGE) or later (using ALTER STAGE).

In particular, you can use a directory table to accomplish the following unstructured data tasks:

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.

Next Topics: