VARIANT is Snowflake’s storage type for semistructured values. It can hold JSON and values composed of OBJECT, ARRAY, and additional VARIANT types, allowing data with hierarchical keys and a changing schema to be loaded without first defining a relational schema. A VARIANT value is limited to 16 MB. section 4.1.4 section 4.6
Use a raw staging table to preserve an incoming JSON document alongside operational metadata such as its source filename and load timestamp. Load the document into a VARIANT column with COPY, then query the stored structure before transforming it for downstream relational use. section 4.1.4
use database BAKERY_DB; use schema EXTERNAL_JSON_ORDERS; create table ORDERS_PARK_INN_RAW_STG ( customer_orders variant, source_file_name varchar, load_ts timestamp );
Create a raw staging table with one VARIANT column for the JSON payload and separate columns that record file lineage and load time. section 4.1.4
Select a JSON key with the : operator, such as customer_orders:"Customer". The selected value remains VARIANT, so cast it to the target relational type—for example, ::varchar, ::date, or ::number—when extracting columns. Use conversion functions or operators when a relational result is required. section 4.2 section 4.6
Flatten nested VARIANT data progressively:
- Extract top-level scalar values and retain a nested array when its contents need later expansion. section 4.2
- Apply LATERAL FLATTEN to a VARIANT array; FLATTEN returns one row for each object, while LATERAL retains columns from the surrounding row. section 4.2
- Apply another aliased LATERAL FLATTEN for an array nested inside the first flattened object, then select fields from each VALUE column. section 4.2
Flattening bakery orders
A Park Inn JSON order contains customer and order-date keys plus an Orders array. Flatten Orders to produce delivery-date rows, then flatten each Orders by day array to produce baked-good type and quantity rows. The result can be exposed through a staging view with source-file and load-time metadata. section 4.1.4 section 4.2
Listing 4.4 Selecting values from JSON keys at the third level of the hierarchy
select customer_orders:"Customer"::varchar as customer, customer_orders:"Order date"::date as order_date, CO.value:"Delivery date"::date as delivery_date, DO.value:"Baked good type":: varchar as baked_good_type, DO.value:"Quantity"::number as quantity from ORDERS_PARK_INN_RAW_STG, lateral flatten (input => customer_orders:"Orders") CO, #1 lateral flatten (input => CO.value:"Orders by day") DO; #2
This query demonstrates two LATERAL FLATTEN operations: the first expands Orders, and the second expands Orders by day into typed relational columns. section 4.2
VARIANT also supports partial normalization. For delivery-service orders, extract stable top-level fields such as order ID and order datetime while retaining an Items array as VARIANT when it should remain unparsed in the raw staging layer. section 5.2.2
