Starting with Databricks Runtime 16.4, Databricks now supports temporary tables—just like other databases such as Netezza, Snowflake, and Oracle. You can use these tables for storing data that you only need for a short time. They exist only during your current session and are automatically deleted when the session ends. You can’t access them from any other session.
In this blog post, we’ll walk through how to create temporary tables in Databricks, look at the syntax, explore common use cases, and go over some limitations—with some easy-to-follow examples.
Page Contents
Introduction
With the release of Databricks Runtime (DBR) 16.4, Databricks now supports temporary tables, one of the important features. Temporary tables are useful for storing short-term, session-specific data. They are especially helpful when you’re migrating from other databases like Snowflake, Oracle, or Netezza, which already support temp tables. This new feature makes it easier to bring your existing DDLs into Databricks without major changes.
In this blog post, we’ll explore how to create temporary tables in Databricks, how they differ from temporary views. We’ll walk through the syntax, common use cases, and important limitations.
What Are Temporary Tables?
In Databricks, a temporary table is accessible only within the session in which it is created. It presents in a session-specific internal schema that is not visible to user and cannot be named. The table is automatically deleted when the session ends.
Benefits of using Temporary Tables in Databricks
Using temporary tables in Databricks offers several benefits.
- The temp table in Databricks provide a convenient way to store intermediate results during data processing without affecting the underlying database.
- Since they exist only within the current session, they help maintain a clean workspace and reduce the risk of accidental data persistence.
- Temporary tables also improve performance by minimizing I/O operations.
Temporary Tables vs Temporary Views
Following is the short comparison between Databricks temporary tables and temporary views.
| Feature | Temporary Table | Temporary View |
|---|---|---|
| Scope | Session-specific | Session or global (if created as global temp view) |
| Data Storage | Stores actual data | Does not store data; represents a query |
| Persistence | Dropped automatically at session end | Session views are dropped at session end; global views persist within system preserved temporary schema global_temp. |
| Use Case | Intermediate data storage during transformations | Reusable query logic |
| Performance | Faster for repeated access to intermediate results | Depends on underlying query execution |
| Schema Visibility | Internal, not user-visible | Visible in catalog (for global views) |
Databricks Temporary Tables Examples
Here are examples of creating temp tables in Databricks.
CREATE TEMPORARY TABLE temp_sales (
id INT,
product STRING,
revenue DOUBLE
) USING CSV;Conclusion
In conclusion, temp tables in Databricks offer a flexible way to manage intermediate data during transformations. They are session-scoped, automatically cleaned up, and ideal for tasks like data transformation, testing, etc. Their ease of use and transient nature make them a important for data engineers.