Semi-Structured Loading Notes
In shortJSON, Avro, ORC, Parquet and XML load through the same stages and the same COPY. What changes is the destination: usually a VARIANT column that stores the whole document.
The problem
Event payloads change shape over time. If every new field breaks the load, the pipeline is fragile. If nothing is ever checked, bad data arrives silently. Thomas has to pick, per source, between flexibility and a strict contract.
How it works: two loading shapes
FILEorders.json{"order_id": 10001,
"customer": {…},
"amount": 42.5}
- COPY $1
OPTION 1 · LAND RAW
TABLERAW.EVENTSPAYLOAD VARIANT(whole document)
EFFECTShape laterviews or dynamic tablesnew fields: no failure
- $1:path
::TYPE OPTION 2 · SHRED AT LOAD
TABLECORE.
ORDERS_JSON ORDER_ID NUMBERCUSTOMER STRING · AMOUNTEFFECTContract enforceduncastable valuefails at load time
Example
-- Land rawCREATE TABLE sales_db. raw. events (payload VARIANT, src STRING, loaded_at TIMESTAMP_ LTZ); COPY INTO sales_db. raw. events (payload, src, loaded_at) FROM (SELECT $1, METADATA$FILENAME, CURRENT_TIMESTAMP( ) FROM @sales_db. raw. s3_ landing/ events/ ) FILE_FORMAT = (FORMAT_NAME = 'sales_db. );raw. json_ events' -- Shred at loadCOPY INTO sales_db. core. orders_ json (order_id, customer_ name, amount) FROM (SELECT $1:order_ id: : NUMBER, $1: customer. name: : STRING, $1: amount: : NUMBER( 10, 2) FROM @sales_db. raw. s3_ landing/ orders_ json/ ) FILE_FORMAT = (TYPE = JSON);-- Let Parquet files describe the tableCREATE TABLE sales_db. raw. sensor_ readings USING TEMPLATE ( SELECT ARRAY_AGG( OBJECT_ CONSTRUCT( *)) FROM TABLE(INFER_ SCHEMA( LOCATION => '@sales_ db. ,raw. s3_ landing/ sensors/ ' FILE_FORMAT => 'sales_db. ))); -- parquet_fmt: CREATE FILE FORMAT ... TYPE = PARQUETraw. parquet_ fmt'
Expected resultRAW.EVENTS gets one row per JSON record, with the source file name. ORDERS_JSON gets typed columns. SENSOR_READINGS is created with the column names and types INFER_SCHEMA found in the Parquet files.
Limits and constraints
| Rule | Detail |
|---|---|
| Value size | A single VARIANT, ARRAY, OBJECT or VARCHAR value can be up to 128 MB (raised from 16 MB in the 2025_ |
| Top-level arrays | STRIP_ splits a file that is one big [ … ] array into one row per element. Without it, the array loads as one value and can hit the size limit. |
| Constraints | On standard tables, Snowflake enforces only NOT NULL and CHECK constraints. PRIMARY KEY, UNIQUE and FOREIGN KEY are recorded for documentation and tools but never block a load. Hybrid tables (a table type built for fast single-row reads and writes) are the exception: they need a primary key and enforce PRIMARY KEY, UNIQUE and FOREIGN KEY. |
Which shape to use
Land raw
The producer changes its schema often, or you don't yet know which fields matter. Shape later with views or dynamic tables.
Shred at load
The schema is a contract, and a wrongly typed value should fail loudly during the load. A missing field still loads as NULL, unless the target column is NOT NULL.
- Semi-structured records land in VARIANT; shape later or shred at load.
- 128 MB per VARIANT value; STRIP_OUTER_ARRAY splits a top-level array into rows.
- Standard tables enforce only NOT NULL and CHECK; PRIMARY KEY, UNIQUE and FOREIGN KEY never block a load.

