Skip to main content

πŸ“ File Format Management in Snowflake

In Snowflake, file formats define how to interpret the structure and content of files that are staged for loading or unloading. Whether you’re working with CSVs, JSON, Parquet, or other formats, properly managing file formats ensures data is accurately read and written during ingest and export operations.


βœ… Why Use Named File Formats?​

Using named file formats allows you to:

  • Reuse consistent format definitions across multiple loads
  • Simplify COPY INTO and UNLOAD operations
  • Reduce error and redundancy in complex pipelines

🧱 Supported File Types in Snowflake​

FormatUse Case
CSVTabular text-based data (spreadsheets)
JSONSemi-structured data with nesting
AVRORow-based storage format (data pipelines)
PARQUETColumnar storage format (analytics)
ORCOptimized columnar format (Hive, Spark)
XMLHierarchical data (less common)

πŸ—οΈ Syntax: Creating a File Format​

CREATE OR REPLACE FILE FORMAT my_csv_format
TYPE = 'CSV'
FIELD_DELIMITER = ','
SKIP_HEADER = 1
FIELD_OPTIONALLY_ENCLOSED_BY = '"'
NULL_IF = ('NULL', 'null');

You can define:

  • Type: CSV, JSON, PARQUET, etc.
  • Delimiters (for CSV)
  • Header Skipping
  • Compression
  • Null handling

πŸ” Viewing and Managing File Formats​

  • View all file formats:
SHOW FILE FORMATS;
  • View format details:
DESC FILE FORMAT my_csv_format;
  • Drop file format:
DROP FILE FORMAT IF EXISTS my_csv_format;

πŸ“„ CSV Format​

CSV (Comma-Separated Values) is the most common plain-text tabular format used to represent data.

βœ… Key Features:​

  • Plain text, human-readable
  • Rows and columns separated by delimiters (usually commas)
  • Easy to create and edit manually
  • Supported by almost all tools and platforms

πŸ“¦ Where is CSV Typically Used?​

Use CaseWhy CSV?
Spreadsheets and simple exportsUniversally supported format
Lightweight data interchangeEasy to read and parse
Legacy system integrationOften required by older systems
Initial data loads or small dataSimple and quick to produce

πŸ“ What Does CSV Data Look Like?​

id,name,email,age
1,Alice,alice@example.com,30
2,Bob,bob@example.com,25
3,Charlie,charlie@example.com,28

🧊 Loading CSV into Snowflake​

  1. Stage the CSV file (e.g., S3 or Snowflake internal stage)
  2. Define CSV file format:
CREATE OR REPLACE FILE FORMAT my_csv_format
TYPE = 'CSV'
FIELD_DELIMITER = ','
SKIP_HEADER = 1
FIELD_OPTIONALLY_ENCLOSED_BY = '"'
NULL_IF = ('NULL', 'null')
COMPRESSION = 'AUTO';
  1. Load data:
COPY INTO my_table
FROM @my_stage/csv_files/
FILE_FORMAT = (FORMAT_NAME = 'my_csv_format');

πŸ’‘ Summary​

FeatureCSV
Format TypePlain text (row-based)
Common Use CasesSpreadsheets, exports
Supported in SF?βœ… Yes
Human-readable?βœ… Yes
Snowflake SupportNative CSV file format

πŸ“„ JSON Format​

JSON (JavaScript Object Notation) is a widely used semi-structured, human-readable format often used in web APIs and logs.

βœ… Key Features:​

  • Supports nested and hierarchical data
  • Human-readable and easy to parse
  • Good for complex objects or arrays
  • Popular in web and NoSQL ecosystems

πŸ“¦ Where is JSON Typically Used?​

Use CaseWhy JSON?
Web API data exchangeSupports nested objects
Logs and event dataEasy to extend and parse
NoSQL document storageFlexible schemas
Semi-structured datasetsCan store complex data

πŸ“ What Does JSON Data Look Like?​

[
{
"id": 1,
"name": "Alice",
"email": "alice@example.com",
"age": 30,
"preferences": {
"newsletter": true,
"theme": "dark"
}
},
{
"id": 2,
"name": "Bob",
"email": "bob@example.com",
"age": 25,
"loyalty": {
"tier": "gold",
"points": 1500
}
},
{
"id": 3,
"name": "Charlie",
"email": null,
"tags": ["vip", "early_access"]
}
]

🧊 Loading JSON into Snowflake​

CREATE OR REPLACE FILE FORMAT my_json_format
TYPE = 'JSON'
COMPRESSION = 'AUTO'
STRIP_OUTER_ARRAY = TRUE
IGNORE_UTF8_ERRORS = TRUE;

πŸ’‘ Summary​

