Phase 2: Data Storage

Snowflake: virtual warehouses & time travel

Intermediate ~3 min read
Think of it this way A friendly analogy. Read this if the technical version feels dense. Show Hide

Imagine you have a gigantic room filled to the ceiling with every Lego brick imaginable – all sorted and ready to go. This room is where your computer stores all its important information, like a massive digital Lego collection. Now, when you want to do something with that information, like building a super-complicated spaceship or just counting how many red bricks you have, you need someone to actually do the building. In the world of Snowflake, these 'builders' are called virtual warehouses. They're like your construction teams! You can hire a small team (an 'XS' warehouse) for quick jobs, or a huge team of master builders (an 'L' warehouse) for really big projects. The cool part is, you can instantly make your team bigger or smaller, even while they're still building, without them dropping a single brick. And when there's no building to be done, your virtual warehouse team goes home, so you only pay for them when they’re actually working.

This also means you can have lots of different builder teams working at the same time without getting in each other's way. For example, you might have one enormous team (a big 'ETL' warehouse, which stands for 'Extract, Transform, Load') busily sorting through millions of bricks to build a brand new city, while a separate, smaller team (an 'Analytics' warehouse) is quickly answering someone's question about how many green bricks are left. Each team has its own workspace and doesn't make the other teams wait, which is super important when you have urgent questions that need answering right away!

Now, what if someone accidentally knocks over your amazing Lego castle, or you make a change to your city that you later decide you don't like? This is where Time Travel comes in. Snowflake is like having a magical camera constantly taking pictures of your entire Lego room. If you ever need to, you can look back at any of those pictures from the past, even if it was yesterday or last week! You could say, "Show me exactly what my city looked like last Tuesday at lunchtime," or even "Please magically put my castle back exactly as it was an hour ago, before it fell over!"

So, when a data engineer uses Snowflake, they can easily build and manage these powerful builder teams to handle all sorts of information projects, big or small. And if they ever make a mistake, like accidentally deleting a whole section of important data, or changing something they shouldn't have, this magical Time Travel feature lets them instantly rewind and fix it, without losing any hard work. It's like having an undo button for your entire data world!

Snowflake's virtual warehouses are the compute engines that execute your queries and DML operations. Crucially, they completely separate compute resources from storage, allowing you to scale your processing power independently of your data storage. Virtual warehouses are elastic: you can resize them (e.g., from an 'XS' to an 'L') up or down at any time, even mid-query, without impacting data availability. They also auto-suspend when not in use and auto-resume when a query arrives, saving costs by only paying for active compute time. A key benefit for data engineers is workload isolation; you can have multiple virtual warehouses running concurrently, each dedicated to a specific workload (e.g., a large 'ETL_WH' for data loading, a smaller 'ANALYTICS_WH' for user queries), ensuring one heavy query doesn't bottleneck another critical process.

Snowflake's Time Travel feature is a powerful data protection and recovery mechanism built directly into the platform. It allows you to query historical versions of your data, or even restore dropped objects, as they existed at any point within a configurable retention period (default 1 day, up to 90 days for Enterprise accounts). Rather than making full copies, Snowflake maintains historical versions of data, enabling you to "travel back in time" to retrieve data that might have been accidentally deleted, updated incorrectly, or simply to analyze how data has evolved over time. This capability is invaluable for data recovery, auditing changes, and validating data transformations without needing to implement complex snapshotting or backup solutions yourself. You use standard SQL with AT or BEFORE clauses to access past data, and UNDROP to restore entire tables or databases.

Together, virtual warehouses provide the flexible, isolated compute needed to process your data, including Time Travel queries, ensuring efficient resource utilization and strong data governance capabilities for any data engineering workload.

Key Takeaways

  • Virtual Warehouses are independent compute clusters that separate processing from storage, allowing for flexible scaling and cost efficiency.
  • They enable workload isolation, letting different teams or processes use dedicated compute resources concurrently without interference.
  • Snowflake Time Travel allows querying or restoring data from past states within a configurable retention window (e.g., last 24 hours, up to 90 days).
  • Time Travel is crucial for data recovery, auditing, and validating historical data without manual backups.
  • You pay only for active virtual warehouse usage (per second), maximizing cost efficiency.

Code Example

sql
-- Assume 'my_table' exists and may have been modified or dropped.

-- Query data as it was 5 minutes ago (using OFFSET in seconds)
SELECT * FROM my_table AT (OFFSET => -60*5);

-- Query data as it was at a specific UTC timestamp
SELECT * FROM my_table AT (TIMESTAMP => '2023-10-26 10:00:00 -0700'::TIMESTAMP_TZ);

-- If 'my_table' was accidentally dropped, you can restore it:
UNDROP TABLE my_table;

How this code works

This code demonstrates Snowflake's powerful "Time Travel" feature, allowing data engineers to access or restore past versions of data tables. This is incredibly useful for auditing, recovering from accidental data modifications, or analyzing data trends without needing traditional backups. The SELECT * FROM my_table AT syntax is central to querying historical states. One SELECT uses OFFSET => -60*5 to retrieve data as it appeared five minutes ago, measuring time in seconds relative to the current moment. Another SELECT uses TIMESTAMP => '...'::TIMESTAMP_TZ to pinpoint a precise historical moment. The ::TIMESTAMP_TZ cast is crucial here; it ensures Snowflake correctly interprets the timestamp string, especially when dealing with specific time zones, accurately retrieving data from that exact point in time.

Beyond querying past data, Time Travel provides a robust recovery mechanism. If my_table was accidentally dropped, the UNDROP TABLE my_table; command instantly restores it to its state just before deletion. This avoids lengthy backup restoration processes. A subtle but important detail for all these Time Travel operations is the data retention period. Both querying historical data with AT and restoring a dropped table with UNDROP are only possible if the event occurred within the table's configured retention period; otherwise, the historical data is no longer available.

Related Articles

Hand-picked reads to go deeper on cloud data warehouses.

Back to Data Engineer Roadmap