Snowflake Interview Questions and Answers Part 7 - Semi-Structured Data and Iceberg Tables

Snowflake Interview Q&A · Part 7 of 10

In this post we are going to cover semi-structured data and Iceberg tables in Snowflake: loading JSON into VARIANT, FLATTEN, schema changes, typed columns, Iceberg tables and the choice between COPY INTO, an Iceberg table and an external table. These are 7 real Snowflake interview questions, asked the way they come up on a real project, and the last one is a scenario where product events arrive as JSON from three apps with different shapes.

Semi-structured data is data that carries its own structure, like JSON from an API or events from a mobile app. Snowflake stores it in a VARIANT column and lets you query it with plain SQL. Interviewers ask about it because almost every project has some JSON coming in, and the hard parts are the details: arrays, new fields, slow queries, and open formats that other engines need to read.

Part 7 of the Real Time Snowflake Interview Questions and Answers series on TechBrothersIT.

On this page

1. The short answer
2. Question 1: An API sends nested JSON every hour. How do you load it into Snowflake with the VARIANT type?
3. Question 2: The JSON has an array of order lines inside each order. How do you turn it into rows with FLATTEN?
4. Question 3: A new field appears in the JSON and the pipeline must not break. How do you handle schema changes?
5. Question 4: Queries on a big VARIANT column are slow. When do you pull fields out into typed columns?
6. Question 5: The architect wants the lake data in open format, readable by Spark too. What are Iceberg tables in Snowflake?
7. Question 6: Data already sits in S3 as Parquet files. When do you use an external table, an Iceberg table or a COPY INTO?
8. Scenario 7: Product events arrive as JSON from three apps with different shapes. How do you design the raw and reporting layers?

The short answer

Say this first

Land JSON in a VARIANT column (up to 128 MB per value), query it with path notation and casts, and turn arrays into rows with LATERAL FLATTEN. Absorb new fields with VARIANT or schema evolution, pull hot fields into typed columns, and choose COPY INTO, an Iceberg table or an external table by who reads the data and how often.

Keep that in mind. Every question below is one piece of it.

Question 1: An API sends nested JSON every hour. How do you load it into Snowflake with the VARIANT type?

VARIANT is Snowflake's type for semi-structured data. One column holds a whole JSON document, with its nested objects and arrays, and a single value can now be up to 128 MB. The old 16 MB answer is out of date.

Here is one order from the API. It has a customer object and an array of order lines.

order_5001.json

{
  "order_id": 5001,
  "customer": {"name": "Ava Patel", "city": "Austin"},
  "lines": [
    {"sku": "TSHIRT-RED", "qty": 2, "price": 15.00},
    {"sku": "MUG-BLUE", "qty": 1, "price": 9.50}
  ]
}

First, the raw table. It is simple: a VARIANT column for the document, plus the file name and a load time. Then COPY INTO loads the files with a JSON file format. STRIP_OUTER_ARRAY turns a file that holds one big array into one row per element.

load_json.sql

CREATE TABLE raw.orders_json (
  src       VARIANT,
  file_name STRING,
  loaded_at TIMESTAMP_LTZ
            DEFAULT CURRENT_TIMESTAMP());
COPY INTO raw.orders_json (src, file_name)
FROM (SELECT $1, METADATA$FILENAME
      FROM @api_stage/orders/)
FILE_FORMAT = (TYPE = JSON STRIP_OUTER_ARRAY = TRUE);

Then we query it with path notation: the column, a colon, and the path, like customer.name, then a double colon cast to a string. PARSE_JSON turns a JSON string into a VARIANT, and TRY_PARSE_JSON returns NULL instead of failing on bad text.

query_paths.sql

SELECT src:order_id::NUMBER      AS order_id,
       src:customer.name::STRING AS customer,
       src:customer.city::STRING AS city
FROM raw.orders_json;
SELECT PARSE_JSON('{"a": 1}'):a::INT;
SELECT TRY_PARSE_JSON('not json');   -- NULL

For our sample order, that gives one clean row:

Result1 row

order_id	customer	city
5001	Ava Patel	Austin

My tip

Always keep the file name. When a number looks wrong in a report, you can trace it back to the exact file.

Question 2: The JSON has an array of order lines inside each order. How do you turn it into rows with FLATTEN?

FLATTEN is a table function. It takes an array and returns one row per element, so one order with two lines becomes two rows.

Let's look at how it fits in the query. You join it with LATERAL: from the raw table, a comma, then LATERAL FLATTEN with the input set to the lines array. LATERAL means the function runs once for each order row, and it can see that row's columns.

flatten_lines.sql

SELECT o.src:order_id::NUMBER     AS order_id,
       f.index                    AS line_no,
       f.value:sku::STRING        AS sku,
       f.value:qty::NUMBER        AS qty,
       f.value:price::NUMBER(10,2) AS price
FROM raw.orders_json AS o,
     LATERAL FLATTEN(input => o.src:lines) f;

Each output row has two columns we care about:

• value is the element itself, so f.value:sku::STRING gives the product.
• index is its position in the array, so it gives the line number.

