Querying data in snowflake: A Guide to JSON and Time Travel

 In Part 1 of our guide, we explored Snowflake’s unique architecture, and in Part 2, we learned how to load data. Now comes the most important part: turning that raw data into valuable insights. The primary way we do this is by querying data in Snowflake.

While Snowflake uses standard SQL that will feel familiar to anyone with a database background, it also has powerful extensions and features that set it apart. This guide will cover the fundamentals of querying, how to handle semi-structured data like JSON, and introduce two of Snowflake’s most celebrated features: Zero-Copy Cloning and Time Travel.

The Workhorse: The Snowflake Worksheet

The primary interface for running queries in Snowflake is the Worksheet. It’s a clean, web-based environment where you can write and execute SQL, view results, and analyze query performance.

When you run a query, you are using the compute resources of your selected Virtual Warehouse. Remember, you can have different warehouses for different tasks, ensuring that your complex analytical queries don’t slow down other operations.

Standard SQL: Your Bread and Butter

At its core, querying data in Snowflake involves standard ANSI SQL. All the commands you’re familiar with work exactly as you’d expect.SQL

-- A standard SQL query to find top-selling products by category
SELECT
    category,
    product_name,
    SUM(sale_amount) as total_sales,
    COUNT(order_id) as number_of_orders
FROM
    sales
WHERE
    sale_date >= '2025-01-01'
GROUP BY
    1, 2
ORDER BY
    total_sales DESC;

Beyond Columns: Querying Semi-Structured Data (JSON)

One of Snowflake’s most powerful features is its native ability to handle semi-structured data. You can load an entire JSON object into a single column with the VARIANT data type and query it directly using a simple, SQL-like syntax.

Let’s say we have a table raw_logs with a VARIANT column named log_payload containing the following JSON:JSON

{
  "event_type": "user_login",
  "user_details": {
    "user_id": "user-123",
    "device_type": "mobile"
  },
  "timestamp": "2025-09-29T10:00:00Z"
}

You can easily extract values from this JSON in your SQL query.

Example Code:SQL

SELECT
    log_payload:event_type::STRING AS event,
    log_payload:user_details.user_id::STRING AS user_id,
    log_payload:user_details.device_type::STRING AS device,
    log_payload:timestamp::TIMESTAMP_NTZ AS event_timestamp
FROM
    raw_logs
WHERE
    event = 'user_login'
    AND device = 'mobile';

• : is used to traverse the JSON object.
• . is used for dot notation to access nested elements.
• :: is used to cast the VARIANT value to a specific data type (like STRING or TIMESTAMP).

This flexibility allows you to build powerful pipelines without needing a rigid, predefined schema for all your data.

Game-Changer #1: Zero-Copy Cloning

Imagine you need to create a full copy of your 50TB production database to give your development team a safe environment to test in. In a traditional system, this would be a slow, expensive process that duplicates 50TB of storage.

In Snowflake, this is instantaneous and free (from a storage perspective). Zero-Copy Cloning creates a clone of a table, schema, or entire database by simply copying its metadata.

• How it Works: The clone points to the same underlying data micro-partitions as the original. No data is actually moved or duplicated. When you modify the clone, Snowflake automatically creates new micro-partitions for the changed data, leaving the original untouched.
• Use Case: Instantly create full-scale development, testing, and QA environments without incurring extra storage costs or waiting hours for data to be copied.

Example Code:SQL

-- This command instantly creates a full copy of your production database
CREATE DATABASE my_dev_db CLONE my_production_db;

Game-Changer #2: Time Travel

Have you ever accidentally run an UPDATE or DELETE statement without a WHERE clause? In most systems, this would mean a frantic call to the DBA to restore from a backup.

With Snowflake Time Travel, you can instantly query data as it existed in the past, up to 90 days by default for Enterprise edition.

• How it Works: Snowflake’s storage architecture is immutable. When you change data, it simply creates new micro-partitions and retains the old ones. Time Travel allows you to query the data using those older, historical micro-partitions.
• Use Cases:
  • Instantly recover from accidental data modification.
  • Analyze how data has changed over a specific period.
  • Run A/B tests by comparing results before and after a change.

Example Code:SQL

-- Query the table as it existed 5 minutes ago
SELECT *
FROM my_table AT(OFFSET => -60 * 5);

-- Or, restore a table to a previous state
UNDROP TABLE my_accidentally_dropped_table;

Conclusion for Part 3

You’ve now moved beyond just loading data and into the world of powerful analytics and data management. You’ve learned that:

1. Querying in Snowflake uses standard SQL via Worksheets.
2. You can seamlessly query JSON and other semi-structured data using the VARIANT type.
3. Zero-Copy Cloning provides instant, cost-effective data environments.
4. Time Travel acts as an “undo” button for your data, providing incredible data protection.

In Part 4, the final part of our guide, we will cover “Snowflake Governance & Sharing,” where we’ll explore roles, access control, and the revolutionary Data Sharing feature.