time travel transient table

Storage Costs for Time Travel and Fail-safe ¶

Storage fees are incurred for maintaining historical data during both the Time Travel and Fail-safe periods.

Storage Usage and Fees ¶

The fees are calculated for each 24-hour period (i.e. 1 day) from the time the data changed. The number of days historical data is maintained is based on the table type and the Time Travel retention period for the table.

Also, Snowflake minimizes the amount of storage required for historical data by maintaining only the information required to restore the individual table rows that were updated or deleted. As a result, storage usage is calculated as a percentage of the table that changed. Full copies of tables are only maintained when tables are dropped or truncated.

Temporary and Transient Tables ¶

To help manage the storage costs associated with Time Travel and Fail-safe, Snowflake provides two table types, temporary and transient, which do not incur the same fees as standard (i.e. permanent) tables:

Transient tables can have a Time Travel retention period of either 0 or 1 day.

Temporary tables can also have a Time Travel retention period of 0 or 1 day; however, this retention period ends as soon as the table is dropped or the session in which the table was created ends.

Transient and temporary tables have no Fail-safe period.

As a result, the maximum additional fees incurred for Time Travel and Fail-safe by these types of tables is limited to 1 day. The following table illustrates the different scenarios, based on table type:

Considerations for Using Temporary and Transient Tables to Manage Storage Costs ¶

When choosing whether to store data in permanent, temporary, or transient tables, consider the following:

Temporary tables are dropped when the session in which they were created ends. Data stored in temporary tables is not recoverable after the table is dropped.

Historical data in transient tables cannot be recovered by Snowflake after the Time Travel retention period ends. Use transient tables only for data you can replicate or reproduce independently from Snowflake.

Long-lived tables, such as fact tables, should always be defined as permanent to ensure they are fully protected by Fail-safe.

Short-lived tables (i.e. <1 day), such as ETL work tables, can be defined as transient to eliminate Fail-safe costs.

If downtime and the time required to reload lost data are factors, permanent tables, even with their added Fail-safe costs, may offer a better overall solution than transient tables.

The default type for tables is permanent. To define a table as temporary or transient, you must explicitly specify the type during table creation:

CREATE [ OR REPLACE ] [ TEMPORARY | TRANSIENT ] TABLE <name> ...

For more information, see CREATE TABLE .

Migrating Data from Permanent Tables to Transient Tables ¶

Migrating data from permanent tables to transient tables involves performing the following tasks:

Use CREATE TABLE … AS SELECT to create and populate the transient tables with the data from the original, permanent tables.

Apply all access control privileges granted on the original tables to the new tables. For more information about access control, see Overview of Access Control .

Use DROP TABLE to delete the original tables.

Optionally, use ALTER TABLE to rename the new tables to match the original tables.

Snowflake Transient Tables, Usage and Examples

Post author: Vithal S
Post last modified: May 2, 2020
Post category: Snowflake
Reading time: 4 mins read

Snowflake Transient tables are similar to permanent tables with the key difference that they do not have a Fail-safe period. The transient tables are similar to temporary tables, but, you have to explicitly drop transient tables at the end of the session.

Snowflake Transient Tables

Snowflake transient tables persist until explicitly dropped and are available to all users with the appropriate privileges. The transient tables are designed for transitory data that needs to be maintained beyond the current session.

Because transient tables do not have a Fail-safe period, they provide a good option for managing the cost of very large tables used to store transitory data. However, you or Snowflake cannot recover the data after the Time Travel retention period passes.

Snowflake Transient Table Syntax

To create a temporary table, simply specify the TRANSIENT keyword in your CREATE TABLE DDL.

For example,

Snowflake Transient Table Example

Following is the example of create transient table in Snowflake.

Create Snowflake Transient Table with same name as Permanent Table

Just like other table types, transient tables belong to a specified database and schema. However, because they are not session-based, they are bound by the same unique name requirements. This means you cannot create transient tables with the same name as permanent table.

Foe example,

As you can see, you cannot create a transient table with the same name as permanent table.

Snowflake Transient Table Restrictions

Following are some if the restriction on the transient tables.

Cannot create transient table with the same name as permanent tables.
Temporary tables do not support some standard features such as cloning .
The data in the transient tables cannot be recovered after the Time Travel retention period passes.
Transient tables have no Fail-safe period

Snowflake Time Travel

By: koen verbeeck.

Snowflake works with immutable cloud storage as the storage layer for the data. This means it doesn’t change a file, it rather creates a new version of the file. This way of working opens new and exciting possibilities and one of them is time travel.

Introduction to Time Travel

Imagine that every time you make a change to a table, a new version of the table is created. Each time you run an INSERT, UPDATE or DELETE (or any other DML statement), a new version of the table is stored alongside all previous versions of the table. This would mean you could read previous versions of the data in your table. Time travel in Snowflake is exactly that.

You can compare it with temporal tables in SQL Server . In fact, you’ll see the syntax to query with time travel is fairly the same as in SQL Server. The biggest difference however is SQL Server stores all the versions indefinitely, while Snowflake only maintains the different versions of your table for a specific period in time. Depending on your edition, this is either one day or up to 90 days. After those 90 days, the versions are lost. Well, actually they are kept for 7 more days, but only Snowflake support can access those. An overview:

To query the current version of your table, you use the standard SQL you’ve been using all along. To query the previous versions, you need specific syntax, which we’ll cover in the next paragraph. The fail-safe cannot be queried. By default, time travel is enabled on every table. You can however shorten the data retention period or you can turn it completely off for a table. Those tables are called transient tables . Good candidates for transient tables are staging tables or tables which are truncated and re-loaded every day. Keep in mind storing all of those different versions of your table actually consumes storage for which you have to pay.

Currently, Snowflake doesn’t have a back-up mechanism. It relies on the underlying cloud to make sure the data is safe and replicated. However, if you do something wrong, like accidentally deleting some data, you can use time travel to fetch the data before you made the change.

Querying Time Travel Data

Let us first create a new table:

With some sample data:

Wait for a couple of minutes, then insert some extra sample data:

We now have 4 rows in the table, and 3 versions (1 with no rows, 1 with 2 rows and 1 with 4 rows).

Using the query history, we can fetch the query ID of our last INSERT statement.

Using the BEFORE clause, we can fetch the version of the table before our last INSERT:

This returns two rows:

Using the OFFSET clause, we can go back a specific period in time.

In the example here, we retrieved the version of the table before any row was inserted.

If you go too far back in time, you’ll get an error because the table didn’t exist yet.

If you truncate a table, time travel still works. If you drop a table however, you cannot query the data anymore. You can restore the table though using the UNDROP command. Once the table is restored, time travel works again.

Additional Information

You can also query time travel data for a specific time stamp.
Introduction to SQL Server Temporal Tables
Options to Retrieve SQL Server Temporal Table and History Data
SQL Server 2016 T-SQL Syntax to Query Temporal Table
More temporal table tips .

Next >>

Comments for this article.

Snowflake configurations

Transient tables .

Snowflake supports the creation of transient tables . Snowflake does not preserve a history for these tables, which can result in a measurable reduction of your Snowflake storage costs. Transient tables participate in time travel to a limited degree with a retention period of 1 day by default with no fail-safe period. Weigh these tradeoffs when deciding whether or not to configure your dbt models as transient . By default, all Snowflake tables created by dbt are transient .

Configuring transient tables in dbt_project.yml

A whole folder (or package) can be configured to be transient (or not) by adding a line to the dbt_project.yml file. This config works just like all of the model configs defined in dbt_project.yml .

Configuring transience for a specific model

A specific model can be configured to be transient by setting the transient model config to true .

Query tags

Query tags are a Snowflake parameter that can be quite useful later on when searching in the QUERY_HISTORY view .

dbt supports setting a default query tag for the duration of its Snowflake connections in your profile . You can set more precise values (and override the default) for subsets of models by setting a query_tag model config or by overriding the default set_query_tag macro:

In this example, you can set up a query tag to be applied to every query with the model's name.

Note: query tags are set at the session level. At the start of each model materialization , if the model has a custom query_tag configured, dbt will run alter session set query_tag to set the new value. At the end of the materialization, dbt will run another alter statement to reset the tag to its default value. As such, build failures midway through a materialization may result in subsequent queries running with an incorrect tag.

Merge behavior (incremental models)

The incremental_strategy config controls how dbt builds incremental models. By default, dbt will use a merge statement on Snowflake to refresh incremental tables.

Snowflake's merge statement fails with a "nondeterministic merge" error if the unique_key specified in your model config is not actually unique. If you encounter this error, you can instruct dbt to use a two-step incremental approach by setting the incremental_strategy config for your model to delete+insert .

Configuring table clustering

dbt supports table clustering on Snowflake. To control clustering for a table or incremental model, use the cluster_by config. When this configuration is applied, dbt will do two things:

It will implicitly order the table results by the specified cluster_by fields
It will add the specified clustering keys to the target table

By using the specified cluster_by fields to order the table, dbt minimizes the amount of work required by Snowflake's automatic clustering functionality. If an incremental model is configured to use table clustering, then dbt will also order the staged dataset before merging it into the destination table. As such, the dbt-managed table should always be in a mostly clustered state.

Using cluster_by

The cluster_by config accepts either a string, or a list of strings to use as clustering keys. The following example will create a sessions table that is clustered by the session_start column.

The code above will be compiled to SQL that looks (approximately) like this:

Automatic clustering

Automatic clustering is enabled by default in Snowflake today , no action is needed to make use of it. Though there is an automatic_clustering config, it has no effect except for accounts with (deprecated) manual clustering enabled.

If manual clustering is still enabled for your account , you can use the automatic_clustering config to control whether or not automatic clustering is enabled for dbt models. When automatic_clustering is set to true , dbt will run an alter table <table name> resume recluster query after building the target table.

The automatic_clustering config can be specified in the dbt_project.yml file, or in a model config() block.

Configuring virtual warehouses

The default warehouse that dbt uses can be configured in your Profile for Snowflake connections. To override the warehouse that is used for specific models (or groups of models), use the snowflake_warehouse model configuration. This configuration can be used to specify a larger warehouse for certain models in order to control Snowflake costs and project build times.

The example config below changes the warehouse for a group of models with a config argument in the yml.

The example config below changes the warehouse for a single model with a config() block in the sql model.

Copying grants

When the copy_grants config is set to true , dbt will add the copy grants DDL qualifier when rebuilding tables and views . The default value is false .

Secure views

To create a Snowflake secure view , use the secure config for view models. Secure views can be used to limit access to sensitive data. Note: secure views may incur a performance penalty, so you should only use them if you need them.

The following example configures the models in the sensitive/ folder to be configured as secure views.

Configuring transient tables in dbt_project.yml
Configuring transience for a specific model
Merge behavior (incremental models)
Using cluster_by
Automatic clustering
Configuring virtual warehouses
Copying grants
Secure views
Temporary tables
Limitations

Table Types in Snowflake

Someone asked this question on Reddit

trying to build a data mart basically and most of the times it will be file source that will be transformed through ETL and eventually loaded into snowflake. So after that plan will be to build some views on top of these transformed tables which will be later used for data science purposes so wanted to know how to first approach building tables on snowflake (transient, permanent) i heard from some people suggesting to use transient tables in development environment So was getting confused what should be used when and how and when things like time travel should be enabled Along with that if there are best practices for roles accesses, data security, designing of tables and views will be great

I thought I could form it into a mini-post while helping my niece and nephew draw Pokemon pictures. They’re pretty good to be honest. This is really just a summary of table types from the Snowflake reference page and a little bit of important stuff I’ve learned from experience. Nothing revolutionary, not like Pokemon.

