Data Movement · Topic 2 of 17

File Formats, INFER_SCHEMA and Structured, Semi-Structured and Unstructured Data

Pick file format options, detect table design with INFER_SCHEMA and USING TEMPLATE, and ingest structured, semi-structured and unstructured data.

  • 5 min
In a nutshell
  • Supported load formats: CSV, JSON, Avro, ORC, Parquet, XML. Semi-structured types land in VARIANT.
  • INFER_SCHEMA works on Parquet, Avro, ORC, JSON and CSV in named or user stages, not table stages.
  • CREATE TABLE ... USING TEMPLATE plus ARRAY_AGG(OBJECT_CONSTRUCT(*)) builds a table from file structure.

What it is

A file format object tells Snowflake how to read (or write) a staged file: its type, delimiters, header rows, null markers and compression. Without one, COPY assumes CSV with default options.

INFER_SCHEMA reads staged files and returns the column names and data types it finds, so you can design a table from the data instead of guessing. The three kinds of data differ in where they end up: structured data in typed columns, semi-structured data in VARIANT, and unstructured data left in the stage.

Staged filesread byINFER_SCHEMAnames, types, nullabilityfeedsCREATE TABLE USING TEMPLATEcolumn list built for youthenCOPY INTOMATCH_BY_COLUMN_NAME

How it works

Required file formats

A format is required for every load, either as FILE_FORMAT = (TYPE = ...) inline, a named CREATE FILE FORMAT object, or one attached to the stage. A format named in COPY overrides the one on the stage.

TypeKindLoads intoNotes
CSVDelimited textTyped columnsDefault type; also used for TSV and other delimiters
JSONSemi-structuredVARIANTOne document per row, or an outer array split with STRIP_OUTER_ARRAY = TRUE
AVROSemi-structured, binaryVARIANTSchema travels with the file
ORCSemi-structured, binaryVARIANTColumnar
PARQUETSemi-structured, binaryVARIANTColumnar; common for data lakes
XMLSemi-structuredVARIANTTags become nested elements

The options you set most often for CSV are FIELD_DELIMITER (default comma), SKIP_HEADER, FIELD_OPTIONALLY_ENCLOSED_BY, NULL_IF, DATE_FORMAT, TIMESTAMP_FORMAT and ERROR_ON_COLUMN_COUNT_MISMATCH (default TRUE). For JSON the key ones are STRIP_OUTER_ARRAY, STRIP_NULL_VALUES and ALLOW_DUPLICATE.

CREATE FILE FORMAT csv_ff
  TYPE = CSV
  PARSE_HEADER = TRUE
  FIELD_OPTIONALLY_ENCLOSED_BY = '"'
  NULL_IF = ('', 'NULL');

PARSE_HEADER = TRUE uses the first line as column names. It is what lets INFER_SCHEMA and MATCH_BY_COLUMN_NAME work on CSV, and it cannot be combined with SKIP_HEADER.

Schema detection with INFER_SCHEMA

INFER_SCHEMA is a table function. It supports Parquet, Avro, ORC, JSON and CSV files in a named internal or external stage or a user stage. It does not support table stages.

SELECT * FROM TABLE(
  INFER_SCHEMA(
    LOCATION => '@raw_stage/orders/',
    FILE_FORMAT => 'parquet_ff'
  )
);

It returns one row per column: COLUMN_NAME, TYPE, NULLABLE, EXPRESSION (such as $1:id::NUMBER(38,0)), FILENAMES and ORDER_ID.

ArgumentEffect
FILESScan only the listed files (up to 1,000)
IGNORE_CASEDefault FALSE; TRUE makes column names case insensitive and returns them in uppercase
MAX_FILE_COUNTLimits how many files are scanned; you cannot choose which
MAX_RECORDS_PER_FILELimits rows scanned per file; applies to CSV and JSON only, and fewer rows can mean less accurate types
KINDSTANDARD (default) returns Snowflake types; ICEBERG returns Iceberg types

To create the table from the result, wrap the function in USING TEMPLATE:

CREATE TABLE orders USING TEMPLATE (
  SELECT ARRAY_AGG(OBJECT_CONSTRUCT(*))
  FROM TABLE(INFER_SCHEMA(
    LOCATION => '@raw_stage/orders/',
    FILE_FORMAT => 'parquet_ff')));

