10.1 Semi-Structured Data: VARIANT, OBJECT & ARRAY Processing

10.1 Semi-Structured Data: VARIANT, OBJECT & ARRAY Processing

Modern cloud data platforms must ingest, query, and transform vast quantities of semi-structured data—such as JSON event streams, IoT sensor payloads, Avro event logs, and Parquet data lake files—without requiring slow and rigid upfront schema definitions. Snowflake was architected from the ground up to provide native, first-class support for semi-structured data, combining the flexibility of schema-on-read with the query performance, compression, and micro-partition pruning of a columnar relational database.

For the SnowPro Advanced: Architect (ARA-C01) exam, candidates must master semi-structured data types, traversal syntax, casting subtleties, lateral flattening mechanics, null-value discrimination, and the underlying physical storage architecture that enables sub-column columnarization and micro-partition pruning.

---

Semi-Structured Data Types: VARIANT, OBJECT & ARRAY

Snowflake provides three specialized native data types specifically designed to store and manipulate semi-structured hierarchical structures:

                               ┌────────────────────────────────────────┐
                               │              VARIANT                   │
                               │  Universal Semi-Structured Container   │
                               │  Holds any primitive or complex type   │
                               │      (Max 128 MB uncompressed)         │
                               └──────────────────┬─────────────────────┘
                                                  │
                         ┌────────────────────────┴────────────────────────┐
                         ▼                                                 ▼
        ┌─────────────────────────────────┐               ┌─────────────────────────────────┐
        │             OBJECT              │               │             ARRAY               │
        │ Key-Value Dictionary Collection │               │ Ordered, Dense List of Elements │
        │ Keys: String | Values: VARIANT  │               │ Index: 0 to N-1 | Values: VARIANT│
        │ Example: {"city": "San Mateo"}  │               │ Example: ["US", "CA", "UK"]     │
        └─────────────────────────────────┘               └─────────────────────────────────┘

1. VARIANT

The VARIANT type is Snowflake's universal semi-structured container. A VARIANT column can store any Snowflake primitive value (e.g., VARCHAR, NUMBER, BOOLEAN, TIMESTAMP_NTZ) or complex hierarchical structure (OBJECT, ARRAY).

• Size Limit: An individual VARIANT value can hold up to 128 MB of uncompressed data. Very large documents (for example, one huge JSON array) should be split into rows during loading — STRIP_OUTER_ARRAY = TRUE in the JSON file format turns top-level array elements into separate rows.
• Universal Ingestion: Formats including JSON, Avro, ORC, Parquet, and XML can be loaded directly into a VARIANT column without prior parsing.

2. OBJECT

An OBJECT represents an unordered collection of key-value pairs (analogous to a JSON object, Python dictionary, or hash map).

• Keys: Always VARCHAR strings.
• Values: Always of type VARIANT, allowing nested values to be primitives, arrays, or further nested objects.
• Size Limit: An OBJECT value can be up to 128 MB.

3. ARRAY

An ARRAY represents an ordered, zero-indexed sequence of elements (analogous to a JSON array or Python list).

• Indexing: Zero-based (array_col[0] accesses the first element).
• Element Types: Each element is stored as a VARIANT, enabling heterogeneous arrays containing mixed data types (e.g., [100, 'Gold', true, {'discount': 0.15}]).
• Size Limit: The combined size of all values in an ARRAY can be up to 128 MB.

Semi-Structured Data Types Comparison

Traversal Syntax, Path Extraction & Type Casting

Querying nested attributes within semi-structured data relies on two primary traversal conventions: colon notation and bracket notation.

Colon Traversal vs. Bracket Traversal

Consider an ingestion table containing raw customer event logs:

CREATE OR REPLACE TABLE raw_events (
    event_id    VARCHAR(64),
    ingested_at TIMESTAMP_NTZ DEFAULT CURRENT_TIMESTAMP(),
    payload     VARIANT
);

Sample payload JSON value:

{
  "customer": {
    "account-id": "ACC-98421",
    "first name": "Jane",
    "contact": {
      "email": "jane.doe@enterprise.com",
      "phones": ["+1-555-0199", "+1-555-0144"]
    }
  },
  "transaction": {
    "amount": 450.75,
    "currency": "USD",
    "tax_exempt": false
  }
}

1. Colon Notation (:) and Dot Syntax

Used for traversing standard alphanumeric object keys:

SELECT 
    payload:customer.contact.email AS contact_email,
    payload:transaction.amount     AS raw_amount
FROM raw_events;

2. Bracket Notation (['<key>'] or [<index>])

Mandatory when object keys contain special characters, hyphens, spaces, dots, or start with digits, as well as for array index access:

