Semi-structured data support (JSON, Avro, Parquet, ORC, XML)

Semi-Structured Data Support in Snowflake: JSON, Avro, Parquet, ORC, XML

Why Semi-Structured Data Support is Important

In today's data landscape, organizations deal with massive amounts of data that doesn't fit neatly into traditional rows and columns. Semi-structured data formats like JSON, Avro, Parquet, ORC, and XML are prevalent in modern applications, APIs, IoT devices, and data pipelines. Snowflake's native support for these formats eliminates the need for complex ETL processes and allows analysts to work with data in its original form.

What is Semi-Structured Data Support?

Snowflake provides native support for loading, querying, and analyzing semi-structured data through its unique VARIANT data type. This capability allows you to:

• Store semi-structured data in a single column
• Query nested data using dot notation and bracket notation
• Convert between structured and semi-structured formats
• Flatten hierarchical data for analysis

Supported Formats:

JSON (JavaScript Object Notation) - The most common format for web APIs and modern applications

Avro - A row-based format popular in Apache Kafka and data streaming

Parquet - A columnar format optimized for analytical queries

ORC (Optimized Row Columnar) - A columnar format from the Hadoop ecosystem

XML (Extensible Markup Language) - A hierarchical format used in enterprise systems

How It Works

1. The VARIANT Data Type:
Snowflake stores semi-structured data in the VARIANT data type, which can hold up to 16 MB of data per value. VARIANT columns can contain any valid JSON, including objects, arrays, strings, numbers, booleans, and null values.

2. Loading Semi-Structured Data:
Use the COPY INTO command with appropriate file format options. For example:
- FILE_FORMAT = (TYPE = 'JSON')
- FILE_FORMAT = (TYPE = 'PARQUET')
- FILE_FORMAT = (TYPE = 'AVRO')
- FILE_FORMAT = (TYPE = 'ORC')
- FILE_FORMAT = (TYPE = 'XML')

3. Querying Semi-Structured Data:
Access nested elements using colon notation (column:key) or bracket notation (column['key']). Array elements are accessed using index numbers starting at 0.

4. Key Functions:
• PARSE_JSON() - Converts a JSON string to VARIANT
• FLATTEN() - Expands arrays or objects into rows
• LATERAL - Used with FLATTEN for correlated joins
• GET_PATH() - Retrieves values using path expressions
• OBJECT_CONSTRUCT() - Creates JSON objects from key-value pairs
• ARRAY_AGG() - Aggregates values into an array
• STRIP_NULL_VALUE() - Removes null values from output

5. Type Casting:
When extracting values from VARIANT columns, results are returned as VARIANT. Cast to specific types using :: notation (e.g., column:price::NUMBER).

Exam Tips: Answering Questions on Semi-Structured Data Support

Key Concepts to Remember:

1. VARIANT is the foundation - All semi-structured data is stored in VARIANT columns, regardless of the source format

2. 16 MB limit - Each VARIANT value can store up to 16 MB of data

3. Automatic schema detection - Snowflake can infer schema from Parquet, Avro, and ORC files during loading

4. FLATTEN is essential - Questions about working with arrays or nested objects often involve FLATTEN

5. Notation matters - Colon notation (data:field) and bracket notation (data['field']) both work, but bracket notation is required for keys with special characters

6. Case sensitivity - Keys in semi-structured data are case-sensitive by default

7. NULL handling - Missing keys return NULL; use TRY_ functions for safe type conversion

Common Exam Scenarios:

• Questions about which data type stores JSON data - Answer: VARIANT
• Questions about flattening nested arrays - Answer: Use FLATTEN with LATERAL
• Questions about file format support - Remember all five formats: JSON, Avro, Parquet, ORC, XML
• Questions about converting strings to JSON - Answer: PARSE_JSON()
• Questions about size limits - Answer: 16 MB per VARIANT value

Practice These Patterns:

• Extracting scalar values: SELECT data:customer:name::STRING
• Flattening arrays: SELECT f.value FROM table, LATERAL FLATTEN(input => data:items) f
• Creating JSON: SELECT OBJECT_CONSTRUCT('key', value)