ARA-C01 Question 381
Single answerMetadata functionsA data architect is designing an ingestion pattern for a Snowflake table named RAW_EVENTS. Source files are staged in an external stage and loaded with COPY INTO. The business requires row-level lineage so analysts can identify which source file each record came from and the row number within that file. The solution should avoid changing the source files and should be implemented during the load process. Which approach best meets these requirements?
- A
Use COPY INTO RAW_EVENTS from a SELECT on the stage that includes METADATA$FILENAME and METADATA$FILE_ROW_NUMBER as additional columns mapped into the target table.
- B
Query INFORMATION_SCHEMA.LOAD_HISTORY after each COPY operation and join the results back to RAW_EVENTS to derive the source file and row number for every loaded record.
- C
Enable change tracking on RAW_EVENTS and use CHANGES queries to identify the file name and row number associated with each inserted row.
- D
Use GET_DDL on RAW_EVENTS after loading to retrieve the source file path and row-level position for each inserted record.
Show answer and explanation
Correct answer: A
Explanation
The best solution is to capture staged-file metadata columns during the load itself. Snowflake supports querying staged files and exposing virtual metadata columns such as METADATA$FILENAME and METADATA$FILE_ROW_NUMBER, which can be selected alongside the file's data and inserted into the target table. This is the recommended approach when row-level lineage from staged files must be retained for auditability or troubleshooting. By contrast, load history mechanisms are useful for operational auditing at the file level, not per-row lineage. Change tracking and GET_DDL serve unrelated purposes. Relevant Snowflake documentation includes the sections on querying staged files and metadata columns, as well as COPY INTO patterns that load from a SELECT statement over stage data.
- A. Correct.
Correct. Snowflake exposes virtual metadata columns for staged file queries, including METADATA$FILENAME and METADATA$FILE_ROW_NUMBER. A common pattern is to load from a staged SELECT, for example selecting business columns plus these metadata columns into target columns in RAW_EVENTS. This captures row-level lineage at load time without modifying source files. This is the most direct and practical design for preserving file-level and row-level provenance during COPY-based ingestion.
- B. Incorrect.
Incorrect. INFORMATION_SCHEMA.LOAD_HISTORY and related load history views/functions provide file-level load audit information such as which files were loaded and when, but they do not provide a row-by-row mapping between each target row and its original row number in the source file. This option reflects a common misconception that operational load history can replace row-level metadata capture.
- C. Incorrect.
Incorrect. Change tracking supports identifying changed rows for downstream consumption patterns such as streams or CHANGES clauses, but it does not capture staged file metadata like source filename or file row number for inserted rows. It tracks table changes, not external file lineage details.
- D. Incorrect.
Incorrect. GET_DDL returns the DDL used to create an object, such as a table, view, or stage definition. It does not expose runtime load metadata or row-level source information. This distractor confuses object metadata retrieval with staged-file metadata functions used during data loading.