π§© What is Semi-Structured Data?β
Semi-structured data is data that doesnβt fit neatly into traditional tables (like rows and columns), but still has some organizational structure using tags or keys.
It lies between:
- Structured data: Tabular format (e.g., Excel, SQL tables)
- Unstructured data: Free-form data (e.g., images, audio, plain text)
β Characteristics of Semi-Structured Data:β
- Flexible schema: Not fixed like relational tables.
- Uses key-value pairs, arrays, or nested structures.
- Can vary from record to record.
- Still queryable using modern tools like Snowflake.
π¦ Common Formats:β
- JSON (
{ "name": "Alice", "age": 30 }) - Avro
- Parquet
- ORC
- XML
π Examples:β
1. JSON Object:
{
"id": 1,
"name": "John",
"skills": ["SQL", "Python"],
"address": {
"city": "Chennai",
"zip": 600001
}
}
2. XML:
<employee>
<name>Jane</name>
<role>Developer</role>
</employee>
π‘ Where Itβs Used:β
- Web APIs (JSON responses)
- IoT device logs
- Event-driven architecture
- Config files and user preferences
π§ In Snowflake:β
Snowflake handles semi-structured data using data types like:
VARIANT(stores any semi-structured format)OBJECT(JSON-like key-value)ARRAY(list of values)
You can query, filter, and join on semi-structured fields just like normal columns.
π§© Semi-Structured Data in Snowflake a Overview
Snowflake natively supports semi-structured formats like JSON, Avro, Parquet, ORC, and XML β all through the flexible VARIANT, OBJECT, and ARRAY data types.
π Overview of VARIANT, OBJECT, and ARRAYβ
| Type | Description | Use Case |
|---|---|---|
| VARIANT | Universal container for all formats | Raw ingestion of JSON/XML/etc. |
| OBJECT | Key-value map (like a JSON object) | Structured key-value storage |
| ARRAY | Ordered list (like JSON arrays) | Lists like tags, skills, or items |
ποΈ Create Sample Table with All Three Typesβ
CREATE OR REPLACE TABLE employee_data (
emp_id INT,
emp_name STRING,
profile VARIANT, -- Full JSON profile
preferences OBJECT, -- Structured settings (e.g., UI)
skills ARRAY -- List of strings
);
π οΈ Insert a Sample Recordβ
INSERT INTO employee_data
SELECT
1,
'Alice Johnson',
PARSE_JSON('{
"age": 30,
"address": { "city": "Mumbai", "pin": 400001 },
"is_active": true
}'),
OBJECT_CONSTRUCT('theme', 'dark', 'notifications', true),
ARRAY_CONSTRUCT('SQL', 'Python', 'Snowflake');
π Query JSON with SELECTβ
Use : and . operators to access nested values.
SELECT
emp_name,
profile:"age" AS age,
profile:"address"."city" AS city,
preferences:"theme" AS theme_preference,
skills[0] AS primary_skill
FROM employee_data;
π‘ Use
::TYPEto cast values when filtering or aggregating.
π§Ό Best Practicesβ
| Area | Best Practice |
|---|---|
| Storage | Use VARIANT for flexible schemas, OBJECT/ARRAY when schema is known |
| Ingestion | Use PARSE_JSON() or constructors for structured inserts |
| Querying | Use dot notation and : operator for performance |
| Performance | Flatten large arrays and avoid deeply nested queries |
| Search/Filters | Use ::STRING or ::NUMBER for WHERE filtering |
π Summaryβ
Snowflake makes working with semi-structured data seamless via native support for JSON, flexible data types, and SQL-like querying.
| Operation | Supported? | Notes |
|---|---|---|
| DDL | β Yes | Use VARIANT, OBJECT, or ARRAY |
| DML | β Yes | Supports INSERT, UPDATE, MERGE |
| SQL Querying | β Yes | Use dot-path and : accessors |
| Joins | β Yes | Can join based on JSON fields |
You can load external JSON data using
COPY INTOwith stages like S3 β covered in the data loading module.