File formats (CSV, JSON, Parquet, Avro) in Snowflake - Step-by-Step Execution

Process Flow - File formats (CSV, JSON, Parquet, Avro)

Start: Data to store

↓

Choose file format

↓

Data saved in chosen format

↓

Data loaded by Snowflake

↓

Data parsed and used in queries

↓

End

This flow shows how data is saved in different file formats and then loaded and used in Snowflake.

Execution Sample

Snowflake

CREATE OR REPLACE FILE FORMAT my_csv_format
  TYPE = 'CSV'
  FIELD_DELIMITER = ','
  SKIP_HEADER = 1;

COPY INTO my_table FROM @my_stage FILE_FORMAT = (FORMAT_NAME = 'my_csv_format');

This code creates a CSV file format and loads data from a stage into a table using that format.

Process Table

Step	Action	File Format	Effect	Snowflake Behavior
1	Create CSV file format	CSV	Defines delimiter and header skip	Snowflake knows how to parse CSV files
2	Load data from stage	CSV	Reads CSV files with defined format	Data is parsed into table rows
3	Query data	CSV	Uses parsed data	Returns table rows for queries
4	Create JSON file format	JSON	Defines JSON parsing rules	Snowflake parses JSON objects
5	Load JSON data	JSON	Reads JSON files	Data loaded as semi-structured data
6	Create Parquet file format	Parquet	Defines columnar binary format	Snowflake reads efficient columnar data
7	Load Parquet data	Parquet	Reads Parquet files	Data loaded with schema and compression
8	Create Avro file format	Avro	Defines schema-based binary format	Snowflake reads Avro files with schema
9	Load Avro data	Avro	Reads Avro files	Data loaded with schema validation
10	Query all data	All	Uses internal parsing	Snowflake returns data rows correctly
11	End	-	-	Data ready for use in Snowflake

💡 All data formats are parsed and loaded successfully for querying in Snowflake.

Status Tracker

Variable	Start	After CSV Load	After JSON Load	After Parquet Load	After Avro Load	Final
Data in Stage	Raw files	CSV files	JSON files	Parquet files	Avro files	All files ready
File Format Object	None	CSV format defined	JSON format defined	Parquet format defined	Avro format defined	All formats defined
Table Data	Empty	CSV data loaded	JSON data loaded	Parquet data loaded	Avro data loaded	All data loaded

Key Moments - 3 Insights

Why do we need to define a file format before loading data?

Snowflake needs to know how to read the file correctly. For example, CSV files need delimiter info, JSON needs parsing rules. See execution_table rows 1 and 4.

What happens if the file format does not match the actual file?

Snowflake will fail to parse the data correctly, causing errors or wrong data. This is why defining the correct format is important (rows 2, 5, 7, 9).

How does Snowflake handle different file formats internally?

Snowflake uses specific parsers for each format to convert files into table rows for queries, as shown in rows 3, 5, 7, 9, and 10.

Visual Quiz - 3 Questions

Look at the execution_table, at which step is the Parquet file format created?

A Step 8

B Step 3

C Step 6

D Step 4

💡 Hint

Check the 'Action' and 'File Format' columns in execution_table rows.

According to variable_tracker, what is the state of 'Table Data' after JSON load?

A Empty

B JSON data loaded

C CSV data loaded

D All data loaded

💡 Hint

Look at the 'Table Data' row and the 'After JSON Load' column in variable_tracker.

If the CSV file format is not defined correctly, what will happen during loading?

A Loading will fail or data will be incorrect

B Data will load but queries will be slow

C Snowflake will guess the format and load data correctly

D Snowflake will convert CSV to JSON automatically

💡 Hint

Refer to key_moments about file format importance and execution_table steps 1 and 2.

Concept Snapshot

File formats in Snowflake:
- Define file format objects (CSV, JSON, Parquet, Avro)
- Specify parsing rules (delimiter, schema, etc.)
- Load data from stage using file format
- Snowflake parses files into table rows
- Correct format ensures successful data load and query

Full Transcript

This visual execution shows how Snowflake handles different file formats: CSV, JSON, Parquet, and Avro. First, you create a file format object that tells Snowflake how to read the files. For example, CSV needs delimiter info, JSON needs parsing rules, Parquet and Avro use schemas. Then, you load data from a stage using the defined format. Snowflake parses the files and loads data into tables. Finally, you can query the data as normal. The execution table traces each step from creating formats to loading and querying data. The variable tracker shows how data and formats change state during execution. Key moments clarify why defining the correct format is crucial and what happens if formats don't match. The quiz tests understanding of steps and states. This helps beginners see how Snowflake processes different file formats step-by-step.

Practice

1. Which file format in Snowflake is best suited for storing hierarchical data with nested structures?

easy

A. Avro

B. JSON

C. Parquet

D. CSV

Solution