Time travel, transient tables, and fail-safe

What is time travel.

Snowflake reference

Time travel is the ability to retrieve older versions of your objects up to 90 days in the past (for enterprise customers; 1 day for non-enterprise). This includes the ability to undrop objects (tables, schemas, databases) which has saved more lives than CPR. Since you’re effectively storing copies of these objects, a longer time travel period will increase your storage costs. As they say, there’s no such thing as free data. Reference: Storage costs for Time Travel If you don’t want to time travel on an object, you can set the retention period to 0 days. Retention period , like a lot of Snowflake parameters, can be set at the account, database, schema or table level. The database or schema retention period is the default for each table made in that object; this can be overridden by a specific table retention.

A bit of advice: if you are doing CREATE OR REPLACE or DROP TABLE/CREATE TABLE for your tables every ETL run and you have time travel set, you will pay for time travel on each iteration of the table created. So let’s say your time travel is set to 3 days and each day you CREATE OR REPLACE TABLE cat_data AS SELECT * FROM cat_source;

Even though when cat_data is created it has the same table name, Snowflake regards it as a different table. So you will pay for time travel storage like this:

You will pay for 6 days of storage when really, it would be hard to reach any of it. The reason why you can hardly access it is because if you drop and recreate a table with the same name, it’s hard to retrieve it. I’ve struggled with it before; this will help you get it back but note it’s not trivial. In my opinion, time travel on CREATE OR REPLACE is just not worth the money. I recommend either not doing time travel for a CREATE/REPLACE or changing it to TRUNCATE/INSERT

What is fail-safe?

Snowflake reference Fail-safe begins where time travel ends. Fail-safe is 7 days, no matter what, you can’t configure it. It’s like a week in that regard. Always 7 days. It is what it sounds like– your last resort in the event of a CATastrophe. Only Snowflake can access it, but you still pay for the storage.

What is a transient table?

A transient table is a table without fail safe! A transient table can still have time travel of up to 1 day, but it won’t be recovered in case of a complete database failure. So I would build transient tables for any table you don’t want more than 1 day of time travel on and you can easily re-build in case of a failure.

To refer specifically to the Reddit question, if you are just developing and it’s not yet production data, I may build transient tables, but, since I’m lazy, I’m more likely to set the retention period of the development database to 0 and pay for the fail safe. The reason is converting from transient to permanent isn’t easy and the DDL is different so you’d have to change all your DDL when moving to production.

Snowflake reference pages say “After creation, transient tables cannot be converted to any other table type,” but in reality, you would just clone the data into a permanent table:

CREATE OR REPLACE TABLE cat_permanent CLONE cat_transient;

So it’s not trivial and storage doesn’t cost that much, so I would just set my time travel preference by database.

What I like to use time travel for

I like to use time travel for: Automated testing

For automated testing, time travel is good to see how the table looked 1 hour ago or 24 hours ago. You can track how all tables change in size over time and find trends to help you create alerts. This will give you size yesterday divided by size today: select count(*)from cat_table at(offset => -60*60*24) /select count(*) as size_yesterday from cat_table

Note: I wouldn’t actually store that result in my QA dataset. I would store the count(*) at different times and then calculate the percent.

Ad-hoc QA and debugging

If there is an alert or even a question about “this suddenly looks different”, you can easily check both how a table looked an hour ago and how a table looked at before a certain commit. If you look in your history tab and find that query ID catz123 is what updated the table last, you can check the before and after like: select * from cat_table before(statement => 'catz123')

I may go as far as to do this following to find what existed before that is now gone:

What I don’t like to use time travel for

I do not like to use time travel for retrieving historical data. If you really need historical copies, you should save them in a _history table. You have to pay for the storage either way, and storing it in a table is easy to access for stakeholders. For instance, instead of expecting stakeholders to do this: select * from cat_table at(offset => -60*60*24) to get the table from yesterday, at the end of every ETL, you can store that data into the cat_table_history table with a stored_timestamp column. Stakeholders can then query:

select * from cat_table_history where stored_timestamp = DATEADD('day', -1, CURRENT_DATE)

Ultimately, you’re going to pay the same storage anyway, and storing in a real table is easier to access and maintain. If you want to replicate the time travel attribute of 7 days, you can create a DELETE statement in your ETL as:

DELETE FROM cat_table_history WHERE stored_timestamp = DATEADD('day', -1, CURRENT_DATE)

I know what you’re thinking: but then this table will have time travel on it and we don’t need that. You can always turn time travel off on a specific table by doing:

alter table cat_data_history set data_retention_time_in_days=0;

Next I’ll write about views…

Strategic Services

Digital Engineering Services

Managed Services

Harness the power of Generative AI

Amplify innovation, creativity, and efficiency through disciplined application of generative AI tools and methods.

Focus Industries

Healthcare & Life Sciences

Retail & CPG

Energy & Utilities

Banking, Financial Services & Insurance

Travel, Hospitality & Logistics

Telecom & Media

Explore Client Success Stories

We create competitive advantage through accelerated technology innovation. We provide the tools, talent, and processes needed to accelerate your path to market leadership.

Global Delivery with Encora

Nearshore in the Americas

Nearshore in Europe

Nearshore in Asia & Oceania

Expertise at Scale in India

Hybrid Global Teams

Experience the power of Global Digital Engineering with Encora.

Refine your global engineering location strategy with the speed of collaboration in Nearshore and the scale of expertise in India.

15+ other partnerships

Accelerating Innovation Cycles and Business Outcomes

Through strategic partnerships, Encora helps clients tap into the potential of the world’s leading technologies to drive innovation and business impact.

Featured Insights

Using Generative AI to Tackle Physician Burnout

Unlocking the Potential of Gen AI for the Automotive Industry

Narrowing Down a Use Case for Shared Loyalty in Travel

Exploring The Potential of Web3 for Shared Loyalty in Travel

Time Travel in Snowflake

Talati adit anil.

June 01, 2023

Consider a scenario where you accidentally dropped the actual table or instead of deleting a set of records, you updated all the records present in the table. What will you do? How will you restore your data that has already been deleted/altered? You must be hoping of going back in time and correcting incorrectly executed statements. Snowflake provides this feature wherein you can get back the data that is present at a particular time. This feature of Snowflake is called Time Travel .

Introduction

Snowflake Time Travel is a very important tool that allows users to access Historical Data (i.e. data that has been updated or removed) at any point in time in the past. It is a powerful Continuous Data Protection (CDP) feature that ensures the maintenance and availability of historical data.

Key Features

Query Optimization: As a user, we should not be concerned about optimizing queries because Snowflake on its own optimizes queries by using Clustering and Partitioning.
Secure Data Sharing: Using Snowflake Database, Tables, Views, and UDFs, data can be shared securely from one account to another.
Support for File Formats: Supports almost all file formats: JSON, Avro, ORC, Parquet, and XML are all Semi-Structured data formats that Snowflake can import. Column type — Variant lets the user store Semi-Structured data.
Caching: Caching strategy of Snowflake returns results quickly for repeated queries as it stores query results in a cache within a given session.
Fault Resistant: In case of event failure, Snowflake provides exceptional fault-tolerant capabilities to recover tables, views, databases, schema, and so on.
To query past data.
To make clones of complete Tables, Schemas, and Databases at or before certain dates.
To restore deleted Tables, Schemas, and Databases.
To restore original data that was updated accidentally.
To check consumption over a period of time.
Cloning and Backing up data from previous times.

How to Enable & Disable Time Travel in Snowflake?

Enable time travel.

No additional configurations are required to enable Time Travel, it is enabled by default, with a one-day retention period. Although to configure longer data retention periods, we need to upgrade to Snowflake Enterprise Edition. The retention period can be set to a maximum of 90 days. Based on the retention period, charges will increase. The below query builds a table with a retention period of 90 days:

The retention period can also be changed using the ‘alter’ query as below:

Disable Time Travel

Time Travel cannot be turned off for accounts, but it can be turned off for individual databases, schemas, and tables by setting data_retention_time_in_days field to 0 using the below query:

Query Time Travel Data

Whenever any Data Manipulation Language (DML) query is executed on a table, Snowflake saves prior versions of the Table data for a given period of time depending on the retention period. The previous version of data can be queried using the AT | BEFORE Clause. Using AT, the user can get data at a given period of time whereas using BEFORE all the data from that point till the end of the retention period can be fetched. The following SQL extensions have been added to facilitate Snowflake Time Travel:

CLONE: To create a logical duplicate of the object at a specific point in its history.
TIMESTAMP: From a given time (Data & Time) provided.
OFFSET: Time difference from current time till offset provided in seconds.
STATEMENT: Using a Statement ID from the point where the last DML query was fired.
UNDROP: If a table is dropped accidentally, it can be restored using the UNDROP command.

The below query generates a Clone of a Table from the given Date and Time as indicated by the Timestamp:

The below query creates a Clone of a Schema and all its Objects as they were an hour ago:

The below query pulls Historical Data from a Table from a given Timestamp:

The below query pulls Historical Data from a Table that was updated 5 minutes ago:

The below query collects Historical Data from a Table up to the given statement’s Modifications (Statement ID):

The below query is used to restore Database EMP:

The following graphic from the Snowflake documentation summarizes all the above points visually:

Data Retention

Snowflake preserves the previous state of the data when DML operations are performed. By default, all Snowflake accounts have a standard retention duration of one day which is automatically enabled.

For Snowflake Standard Edition, the Retention Period can be adjusted to 0 from default 1 day for all objects (Temporary & Permanent).
For Snowflake Enterprise Edition (or higher) it gives more flexibility for setting retention period, that is The Retention Time for permanent Databases, Schemas, and Tables can be configured to any number between 0 and 90 days whereas for temporary objects it can be set to 0 from the default 1 day.

The below query sets a retention period of 90 days while creating the table:

Snowflake provides another exciting feature called Fail-safe where historical data can be protected in case of any failure. Fail-safe allows a maximum period of 7 days which begins after the Time Travel retention period ends wherein Historical data can be recovered. Recovering data through Fail-safe can take hours to days and it involves cost.

The number of days historical data is maintained is based on the table type and the Fail-safe period for the table. Transient and temporary tables have no Fail-safe period.

Storage fees are incurred for maintaining historical data during both the Time Travel and Fail-safe periods. The fees are calculated for each 24 hours (i.e. 1 day) from the time the data changed. The number of days historical data is maintained is based on the table type and retention period set for the table.

Snowflake minimizes the amount of storage required for historical data by maintaining only the information required to restore the individual table rows that were updated or deleted. As a result, storage usage is calculated as a percentage of the table that changed. In most cases, Snowflake does not keep a full copy of data. Only when tables are dropped or truncated, full copies of tables are maintained.

Temporary and Transient Tables

To manage the storage costs Snowflake provides two table types: TEMPORARY & TRANSIENT, which do not incur the same fees as standard (i.e. permanent) tables:

Transient tables can have a Time Travel retention period of either 0 or 1 day.
Temporary tables can also have a Time Travel retention period of 0 or 1 day; however, this retention period ends as soon as the table is dropped or the session in which the table was created ends.
Transient and temporary tables have no Fail-safe period.
The maximum additional fees incurred for Time Travel and Fail-safe by these types of tables are limited to 1 day.

The above table illustrates the different scenarios, based on table type.

hbspt.cta._relativeUrls=true;hbspt.cta.load(7958737, '1308a939-5241-47c3-bf0f-864090d8516d', {"useNewLoader":"true","region":"na1"});

Snowflake Time Travel is a powerful feature that enables users to examine data usage and manipulations over a specific time. Syntax to query with time travel is fairly the same as in SQL Server which is easy to understand and execute. Users can restore deleted objects, make duplicates, make a Snowflake backup, and recover historical data.

