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 type | Time Travel | Fail-safe | Good for | Typical use |
|---|---|---|---|---|
| Permanent table | Yes | Yes | Important business data | Gold and Mart models |
| Transient table | Yes | No | Re-creatable intermediate data | Silver or staging-style models |
| Temporary table | Limited lifecycle | No | Short-lived work in a session | Ad 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.