Here, order 5001 becomes two rows: index 0 is two red t-shirts, and index 1 is one blue mug.

Result2 rows

order_id	line_no	sku	qty	price
5001	0	TSHIRT-RED	2	15.00
5001	1	MUG-BLUE	1	9.50

If the lines have their own nested array, add a second LATERAL FLATTEN that reads the first one's value.

Now the pitfall. An order with an empty lines array disappears from the result, because there is nothing to flatten. Set OUTER to TRUE to keep it, with nulls in the line columns.

keep_empty_orders.sql

     LATERAL FLATTEN(input => o.src:lines, OUTER => TRUE) f;

Interview point

Mention OUTER => TRUE before they ask. Losing orders with no lines is a quiet bug that shows up as a wrong order count.

Question 3: A new field appears in the JSON and the pipeline must not break. How do you handle schema changes?

The first answer is the design: land the raw data in a VARIANT column. Say Monday's file has order_id, customer and lines, and Tuesday's file adds coupon_code. The new field is simply there in the next document. Nothing fails, and you add it to the typed layer when the business needs it.

When the raw table has typed columns, use schema evolution. First, create the table from the files with INFER_SCHEMA and USING TEMPLATE. Then turn on schema evolution, grant the privilege, and load with MATCH_BY_COLUMN_NAME.

schema_evolution.sql

CREATE FILE FORMAT ff_json TYPE = JSON
  STRIP_OUTER_ARRAY = TRUE;
CREATE TABLE raw.orders USING TEMPLATE (
  SELECT ARRAY_AGG(OBJECT_CONSTRUCT(*))
  FROM TABLE(INFER_SCHEMA(
    LOCATION => '@api_stage/orders/',
    FILE_FORMAT => 'ff_json')));
ALTER TABLE raw.orders SET ENABLE_SCHEMA_EVOLUTION = TRUE;
GRANT EVOLVE SCHEMA ON TABLE raw.orders TO ROLE loader;
COPY INTO raw.orders FROM @api_stage/orders/
  FILE_FORMAT = (FORMAT_NAME = ff_json)
  MATCH_BY_COLUMN_NAME = CASE_INSENSITIVE;

Three things must all be true:

• ENABLE_SCHEMA_EVOLUTION = TRUE on the table
• COPY with MATCH_BY_COLUMN_NAME
• EVOLVE SCHEMA privilege or OWNERSHIP for the loading role

Then a new field becomes a new column automatically.

Know the limits too. By default, one COPY adds at most 100 columns and evolves the schema once. A column missing from new files loses its NOT NULL constraint. And it works with COPY and Snowpipe, not with INSERT or tasks.

Real-project tip

Alert on every evolution, so a renamed field doesn't quietly become a second column.

Question 4: Queries on a big VARIANT column are slow. When do you pull fields out into typed columns?

Snowflake already helps. When it stores VARIANT data, it extracts up to 200 elements per micro-partition into their own columnar sub-columns, so a query on those paths reads less data.

But there is a catch. An element that has even one null, or mixed types, is not extracted. Then every query on it reads the whole document. Setting STRIP_NULL_VALUES = TRUE helps, when a null just means missing.

So the rule is to pull fields into typed columns when they are filtered, joined or aggregated all the time: IDs, dates, status, amounts. Dates and timestamps sit in a VARIANT as plain strings, so a typed timestamp column is smaller, and date filters prune well.

	Keep in the VARIANT	Pull into a typed column
READ HOW OFTEN	rarely, ad hoc	in every report
USED FOR	display, exploration	filters, joins, aggregates
VALUES	sparse, changing shape	nulls, mixed types, dates as strings
EXAMPLES	device info, free text	order_id, order_ts, status, amount

The pattern: keep raw as VARIANT, and build a typed silver table with the casts done once, keeping the original document next to it. Fields that are rarely read can stay in the VARIANT.

silver_orders.sql

CREATE TABLE silver.orders AS
SELECT src:order_id::NUMBER         AS order_id,
       src:order_ts::TIMESTAMP_NTZ  AS order_ts,
       src:status::STRING           AS status,
       src                          AS src_raw
FROM raw.orders_json;

Worth mentioning

Snowflake also has structured types, generally available for standard tables, if you want a typed object instead of a free-form VARIANT.

Question 5: The architect wants the lake data in open format, readable by Spark too. What are Iceberg tables in Snowflake?

An Iceberg table stores its data as Parquet files with Apache Iceberg metadata, an open table format. Snowflake queries it like a normal table, and other engines, like Spark, can work with the same files.

There are two choices to make. The first one is the catalog:

• Snowflake as the catalog, a Snowflake-managed table: you get full read and write, and Snowflake does the maintenance, like compaction.
• An external catalog, through a catalog integration: Snowflake reads it, and can also write to Iceberg REST catalogs.

The second choice is where the files live. An external volume points to your own bucket, with an IAM role. Or you use Snowflake storage, which is now generally available on AWS and Azure.

iceberg_table.sql

CREATE ICEBERG TABLE lake.orders (
  order_id NUMBER(38,0),
  status   STRING,
  order_ts TIMESTAMP_NTZ(6))
  CATALOG = 'SNOWFLAKE'
  EXTERNAL_VOLUME = 'lake_vol'
  BASE_LOCATION = 'orders/';
