What you will be able to do
- Use CSV file format options to turn placeholder text into NULLs and parse non-default date formats
- Explain the two ways Snowflake reads Parquet: into one VARIANT column or into separate columns
- Load XML into a VARIANT and convert XML strings with PARSE_XML, controlling auto-conversion
- Read XML element content and attributes with the $ and @ operators
1.CSV: fixing values with file format options
CSV is plain delimited text. It has no nesting, so preparing it is mostly about getting each field to land cleanly in a typed column. That work happens in the file format options of CREATE FILE FORMAT or COPY INTO. Two problems cause most typed-column load failures. The first is placeholder text where a number or date should be. The second is dates written in a pattern the session doesn't expect.
NULL_IF handles placeholders. It takes a list of strings, and "Snowflake replaces these strings in the data load source with SQL NULL". The replacement is broad: it converts every instance of a listed value regardless of data type. DATE_FORMAT, along with TIME_FORMAT and TIMESTAMP_FORMAT, describes how dates are written in the file. If you leave it unset or set it to AUTO, Snowflake falls back to the DATE_INPUT_FORMAT session parameter. That's why a file whose dates use a different pattern fails until you set the option.
PARSE_HEADER = TRUE | FALSE SKIP_HEADER = <integer> SKIP_BLANK_LINES = TRUE | FALSE DATE_FORMAT = '<string>' | AUTO TIME_FORMAT = '<string>' | AUTO TIMESTAMP_FORMAT = '<string>' | AUTO| Option | Use it when | Default |
|---|---|---|
| NULL_IF | Placeholder strings in the file should load as SQL NULL | \\N |
| DATE_FORMAT | Dates in the file don't match the DATE_INPUT_FORMAT session parameter | AUTO |
| SKIP_HEADER | The file starts with header lines that shouldn't load as data | 0 |
| PARSE_HEADER | The first-row headers should provide column names (with INFER_SCHEMA / MATCH_BY_COLUMN_NAME) | FALSE |
| TRIM_SPACE | Leading or trailing spaces, for example before an opening quote, break the field | FALSE |
| SKIP_BLANK_LINES | Blank lines would otherwise cause an end-of-record error | FALSE |
Header handling has a catch. SKIP_HEADER just skips a fixed number of lines at the top of the file. PARSE_HEADER reads the first row to get column names. You can't combine the two: SKIP_HEADER isn't supported when PARSE_HEADER = TRUE.
Checkpoint 1 of 7· Check yourself
A CSV load into a DATE column fails, and DATE_FORMAT is not set in the file format. Which format does Snowflake use to parse the dates?
When DATE_FORMAT is unset or AUTO, Snowflake uses the session's DATE_INPUT_FORMAT. Dates in any other pattern need an explicit DATE_FORMAT.
“If a value is not specified or is AUTO, the value for the DATE_INPUT_FORMAT session parameter is used.”Source: docs.snowflake.com
Checkpoint 2 of 7· Check yourself
You set NULL_IF = ('2') to remove a sentinel value from a text column. What else happens during the load?
NULL_IF ignores the column's data type, so a numeric 2 elsewhere also becomes NULL.
“Note that Snowflake converts all instances of the value to NULL, regardless of the data type.”Source: docs.snowflake.com
Sources1
2.Parquet: columnar files read as VARIANT or as columns
Parquet is different from CSV in almost every way. The docs describe it as "a compressed, efficient columnar data representation designed for projects in the Hadoop ecosystem". It supports complex nested structures, and because it's binary you can't inspect it in a text editor. Snowflake reads it in one of two ways, depending on the loading use case.
In the general case, each Parquet record arrives in a single VARIANT column. From there you treat it exactly like JSON, using the same colon, dot and bracket paths and the same FLATTEN. The docs' sample shows how nested Parquet lists come through: a country object holds a city object, which holds a "bag" array of objects that each carry an "array_element" key. So to reach the city names you flatten the bag and read array_element from each value. In the second case, such as Iceberg-compatible Parquet, Snowflake reads the data directly into table columns. You can also do the column split yourself, extracting selected columns from a staged Parquet file with CREATE TABLE AS SELECT.
Checkpoint 3 of 7· Check yourself
Parquet data has been loaded into a single VARIANT column. How do you query a nested field in it?
Once Parquet is in a VARIANT, it uses the same query tools as JSON.
“You can query the data in a VARIANT column just as you would JSON data, using similar commands and functions.”Source: docs.snowflake.com
Sources2
3.XML: loading files and parsing strings with PARSE_XML
XML encodes data as tags and elements. An element has a start tag and an end tag, or is a single empty-element tag, and the tags can carry attributes. Snowflake brings XML into a VARIANT in one of two ways. To load a file, stage it and run COPY with TYPE=XML. To convert a string that's already in a table, call PARSE_XML, which returns an OBJECT holding an internal representation of the XML. Snowflake also provides CHECK_XML, TO_XML and XMLGET for XML work.
COPY INTO sample_xml_parts FROM @~/xml_stage FILE_FORMAT=(TYPE=XML) ON_ERROR='CONTINUE';Checkpoint 4 of 7· Put it in order
Put the steps for loading a local XML document into a table in order
- 1.CREATE a table with a VARIANT column for the document
- 2.COPY INTO the table with FILE_FORMAT=(TYPE=XML)
- 3.Save the XML content to a file on the local file system
- 4.PUT the file to an internal stage such as @~/xml_stage
The file must exist locally before PUT can stage it, and the table must exist before COPY can load the staged file into it.
“Copy the content of the XML document into a file on your file system.”Source: docs.snowflake.com
Every XML value is text, so PARSE_XML tries to convert values that are obviously numeric or Boolean into native types. Decimals keep exact precision, scientific notation becomes DOUBLE, and trailing zeros may be dropped, or added when integers and decimals are mixed in one column. Dates, times, timestamps and binary values are never auto-detected. They stay strings until you convert them. If you need the text left exactly as written, pass TRUE for disable_auto_convert. Like PARSE_JSON's output, the result doesn't guarantee that attribute order or whitespace survive a round trip.
SELECT PARSE_XML('<test>22257e111</test>'), PARSE_XML('<test>22257e111</test>', TRUE);Checkpoint 5 of 7· Fill the gap
Which named argument keeps 22257e111 as written instead of converting it to a DOUBLE?
SELECT PARSE_XML(STR => '<test>22257e111</test>', ? => TRUE);DISABLE_AUTO_CONVERT => TRUE stops PARSE_XML from converting numeric and Boolean text. The other options are file format settings, not PARSE_XML arguments.
Source: docs.snowflake.comSources3
4.XML: reading content with $ and attributes with @
Once XML is in a VARIANT, two operators read it. $ returns an element's contents as a VARIANT, and what you get depends on what's inside. Text comes back as a VARIANT value. A single child element comes back as a VARIANT in XML format. A series of child elements comes back as an array in JSON format. @ returns the name of the value, which helps when you iterate over differently named elements. @attribute_name returns the contents of the named attribute, so for <price units="dollar"> you would read the attribute with @units. If no such attribute exists, the result is NULL rather than an error.
| Operator | Returns |
|---|---|
| $ | The contents of the value: text, a nested element in XML format, or an array of elements in JSON format |
| @ | The name of the value (the element's tag name) |
| @attribute_name | The contents of the named attribute, or NULL if it doesn't exist |
Checkpoint 6 of 7· Check yourself
An element in an XML VARIANT has several child elements. What does applying $ to it return?
$ returns text for text content, XML for a single nested element, and a JSON array when the element holds a series of elements.
“If the element contains a series of elements, an array of the elements is returned as a VARIANT value in JSON format.”Source: docs.snowflake.com
Checkpoint 7 of 7· Exam question
Parquet files with columns named in mixed case are staged in `@lake_stage`, and no target table exists yet. The analyst wants the table to be built from the Parquet schema and then loaded by column name. Select TWO steps that accomplish this.(Select 2)
Correct answers: A, B — Run `CREATE TABLE sales USING TEMPLATE (SELECT ARRAY_AGG(OBJECT_CONSTRUCT(*)) FROM TABLE(INFER_SCHEMA(LOCATION=>'@lake_stage', FILE_FORMAT=>'pq_fmt')))`; Run `COPY INTO sales FROM @lake_stage FILE_FORMAT=(FORMAT_NAME='pq_fmt') MATCH_BY_COLUMN_NAME=CASE_INSENSITIVE` to map file columns to table columns
- A. Correct. INFER_SCHEMA reads the Parquet metadata and USING TEMPLATE creates a table with matching column names and types. This avoids hand-writing the DDL.
- B. Correct. MATCH_BY_COLUMN_NAME maps each Parquet column to the table column of the same name, ignoring case. Column order in the files then no longer matters.
- C. SKIP_HEADER is a CSV file format option, and Parquet files carry their schema in metadata rather than in a header row. Supplying it to a Parquet format is not a valid way to map columns.
- D. Each Parquet record is loaded as an object, which cannot be placed in a VARCHAR column without a transformation. This approach also discards the per-column typing the analyst wants.
- E. PARSE_HEADER belongs to the CSV file format and tells Snowflake to read column names from a header line. Parquet already stores column names in its metadata, so the option does not apply.
Sources2
Exam traps
Each one states something that sounds right. Open it to see what is actually true.
1.PARSE_XML recognises date and timestamp text and converts it to native date types, just as it does for numbers.Why is that wrong?
Auto-conversion covers only numeric and Boolean values. Dates, times, timestamps and binary values stay strings, and you must convert them yourself.
Covered in XML: loading files and parsing strings with PARSE_XML
2.SKIP_HEADER and PARSE_HEADER can be combined to skip a banner line and still read column names from the header.Why is that wrong?
The two options are incompatible. SKIP_HEADER isn't supported when PARSE_HEADER is TRUE.
Covered in CSV: fixing values with file format options
Sources
Every claim above is drawn from one of these pages, quoted as it was written on the date shown.
- 1.
“Snowflake replaces these strings in the data load source with SQL NULL.”
↩︎ CSV: fixing values with file format options“String that defines the format of date values in the data files to be loaded.”
↩︎ CSV: fixing values with file format options“Set this option to TRUE to remove undesirable spaces during the data load.”
↩︎ CSV: fixing values with file format options“Note that the SKIP_HEADER option is not supported with PARSE_HEADER = TRUE.”
↩︎ Exam trap 2“If a value is not specified or is AUTO, the value for the DATE_INPUT_FORMAT session parameter is used.”
↩︎ Checkpoint“Note that Snowflake converts all instances of the value to NULL, regardless of the data type.”
↩︎ Checkpoint - 2.
“Parquet is a compressed, efficient columnar data representation designed for projects in the Hadoop ecosystem.”
↩︎ Parquet: columnar files read as VARIANT or as columns“Depending on your loading use case, Snowflake either reads Parquet data into a single VARIANT column or directly into table columns”
↩︎ Parquet: columnar files read as VARIANT or as columns“Alternatively, you can extract select columns from a staged Parquet file into separate table columns using a CREATE TABLE AS SELECT statement.”
↩︎ Parquet: columnar files read as VARIANT or as columns“$ for the contents of the value.”
↩︎ XML: reading content with $ and attributes with @“Use @attribute_name for the contents of a named attribute.”
↩︎ XML: reading content with $ and attributes with @“If no attribute is found, NULL is returned.”
↩︎ XML: reading content with $ and attributes with @“You can query the data in a VARIANT column just as you would JSON data, using similar commands and functions.”
↩︎ Checkpoint“Copy the content of the XML document into a file on your file system.”
↩︎ Checkpoint“If the element contains a series of elements, an array of the elements is returned as a VARIANT value in JSON format.”
↩︎ Checkpoint - 3.
“Interprets an input string as an XML document, producing an OBJECT value.”
↩︎ XML: loading files and parsing strings with PARSE_XML“If you do not want the function to perform this conversion, pass TRUE for the disable_auto_convert argument.”
↩︎ XML: loading files and parsing strings with PARSE_XML“They are retained as strings, so convert the values from strings to native SQL data types if needed.”
↩︎ Exam trap 1