> ## Content Index
> Fetch the complete content index at: https://www.sfrt.io/llms.txt
> Use this file to discover other available public pages before exploring further.

# Transient Database in Snowflake
- URL: https://www.sfrt.io/transient-database-in-snowflake/
- Published: 2023-07-03T12:09:00.000Z
- Updated: 2025-12-03T13:10:05.000Z
- Description: What are transient tables in Snowflake good for?
- Author: Martin Seifert
- Tags: Lakehouse

Transient tables in Snowflake are a weird specialty: They are normal persisting tables (unlike temporary tables), but without fail-safe and limited timetravel capabilities.

🔗 [docs.snowflake.com/en/user-guide/tables-temp-transient](https://docs.snowflake.com/en/user-guide/tables-temp-transient?ref=sfrt.io)

However, because Snowflake doesn't apply fail-safe and time travel capabilities to them, they can be cheaper than normal permanent tables. You probably won't recognize the difference for small tables, but for a table larger than a TB, this can make a difference.

## When to use transient tables?

Let me quote [Serge](https://www.linkedin.com/in/serge-cloud-connected/?ref=sfrt.io) here:

> Transient tables are a good choice when backup and recovery are not a high priority - as in the case of **staging** tables. The same is true for **development and test** environments \[...\]

(Gershkovich, Serge: *Data Modeling with Snowflake*, 1st edition, Packt Publishing Ltd., 2023, p.51)

## Transient database and schema

You probably don't want to remember adding the **transient** keyword to each create-table statement if you are working on a staging or dev database/schema. And you don't have to, because you can default new tables in a database/schema to be transient and just create the tables the same way you would create permanent tables:

🔗 [community.snowflake.com/s/article/Making-Transient-table-by-Default](https://community.snowflake.com/s/article/Making-Transient-table-by-Default?ref=sfrt.io)

### But my staging/dev database/schema already exists!

Unfortunately you can't alter a database/schema/table to become transient. But there is a neat little workaround: **zero-copy-cloning**

🔗 [docs.snowflake.com/en/sql-reference/sql/create-clone](https://docs.snowflake.com/en/sql-reference/sql/create-clone?ref=sfrt.io)

1. Create a clone of your database/schema/table - the clone being transient of course (note: depending on the number of objects in the original database/schema, this might take a few seconds)
2. Swap the clone and it's original database/schema/table
3. Drop the original database/schema/table

Cloning will effectively re-create all the objects in a database/schema, not just tables, but also views, procedures, etc. - but the latter are not affected by the change at all. While re-creating the tables, though, the database/schema defaults to transient, and hence all permanent tables effectively are modified to become transient:

```SQL
create transient database sandbox_tmp
clone sandbox;

alter database sandbox_tmp
swap with sandbox;

drop database sandbox_tmp;
```

If you then check the DDL of any table in the database, you'll recognize them all to be transient now 🥳

## Caution!

1. This approach will not affect [hybrid tables](https://www.snowflake.com/blog/introducing-unistore/?ref=sfrt.io) as they can't be transient (a table can only be of one type at any time, so yeah, of course...), those will be re-created as hybrid tables
2. External tables and internal stages [are not cloned](https://docs.snowflake.com/en/sql-reference/sql/create-clone?ref=sfrt.io#additional-rules-that-apply-to-cloning-objects). If you have any of those in the original database/schema, you'll have to re-create them manually