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
- 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 TEMPLATEplusARRAY_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.
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.
| Type | Kind | Loads into | Notes |
|---|---|---|---|
CSV | Delimited text | Typed columns | Default type; also used for TSV and other delimiters |
JSON | Semi-structured | VARIANT | One document per row, or an outer array split with STRIP_OUTER_ARRAY = TRUE |
AVRO | Semi-structured, binary | VARIANT | Schema travels with the file |
ORC | Semi-structured, binary | VARIANT | Columnar |
PARQUET | Semi-structured, binary | VARIANT | Columnar; common for data lakes |
XML | Semi-structured | VARIANT | Tags 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.
| Argument | Effect |
|---|---|
FILES | Scan only the listed files (up to 1,000) |
IGNORE_CASE | Default FALSE; TRUE makes column names case insensitive and returns them in uppercase |
MAX_FILE_COUNT | Limits how many files are scanned; you cannot choose which |
MAX_RECORDS_PER_FILE | Limits rows scanned per file; applies to CSV and JSON only, and fewer rows can mean less accurate types |
KIND | STANDARD (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.
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
| Data | Examples | How it is ingested | How it is queried |
|---|---|---|---|
| Structured | CSV, TSV | COPY into typed columns | Normal SQL |
| Semi-structured | JSON, Avro, ORC, Parquet, XML | COPY into a VARIANT column, or into typed columns by name or transformation | Path notation col:a.b::STRING, FLATTEN for arrays |
| Unstructured | Images, PDFs, audio, video | Files are put in a stage with a directory table, not loaded as rows | File URLs, file functions, Snowpark, or Cortex functions that read staged files |
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.
"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 TEMPLATEplusARRAY_AGG(OBJECT_CONSTRUCT(*))builds a table from file structure.MATCH_BY_COLUMN_NAMEloads by name; for CSV it needsPARSE_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.