Course contents

SnowPro Core COF-C03 Study GuidePart 4: Data loading and storageSemi-Structured Loading Notes

Semi-Structured Loading Notes

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

Two ways to load the same JSON. Land raw: COPY puts the whole document in one VARIANT column of RAW.EVENTS and you shape it later with views or dynamic tables, so new fields never break the load. Shred at load: COPY extracts typed columns with $1:path::TYPE into CORE.ORDERS_JSON, so a value that can't be cast to its type fails at load time, while a missing field loads as NULL.OPTION 1 · LAND RAWOPTION 2 · SHRED AT LOADFILEorders.json{"order_id": 10001, "customer": {…}, "amount": 42.5}TABLERAW.EVENTSPAYLOAD VARIANT(whole document)EFFECTShape laterviews or dynamic tablesnew fields: no failureTABLECORE.ORDERS_JSONORDER_ID NUMBERCUSTOMER STRING · AMOUNTEFFECTContract enforceduncastable valuefails at load timeCOPY $1$1:path::TYPE

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_JSONORDER_ID NUMBERCUSTOMER STRING · AMOUNT

    EFFECTContract enforceduncastable valuefails at load time

Figure 4.9 Land raw or shred at load. Landing raw keeps the whole document in one VARIANT column and shapes it later (Part 6 covers path syntax and FLATTEN). Shredding at load extracts typed columns during COPY, so records that break the contract fail at the door.

Example

-- Land raw
CREATE 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 load
COPY 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 table
CREATE 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.raw.parquet_fmt')));   -- parquet_fmt: CREATE FILE FORMAT ... TYPE = PARQUET

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

RuleDetail
Value sizeA single VARIANT, ARRAY, OBJECT or VARCHAR value can be up to 128 MB (raised from 16 MB in the 2025_03 behavior change). A VARCHAR column declared without a length holds values up to 16 MB. Declare a larger length (up to 128 MB) for bigger values. BINARY and geospatial values are capped at 64 MB.
Top-level arraysSTRIP_OUTER_ARRAY = TRUE 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.
ConstraintsOn 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.

Exam takeaway
  • 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.