Snowflake VARIANT and FLATTEN

Modern applications send data as JSON: nested objects, lists, and fields that change from record to record. Snowflake stores that data in a column type called VARIANT and queries it with plain SQL. The FLATTEN function unpacks lists into ordinary rows, so analysts can count, filter, and join nested data like any table.

The Nesting Doll Comparison

A nesting doll holds a smaller doll, which holds a smaller one still. JSON works the same way. An order holds a customer object, and the customer holds an address object. The order holds a list of items. A VARIANT column stores the entire doll set in one cell. Dot notation opens one doll at a time, and FLATTEN lines up every item in the list on the table.

ONE ORDER AS JSON
=================
{
  "order_id": 5001,
  "customer": { "name": "Asha", "city": "Pune" },
  "items": [
    { "sku": "A-100", "qty": 2, "price": 150 },
    { "sku": "B-200", "qty": 1, "price": 400 }
  ]
}

Loading JSON into a VARIANT Column

CREATE OR REPLACE TABLE raw_orders (payload VARIANT);

INSERT INTO raw_orders
SELECT PARSE_JSON('{"order_id":5001,
                    "customer":{"name":"Asha","city":"Pune"},
                    "items":[{"sku":"A-100","qty":2,"price":150},
                             {"sku":"B-200","qty":1,"price":400}]}');

Bulk loads use COPY INTO with a JSON file format, and Snowpipe handles continuous streams.

Reading Nested Values

A colon reaches into the VARIANT, a dot walks down through objects, and a double colon casts the value to a normal type. Square brackets pick an array position.

SELECT payload:order_id::NUMBER            AS order_id,
       payload:customer.name::STRING       AS customer_name,
       payload:customer.city::STRING       AS city,
       payload:items[0].sku::STRING        AS first_sku
FROM raw_orders;

Casting matters. Without the double colon, results keep JSON quotes and the VARIANT type.

FLATTEN: One Row per List Item

SELECT payload:order_id::NUMBER   AS order_id,
       i.index                    AS item_position,
       i.value:sku::STRING        AS sku,
       i.value:qty::NUMBER        AS qty,
       i.value:price::NUMBER      AS price
FROM raw_orders,
     LATERAL FLATTEN(input => payload:items) i;
RESULT AFTER FLATTEN
====================
order_id  item_position  sku    qty  price
--------  -------------  -----  ---  -----
5001      0              A-100  2    150
5001      1              B-200  1    400

One order row became two item rows.

LATERAL tells Snowflake to run FLATTEN once for every row of the left table. FLATTEN returns helpful columns: value holds the element, index holds its position in an array, and key holds the field name when flattening an object.

Useful Companion Functions

FUNCTION                 PURPOSE
-----------------------  ---------------------------------------------
PARSE_JSON(text)         Turn JSON text into a VARIANT
TO_JSON(variant)         Turn a VARIANT back into JSON text
OBJECT_CONSTRUCT(...)    Build a JSON object from key and value pairs
ARRAY_AGG(col)           Collect rows into a JSON array
ARRAY_SIZE(array)        Count elements in a list
TYPEOF(variant)          Report the stored type
GET_PATH(variant, path)  Read a value by a path string
-- Rebuild JSON from rows
SELECT OBJECT_CONSTRUCT('city', city, 'total_orders', COUNT(*))
FROM orders
GROUP BY city;

Turning JSON into a Clean View

Keep the raw JSON untouched and expose a typed view to analysts.

CREATE OR REPLACE VIEW v_order_items AS
SELECT payload:order_id::NUMBER AS order_id,
       payload:customer.name::STRING AS customer_name,
       i.value:sku::STRING AS sku,
       i.value:qty::NUMBER AS qty,
       i.value:qty::NUMBER * i.value:price::NUMBER AS line_total
FROM raw_orders,
     LATERAL FLATTEN(input => payload:items) i;

Analysts query v_order_items with ordinary SQL and never touch JSON paths.

Performance Notes

  • Snowflake splits VARIANT data into columnar sub-parts automatically, so path queries run fast.
  • A single VARIANT value holds up to 16 MB of data in compressed form.
  • Frequently filtered fields deserve their own typed columns in a transformed table.
  • Missing paths return NULL rather than raising an error.

A Practical Scenario

A mobile app sends one JSON event per tap, each with a list of screen elements. The data team lands the events in a VARIANT column, flattens the element list in a view, and counts taps per screen element. When the app adds a new field, existing queries keep working and analysts add the new path whenever they need it.

Frequently Asked Questions

Should I store everything as VARIANT?

VARIANT suits data whose shape changes often. Fields that analysts filter and join daily perform best as typed columns in a transformed table.

What happens when a key is missing?

A missing path returns NULL, so queries keep running when some records lack a field. Add checks for NULL when a field is required for a calculation.

Key Points

  • VARIANT stores JSON, Avro, Parquet, and XML-like data in one column.
  • Colon and dot notation read nested fields; double colon casts types.
  • LATERAL FLATTEN turns list elements into rows.
  • OBJECT_CONSTRUCT and ARRAY_AGG build JSON from rows.
  • A typed view hides JSON paths from analysts.
Previous lesson
Back to course
Next lesson