Skip to main content

Transient Tables in DBT

In Snowflake, a transient table is a physical table that is designed for data that does not need Fail-safe protection. It is useful when the data can be recreated from upstream sources and you want to reduce storage cost without losing the benefit of a persistent table.

This is especially relevant in a dbt project where not every model needs the same durability level.

Why use transient tables?​

Think of the flow in a typical e-commerce data pipeline:

RAW
↓
BRONZE
↓
SILVER
↓
GOLD
↓
MART

Not every layer is equally important.

For example, some intermediate models are created to support downstream transformations and can be reproduced from source data. In those cases, you may not need Snowflake's Fail-safe protection.

This is where transient tables become useful.

Permanent vs transient vs temporary​

Table typeTime TravelFail-safeGood forTypical use
Permanent tableYesYesImportant business dataGold and Mart models
Transient tableYesNoRe-creatable intermediate dataSilver or staging-style models
Temporary tableLimited lifecycleNoShort-lived work in a sessionAd hoc analysis

The key concept to remember is:

A transient table is not the same as a temporary table.

A transient table still exists as a Snowflake object and persists until you drop it. It simply skips Fail-safe protection.

Configuring a transient table in dbt​

To make a model transient, you can configure it directly inside the model:

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

SELECT
order_id,
customer_id,
order_date,
total_amount
FROM {{ ref('stg_orders') }}

This tells dbt and Snowflake:

Create this model as a transient table rather than a permanent table.

Example in the e-commerce project​

Suppose we create an intermediate silver model:

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

SELECT
o.order_id,
o.customer_id,
oi.product_id,
oi.quantity,
oi.unit_price,
(oi.quantity * oi.unit_price) AS line_amount
FROM {{ ref('stg_orders') }} o
JOIN {{ ref('stg_order_items') }} oi
ON o.order_id = oi.order_id

This is a good candidate for a transient table because it can be rebuilt from upstream data if needed.

When to use transient tables​

Use transient tables for models that are:

  • intermediate or staging-focused
  • reproducible from upstream data
  • not critical for long-term retention
  • built for performance and storage efficiency

When not to use transient tables​

Avoid transient tables for final business-critical models such as:

  • fact tables used in reporting
  • dimension tables used across multiple downstream pipelines
  • mart tables that are business-critical and need stronger recovery guarantees

For those, permanent tables are usually the safer choice.

A common beginner mistake​

A lot of learners think:

“Transient tables save money because they are temporary.”

That is not the correct mental model.

The correct statement is:

Transient tables are persistent Snowflake tables that do not use Fail-safe protection and are useful for data that can be recreated.

Best practice for this course​

For your training project, a practical rule is:

  • Use transient tables for intermediate models
  • Use permanent tables for Gold and Mart models

This gives you a clean balance between cost efficiency and data durability.

Hands-on example​

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

SELECT
order_id,
customer_id,
total_amount
FROM {{ ref('stg_orders') }}

This is the exact pattern you can teach in class: a model that is easy to rebuild, not mission-critical, and still stored as a Snowflake table.

Summary​

Transient tables are useful when you want a persistent Snowflake table without Fail-safe protection. They are excellent for intermediate, re-creatable dbt models, but they are not a replacement for permanent tables in business-critical reporting layers.

The main takeaway is simple:

Use transient tables for data you can rebuild, and use permanent tables for data you cannot afford to lose.