VARIANT Data Type: Working with Semi-Structured Data in Databricks

What is VARIANT?

VARIANT is Databricks’ data type for storing semi-structured data (JSON, XML, Avro) in a single column. It provides:

Schema flexibility: Handle evolving JSON schemas
Native performance: Optimized storage and querying
SQL/Python access: Extract nested fields easily

Think of VARIANT as a “smart JSON column” that understands structure.

Why Use VARIANT?

Traditional Approach (Flattening)

{"id": 1, "user": {"name": "Alice", "email": "alice@email.com", "address": {"city": "Sydney"}}}

Must flatten into:

id INT, user_name STRING, user_email STRING, user_address_city STRING

Problems:

Schema changes require ALTER TABLE
Nested arrays difficult to handle
Loses original structure

VARIANT Approach

CREATE TABLE events (
    id INT,
    payload VARIANT  -- Stores entire JSON
);

Benefits:

Schema evolution without ALTER TABLE
Query nested fields with : operator
Preserve original JSON structure

VARIANT vs Alternatives Decision Tree

graph TD
    Start["Need to Store JSON Data?"]
    
    Start -->|Yes| Q1["Schema Stable & Known?"]
    Start -->|No| End1["Use Strongly-Typed Columns"]
    
    Q1 -->|Yes| Q2["Need to Query Fields?"]
    Q1 -->|No| UseVariant["Use VARIANT"]
    
    Q2 -->|Yes| Q3["Performance Critical?"]
    Q2 -->|No| UseString["Use STRING Column"]
    
    Q3 -->|Very Critical| UseTyped["Use Strongly-Typed
(Flatten to Columns)"]
    Q3 -->|Normal| Q4["Schema Changes Expected?"]
    
    Q4 -->|Yes| UseVariant
    Q4 -->|No| UseTyped
    
    UseVariant -->|"Best For"| V1["- Evolving schemas
- Exploratory analysis
- Mixed event types
- Rapid prototyping"]
    UseTyped -->|"Best For"| T1["- Production analytics
- Type safety required
- Complex joins
- Stable schemas"]
    UseString -->|"Best For"| S1["- Archival only
- Passthrough data
- Pure archival access"]
    
    style UseVariant fill:#a8e6cf
    style UseTyped fill:#ffd3b6
    style UseString fill:#ffaaa5
    style V1 fill:#e8f5e9
    style T1 fill:#fff3e0
    style S1 fill:#ffebee

VARIANT Storage Format

graph LR
    subgraph JSON_Input["JSON Input"]
        J1["JSON object
id: 100
name: Alice
tags: premium, verified"]
    end
    
    subgraph VARIANT_Storage["VARIANT Internal Structure"]
        V1["Binary-Encoded Tree"]
        V2["🔢 id: 100
(INT)"]
        V3["🔤 name: 'Alice'
(STRING)"]
        V4["📃 tags: ARRAY"]
        V5["🔤 'premium'"]
        V6["🔤 'verified'"]
        
        V1 --> V2
        V1 --> V3
        V1 --> V4
        V4 --> V5
        V4 --> V6
    end
    
    subgraph Benefits["Storage Benefits"]
        B1["✅ Numbers kept as numbers"]
        B2["✅ Paths indexed
for fast access"]
        B3["✅ Compression
applied"]
    end
    
    J1 -->|"parse_json()"| V1
    V1 -.benefits.-> B1
    V1 -.benefits.-> B2
    V1 -.benefits.-> B3
    
    style JSON_Input fill:#e3f2fd
    style VARIANT_Storage fill:#f3e5f5
    style Benefits fill:#e8f5e9

Delta Lake stores VARIANT efficiently:

Numbers stored with their native types preserved
Paths indexed for fast access
Compression applied

JSON Structure Visualization

graph TB
    subgraph Event["Customer Event JSON"]
        Root["Root Object"]
        
        Root --> ID["id: 1"]
        Root --> Time["timestamp: '2025-01-15'"]
        Root --> Event_Obj["event: object"]
        
        Event_Obj --> Type["type: 'purchase'"]
        Event_Obj --> Customer["customer: object"]
        Event_Obj --> Trans["transaction: object"]
        
        Customer --> CID["id: 100"]
        Customer --> CName["name: 'Alice'"]
        Customer --> Tier["tier: 'gold'"]
        
        Trans --> Amount["amount: 150.50"]
        Trans --> Currency["currency: 'AUD'"]
        Trans --> Items["items: array"]
        
        Items --> Item1["sku: LAP-001
qty: 1"]
        Items --> Item2["sku: MOU-042
qty: 2"]
    end
    
    subgraph Access["Access Patterns"]
        A1["event:type
→ 'purchase'"]
        A2["event:customer.id
→ 100"]
        A3["event:customer.tier
→ 'gold'"]
        A4["event:transaction.amount
→ 150.50"]
        A5["event:transaction.items.0.sku
→ LAP-001"]
    end
    
    Type -.->|colon operator| A1
    CID -.->|colon operator| A2
    Tier -.->|colon operator| A3
    Amount -.->|colon operator| A4
    Item1 -.->|array indexing| A5
    
    style Event fill:#e3f2fd
    style Access fill:#fff3e0

Basic VARIANT Operations

Creating Tables with VARIANT

CREATE TABLE learning_catalog.sales.customer_events (
    event_id BIGINT,
    event_time TIMESTAMP,
    event_data VARIANT  -- Semi-structured payload
) USING DELTA;

Inserting JSON Data

-- Direct JSON insertion
INSERT INTO learning_catalog.sales.customer_events
VALUES (
    1,
    current_timestamp(),
    parse_json('{"customer_id": 100, "action": "purchase", "amount": 150.50, "items": ["laptop", "mouse"]}')
);

-- From file (common pattern)
COPY INTO learning_catalog.sales.customer_events
FROM '/Volumes/learning_catalog/sales/landing_files/events/'
FILEFORMAT = JSON;

Extracting Fields with : Operator

-- Extract top-level fields
SELECT 
    event_id,
    event_data:customer_id AS customer_id,
    event_data:action AS action,
    event_data:amount AS amount
FROM learning_catalog.sales.customer_events;

-- Extract nested fields
SELECT 
    event_data:user.name AS user_name,
    event_data:user.address.city AS city
FROM learning_catalog.sales.customer_events;

-- Extract from arrays (zero-indexed)
SELECT 
    event_data:items[0] AS first_item,
    event_data:items[1] AS second_item
FROM learning_catalog.sales.customer_events;

Hands-On Exercise 1: Basic VARIANT Operations

Objective

Learn to store, query, and extract data from VARIANT columns.

Step 1: Create Test Data

Create customer_events.json:

{"event_id": 1, "timestamp": "2025-01-15T10:00:00", "event": {"type": "login", "customer": {"id": 100, "name": "Alice Johnson", "tier": "gold"}, "device": {"type": "mobile", "os": "iOS"}}}
{"event_id": 2, "timestamp": "2025-01-15T10:05:00", "event": {"type": "purchase", "customer": {"id": 100, "name": "Alice Johnson", "tier": "gold"}, "transaction": {"amount": 150.50, "currency": "AUD", "items": [{"sku": "LAP-001", "quantity": 1}, {"sku": "MOU-042", "quantity": 2}]}}}
{"event_id": 3, "timestamp": "2025-01-15T10:10:00", "event": {"type": "support_ticket", "customer": {"id": 101, "name": "Bob Smith", "tier": "silver"}, "ticket": {"category": "billing", "priority": "high", "description": "Issue with invoice"}}}
{"event_id": 4, "timestamp": "2025-01-15T10:15:00", "event": {"type": "logout", "customer": {"id": 100, "name": "Alice Johnson", "tier": "gold"}, "session_duration": 900}}

Step 2: Create Table and Load Data

-- Create table with VARIANT column
CREATE TABLE IF NOT EXISTS learning_catalog.sales.customer_events (
    event_id BIGINT,
    timestamp TIMESTAMP,
    event VARIANT
) USING DELTA;

-- Load from file (first upload to volume)
COPY INTO learning_catalog.sales.customer_events
FROM '/Volumes/learning_catalog/sales/landing_files/customer_events/'
FILEFORMAT = JSON
FORMAT_OPTIONS ('inferSchema' = 'true');

-- Verify
SELECT * FROM learning_catalog.sales.customer_events;

Step 3: Query VARIANT Data

-- Extract customer information
SELECT 
    event_id,
    timestamp,
    event:type AS event_type,
    event:customer.id AS customer_id,
    event:customer.name AS customer_name,
    event:customer.tier AS customer_tier
FROM learning_catalog.sales.customer_events;

-- Purchase events only with transaction details
SELECT 
    event_id,
    timestamp,
    event:customer.id AS customer_id,
    event:transaction.amount AS amount,
    event:transaction.currency AS currency,
    event:transaction.items AS items  -- Returns ARRAY
FROM learning_catalog.sales.customer_events
WHERE event:type = 'purchase';

-- Extract individual items from array
SELECT 
    event_id,
    event:customer.id AS customer_id,
    event:transaction.items[0].sku AS item1_sku,
    event:transaction.items[0].quantity AS item1_qty,
    event:transaction.items[1].sku AS item2_sku,
    event:transaction.items[1].quantity AS item2_qty
FROM learning_catalog.sales.customer_events
WHERE event:type = 'purchase';

Step 4: Convert to Strongly-Typed Columns

-- Create typed view for analytics
CREATE OR REPLACE VIEW learning_catalog.sales.customer_events_typed AS
SELECT 
    event_id,
    timestamp,
    CAST(event:type AS STRING) AS event_type,
    CAST(event:customer.id AS BIGINT) AS customer_id,
    CAST(event:customer.name AS STRING) AS customer_name,
    CAST(event:customer.tier AS STRING) AS customer_tier,
    event  -- Keep original for flexibility
FROM learning_catalog.sales.customer_events;

-- Query typed view
SELECT * FROM learning_catalog.sales.customer_events_typed
WHERE customer_tier = 'gold';

Hands-On Exercise 2: Array Explosion with VARIANT

Objective

Flatten nested arrays within VARIANT columns for analytics.

Array Explosion Flow

graph LR
    subgraph Input["Input: Order with Items Array"]
        I1["order_id: 1001
items: [{sku: 'LAP-001', price: 300},
        {sku: 'MOU-042', price: 25}]"]
    end
    
    subgraph Explode["LATERAL VIEW explode()"]
        E1["Explode items array
into separate rows"]
    end
    
    subgraph Output["Output: One Row Per Item"]
        O1["Row 1: order_id: 1001, sku: 'LAP-001', price: 300"]
        O2["Row 2: order_id: 1001, sku: 'MOU-042', price: 25"]
    end
    
    I1 -->|"from_json() + explode()"| E1
    E1 --> O1
    E1 --> O2
    
    style Input fill:#e3f2fd
    style Explode fill:#fff3e0
    style Output fill:#e8f5e9

Step 1: Create Order Data

{"order_id": 1001, "customer_id": 100, "order_date": "2025-01-15", "order": {"total": 350.00, "items": [{"sku": "LAP-001", "name": "Laptop", "price": 300.00, "quantity": 1}, {"sku": "MOU-042", "name": "Mouse", "price": 25.00, "quantity": 2}]}}
{"order_id": 1002, "customer_id": 101, "order_date": "2025-01-16", "order": {"total": 75.00, "items": [{"sku": "KEY-100", "name": "Keyboard", "price": 75.00, "quantity": 1}]}}
{"order_id": 1003, "customer_id": 102, "order_date": "2025-01-17", "order": {"total": 450.00, "items": [{"sku": "MON-200", "name": "Monitor", "price": 350.00, "quantity": 1}, {"sku": "CAB-050", "name": "HDMI Cable", "price": 20.00, "quantity": 5}]}}

Step 2: Load and Explode

-- Create orders table
CREATE TABLE IF NOT EXISTS learning_catalog.sales.orders_raw (
    order_id BIGINT,
    customer_id BIGINT,
    order_date DATE,
    order VARIANT
) USING DELTA;

-- Load data (upload JSON to volume first)
COPY INTO learning_catalog.sales.orders_raw
FROM '/Volumes/learning_catalog/sales/landing_files/orders/'
FILEFORMAT = JSON;

-- Explode items array into rows
SELECT 
    order_id,
    customer_id,
    order_date,
    CAST(order:total AS DECIMAL(10,2)) AS order_total,
    item.sku AS item_sku,
    item.name AS item_name,
    CAST(item.price AS DECIMAL(10,2)) AS item_price,
    CAST(item.quantity AS INT) AS item_quantity,
    CAST(item.price AS DECIMAL(10,2)) * CAST(item.quantity AS INT) AS line_total
FROM learning_catalog.sales.orders_raw
LATERAL VIEW explode(from_json(order:items, 'array<struct<sku:string,name:string,price:double,quantity:int>>')) AS item;

Step 3: Create Flattened Table

-- Materialize flattened data
CREATE OR REPLACE TABLE learning_catalog.sales.order_items AS
SELECT 
    order_id,
    customer_id,
    order_date,
    CAST(order:total AS DECIMAL(10,2)) AS order_total,
    posexplode(from_json(order:items, 'array<struct<sku:string,name:string,price:double,quantity:int>>')) AS (item_index, item)
FROM learning_catalog.sales.orders_raw;

SELECT 
    order_id,
    customer_id,
    item_index,
    item.sku,
    item.name,
    item.price,
    item.quantity,
    item.price * item.quantity AS line_total
FROM learning_catalog.sales.order_items;

Hands-On Exercise 3: Schema Evolution with VARIANT

Objective

Handle evolving JSON schemas without table migrations.

Schema Evolution Timeline

graph TD
    subgraph V1["Version 1 Schema (2025-01)"]
        V1_Fields["Fields:
- type
- customer.id
- customer.name
- page"]
    end
    
    subgraph V2["Version 2 Schema (2025-02)"]
        V2_Fields["Fields:
- type
- customer.id
- customer.name
- customer.email NEW
- page
- metadata NEW
- referrer, user_agent
- geolocation NEW
- country, city, lat/lon"]
    end
    
    subgraph Compatibility["Backward Compatibility"]
        C1["✅ Old queries still work"]
        C2["✅ Schema evolves in place"]
        C3["✅ NULL for missing fields"]
    end
    
    V1 -->|"Schema Evolution"| V2
    V2 --> Compatibility
    
    style V1 fill:#e3f2fd
    style V2 fill:#e8f5e9
    style Compatibility fill:#fff3e0

Step 1: Initial Schema

-- V1 events (simple schema)
INSERT INTO learning_catalog.sales.customer_events VALUES
(5, current_timestamp(), parse_json('{"type": "page_view", "customer": {"id": 103, "name": "Carol White"}, "page": "/products"}'));

-- Query works fine
SELECT 
    event:type AS event_type,
    event:customer.id AS customer_id,
    event:page AS page
FROM learning_catalog.sales.customer_events
WHERE event_id = 5;

Step 2: Schema Evolution (Add New Fields)

-- V2 events (added metadata, geolocation)
INSERT INTO learning_catalog.sales.customer_events VALUES
(6, current_timestamp(), parse_json('{
    "type": "page_view",
    "customer": {"id": 103, "name": "Carol White", "email": "carol@email.com"},
    "page": "/products",
    "metadata": {"referrer": "google.com", "user_agent": "Chrome/98"},
    "geolocation": {"country": "AU", "city": "Brisbane", "lat": -27.4698, "lon": 153.0251}
}'));

-- Old queries still work!
SELECT 
    event:type AS event_type,
    event:customer.id AS customer_id,
    event:page AS page
FROM learning_catalog.sales.customer_events
WHERE event_id = 6;

-- New queries can access new fields
SELECT 
    event:type AS event_type,
    event:customer.id AS customer_id,
    event:customer.email AS customer_email,  -- New field
    event:metadata.referrer AS referrer,     -- New nested object
    event:geolocation.city AS city           -- New nested object
FROM learning_catalog.sales.customer_events
WHERE event_id = 6;

Step 3: Handle Missing Fields Gracefully

-- Query both old and new events (handles nulls automatically)
SELECT 
    event_id,
    event:type AS event_type,
    event:customer.id AS customer_id,
    COALESCE(event:customer.email, 'N/A') AS customer_email,  -- NULL for old events
    COALESCE(event:geolocation.city, 'Unknown') AS city      -- NULL for old events
FROM learning_catalog.sales.customer_events
WHERE event:type = 'page_view'
ORDER BY event_id;

Advanced VARIANT Techniques

1. Complex Filtering

-- Filter by nested field
SELECT * 
FROM customer_events
WHERE event:transaction.amount > 100;

-- Filter by array element
SELECT *
FROM customer_events
WHERE array_contains(event:transaction.items, 'LAP-001');

-- Filter by nested boolean
SELECT *
FROM customer_events
WHERE event:metadata.is_premium = true;

2. Aggregations on VARIANT

-- Sum amounts from VARIANT
SELECT 
    CAST(event:customer.id AS BIGINT) AS customer_id,
    SUM(CAST(event:transaction.amount AS DECIMAL(10,2))) AS total_spent
FROM customer_events
WHERE event:type = 'purchase'
GROUP BY customer_id
ORDER BY total_spent DESC;

-- Count distinct values
SELECT 
    CAST(event:customer.tier AS STRING) AS tier,
    COUNT(DISTINCT CAST(event:customer.id AS BIGINT)) AS customer_count
FROM customer_events
GROUP BY tier;

3. Convert VARIANT to JSON String

-- Back to JSON string for export
SELECT 
    event_id,
    to_json(event) AS event_json
FROM customer_events;

4. Schema Introspection

-- Get all unique event types
SELECT DISTINCT event:type AS event_type
FROM customer_events;

-- Find events with specific field
SELECT event_id, event
FROM customer_events
WHERE event:metadata IS NOT NULL;

Terraform: VARIANT Data Pipeline

# Volume for semi-structured data
resource "databricks_volume" "json_landing" {
  catalog_name = "learning_catalog"
  schema_name  = "sales"
  name         = "json_landing"
  volume_type  = "MANAGED"
  comment      = "Landing zone for JSON/semi-structured data"
}

# Create table with VARIANT
resource "databricks_sql_table" "events_raw" {
  catalog_name = "learning_catalog"
  schema_name  = "sales"
  name         = "events_raw"
  table_type   = "MANAGED"

  column {
    name = "event_id"
    type = "BIGINT"
  }

  column {
    name = "ingestion_time"
    type = "TIMESTAMP"
  }

  column {
    name = "event_payload"
    type = "VARIANT"
  }

  column {
    name = "source_file"
    type = "STRING"
  }

  comment = "Raw events with VARIANT payload for schema flexibility"
}

# DLT pipeline for VARIANT processing
resource "databricks_notebook" "variant_pipeline" {
  path     = "/Shared/variant_pipeline"
  language = "PYTHON"
  content_base64 = base64encode(<<-EOT
import dlt
from pyspark.sql.functions import *

@dlt.table(
    comment="Raw JSON events loaded with Auto Loader"
)
def events_raw():
    return (
        spark.readStream
            .format("cloudFiles")
            .option("cloudFiles.format", "json")
            .option("cloudFiles.schemaLocation", "/tmp/schema/events")
            .load("/Volumes/learning_catalog/sales/json_landing/")
            .select(
                col("event_id"),
                current_timestamp().alias("ingestion_time"),
                to_json(struct("*")).cast("VARIANT").alias("event_payload"),
                input_file_name().alias("source_file")
            )
    )

@dlt.table(
    comment="Typed view of events extracted from VARIANT"
)
def events_typed():
    return (
        dlt.read_stream("events_raw")
            .select(
                col("event_id"),
                col("event_payload:type").cast("string").alias("event_type"),
                col("event_payload:customer.id").cast("bigint").alias("customer_id"),
                col("event_payload:customer.name").cast("string").alias("customer_name"),
                col("event_payload:timestamp").cast("timestamp").alias("event_time"),
                col("event_payload").alias("raw_payload")
            )
    )
EOT
  )
}

resource "databricks_pipeline" "variant_pipeline" {
  name = "variant_processing_pipeline"

  library {
    notebook {
      path = databricks_notebook.variant_pipeline.path
    }
  }

  cluster {
    label       = "default"
    num_workers = 1
  }

  target       = "learning_catalog.sales"
  storage      = "/pipelines/variant"
  continuous   = false
  development  = true
}

VARIANT vs. Alternatives

Approach	When to Use	Pros	Cons
VARIANT	Evolving schemas, exploratory	Schema flexibility, performance	Loose typing
Strongly-Typed	Stable schemas, production	Type safety, optimization	Rigid schema
JSON String	Storage only, no queries	Simple	Text-only access
Parquet Nested	Complex nesting, analytics	Efficient storage	Inflexible schema

Best Practice: Start with VARIANT for ingestion, create typed views for analytics.

Performance Considerations

1. Indexing

VARIANT columns benefit from Bloom filters:

CREATE TABLE events (
    event_id BIGINT,
    event VARIANT
) USING DELTA
TBLPROPERTIES (
    'delta.bloomFilter.enabled' = 'true',
    'delta.bloomFilter.columns' = 'event'
);

2. Partition by Extracted Field

CREATE TABLE events (
    event_id BIGINT,
    event VARIANT,
    event_date DATE GENERATED ALWAYS AS (CAST(event:timestamp AS DATE))
) USING DELTA
PARTITIONED BY (event_date);

3. Materialize Common Queries

-- Create typed table for frequent queries
CREATE TABLE events_typed AS
SELECT 
    event_id,
    CAST(event:type AS STRING) AS event_type,
    CAST(event:customer.id AS BIGINT) AS customer_id,
    event
FROM events_raw;

Troubleshooting

Issue: NULL values when extracting fields

Cause: Incorrect path or field doesn’t exist

Solution:

-- Check if field exists
SELECT event_id, event
FROM events
WHERE event:nonexistent_field IS NULL;

-- Use COALESCE for defaults
SELECT COALESCE(event:customer.email, 'N/A') FROM events;

Issue: Type mismatch errors

Cause: Incorrect CAST

Solution:

-- Safe casting with TRY_CAST
SELECT TRY_CAST(event:amount AS DECIMAL(10,2)) FROM events;

Issue: Poor query performance

Cause: Delta optimization properties left at defaults

Solution: Enable optimization

ALTER TABLE events 
SET TBLPROPERTIES (
    'delta.autoOptimize.optimizeWrite' = 'true',
    'delta.autoOptimize.autoCompact' = 'true'
);

Best Practices

Use VARIANT for ingestion, typed columns for analytics
Create views to expose commonly-accessed fields as typed columns
Add generated columns for frequently-filtered fields
Partition by extracted date for time-series data
Enable Change Data Feed for VARIANT tables to track schema evolution
Document schema versions in table comments
Use try_cast() for defensive type conversions

Next Steps

✅ Complete Exercise 1: Basic VARIANT operations
✅ Complete Exercise 2: Array explosion
✅ Complete Exercise 3: Schema evolution
🔄 Build real-time JSON ingestion pipeline
🔄 Implement schema registry for VARIANT data
🔄 Optimize VARIANT queries with statistics
🔄 Integrate with CDC pipeline

Delta Live Tables: Pipelines for VARIANT processing
Unity Catalog: Metadata for VARIANT columns
Auto Loader: Ingestion of JSON into VARIANT
CDC with apply_changes(): SCD Type 2 with VARIANT payloads

Last Updated: December 12, 2025

VARIANT Data Type: Working with Semi-Structured Data in Databricks

Manan Younas

What is VARIANT?

Why Use VARIANT?

Traditional Approach (Flattening)

VARIANT Approach

VARIANT vs Alternatives Decision Tree

VARIANT Storage Format

JSON Structure Visualization

Basic VARIANT Operations

Creating Tables with VARIANT

Inserting JSON Data

Extracting Fields with : Operator

Hands-On Exercise 1: Basic VARIANT Operations

Objective

Step 1: Create Test Data

Step 2: Create Table and Load Data

Step 3: Query VARIANT Data

Step 4: Convert to Strongly-Typed Columns

Hands-On Exercise 2: Array Explosion with VARIANT

Objective

Array Explosion Flow

Step 1: Create Order Data

Step 2: Load and Explode

Step 3: Create Flattened Table

Hands-On Exercise 3: Schema Evolution with VARIANT

Objective

Schema Evolution Timeline

Step 1: Initial Schema

Step 2: Schema Evolution (Add New Fields)

Step 3: Handle Missing Fields Gracefully

Advanced VARIANT Techniques

1. Complex Filtering

2. Aggregations on VARIANT

3. Convert VARIANT to JSON String

4. Schema Introspection

Terraform: VARIANT Data Pipeline

VARIANT vs. Alternatives

Performance Considerations

1. Indexing

2. Partition by Extracted Field

3. Materialize Common Queries

Troubleshooting

Issue: NULL values when extracting fields

Issue: Type mismatch errors

Issue: Poor query performance

Best Practices

Next Steps

Related Concepts