COF-C03 Question 220
Single answerDirectory tablesA data engineering team stores daily CSV files in a Snowflake stage and wants downstream SQL processes to read file metadata such as file names, relative paths, checksums, and last modified timestamps without manually maintaining a file manifest table. The team also wants metadata to stay current as files are added or removed from the stage. Which approach should they use?
- A
Create a directory table on the stage and refresh it so Snowflake can expose the staged file metadata as queryable rows.
- B
Create a materialized view on the stage location so Snowflake automatically tracks file-level metadata changes.
- C
Use a stream on the stage to capture file additions and deletions directly from the internal or external stage.
- D
Query INFORMATION_SCHEMA.STAGES because it stores one row per file in every stage with path and checksum details.
Show answer and explanation
Correct answer: A
Explanation
Directory tables are the Snowflake feature used to query metadata for files stored in internal and external stages. They are especially useful when downstream processes need a current list of staged files and related metadata without building a custom manifest table. In practice, teams create a stage with a directory table enabled or alter the stage to enable a directory table, then refresh the directory table so file metadata is synchronized with the stage contents. This supports use cases such as dynamic file discovery, validation workflows, and integration with unstructured or staged data pipelines. By contrast, streams do not track file objects in stages, materialized views are not defined on stages, and INFORMATION_SCHEMA.STAGES describes stage objects rather than individual files. This aligns with Snowflake documentation on directory tables and stage metadata management.
- A. Correct.
Correct. A directory table is designed to provide a catalog of files in an internal or external stage. It exposes file metadata such as relative path, size, last modified timestamp, and file URL as queryable rows. To keep the metadata current, the directory table must be refreshed, either manually or through automated mechanisms depending on the stage configuration. This is the intended Snowflake feature for avoiding manual manifest tables when tracking files in stages.
- B. Incorrect.
Incorrect. A materialized view cannot be created directly on a stage location to track staged files. Materialized views are created on base tables or certain views, not on staged storage locations. This option reflects a common misconception that any queryable metadata source in Snowflake can be materialized in the same way as table data.
- C. Incorrect.
Incorrect. Streams track row-level changes in supported Snowflake objects such as tables and some views, but not staged files directly. A stream does not monitor file additions or deletions in an internal or external stage. Candidates might choose this because streams are commonly used for change tracking, but directory tables are the appropriate feature for stage file metadata.
- D. Incorrect.
Incorrect. INFORMATION_SCHEMA.STAGES contains metadata about stages themselves, such as stage definitions and properties, not one row per file stored in each stage. It does not serve as a file manifest and does not provide the per-file metadata required in the scenario.