๐งฉ Working with Semi-Structured Data in Snowflake
Snowflake natively supports semi-structured data using powerful data types like
VARIANT, OBJECT, and ARRAY.
These allow you to ingest, store, and query JSON, Avro, Parquet, XML efficiently โ without rigid schemas.
1. VARIANT โ The Foundation of Semi-Structured Dataโ
โ What is VARIANT?โ
- Stores any semi-structured format (JSON, XML, Avro, Parquet, ORC).
- Supports deep nesting and schema evolution.
- Stored internally in a compressed binary format.
๐ ๏ธ Common Use Casesโ
- API responses & application logs
- Clickstream & event data
- IoT sensor feeds
- Data lake ingestion
๐๏ธ Table Definitionโ
CREATE OR REPLACE TABLE api_logs (
event_id INT,
payload VARIANT
);
๐งพ Insert Sample JSON Dataโ
INSERT INTO api_logs
SELECT
1,
PARSE_JSON('{"user": "alice", "event": "login", "location": {"ip": "192.0.2.1", "country": "US"}}')
UNION ALL
SELECT
2,
PARSE_JSON('{"user": "bob", "event": "purchase",
"items": [{"id": 101, "qty": 2}, {"id": 102, "qty": 1}]}')
UNION ALL
SELECT
3,
PARSE_JSON('{"user": "carol", "event": "logout"}');
๐ Extract Nested Fieldsโ
SELECT
event_id,
payload:user::STRING AS user_name,
payload:event::STRING AS action,
payload:location.country::STRING AS country
FROM api_logs;
๐ฅ๏ธ Outputโ
| event_id | user_name | action | country |
|---|---|---|---|
| 1 | alice | login | US |
| 2 | bob | purchase | NULL |
| 3 | carol | logout | NULL |
๐ Key Notesโ
- Always cast (
::) variant values - Use
PARSE_JSON()when loading - VARIANT can hold OBJECT + ARRAY together
- Supports unlimited nesting
๐จ FLATTEN โ Converting JSON into Rowsโ
FLATTEN() explodes semi-structured data into relational rows.
1๏ธโฃ FLATTEN Top-Level JSON OBJECTโ
SELECT
event_id,
f.key AS json_key,
f.value AS json_value
FROM api_logs,
LATERAL FLATTEN(input => payload) f;
๐ Converts top-level JSON keys into rows.
2๏ธโฃ FLATTEN Nested OBJECT (location)โ
SELECT
event_id,
f.key AS location_key,
f.value::STRING AS location_value
FROM api_logs,
LATERAL FLATTEN(input => payload:location) f;
3๏ธโฃ FLATTEN ARRAY of OBJECTS (items)โ
SELECT
event_id,
i.value:id::INT AS item_id,
i.value:qty::INT AS quantity
FROM api_logs,
LATERAL FLATTEN(input => payload:items) i;
4๏ธโฃ Normalized View (Recommended Pattern)โ
SELECT
event_id,
payload:user::STRING AS user_name,
payload:event::STRING AS event,
i.value:id::INT AS item_id,
i.value:qty::INT AS quantity
FROM api_logs
LEFT JOIN LATERAL FLATTEN(input => payload:items) i;
- โ Preserves non-array rows
- โ Production-friendly design
5๏ธโฃ Flatten Only Purchase Eventsโ
SELECT
event_id,
payload:user::STRING AS user_name,
i.value:id::INT AS item_id,
i.value:qty::INT AS quantity
FROM api_logs
LEFT JOIN LATERAL FLATTEN(input => payload:items) i
WHERE payload:event::STRING = 'purchase';
๐ FLATTEN Output Columnsโ
| Column | Meaning |
|---|---|
f.key | OBJECT key |
f.value | Value |
f.index | ARRAY position (0-based) |
โ Common Mistakesโ
- โ Using
payload[0]on OBJECT - โ Forgetting
LATERAL - โ Assuming FLATTEN returns single row
๐๏ธ 2. OBJECT โ KeyโValue Dataโ
โ What exactly is an OBJECT?โ
An OBJECT represents data in the form:
{
"key1": "value1",
"key2": 123,
"key3": true
}
-
โ Keys must always be strings
-
โ Values can be any type:
- String
- Number
- Boolean
- Array
- Another Object
-
โ Stored internally as VARIANT
๐ Think of OBJECT as:
- A dictionary in Python
- A Map in Java
- A JSON object in APIs
๐ฆ Table Definitionโ
CREATE OR REPLACE TABLE user_settings (
user_id INT,
preferences OBJECT
);
๐งพ Insert Dataโ
INSERT INTO user_settings VALUES
(1, OBJECT_CONSTRUCT('theme','dark','language','en','notifications',true)),
(2, OBJECT_CONSTRUCT('theme','light','language','fr')),
(3, OBJECT_CONSTRUCT('theme','dark','newsletter','subscribed'));
๐ Access Keys Directly (No FLATTEN)โ
SELECT
user_id,
preferences:theme::STRING AS theme,
preferences:newsletter::STRING AS newsletter
FROM user_settings;
๐ FLATTEN OBJECTโ
SELECT
user_id,
f.key AS preference_name,
f.value AS preference_value
FROM user_settings,
LATERAL FLATTEN(input => preferences) f;
๐ Filter Example (Dark Theme Users)โ
SELECT DISTINCT user_id
FROM user_settings,
LATERAL FLATTEN(input => preferences)
WHERE key = 'theme'
AND value::STRING = 'dark';
๐ Aggregate Exampleโ
SELECT
f.key AS preference_name,
COUNT(*) AS user_count
FROM user_settings,
LATERAL FLATTEN(input => preferences) f
GROUP BY f.key;
๐ OBJECT vs ARRAY (Quick Comparison)โ
| Feature | OBJECT | ARRAY |
|---|---|---|
| Structure | KeyโValue | List |
| Access | By key | By index |
| Order | โ No | โ Yes |
| Best use | Settings | Repeated values |
- โ When to Use OBJECT (Decision Guide)
Use OBJECT when:โ
- Keys are known but optional
- Schema may change
- New attributes get added over time
- Data is metadata-style
Avoid OBJECT when:โ
- Order matters
- Data is purely a list
- Frequent joins are needed
๐งฎ 3. ARRAY โ Ordered Listsโ
โ Use Casesโ
- Tags & categories
- Page journeys
- Scores & ratings
๐ฆ Tableโ
CREATE OR REPLACE TABLE user_activity (
user_id INT,
pages ARRAY
);
๐งพ Insert Dataโ
INSERT INTO user_activity
SELECT
101,
ARRAY_CONSTRUCT('Home','Search','Product','Cart')
UNION ALL
SELECT
102,
ARRAY_CONSTRUCT('Home','Product');
๐ Access by Indexโ
SELECT
user_id,
pages[0]::STRING AS first_page,
pages[1]::STRING AS second_page
FROM user_activity;
๐ FLATTEN ARRAYโ
SELECT
user_id,
f.index AS page_order,
f.value::STRING AS page_name
FROM user_activity,
LATERAL FLATTEN(input => pages) f;
๐งพ Final Comparison Tableโ
| Feature | VARIANT | OBJECT | ARRAY |
|---|---|---|---|
| Structure | Flexible | Key-Value | Ordered List |
| Nesting | โ | โ | โ |
| Index access | โ | โ | โ |
| FLATTEN use | โ | โ | โ |
๐ฏ Choosing the Right Typeโ
| Scenario | Best Type |
|---|---|
| Complex JSON | VARIANT |
| Settings / flags | OBJECT |
| Lists / tags | ARRAY |
๐ Helpful Functionsโ
| Function | Purpose |
|---|---|
PARSE_JSON() | Convert string to JSON |
FLATTEN() | Explode arrays/objects |
OBJECT_KEYS() | List object keys |
ARRAY_SIZE() | Count array elements |
IS_ARRAY() / IS_OBJECT() | Type checks |
๐ Summaryโ
- โ Snowflake eliminates schema rigidity
- โ Semi-structured data is first-class
- โ FLATTEN bridges JSON โ SQL
- โ Choose structure based on semantics, not convenience
๐ This is why Snowflake excels in modern data platforms