Variant data type for Delta tables

The Variant data type stores semi-structured, JSON-like data in a binary columnar format inside a Delta table. Instead of storing raw JSON as a STRING and parsing it at read time, store the data once as Variant and query nested fields directly by using dot and colon notation. This approach eliminates parsing overhead for each query.

Variant is a good fit for event data, application logs, API payloads, and other sources where the shape of the data varies or evolves over time.

Important

The Variant data type requires Fabric Spark runtime 2.0 (Delta 4.1) or later.

Why use Variant

Teams often store semi-structured data as a STRING column and call from_json() every time they read it. That pattern repeats parsing work on every query and requires you to define the full schema upfront.

The Variant type improves on this pattern:

  • No repeated parsing — Data is parsed once on write and stored in an efficient binary format, so queries read fields directly.
  • Schema-on-read — You don't need to define every field upfront. New fields appear automatically as the source data changes.
  • Efficient access — The binary columnar encoding lets the engine extract individual fields without deserializing the entire value.

Create a table with a Variant column

Define a column with the VARIANT data type.

CREATE TABLE analytics.events (
  event_id   BIGINT,
  event_time TIMESTAMP,
  data       VARIANT
)

Load Variant data

Use PARSE_JSON() in Spark SQL, or parse_json in PySpark and Scala, to convert a JSON string into a Variant value on write. The following example loads a nested JSON document with objects and an array.

INSERT INTO analytics.events
SELECT
  1 AS event_id,
  TIMESTAMP '2026-07-16 09:30:00' AS event_time,
  PARSE_JSON('{
    "user_id": 4471,
    "action": "checkout",
    "session": { "id": "a1b2c3", "duration.sec": 128 },
    "items": [
      { "sku": "TShirt-01", "qty": 2, "price": 19.99 },
      { "sku": "Mug-07", "qty": 1, "price": 8.50 }
    ],
    "beta_user": true
  }') AS data

When you load from an existing column that already holds JSON text, pass that column to the same function instead of a literal string.

Query Variant fields

You can extract fields from a Variant value in two ways, and both methods are available in Spark SQL:

  • Colon path notation — data:user_id navigates into the Variant. This syntax is shorthand for the variant_get function.
  • The variant_get function — variant_get(data, '$.user_id', 'int') takes the Variant value, a JSONPath-style path that starts with $, and a target type.

To convert an extracted value to a specific type, use the :: operator, which is shorthand for CAST(... AS type). For example, data:user_id::INT is equivalent to CAST(variant_get(data, '$.user_id') AS INT) and to variant_get(data, '$.user_id', 'int').

To escape fields with special characters, use ['<field>']. For example, data:['duration.sec'].

The following examples use colon notation in the Spark SQL tab because it's the most concise form, and the variant_get function in the PySpark and Scala tabs. In Spark SQL, you can use either form.

Access top-level fields

SELECT event_id,
       data:user_id::INT       AS user_id,
       data:action::STRING     AS action,
       data:beta_user::BOOLEAN AS beta_user
FROM   analytics.events

Access nested objects

Chain accessors to reach fields inside nested objects.

SELECT event_id,
       data:session:id::STRING        AS session_id,
       data:session:duration_sec::INT AS duration_sec
FROM   analytics.events

Access array elements

Use a zero-based index to access array elements. Combine it with field accessors to get values inside each element.

SELECT event_id,
       data:items[0]:sku::STRING   AS first_sku,
       data:items[0]:qty::INT      AS first_qty,
       data:items[1]:price::DOUBLE AS second_price
FROM   analytics.events

Filter on Variant fields

Filter and aggregate on extracted values just like regular columns.

SELECT data:action::STRING AS action,
       COUNT(*)            AS event_count
FROM   analytics.events
WHERE  data:beta_user::BOOLEAN = true
GROUP BY data:action::STRING

Handle missing or mismatched fields

Use try_variant_get when a field might be missing or hold a value that can't be cast to the requested type. Instead of failing the query, it returns NULL for those rows.

SELECT event_id,
       try_variant_get(data, '$.items[1].price', 'double') AS second_price
FROM   analytics.events

Inspect the shape of Variant data

Use schema_of_variant to discover the inferred structure of a Variant value, which is helpful when the source schema isn't documented.

SELECT DISTINCT schema_of_variant(data) AS inferred_schema
FROM   analytics.events

Understand the protocol impact

The Variant type requires Delta reader version 3 and writer version 7. Both the reader and writer must support the Variant type table feature.

Important

Enabling the Variant type upgrades the table protocol permanently. Older readers and writers that don't support the Variant type feature can't read or write the table. Test the feature on nonproduction tables first, and verify that every engine and runtime that accesses the table supports Variant before you use it on shared tables.

You can inspect protocol-related metadata by using DESCRIBE DETAIL and by reviewing the table properties in the Delta log.

Best practices

  • Use Variant for genuinely semi-structured or evolving data rather than for well-defined columns that belong in a typed schema.
  • Cast extracted Variant fields to explicit types so downstream consumers get stable, predictable data types.
  • Confirm that all readers and writers support the Variant type feature before you enable it on shared tables.
  • Validate the feature on a nonproduction table first, because the protocol upgrade is permanent.

Related content

  • Last updated on