Semi-Structured Data: JSON, Parquet, Avro & VARIANT

In the modern data landscape, semi-structured data—JSON logs, Parquet files from data lakes, Avro messages from Kafka—is everywhere. Traditional relational databases struggle with this data because they require a rigid schema upfront. Snowflake solves this with the VARIANT data type, a flexible column that can store any semi-structured format and query it using SQL extensions.

Imagine you're a data engineer at an e-commerce company. Your application sends user clickstream events as JSON objects with varying fields: sometimes a 'product_id', sometimes a 'search_query', and occasionally an 'error_code'. With Snowflake's VARIANT, you can ingest all these events into a single table without preprocessing, then use SQL to extract and analyze the relevant fields on the fly.

In this tutorial, you'll learn how to define VARIANT columns, load JSON/Parquet/Avro data, query nested structures using dot and bracket notation, flatten arrays with FLATTEN, and optimize performance. We'll also cover a real production incident where a missing NULL check caused a data pipeline to fail, and provide a debugging guide for common issues. By the end, you'll be able to handle semi-structured data in Snowflake with confidence.

What is Semi-Structured Data and VARIANT?

Semi-structured data doesn't fit neatly into rows and columns but has tags or markers to separate data elements. Common formats include JSON, Avro, Parquet, ORC, and XML. Snowflake's VARIANT is a universal data type that can hold any of these formats. Internally, Snowflake stores VARIANT data in a compressed, columnar format optimized for query performance.

To define a VARIANT column: ``sql CREATE TABLE events ( event_id INTEGER AUTOINCREMENT, event_data VARIANT, ingested_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP() ); ``

You can also use OBJECT and ARRAY subtypes for stricter typing, but VARIANT is the most flexible.

CREATE TABLE events (
    event_id INTEGER AUTOINCREMENT,
    event_data VARIANT,
    ingested_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP()
);

Loading Semi-Structured Data into Snowflake

Snowflake supports loading semi-structured data from internal or external stages using COPY INTO. The file format must be specified (JSON, Avro, Parquet, ORC, XML).

Example: Loading JSON files from an S3 stage.

First, create a file format: ``sql CREATE FILE FORMAT my_json_format TYPE = JSON COMPRESSION = AUTO STRIP_OUTER_ARRAY = TRUE; ``

Then, copy data: ``sql COPY INTO events (event_data) FROM @my_s3_stage FILE_FORMAT = (FORMAT_NAME = my_json_format) ON_ERROR = 'SKIP_FILE'; ``

For Avro and Parquet, Snowflake automatically infers the schema and maps fields to VARIANT. You can also use VALIDATION_MODE to check for errors before loading.

Tip: Use STRIP_OUTER_ARRAY = TRUE for JSON files that contain an array of objects; it will load each object as a separate row.

-- Create file format
CREATE FILE FORMAT my_json_format
  TYPE = JSON
  COMPRESSION = AUTO
  STRIP_OUTER_ARRAY = TRUE;

-- Load data
COPY INTO events (event_data)
FROM @my_s3_stage
FILE_FORMAT = (FORMAT_NAME = my_json_format)
ON_ERROR = 'SKIP_FILE';

Querying JSON and Nested Data with Dot Notation

Snowflake provides two ways to access fields in a VARIANT: dot notation (:) and bracket notation ([]). Dot notation is simpler for fixed field names.

Example JSON: { "user": { "name": "Alice", "age": 30 }, "items": ["book", "pen"] }

Query: ``sql SELECT event_data:user.name::VARCHAR AS user_name, event_data:user.age::INT AS user_age, event_data:items[0]::VARCHAR AS first_item FROM events; ``

Note the double colon (::) for explicit casting. Without casting, the result remains VARIANT, which may cause issues in joins or aggregations.

For fields with special characters or spaces, use bracket notation with quotes: ``sql SELECT event_data['user name']::VARCHAR FROM events; ``

You can also use the GET function: ``sql SELECT GET(event_data, 'user'):name::VARCHAR FROM events; ``

Performance tip: Explicit casting (::) is faster than relying on implicit conversion.

-- Sample data
INSERT INTO events (event_data)
SELECT PARSE_JSON('{"user":{"name":"Alice","age":30},"items":["book","pen"]}');

-- Query using dot notation
SELECT 
  event_data:user.name::VARCHAR AS user_name,
  event_data:user.age::INT AS user_age,
  event_data:items[0]::VARCHAR AS first_item
FROM events;

Flattening Arrays with FLATTEN and LATERAL FLATTEN

When your data contains arrays, you often need to expand each array element into its own row. Snowflake's FLATTEN function does exactly that.

Example: Each event has an array of 'items'. We want one row per item.

``sql SELECT event_id, f.value::VARCHAR AS item FROM events, LATERAL FLATTEN(input => event_data:items) f; ``

The LATERAL keyword allows the FLATTEN to reference columns from the left side of the join. The output includes a 'value' column containing each array element.

To include rows where the array is empty or NULL, use OUTER => TRUE: ``sql SELECT event_id, f.value::VARCHAR AS item FROM events, LATERAL FLATTEN(input => event_data:items, OUTER => TRUE) f; ``

This returns the event even if the array is empty (with NULL item).

-- Sample data with array
INSERT INTO events (event_data)
SELECT PARSE_JSON('{"user":"Bob","items":["laptop","mouse"]}');

-- Flatten items
SELECT 
  event_id,
  f.value::VARCHAR AS item,
  f.index
