Metadata describes data and its operational state. In Snowflake pipelines, it improves discoverability, prevents duplicate staged-file loads, records incremental changes, and supports query pruning. section 1.2.3 section 3.1.5 section 8.3.1
Common operational uses include:
- Expose table and column comments, refresh timestamps, ingested-record counts, and data-quality metrics to downstream consumers. section 1.2.3
- Track staged-file names, sizes, entity tags, row counts, load times, and errors so COPY avoids loading the same file twice. section 3.1.5
- Inspect LOAD_HISTORY to verify whether files loaded successfully, including the file name, target table, load time, and status. section 3.1.5
- Represent changes through stream metadata columns: METADATA$ACTION, METADATA$ISUPDATE, and METADATA$ROW_ID. section 12.3
- Use micro-partition value ranges to exclude irrelevant partitions and columns during query execution. section 8.3.1
Verify staged-file loads
Query INFORMATION_SCHEMA.LOAD_HISTORY, filter by schema and table, and order by LAST_LOAD_TIME to inspect recent file-loading outcomes. section 3.1.5
select * from information_schema.load_history where schema_name = 'EXTERNAL_ORDERS' and table_name = 'ORDERS_BISTRO_STG' order by last_load_time desc;
Browse the executable LOAD_HISTORY query used to inspect staged-file loading records. section 3.1.5