1. Step 1: Understand file format characteristics

   JSON supports nested and hierarchical data structures naturally, unlike CSV which is flat.

2. Step 2: Compare JSON with other formats

   Parquet and Avro also support nested data but JSON is most commonly used for hierarchical data due to its readability and flexibility.

3. Final Answer:

   JSON -> Option B

4. Quick Check:

   Hierarchical data = JSON [OK]

Hint: Nested data? Think JSON first [OK]

Common Mistakes:

• Choosing CSV for nested data
• Confusing Parquet with JSON for readability
• Assuming Avro is always best for nested data

2. Which Snowflake file format option correctly specifies that the CSV file uses a semicolon as the field delimiter?

easy

A. FIELD_DELIMITER = ';'

B. FIELD_DELIMITER = ','

C. FIELD_DELIMITER = ':'

D. FIELD_DELIMITER = '|'

Solution

1. Step 1: Identify the delimiter option for CSV in Snowflake

   Snowflake uses FIELD_DELIMITER to specify the character separating fields in CSV files.

2. Step 2: Match the semicolon delimiter

   The semicolon character is ';', so FIELD_DELIMITER = ';' is correct.

3. Final Answer:

   FIELD_DELIMITER = ';' -> Option A

4. Quick Check:

   Semicolon delimiter = FIELD_DELIMITER ';' [OK]

Hint: Delimiter option is FIELD_DELIMITER [OK]

Common Mistakes:

• Using comma instead of semicolon
• Confusing FIELD_DELIMITER with RECORD_DELIMITER
• Using wrong delimiter characters

3. Given this Snowflake file format definition for JSON:

CREATE FILE FORMAT my_json_format TYPE = 'JSON' STRIP_OUTER_ARRAY = TRUE;

What happens when you load a JSON file containing an outer array of objects?

medium

A. Snowflake loads the entire array as a single row

B. Snowflake ignores the outer array and loads nothing

C. Snowflake throws an error due to the outer array

D. Snowflake loads each object inside the outer array as a separate row

Solution

1. Step 1: Understand STRIP_OUTER_ARRAY option

   This option tells Snowflake to treat each element inside the outer JSON array as a separate record.

2. Step 2: Apply to loading behavior

   When loading, Snowflake will parse the outer array and load each object inside it as its own row.

3. Final Answer:

   Snowflake loads each object inside the outer array as a separate row -> Option D

4. Quick Check:

   STRIP_OUTER_ARRAY TRUE = separate rows [OK]

Hint: STRIP_OUTER_ARRAY TRUE splits array into rows [OK]

Common Mistakes:

• Thinking entire array loads as one row
• Expecting an error on outer array
• Assuming outer array is ignored

4. You created a Snowflake file format for CSV with:

CREATE FILE FORMAT my_csv_format TYPE = 'CSV' FIELD_OPTIONALLY_ENCLOSED_BY = '"';

When loading data, some fields with commas inside quotes are split incorrectly. What is the likely issue?

medium

A. FIELD_DELIMITER is missing and defaults to tab

B. FIELD_OPTIONALLY_ENCLOSED_BY should be set to single quote instead of double quote

C. The CSV file uses a different enclosing character than specified

D. The file format type should be JSON, not CSV

Solution

1. Step 1: Check FIELD_OPTIONALLY_ENCLOSED_BY usage

   This option tells Snowflake which character encloses fields optionally, often double quotes for CSV.

2. Step 2: Identify mismatch with actual file

   If the CSV file uses a different enclosing character (like single quotes), Snowflake will not parse fields with commas correctly.

3. Final Answer:

   The CSV file uses a different enclosing character than specified -> Option C

4. Quick Check:

   Enclosing char mismatch breaks parsing [OK]

Hint: Match enclosing char exactly to file [OK]

Common Mistakes:

• Changing enclosing char without checking file
• Assuming FIELD_DELIMITER defaults to comma always
• Switching file format type unnecessarily

5. You want to load a large dataset with complex nested data and efficient compression into Snowflake. Which file format should you choose and why?

hard

A. Parquet, because it supports nested data and is optimized for compression and performance

B. JSON, because it supports nested data and is human-readable

C. CSV, because it is simple and widely supported

D. Avro, because it only supports flat data but is fast

Solution

1. Step 1: Identify requirements

   The dataset is large, has nested data, and needs efficient compression and performance.

2. Step 2: Compare file formats

   CSV is flat and not compressed; JSON is nested but less efficient; Avro supports nested but less optimized than Parquet; Parquet supports nested data and is columnar, offering better compression and query speed.

3. Final Answer:

   Parquet, because it supports nested data and is optimized for compression and performance -> Option A

4. Quick Check:

   Large nested data + compression = Parquet [OK]

Hint: Large nested data? Pick Parquet for speed and size [OK]

Common Mistakes:

• Choosing CSV for nested data
• Preferring JSON despite compression needs
• Misunderstanding Avro's capabilities