How to Detect CSV Headers with INFER_SCHEMA in Snowflake

Learn how to correctly configure Snowflake's file format settings to recognize CSV headers during schema inference using the INFER_SCHEMA function.
---
This video is based on the question https://stackoverflow.com/q/79419087/ asked by the user 'Mat' ( https://stackoverflow.com/u/3737762/ ) and on the answer https://stackoverflow.com/a/79419392/ provided by the user 'Simeon Pilgrim' ( https://stackoverflow.com/u/43992/ ) at 'Stack Overflow' website. Thanks to these great users and Stackexchange community for their contributions.

Visit these links for original content and any more details, such as alternate solutions, latest updates/developments on topic, comments, revision history etc. For example, the original title of the Question was: snowflake - header not detected by infer schema

Also, Content (except music) licensed under CC BY-SA https://meta.stackexchange.com/help/l...
The original Question post is licensed under the 'CC BY-SA 4.0' ( https://creativecommons.org/licenses/... ) license, and the original Answer post is licensed under the 'CC BY-SA 4.0' ( https://creativecommons.org/licenses/... ) license.

If anything seems off to you, please feel free to drop me a comment under this video.
---
Problem Overview

When using Snowflake's INFER_SCHEMA function to derive a table schema from a CSV file, you might notice the header row being treated as data instead of column names. This happens because the header row is not being parsed properly, resulting in generic column names like c1, c2, c3 and the header row appearing as the first data record.

Why This Happens

The key issue stems from how the file format is configured for the INFER_SCHEMA function. Specifically, two properties control header processing:

SKIP_HEADER: Number of lines to skip at the start.

PARSE_HEADER: Enables parsing the header row as column names.

If PARSE_HEADER is set to false or not set at all, the header row is treated as regular data, causing incorrect schema inference.

Correct Approach to Handle CSV Headers in Snowflake

To properly detect and use headers during schema inference, follow these best practices:

1. Create Separate File Formats for Inference and Loading

Inference File Format: Should have PARSE_HEADER=true and SKIP_HEADER=0 to ensure the header row is read and used for column names.

Loading File Format: Should have PARSE_HEADER=false and SKIP_HEADER=1 to skip the header during data loading.

Example:

[[See Video to Reveal this Text or Code Snippet]]

2. Run INFER_SCHEMA with the Header-Aware File Format

Use the inference file format when calling INFER_SCHEMA:

[[See Video to Reveal this Text or Code Snippet]]

3. Load Data Using the Loading File Format

For loading data into your tables, use the format that skips the header row:

[[See Video to Reveal this Text or Code Snippet]]

Summary

Always set PARSE_HEADER=true when you want Snowflake to treat the first row as a header for schema inference.

Use PARSE_HEADER=false and SKIP_HEADER=1 when loading data to skip the header row.

Define separate file formats for schema inference and data loading to avoid conflicts.

This ensures your CSV headers are correctly recognized during inference and excluded during data loading.