File Formats & Information Schema
Snowflake · Databases, Schemas & Objects
Theory
A file format object stores parsing rules for loading and unloading: TYPE (CSV, JSON, Parquet and others) plus options like FIELD_DELIMITER, SKIP_HEADER, FIELD_OPTIONALLY_ENCLOSED_BY, NULL_IF and, for JSON, STRIP_OUTER_ARRAY. Stages and COPY statements refer to it by name, so the rules are defined once.
For metadata, every database has an INFORMATION_SCHEMA with views such as TABLES and COLUMNS and table functions such as QUERY_HISTORY. It is current but limited to that database, objects your role can see, and a short history. The SNOWFLAKE.ACCOUNT_USAGE schema covers the whole account, includes dropped objects and keeps about a year of history, but its views lag by up to a few hours.
Why we use it
Shared file formats prevent every load from redefining delimiters and null rules slightly differently. Metadata views let you answer questions like which tables exist, who ran what and how much was used with ordinary SQL.
When to use it
Create named file formats per source type and reuse them in stages. Use INFORMATION_SCHEMA for current, database-scoped questions; use ACCOUNT_USAGE for account-wide audits, cost analysis and longer history.
Defining a File Format
CREATE FILE FORMAT captures delimiter, header row, encoding, and compression settings once, so every COPY INTO or stage definition that references it stays consistent without repeating the same options.
Information Schema
Every database automatically includes an INFORMATION_SCHEMA with views like TABLES and COLUMNS, letting you query your own schema structure with SQL instead of clicking through the UI.
Account Usage Views
SNOWFLAKE.ACCOUNT_USAGE holds account-wide historical data — query history, login history, warehouse metering — with slightly delayed but much longer retention than Information Schema's real-time but session-scoped views.
How it works
- Need metadata about objects or queries
- Need account-wide data, dropped objects or long history? Yes: Query SNOWFLAKE.ACCOUNT_USAGE (some lag). No: Query the database's INFORMATION_SCHEMA.
- Filter by object name, user or time
- Use the result for audits, docs or monitoring
Example
CREATE OR REPLACE FILE FORMAT company_dw.raw.csv_standard
TYPE = 'CSV' FIELD_DELIMITER = ',' SKIP_HEADER = 1
FIELD_OPTIONALLY_ENCLOSED_BY = '"' NULL_IF = ('NULL','null','') COMPRESSION = 'AUTO';
CREATE OR REPLACE FILE FORMAT company_dw.raw.json_standard
TYPE = 'JSON' STRIP_OUTER_ARRAY = TRUE;
SELECT TABLE_NAME, ROW_COUNT, BYTES FROM company_dw.INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA = 'CURATED' ORDER BY BYTES DESC;Key points
- File formats centralise parsing rules for reuse across loads
- STRIP_OUTER_ARRAY loads each element of a top-level JSON array as a row
- NULL_IF lists strings that should load as NULL
- INFORMATION_SCHEMA is real-time but scoped to what your role can see
- ACCOUNT_USAGE has long history but a replication delay