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

  • COPY INTO requires files to be staged first (internal user/table/named stage or external stage on S3/Azure/GCS) before loading into a table.
  • A 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.
  • Semi-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.
  • COPY 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.
  • ON_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.
  • Parquet 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.

3 · Keep going

Ready for more? Take a weighted mock or try free practice questions.

These are independent practice questions, written against this certification's published exam guide. They are not the certification vendor's own questions, and not the real exam.