liveBook · Manning

Metadata column

As explained in Snowflake Data Engineering

metadata$filename is a Snowflake staged-file metadata column containing the name of the file from which a record was ingested.

metadata$filename is a Snowflake staged-file metadata column that records the name of the file from which each record was ingested. With external stages, its value includes the file name and path. section 2.3 section 3.4.1

Persist the column as source_file_name when loading staging tables or extracting data from external-stage files. It becomes increasingly useful when a stage contains multiple files and records require ingestion provenance. section 2.3.1 section 2.3

Use metadata columns alongside file-format and ingestion controls:

  • Select metadata$filename with staged columns inside COPY INTO to populate a source_file_name table column. section 2.3.1
  • Use metadata$file_row_number when retaining each record’s position within its source file matters. section 2.3
  • Expose metadata$filename as a regular external-table column while parsing VALUE fields into typed columns. section 3.4.1

Capture filenames during a staged-file load

A staging table can retain file provenance with a SELECT inside COPY INTO: select $1, $2, $3, $4, $5, metadata$filename, current_timestamp() from @ORDERS_STAGE. The resulting rows include values such as Orders_2023-07-07.csv alongside the load timestamp. section 2.3.1

Listing 2.1 COPY command to load data from the staged file into Snowflake
use database BAKERY_DB;
use schema ORDERS;
copy into ORDERS_STG
from (
  select $1, $2, $3, $4, $5, metadata$filename, current_timestamp() 
  from @ORDERS_STAGE
)
file_format = (type = CSV, skip_header = 1)
on_error = abort_statement
purge = true;

Inspect the complete COPY INTO pattern, including CSV header handling, error behavior, filename capture, timestamping, and staged-file purging. section 2.3.1

How Snowflake Data Engineering covers it

How deep it goes
Defines the filename value and demonstrates persistence through COPY INTO and external-table column definitions, without deeper metadata internals.
Examples
Uses bakery orders loaded from CSV and JSON files, including source_file_name columns in staging and external-table definitions.
What it assumes
Assumes basic staged-file queries and SQL column selection while teaching filename capture through CSV, JSON, and external-stage examples.
What you can do after
Build it from scratch by capturing each ingested record’s source filename in Snowflake SQL loading and external-table definitions.
How it connects
Connects staged files with COPY INTO, file formats, external tables, external stages, and metadata$file_row_number.
Cover of Snowflake Data Engineering
liveBookmanning.com
Read Snowflake Data Engineering on liveBook Get the book at manning.com

Related concepts

This explainer was generated by Manning's knowledge graph from the text of Snowflake Data Engineering. The section links point to the parts of the book it draws on.