Modeling Semi-Structured Data with the VARIANT Type

1.Choosing a storage pattern for semi-structured data

Before you write a query you have to decide how the data is stored. Databricks describes four patterns for semi-structured data. Three questions decide between them: how often the fields or types in the source change, how many unique fields there are, and whether the workload is optimized for reads or for writes. Whichever pattern you pick, the data should still live in Delta tables.

The table shows the trade-off. A JSON string has no type information, so every query has to re-parse the whole string. A struct gives the fastest reads, but its schema is enforced on write, and it copes poorly with evolving types or new fields. VARIANT sits between them: its schema is as flexible as a JSON string's, but its encoding beats JSON strings on both read and write. Databricks says new users should choose VARIANT whenever the schema is changing or unknown. For data with a well-known schema, structs, maps, and arrays still have a place, because they allow optimized data layout and statistics collection.

Sources1

2.Creating VARIANT columns and promoting hot fields

VARIANT is a standard SQL type, and Delta Lake tables support it. You can declare an empty column directly with CREATE TABLE table_name (variant_column VARIANT). More often you convert existing JSON text with parse_json inside a CTAS. The documented example also pulls one field out into its own column in the same statement:

CREATE TABLE table_name AS
  SELECT json_string:id AS id,
    PARSE_JSON(json_string) variant_column
  FROM source_data

Pulling id out is a deliberate modeling choice, not a convenience. Databricks recommends storing frequently queried fields as separate non-variant columns, to speed up queries and optimize storage layout. It also has to work this way because of hard limits on the type: a VARIANT column cannot be a clustering key, a partition column, or a Z-order key. VARIANT values also cannot be used in comparisons, grouping, ordering, or set operations. So any field you need to cluster on or group by has to exist as a typed column next to the variant.

When the target table already has a VARIANT column, you load new records the same way, using INSERT ... SELECT PARSE_JSON(...). The other routes into the type are CAST, which converts a literal to VARIANT, and to_variant_object, which converts a STRUCT or MAP value (MAP keys must be STRING). Two further constraints affect table design. A single VARIANT record can be at most 16 MB. Iceberg v2 tables do not support VARIANT columns; Iceberg v3 does.

INSERT INTO table_name (variant_column)
  SELECT PARSE_JSON(json_string)
  FROM source_data

Sources23

3.parse_json, try_parse_json, and the round trip to JSON text

parse_json(jsonStr) takes a STRING containing a JSON document and returns the equivalent VARIANT value. The document does not have to be an object: a bare scalar such as '123' parses fine. parse_json is strict, though. It raises an error when the string is malformed, invalid, or too large for a variant. In a pipeline, one bad record would then fail the whole query.

-- Simple example
> SELECT parse_json('{"key": 123, "data": [4, 5, "str"]}');
  {"data":[4,5,"str"],"key":123}

-- Parsing a scalar value
> SELECT parse_json('123');
  123

-- invalid JSON string
> SELECT parse_json('{ bad }');
  Error: MALFORMED_RECORD_IN_PARSING

When you would rather keep the row and handle the failure later, use try_parse_json. It has the same purpose, but it returns NULL when parsing fails. Both functions exist in SQL and in Python (pyspark.sql.functions).

SELECT  ? ('{"a" : invalid, "b" : 2}');

to_json goes the other way and turns a VARIANT back into a STRING. It is the logical inverse of parse_json, but not an exact one. Whitespace is not preserved, key order is arbitrary (the example above came back with data before key), and trailing zeros in numbers may be truncated. A test that compares raw JSON text with to_json(parse_json(...)) can therefore fail even when the data is identical.

Sources45

Exam traps

Each one states something that sounds right. Open it to see what is actually true.

1. 1.You can partition, cluster, or Z-order a table by its VARIANT column, or GROUP BY / ORDER BY a VARIANT value.Why is that wrong?

   VARIANT cannot be a clustering, partition, or Z-order key, and it cannot be used in comparisons, grouping, ordering, or set operations. Extract the field you need into a typed column.

   Covered in Creating VARIANT columns and promoting hot fields

2. 2.to_json(parse_json(s)) always gives back exactly the original string s.Why is that wrong?

   to_json is only the logical inverse of parse_json. Whitespace, key order, and trailing zeros in numbers can all change.

   Covered in parse_json, try_parse_json, and the round trip to JSON text