Re: Zerobus Variant Data not Processed as Expected...

DB1To3 · Friday

Lets say I have a table receiving zerobus data (json) over HTTP/rest.

That last VARIANT is "ValueJson".

If I want to submit data to it, I would expect to be able to use normal json formatting:

[
    {
        "a": "E",
        "b": 123,
        "c": "xyz", 
        "ValueJson": { "x" :  "y" }


    }
]

Unfortunately it doesn't want that part of the record to be ACTUAL json. The error is like so

Record decoder/encoder error: invalid type: map, expected a string at line 13 column 24. Error Code: 4044, Error State: 3.

Unfortunately the fix is to send json-encoded json (escaped json in a string, since the error says it expected a string).

[
    {
        "a": "E",
        "b": 123,
        "c": "xyz", 
        "ValueJson": "{ \"x\" :  \"y\" }"


    }
]

Does this seem reasonable behavior, or is it a bug? I think it is a bit unnatural to have to encode the json VARIANT instead of just nesting it inside the record as normal json. Ideally the use of this VARIANT wouldn't force us towards using json-encoded-json. It isn't the end of the world, but it seems like a very unfortunate way of sending data from a remote client - one that shouldn't need to have a deep understanding of the eccentricities of the VARIANT-parsing rules for zerobus.

DB1To3 · Friday

I found docs that say I'm supposed to rely on json-encoded-json:

https://docs.databricks.com/aws/en/ingestion/zerobus-concepts#supported-data-types

That is pretty unfortunate. I guess it isn't the end of the world, but it gives a dirty feeling when presenting data to a remote system in this way.

Jen_Evangelista · Friday

Agree it feels unnatural, but you can hide it from the remote client with a thin encoding step right before sending, so producers keep working with normal nested JSON.

A small helper that encodes only the VARIANT columns:

import json

VARIANT_COLUMNS = {"ValueJson"}

def to_zerobus_record(record: dict) -> dict:
return {
k: json.dumps(v) if k in VARIANT_COLUMNS and not isinstance(v, str) else v
for k, v in record.items()
}

record = {"a": "E", "b": 123, "c": "xyz", "ValueJson": {"x": "y"}}
payload = [to_zerobus_record(record)]
# -> [{"a": "E", "b": 123, "c": "xyz", "ValueJson": "{\"x\": \"y\"}"}]

On the Databricks side nothing changes: it lands as a real VARIANT, not a string, so you query it normally:

SELECT
a,
ValueJson:x::string AS x,
variant_get(ValueJson, '$.x', 'string') AS x_alt
FROM catalog.schema.your_table;

If several producers send data, ship this helper in a small shared client package so nobody needs to know about the rule.

View solution in original post

DB1To3 · Friday

Thanks for the tip. Have a nice weekend.

Jen_Evangelista · Monday

Small correction to my earlier helper: skipping Python strings can change their meaning. For a VARIANT field, the string "5" must remain a string, not become the number 5.

The helper below accepts native Python JSON values. Only ValueJson is a VARIANT column; other columns are left unchanged:

import json

VARIANT_COLUMNS = {"ValueJson"}

def to_zerobus_record(record: dict) -> dict:
return {
k: json.dumps(v, allow_nan=False) if k in VARIANT_COLUMNS else v
for k, v in record.items()
}

This preserves the object case from the original answer while correctly encoding strings. If the input is already serialized JSON, decode it explicitly upstream rather than guessing from its Python type. None encodes JSON null; allow_nan=False rejects NaN and Infinity.

Local serialization tests passed; this has not been validated against a live Zerobus service. This clarification was prepared with AI assistance.

Reference: https://docs.databricks.com/aws/en/ingestion/zerobus-concepts#supported-data-types

Zerobus Variant Data not Processed as Expected (json data destined for Variant column).