SELECT 
    -- Hyphenated key requires bracket notation
    payload:customer['account-id'] AS account_id,
    -- Key containing spaces requires bracket notation
    payload:customer['first name'] AS first_name,
    -- Array zero-based element access
    payload:customer.contact.phones[0] AS primary_phone
FROM raw_events;

Exam Trap: If you write payload:customer.account-id, Snowflake interprets -id as a subtraction operator rather than part of the key, giving an error or an unexpected result. When a key contains a hyphen, space, or other character that is not valid in an identifier, either use bracket notation (payload:customer['account-id']) or enclose the element name in double quotes (payload:customer."account-id"). Key names are case-sensitive.

Path Extraction Returns VARIANT: The Double-Quote Dilemma

A critical concept on the ARA-C01 exam is that all path extractions using colon or bracket syntax return a VARIANT data type, not a SQL primitive:

-- Query returning uncast VARIANT
SELECT payload:customer.contact.email FROM raw_events;
-- Output: "jane.doe@enterprise.com" (includes literal enclosing double quotes!)

The double quotes appear because the output is a VARIANT that contains a string, not a VARCHAR. Snowflake can still compare it with a literal through implicit conversion, but relying on implicit conversion is fragile:

• Comparisons and sorts between VARIANT values follow VARIANT rules, which can differ from the typed behavior you expect (for example, numbers stored as strings in one file and as numbers in another).
• Joins and GROUP BY on uncast paths carry VARIANT values into results, BI tools, and downstream tables.
• Explicit casts document the intended type and fail loudly (or return NULL with TRY_ functions) when data does not match.

-- Works through implicit conversion, but the intent and type are unclear
SELECT * FROM raw_events WHERE payload:customer.contact.email = 'jane.doe@enterprise.com';

-- Preferred: explicit, typed comparison
SELECT * FROM raw_events WHERE payload:customer.contact.email::VARCHAR = 'jane.doe@enterprise.com';

Explicit Casting (:: and Conversion Functions)

To strip the enclosing JSON quotes and convert semi-structured attributes into first-class relational types, apply explicit casting with the double colon (::) operator or conversion functions:

SELECT 
    payload:customer['account-id']::VARCHAR(32)     AS account_id,
    payload:customer['first name']::VARCHAR(100)    AS first_name,
    payload:customer.contact.email::VARCHAR(256)    AS email,
    payload:customer.contact.phones[0]::VARCHAR(32) AS primary_phone,
    payload:transaction.amount::NUMBER(12,2)        AS txn_amount,
    payload:transaction.tax_exempt::BOOLEAN         AS is_tax_exempt
FROM raw_events
WHERE payload:customer.contact.email::VARCHAR = 'jane.doe@enterprise.com';

Safe Type Casting: TRY_CAST and TRY_TO_*

In real-world ingestion feeds, semi-structured fields often contain corrupted or unexpected data (e.g., an alphabetical string in an amount field). A standard cast (payload:transaction.amount::NUMBER) aborts the entire query upon encountering an invalid conversion. Use TRY_CAST or TRY_TO_NUMBER to return NULL on conversion errors without failing the query:

SELECT 
    event_id,
    -- Aborts on malformed numeric data:
    -- payload:transaction.amount::NUMBER(10,2) AS unsafe_amt,
    
    -- Safely returns NULL on malformed data:
    TRY_CAST(payload:transaction.amount::VARCHAR AS NUMBER(10,2)) AS safe_amount,
    TRY_TO_TIMESTAMP_NTZ(payload:event_timestamp::VARCHAR)        AS safe_timestamp
FROM raw_events;

The Null Dilemma: SQL NULL vs. JSON null

One of the most frequently tested semi-structured concepts on the SnowPro Advanced: Architect exam is the distinction between a SQL NULL and an explicit JSON null literal (IS_NULL_VALUE).

Understanding the Two Types of Nulls

1. SQL NULL (Missing Key or Undefined Path):
   • Occurs when a queried key or path does not exist in the semi-structured object, or when the entire relational column is NULL.
   • Evaluates to TRUE for standard SQL IS NULL predicates.
2. JSON null (null Literal Value):
   • Occurs when a key explicitly exists in the JSON payload with a value of null (e.g., {"secondary_email": null}).
   • In Snowflake, an explicit JSON null is a valid, non-null VARIANT value that represents the JSON literal null.
   • Crucial: A JSON null literal evaluates to FALSE when tested with IS NULL!

Demonstrating the Null Behavior