About Encora

Fast-growing tech companies partner with Encora to outsource product development and drive growth. Contact us to learn more about our software engineering capabilities.

Encora accelerates enterprise modernization and innovation through award-winning digital engineering across cloud, data, AI, and other strategic technologies. With robust nearshore and India-based capabilities, we help industry leaders and digital natives capture value through technology, human-centric design, and agile delivery.

Share this post

Related insights.

5 Axioms to Improve Your Team Communication and Collaboration

Good communication within a team is key to keeping everyone on the right track. But it can be ...

JavaScript: setTimeout() and Promise under the Hood

In this blog, we will delve deeper into how setTimeout works under the hood.

Exponential Smoothing Methods for Time Series Forecasting

Recently, we covered basic concepts of time series data and decomposition analysis. We started ...

Innovation Acceleration

Headquarters - Scottsdale, AZ 85260 ©Encora Digital LLC

Global Delivery

Partnerships

Which Snowflake Table Type Should You Use?

As Snowflake has developed over the years, we have seen the introduction of more and more table types, and it isn’t always immediately clear what the differences are and when they should be used. Today, I hope to shed some light on this by outlining the following table types and their nature:

Permanent tables
Temporary tables
Transient tables
External tables
Dynamic tables

Permanent Tables

Snowflake permanent tables are your standard database tables. They are Snowflake’s default table type and can be created with the common CREATE TABLE syntax, with no additional properties.

Being the most used and default type of table in Snowflake, permanent tables support features such as Fail-safe and Time Travel, which are useful when in need to recover lost data. Permanent table data contribute to the storage charges that Snowflake bills your account.

Temporary Tables

Snowflake temporary tables are tables that only exist within the current session. Once the session ends, the table and its data will be deleted and they are not recoverable, either by the user who created it or by Snowflake. Temporary tables are useful for storing non-permanent, transitory data like ETL data and session-specific data and they are not visible to other users or sessions.

The data stored in the temporary table will contribute to the overall storage charges that Snowflake bills your account. For large temporary tables that are created in longer sessions (i.e., over 24 hours), Snowflake recommends dropping these tables once they are no longer used in order to avoid unexpected storage costs.

Because temporary tables belong to a session, they are not bound by the same uniqueness requirements as other table types and you might run into naming conflicts. Temporary tables can have the same name as a permanent table in the same schema. When creating a temporary table with the same name as a permanent table, the existing table will be effectively hidden. When creating a table with the same name as a temporary table, the newly created table is hidden by the temporary table.

You can create a temporary table by using the CREATE TABLE command with the TEMPORARY (or TEMP) keyword, for example:

Transient Tables

Transient tables are tables that persist until explicitly dropped. They are available to all users with the correct privileges. The main difference between transient and permanent tables is that transient tables do not have a Fail-safe period as well as having a lower Time Travel retention period of 0 or 1 day (default is 1). Transient tables are designed for transitory data that needs to be maintained beyond the current session (unlike temporary tables).

Data in transient tables will contribute to the overall storage costs, however, there will be no Fail-safe costs as they do not make use of Snowflake’s Fail-safe feature.

To create a transient table, you can specify the TRANSIENT keyword when creating the object:

You can also create transient databases and schemas. Tables created in a transient schema and schemas created in a transient database are transient by definition.

The same syntax applies to databases and schemas:

External Tables

Snowflake external tables allow you to query data that is stored in a data lake outside Snowflake in your cloud storage provider. They allow you to access and analyse data residing in various file formats, such as CSV, JSON, Parquet, Avro and more, without needing to load the data into Snowflake’s internal storage.

External tables are read-only, meaning you cannot perform certain DML operations on them such as INSERT, DELETE and UPDATE. However, you can create views against external tables as well as use them for queries and join operations.

Since the data is stored outside Snowflake, querying an external table can be slower than querying a table that is stored in Snowflake. You can improve the performance of a query that looks into an external table by creating a Materialised View (Enterprise Edition feature) based on the external table. A particularly strong use-case is to point an external table at a set of often-changing semi-structured data files and using a materialised view to flatten and parse the semi-structured data into a structured, tabular format for users to query.

In order to create an external table, first you need to create a named stage (CREATE STAGE) that points to an external location where your data files are staged (for example, an S3 Bucket). For this example, we’ll be creating a stage that points to an S3 bucket called table-types containing files which contain data for players in the FIFA 2023 video-game.

Here, we used a storage integration for authentication with AWS. You can read more about Storage Integrations and how to set them up here: Configuring Storage Integrations Between Snowflake and AWS S3

Once the stage is created, you can create your external table by running a CREATE EXTERNAL TABLE command. This method is great when you don’t know the schema of the files, the table will be created with one column as VARIANT with each row of your CSV file as a JSON Object. This VARIANT column’s name is VALUE and can be referenced when querying your external table.

You can select different columns by specifying $1:c(colNumber) or the column name VALUE:c(colNumber) in your SELECT statement, for example:

If you know the schema of the files, you can then specify column names when creating the external table.

Note that by default, the VALUE column containing the JSON object will be the first column of your table:

You can learn more about External Tables in my blog post: Snowflake External Tables: Connect Efficiently to Your Data Lake

Dynamic Tables

Dynamic tables are a type of table in Snowflake that automate the way to transform your data for consumption. In dynamic tables, data transformations can be defined on the table creation, and Snowflake will manage the pipeline automatically. Instead of creating a separate target table and write code to transform your data in that table, you can create a dynamic table as your target table and write your transformation in the table definition as a SQL statement.

Because the definition of a dynamic table is determined by the SQL statement, you cannot insert, update or delete the rows. The automated refresh process that manages the pipeline will materialise the query results into a dynamic table.

The following is a simple example that creates a dynamic table that joins two tables together and apply a simple UPPER function in one of the columns:

On the example above, you can specify the query results you want to see. This can replace stream and tasks by specifying how often you’d like the data to refresh under the TARGET_LAG property.

At InterWorks, we’re very happy with the release of dynamic tables as it matches a declarative pipeline pattern that we have desired for years and partially achieved using views in the past. My colleague Mike Oldroyd wrote an article on this back in 2020: “ A Better Alternative to Algorithms in Business Intelligence. ”

Dynamic tables provide a reliable mechanism for automatic incremental data updates. However, if the underlying process encounters challenges in determining how to perform this incremental refresh, such as when an unsupported expression is used in the query, it will resort to a full refresh instead. It’s worth noting that warehouse credits are only consumed for incremental refresh when new data is available.

Another noteworthy feature of dynamic tables is their snapshot isolation capability. This means they always maintain data consistency, ensuring that their content reflects the outcome that the defining query would have generated at a specific point in the past.

My colleague Tinju has written an outstanding blog post about Dynamic tables. To delve deeper into this topic, I encourage you to explore the content by following this link: “ Snowflake Dynamic Tables: Automatic Declarative Data Transformation Pipelines. ”

When you are working with data that is only needed for a short period of time, temporary tables can be very useful. These tables are designed to hold transitory data that only needs to exist within the current session and will be deleted afterwards to save on storage costs.

When working with data that is intended to be kept for a longer period of time than the current session, using transient tables is a viable option. Although it is important to note that while this approach may be useful for maintaining the data beyond the current session, it does not offer the benefits of Fail-safe that permanent tables do.

When creating your fact and dimension tables, it is highly recommended to use permanent tables. These tables offer a number of advantages, including the ability to travel back in time and recover previous states of the data in cases where errors or other issues arise. Additionally, permanent table data can be recovered with Fail-safe by the Snowflake team.

External tables are a great way to access data that is stored outside of Snowflake. By using external tables, you can easily query your data without having to move it into Snowflake. This can be especially useful if you have a large amount of data in your external storage. However, it’s important to be aware of the limitations in performance that can come with using external tables. Since the data is not stored in Snowflake, query times can be higher as we need to retrieve it from your cloud storage provider.

Dynamic tables are an excellent solution for simplifying the process of tracking data dependencies and managing data refresh. They can be used without requiring you to write code, and they allow you to avoid the complexity of streams and tasks. However, it’s crucial to note certain limitations that exist. For instance, when constructing pipelines with streams and tasks, it’s common to incorporate stored procedures and external functions. Unfortunately, these functionalities are not accessible when creating your pipeline with dynamic tables.

Thank you for reading about the various table types in Snowflake. I hope you found this overview helpful in understanding their differences and how to use them effectively. With this knowledge, you can make informed decisions when choosing which table type to use, unlocking new possibilities for your data-driven projects. Happy exploring!

InterWorks Blog Roundup — March 2024

Recreating Level of Detail Calculations in Power BI

Power BI Case Statements

Power BI Logic and Calculation Foundations

More about the author, guilherme rampani.

See more from this author →

Tax Information

Sales tax will be added to invoices for shipments into Alabama, Arizona, Arkansas, California, Colorado, Connecticut, DC, Florida, Georgia, Hawaii, Illinois, Indiana, Iowa, Kansas, Louisiana, Maryland, Massachusetts, Michigan, Minnesota, Missouri, Nebraska, Nevada, New Jersey, New York, North Carolina, Ohio, Oklahoma, Pennsylvania, Rhode Island, South Carolina, Tennessee, Texas, Utah, Virginia, Washington, West Virginia, Wisconsin and Wyoming unless customer is either a reseller or sales tax exempt. Please submit exemption forms to [email protected] for review.

