π 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.
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β
| Format | Use Case |
|---|
| CSV | Tabular text-based data (spreadsheets) |
| JSON | Semi-structured data with nesting |
| AVRO | Row-based storage format (data pipelines) |
| PARQUET | Columnar storage format (analytics) |
| ORC | Optimized columnar format (Hive, Spark) |
| XML | Hierarchical data (less common) |
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
DESC FILE FORMAT my_csv_format;
DROP FILE FORMAT IF EXISTS my_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 Case | Why CSV? |
|---|
| Spreadsheets and simple exports | Universally supported format |
| Lightweight data interchange | Easy to read and parse |
| Legacy system integration | Often required by older systems |
| Initial data loads or small data | Simple 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β
- Stage the CSV file (e.g., S3 or Snowflake internal stage)
- 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';
- Load data:
COPY INTO my_table
FROM @my_stage/csv_files/
FILE_FORMAT = (FORMAT_NAME = 'my_csv_format');
π‘ Summaryβ
| Feature | CSV |
|---|
| Format Type | Plain text (row-based) |
| Common Use Cases | Spreadsheets, exports |
| Supported in SF? | β
Yes |
| Human-readable? | β
Yes |
| Snowflake Support | Native CSV file 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 Case | Why JSON? |
|---|
| Web API data exchange | Supports nested objects |
| Logs and event data | Easy to extend and parse |
| NoSQL document storage | Flexible schemas |
| Semi-structured datasets | Can 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β
| Feature | JSON |
|---|
| Format Type | Text (semi-structured) |
| Common Use Cases | APIs, logs, NoSQL |
| Supported in SF? | β
Yes |
| Human-readable? | β
Yes |
| Snowflake Support | Native JSON 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 Case | Why Parquet? |
|---|
| Big data analytics | Efficient column pruning |
| Data lakes on cloud (S3, ADLS) | Splittable, compressed format |
| Spark, Hive, Snowflake workloads | Supports nested structures |
| Machine learning pipelines | Fast reads, smaller storage |
π What Does Parquet Data Look Like?β
Since Parquet is binary, hereβs a logical view:
π§ Loading Parquet into Snowflakeβ
CREATE OR REPLACE FILE FORMAT my_parquet_format
TYPE = 'PARQUET'
COMPRESSION = 'SNAPPY';
π‘ Summaryβ
| Feature | Parquet |
|---|
| Format Type | Binary columnar |
| Common Use Cases | Analytics, big data |
| Supported in SF? | β
Yes |
| Human-readable? | β No |
| Snowflake Support | Native Parquet support |
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 Case | Why Avro? |
|---|
| Kafka data pipelines | Schema support and compactness |
| Stream processing systems | Efficient serialization |
| Data interchange in pipelines | Schema evolution capabilities |
π§ Loading Avro into Snowflakeβ
CREATE OR REPLACE FILE FORMAT my_avro_format
TYPE = 'AVRO'
COMPRESSION = 'AUTO';
π‘ Summaryβ
| Feature | Avro |
|---|
| Format Type | Binary (row-based) |
| Common Use Cases | Streaming, pipelines |
| Supported in SF? | β
Yes |
| Human-readable? | β No |
| Snowflake Support | Native Avro support |
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 Case | Why XML? |
|---|
| Web services (SOAP) | Industry standard for messaging |
| Enterprise data exchange | Well-structured hierarchical data |
| Legacy integrations | Widely 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β
| Feature | XML |
|---|
| Format Type | Text (hierarchical) |
| Common Use Cases | Web services, legacy |
| Supported in SF? | β
Yes |
| Human-readable? | β
Yes |
| Snowflake Support | Native 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 Format | Supports 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.