FROM events,
LATERAL FLATTEN(input => event_data:items) f;

Working with Parquet and Avro Data

Parquet and Avro are columnar storage formats commonly used in data lakes. Snowflake can query them directly using the same VARIANT approach, but with some differences.

Parquet: Snowflake automatically infers the schema and maps columns to VARIANT. You can query nested fields using dot notation, but Parquet's schema is more rigid than JSON.

Example: Loading Parquet files. ```sql CREATE FILE FORMAT my_parquet_format TYPE = PARQUET;

COPY INTO my_table FROM @stage FILE_FORMAT = (FORMAT_NAME = my_parquet_format); ```

Avro: Similar to Parquet, but Avro files contain the schema within the file. Snowflake reads the schema and maps fields to VARIANT.

For both formats, you can use the INFER_SCHEMA function to get the column definitions: ``sql SELECT * FROM TABLE( INFER_SCHEMA( LOCATION => '@stage', FILE_FORMAT => 'my_parquet_format' ) ); ``

This helps you understand the structure before querying.

-- Infer schema from Parquet files
SELECT * FROM TABLE(
  INFER_SCHEMA(
    LOCATION => '@my_stage',
    FILE_FORMAT => 'my_parquet_format'
  )
);

Performance Optimization for Semi-Structured Queries

Querying VARIANT columns can be slower than querying native columns due to the overhead of parsing and type conversion. Here are best practices:

1. Explicit Casting: Always cast extracted values to native types (::VARCHAR, ::INT). This avoids repeated parsing.
2. Materialized Views: If you frequently query the same field, create a materialized view that extracts it into a native column.
3. ```sql
4. CREATE MATERIALIZED VIEW mv_user_names AS
5. SELECT event_id, event_data:user.name::VARCHAR AS user_name
6. FROM events;
7. ```
8. Limit Nested Access: Deeply nested paths (a:b:c:d) are slower. Flatten the structure if possible.
9. Use WHERE Clauses Early: Filter rows before extracting fields to reduce the amount of data parsed.
10. Avoid SELECT *: Only select the fields you need.
11. Use PARSE_JSON vs TRY_PARSE_JSON: TRY_PARSE_JSON returns NULL for invalid JSON, which is safer but slightly slower. Use PARSE_JSON when data is guaranteed valid.
12. Compression: Snowflake compresses VARIANT data automatically, but you can further optimize by using appropriate file formats (Parquet is more compressed than JSON).

-- Create materialized view for frequently accessed field
CREATE MATERIALIZED VIEW mv_user_names AS
SELECT event_id, event_data:user.name::VARCHAR AS user_name
FROM events;

-- Query the materialized view
SELECT * FROM mv_user_names WHERE user_name IS NOT NULL;

Handling Missing Fields and NULLs Gracefully

Semi-structured data often has missing fields or NULL values. Snowflake provides functions to handle these gracefully.

• TRY_PARSE_JSON: Returns NULL instead of error if JSON is invalid.
• GET: Returns NULL if key doesn't exist.
• COALESCE: Replace NULL with a default.
• IFNULL: Similar to COALESCE for two arguments.

Example: Safely extract a field that may be missing. ``sql SELECT COALESCE(event_data:missing_field::VARCHAR, 'default') AS safe_field FROM events; ``

For arrays, use ARRAY_SIZE to check if an array exists: ``sql SELECT CASE WHEN ARRAY_SIZE(event_data:items) > 0 THEN event_data:items[0]::VARCHAR ELSE NULL END AS first_item FROM events; ``

You can also use FLATTEN with OUTER => TRUE to include rows with empty arrays.

Remember: NULL in VARIANT is different from SQL NULL. Use IS NULL and IS NOT NULL to check.

-- Safe extraction with COALESCE
SELECT 
  COALESCE(event_data:missing_field::VARCHAR, 'N/A') AS field
FROM events;

-- Check array existence
SELECT 
  CASE WHEN ARRAY_SIZE(event_data:items) > 0 
       THEN event_data:items[0]::VARCHAR 
       ELSE NULL 
  END AS first_item
FROM events;

Advanced: Querying Nested Objects and Arrays

Real-world data often has deeply nested structures. Snowflake allows you to navigate these with a combination of dot notation, bracket notation, and FLATTEN.

Example: A JSON object with nested arrays of objects. ``json { "orders": [ { "order_id": 1, "items": [ {"product": "A", "price": 10}, {"product": "B", "price": 20} ] } ] } ``

To get all products and prices: ``sql SELECT o.value:order_id::INT AS order_id, i.value:product::VARCHAR AS product, i.value:price::INT AS price FROM events, LATERAL FLATTEN(input => event_data:orders) o, LATERAL FLATTEN(input => o.value:items) i; ``

This uses two FLATTENs: first to expand orders, then to expand items within each order.

You can also use the PATH and INDEX columns to understand the structure.

For very deep nesting, consider using a recursive CTE, but be cautious of performance.

-- Sample data
INSERT INTO events (event_data)
SELECT PARSE_JSON('{"orders":[{"order_id":1,"items":[{"product":"A","price":10},{"product":"B","price":20}]}]}');

-- Flatten nested arrays
SELECT 
  o.value:order_id::INT AS order_id,
  i.value:product::VARCHAR AS product,
  i.value:price::INT AS price
FROM events,
LATERAL FLATTEN(input => event_data:orders) o,
LATERAL FLATTEN(input => o.value:items) i;