File upload * Drop files here or Select files Accepted file types: jpg, png, gif, pdf, Max. file size: 100 MB. empty to support CSS :empty selector. --> Please provide a resale certificate for each applicable state. (Seller's permit does not meet requirement for deferring sales tax.)
File upload * Drop files here or Select files Accepted file types: jpg, png, gif, pdf, Max. file size: 100 MB. empty to support CSS :empty selector. --> Please provide tax exempt status document

Partners or Corporate Officers

Full Name *

Trade References

Payment options.

Credit card
Electronic Fund Transfer
* Due Upon Receipt (unless other arrangements have been made). Past Due Accounts may be subject to a late payment charge of 2% or $15 per month (whichever is greater). The applicant agrees to pay any reasonable collection costs and legal fees. Additionally, the applicant agrees that this agreement shall be construed under and enforced in accordance with the laws of the State of Oklahoma and that any action commenced hereunder shall be venued in Payne County. InterWorks may terminate any credit availability within its sole discretion. The undersigned agrees to the above terms and authorizes InterWorks to utilize any outside credit reporting services to obtain information on the applicant and certifies that all the above information is true and correct.
Name * First Last
Date * Month Day Year
Phone This field is for validation purposes and should be left unchanged.

InterWorks uses cookies to allow us to better understand how the site is used. By continuing to use this site, you consent to this policy. Review Policy OK

Interworks GmbH Ratinger Straße 9 40213 Düsseldorf Germany Geschäftsführer: Mel Stephenson

Kontaktaufnahme: [email protected] Telefon: +49 (0)211 5408 5301

Amtsgericht Düsseldorf HRB 79752 UstldNr: DE 313 353 072

Love our blog? You should see our emails. Sign up for our newsletter! SUBSCRIBE

Überblick über den Datenschutz

Any cookies that may not be particularly necessary for the website to function and is used specifically to collect user personal data via analytics, ads, other embedded contents are termed as non-necessary cookies. It is mandatory to procure user consent prior to running these cookies on your website.

Can't find what you're looking for? Ask The Community

park c. 287 asked a question.

Are Temporary Table and Transient Table used?

Snowflake Community Questions
Transient Table

TKruegerISAG (initions GmbH)

Correct. That's the biggest difference between a permanent table and a transient table. Only permanent tables offer the 7 day fail safe period, which on the other hand consumes more storage.

So mostly transient tables are used for volatile tables, that do not hold data permanently to save storage costs.

Best regards,

Hi @park c. 287 ,

yes they can be used for time travel up to 1 day(s). Though they do not have the fail-safe storage of 7 days.

Here is a good comparison in the documentation: https://docs.snowflake.com/en/user-guide/tables-temp-transient#comparison-of-table-types

park c. 287

Time Travel is possible, but fail-safe is not possible?

No-code Data Pipeline for Snowflake

Seamlessly load data from 150+ sources to Snowflake in real-time with Hevo.

Time Travel snowflake: The Ultimate Guide to Understand, Use & Get Started 101

By: Harsh Varshney Published: January 13, 2022

To empower your business decisions with data, you need Real-Time High-Quality data from all of your data sources in a central repository. Traditional On-Premise Data Warehouse solutions have limited Scalability and Performance , and they require constant maintenance. Snowflake is a more Cost-Effective and Instantly Scalable solution with industry-leading Query Performance. It’s a one-stop-shop for Cloud Data Warehousing and Analytics, with full SQL support for Data Analysis and Transformations. One of the highlighting features of Snowflake is Snowflake Time Travel.

Snowflake Time Travel allows you to access Historical Data (that is, data that has been updated or removed) at any point in time. It is an effective tool for doing the following tasks:

Restoring Data-Related Objects (Tables, Schemas, and Databases) that may have been removed by accident or on purpose.
Duplicating and Backing up Data from previous periods of time.
Analyzing Data Manipulation and Consumption over a set period of time.

In this article, you will learn everything about Snowflake Time Travel along with the process which you might want to carry out while using it with simple SQL code to make the process run smoothly.

Key Features of Snowflake

What is snowflake time travel feature.

Enable Snowflake Time Travel
Disable Snowflake Time Travel
What are Data Retention Periods?
What are Snowflake Time Travel SQL Extensions?

How Many Days Does Snowflake Time Travel Work?

How to specify a custom data retention period for snowflake time travel , how to modify data retention period for snowflake objects.

How to Query Snowflake Time Travel Data?

How to Clone Historical Data in Snowflake?

Using UNDROP Command with Snowflake Time Travel: How to Restore Objects?

Snowflake Fail-Safe vs Snowflake Time Travel: What is the Difference?

What is snowflake.

Snowflake is the world’s first Cloud Data Warehouse solution, built on the customer’s preferred Cloud Provider’s infrastructure (AWS, Azure, or GCP) . Snowflake (SnowSQL) adheres to the ANSI Standard and includes typical Analytics and Windowing Capabilities. There are some differences in Snowflake’s syntax, but there are also some parallels.

Snowflake’s integrated development environment (IDE) is totally Web-based . Visit XXXXXXXX.us-east-1.snowflakecomputing.com. You’ll be sent to the primary Online GUI , which works as an IDE, where you can begin interacting with your Data Assets after logging in. Each query tab in the Snowflake interface is referred to as a “ Worksheet ” for simplicity. These “ Worksheets ,” like the tab history function, are automatically saved and can be viewed at any time.

Query Optimization: By using Clustering and Partitioning, Snowflake may optimize a query on its own. With Snowflake, Query Optimization isn’t something to be concerned about.
Secure Data Sharing: Data can be exchanged securely from one account to another using Snowflake Database Tables, Views, and UDFs.
Support for File Formats: JSON, Avro, ORC, Parquet, and XML are all Semi-Structured data formats that Snowflake can import. It has a VARIANT column type that lets you store Semi-Structured data.
Caching: Snowflake has a caching strategy that allows the results of the same query to be quickly returned from the cache when the query is repeated. Snowflake uses permanent (during the session) query results to avoid regenerating the report when nothing has changed.
SQL and Standard Support: Snowflake offers both standard and extended SQL support, as well as Advanced SQL features such as Merge, Lateral View, Statistical Functions, and many others.
Fault Resistant: Snowflake provides exceptional fault-tolerant capabilities to recover the Snowflake object in the event of a failure (tables, views, database, schema, and so on).

To get further information check out the official website here .

Snowflake Time Travel is an interesting tool that allows you to access data from any point in the past. For example, if you have an Employee table, and you inadvertently delete it, you can utilize Time Travel to go back 5 minutes and retrieve the data. Snowflake Time Travel allows you to Access Historical Data (that is, data that has been updated or removed) at any point in time. It is an effective tool for doing the following tasks:

Query Data that has been changed or deleted in the past.
Make clones of complete Tables, Schemas, and Databases at or before certain dates.
Tables, Schemas, and Databases that have been deleted should be restored.

As the ability of businesses to collect data explodes, data teams have a crucial role to play in fueling data-driven decisions. Yet, they struggle to consolidate the data scattered across sources into their warehouse to build a single source of truth. Broken pipelines, data quality issues, bugs and errors, and lack of control and visibility over the data flow make data integration a nightmare.

1000+ data teams rely on Hevo’s Data Pipeline Platform to integrate data from over 150+ sources in a matter of minutes. Billions of data events from sources as varied as SaaS apps, Databases, File Storage and Streaming sources can be replicated in near real-time with Hevo’s fault-tolerant architecture. What’s more – Hevo puts complete control in the hands of data teams with intuitive dashboards for pipeline monitoring, auto-schema management, custom ingestion/loading schedules.

All of this combined with transparent pricing and 24×7 support makes us the most loved data pipeline software on review sites.

Take our 14-day free trial to experience a better way to manage data pipelines.

How to Enable & Disable Snowflake Time Travel Feature?

1) enable snowflake time travel.

To enable Snowflake Time Travel, no chores are necessary. It is turned on by default, with a one-day retention period . However, if you want to configure Longer Data Retention Periods of up to 90 days for Databases, Schemas, and Tables, you’ll need to upgrade to Snowflake Enterprise Edition. Please keep in mind that lengthier Data Retention necessitates more storage, which will be reflected in your monthly Storage Fees. See Storage Costs for Time Travel and Fail-safe for further information on storage fees.

For Snowflake Time Travel, the example below builds a table with 90 days of retention.

To shorten the retention term for a certain table, the below query can be used.

2) Disable Snowflake Time Travel

Snowflake Time Travel cannot be turned off for an account, but it can be turned off for individual Databases, Schemas, and Tables by setting the object’s DATA_RETENTION_TIME_IN_DAYS to 0.

Users with the ACCOUNTADMIN role can also set DATA_RETENTION_TIME_IN_DAYS to 0 at the account level, which means that by default, all Databases (and, by extension, all Schemas and Tables) created in the account have no retention period. However, this default can be overridden at any time for any Database, Schema, or Table.

3) What are Data Retention Periods?

Data Retention Time is an important part of Snowflake Time Travel. Snowflake preserves the state of the data before the update when data in a table is modified, such as deletion of data or removing an object containing data. The Data Retention Period sets the number of days that this historical data will be stored, allowing Time Travel operations ( SELECT, CREATE… CLONE, UNDROP ) to be performed on it.

All Snowflake Accounts have a standard retention duration of one day (24 hours) , which is automatically enabled:

At the account and object level in Snowflake Standard Edition , the Retention Period can be adjusted to 0 (or unset to the default of 1 day) (i.e. Databases, Schemas, and Tables).
The Retention Period can be set to 0 for temporary Databases, Schemas, and Tables (or unset back to the default of 1 day ). The same can be said of Temporary Tables.
The Retention Time for permanent Databases, Schemas, and Tables can be configured to any number between 0 and 90 days .

4) What are Snowflake Time Travel SQL Extensions?

The following SQL extensions have been added to facilitate Snowflake Time Travel:

OFFSET (time difference in seconds from the present time)
STATEMENT (identifier for statement, e.g. query ID)
For Tables, Schemas, and Databases, use the UNDROP command.

The maximum Retention Time in Standard Edition is set to 1 day by default (i.e. one 24 hour period). The default for your account in Snowflake Enterprise Edition (and higher) can be set to any value up to 90 days :

The account default can be modified using the DATA_RETENTION_TIME IN_DAYS argument in the command when creating a Table, Schema, or Database.
If a Database or Schema has a Retention Period , that duration is inherited by default for all objects created in the Database/Schema.

The Data Retention Time can be set in the way it has been set in the example below.

Using manual scripts and custom code to move data into the warehouse is cumbersome. Frequent breakages, pipeline errors and lack of data flow monitoring makes scaling such a system a nightmare. Hevo’s reliable data pipeline platform enables you to set up zero-code and zero-maintenance data pipelines that just work.

Reliability at Scale : With Hevo, you get a world-class fault-tolerant architecture that scales with zero data loss and low latency.
Monitoring and Observability : Monitor pipeline health with intuitive dashboards that reveal every stat of pipeline and data flow. Bring real-time visibility into your ELT with Alerts and Activity Logs
Stay in Total Control : When automation isn’t enough, Hevo offers flexibility – data ingestion modes, ingestion, and load frequency, JSON parsing, destination workbench, custom schema management, and much more – for you to have total control.
Auto-Schema Management : Correcting improper schema after the data is loaded into your warehouse is challenging. Hevo automatically maps source schema with destination warehouse so that you don’t face the pain of schema errors.
24×7 Customer Support : With Hevo you get more than just a platform, you get a partner for your pipelines. Discover peace with round the clock “Live Chat” within the platform. What’s more, you get 24×7 support even during the 14-day full-feature free trial.
Transparent Pricing : Say goodbye to complex and hidden pricing models. Hevo’s Transparent Pricing brings complete visibility to your ELT spend. Choose a plan based on your business needs. Stay in control with spend alerts and configurable credit limits for unforeseen spikes in data flow.

When you alter a Table’s Data Retention Period, the new Retention Period affects all active data as well as any data in Time Travel. Whether you lengthen or shorten the period has an impact:

1) Increasing Retention

This causes the data in Snowflake Time Travel to be saved for a longer amount of time.

For example, if you increase the retention time from 10 to 20 days on a Table, data that would have been destroyed after 10 days is now kept for an additional 10 days before being moved to Fail-Safe. This does not apply to data that is more than 10 days old and has previously been put to Fail-Safe mode .

2) Decreasing Retention

Temporal Travel reduces the quantity of time data stored.
The new Shorter Retention Period applies to active data updated after the Retention Period was trimmed.
If the data is still inside the new Shorter Period , it will stay in Time Travel.
If the data is not inside the new Timeframe, it is placed in Fail-Safe Mode.

For example, If you have a table with a 10-day Retention Term and reduce it to one day, data from days 2 through 10 will be moved to Fail-Safe, leaving just data from day 1 accessible through Time Travel.

However, since the data is moved from Snowflake Time Travel to Fail-Safe via a background operation, the change is not immediately obvious. Snowflake ensures that the data will be migrated, but does not say when the process will be completed; the data is still accessible using Time Travel until the background operation is completed.

Use the appropriate ALTER <object> Command to adjust an object’s Retention duration. For example, the below command is used to adjust the Retention duration for a table:

How to Query Snowflake Time Travel Data?

When you make any DML actions on a table, Snowflake saves prior versions of the Table data for a set amount of time. Using the AT | BEFORE Clause, you can Query previous versions of the data.

This Clause allows you to query data at or immediately before a certain point in the Table’s history throughout the Retention Period . The supplied point can be either a time-based (e.g., a Timestamp or a Time Offset from the present) or a Statement ID (e.g. SELECT or INSERT ).

The query below selects Historical Data from a Table as of the Date and Time indicated by the Timestamp:
The following Query pulls Data from a Table that was last updated 5 minutes ago:
The following Query collects Historical Data from a Table up to the specified statement’s Modifications, but not including them:

