Querying for Snowflakes
Querying in Snowflake uses standard SQL with extensions for semi-structured data. JSON, Avro and Parquet values are stored in the VARIANT type and read with path notation and functions like FLATTEN. Other features include time travel queries, result caching and common table expressions, which make exploration fast and repeatable.
Stage: Data Loading · Part of the Snowflake roadmap · Last reviewed 19 November 2026
What to learn in Querying
- Load JSON into a VARIANT column and read nested fields with colon notation
- Expand arrays into rows using LATERAL FLATTEN
- Cast VARIANT values to proper types with the :: operator
- Query past data using AT or BEFORE clauses for time travel
- Explain when a query hits the result cache instead of recomputing
Querying interview questions
What does FLATTEN do in Snowflake?
FLATTEN is a table function that turns each element of an array or object in a VARIANT value into its own row. Used with LATERAL, it lets you join an array back to its parent row and filter or aggregate the elements with plain SQL.
What is time travel?
Time travel lets you query, clone or restore data as it existed at an earlier point within the retention period, using AT or BEFORE clauses, and UNDROP for dropped objects. It helps recover from mistakes. The retention length depends on edition and settings.
Where Querying fits in the Snowflake roadmap
Querying belongs to the Data Loading stage (intermediate level). Get data in and out.
Other units in this stage
Back to the full Snowflake roadmap