Skip to main content

๐Ÿงฉ 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_iduser_nameactioncountry
1aliceloginUS
2bobpurchaseNULL
3carollogoutNULL

๐Ÿ“Œ 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;

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โ€‹

ColumnMeaning
f.keyOBJECT key
f.valueValue
f.indexARRAY 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)โ€‹

FeatureOBJECTARRAY
StructureKeyโ€“ValueList
AccessBy keyBy index
OrderโŒ Noโœ… Yes
Best useSettingsRepeated 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โ€‹

FeatureVARIANTOBJECTARRAY
StructureFlexibleKey-ValueOrdered List
Nestingโœ…โŒโœ…
Index accessโŒโŒโœ…
FLATTEN useโœ…โœ…โœ…

๐ŸŽฏ Choosing the Right Typeโ€‹

ScenarioBest Type
Complex JSONVARIANT
Settings / flagsOBJECT
Lists / tagsARRAY

๐Ÿ“˜ Helpful Functionsโ€‹

FunctionPurpose
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