Skip to main content

🧩 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​

TypeDescriptionUse Case
VARIANTUniversal container for all formatsRaw ingestion of JSON/XML/etc.
OBJECTKey-value map (like a JSON object)Structured key-value storage
ARRAYOrdered 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 ::TYPE to cast values when filtering or aggregating.


🧼 Best Practices​

AreaBest Practice
StorageUse VARIANT for flexible schemas, OBJECT/ARRAY when schema is known
IngestionUse PARSE_JSON() or constructors for structured inserts
QueryingUse dot notation and : operator for performance
PerformanceFlatten large arrays and avoid deeply nested queries
Search/FiltersUse ::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.

OperationSupported?Notes
DDLβœ… YesUse VARIANT, OBJECT, or ARRAY
DMLβœ… YesSupports INSERT, UPDATE, MERGE
SQL Queryingβœ… YesUse dot-path and : accessors
Joinsβœ… YesCan join based on JSON fields

You can load external JSON data using COPY INTO with stages like S3 β€” covered in the data loading module.