The AT | BEFORE Clause, in addition to queries, can be combined with the CLONE keyword in the Construct command for a Table, Schema, or Database to create a logical duplicate of the object at a specific point in its history.

Consider the following scenario:

The CREATE TABLE command below generates a Clone of a Table as of the Date and Time indicated by the Timestamp:
The following CREATE SCHEMA command produces a Clone of a Schema and all of its Objects as they were an hour ago:
The CREATE DATABASE command produces a Clone of a Database and all of its Objects as they were before the specified statement was completed:

Using UNDROP Command with Snowflake Time Travel: How to Restore Objects?

The following commands can be used to restore a dropped object that has not been purged from the system (i.e. the item is still visible in the SHOW object type> HISTORY output):

UNDROP DATABASE
UNDROP TABLE
UNDROP SCHEMA

UNDROP returns the object to its previous state before the DROP command is issued.

A Database can be dropped using the UNDROP command. For example,

Similarly, you can UNDROP Tables and Schemas .

In the event of a System Failure or other Catastrophic Events , such as a Hardware Failure or a Security Incident, Fail-Safe ensures that Historical Data is preserved . While Snowflake Time Travel allows you to Access Historical Data (that is, data that has been updated or removed) at any point in time.

Fail-Safe mode allows Snowflake to recover Historical Data for a (non-configurable) 7-day period . This time begins as soon as the Snowflake Time Travel Retention Period expires.

This article has exposed you to the various Snowflake Time Travel to help you improve your overall decision-making and experience when trying to make the most out of your data. In case you want to export data from a source of your choice into your desired Database/destination like Snowflake , then Hevo is the right choice for you!

However, as a Developer, extracting complex data from a diverse set of data sources like Databases, CRMs, Project management Tools, Streaming Services, and Marketing Platforms to your Database can seem to be quite challenging. If you are from non-technical background or are new in the game of data warehouse and analytics, Hevo can help!

Hevo will automate your data transfer process, hence allowing you to focus on other aspects of your business like Analytics, Customer Management, etc. Hevo provides a wide range of sources – 150+ Data Sources (including 40+ Free Sources) – that connect with over 15+ Destinations. It will provide you with a seamless experience and make your work life much easier.

Want to take Hevo for a spin? Sign Up for a 14-day free trial and experience the feature-rich Hevo suite first hand.

You can also have a look at our unbeatable pricing that will help you choose the right plan for your business needs!

Harsh comes with experience in performing research analysis who has a passion for data, software architecture, and writing technical content. He has written more than 100 articles on data integration and infrastructure.

Snowflake Commands

Hevo - No Code Data Pipeline

Continue Reading

Radhika Sarraf

Amazon Redshift Serverless: A Comprehensive Guide

Suraj Poddar

Amazon Redshift ETL – Top 3 ETL Approaches for 2024

Snowflake Features: 7 Comprehensive Aspects

I want to read this e-book.

Supported by

What to Know When You’ve Felt an Earthquake

And what else to be aware of when the Earth moves.

Share full article

A person holds a phone with a text alert that reads “4.7 magnitude earthquake has occurred in the NYC area. Residents are advised to remain indoors and to call 911 if injured.”

By Camille Baker

If you just felt the ground shaking, you might be wondering what happened or how to react the next time an earthquake strikes. Here are the answers to some common questions about earthquakes.

What causes them?

To understand earthquakes, imagine the Earth as an egg, said Mark Benthien, the communications director for the Statewide California Earthquake Center, a research organization.

The egg’s shell represents the Earth’s crust, and “if you look at it from a worldwide view, there are 12 or so major egg pieces of the crust that are called plates,” Mr. Benthien said. Pieces of the egg’s shell — tectonic plates — move around slowly, about as quickly as your fingernails grow, building up pressure between them.

Most earthquakes occur when the force of the moving tectonic plates exceeds the friction between them. When this happens, the pressure releases suddenly and plates move into, past or away from one another. The pressure is released as seismic waves that pass through the earth, causing the ground to shake.

In rare cases, an earthquake can also occur in the interior of a tectonic plate.

Earthquakes can also be caused by human action , such as the disposal of waste fluids as part of the process of oil production.

Can earthquakes be predicted?

No. It is not possible to predict where or when an earthquake might happen.

However, national seismic hazard maps from the U.S. Geological Survey describe how many strong earthquakes are likely to happen in the next 10,000 years in the United States. More earthquakes with damaging shaking are likely to occur along the West Coast, along Alaska’s south coast and in parts of Hawaii, Puerto Rico and the Virgin Islands.

Globally, earthquakes are most likely near the “Ring of Fire,” which spans the Pacific Ocean at the edges of tectonic plates.

You can prepare ahead of time.

Should people worry about earthquakes before they happen? “It’s not that they should be worried, but they should act,” said Mr. Benthien, who leads the Earthquake Country Alliance, an earthquake coordination organization.

The group suggests securing items in your home ahead of time. Fasten down bookcases and other furniture, as well as your television and even your hot water heater. “As our buildings are built better and better, these aspects are really the cause of the most injuries — not the buildings collapsing, but just all this stuff flying around,” Mr. Benthien said.

You should also prepare at least one kit of supplies to use in the aftermath of an earthquake. Among other things, your emergency kit should include a fire extinguisher, extra doses of medications you may be taking, first-aid supplies, food and water, according to the U.S.G.S .

Earthquake early warning systems such as ShakeAlert can also sometimes provide precious seconds of advance warning before shaking strikes your area, said Robert-Michael de Groot, a coordinator for the program, which is run by the U.S.G.S. This gives you more time to react and avoid injury. “ShakeAlert asks you to do what you already do, but do it sooner,” he said. (Here’s how to sign up for U.S.G.S. early warning systems.)

What should I do if an earthquake happens?

The next time you feel the ground shaking, follow these three steps : Drop to the ground, cover your body to prevent injuries — by crawling under a table, for example — and hold on, according to the Earthquake Country Alliance. If you use a wheelchair or walker, remain seated, bend over and cover your head and neck.

Experts used to advise people to stand under a doorway during an earthquake, but they don’t do that anymore, Dr. de Groot said. “There was a time when the doorway was structurally more stable, stronger than the rest of other parts of the house,” he said. “That really isn’t the case anymore.”

No matter where you are when an earthquake strikes, Dr. de Groot said, stay alert and take protective measures. “If you’re driving on the highway at 70 miles an hour, you may not be able to drop, cover and hold on,” he said. “But there’s an idea about having that situational awareness and knowing how to protect yourself no matter where you are.”

“So staying in your place, dropping, is actually the most important part,” he said, advising people to stay low on the ground, and find a way to protect themselves, wherever they are.

Camille Baker is a news assistant working for The Times’s Data team, which analyzes important data related to weather and elections. More about Camille Baker

Skip to main content
Keyboard shortcuts for audio player

Solar eclipse 2024: Follow the path of totality

Solar eclipse, what you need to know to watch monday's total solar eclipse.

The NPR Network

A stunning celestial event is visible across the country Monday, when the moon crosses directly in front of the sun: a total solar eclipse. For those in the path of totality, there will be a few brief moments when the moon completely covers the sun and the world becomes dark.

Traveling for totality? Skip ahead.

This will be the last chance to catch a total solar eclipse in the continental U.S. for about 20 years, so here's what you need to know to safely enjoy!

When is the eclipse?

April 8, 2024 there will be a total solar eclipse that crosses from the Pacific coast of Mexico through the United States.

What is totality and why it matters

According to NASA , totality will start around 11:07 a.m. PDT/1:07 EDT in Mexico and leave Maine at around 1:30 pm PDT/3:30 pm EDT.

Here's what time the eclipse will be visible in your region

Check out this table for when the partial eclipse and totality are visible in each region or check by zip code here.

A partial solar eclipse will be visible across the contiguous United States, so even if you're not directly in the path, you should be able to see something special, weather permitting.

Unable to get to totality? We'll be sharing highlights here from across the NPR Network throughout the day Monday if you can't see it in real time.

Where to see totality?

More than 30 million people live in the path of totality for Monday's eclipse, and many more in nearby areas.

Here's what we know about Monday's weather forecast.

Why totality matters

As NPR's Neil Greenfieldboyce explains , "During a total eclipse, the sky darkens suddenly and dramatically . The temperature drops. Stars come out. Beautiful colors appear around the horizon. And the once-familiar sun becomes a black void in the sky surrounded by the glowing corona — that's the ghostly white ring that is the sun's atmosphere."

Eclipse Science

For april's eclipse, going from 'meh' to 'omg' might mean just driving across town.

A partial eclipse, while still a fun experience, is hardly as dramatic. Those with a view of the partial eclipse will see crescent-shaped shadows like those seen here in 2017.

How to watch safely

If you plan to look directly at the eclipse (partial or totality), you're going to need eclipse glasses handy because looking directly at the sun without proper protection ( traditional sunglasses don't count! ) can be harmful to your eyes.

The perfect celestial soundtrack to the total solar eclipse

As NPR's Joe Hernandez explains, "Proper eye protection must be worn throughout a total solar eclipse — except for the roughly 3 1/2 to 4 minutes when the moon fully obscures the sun, a brief period known as 'totality.' (You will need to take your glasses off during totality to actually see it.)"

If you don't have access to eclipse glasses, you can get crafty with things you have around the house ( like some of us did back in 2017!) More on that here.

Traveling for totality?

The celestial event is driving a ton of domestic travel to the path of totality. If you're headed out of town to view the eclipse, here are some NPR Network resources for areas in the path of totality:

Texas The path of totality crosses through the Lone Star State, with some areas expecting a possible influx of visitors in the hundreds of thousands to catch prime viewing. Our member stations across the state have gathered local resources to help you navigate the region and the eclipse!

San Antonio: Check out the latest from Texas Public Radio
Dallas: Explore KERA's coverage for the latest
Austin: Head to KUT for the best local resources

Arkansas The eclipse will be cutting through the state, putting Little Rock in the path of totality. Check out Little Rock Public Radio for local resources.

The southwestern edge of the state will be well-positioned to witness the total solar eclipse this year. Kentucky Public Radio is covering the eclipse throughout the region, from Kentuckiana eclipse mania to the University of Louisville's free class about the celestial event. Keep an eye on WKMS for the latest local updates.

Missouri The southeastern corner of the state will be in the path of totality, crossing across towns like Whitewater and Ste. Genevieve. Head to St. Louis Public Radio for local coverage and resources. Illinois Carbondale seems to have won the eclipse lottery, being in the path of totality both in 2017 and for this year's eclipse . For resources from across the state, check out Illinois Public Media .

Indiana A huge portion of the state will be within the path of totality, giving cities across Indiana, including Bloomington and Indianapolis, prime viewing of the eclipse.

Bloomington: Check out Indiana Public Media
Indianapolis: Head to WFYI for the latest
Fort Wayne: Just north of the path of totality, WBOI has resources for the Allen County area

Ohio The Buckeye State is getting bisected by this year's path of totality, plunging a number of the state's most populous areas into darkness for a few minutes on Monday.

Cleveland: Head to Ideastream Public Media for the latest.
Columbus: With the capital city just south of totality, head to WOSU for regional resources.
Cincinnati: Totality will just miss the border town. Here are some tips from WVXU on how to navigate the eclipse in the region.

Pennsylvania Only the northwestern-most corner of the state will catch totality, with views from the lakeside in Erie being particularly well-positioned for a stunning viewing experience. WESA has more from across the region.

Plan to watch the eclipse from a wild mountain summit? Be ready for harsh conditions

New York Buffalo, Rochester, Syracuse and Plattsburgh will fall under the path of totality on Monday. If you're planning to travel to the region for the best views, here are some local resources to stay safe and informed:

Buffalo: Head to WBFO for the latest
Syracuse: WAER has more on plans in the Salt City
North Country: NCPR has the latest from across the region, as well as information on local viewing events to check out

Vermont The Green Mountain State will see totality across its most populous region, including Burlington and Montpelier, as well as the Northeast Kingdom on the Canadian border. Vermont Public has everything you need to know to navigate your time in the region to enjoy the eclipse safely. New Hampshire The northernmost region of the Granite State will be in the path of totality, providing prime viewing to those in Coos County. NHPR has info on local events, travel updates as well as special coverage with New Hampshire Public Television. Maine The last state in the path of totality in the U.S., much of Northern Maine will be positioned for prime viewing. The rural region is preparing for an influx of visitors, and safety officials are encouraging visitors and locals alike to be prepared. Maine Public will be covering the eclipse and has everything you need to know to navigate the region safely.

How to document the eclipse safely

With the ease of cell photography , it can be tempting to reach for your phone to document the eclipse and the moments of totality, but make sure to do so safely.

As NPR's Scott Neuman explains , "For starters, you'll need to wear eclipse glasses or similar protective eye gear while aiming your camera or even just observing the eclipse."

Feeling ambitious? Here are a few more tips.

Or if you're not inclined to capture the moment visually, you lean into some other forms of creative expression. Indiana, for example, has named Linda Neal Reising the official poet in the state for this year's eclipse.

As former NPR reporter and eclipse superfan David Baron shared with Life Kit , viewing totality "[is] like you've left the solar system and are looking back from some other world."

So consider focusing on being present in the moment to enjoy the celestial spectacle.

More resources to enjoy the eclipse

Sharing the eclipse with tiny humans? Check out these kid-friendly total solar eclipse learning guides from Vermont Public's But Why, and this great explainer from KERA Kids on the difference between a solar and a lunar eclipse.
Want to see how a solar eclipse alters colors? Wear red and green on Monday
Plan to wander into the wild for the best view? Here are some tips from outdoor experts.
Tips from Bill Nye on the best ways to enjoy the eclipse.

NPR will be sharing highlights here from across the NPR Network throughout the day Monday if you're unable to get out and see it in real time. NPR's Emily Alfin Johnson compiled these resources.

2024 eclipse

Watch CBS News

Solar eclipse maps show 2024 totality path, peak times and how much of the eclipse people could see across the U.S.

By Aliza Chasan

Updated on: April 9, 2024 / 5:00 AM EDT / CBS News

A total solar eclipse crossed North America Monday with parts of 15 U.S. states within the path of totality. Maps show where and when astronomy fans could see the big event as skies darkened in the middle of the day Monday, April 8.

The total eclipse first appeared along Mexico's Pacific Coast at around 11:07 a.m. PDT, then traveled across a swath of the U.S., from Texas to Maine, and into Canada.

About 31.6 million people live in the path of totality , the area where the moon fully blocked out the sun , according to NASA. The path ranged between 108 and 122 miles wide. An additional 150 million people live within 200 miles of the path of totality.

Solar eclipse path of totality map for 2024

United states map showing the path of the 2024 solar eclipse and specific regions of what the eclipse duration will be.

The total solar eclipse started over the Pacific Ocean, and the first location in continental North America that experienced totality was Mexico's Pacific Coast, around 11:07 a.m. PDT, according to NASA. From there, the path continued into Texas, crossing more than a dozen states before the eclipse enters Canada in southern Ontario. The eclipse exited continental North America at around 5:16 p.m. NDT from Newfoundland, Canada.

The path of totality included portions of the following states:

Pennsylvania
New Hampshire

Small parts of Tennessee and Michigan also experienced the total solar eclipse.

Several major cities across the U.S. were included in the eclipse's path of totality, while many others saw a partial eclipse. These were some of the best major cities for eclipse viewing — though the weather was a factor :

San Antonio, Texas (partially under the path)
Austin, Texas
Waco, Texas
Dallas, Texas
Little Rock, Arkansas
Indianapolis, Indiana
Dayton, Ohio
Cleveland, Ohio
Buffalo, New York
Rochester, New York
Syracuse, New York
Burlington, Vermont

Map of when the solar eclipse reached totality across its path

The eclipse began in the U.S. as a partial eclipse beginning at 12:06 p.m. CDT near Eagle Pass, Texas, before progressing to totality by about 1:27 p.m. CDT and then moving along its path to the northeast over the following few hours.

NASA shared times for several cities in the path of totality across the U.S. People could have also checked their ZIP code on NASA's map to see when the eclipse was to reach them if they were on, or near, the path of totality — or if they saw a partial eclipse instead.

How much of the eclipse did people see if they live outside the totality path?

While the April 8 eclipse covered a wide swath of the U.S., outside the path of totality observers may have spotted a partial eclipse, where the moon covers some, but not all, of the sun, according to NASA. The closer they were to the path of totality, the larger the portion of the sun that was hidden.

NASA allowed viewers to input a ZIP code and see how much of the sun was to be covered in their locations.

Could there be cloud cover be during the solar eclipse?

Some areas along the path of totality had a higher likelihood of cloud cover that could interfere with viewing the eclipse. Here is a map showing the historical trends in cloud cover this time of year.

You could have checked the latest forecast for your location with our partners at The Weather Channel .

United States map showing the percent of cloud cover in various regions of the eclipse path on April 8. The lakeshore region will be primarily affected.

Where did the solar eclipse reach totality for the longest?

Eclipse viewers near Torreón, Mexico, got to experience totality for the longest. Totality there lasted 4 minutes, 28 seconds, according to NASA.

Most places along the centerline of the path of totality saw a totality duration of between 3.5 and 4 minutes, according to NASA. Some places in the U.S. came close to the maximum; Kerrville, Texas, had a totality duration of 4 minutes, 24 seconds.

What is the path of totality for the 2044 solar eclipse?

The next total solar eclipse that will be visible from the contiguous U.S. will be on Aug. 23, 2044.

Astronomy fans in the U.S. will have far fewer opportunities to see the 2044 eclipse they had on April 8. NASA has not yet made maps available for the 2044 eclipse but, according to The Planetary Society , the path of totality will only touch three states.

The 2024 eclipse will start in Greenland, pass over Canada and end as the sun sets in Montana, North Dakota and South Dakota, according to the Planetary Society.

Map showing the path of the 2044 total solar eclipse from Greenland, Canada and parts of the United States.

Aliza Chasan is a digital producer at 60 Minutes and CBSNews.com. She has previously written for outlets including PIX11 News, The New York Daily News, Inside Edition and DNAinfo. Aliza covers trending news, often focusing on crime and politics.

More from CBS News

See the list of notable total solar eclipses in the U.S. since 1778

How often do total solar eclipses happen?

When is the next total solar eclipse in the U.S.?

Is it safe to take pictures of the solar eclipse with your phone?

Total solar eclipse April 8, 2024 facts: Path, time and the best places to view

In the U.S., 31 million people already live inside the path of totality.

Scroll down to see the list of U.S. cities where the April 8 total solar eclipse will be visible, the duration of the eclipse in those locations and what time totality will begin, according to GreatAmericanEclipse.com .

"Eclipse Across America," will air live Monday, April 8, beginning at 2 p.m. ET on ABC, ABC News Live, National Geographic Channel, Nat Geo WILD, Disney+ and Hulu as well as network social media platforms.

On April 8, 2024, a historic total solar eclipse will cast a shadow over parts of the United States, prompting a mass travel event to the path of totality -- from Texas to Maine and several states and cities in between.

A total solar eclipse occurs when the moon passes between the sun and the Earth and, for a short time, completely blocks the face of the sun, according to NASA .

PHOTO: Tyler Hanson, of Fort Rucker, Ala., watches the sun moments before the total eclipse, Aug. 21, 2017, in Nashville, Tenn.

The track of the moon's shadow across Earth's surface is called the path of totality, and to witness the April 8 total solar eclipse, viewers must be within the 115-mile-wide path. To discover when to see the solar eclipse in totality or the partial eclipse in locations across the U.S. outside of the path, check out NASA's Eclipse Explorer tool .

Eclipse travel

In the U.S., 31 million people already live inside the path of totality, bringing the celestial phenomenon to their doorsteps, Michael Zeiler, expert solar eclipse cartographer at GreatAmericanEclipse.com told ABC News.

MORE: Eclipse glasses: What to know to keep your eyes safe

But for individuals outside of the path, investing time and money are needed to experience the event in totality.

PHOTO: People watch a partial solar eclipse from the roof deck at the 1 Hotel Brooklyn Bridge on Aug. 21, 2017 in the Brooklyn borough of New York City.

Eclipse chasers, or umbraphiles, are individuals who will do almost anything, and travel almost anywhere, to see totality, according to the American Astronomical Society .

"There's a very active community of solar eclipse chasers and we will go to any reasonable lengths to see solar eclipses anywhere in the world," Zeiler said. "All of us are united in pursuing the unimaginable beauty of a total solar eclipse."

MORE: The surprising reason why a Texas county issued a disaster declaration ahead of April total solar eclipse

Bringing together both eclipse experts and novice sky watchers, the total solar eclipse on April 8 is projected to be the U.S.'s largest mass travel event in 2024, according to Zeiler, who likened it to "50 simultaneous Super Bowls across the nation."

"When you look at the number of people expected to come to the path of totality for the solar eclipse, we estimate those numbers are roughly the equivalent of 50 simultaneous Super Bowls across the nation, from Texas to Maine," he said.

Eclipse map, path of totality

In the U.S., the path of totality begins in Texas and will travel through Oklahoma, Arkansas, Missouri, Illinois, Kentucky, Indiana, Ohio, Pennsylvania, New York, Vermont, New Hampshire and Maine. Small parts of Tennessee and Michigan will also experience the total solar eclipse, according to NASA.

Best times, places to view eclipse

Below is a list of some American cities where the April 8 total solar eclipse will be most visible -- pending weather forecasts -- the duration of the eclipse in those locations and what time totality will begin, according to GreatAmericanEclipse.com.

Eagle Pass, Texas, 1:27 p.m. CDT: 4 minutes, 23 seconds
Uvalde, Texas, 1:29 p.m. CDT: 4 minutes, 16 seconds
Kerrville, Texas, 1:32 p.m. CDT: 4 minutes, 23 seconds
Austin, Texas, 1:36 p.m. CDT: 1 minute, 53 seconds
Killeen, Texas, 1:36 p.m. CDT: 4 minutes, 17 seconds
Fort Worth, Texas, 1:40 p.m. CDT: 2 minutes, 34 seconds
Dallas 1:40 p.m. CDT: 3 minutes, 47 seconds
Little Rock, Arkansas, 1:51 p.m. CDT: 2 minutes, 33 seconds
Jonesboro, Arkansas, 1:55 p.m. CDT: 2 minutes, 24 seconds
Poplar Bluff, Arkansas, 1:56 p.m. CDT: 4 minutes, 8 seconds
Cape Girardeau, Missouri, 1:58 p.m. CDT: 4 minutes, 6 seconds
Carbondale, Illinois, 1:59 p.m. CDT: 4 minutes, 8 seconds
Mount Vernon, Illinois, 2:00 p.m. CDT: 3 minutes, 40 seconds
Evansville, Indiana, 2:02 p.m. CDT: 3 minutes, 2 seconds
Terre Haute, Indiana, 3:04 p.m. EDT: 2 minutes, 57 seconds
Indianapolis 3:06 p.m. EDT: 3 minutes, 46 seconds
Dayton, Ohio, 3:09 p.m. EDT: 2 minutes, 46 seconds
Wapakoneta, Ohio, 3:09 p.m. EDT: 3 minutes, 55 seconds
Toledo, Ohio, 3:12 p.m. EDT: 1 minute, 54 seconds
Cleveland 3:13 p.m. EDT: 3 minutes, 50 seconds

