Articles · Snowflake

Snowflake FLATTEN Interview Questions: Empty Arrays, Nested Paths, and Row Multiplication

Practice Snowflake FLATTEN interview questions with empty arrays, nested paths, OUTER behavior, sibling-array multiplication, and trustworthy counts.

Snowflake FLATTEN interview questions are usually questions about row grain: what does one output row represent, which parents disappear, and which measures repeat? A query can successfully expand every array and still produce an incorrect count or revenue total. Start by naming the intended output, then predict its cardinality before writing SQL.

Official behavior below comes from Snowflake documentation checked October 6, 2026. The order data, expected-result ledgers, and interview follow-ups are original practice material. The SQL is documentation-reviewed; local Python checks verify the example arithmetic, not execution inside Snowflake. No candidate report establishes these as a particular employer's assessment questions.

For a useful warm-up, try Reason About Nested JSON Schema Validation. Separate a valid empty collection from an invalid shape before deciding how either should enter a relational result.

What should a strong FLATTEN answer explain first?

FLATTEN is a table function: it turns a compound value into rows. In an interview, distinguish the input's parent identity from the expanded element's identity. An order containing two items can become two rows, each carrying the same order ID and order total. That repetition is useful for attribution and dangerous for aggregation.

Use this opening answer: “I want one row per order item, including a representation of orders with no items. I will preserve the order ID, retain the element index, and keep order-level totals outside the item aggregation.” That answer states a testable contract rather than merely recognizing syntax.

Now clarify whether an empty order should appear in an item report, whether malformed input should fail or enter a quarantine report, and whether repeated item values represent legitimate separate positions. Those decisions determine which transformations are correct. They cannot be inferred from an attractive output table.

Which output columns identify an element?

Official facts: FLATTEN exposes SEQ, KEY, PATH, INDEX, VALUE, and THIS. KEY describes an object entry; INDEX describes an array position; VALUE holds the expanded element. THIS identifies the compound being expanded. SEQ associates output with an input record but is not guaranteed to be ordered or gap-free. FLATTEN reference.

For our order fixture, (order_id, INDEX) is a useful item position. It is not automatically a durable business key: a producer might reorder the array tomorrow. If the payload supplies a stable item ID, preserve it and explain the difference between position and identity.

An interviewer may ask why sorting by SEQ is insufficient. State the output ordering you actually need, such as ORDER BY order_id, item_index. Another follow-up is duplicate values: two identical item objects at different indexes should not be removed unless the business contract says they are duplicate records.

Predict empty-array output before running the query

Use this original fixture. Order A has two items with prices 12 and 8. Order B has an empty array. Order C lacks the items property. Order D has JSON null at that property. Order E contains one item with price 5. Treat each order as an independently stored parent row.

WITH orders AS (
  SELECT column1::VARCHAR AS order_id,
         PARSE_JSON(column2) AS payload
  FROM VALUES
    ('A', '{"items":[{"price":12},{"price":8}]}'),
    ('B', '{"items":[]}'),
    ('C', '{}'),
    ('D', '{"items":null}'),
    ('E', '{"items":[{"price":5}]}')
)
SELECT o.order_id,
       f.index AS item_index,
       f.value:price::NUMBER AS item_price
FROM orders o,
     LATERAL FLATTEN(INPUT => o.payload:items,
                     OUTER => TRUE) f
ORDER BY o.order_id, item_index;

Our expected ledger has six rows: two for A and one each for B, C, D, and E. The three unexpandable cases have no item position or price. Without OUTER => TRUE, the item expansion contributes only A's two rows and E's one row.

Official boundary: OUTER defaults to false; true produces one row for a zero-row expansion, with SQL NULL in its element columns. It does not manufacture a real item. Empty compounds and unavailable or unsuitable compounds also differ in their THIS diagnostics. Keep the original payload when investigating that distinction. FLATTEN reference.

The useful interview follow-up is: “Are these six rows six items?” No. They represent three actual array positions plus three preservation rows. The row count answers a representation question, not the business item count.

Keep missing, JSON null, and empty separate

An empty array says the producer supplied a collection with no entries. A missing property says the key was absent. JSON null is an explicit value inside the payload. Treating all three as “no items” may be acceptable for one metric while concealing an ingestion defect in another.

Official fact: IS_NULL_VALUE detects JSON null in VARIANT data; SQL NULL is distinct and can produce a NULL predicate result. IS_NULL_VALUE reference. For our fixture, inspect the original payload:items and preserve the source order ID before expansion. Do not classify source shape using only the placeholder's VALUE.

Official fact: ARRAY_SIZE returns an array's size; for a VARIANT that is not an array it returns NULL. Sparse arrays require care because size reflects the largest index plus one, not simply the number of defined values. ARRAY_SIZE reference.

