Skip to main content

Time Travel Snowflake | Infrastructure

Time Travel in Snowflake is an advanced feature included in our Enterprise plan that enables recovery and restoration of data from previous states. This capability can also be contracted as an add-on for other plans.

If you are interested in enabling Time Travel in your environment or want more details on how to use it, contact our Customer Success team at cs@dadosfera.ai. Our team can guide the activation process and help you get the most from this recovery capability.

Time Travel is useful for data recovery, auditing, and change validation. Below is a quick guide for using this feature.

1. Create the Table​

Start by creating a test table or duplicating an existing structure. This will serve as the basis for experimenting with Time Travel.

CREATE OR REPLACE TABLE ESQUEMA.NOME_TABELA(
	COLUNA1 TIPO,
	COLUNA2 TIPO,
)

After creation, query the table to make sure the structure is correct.

SELECT * FROM ESQUEMA.NOME_TABELA;

2. Insert Data​

Insert data into the table to perform future operations.

INSERT INTO ESQUEMA.NOME_TABELA
SELECT * FROM OUTRA_TABELA;

Validate the inserted content with a simple query.

SELECT * FROM ESQUEMA.NOME_TABELA;

3. Update Data​

Make changes to the table, for example by updating specific columns.

UPDATE ESQUEMA.NOME_TABELA
SET COLUNA = VALOR
WHERE CONDICAO;

4. Validate Time Travel​

4.1 Using the AT(OFFSET) clause​

You can query the table state before a change by using a time offset in seconds from the operation:

SELECT *
FROM ESQUEMA.NOME_TABELA  
AT(OFFSET => -60*3); -- Example: 3 minutes ago

4.2 Using the BEFORE(STATEMENT) clause​

Another approach is to query the table state before a specific query by using the operation QUERY_ID.

SELECT * 
FROM ESQUEMA.NOME_TABELA 
BEFORE(STATEMENT => 'QUERY_ID');

5. Restore the Table​

5.1 Restore with BEFORE(STATEMENT)​

To restore the table to the state before a specific query, use the command below with the relevant QUERY_ID:

CREATE OR REPLACE TABLE ESQUEMA.NOME_TABELA AS 
SELECT *  
FROM ESQUEMA.NOME_TABELA  
BEFORE(STATEMENT => 'QUERY_ID');

Verify the restored state with a simple query.

SELECT * FROM ESQUEMA.NOME_TABELA;

5.2 Restore with AT(OFFSET)​

Another option is to restore the table by using a time offset.

CREATE OR REPLACE TABLE ESQUEMA.NOME_TABELA AS 
SELECT * 
FROM ESQUEMA.NOME_TABELA 
AT(OFFSET => -60*2); -- Example: 2 minutes ago

Again, validate the restored table state.

SELECT * FROM ESQUEMA.NOME_TABELA;

Snowflake Time Travel gives you flexibility to query and restore data from previous states. Whether you need to correct accidental changes or support audits, it lets you go back in time and recover data with confidence.

For more details, refer to the official documentation: Snowflake Time Travel.