Pennsylvania

Erie, Pennsylvania, 3:16 p.m. EDT: 3 minutes, 43 seconds
Buffalo, New York, 3:18 p.m. EDT: 3 minutes, 45 seconds
Rochester, New York, 3:20 p.m. EDT: 3 minutes, 40 seconds
Syracuse, New York, 3:23 p.m. EDT: 1 minute, 26 seconds
Burlington, Vermont, 3:26 p.m. EDT: 3 minutes, 14 seconds
Island Falls, Maine, 3:31 p.m. EDT: 3 minutes, 20 seconds
Presque Island, Maine, 3:32 p.m. EDT: 2 minutes, 47 seconds

Total solar eclipse weather forecast on April 8

Apr 7, 3:34 PM

When is the next total solar eclipse?

Apr 8, 12:20 PM

Why schools are closing for total solar eclipse

Apr 4, 7:03 PM

Why April’s total solar eclipse will be historic

Apr 4, 10:23 AM

History and mythology of total solar eclipses

Apr 7, 3:00 PM

ABC News Live

24/7 coverage of breaking news and live events

Vancouver Travel Guide 2024

Updated : March 21, 2024

AAA Travel Editors

Table of contents, getting into vancouver, getting around vancouver, best time to visit vancouver, top things to do in vancouver, best hotels in vancouver, best restaurants in vancouver, vancouver safety tips.

Planning Your Trip to Vancouver

Vancouver, BC is a bustling, modern city set between the vast Pacific Ocean and the towering North Shore Mountains. This unique setting makes Vancouver an exceptional destination for all types of travelers, from seasoned backpackers and adventurers to families wanting to explore an artful city with occasional outdoor outings. In short: Vancouver offers the best of Canada in one easy-to-navigate city.

Vancouver is constantly growing and you can enjoy restaurants that borrow from culinary traditions around the world along with a great combination of historical neighborhoods and modern skyscrapers. Visit local museums and outdoor parks to experience the city’s unique culture, or spend a night on the town with wine tastings, pub crawls along cobblestone streets and local theater events. Of course, no trip to Vancouver is complete without enjoying its breathtaking nature and there are activities available for both serious hikers and those just looking for a quick view.

Whether you wish to set sail and whale watch near glaciers or enjoy the mountainous and modern skyline from a rooftop bar, this guide to visiting Vancouver will help you explore the best things to do on your next Canadian adventure.

Pacific Standard Time

The Canadian Dollar

Vancouver International Airport

The Vancouver International Airport (YVR) is only 10 miles away from downtown and there are a number of ways to cheaply and comfortably reach your destination from all the terminals:

• The SkyTrain takes less than 30 minutes to reach downtown and it’s one of the most affordable options available for travelers (Roughly $5 per ticket.)

• Taxis average at around $40 and while generally very quick, may take longer than the SkyRail if traffic is heavy.

• Shuttles are available as well and may be free depending on your airline and hotel destination.

• Rental cars via Hertz and other brands are also available at YVR, making traveling convenient for any budget.

Vancouver is a major hub for cruises of all sizes that arrive from Washington, Alaska, plus other North American and international ports. While American travelers will not need a visa to enter Canada, note that some international visitors may be required to apply for a visa in advance, regardless of whether they enter by air, sea, or land.

If you’re in Vancouver and want to explore the incredible waters and nearby coastal regions, there are also many day-trips or even hour-long cruises available that are perfect for viewing the glaciers, whale watching, or traveling further towards Alaska for a great outdoor adventure.

For visitors coming into Vancouver from the United States, there are 13 land border crossings along Washington State which provide the easiest access when driving into the city.

The most common border crossings include:

• Peace Arch

• Blaine Surrey

• Lynden Aldengrove

• Sumas Huntingdon

Vancouver has multiple districts and vast parks to explore on foot, although getting from one end of the city to the other will require other forms of transportation. Plus, with many nearby national parks and outdoor wonders to explore, it's worth thinking about your transportation ahead of time so you never miss a bit. This quick Vancouver travel guide on transportation will help you plan your trip so you can easily explore everything you want on your next vacation.

Public Transit

Vancouver’s buses are a great transportation option, providing fast, clean and comfortable service throughout the city. The buses run from 5 a.m. until 1 a.m. and they can quickly get through downtown towards main attractions like Stanley Park, among other essential parks and destinations. While many routes are direct, you may occasionally have to change lines when connecting from within the city towards the marina.

The SkyTrain is another option similar to the bus, although it provides a faster service with fewer stops. However, it can be a great way to quickly cross greater distances without a car.

Biking and Walking

Vancouver is a fantastic city to walk in and you can walk endlessly in the downtown area and along the bay for great views and exciting entertainment opportunities at any hour.

If you wish to travel a greater distance, you can also rent a bicycle and enjoy Vancouver’s extensive bike baths and green lines along the road for safe and efficient cycling. Be cautious of the weather, however, as cold weather or rain may make cycling difficult in certain seasons.

Vancouver has a number of ride-share options, including popular international brands like Uber and Lyft, along with more local apps, making it a great option for quick and convenient transportation throughout the city.

However, for longer-distance drives, such as to nearby national parks, it may be worth renting a car as ridesharing may be expensive or unavailable further into the wilderness.

Rental Car via Hertz

Address: 3880 Grant McConachie Way, Vancouver, BC V7B 1Y7

Save time and money on your Vancouver vacation by renting a car from Hertz at the Vancouver International Airport. Choose from a wide range of models to fit your style and budget and enjoy quick and easy access to your hotel and all the city’s greatest attractions.

You can also utilize AAA and Hertz at other car rental locations throughout the city and surrounding areas.

Vancouver is beautiful in any season, but each season may provide something unique and exciting to help enhance your vacation.

September and October are often less crowded than the warm summer months, yet the weather is still sunny and ideal for walking and long days of sightseeing. While summer is certainly warmer, it will be much more crowded as it’s considered peak tourism season.

Starting in November, Vancouver experiences a lot of rain which usually lasts until March. During this time, there may also be occasional snowfall within the city, which can cause traffic congestion and mild travel issues. The surrounding mountainous regions will likely have a lot of snow during winter as well, making travel to the nearby parks more difficult.

From March to May, you can enjoy a milder climate with fewer crowds, which makes it a great time for cruises and general travel and potentially less expensive hotel stays as well.

From mid-May to August, you’ll experience the warmest months but also the most crowds and highest hotel prices.

From exceptional outdoor excursions to historical and modern metropolitan entertainment, here are some great things to do in Vancouver on your next vacation.

Whale Watching Boat Tour

Enjoy a three-hour whale watching tour to see Orcas, Humpback Whales and other exciting marine life in this scenic tour. While three hours is the most common tour duration, there are shorter and longer cruises available depending on your preferences.

Enjoy a Night in Downtown Vancouver

Along Granville Street in downtown Vancouver, you can walk among endless pubs, nightclubs and exciting new restaurants covered with bright neon lights. Plus, there are many historical side streets to get lost in and explore at any hour.

Families can also enjoy a night walk and watch as the city transforms into a colorful and bustling destination. However, couples and adult groups will likely want to stay out until later to enjoy the energetic buzz of nighttime Vancouver.

Granville Island Public Market

Address: 1689 Johnston St., Vancouver, BC V6H 3R9

The Granville Island Public Market is right along the water and it’s a large complex that features over 50 unique restaurants and shopfronts to explore. From handmade pastries to delectable meals served to-go, along with everything from coffee to local wines and spirits, you can experience the best of Vancouver’s cuisine in one scenic and unforgettable location. There are many tables available as well, both indoors and outside along the marina.

This place is great for couples and families with kids of all ages. After your meal, make sure to walk around the harbor for incredible views of the city and bay.

VanDusen Botanical Garden

Address: 5251 Oak St., Vancouver, BC V6M 4H1

The VanDusen Botanical Garden is great for families and couples and it features multiple colorful sub-gardens that you can walk around in for hours. Each garden is carefully cultivated and you can view gorgeous rose gardens, rows of cherry trees, magnolias and many other bright seasonal offerings.

Stanley Park

Address: 7500 Stanley Park Dr., Vancouver BC V65 1Z4

Stanley Park is a vast, 400-hectare rainforest that features incredible trails, lakes and views of both the pristine bay and modern Vancouver. You could easily spend an entire day here hiking, riding your bike, or lounging among the gorgeous native fauna. You could also pass through relatively quickly by sticking to the main trails near the waterfront, making this a great place for those wanting a scenic view along with those looking to break a sweat.

There are also guided tours available if you wish to learn more about the region or get taken to a specific point of interest.

Vancouver Art Gallery

Address: 750 Hornby St., Vancouver, BC V6Z 2H7

The Vancouver Art Gallery is one of Canada’s largest collections of historical art and this outstanding museum features paintings from the 20th century along with modern art and rotating exhibitions.

This is a great place for those interested in history, art and local culture and it's suitable for all ages.

From luxurious to budget-friendly, Vancouver has a wide range of hotels available for your next vacation. Let’s look at some different options to help you get started.

The Westin Bayshore Vancouver

Address: 1601 Bayshore Dr, Vancouver, BC V65 2V4
Rates: From $235
Amenities: Free onsite parking and valet, two restaurants, full bar, sauna, onsite peninsula marina, eco certification, indoor and outdoor heated pools, pet friendly in certain rooms

The Westin Bayshore Vancouver is a AAA Four Diamond designated luxurious and modern hotel right on Vancouver’s idyllic peninsula. With stunning views in every room, either of the bay or the surrounding skyline and mountains, you’ll feel pampered with the finest amenities and stylish comfort.

Great for couples, groups and solo travelers who desire luxury during their vacation.

Hilton Vancouver Downtown

Address: 433 Robson St, Vancouver, BC, V6B 6L9
Rates: From $274
Amenities: Paid onsite parking and valet, restaurant, full bar, sauna, heated outdoor pool, pet friendly in certain rooms, valet laundry, eco certification

With an amazing downtown location, the AAA Three Diamond designated Hilton Vancouver Downtown allows you to enjoy the best of the city while living in modern luxury.

The airy rooms and decor make each room a pleasure to relax in and the views of the surrounding city are breathtaking at any hour.

Great for couples, those who wish to explore the city on a whim and families with children of all ages.

Residence Inn by Marriott Vancouver Downtown

Address: 1234 Hornby St, Vancouver, BC, V6Z 1W2
Rates: $157
Amenities: On-site for a fee, heated indoor pool, valet and coin laundry, close to downtown, eco certification

The Residence Inn by Marriott Vancouver Downtown is a AAA Three Diamond designated great option for families and couples who desire modern comfort without a premium price tag. Even with a lower price than other downtown hotels, the Residence Inn still provides spacious, comfortable and welcoming rooms that make you feel right at home.

Plus, the location makes exploring downtown on foot very simple and you’re a close drive to many of the city’s best attractions.

Great for couples, families with children and those looking for a comfortable, modern hotel at a reasonable price.

Vancouver has great dining options for all budgets and tastes and to help make your trip unforgettable, here are some must-visit places suitable for any occasion.

Blue Water Cafe + Raw Bar

Address: 1095 Hamilton St, Vancouver, BC V6B 5T4
Type: Seafood
Price: $$$$

Situated in a cozy, historical warehouse, the Blue Water Cafe + Raw Bar holds a AAA Four Diamond designation and is one of Vancouver's highest-rated restaurants for fresh caught seafood, oysters and premium steaks.

