Skip to content
Dr. Farrukh Akhtar
All insights

· Databricks

Databricks Temporary Tables (Public Preview) — What It Is and Why

Databricks just introduced Temporary Tables in SQL — now available in public preview on SQL warehouses — and it’s a simple but powerful addition to the Lakehouse toolkit that makes working with intermediate session da…

Databricks just introduced Temporary Tables in SQL — now available in public preview on SQL warehouses — and it’s a simple but powerful addition to the Lakehouse toolkit that makes working with intermediate session data far smoother.

Whether you’re an analytics engineer, data architect, or ETL pipeline developer, this feature can improve how you build and iterate on SQL workloads within a session.

What Are Temporary Tables?

Temporary tables in Databricks are session-scoped tables that store data only for the duration of your SQL session. They behave like physical Delta tables during that session but do not persist in your catalog — meaning they disappear automatically when the session ends.

You can define a temporary table using SQL like:

CREATE TEMPORARY TABLE temp_customers (

id INT,

name STRING,

email STRING

);

or populate it directly from a query:

CREATE TEMP TABLE temp_recent_orders AS

SELECT order_id, customer_id, order_date, amount

FROM prod.sales.orders

WHERE order_date >= current_date() - INTERVAL 30 DAYS;

Within the session, you can query, insert, update, and even merge data into these tables just like any other table — but when your session ends, Databricks will automatically clean them up.

Why This Feature Matters

This is a welcome evolution in SQL workflows for several reasons:

1. Better Intermediate Workflow Support

Before temporary tables, you would often:

  • Create persistent tables for intermediate steps
  • Manually manage cleanup
  • Or use views (which re-run queries each time)

Temporary tables give you a materialized intermediate result without cluttering your catalog.

2. Performance and Productivity

Since temporary tables behave like physical tables and leverage Delta’s performance characteristics, they can reduce recomputation in iterative SQL or pipeline development.

This makes rapid prototyping and exploration more efficient.

3. Catalog Cleanliness

One of the perennial issues in data teams is leftover ephemeral data in catalogs. Temporary tables avoid that entirely because they’re scoped to your session and cleaned up automatically.

When to Use Temporary Tables

Use temporary tables when you need:

  • Short-lived intermediate results
  • Iterative analysis inside a SQL warehouse session
  • A way to reuse query results across multiple SQL statements during exploration or testing
  • A materialized result without committing to a permanent table

If your data needs to be shared across sessions, or used in production jobs or other users’ workflows, a permanent Unity Catalog table is still the best choice.

Limitations to Know

Temporary tables:

  • Only exist in the current session
  • Are available on SQL warehouse compute (not classic/serverless)
  • Do not persist beyond session end
  • Do not support SQL features like time travel the way catalog Delta tables do yet — and that’s expected given their scope.

How This Fits With Existing Options

Databricks already supports:

  • Views — which are dynamically evaluated
  • Temporary views — session-scoped but virtual
  • Temporary tables — session-scoped and materialized

Choosing between these depends on your use case — but having a real temporary table option adds a lot of flexibility to SQL workflows.

Final Thoughts

Temporary tables are not flashy, but they’re very practical — especially for exploratory analysis, SQL pipeline building, or iterative development where intermediate results matter but master data persistence does not.

For teams that build a lot of data transformations in SQL, this feature brings Databricks closer to the comfort and flexibility seen in traditional RDBMS environments while preserving the performance and scale of the Lakehouse.

More writing