Snowflake File Formats Design Guide: Optimal Settings for CSV, JSON, Parquet

File Format is a reusable object that defines the file type and parse options used when loading data into Snowflake (COPY INTO <table>) or unloading it (COPY INTO <location>). It supports five formats — CSV, JSON, Parquet, Avro, and ORC — and lets you centrally manage format-specific options (delimiters, compression, error handling, etc.).

Creating a CSV File Format

-- Basic CSV File Format
CREATE OR REPLACE FILE FORMAT util_db.formats.csv_standard
  TYPE = 'CSV'
  FIELD_DELIMITER = ','
  RECORD_DELIMITER = '\n'
  SKIP_HEADER = 1
  FIELD_OPTIONALLY_ENCLOSED_BY = '"'
  NULL_IF = ('NULL', 'null', '\N', '')
  EMPTY_FIELD_AS_NULL = TRUE
  COMPRESSION = 'AUTO';

-- Pipe-delimited CSV (for legacy system integration)
CREATE OR REPLACE FILE FORMAT util_db.formats.csv_pipe
  TYPE = 'CSV'
  FIELD_DELIMITER = '|'
  RECORD_DELIMITER = '\n'
  SKIP_HEADER = 0
  ESCAPE = '\\'
  ESCAPE_UNENCLOSED_FIELD = '\\'
  TRIM_SPACE = TRUE;

-- Error-tolerant CSV format (for unstable data quality)
CREATE OR REPLACE FILE FORMAT util_db.formats.csv_tolerant
  TYPE = 'CSV'
  FIELD_DELIMITER = ','
  SKIP_HEADER = 1
  FIELD_OPTIONALLY_ENCLOSED_BY = '"'
  ERROR_ON_COLUMN_COUNT_MISMATCH = FALSE
  REPLACE_INVALID_CHARACTERS = TRUE;

Key CSV Options

Creating a JSON File Format

-- Basic JSON File Format
CREATE OR REPLACE FILE FORMAT util_db.formats.json_standard
  TYPE = 'JSON'
  STRIP_OUTER_ARRAY = TRUE
  STRIP_NULL_VALUES = FALSE
  IGNORE_UTF8_ERRORS = FALSE
  COMPRESSION = 'AUTO';

-- Nested JSON (loaded into a VARIANT column)
CREATE OR REPLACE FILE FORMAT util_db.formats.json_nested
  TYPE = 'JSON'
  STRIP_OUTER_ARRAY = TRUE
  ALLOW_DUPLICATE = TRUE
  ENABLE_OCTAL = FALSE;

Key JSON Options

Creating Parquet / Avro / ORC File Formats

-- Parquet File Format
CREATE OR REPLACE FILE FORMAT util_db.formats.parquet_standard
  TYPE = 'PARQUET'
  COMPRESSION = 'SNAPPY'
  BINARY_AS_TEXT = FALSE;

-- Avro File Format
CREATE OR REPLACE FILE FORMAT util_db.formats.avro_standard
  TYPE = 'AVRO'
  COMPRESSION = 'AUTO';

-- ORC File Format
CREATE OR REPLACE FILE FORMAT util_db.formats.orc_standard
  TYPE = 'ORC'
  TRIM_SPACE = FALSE;

File Format Comparison Table

File Format Usage Patterns with COPY INTO

-- Pattern 1: Reference a named File Format
COPY INTO raw_data.orders
FROM @landing_stage/orders/
FILE_FORMAT = util_db.formats.csv_standard
ON_ERROR = 'CONTINUE';

-- Pattern 2: Specify options inline
COPY INTO raw_data.events
FROM @landing_stage/events/
FILE_FORMAT = (TYPE = 'JSON' STRIP_OUTER_ARRAY = TRUE);

-- Pattern 3: Load from Parquet with auto schema detection
COPY INTO raw_data.metrics
FROM @landing_stage/metrics/
FILE_FORMAT = util_db.formats.parquet_standard
MATCH_BY_COLUMN_NAME = CASE_INSENSITIVE;

-- Unload: export from table to Parquet files
COPY INTO @export_stage/reports/
FROM analytics.monthly_report
FILE_FORMAT = util_db.formats.parquet_standard
HEADER = TRUE
OVERWRITE = TRUE;

Binding a File Format to a Stage

-- Specify the File Format when creating the stage
CREATE OR REPLACE STAGE csv_landing
  URL = 's3://my-bucket/csv/'
  STORAGE_INTEGRATION = s3_integration
  FILE_FORMAT = util_db.formats.csv_standard;

-- In this case, FILE_FORMAT is not required in COPY INTO
COPY INTO raw_data.orders FROM @csv_landing;

File Format Management Commands

-- List
SHOW FILE FORMATS IN SCHEMA util_db.formats;

-- Inspect the definition in detail
DESCRIBE FILE FORMAT util_db.formats.csv_standard;

-- Change an option
ALTER FILE FORMAT util_db.formats.csv_standard
  SET SKIP_HEADER = 2;

-- Drop
DROP FILE FORMAT util_db.formats.csv_standard;

Troubleshooting

Best Practices

• Consolidate File Formats in a central management schema: manage them in a shared schema such as util_db.formats and GRANT the USAGE privilege to the roles that need it
• Prefer Parquet for analytics workloads: columnar format with high compression efficiency, and it also supports schema evolution via MATCH_BY_COLUMN_NAME
• Pre-validate with the VALIDATE function: before COPY INTO, identify problem rows with VALIDATION_MODE = 'RETURN_ERRORS' and only then load
• Explicitly set NULL_IF and EMPTY_FIELD_AS_NULL: prevent data quality issues by making the treatment of empty strings versus NULL unambiguous

Check Your Understanding

Frequently Asked Questions