Performance Optimization, Querying, and Transformation · Topic 5 of 6

Using Structured, Semi-Structured and Unstructured Data

Transform structured tables, query VARIANT data with path notation, casts and FLATTEN, and work with unstructured files through directory tables and URLs.

  • 5 min
In a nutshell
  • Semi-structured data goes in VARIANT, OBJECT and ARRAY columns; supported formats include JSON, Avro, ORC, Parquet and XML.
  • Path notation: col:a.b, col['a']['b'], col:arr[0]; element names are case sensitive; cast with ::.
  • LATERAL FLATTEN turns arrays into rows; the element is in the VALUE column.

What it is

Snowflake handles three shapes of data, and each one is transformed with different tools.

  • Structured data has a fixed schema of typed columns, such as a sales table.
  • Semi-structured data carries its own structure, such as JSON, Avro, ORC, Parquet or XML. Snowflake stores it in VARIANT, OBJECT and ARRAY columns.
  • Unstructured data is files with no table structure, such as PDFs, images, audio and documents, stored in stages.

The exam asks how to query each shape: path notation and FLATTEN for semi-structured data, and directory tables and file URLs for unstructured data.

How it works

Structured data

Structured transformation is standard SQL. Common patterns are:

  • CREATE TABLE ... AS SELECT (CTAS) to build a new table from a query.
  • INSERT INTO ... SELECT to append, and MERGE to insert, update or delete in one statement based on a match condition.
  • Views and common table expressions (WITH) to break a transformation into readable steps.
  • Casting with CAST(x AS type) or x::type, and TRY_CAST to return NULL instead of failing on bad values.
Raw tablelandingMERGEClean tablededuplicatedCTASReporting tableaggregatedSELECTDashboard

Semi-structured data

A VARIANT column can hold any value, including nested objects and arrays. You navigate it with path notation:

SyntaxExampleMeaning
Colon then dotsrc:customer.nameFirst level after the colon, nested levels after dots
Bracketssrc['customer']['name']Same, useful for names with spaces or special characters
Array indexsrc:items[0]First element, indexes start at 0
Castsrc:customer.name::STRINGTurns the VARIANT value into a typed value

Key rules:

  • Element names are case sensitive in path notation, while column names are not.
  • A path value is still a VARIANT. Cast it with :: to compare it or to drop the double quotes around strings in output.
  • A missing path returns SQL NULL; a JSON null value is stored as a VARIANT null, which is different from SQL NULL.

To turn arrays into rows, use FLATTEN, usually with LATERAL:

SELECT o.src:order_id::NUMBER AS order_id,
       f.value:sku::STRING     AS sku
FROM orders o,
     LATERAL FLATTEN(input => o.src:items) f;

FLATTEN returns the columns SEQ, KEY, PATH, INDEX, VALUE and THIS. Add RECURSIVE => TRUE to expand every nested level, and OUTER => TRUE to keep rows whose array is empty.

VARIANT roworder with items arrayLATERAL FLATTENOne row per itemVALUE columncast with ::Typed columnsrelational table

Going the other way, functions build semi-structured values:

  • PARSE_JSON turns a JSON string into a VARIANT; TRY_PARSE_JSON returns NULL on invalid input.
  • OBJECT_CONSTRUCT builds an OBJECT from key and value pairs.
  • ARRAY_AGG and OBJECT_AGG aggregate rows into an ARRAY or OBJECT.

For staged Parquet, Avro, ORC, JSON or CSV files, INFER_SCHEMA can detect column names and types so you can create a structured table directly.

Exam tip

If a query on a JSON column returns NULL for every row, check the letter case of the element names first. src:CustomerId and src:customerid are different paths.

Unstructured data

Unstructured files live in internal or external stages. A directory table on a stage stores a catalog of the files, with columns such as RELATIVE_PATH, SIZE, LAST_MODIFIED and FILE_URL.

  • Enable it with DIRECTORY = (ENABLE = TRUE) on the stage, and query it with SELECT * FROM DIRECTORY(@my_stage).
  • Refresh it with ALTER STAGE my_stage REFRESH, or set up automatic refresh for external stages through cloud event notifications.

To give access to a file, Snowflake offers three URL types:

URL typeFunctionLifetimeTypical use
Scoped URLBUILD_SCOPED_FILE_URL24 hoursShort term access for a user or app without granting stage privileges
File URLBUILD_STAGE_FILE_URLPermanentAccess for roles that hold privileges on the stage
Pre-signed URLGET_PRESIGNED_URLSet by an expiration timeDirect download, for example from a BI tool or browser, with no Snowflake login

To extract content from the files, you process them with Snowpark or with Java or Python UDFs that read staged files, or with Snowflake Cortex AI functions such as AI_PARSE_DOCUMENT for text and layout from documents. The extracted values then land in normal tables.

FilesPDF, imagesstored inStagedirectory tableread byUDF or Cortex AIextract textwritesTablestructured result

Common mistakes

Comparing a VARIANT value without a cast. src:status = 'OPEN' compares a VARIANT with a string. Casting first (src:status::STRING = 'OPEN') makes the comparison clear and returns plain text.

Using FLATTEN without LATERAL in the join. FLATTEN needs the row it expands, so it is joined laterally to the source table. Without that link it cannot see each row's array.

Loading unstructured files into a VARIANT column. PDFs and images stay in a stage. You catalog them with a directory table and process them with functions, not by loading them like JSON.

Watch out

A scoped URL works only for the user who generated it and expires after 24 hours. For a link a third party can open without a Snowflake login, use a pre-signed URL.

What to remember for the exam

  • Semi-structured data goes in VARIANT, OBJECT and ARRAY columns; supported formats include JSON, Avro, ORC, Parquet and XML.
  • Path notation: col:a.b, col['a']['b'], col:arr[0]; element names are case sensitive; cast with ::.
  • LATERAL FLATTEN turns arrays into rows; the element is in the VALUE column.
  • PARSE_JSON, OBJECT_CONSTRUCT and ARRAY_AGG build semi-structured values.
  • Unstructured files sit in stages; directory tables list them; scoped, file and pre-signed URLs grant access.
  • MERGE, CTAS and TRY_CAST are the core structured transformation tools.

Check yourself

  • How do you return one row per element of the items array in a JSON column?

    Join the table to LATERAL FLATTEN(input => src:items) and read each element from the VALUE column.

  • Why does src:Name return NULL when the JSON key is name?

    Element names in path notation are case sensitive, so the path must match the key exactly.

  • Which URL lets an external partner download a staged PDF without a Snowflake account?

    A pre-signed URL from GET_PRESIGNED_URL, which grants access until its expiration time.

Topic 5 of 6 in Performance Optimization, Querying, and Transformation

Test yourself on Performance Optimization, Querying, and Transformation

The full Snowflake SnowPro Core COF-C03 question bank has 845 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.