# Inferring Schema from VARIANT Fields in Snowflake

I get asked about this a lot. Someone lands JSON from a REST API into a VARIANT column, and now they want proper columns without manually writing `json_data:field1::VARCHAR`, `json_data:field2::NUMBER` for every single path. And you guessed it - there's no built-in `INFER_SCHEMA` for VARIANT columns like there is for staged files.

The reason? It's genuinely hard. Unlike staged files where Snowflake can sample a few files upfront, VARIANT columns can contain wildly different structures across rows, nested objects go arbitrarily deep, and arrays make things exponentially messier. So far so good - but that doesn't mean we can't build something ourselves.

## The Core Trick: Recursive FLATTEN + TYPEOF

Here's the discovery query that makes everything possible:

```sql
SELECT DISTINCT
    f.path AS original_path,
    UPPER(REPLACE(f.path, '.', '_')) AS column_name,
    TYPEOF(f.value) AS data_type
FROM my_table,
LATERAL FLATTEN(json_data, RECURSIVE => TRUE) f
WHERE TYPEOF(f.value) NOT IN ('OBJECT')
  AND f.path NOT LIKE '%[%'
ORDER BY column_name;
```

`FLATTEN` with `RECURSIVE => TRUE` walks the entire JSON tree and returns every path. `TYPEOF()` tells us what's at each path. The `NOT LIKE '%[%'` filter excludes array contents - I'll explain why in a moment.

Run this against a typical API response and you'll see something like:

| original\_path | column\_name | data\_type |
| --- | --- | --- |
| address.city | ADDRESS\_CITY | VARCHAR |
| address.geo.lat | ADDRESS\_GEO\_LAT | DOUBLE |
| balance | BALANCE | DECIMAL |
| is\_active | IS\_ACTIVE | BOOLEAN |
| orders | ORDERS | ARRAY |
| tags | TAGS | ARRAY |

## Why Arrays Stay as VARIANT

My first instinct was to recursively explode everything. But that creates a mess:

1. Arrays can have different lengths per row - do you create `ITEM_0`, `ITEM_1`, `ITEM_2`... how many?
    
2. Nested arrays explode your row count exponentially
    
3. The resulting schema becomes unpredictable
    

Instead, the approach I landed on keeps top-level arrays as VARIANT columns. You can still query them with `LATERAL FLATTEN` when you need to, but your base schema stays stable. The people who need to dig into arrays can handle that downstream.

## Wrapping It in a Procedure

Once you've got the discovery query working, wrapping it in a stored procedure makes it reusable:

```sql
CREATE OR REPLACE PROCEDURE discover_json_schema(
    source_table VARCHAR,
    variant_column VARCHAR
)
RETURNS ARRAY
LANGUAGE SQL
AS
DECLARE
    schema_array ARRAY;
BEGIN
    SELECT ARRAY_AGG(OBJECT_CONSTRUCT(
        'original_path', original_path,
        'column_name', column_name,
        'sql_type', sql_type
    ))
    INTO schema_array
    FROM (
        SELECT DISTINCT
            f.path AS original_path,
            UPPER(REPLACE(f.path, '.', '_')) AS column_name,
            CASE
                WHEN TYPEOF(f.value) = 'INTEGER' THEN 'NUMBER'
                WHEN TYPEOF(f.value) IN ('DOUBLE', 'DECIMAL') THEN 'FLOAT'
                WHEN TYPEOF(f.value) = 'BOOLEAN' THEN 'BOOLEAN'
                WHEN TYPEOF(f.value) = 'ARRAY' THEN 'VARIANT'
                ELSE 'VARCHAR'
            END AS sql_type
        FROM IDENTIFIER(:source_table),
        LATERAL FLATTEN(IDENTIFIER(:variant_column), RECURSIVE => TRUE) f
        WHERE TYPEOF(f.value) NOT IN ('OBJECT')
          AND f.path NOT LIKE '%[%'
        ORDER BY column_name
    );

    RETURN schema_array;
END;
```

Call it like this:

```sql
CALL discover_json_schema('my_db.my_schema.raw_api_data', 'json_data');
```

Returns an array of objects you can loop through to generate DDL.

```json
[
  {
    "column_name": "EMAIL",
    "original_path": "email",
    "sql_type": "VARCHAR"
  },
  {
    "column_name": "IS_ACTIVE",
    "original_path": "is_active",
    "sql_type": "BOOLEAN"
  },
-- [... and so on ]
]
```

## Generating Views Automatically

The natural next step - a procedure that creates a view with all discovered columns:

```sql
CREATE OR REPLACE PROCEDURE generate_flattened_view(
    source_table VARCHAR,
    variant_column VARCHAR,
    target_view VARCHAR
)
RETURNS VARCHAR
LANGUAGE SQL
AS
DECLARE
    ddl_statement VARCHAR;
    select_cols VARCHAR;
    schema_array ARRAY;
BEGIN
    CALL discover_json_schema(:source_table, :variant_column) INTO schema_array;

    SELECT LISTAGG(
        'GET_PATH(' || :variant_column || ', ''' || s.value:original_path::VARCHAR || ''')::' ||
        s.value:sql_type::VARCHAR || ' AS "' || s.value:column_name::VARCHAR || '"',
        ', '
    ) WITHIN GROUP (ORDER BY s.value:column_name::VARCHAR)
    INTO select_cols
    FROM TABLE(FLATTEN(:schema_array)) s;

    ddl_statement := 'CREATE OR REPLACE VIEW ' || :target_view ||
                     ' AS SELECT ' || select_cols ||
                     ' FROM ' || :source_table;
    EXECUTE IMMEDIATE ddl_statement;

    RETURN 'Created view: ' || :target_view;
END;
```

Now one call flattens your entire JSON structure:

```sql
CALL generate_flattened_view(
    'raw_api_data',
    'json_data',
    'api_data_flat'
);
```

## Dynamic Tables for Auto-Refresh

Same idea, but with Dynamic Tables for continuously refreshing data:

```sql
CREATE OR REPLACE PROCEDURE generate_flattened_dynamic_table(
    source_table VARCHAR,
    variant_column VARCHAR,
    target_dt VARCHAR,
    warehouse VARCHAR DEFAULT 'COMPUTE_WH',
    target_lag VARCHAR DEFAULT '1 hour'
)
RETURNS VARCHAR
LANGUAGE SQL
AS
DECLARE
    ddl_statement VARCHAR;
    select_cols VARCHAR;
    schema_array ARRAY;
BEGIN
    CALL discover_json_schema(:source_table, :variant_column) INTO schema_array;

    SELECT LISTAGG(
        'GET_PATH(' || :variant_column || ', ''' || s.value:original_path::VARCHAR || ''')::' ||
        s.value:sql_type::VARCHAR || ' AS "' || s.value:column_name::VARCHAR || '"',
        ', '
    ) WITHIN GROUP (ORDER BY s.value:column_name::VARCHAR)
    INTO select_cols
    FROM TABLE(FLATTEN(:schema_array)) s;

    ddl_statement := 'CREATE OR REPLACE DYNAMIC TABLE ' || :target_dt ||
                     ' TARGET_LAG = ''' || :target_lag || '''' ||
                     ' WAREHOUSE = ' || :warehouse ||
                     ' AS SELECT ' || select_cols ||
                     ' FROM ' || :source_table;
    EXECUTE IMMEDIATE ddl_statement;

    RETURN 'Created dynamic table: ' || :target_dt;
END;
```

## Caveats

This is a starting point, not a production-ready solution. Things you'll likely need to adjust:

* **Type conflicts**: If the same path has different types across rows (eg. APIs returning `null` vs `0`), the discovery picks one. You might want majority-wins logic or explicit overrides.
    
* **Column name collisions**: `user.id` and `user_id` both become `USER_ID`. Add disambiguation if your data has this. For example you can use a different separator.
    
* **Schema evolution**: New fields in source JSON won't automatically appear. Re-run the procedure or build a scheduled task to detect drift.
    
* **Performance**: Scanning the entire table for schema discovery is expensive. Consider sampling with [`TABLESAMPLE`](https://docs.snowflake.com/en/sql-reference/constructs/sample) or [`LIMIT`](https://docs.snowflake.com/en/sql-reference/constructs/limit) for large tables.
    

## When to Use What

* **One-off exploration**: Run the discovery query directly, eyeball the results
    
* **Stable schema, needs to stay current**: Generate a View
    
* **Performance-critical queries on semi-structured data**: Generate a Dynamic Table
    
* **Complex transformation logic**: Use the Python variant of the procedure and add your business rules
    

The full notebook with all procedures and sample data is available - drop me a line if you want it.