Bringing structured, semi-structured, and unstructured files in through stages and file formats

Snowflake ingests structured and semi-structured data (CSV, JSON, Parquet, Avro, ORC, XML) primarily via the COPY INTO <table> command, which reads staged files using a named or inline FILE FORMAT object to define parsing rules. Understanding stage types, file format options, and how Snowflake maps semi-structured data into VARIANT/ARRAY/OBJECT columns is essential for reliable, efficient loading.

1 · Learn the must-know

• 1COPY INTO requires files to be staged first (internal user/table/named stage or external stage on S3/Azure/GCS) before loading into a table.
• 2A FILE FORMAT object (named or inline) specifies TYPE and parsing options like FIELD_DELIMITER, SKIP_HEADER, COMPRESSION, STRIP_OUTER_ARRAY, and ON_ERROR, and can be reused across multiple COPY commands.
• 3Semi-structured formats (JSON, Avro, ORC, Parquet, XML) are typically loaded into a single VARIANT column, or can be flattened into relational columns during load using COPY INTO with explicit column mapping and FLATTEN/PARSE_JSON in a SELECT.
• 4COPY INTO tracks load history (load metadata) per file per table for 64 days by default, preventing accidental reloading of the same file unless FORCE=TRUE is specified.
• 5ON_ERROR options (CONTINUE, SKIP_FILE, SKIP_FILE_<n>, ABORT_STATEMENT) control how COPY INTO handles malformed records, and validation can be pre-checked using VALIDATION_MODE or the VALIDATE table function.
• 6Parquet and ORC files can leverage columnar metadata for efficient partial loads, and Snowflake auto-detects schema for these formats when using MATCH_BY_COLUMN_NAME or schema detection with CREATE TABLE ... USING TEMPLATE.

2 · Check your understanding