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

  1. Need metadata about objects or queries
  2. Need account-wide data, dropped objects or long history? Yes: Query SNOWFLAKE.ACCOUNT_USAGE (some lag). No: Query the database's INFORMATION_SCHEMA.
  3. Filter by object name, user or time
  4. 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

Snowflake topics

Snowflake Fundamentals

Warehouses & Performance

Staging & SnowSQL

Tables & Data Handling

Cloud Integrations

Storage & Optimization

Security & Governance

Views, Pipelines & Automation

Data Types & SQL Fundamentals

Semi-Structured Data Deep Dive

Advanced Features

Data Sharing & Collaboration

Cost Management & Monitoring

Databases, Schemas & Objects

Performance Deep Dive

Advanced Security & Governance

Stored Procedures & UDFs

Ecosystem & Integrations

SnowPro Certification Prep

T
Tech Town
Log InSign Up Free
Home SQL Python DSA Power BI Snowflake Databricks Spark Gen AI Practice Problems Contest Blog

Mastering Snowflake

The modern data cloud, from architecture to SnowPro certification. Click any topic in the sidebar to open it right here.

{{ sectionCount }}
Sections
{{ topicCount }}
Topics
Free
Forever
Hands-on practice Practice Snowflake SQL Sharpen the SQL you use in Snowflake: 150 query problems in a live editor with instant checking. Start practising →
Section {{ sec.num }}

{{ sec.label }}

Snowflake  /  {{ current.sectionLabel }}  /  {{ current.name }}

{{ current.name }}

Theory

{{ current.def }}

Why we use it

{{ rich.why }}

When to use it

{{ rich.when }}

{{ sec.h }}

{{ sec.body }}

{{ sec.body }}

Diagram

{{ rich.dg.caption }}

{{ tb.title }}
{{ c.v }}
{{ rich.dg.op }}
➜
{{ rich.dg.output.title }}
{{ c.v }}
{{ nd.label }}
{{ nd.sub }}
{{ nd.label }}
{{ nd.sub }}
{{ rich.dg.left.title }}
{{ it }}
{{ rich.dg.right.title }}
{{ it }}
On screen — where to click

{{ rich.ui.title }}

{{ u.text }}
  1. {{ st.n }}{{ st.text }}
{{ rich.ui.title }}
{{ u.text }}
Flowchart — how it works
{{ nd.text }}
{{ nd.text }}
◇ {{ nd.text }}
Yes → {{ nd.yes }}
No → {{ nd.no }}
Syntax
{{ current.syntax }}
Example
{{ current.example }}
Want to run this yourself? Unlike the SQL and Python courses, Snowflake needs a real account — there's no in-browser engine for it. Click Copy above, then paste it into a free Snowflake trial (30 days, no card required) and run it in Snowsight.
Key Points — click to check off as you learn
  • {{ pt.mark }} {{ pt.text }}

Share this topic