File formats (CSV, JSON, Parquet, Avro) in Snowflake - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ service_behavior

intermediate

Understanding CSV File Loading Behavior in Snowflake

You load a CSV file into a Snowflake table using the COPY INTO command with default options. The CSV file contains some rows with missing values at the end of the line.

What will happen to those missing values during the load?

Snowflake

COPY INTO my_table FROM @my_stage/file.csv FILE_FORMAT = (TYPE = 'CSV');

A Missing trailing columns are ignored and the row is truncated to available columns.

B The load fails with an error due to missing columns in some rows.

C Missing trailing columns are loaded as NULL values in the table columns.

D Snowflake fills missing columns with empty strings instead of NULL.

💡 Hint

Think about how Snowflake handles incomplete rows in CSV files by default.

Snowflake treats missing trailing columns in CSV files as NULL values by default when loading data. This allows partial rows to be loaded without errors.

❓ Architecture

intermediate

Choosing the Best File Format for Semi-Structured Data in Snowflake

You need to store and query semi-structured JSON data efficiently in Snowflake. Which file format should you choose to optimize query performance and storage?

A Parquet

B JSON

C CSV

D Avro

💡 Hint

Consider which format supports columnar storage and efficient compression.

Parquet is a columnar storage format that compresses data efficiently and improves query performance, especially for semi-structured data like JSON.

❓ security

advanced

Handling Schema Evolution with Avro Files in Snowflake

You have Avro files with evolving schemas being loaded into a Snowflake table. What is the best practice to handle schema changes without causing load failures?

A Convert Avro files to CSV before loading to avoid schema issues.

B Use a fixed schema in Snowflake and reject files with schema differences.

C Manually update the Snowflake table schema before loading new Avro files.

D Use Snowflake's VARIANT column type to store Avro data and parse schema dynamically.

💡 Hint

Think about how Snowflake handles semi-structured data and schema flexibility.

Using VARIANT columns allows Snowflake to store semi-structured data like Avro with evolving schemas without requiring strict schema enforcement, avoiding load failures.

🧠 Conceptual

advanced

Comparing Compression and Query Efficiency of File Formats in Snowflake

Which file format among CSV, JSON, Parquet, and Avro generally provides the best compression and fastest query performance in Snowflake?

A CSV

B Parquet

C Avro

D JSON

💡 Hint

Consider columnar vs row-based formats and compression capabilities.

Parquet is a columnar format that compresses data well and enables faster queries by reading only needed columns, outperforming row-based formats like CSV, JSON, and Avro.

✅ Best Practice

expert

Optimizing Snowflake Data Loading with Mixed File Formats

You have a Snowflake pipeline that loads data from multiple file formats: CSV, JSON, Parquet, and Avro. To optimize loading speed and minimize errors, which approach is best?

A Create separate tables optimized for each file format and load accordingly.

B Load all files into one table with columns matching the CSV schema.

C Load all files into a single VARIANT column table regardless of format.

D Convert all files to CSV before loading to simplify the process.

💡 Hint

Think about how different file formats have different strengths and schema requirements.

Creating separate tables optimized for each file format allows Snowflake to leverage format-specific features and avoid errors, improving load speed and data quality.

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