ARA-C01 Question 380
Single answerMetadata functionsAn architect is designing a Snowflake-based file ingestion process for a finance team. CSV files are landed in an internal stage and then loaded into a raw table. The team needs to support these requirements:
- identify which staged file each row came from,
- capture the row number within the staged file for troubleshooting,
- avoid reloading files that were already processed successfully, and
- keep the solution simple without parsing file names in application code.
Which approach best meets these requirements using Snowflake metadata capabilities?
- A
Use a COPY INTO statement that loads from the stage into the raw table while selecting METADATA$FILENAME and METADATA$FILE_ROW_NUMBER into audit columns, and rely on Snowflake load history to prevent reloading the same files unless explicitly forced.
- B
Use INFORMATION_SCHEMA.TABLES after each load to determine the source file name and row order for each inserted row, because table metadata retains staged file lineage automatically.
- C
Add an AUTOINCREMENT column to the target table for row tracking and query QUERY_HISTORY to infer the staged file name associated with each inserted row.
- D
Use DIRECTORY tables on the internal stage to retrieve per-row file lineage and row number during COPY INTO, because directory tables expose both staged file contents and row-level metadata.
Show answer and explanation
Correct answer: A
Explanation
The best answer is to use Snowflake's staged file metadata columns during COPY or SELECT-from-stage operations. In practice, architects commonly persist METADATA$FILENAME and METADATA$FILE_ROW_NUMBER into raw ingestion tables to support traceability and troubleshooting. This avoids brittle filename parsing in external code and provides deterministic lineage directly from Snowflake.
To prevent duplicate processing, Snowflake maintains load metadata for COPY INTO
operations and generally skips files already loaded successfully, which is a key best practice for idempotent batch ingestion. If a team needs to reload intentionally, they can use supported mechanisms such as FORCE where appropriate, but the default behavior helps satisfy the requirement to avoid accidental reprocessing.Relevant Snowflake documentation areas include metadata columns for staged data (for example, METADATA$FILENAME and METADATA$FILE_ROW_NUMBER) and COPY INTO
- A. Correct.
Correct. Snowflake exposes virtual metadata columns for staged data, including METADATA$FILENAME and METADATA$FILE_ROW_NUMBER, which can be selected during loading to persist file-level and row-level lineage into target columns. This directly satisfies requirements 1 and 2 without custom filename parsing. For requirement 3, Snowflake tracks loaded files for COPY operations and skips files that have already been loaded successfully unless the load is forced or otherwise reprocessed intentionally. This is the standard, simplest design for row-level troubleshooting tied to staged files.
- B. Incorrect.
Incorrect. INFORMATION_SCHEMA.TABLES contains table-level metadata such as names, schemas, and timestamps, not row-level lineage from staged files. Snowflake does not automatically retain source staged file name and row number for each target row unless those values are explicitly loaded from metadata columns. This option reflects a common misconception that system metadata views preserve ingestion lineage at row granularity.
- C. Incorrect.
Incorrect. An AUTOINCREMENT column can generate a surrogate key in the target table, but it does not represent the original row number in the staged file. QUERY_HISTORY may show the COPY statement that ran, but it cannot reliably infer which source file produced each individual row. This approach fails requirements 1 and 2 and adds unnecessary indirect logic for requirement 3.
- D. Incorrect.
Incorrect. Directory tables provide file-level information about staged files, such as file paths and related metadata for discovery and orchestration scenarios, but they do not expose row-level contents or the per-row file row number during COPY INTO. They are useful for listing files, not for capturing row-level lineage in the target table. This option confuses stage file inventory metadata with staged data metadata columns.