File formats (CSV, JSON, Parquet, Avro) in Snowflake - Deep Dive

Overview - File formats (CSV, JSON, Parquet, Avro)

What is it?

File formats are ways to organize and store data in files so computers can read and write them easily. CSV, JSON, Parquet, and Avro are popular file formats used to save data in different structures and for different purposes. Each format has its own style and rules for how data is arranged inside the file. These formats help move data between systems and store it efficiently.

Why it matters

Without standard file formats, sharing and storing data would be chaotic and slow. Imagine trying to read a book where every page uses a different language or layout. File formats solve this by creating common rules so computers and people can understand data quickly. This makes data analysis, storage, and transfer faster and more reliable, which is crucial for businesses and apps that depend on data.

Where it fits

Before learning file formats, you should understand basic data concepts like tables, records, and fields. After this, you can learn how to load and query data in Snowflake using these formats, and how to optimize storage and performance with the right format choice.

Mental Model

Core Idea

File formats are like different types of containers designed to hold and organize data efficiently for specific uses and systems.

Think of it like...

Think of file formats like different types of boxes for packing items: a simple cardboard box (CSV) for basic storage, a labeled box with compartments (JSON) for nested items, a sturdy insulated box (Parquet) for keeping things organized and efficient, and a reusable, standardized crate (Avro) for sharing between warehouses.

┌─────────────┐
│   Data      │
├─────────────┤
│ CSV         │  Simple rows, plain text
│ JSON        │  Nested objects, flexible
│ Parquet     │  Columnar, compressed
│ Avro        │  Schema-based, compact
└─────────────┘

Build-Up - 6 Steps

1

Foundation

Understanding CSV Basics

Concept: CSV stores data as plain text with rows and columns separated by commas.

CSV (Comma-Separated Values) files list data in rows, where each row has values separated by commas. Each row represents a record, and each value is a field. It is easy to read and write but does not support complex data like nested objects.

Result

You can open CSV files in text editors or spreadsheets and see simple tables of data.

Understanding CSV shows how data can be stored in the simplest, most universal way, but with limits on complexity.

2

Foundation

Exploring JSON Structure

Concept: JSON stores data as nested objects and arrays, allowing complex data shapes.

JSON (JavaScript Object Notation) uses braces and brackets to organize data into objects (key-value pairs) and arrays (lists). This lets you represent nested data like a person with multiple phone numbers or addresses inside one file.

Result

You can represent complex, hierarchical data clearly and share it between systems that understand JSON.

Knowing JSON helps you handle data that is more than just flat tables, which is common in modern apps.

3

Intermediate

Introducing Parquet for Efficiency

🤔Before reading on: do you think Parquet stores data row-by-row like CSV, or column-by-column? Commit to your answer.

Concept: Parquet stores data column-by-column, which improves compression and query speed for big data.

Parquet is a columnar file format designed for fast reading and efficient storage. Instead of storing data row by row, it stores all values of a column together. This helps when you only need some columns from a large dataset, saving time and space.

Result

Queries on Parquet files run faster and use less storage compared to row-based formats.

Understanding columnar storage reveals why Parquet is preferred for big data analytics and cloud data warehouses.

4

Intermediate

Understanding Avro and Schemas

🤔Before reading on: does Avro store data with or without a schema? Commit to your answer.

Concept: Avro stores data with a schema that describes the data structure, enabling compact and consistent storage.

Avro files include a schema that defines the data types and structure. This schema travels with the data or is shared separately. It allows programs to read data correctly and supports schema evolution, meaning the data structure can change over time without breaking compatibility.

Result

Avro files are compact and reliable for data exchange between systems that agree on the schema.

Knowing schema-based formats like Avro helps manage data changes safely in production systems.

5

Advanced

Choosing Formats in Snowflake

🤔Before reading on: which format do you think Snowflake prefers for fast queries on large datasets? Commit to your answer.

Concept: Snowflake supports all these formats but optimizes performance and storage differently based on format choice.

In Snowflake, CSV and JSON are easy to load but less efficient for large data. Parquet and Avro are preferred for big data because they compress well and speed up queries. Snowflake can automatically parse these formats and convert them internally for fast processing.

Result

Choosing the right format improves query speed, reduces storage costs, and simplifies data pipelines.

Understanding Snowflake's format support guides better decisions for data ingestion and analytics.

6

Expert

Handling Schema Evolution and Compatibility

🤔Before reading on: do you think changing a schema in Avro breaks all old data? Commit to your answer.

Concept: Schema evolution allows data formats like Avro to change structure over time without losing access to old data.

Avro supports adding or removing fields and changing types in a controlled way. Snowflake can handle these changes when loading data, ensuring backward and forward compatibility. This avoids costly data migrations and downtime.

Result

You can update data structures safely while keeping historical data usable.

Knowing schema evolution is key to maintaining long-term data reliability in dynamic environments.

Under the Hood