FeatureJSON
Format TypeText (semi-structured)
Common Use CasesAPIs, logs, NoSQL
Supported in SF?βœ… Yes
Human-readable?βœ… Yes
Snowflake SupportNative JSON file format

πŸ“Š Parquet File Format​

Parquet is a binary columnar storage format optimized for big data analytics.

βœ… Key Features:​

  • Columnar storage for efficient analytics
  • Highly compressed and splittable
  • Supports complex nested data structures
  • Widely used in Hadoop, Spark, Snowflake, BigQuery

πŸ“¦ Where is Parquet Typically Used?​

Use CaseWhy Parquet?
Big data analyticsEfficient column pruning
Data lakes on cloud (S3, ADLS)Splittable, compressed format
Spark, Hive, Snowflake workloadsSupports nested structures
Machine learning pipelinesFast reads, smaller storage

πŸ“ What Does Parquet Data Look Like?​

Since Parquet is binary, here’s a logical view:

idnameemailactivetags
1Alicealice@example.comtrue["admin", "beta"]
2Bobbob@example.comfalse["user"]
3Charliecharlie@demo.comtrue["editor", "review"]


🧊 Loading Parquet into Snowflake​

CREATE OR REPLACE FILE FORMAT my_parquet_format
TYPE = 'PARQUET'
COMPRESSION = 'SNAPPY';

πŸ’‘ Summary​

FeatureParquet
Format TypeBinary columnar
Common Use CasesAnalytics, big data
Supported in SF?βœ… Yes
Human-readable?❌ No
Snowflake SupportNative Parquet support

πŸ§ͺ Avro Format​

Avro is a row-based binary format commonly used in streaming and pipeline architectures.

βœ… Key Features:​

  • Row-oriented storage (better for writes)
  • Supports schema evolution and metadata
  • Popular in Kafka and streaming data
  • Compact and efficient serialization

πŸ“¦ Where is Avro Typically Used?​

Use CaseWhy Avro?
Kafka data pipelinesSchema support and compactness
Stream processing systemsEfficient serialization
Data interchange in pipelinesSchema evolution capabilities

🧊 Loading Avro into Snowflake​

CREATE OR REPLACE FILE FORMAT my_avro_format
TYPE = 'AVRO'
COMPRESSION = 'AUTO';

πŸ’‘ Summary​

FeatureAvro
Format TypeBinary (row-based)
Common Use CasesStreaming, pipelines
Supported in SF?βœ… Yes
Human-readable?❌ No
Snowflake SupportNative Avro support

🧷 XML Format​

XML is a hierarchical, text-based format used in older web services and enterprise systems.

βœ… Key Features:​

  • Supports nested and hierarchical data
  • Verbose and less compact than JSON
  • Often used in SOAP and legacy systems

πŸ“¦ Where is XML Typically Used?​

Use CaseWhy XML?
Web services (SOAP)Industry standard for messaging
Enterprise data exchangeWell-structured hierarchical data
Legacy integrationsWidely supported by older systems

πŸ“ What Does XML Data Look Like?​

<users>
<user>
<id>1</id>
<name>Alice</name>
<email>alice@example.com</email>
<age>30</age>
</user>
<user>
<id>2</id>
<name>Bob</name>
<email>bob@example.com</email>
<age>25</age>
</user>
<user>
<id>3</id>
<name>Charlie</name>
<email>charlie@example.com</email>
<age>28</age>
</user>
</users>

🧊 Loading XML into Snowflake​

CREATE OR REPLACE FILE FORMAT my_xml_format
TYPE = 'XML'
COMPRESSION = 'AUTO'
PRESERVE_SPACE = TRUE
STRIP_OUTER_ELEMENT = FALSE;

πŸ’‘ Summary​

FeatureXML
Format TypeText (hierarchical)
Common Use CasesWeb services, legacy
Supported in SF?βœ… Yes
Human-readable?βœ… Yes
Snowflake SupportNative XML support

🧠 Best Practices​

  • Use named file formats for pipelines and automation.
  • Keep formats separate per file type to avoid confusion.
  • Use COMPRESSION = AUTO unless you have specific needs.
  • For JSON/XML, set options like STRIP_OUTER_ARRAY or PRESERVE_SPACE thoughtfully.

πŸ“š Summary Table​

File FormatSupports Semi-Structured?Auto Schema?Typical Use
CSV❌❌Tabular exports from Excel, DBs
JSONβœ…βŒLogs, APIs, NoSQL exports
PARQUETβœ…βœ…Big Data, Analytics pipelines
AVROβœ…βœ…Stream or Kafka-like ingestion
ORCβœ…βœ…Hive/Spark columnar data
XMLβœ…βŒWeb service responses

πŸ’‘ A well-managed file format is essential for fast, accurate, and repeatable data loading in Snowflake.