Introduction to Directory Tables

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

In this Topic:

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. Snowflake charges 0.06 credits per 1000 event notifications received.

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.

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.

Back to top