SnowPro Associate: Platform Question 295
Single answer● Directory tablesA data engineering team stores product images in an internal named stage and needs a reliable way to query file metadata such as filenames, sizes, checksums, and last modified timestamps from SQL. The team also wants the metadata to stay reasonably current as files are added or removed over time, without parsing cloud storage listings manually. Which approach should the engineer use?
- A
Enable a directory table on the stage and query the stage's directory table metadata, refreshing it as needed to keep file information current.
- B
Create a standard table and use COPY INTO to load image files into rows so Snowflake can automatically expose filename and checksum metadata.
- C
Query INFORMATION_SCHEMA.STAGES because it stores one row per file in each stage, including file size and last modified timestamp.
- D
Create an external table on the internal stage because external tables are the only Snowflake objects that can expose file-level metadata for staged files.
Show answer and explanation
Correct answer: A
Explanation
The best solution is to enable and use a directory table on the stage. Directory tables provide a SQL-accessible view of staged files and their metadata, which is exactly what the scenario requires. This is more appropriate than loading files into a table with COPY INTO, which is for data ingestion rather than file inventory management. It is also more appropriate than relying on INFORMATION_SCHEMA.STAGES, which describes stage objects rather than individual files. Finally, external tables are a different feature used to query data files in external storage and are not the general-purpose answer for file metadata inventory in an internal stage. In Snowflake documentation, directory tables are specifically described as a way to query a stage directory and retrieve file-level metadata, with refresh required to synchronize changes in staged files.
- A. Correct.
Correct. Directory tables are designed to expose metadata about files in a stage, including information such as relative path, size, last modified time, and file URL. They can be enabled on stages and queried from SQL. To keep the metadata current, the directory table metadata must be refreshed, either manually or through supported automation patterns depending on the stage setup.
- B. Incorrect.
Incorrect. COPY INTO loads data from files into tables; it does not create an ongoing metadata catalog of staged files for general querying. While metadata columns can be used during load operations, COPY INTO is not the mechanism for maintaining a queryable inventory of files in a stage over time.
- C. Incorrect.
Incorrect. INFORMATION_SCHEMA.STAGES contains metadata about stages themselves, not one row per file stored in a stage. A common misconception is that account metadata views provide file inventories, but file-level stage contents are not exposed there in the way described.
- D. Incorrect.
Incorrect. External tables are used primarily to query data in external storage as if it were a table, typically based on file contents and partitions. They are not the only way to expose file-level metadata, and they are not applicable to internal stages in the way described here. Directory tables are the appropriate feature for file metadata on stages.