WITH sample_data AS (
    SELECT PARSE_JSON('{
        "user_id": 101,
        "primary_email": "user@corp.com",
        "secondary_email": null
        -- "phone" key is completely missing
    }') AS v
)
SELECT 
    -- Key is completely missing: SQL NULL
    v:phone IS NULL                    AS phone_is_sql_null,        -- Returns TRUE
    IS_NULL_VALUE(v:phone)             AS phone_is_json_null,       -- Returns FALSE
    
    -- Key exists with explicit null literal: JSON null
    v:secondary_email IS NULL          AS email2_is_sql_null,       -- Returns FALSE (Exam Trap!)
    IS_NULL_VALUE(v:secondary_email)   AS email2_is_json_null,      -- Returns TRUE
    v:secondary_email = PARSE_JSON('null') AS email2_equals_json_null, -- Returns TRUE
    
    -- Casting JSON null to VARCHAR strips the variant wrapper and yields a SQL NULL
    v:secondary_email::VARCHAR IS NULL AS cast_email2_is_sql_null   -- Returns TRUE
FROM sample_data;

Null Handling Decision Matrix

Architect Exam Takeaway: To reliably filter out records where an attribute is either omitted OR explicitly set to JSON null, cast the attribute to a SQL primitive (col:path::VARCHAR IS NOT NULL) or use compound logic: WHERE col:path IS NOT NULL AND NOT IS_NULL_VALUE(col:path).

Relational Transformation Functions

Snowflake provides a suite of specialized functions for parsing, validating, constructing, and decomposing semi-structured data.

Parsing and Validation Functions

1. PARSE_JSON(string_expr):
   • Converts a valid JSON-formatted string into a VARIANT object.
   • If the input string contains invalid JSON syntax, PARSE_JSON aborts the query execution with a parsing error.
2. TRY_PARSE_JSON(string_expr):
   • Safely parses a JSON string into a VARIANT. If parsing fails due to malformed JSON, it returns SQL NULL instead of aborting the query.
   • Architect Recommendation: Always use TRY_PARSE_JSON in ingestion ELT pipelines to isolate corrupted records into an error handling quarantine table.
3. CHECK_JSON(string_expr):
   • Validates a string expression for JSON compliance.
   • Returns NULL if the string is 100% syntactically valid JSON.
   • Returns a detailed error message string describing the exact syntax defect if invalid.

-- Quarantining invalid payloads during staging
INSERT INTO dead_letter_queue (raw_string, error_message)
SELECT 
    raw_payload,
    CHECK_JSON(raw_payload) AS error_reason
FROM landing_stage_raw
WHERE CHECK_JSON(raw_payload) IS NOT NULL;

Construction Functions: OBJECT_CONSTRUCT & ARRAY_AGG

• OBJECT_CONSTRUCT(k1, v1, k2, v2, ...):
  • Constructs an OBJECT from key-value pairs.
  • Omission Rule: Any key whose corresponding value evaluates to a SQL NULL is completely omitted from the resulting object.
• OBJECT_CONSTRUCT_KEEP_NULL(k1, v1, ...):
  • Retains keys whose values evaluate to SQL NULL, explicitly storing them as JSON null literals ("key": null).
• ARRAY_CONSTRUCT(v1, v2, ...):
  • Constructs an ARRAY containing the provided arguments.
• ARRAY_AGG(expr):
  • Aggregate function that rolls multiple relational rows into a single ordered ARRAY grouped by a dimensional key.

-- Aggregating relational order lines into a nested JSON customer document
SELECT 
    c.customer_id,
    OBJECT_CONSTRUCT(
        'customer_id', c.customer_id,
        'customer_name', c.name,
        'orders', ARRAY_AGG(OBJECT_CONSTRUCT(
            'order_id', o.order_id,
            'total_amount', o.total,
            'order_date', o.order_date
        )) WITHIN GROUP (ORDER BY o.order_date DESC)
    ) AS customer_profile_json
FROM customers c
JOIN orders o ON c.customer_id = o.customer_id
GROUP BY c.customer_id, c.name;

---

Unnesting Data with the FLATTEN Table Function

The FLATTEN function is a lateral table function used to explode an ARRAY or OBJECT into individual relational rows. It is almost always paired with the LATERAL join operator.

FLATTEN Syntax and Parameters

TABLE(FLATTEN(
    input     => <variant_array_or_object>,
    path      => '<string_path_inside_input>',
    outer     => TRUE | FALSE,
    recursive => TRUE | FALSE,
    mode      => 'BOTH' | 'ARRAY' | 'OBJECT'
))

Columns Produced by FLATTEN

Every invocation of FLATTEN outputs a deterministic virtual table with six standard columns:

1. seq (INT): A unique sequence number corresponding to the source parent row. All exploded rows derived from the same source row share identical seq numbers.
2. key (VARCHAR): For objects, the key name. For arrays, this is always NULL.
3. path (VARCHAR): The full path to the element being extracted.
4. index (INT): For arrays, the zero-based element index (0, 1, 2...). For objects, this is always NULL.
5. value (VARIANT): The unnested value of the array element or object key. Because it is returned as VARIANT, it requires casting (value::STRING).
6. this (VARIANT): The parent container object or array currently being exploded.

