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

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

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

Next Steps

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

Related Concepts

• 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