CSV stores data as plain text lines with commas separating fields, making it simple but unstructured. JSON uses a text-based tree structure with nested objects and arrays, parsed by JSON parsers. Parquet stores data in a columnar binary format with metadata and compression, enabling efficient reads of specific columns. Avro stores data in a compact binary format along with a schema that describes the data layout, allowing programs to decode data correctly and handle schema changes.

Why designed this way?

CSV was designed for simplicity and human readability, making it widely compatible but limited. JSON was created for flexible data exchange in web applications, supporting complex data. Parquet was built for big data analytics to optimize storage and query speed by using columnar storage. Avro was designed for data serialization with schema support to ensure data consistency and evolution across distributed systems.

┌───────────────┐
│   Data Input  │
└──────┬────────┘
       │
┌──────▼───────┐
│   CSV        │  Plain text, rows
├──────────────┤
│   JSON       │  Nested objects
├──────────────┤
│   Parquet    │  Columnar binary
├──────────────┤
│   Avro       │  Binary + schema
└──────────────┘
       │
┌──────▼────────┐
│ Snowflake Load│
└───────────────┘

Myth Busters - 4 Common Misconceptions

Quick: Is CSV a good choice for storing nested data? Commit to yes or no.

Common Belief: CSV can store any kind of data, including nested structures.

Reality: CSV only supports flat, tabular data without nested or hierarchical structures.

Why it matters: Using CSV for nested data leads to data loss or complicated parsing errors.

Quick: Does JSON always use more storage than Parquet? Commit to yes or no.

Common Belief: JSON files are always smaller than Parquet files because they are text-based.

Reality: Parquet files are usually smaller because they compress data column-wise and store it in binary format.

Why it matters: Choosing JSON for large datasets can increase storage costs and slow queries.

Quick: Can Avro files be read without their schema? Commit to yes or no.

Common Belief: Avro files can be read independently without needing the schema.

Reality: Avro requires the schema to decode the data correctly; without it, the data is meaningless.

Why it matters: Losing the schema makes Avro data unusable, causing data access failures.

Quick: Does schema evolution in Avro break old data compatibility? Commit to yes or no.

Common Belief: Changing the schema in Avro always breaks compatibility with old data.

Reality: Avro supports controlled schema evolution that maintains compatibility with old data versions.

Why it matters: Misunderstanding this leads to unnecessary data migrations and downtime.

Expert Zone

1

Parquet's columnar format allows predicate pushdown, meaning queries can skip irrelevant data early, improving speed.

2

Avro's schema evolution supports default values for new fields, enabling smooth transitions without breaking readers.

3

Snowflake internally converts all these formats into its optimized micro-partitions, abstracting format differences during queries.

When NOT to use

Avoid CSV for large or complex datasets due to lack of structure and compression. JSON is not ideal for big data analytics because of size and parsing overhead. Use Avro only when schema management is needed; otherwise, Parquet is preferred for analytics. For streaming data, consider formats like Apache Kafka's native formats instead.

Production Patterns

In production, teams use Parquet for data lakes and analytics workloads in Snowflake. Avro is common in data pipelines where schema evolution and compatibility are critical. JSON is used for semi-structured data ingestion and APIs. CSV is mostly for small, simple data exchanges or legacy systems.

Connections

Database Normalization

Both organize data to reduce redundancy and improve structure.

Understanding how file formats structure data helps grasp why databases normalize tables to keep data clean and efficient.

Compression Algorithms

File formats like Parquet use compression techniques to reduce size.

Knowing compression basics explains why columnar formats save space and speed up data processing.

Linguistics - Grammar and Syntax

Schemas in Avro are like grammar rules defining valid sentence structures.

Recognizing schemas as rules helps understand how data formats enforce structure and meaning.

Common Pitfalls

#1 Trying to store nested data in CSV format.

Wrong approach:

name,age,phones
John,30,"123-4567;987-6543"

Correct approach:

{ "name": "John", "age": 30, "phones": ["123-4567", "987-6543"] }

Root cause:

CSV cannot represent nested lists or objects, so forcing it leads to ambiguous or broken data.

#2 Loading large JSON files directly for analytics without conversion.

Wrong approach:

COPY INTO table FROM @stage/file.json FILE_FORMAT = (TYPE = 'JSON')

Correct approach:

Convert JSON to Parquet first, then load: COPY INTO table FROM @stage/file.parquet FILE_FORMAT = (TYPE = 'PARQUET')

Root cause:

JSON parsing is slower and less efficient than columnar formats for big data queries.

#3 Using Avro files without managing schemas properly.

Wrong approach:

Loading Avro data without providing or registering the schema in Snowflake.

Correct approach:

Ensure the Avro schema is available and compatible when loading data into Snowflake.

Root cause:

Avro data decoding depends on the schema; missing it causes load failures.

Key Takeaways

File formats define how data is stored and shared, each suited for different data shapes and uses.

CSV is simple and universal but limited to flat data without nesting or compression.

JSON supports complex nested data but can be large and slower for big data processing.

Parquet is a columnar format optimized for fast queries and storage efficiency in analytics.

Avro uses schemas to ensure data consistency and supports safe schema evolution over time.