File formats (CSV, JSON, Parquet, Avro) in Snowflake - Mini Project: Build & Apply

Working with File Formats in Snowflake

📖 Scenario: You are a data engineer working with Snowflake. You need to prepare and load data files in different formats like CSV, JSON, Parquet, and Avro into Snowflake tables.

🎯 Goal: Build Snowflake SQL commands step-by-step to create tables and stage files in CSV, JSON, Parquet, and Avro formats, and configure file format objects for loading data correctly.

📋 What You'll Learn

Create a table for customer data

Create a file format for CSV files

Create a file format for JSON files

Create a file format for Parquet files

Create a file format for Avro files

💡 Why This Matters

🌍 Real World

Data engineers often need to prepare Snowflake tables and file formats to load data from various file types efficiently.

💼 Career

Understanding how to configure file formats and stages in Snowflake is essential for roles involving data ingestion and cloud data warehousing.

1

Create a table for customer data

Write a Snowflake SQL command to create a table called customers with columns id as INTEGER, name as VARCHAR(100), and email as VARCHAR(100).

Snowflake

-- Your code here

Need a hint?

Use CREATE OR REPLACE TABLE customers and define the columns with their data types.

2

Create a file format for CSV files

Write a Snowflake SQL command to create a file format called csv_format for CSV files with FIELD_DELIMITER as comma and SKIP_HEADER set to 1.

Snowflake

CREATE OR REPLACE TABLE customers (
    id INTEGER,
    name VARCHAR(100),
    email VARCHAR(100)
);
-- Your code here

Need a hint?

Use CREATE OR REPLACE FILE FORMAT csv_format and specify CSV type with the correct options.

3

Create file formats for JSON, Parquet, and Avro files

Write Snowflake SQL commands to create file formats called json_format, parquet_format, and avro_format for JSON, Parquet, and Avro file types respectively.

Snowflake

CREATE OR REPLACE TABLE customers (
    id INTEGER,
    name VARCHAR(100),
    email VARCHAR(100)
);

CREATE OR REPLACE FILE FORMAT csv_format
    TYPE = 'CSV'
    FIELD_DELIMITER = ','
    SKIP_HEADER = 1;
-- Your code here

Need a hint?

Use CREATE OR REPLACE FILE FORMAT for each format with the correct TYPE.

4

Complete by creating a stage for loading files

Write a Snowflake SQL command to create a stage called customer_stage that uses the csv_format file format.

Snowflake

CREATE OR REPLACE TABLE customers (
    id INTEGER,
    name VARCHAR(100),
    email VARCHAR(100)
);

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

CREATE OR REPLACE FILE FORMAT json_format
    TYPE = 'JSON';

CREATE OR REPLACE FILE FORMAT parquet_format
    TYPE = 'PARQUET';

CREATE OR REPLACE FILE FORMAT avro_format
    TYPE = 'AVRO';
-- Your code here

Need a hint?

Use CREATE OR REPLACE STAGE customer_stage and specify the FILE_FORMAT option.