Whether you want a classic salmon steak or beef ribeye, or a more interesting and unique presentation of delectable oysters and raw shellfish, you’re guaranteed to have an exceptional meal. Plus, they have a fantastic wine and cocktail menu to make your night complete.

Reservations are highly recommended.

Address: 1038 Canada Place, Vancouver, BC, V6C 0B9
Type: Canadian, Pacific Northwest

Botanist is a trendy and beautifully decorated restaurant with a AAA Four Diamond designation that serves premium and artful meals in a relaxing setting. Try the local halibut, or an expertly prepared lamb rack, along with a delectable wine selection. The expert chefs are also visible at all times, making it an immersive and unforgettable experience.

Café Medina

Address: 780 Richards St, Vancouver, BC V6B 3A4
Type: Canadian, Breakfast

Café Medina is one of Vancouver’s best spots for all-day breakfast. Enjoy Belgian waffles, home-style potatoes, plus a wide variety of omelets, side meats and fresh pastries. Along with great coffee and a cozy setting, this is a great spot for families and couples looking to start the day off right.

Rogue Kitchen and Wetbar

Address: 602 Broadway, Vancouver, BC, V5Z 1G1
Type: Canadian, Pub Food

Rogue Kitchen and Wetbar is a casual yet cozy brewery that offers over 30 beers on tap along with fantastic cocktails and spirits. Plus, they serve up great burgers, pastas and salads among other pub-style offerings.

This is a great place to enjoy a casual dinner and drinks before hitting the town.

Reservations are recommended on weekends and holidays.

Vancouver is overall a very safe city for travelers of all ages, although it’s important to take proper precautions when hiking or venturing into nearby parks and trails.

For personal safety within the city, you should exercise standard precautions as you would in any other major city. While the overall crime rate is very low, make sure not to leave valuables visible in your car when away and take general safety precautions when walking in unfamiliar areas at night. Be aware of your surroundings and stay in well-lit areas of the city later in the evening.

Weather and Outdoor Safety

Vancouver can get quite cold in the autumn and winter and it’s important to have proper clothing while staying in the city. This includes:

• A winter jacket

• Non-slip winter boots

• Beanie or warm hat

All of these standard winter items can keep you warm and healthy during your stay.

When hiking, you may wish to bring the following items since the weather can change quickly on mountainous trails:

• Thermal pants

• Water-resistant clothing or ponchos

• Extra water and food

If hiking further outside the city limits, such as in the surrounding mountains and wilderness, please take proper precautions since these areas are often untamed and serious situations may arise.

Some safety tips in the wilderness to consider include:

• The presence of black bears and grizzly bears in certain regions. Do not engage with wildlife and talk to local rangers about whether there has been any recent bear activity. Some parks may also allow the use of bear mace if necessary.

• Tell people you know and the local rangers where you’re hiking and stay on the trail at all times.

• Expect there to be limited or no cell reception when hiking in the wilderness.

• Bring extra water and food.

• Bring a paper map to help you stay on the trail.

• If traveling far, bring a signaling device, such as a whistle, to make yourself known if lost.

Planning Your Trip to Vancouver

When thinking of the best time to visit Vancouver it’s important to know that AAA members can access perks and take advances for vacation planning such as getting the best discounts on hotels , rental cars and entertainment tickets.

AAA Travel Editors are AAA Travel Experts.

Travel like an expert with aaa and trip canvas, get ideas from the pros.

As one of the largest travel agencies in North America, we have a wealth of recommendations to share! Browse our articles and videos for inspiration, or dive right in with preplanned AAA Road Trips, cruises and vacation tours.

Build and Research Your Options

Save and organize every aspect of your trip including cruises, hotels, activities, transportation and more. Book hotels confidently using our AAA Diamond Designations and verified reviews.

Book Everything in One Place

From cruises to day tours, buy all parts of your vacation in one transaction, or work with our nationwide network of AAA Travel Agents to secure the trip of your dreams!

IMAGES

Types of Tables in Snowflake.
Understanding & Using Time Travel
How to... define transient table to BC's in Ansys Fluent
Simulated transient travel time
Snowflake Convert Table To Transient Data
When to Begin? Time and Events in Transient Studies

COMMENTS

Understanding & Using Time Travel
For transient databases, schemas, and tables, the retention period can be set to 0 (or unset back to the default of 1 day). The same is also true for temporary tables. ... During that time, the TIME_TRAVEL_BYTES in table storage metrics might contain a non-zero value even when the Time Travel retention period is 0 days.
Working with Temporary and Transient Tables
A long-running Time Travel query will delay the purging of temporary and transient tables until the query completes. Fail-safe Notes¶ The Fail-safe period is not configurable for any table type. Transient and temporary tables have no Fail-safe period. As a result, no additional data storage charges are incurred beyond the Time Travel retention ...
Storage Costs for Time Travel and Fail-safe
Transient tables can have a Time Travel retention period of either 0 or 1 day. Temporary tables can also have a Time Travel retention period of 0 or 1 day; however, this retention period ends as soon as the table is dropped or the session in which the table was created ends. Transient and temporary tables have no Fail-safe period.
Snowflake Temporary Tables vs. Transient Tables
Time Travel allows you to query data as it existed at a specific point in time. At the same time, Fail-safe protects historical data in the event of unexpected system failures or security breaches. Both temporary and transient tables can have Time Travel enabled with a retention period of up to one day. This means you can query the table data ...
Snowflake Temporary Tables and Transient Tables vs. SQL Temporary Tables
The Time Travel feature can be set up to one day for temporary and transient tables. The syntax of creating and referring to these tables is also different in these two systems. In SQL Server, local temporary table names start with # and global temporary table names with ##.
Snowflake Transient Tables, Usage and Examples
Because transient tables do not have a Fail-safe period, they provide a good option for managing the cost of very large tables used to store transitory data. However, you or Snowflake cannot recover the data after the Time Travel retention period passes. Snowflake Transient Table Syntax. To create a temporary table, simply specify the TRANSIENT ...
Snowflake Time Travel
The fail-safe cannot be queried. By default, time travel is enabled on every table. You can however shorten the data retention period or you can turn it completely off for a table. Those tables are called transient tables. Good candidates for transient tables are staging tables or tables which are truncated and re-loaded every day.
Snowflake configurations
Transient tables Snowflake supports the creation of transient tables. Snowflake does not preserve a history for these tables, which can result in a measurable reduction of your Snowflake storage costs. Transient tables participate in time travel to a limited degree with a retention period of 1 day by default with no fail-safe period.
How to Use Time Travel in Snowflake the Right Way
And transient tables have max 1-day time travel. However, the following sequence allows a permanent table with 90-day time travel in a transient schema (which could be a bug): CREATE OR REPLACE DATABASE tt_db DATA_RETENTION_TIME_IN_DAYS=3; CREATE TRANSIENT SCHEMA schema1;-- this confirms a 90-day time travel for a permanent table!
Table Types in Snowflake · Alisa in Techland
Snowflake reference. A transient table is a table without fail safe! A transient table can still have time travel of up to 1 day, but it won't be recovered in case of a complete database failure. So I would build transient tables for any table you don't want more than 1 day of time travel on and you can easily re-build in case of a failure.
Snowflake Series
Time Travel (Source: Snowflake) It is an interesting tool that helps us in performing the below tasks: Query the historical data until the retention period. Undrop a mistakenly dropped database, schema, and table. Create a snapshot of the database, schema, and table via cloning. Analyzing data manipulation over different periods of time.
Snowflake Tables: Explained Simply
Transient tables do not have a fail-safe period, meaning once the time travel retention period is over it is impossible to recover the lost data. Since transient tables do not utilize fail-safe, there are no additional data storage charges are incurred beyond the Time Travel retention period. CREATE TRANSIENT TABLE trans_table (id NUMBER, name ...
Snowflake Dynamic Tables—Simple Way to Automate Data Pipeline
What is the difference between transient table and permanent table in Snowflake? Transient tables in Snowflake persist only until explicitly dropped, have no fail-safe period, and limited time travel. Permanent tables persist until dropped, have a 7-day fail-safe period, and larger time travel. Transient tables suit temporary data while ...
Time Travel in Snowflake
Transient tables can have a Time Travel retention period of either 0 or 1 day. Temporary tables can also have a Time Travel retention period of 0 or 1 day; however, this retention period ends as soon as the table is dropped or the session in which the table was created ends. Transient and temporary tables have no Fail-safe period.
Which Snowflake Table Type Should You Use?
Transient Tables. Transient tables are tables that persist until explicitly dropped. They are available to all users with the correct privileges. The main difference between transient and permanent tables is that transient tables do not have a Fail-safe period as well as having a lower Time Travel retention period of 0 or 1 day (default is 1 ...
Snowflake Time Travel and Fail-safe
Aside from the Time Travel feature, Snowflake provides the fail-safe feature to ensure data protection in case of failure. Fail-safe provides a non-configurable 7-day further storage of historical data in permanent after the time travel period has ended. For transient and temporary tables, the fail-safe period is 0 days.
Are Temporary Table and Transient Table used for Time Travel?
Only permanent tables offer the 7 day fail safe period, which on the other hand consumes more storage. So mostly transient tables are used for volatile tables, that do not hold data permanently to save storage costs. yes they can be used for time travel up to 1 day (s). Though they do not have the fail-safe storage of 7 days.
time travel
Instead of using temporary or transient table we accidentally leveraged permanent tables which resulted in huge storage sizes as part of time travel ... KB Article for Time Travel on dropped tables. After temporarily renaming your new tables, undrop your old tables. You can then set their retention to 0 (be 100% you want this), and then drop ...
Leveraging Time Travel in Snowflake: A Guide
Nov 28, 2023. Learn how to leverage Snowflake's Time Travel feature in conjunction with DbVisualizer to effortlessly explore historical data, restore tables to previous states, and track changes ...
Snowflake Time Travel: The Ultimate Guide 101
Snowflake Time Travel is an interesting tool that allows you to access data from any point in the past. For example, if you have an Employee table, and you inadvertently delete it, you can utilize Time Travel to go back 5 minutes and retrieve the data. Snowflake Time Travel allows you to Access Historical Data (that is, data that has been ...
What time the 2024 solar eclipse started, reached peak totality and
New York City will also see a substantial partial eclipse, beginning at 2:10 p.m. ET and peaking around 3:25 p.m. ET. In Boston it will begin at 2:16 p.m. ET and peak at about 3:29 p.m. ET. The ...
What to Know When You've Felt an Earthquake
David McNew/Getty Images. The next time you feel the ground shaking, follow these three steps: Drop to the ground, cover your body to prevent injuries — by crawling under a table, for example ...
What you need to know to watch Monday's total solar eclipse
Over 30 million people will be within the path of totality for Monday's solar eclipse as it crosses the U.S. from Texas to Maine. Here's what you need to know to safely enjoy the celestial spectacle.
Solar eclipse maps show 2024 totality path, peak times and how much of
The total eclipse will first appear along Mexico's Pacific Coast at around 11:07 a.m. PDT, then travel across a swath of the U.S., from Texas to Maine, and into Canada.
Total solar eclipse April 8, 2024 facts: Path, time and the best places
Bringing together both eclipse experts and novice sky watchers, the total solar eclipse on April 8 is projected to be the U.S.'s largest mass travel event in 2024, according to Zeiler, who likened ...
Vancouver Travel Guide 2024
Residence Inn by Marriott Vancouver Downtown. Address: 1234 Hornby St, Vancouver, BC, V6Z 1W2. Rates: $157. Amenities: On-site for a fee, heated indoor pool, valet and coin laundry, close to downtown, eco certification.