Bird
Raised Fist0
SnowflakeHow-ToBeginner · 3 min read

How to Use Transient Tables for Cost Efficiency in Snowflake

Use TRANSIENT tables in Snowflake to store temporary data without the extra cost of fail-safe storage. These tables keep data until explicitly dropped, reducing storage costs compared to permanent tables while still supporting standard table features.
📐

Syntax

The syntax to create a transient table in Snowflake is similar to creating a permanent table but includes the TRANSIENT keyword. This tells Snowflake not to keep fail-safe copies, lowering storage costs.

  • CREATE TRANSIENT TABLE: Creates a transient table.
  • table_name: Name of the table.
  • (column_definitions): Defines columns and data types.
sql
CREATE TRANSIENT TABLE table_name (
  column1 STRING,
  column2 INT
);
💻

Example

This example creates a transient table, inserts data, and queries it. It shows how transient tables work like regular tables but save cost by skipping fail-safe storage.

sql
CREATE TRANSIENT TABLE sales_temp (
  id INT,
  product STRING,
  amount FLOAT
);

INSERT INTO sales_temp VALUES (1, 'Book', 12.99), (2, 'Pen', 1.50);

SELECT * FROM sales_temp;
Output
ID | PRODUCT | AMOUNT ---|---------|-------- 1 | Book | 12.99 2 | Pen | 1.50
⚠️

Common Pitfalls

Common mistakes when using transient tables include:

  • Expecting fail-safe recovery: Transient tables do not have fail-safe, so dropped data cannot be recovered.
  • Using transient tables for long-term storage: They are best for temporary or intermediate data.
  • Confusing transient with temporary tables: Temporary tables exist only in session scope, transient tables persist until dropped.
sql
/* Wrong: Using transient table for critical data needing fail-safe */
CREATE TRANSIENT TABLE critical_data (id INT, info STRING);

/* Right: Use permanent table for critical data */
CREATE TABLE critical_data (id INT, info STRING);
📊

Quick Reference

FeatureTransient TablePermanent Table
Fail-safe StorageNo (cost saving)Yes (extra cost)
Data RetentionUntil droppedUntil dropped
Use CaseTemporary/intermediate dataCritical/long-term data
RecoveryNo fail-safe recoveryFail-safe recovery available
✅

Key Takeaways

Use transient tables to reduce storage costs by skipping fail-safe storage.
Transient tables keep data until you drop them but do not support fail-safe recovery.
Do not use transient tables for critical data that requires long-term retention or recovery.
Transient tables behave like permanent tables except for cost and fail-safe features.
Choose transient tables for temporary or intermediate data processing to save money.