What you will be able to do
- Tell structured, semi-structured and unstructured data apart, and say how Snowflake gives SQL access to each one
- Use PARSE_JSON to turn JSON stored as text into a VARIANT value
- Read VARIANT data with colon, dot and bracket notation and with GET_PATH, keeping in mind which parts of a path are case-sensitive
- Use FLATTEN and higher-order functions to turn nested arrays into rows
- Pick the right URL type for giving access to staged unstructured files
Key concept
How many rows come out for each row that goes in — Every transformation technique in this objective changes rows in one of three ways. A scalar or window function returns one row for each input row. An aggregate function collapses many rows into one. FLATTEN expands one row that holds nested data into many rows. If you know which of these you need, you know which tool to use.
1.Three shapes of input data
Transformation work in Snowflake starts from one of three shapes of data. Structured data is the shape most SQL examples assume: typed columns in a table, such as a table defined with x INTEGER, y INTEGER. Semi-structured data is hierarchical, made of nested objects and arrays, and Snowflake stores it in a VARIANT column. The documentation lists where it usually comes from: JSON, Avro, ORC and Parquet. XML is handled by first converting it to an OBJECT value with PARSE_XML. Unstructured data is files, such as images and documents. They sit on a stage and you reach them through URLs, not through columns.
Most of this page covers semi-structured data, because that is where the specialised techniques are. The usual aim is to move semi-structured data towards a relational form, so that ordinary SQL (aggregates, joins, window functions) can work on it.
| Data shape | Where it lives | How SQL reaches it |
|---|---|---|
| Structured | Typed table columns (e.g. INTEGER) | Standard column references |
| Semi-structured (JSON, Avro, ORC, Parquet) | A VARIANT column holding nested ARRAYs and OBJECTs | Colon, dot and bracket path notation; GET / GET_PATH; FLATTEN |
| XML | Converted to an OBJECT value with PARSE_XML | XMLGET and path queries |
| Unstructured | Files on an internal or external stage | Scoped, file or pre-signed URLs |
Path notation only works on a VARIANT value. Sometimes JSON arrives as plain text instead, for example a VARCHAR column holding a raw API response. In that case you have to parse it first. PARSE_JSON takes a string expression that holds valid JSON and returns a VARIANT value. The documentation builds its sample car_sales table exactly this way, with SELECT PARSE_JSON(column1) AS src.
Two details are worth remembering. First, PARSE_JSON rejects duplicate keys in a JSON object by default. An optional parameter allows them, and then the object keeps the last value given for that key. Second, if you pass an empty string or a string of only whitespace, it returns NULL. It does not raise an error.
Checkpoint 1 of 6· Check yourself
A staging table has a VARCHAR column, response_text, that holds complete JSON documents as text. A later step needs to read fields with colon and dot notation. What has to happen first?
PARSE_JSON reads a string that holds JSON and returns a VARIANT value. The path operators need a VARIANT, and so does FLATTEN, which takes VARIANT, OBJECT or ARRAY input.
“Interprets an input string as a JSON document, producing a VARIANT value.”Source: docs.snowflake.com
2.Navigating a VARIANT: colon, dot, bracket and GET_PATH
After the data is in a VARIANT, you read it with path notation. To reach a first-level element, put a colon after the column name, as in src:dealership. To go deeper, add dot steps, as in src:salesperson.name. Bracket notation reaches the same element with each name in single quotes, as in src['salesperson']['name']. Array elements are addressed by index, as in src:vehicle[0].make.
The result of any of these operators is VARIANT, not VARCHAR. That is why query output shows values like "Frank Beasley" inside double quotes: it is a VARIANT that contains a string.
SELECT src['salesperson']['name']
FROM car_sales
ORDER BY 1;Case is the first thing to watch. src:salesperson.name and SRC:salesperson.name are the same path, but SRC:Salesperson.Name is a different one. The rules for JSON keys also differ from the rules for SQL identifiers. If a key contains a space, starts with a digit, or contains symbols, you must put it in double quotes, as in src:"company name" or zipcode_info:"94987".
Path notation is shorthand for the GET and GET_PATH functions. GET_PATH works like a chain of GET calls. Unlike the shorthand, the functions can cope with irregular paths or path elements. Path notation also works on files that are still on a stage, before anything is loaded. You query the stage with a JSON file format and apply the path to $1:
SELECT 'The First Employee Record is '||
S.$1:root[0].employees[0].firstName||
' '||S.$1:root[0].employees[0].lastName
FROM @%customers/contacts.json.gz (file_format => 'my_json_format') as S;Checkpoint 2 of 6· Fill the gap
This query returns the same result as SELECT src:vehicle[0].make FROM car_sales. Which function completes it?
SELECT ? (src, 'vehicle[0].make') FROM car_sales;GET_PATH is the function behind the path shorthand. It takes the VARIANT value and a path string. FLATTEN is a table function that produces rows, and PARSE_JSON parses text.
Source: docs.snowflake.comCheckpoint 3 of 6· Check yourself
A query selects src:dealership and the results appear in double quotes. Why?
The colon, dot and bracket operators all return VARIANT. A VARIANT that holds a string is shown with double quotes around it.
“Operators : and subsequent . and [] always return VARIANT values containing strings.”Source: docs.snowflake.com
Checkpoint 4 of 6· Exam question
A table ORDERS has a VARIANT column PAYLOAD holding each order's JSON, including a nested array `line_items` whose elements are objects with `sku` and `qty` keys. An analyst needs one output row per line item, keeping the parent `order_id` column from ORDERS attached to each row. Which approach correctly produces this result?
Correct answer: A — Join ORDERS to a LATERAL FLATTEN call on `payload:line_items`, then select `order_id` with `value:sku::string` and `value:qty::number` from the flattened rows.
- A. LATERAL FLATTEN explodes a VARIANT array into one row per element while keeping outer columns like `order_id` in scope, which is exactly the join pattern needed to pair each line item with its parent order.
- B. Hardcoding array index offsets only works if every order has the same fixed number of line items, and it silently drops or duplicates rows whenever the array length varies between orders.
- C. Aggregating with ARRAY_AGG followed by picking one element collapses the array back into a single representative value per order, which is the opposite of producing a row per line item.
- D. JSON text does not have a fixed comma-delimited layout that SPLIT_PART can reliably parse, so treating the VARIANT as a flat string breaks as soon as formatting or field order changes.
Sources1
3.From nested arrays to rows: FLATTEN and higher-order functions
Path notation reads one value from each row. But an array such as vehicle or extras can hold any number of entries, and relational SQL wants one row per entry. FLATTEN does this conversion. It is a table function that explodes a compound value into multiple rows. It takes a VARIANT, OBJECT or ARRAY input and produces a lateral view, so it can refer to tables that come before it in the FROM clause. That is why it usually appears as LATERAL FLATTEN.
Its optional arguments control what gets expanded. PATH points at a nested element. RECURSIVE decides whether to go into sub-elements. OUTER decides what happens to input rows that have nothing to expand. With the default, OUTER => FALSE, those rows are left out of the output completely. With OUTER => TRUE, each one still produces a single row, with NULL in the KEY, INDEX and VALUE columns. This works like an outer join: use it when losing parent rows would silently change your counts.
Sometimes you only want to change the values inside an array, not turn them into rows. For that, Snowflake has higher-order functions: FILTER, REDUCE and TRANSFORM. Each one takes an array and a lambda expression, written as an argument, the -> operator, then an expression. For example, a -> a * 2 doubles each element, and a -> a:value > 50 keeps only the elements that match. The documentation presents these as a replacement for code that would otherwise need LATERAL FLATTEN operations or UDFs.
Lambdas have limits. They can only be used as arguments to a higher-order function, and they must be anonymous. Inside one you may use built-in functions, SQL UDFs and uncorrelated scalar subqueries. Aggregate and window functions are not allowed, and neither are non-SQL UDFs, CTE references or correlated subqueries.
Checkpoint 5 of 6· Check yourself
A developer passes a lambda to TRANSFORM. Which of these is NOT allowed inside the lambda?
Lambdas accept built-in functions, but aggregate and window functions are excluded. SQL UDFs and uncorrelated scalar subqueries are allowed.
“Lambda expressions only accept built-in functions (excluding aggregate and window functions), SQL user-defined functions, and uncorrelated scalar subqueries.”Source: docs.snowflake.com
4.Unstructured data: giving access to staged files through URLs
Unstructured files are not stored in VARIANT columns. They stay on a stage, and SQL is used to produce links to them. Snowflake has three URL types, and each one balances control against convenience differently. The question to ask is who needs the file, and whether they can authenticate to Snowflake.
| URL type | How to generate | Access model | Ideal for |
|---|---|---|---|
| Scoped URL | BUILD_SCOPED_FILE_URL | Only roles with privileges on the view that retrieves the URLs; who used the URL and when is recorded in query history | Custom applications, sharing unstructured data with other accounts, analysis in Snowsight |
| File URL | Query the stage's directory table, or call BUILD_STAGE_FILE_URL | Permanent URL; the user sends it in a GET request to the REST API with an authorization token | Custom applications that need access to the files |
| Pre-signed URL | GET_PRESIGNED_URL | Open: anyone can download without authenticating or passing a token | BI and reporting tools that display file contents |
The directory table is the SQL-queryable inventory of a stage. Querying it is one of the two documented ways to get file URLs. The other is BUILD_STAGE_FILE_URL. File URLs are permanent, but they are not public, because every request has to carry an authorization token. Pre-signed URLs are the opposite. They are open, so they suit a dashboard or web page that has to show an image to someone with no Snowflake login. That openness is exactly why they should not be used for files that only certain roles may see. For those, scoped URLs served through a view keep access tied to privileges and leave an audit trail in query history.
Checkpoint 6 of 6· Check yourself
A reporting tool has to show product images from a stage to viewers who never sign in to Snowflake. Which function produces a suitable link?
Pre-signed URLs need no authentication or token, and they are described as ideal for BI and reporting tools. Scoped URLs and file URLs both require Snowflake access.
“Pre-signed URLs are open; any user or application can directly access or download the files.”Source: docs.snowflake.com
Sources4
Exam traps
Each one states something that sounds right. Open it to see what is actually true.
1.Snowflake ignores case everywhere, so SRC:Salesperson.Name finds the same value as src:salesperson.name.Why is that wrong?
Only the column name ignores case. JSON element names have to match exactly, so a path with the wrong case returns nothing.
Covered in Navigating a VARIANT: colon, dot, bracket and GET_PATH
2.A file URL is a public link that anyone can open, just like a pre-signed URL.Why is that wrong?
A file URL is permanent, but each request must go to the REST API with an authorization token. Only pre-signed URLs are open to anyone.
Covered in Unstructured data: giving access to staged files through URLs
Sources
Every claim above is drawn from one of these pages, quoted as it was written on the date shown.
- 1.
“Typically, hierarchical data has been imported into a VARIANT from one of the following supported data formats:”
↩︎ Three shapes of input data“Insert a colon : between the VARIANT column name and any first-level element”
↩︎ Navigating a VARIANT: colon, dot, bracket and GET_PATH“then you must enclose the name in double quotes”
↩︎ Navigating a VARIANT: colon, dot, bracket and GET_PATH“Unlike the path syntax, these functions can handle irregular paths or path elements.”
↩︎ Navigating a VARIANT: colon, dot, bracket and GET_PATH“Without higher-order functions, this type of manipulation requires LATERAL FLATTEN operations or user-defined functions (UDFs).”
↩︎ From nested arrays to rows: FLATTEN and higher-order functions“the column name is case-insensitive but element names are case-sensitive”
↩︎ Exam trap 1“the column name is case-insensitive but element names are case-sensitive”
↩︎ Prediction“Operators : and subsequent . and [] always return VARIANT values containing strings.”
↩︎ Checkpoint“Lambda expressions only accept built-in functions (excluding aggregate and window functions), SQL user-defined functions, and uncorrelated scalar subqueries.”
↩︎ Checkpoint - 2.
“An expression of string type (for example, VARCHAR) that holds valid JSON information.”
↩︎ Three shapes of input data“then the function returns NULL (rather than raising an error)”
↩︎ Three shapes of input data“Interprets an input string as a JSON document, producing a VARIANT value.”
↩︎ Checkpoint - 3.
“FLATTEN can be used to convert semi-structured data to a relational representation.”
↩︎ From nested arrays to rows: FLATTEN and higher-order functions“If TRUE, exactly one row is generated for zero-row expansions (with NULL in the KEY, INDEX, and VALUE columns).”
↩︎ From nested arrays to rows: FLATTEN and higher-order functions - 4.
“Ideal for business intelligence applications or reporting tools that need to display the unstructured file contents.”
↩︎ Unstructured data: giving access to staged files through URLs“Snowflake records information in the query history about who uses a scoped URL to access a file, and when.”
↩︎ Unstructured data: giving access to staged files through URLs“users send the file URL in a GET request to the REST API endpoint along with the authorization token.”
↩︎ Exam trap 2“Pre-signed URLs are open; any user or application can directly access or download the files.”
↩︎ Checkpoint
Also cited
“For a window function, the input is each row within a partition, and the output is one row per input row.”
↩︎ Key concept