Our preparation rule is to create a separate shape-validation result before counting items. Define whether absent fields are permitted and whether explicit null belongs in the same business category as an empty array. A diagnostic query should keep those categories separate even when a downstream display groups them together.

If the interviewer replaces items with an object, ask whether that is a valid alternate schema. FLATTEN can expand objects too; syntactic success does not mean the result has the intended array-item grain. A type check belongs before the transformation when arrays are required.

How do nested paths differ from recursive expansion?

Suppose each order item contains a discounts array. The desired grain is now one discount per item, with both the order ID and item position retained. Expand the items first, then expand i.value:discounts; the second expansion depends on the selected first-level element.

SELECT o.order_id,
       i.index AS item_index,
       d.index AS discount_index,
       d.value::NUMBER AS discount_amount
FROM orders o,
     LATERAL FLATTEN(INPUT => o.payload:items) i,
     LATERAL FLATTEN(INPUT => i.value:discounts) d;

Official facts: lateral expressions can reference preceding table expressions. Snowflake documents chained lateral expansion for dependent nested arrays. Using lateral joins. Path access and casts also have specific semi-structured syntax; JSON key casing matters. Querying semi-structured data.

For an original case with two items, where their discount lengths are two and one, expect three discount rows. The count is the sum of those child lengths. If one item's discount array is empty, it contributes zero discount rows unless that expansion deliberately preserves it.

Recursive expansion instead explores nested sub-elements more broadly. It can be useful for inspecting unknown structure, but it does not automatically produce a business table containing only discounts. Define which paths and types qualify; otherwise container and descendant records may enter the same result. Say why your chosen expansion matches the target grain.

Why do sibling arrays multiply rows?

Consider one order with two items and three tags. Both arrays belong directly to the order. If you independently expand both in the same FROM clause, every item can pair with every tag. The result contains six item-tag combinations, not two items plus three tags.

SELECT o.order_id, i.index AS item_index, t.value AS tag
FROM orders o,
     LATERAL FLATTEN(INPUT => o.payload:items) i,
     LATERAL FLATTEN(INPUT => o.payload:tags) t;

Our two item prices are 12 and 8. Summing item prices over those six combinations yields 60, even though the actual item-price total is 20. This is a grain error; faster execution will not repair it.

If the requirement asks for every item-tag pair, six rows are correct. If it asks for an order's total and tag list, aggregate the arrays independently to one row per order before combining results. If arrays are positionally aligned, confirm that contract and join positions deliberately; unequal lengths need an explicit policy.

Never repair this example with SUM(DISTINCT item_price). Two different items might both cost 12, and their legitimate total would then be 24. Deduplicate identities when warranted; do not deduplicate a measure merely because a join repeated it.

Which counts survive preservation and filtering?

For the five-order fixture, write the expected result before selecting an aggregate.

Quantity	Expected value	Reason
Source orders	5	Five parent records
Expanded rows with OUTER	6	Three positions plus three preservation rows
Actual array positions	3	A contributes two; E contributes one
Orders containing items	2	A and E
Sum of item prices	25	12 + 8 + 5

Official aggregate distinction: COUNT(*) counts rows while COUNT(expression) counts non-NULL expression values. COUNT reference. In this array fixture, COUNT(f.index) counts positions; COUNT(f.value:price) answers a different question if a real item lacks a price.

A later WHERE f.value:price::NUMBER > 10 removes preservation rows and items that fail the predicate. The initial use of OUTER does not make them immune to filtering. If the requirement is “every order, with the count of expensive items,” derive expensive-item aggregates separately and join them to the parent relation.

State another boundary: array positions containing JSON null are still positions. Counting indexes can therefore differ from counting non-null business objects. Define the validity rule before choosing a metric, and show a counterexample rather than promising one aggregate works for every payload.

How should you debug an unexpected result?

Our recommended investigation starts with a single known order. Display its raw payload, expand one level, and inspect index, path, and value. Add the next expansion only after predicting its contribution. Track parent identities as well as output rows so vanished parents remain observable.

If totals inflate, ask which measure's grain is being repeated. If rows disappear, inspect empty paths, source types, OUTER settings, and downstream filters. If a cast fails, inspect the actual value before changing the target type. A tolerant conversion policy should retain evidence of rejected values rather than silently treating them as ordinary zeros.

For performance discussion, reduce irrelevant parent rows and unnecessary projected data when semantically valid, then inspect actual query evidence. Avoid asserting a universal warehouse size or optimization shortcut. Correct cardinality is a prerequisite for interpreting execution cost: an accidental sixfold expansion can dominate a perfectly reasonable scan.

Sources and Further Reading

Documentation checked October 6, 2026. Fixtures and preparation recommendations are original; expected Snowflake results are documentation-derived rather than captured engine output.