Skip to main content

Transient Tables in dbt

When building a data warehouse in Snowflake, one of the hidden costs is Time Travel and Fail-safe storage.

If you build a massive Staging table that is recreated completely every single night, you do not need 90 days of Time Travel backups for it. If you accidentally delete it, you can just run dbt run again to recreate it from the raw source!

To save storage costs, Snowflake offers Transient Tables. dbt has native support for configuring models as transient.

Topics Covered​

  • What is a Transient Table?
  • Storage Cost Implications
  • Configuring Transient Tables in dbt
  • Best Practices for Transient Usage

1. What is a Transient Table?​

In Snowflake, a standard Permanent Table comes with two data recovery features:

  1. Time Travel: Allows you to UNDROP a table or query its historical state (up to 90 days).
  2. Fail-safe: A 7-day non-configurable disaster recovery window managed by Snowflake.

A Transient Table is a table that deliberately disables Fail-safe and limits Time Travel to a maximum of 1 day.

Because Snowflake does not have to maintain gigabytes of historical backups for a Transient table, you save massive amounts of storage costs.

2. Configuring Transient Tables in dbt​

You can instruct dbt to build a model as a Transient Table by modifying the transient configuration.

Method 1: Model-Level Configuration​

Use the {{ config() }} block at the top of your .sql file.

{{ config(
materialized='table',
transient=true
) }}

SELECT
order_id,
customer_id,
order_amount
FROM {{ ref('raw_orders') }}

(Note: Transient configuration only applies when materialized='table'. It does not apply to views, as views do not store physical data).

Usually, you want entire folders of models (like all your Staging models) to be transient. You configure this in dbt_project.yml.

# dbt_project.yml
models:
beems_retail:
staging:
+materialized: table
+transient: true # All staging models will be built as Transient Tables
marts:
+materialized: table
+transient: false # Core business tables should remain Permanent

3. When to use Transient Tables​

DO NOT use Transient tables for:

  • marts models (Core Fact and Dimension tables). If these are corrupted, you want the 7-day Fail-safe window to recover your business-critical data.
  • Incremental models that are difficult to rebuild from scratch.

DO use Transient tables for:

  1. Developer Environments: If Arjun (DEV_ARJUN) is building tables locally to test his code, those tables should be Transient. There is no reason to pay for Fail-safe storage on sandbox data.
  2. Intermediate Models: Models that exist purely to join data together before passing it to a final Fact table.
  3. Staging Models: If your staging models are materialized as tables (instead of views) for performance reasons, they should almost always be Transient, because they can be instantly rebuilt from the raw source data.

4. Hands-On: Verifying a Transient Table​

How do you know if dbt successfully created a transient table? You can query Snowflake's Information Schema.

(Run this in your Snowflake worksheet to verify. Check your role with SELECT CURRENT_ROLE();)

SELECT 
TABLE_NAME,
IS_TRANSIENT
FROM BEEMS_PROD.INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA = 'STAGING';

If IS_TRANSIENT is YES, you are successfully saving your company money on storage costs!

Summary​

  • Transient Tables disable Snowflake's Fail-safe storage, significantly reducing storage costs.
  • Set transient=true in your model's config() block or in dbt_project.yml.
  • Use transient tables for Developer schemas, Staging models, and Intermediate models.
  • Never use transient tables for final, business-critical Fact and Dimension tables.