CREATE EXTERNAL TABLE and CREATE ICEBERG TABLE accept USING TEMPLATE too. GENERATE_COLUMN_DESCRIPTION produces a column list you can paste into a CREATE TABLE or CREATE VIEW statement.

Watch out

For CSV and JSON, every column is reported as nullable, all timestamp variants come back as TIMESTAMP_NTZ, and only the first level of nested data is inferred. Check the result before trusting it, especially for decimals and dates.

After the table exists, load by name so column order does not matter:

COPY INTO orders
FROM @raw_stage/orders/
FILE_FORMAT = (FORMAT_NAME = 'parquet_ff')
MATCH_BY_COLUMN_NAME = CASE_INSENSITIVE;

Structured, semi-structured and unstructured data

DataExamplesHow it is ingestedHow it is queried
StructuredCSV, TSVCOPY into typed columnsNormal SQL
Semi-structuredJSON, Avro, ORC, Parquet, XMLCOPY into a VARIANT column, or into typed columns by name or transformationPath notation col:a.b::STRING, FLATTEN for arrays
UnstructuredImages, PDFs, audio, videoFiles are put in a stage with a directory table, not loaded as rowsFile URLs, file functions, Snowpark, or Cortex functions that read staged files
JSON or Parquet fileCOPYVARIANT columnone column holds the documentpath and castTyped columnsPDF or image filestored inStagedirectory table onread bySnowpark or Cortexvia file URL

Semi-structured data inside VARIANT is stored in an optimized columnar way, so you can query it without loading it into typed columns first. Typed columns are still better when you filter and join often, because data types and statistics help pruning.

For unstructured files the directory table lists each file (path, size, last modified, ETag and FILE_URL). Access is by URL, and the URL type decides who can open the file. The next page covers scoped, file and pre-signed URLs.

Exam tip

"The schema of incoming Parquet files changes every week and the table must be built from the files" points to INFER_SCHEMA with USING TEMPLATE. "Store the raw JSON exactly as received" points to a single VARIANT column.

Common mistakes

Pointing INFER_SCHEMA at a table stage. Only named stages (internal or external) and the user stage work.

Using SKIP_HEADER with PARSE_HEADER. They are mutually exclusive. Use PARSE_HEADER = TRUE when you want header names for schema detection.

Forgetting STRIP_OUTER_ARRAY. A JSON file that is one big array loads as a single huge row. With STRIP_OUTER_ARRAY = TRUE each element becomes a row.

Trusting inferred types blindly. CSV and JSON inference scans a sample and defaults every column to nullable.

Loading unstructured files with COPY INTO a table. Unstructured data stays in the stage and is read through URLs.

What to remember for the exam

  • Supported load formats: CSV, JSON, Avro, ORC, Parquet, XML. Semi-structured types land in VARIANT.
  • INFER_SCHEMA works on Parquet, Avro, ORC, JSON and CSV in named or user stages, not table stages.
  • CREATE TABLE ... USING TEMPLATE plus ARRAY_AGG(OBJECT_CONSTRUCT(*)) builds a table from file structure.
  • MATCH_BY_COLUMN_NAME loads by name; for CSV it needs PARSE_HEADER = TRUE.
  • Unstructured data needs a directory table and is accessed through file URLs.

Check yourself

  • Which function and clause create a table whose columns match staged Parquet files?

    INFER_SCHEMA inside CREATE TABLE ... USING TEMPLATE, aggregating the rows with ARRAY_AGG(OBJECT_CONSTRUCT(*)).

  • Why might INFER_SCHEMA on a CSV file give wrong types?

    It scans only a sample of rows and reports every column as nullable, so values later in the file may not fit.

  • Where do semi-structured JSON documents land by default when loaded without a transformation?

    In a single VARIANT column of the target table.

Topic 2 of 17 in Data Movement

Test yourself on Data Movement

The full Snowflake DEA-C02 question bank has 709 questions, with timed practice exams and an explanation for every answer.

This study guide is independent and is not affiliated with, endorsed by, or sponsored by Snowflake. Vendor names, certification names and exam codes are trademarks of their respective owners and are used for identification only.