SnowPro Core · 14% of the exam

Structured, semi-structured, and unstructured data: free practice questions

5 sample questions from our 35-question bank for this domain — answers and explanations included. These are the same scenario-based style as the real Snowflake exam.

1. What is the essential difference between Snowflake's OBJECT type and its VARIANT type?

  • A. OBJECT specifically represents a semi-structured key-value structure (similar to a JSON object), while VARIANT is a more general container that can hold an OBJECT, ARRAY, or any scalar value✓ Correct
  • B. OBJECT and VARIANT are exactly the same type with two different names
  • C. OBJECT can only store numbers, while VARIANT can only store strings
  • D. VARIANT is deprecated in favor of OBJECT
Explanation

OBJECT is specifically a key-value (JSON-object-like) structure, while VARIANT is the broader, general-purpose semi-structured container that can hold an OBJECT, an ARRAY, or a scalar value — VARIANT is effectively the superset type used when the exact shape isn't fixed to 'always an object.' Option B collapses a real distinction. Option C mischaracterizes both types, which are not restricted to a single scalar kind. Option D is false; VARIANT remains a core, actively used type, not deprecated.

2. A query joins a table to the output of FLATTEN(input => t.items) using LATERAL. Why is LATERAL needed here rather than a plain join?

  • A. LATERAL allows the table function's input (t.items) to reference columns from the preceding table in the FROM clause, row by row — a plain join cannot pass per-row values into a table function call✓ Correct
  • B. LATERAL is required only for performance tuning and has no functional effect
  • C. LATERAL converts the VARIANT column into a permanent table automatically
  • D. LATERAL disables the result cache for that query
Explanation

A LATERAL join lets a table function's arguments (like FLATTEN's input) reference columns from a preceding table/row in the same FROM clause, evaluating the function once per input row — this row-by-row correlation is exactly what's needed to flatten an array from each row of t. A plain (non-lateral) join cannot pass per-row correlated values as arguments into a table function call, so LATERAL isn't just a tuning option, it's functionally required for this pattern. Options B, C, and D describe effects LATERAL does not have.

3. Which statement about ORC (Optimized Row Columnar) files and Snowflake is accurate?

  • A. Snowflake supports loading and querying ORC as a semi-structured/columnar source file format, similar in spirit to how it supports Parquet✓ Correct
  • B. ORC files can only be used with Hadoop and are entirely unsupported by Snowflake
  • C. ORC is a proprietary Snowflake-only format not used by any other platform
  • D. ORC files must be converted to JSON before Snowflake can read them
Explanation

Snowflake includes native support for loading and querying ORC files, alongside JSON, Avro, Parquet, and XML, as part of its semi-structured/columnar file format ecosystem. Option B is false — Snowflake explicitly documents ORC as a supported staged file format, not limited to Hadoop-only tooling. Option C is false; ORC originated in the Hadoop/Hive ecosystem and is used broadly, not something Snowflake invented. Option D is false and unnecessary; ORC has direct native support without requiring conversion to JSON first.

4. A table's DATA_RETENTION_TIME_IN_DAYS is set to 1. A user tries to query the table's state from 5 days ago using Time Travel. What happens?

  • A. The query fails or returns unexpected results, because the requested point in time is outside the table's configured Time Travel retention window✓ Correct
  • B. Snowflake automatically extends retention on the fly to satisfy any historical query
  • C. The query succeeds using Fail-safe data, which is always directly queryable by users
  • D. Time Travel retention has no upper bound and always succeeds regardless of the DATA_RETENTION_TIME_IN_DAYS setting
Explanation

Time Travel is only guaranteed to work within the table's configured retention window (DATA_RETENTION_TIME_IN_DAYS); querying a point further back than that (5 days back with only 1 day retained) will fail or not return the expected historical state, since that history is no longer retained for Time Travel querying. Option B is false — retention doesn't dynamically expand for a query. Option C is false; Fail-safe protects data for disaster recovery purposes and is accessed only by Snowflake support/operations, not directly queryable by end users through Time Travel syntax. Option D contradicts the very purpose of the DATA_RETENTION_TIME_IN_DAYS setting, which caps the retrievable window.

5. A table column stores monetary amounts that must never lose precision due to floating-point rounding, such as $19.99 always being stored exactly. Which Snowflake data type family is appropriate?

  • A. FLOAT
  • B. NUMBER (fixed-point, with defined precision and scale)✓ Correct
  • C. VARCHAR
  • D. BOOLEAN
Explanation

NUMBER (Snowflake's fixed-point numeric type, e.g., NUMBER(10,2)) stores exact decimal values with defined precision and scale, avoiding the rounding artifacts that binary floating-point representations can introduce — ideal for currency. FLOAT (option A) is a binary floating-point type and can introduce small rounding errors unsuitable for exact currency values. VARCHAR (option C) would store the amount as text, losing the ability to do reliable arithmetic and comparisons without casting, and risking format inconsistency. BOOLEAN (option D) is irrelevant to storing a monetary value.

30 more questions in this domain

Practice the full bank with instant grading, flashcards, and a timed mock exam.

Start practicing free