Comprehensive FLATTEN Example: Order Processing

-- Table containing orders with an embedded array of items
CREATE OR REPLACE TABLE raw_orders (
    order_id     VARCHAR(32),
    order_date   DATE,
    order_json   VARIANT
);

-- Sample Query unnesting line items
SELECT 
    r.order_id,
    r.order_date,
    f.index                       AS item_line_number,
    f.value:item_sku::VARCHAR(64) AS sku,
    f.value:quantity::INT         AS quantity,
    f.value:price::NUMBER(10,2)   AS unit_price,
    (f.value:quantity::INT * f.value:price::NUMBER(10,2)) AS line_total
FROM raw_orders r,
LATERAL FLATTEN(input => r.order_json:line_items, outer => TRUE) f
WHERE f.value:price::NUMBER(10,2) > 50.00;

Exam Trap on outer => TRUE: If an order contains zero items (line_items: []) or the line_items key is missing, standard LATERAL FLATTEN(input => ...) acts as an inner join and completely eliminates the order from the result set. To guarantee that orders without line items appear in reporting (e.g., cancelled or pending orders), you must specify outer => TRUE.

Sub-Column Columnarization, Automatic Pruning & Performance

One of the most impressive technical feats of Snowflake's database engine is how it optimizes VARIANT storage under the hood. While developers interact with VARIANT data using schema-on-read JSON semantics, Snowflake physically stores and scans the data using high-performance columnar execution.

Physical Sub-Column Extraction Mechanics

When semi-structured documents are ingested into a VARIANT column within a Snowflake table:

1. Path Frequency Analysis: Snowflake's background micro-partition write engine analyzes the structure of each ingested document across the rows belonging to that micro-partition.
2. Type Detection & Extraction: For paths that appear frequently and demonstrate consistent primitive data types (e.g., payload:customer.id always containing an integer, or payload:device.temp always containing a float), Snowflake automatically extracts those sub-paths into distinct physical columnar sub-columns within the micro-partition file.
3. Columnar Encoding & Compression: The extracted sub-columns are compressed using standard columnar compression algorithms (e.g., dictionary encoding, run-length encoding, bit-packing), identical to first-class relational columns.
4. Fallback Representation: Irregular, highly sparse, or deeply nested dynamic paths remain encoded in Snowflake's optimized internal binary semi-structured format within the micro-partition.

Logical View (VARIANT Column):             Physical Micro-Partition Storage:
┌──────────────────────────────────────┐   ┌──────────────────────────────────────────────┐
│ Row 1: {"id": 10, "geo": "US"}       │   │ Sub-Col: payload:id  -> [10, 11, 12] (INT)   │
│ Row 2: {"id": 11, "geo": "CA"}       │──►│ Sub-Col: payload:geo -> ['US','CA','US']     │
│ Row 3: {"id": 12, "geo": "US"}       │   │ Metadata: min/max per sub-column registered  │
└──────────────────────────────────────┘   └──────────────────────────────────────────────┘

Micro-Partition Metadata & Query Pruning

Because frequently queried sub-paths are physically stored as discrete sub-columns, Snowflake's metadata layer registers min/max statistics for these sub-columns in the micro-partition header.

When a query executes with a filter on a nested attribute:

SELECT * FROM raw_events 
WHERE payload:customer.country::VARCHAR = 'DE';

• The Snowflake Cloud Services query optimizer evaluates the query predicate against the min/max statistics stored for the sub-column payload:customer.country across all micro-partitions.
• Micro-partitions whose sub-column min/max range does not include 'DE' are pruned entirely, without the virtual warehouse reading the micro-partitions from cloud object storage.
• Furthermore, during query execution, the virtual warehouse scans only the extracted sub-column rather than parsing the entire VARIANT payload for every row.

Architectural Decision: Schema-on-Read vs. Flattened Relational Columns

While sub-column extraction provides exceptional out-of-the-box performance, enterprise architects must make deliberate physical modeling choices for high-scale analytical environments:

Exam Best Practice Guidelines for Semi-Structured Modeling

1. Land Raw in VARIANT: Always ingest raw event payloads directly into an ELT landing table with a VARIANT column to ensure 100% data fidelity and avoid load failures caused by schema changes.
2. Materialize High-Frequency Filters and Joins: If specific nested attributes (such as tenant_id, event_date, or user_id) are repeatedly used in JOIN conditions, WHERE clauses, or GROUP BY operations across heavy analytical queries, extract them into explicit relational columns via a transformation pipeline or defined as deterministic virtual columns.
3. Clustering on Semi-Structured Data: If a table must be clustered on an attribute inside a VARIANT, you must explicitly cast the expression in the clustering key definition:

   ALTER TABLE raw_events CLUSTER BY (payload:tenant_id::VARCHAR, payload:event_date::DATE);