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
- Semi-structured data goes in
VARIANT,OBJECTandARRAYcolumns; 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 FLATTENturns arrays into rows; the element is in theVALUEcolumn.
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,OBJECTandARRAYcolumns. - 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 ... SELECTto append, andMERGEto 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)orx::type, andTRY_CASTto return NULL instead of failing on bad values.
Semi-structured data
A VARIANT column can hold any value, including nested objects and arrays. You navigate it with path notation:
| Syntax | Example | Meaning |
|---|---|---|
| Colon then dot | src:customer.name | First level after the colon, nested levels after dots |
| Brackets | src['customer']['name'] | Same, useful for names with spaces or special characters |
| Array index | src:items[0] | First element, indexes start at 0 |
| Cast | src:customer.name::STRING | Turns 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
nullvalue 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.
Going the other way, functions build semi-structured values:
PARSE_JSONturns a JSON string into a VARIANT;TRY_PARSE_JSONreturns NULL on invalid input.OBJECT_CONSTRUCTbuilds an OBJECT from key and value pairs.ARRAY_AGGandOBJECT_AGGaggregate 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.
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 withSELECT * 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 type | Function | Lifetime | Typical use |
|---|---|---|---|
| Scoped URL | BUILD_SCOPED_FILE_URL | 24 hours | Short term access for a user or app without granting stage privileges |
| File URL | BUILD_STAGE_FILE_URL | Permanent | Access for roles that hold privileges on the stage |
| Pre-signed URL | GET_PRESIGNED_URL | Set by an expiration time | Direct 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.
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.
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,OBJECTandARRAYcolumns; 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 FLATTENturns arrays into rows; the element is in theVALUEcolumn.PARSE_JSON,OBJECT_CONSTRUCTandARRAY_AGGbuild semi-structured values.- Unstructured files sit in stages; directory tables list them; scoped, file and pre-signed URLs grant access.
MERGE, CTAS andTRY_CASTare 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.