Snowflake File Formats, INFER_SCHEMA and Staged-File Metadata

1.File format objects and the supported types

A named file format tells Snowflake how to read a set of staged files. You create it with CREATE FILE FORMAT, and it can be reused by stages, queries and COPY statements. If you declare it TEMPORARY, it is dropped when the session ends. CREATE OR ALTER FILE FORMAT can change a format's type-specific options and its comment, but not its TYPE.

If you don't specify TYPE, it defaults to CSV. Structured data usually comes as CSV, which means any delimited plain-text file: despite the name, any valid character can be the field separator. Semi-structured data comes as JSON, AVRO, ORC, Parquet or XML. On load, JSON can be newline-delimited (NDJSON) or comma-separated, but unloads are always written as NDJSON. Not every type works in both directions, as the table below shows.

Semi-structured values usually end up in a VARIANT column, which can hold objects up to 128 MB without a declared length. VARCHAR columns default to 16 MB, so you have to declare a larger size explicitly if you need one.

Sources12

2.Compression, file sizing and parsing options

Each TYPE has its own COMPRESSION values. AUTO is listed for every type that has the option. The binary columnar formats have their own codecs, and the reference lists no COMPRESSION option at all for ORC.

Compression also changes how the work is split up. A load can't run more operations in parallel than there are files, so Snowflake recommends compressed files of roughly 100–250 MB or more and advises against very large files of 100 GB or more. Combine small files, split large ones, and split by line so that no record spans two chunks. There is one exception for large uncompressed CSV files (over 128 MB) that follow RFC4180. Snowflake can scan these in parallel, but only if MULTI_LINE = FALSE, COMPRESSION = NONE and ON_ERROR is ABORT_STATEMENT or CONTINUE.

Parsing options handle the rest. The default character set is UTF-8, and ENCODING declares any other. Fields that contain the delimiter, or the Windows carriage return, should be enclosed in quotes, and quotes inside the data must be escaped. Every row should have the same number of columns. The CSV options reflect this: FIELD_OPTIONALLY_ENCLOSED_BY, ESCAPE, SKIP_HEADER, PARSE_HEADER, NULL_IF and ERROR_ON_COLUMN_COUNT_MISMATCH. JSON has STRIP_OUTER_ARRAY, and XML has STRIP_OUTER_ELEMENT.

Sources2

3.INFER_SCHEMA: deriving table design from staged files

You can design the target table without opening the files yourself. The INFER_SCHEMA table function reads staged Parquet, Avro, ORC, JSON or CSV files and returns one row per detected column. Each row has COLUMN_NAME, TYPE, NULLABLE, EXPRESSION (in the form $1:COLUMN_NAME::TYPE, mainly for external tables), FILENAMES and ORDER_ID. It needs a LOCATION (a named stage or the user stage, with an optional path) and a FILE_FORMAT object.

-- Create a file format that sets the file type as Parquet.
CREATE FILE FORMAT my_parquet_format
  TYPE = parquet;

-- Query the INFER_SCHEMA function.
SELECT *
  FROM TABLE(
    INFER_SCHEMA(
      LOCATION=>'@mystage'
      , FILE_FORMAT=>'my_parquet_format'
      )
    );

You can pass the output to CREATE TABLE, CREATE EXTERNAL TABLE or CREATE ICEBERG TABLE ... USING TEMPLATE, and GENERATE_COLUMN_DESCRIPTION turns the output into column text for a CREATE statement. A few arguments control the cost and accuracy of the scan:

- FILES names up to 1000 specific files. MAX_FILE_COUNT caps how many files are scanned but doesn't let you choose which ones. - MAX_RECORDS_PER_FILE applies only to CSV and JSON, and it can reduce accuracy. - With IGNORE_CASE => TRUE, column names come back in uppercase and the expression uses GET_IGNORE_CASE. - Use KIND => 'ICEBERG' when inferring Parquet for Iceberg tables. Otherwise the column definitions may be wrong.

The limitations are what the exam tests. Table stages aren't supported. For CSV, column names come from the header row only when the format sets PARSE_HEADER = TRUE, and that option can't be combined with SKIP_HEADER. With the default, columns are named c1, c2 and so on. For CSV and JSON, every column is reported as NULLABLE, and DATE_FORMAT, TIME_FORMAT and TIMESTAMP_FORMAT are ignored. Every timestamp variant comes back as TIMESTAMP_NTZ, and only the first level of nesting is inferred.

SELECT *
  FROM TABLE(
    INFER_SCHEMA(
      LOCATION=>'@mystage/geography/cities.parquet'
      ,  ? =>'my_parquet_format'
      )
    );

Sources3

4.Extracting metadata from staged files

For every file in an internal or external stage, Snowflake generates virtual metadata columns. You can select them alongside the data columns ($1, $2, ...) when you query the stage, or load them with COPY INTO <table>. This is how a target table records lineage, meaning which file and which row each record came from. The file format still matters here. In the source's CSV example, leaving out the |-delimited format means the delimiter is ignored, so each whole line ends up in $1.

To persist metadata, use the transformation form of COPY: a SELECT list in the FROM (...) clause that pulls the METADATA$ columns and the data columns into named target columns. Metadata can only be captured as rows are loaded. You can't insert it into rows that already exist, so add the lineage columns before the first load rather than backfilling them afterwards.

Sources4

Exam traps

Each one states something that sounds right. Open it to see what is actually true.

1. 1.INFER_SCHEMA can read files from any stage, including a table stage.Why is that wrong?

   INFER_SCHEMA supports named stages (internal or external) and the user stage, but not table stages.

   Covered in INFER_SCHEMA: deriving table design from staged files

2. 2.Setting PARSE_HEADER = TRUE together with SKIP_HEADER = 1 lets INFER_SCHEMA read column names from the header and then skip it.Why is that wrong?

   SKIP_HEADER can't be used with PARSE_HEADER = TRUE. PARSE_HEADER on its own treats the first row as the source of column names.

   Covered in INFER_SCHEMA: deriving table design from staged files

3. 3.Lineage columns can be added later by updating existing rows with METADATA$FILENAME.Why is that wrong?

   Metadata can only be queried at read time or loaded as rows are copied. It can't be inserted into rows that already exist.

   Covered in Extracting metadata from staged files