-- Snowflake storage: EXTERNAL_VOLUME = SNOWFLAKE_MANAGED

Now the Spark part. Horizon Catalog exposes an Iceberg REST endpoint. Spark reads and writes Snowflake-managed tables through it, with the same Snowflake users, roles and policies. So the architect gets open format, and security stays in one place.

The trade-off to mention is storage and Fail-safe:

	Own bucket	Snowflake storage
STORAGE BILL	cloud provider	Snowflake
FAIL-SAFE	no	permanent tables

Trade-off

On your own volume, your cloud provider bills the storage and there is no Fail-safe. On Snowflake storage, permanent tables get Fail-safe.

Question 6: Data already sits in S3 as Parquet files. When do you use an external table, an Iceberg table or a COPY INTO?

It depends on three things: who owns the data, who else reads it, and how often you query it.

	COPY INTO	Iceberg table	External table
DATA	native Snowflake storage	Parquet + Iceberg metadata	files stay in the stage
WRITES	full DML	full DML, Snowflake catalog	read-only
OTHER ENGINES	no	Spark and others	read the raw files
BEST FOR	queried every day	open lakehouse, shared	rare, exploratory reads

Let's go through them one by one. COPY INTO loads the files into a native Snowflake table. Use it when Snowflake is the main engine and the data is queried all the time. You get native storage, Time Travel and the full feature set, and the S3 files are just the landing zone.

copy_parquet.sql

COPY INTO raw.orders FROM @s3_stage/orders/
  FILE_FORMAT = (TYPE = PARQUET)
  MATCH_BY_COLUMN_NAME = CASE_INSENSITIVE;

An Iceberg table is for an open lakehouse. The data stays in an open format, and Spark or other engines read and write the same table.

An external table is the older, read-only option. Snowflake keeps metadata over the files, with a value column, the file name, and partition columns taken from the path. AUTO_REFRESH uses event notifications, and it is billed under Snowpipe.

external_table.sql

CREATE EXTERNAL TABLE ext.orders (
  order_date DATE AS (TO_DATE(
    SPLIT_PART(METADATA$FILENAME, '/', 2))),
  amount NUMBER(10,2) AS (VALUE:amount::NUMBER(10,2)))
PARTITION BY (order_date)
LOCATION = @s3_stage/orders/
AUTO_REFRESH = TRUE
FILE_FORMAT = (TYPE = PARQUET);

Snowflake's own docs now say to consider Iceberg tables instead of external tables, for Parquet.

My rule

Queried every day: COPY INTO. Shared with Spark: Iceberg. Rare or exploratory reads of files you don't own: an external table.

Scenario 7: Product events arrive as JSON from three apps with different shapes. How do you design the raw and reporting layers?

First, let's see the problem. The iOS app sends eventId and userId. The Android app sends event_id and a nested user.id. The web app sends eventId and user.id. Same events, three shapes.

I start with one rule: the raw layer never rejects a document because of its shape. So raw is one table, raw.events: a VARIANT column for the event, plus the app, the file name and the load time, loaded by Snowpipe with auto ingest.

Then silver maps each app's shape into one set of typed columns: event ID, user ID, event type and event time. Where the apps disagree, COALESCE the paths. Use TRY_ functions, so a bad value gives a null, not a failed run. I build it as a dynamic table with a target lag, and dedupe on the event ID with QUALIFY, because apps resend events.

silver_events.sql

CREATE DYNAMIC TABLE silver.events
  TARGET_LAG = '10 minutes' WAREHOUSE = transform_wh AS
SELECT app,
  COALESCE(src:eventId, src:event_id)::STRING AS event_id,
  COALESCE(src:userId, src:user.id)::STRING   AS user_id,
  src:type::STRING                       AS event_type,
  TRY_TO_TIMESTAMP(src:ts::STRING)       AS event_ts
FROM raw.events
QUALIFY ROW_NUMBER() OVER (PARTITION BY event_id
                           ORDER BY loaded_at DESC) = 1;

Gold is a plain star schema, a fact table of events joined to users and products, so dashboards never touch JSON.

To sum up the design:

• Raw never rejects a shape.
• TRY_ functions give a null, not a failed run.
• Arrays, like cart items, get their own child table.
• Data quality: count null keys per app, per hour.

Last step

Count events per app where key fields are null. A jump means an app changed its shape, and you know before the business does.

Key points to remember

• VARIANT holds whole JSON documents, up to 128 MB per value
• LATERAL FLATTEN: one row per array element; OUTER keeps empty arrays
• New fields: VARIANT absorbs them, or schema evolution with MATCH_BY_COLUMN_NAME
• Pull hot, null or mixed-type fields into typed columns
• COPY INTO for daily queries, Iceberg for Spark, external tables for rare reads

Next in this series

Part 8: Incremental Loads, MERGE and SCD

Part of the Real Time Snowflake Interview Questions and Answers series on TechBrothersIT. Features, limits and their status (GA or preview) are current as of October 2026; check the Snowflake documentation for anything you quote in an interview.