It offers two primary mechanisms for working with temporary data:

1. Temp Views:

• Created using CREATE OR REPLACE TEMPORARY VIEW view_name AS SELECT …
• Similar to temporary tables, they exist only for the duration of the Spark session.
• It can be used in subsequent queries like regular tables.
• Suitable for ad hoc analysis or temporary transformations.

Example:

SQL

CREATE OR REPLACE TEMPORARY VIEW temp_employee AS 

SELECT * FROM employee WHERE department = ‘Sales’;

SELECT * FROM temp_employee; 

2. Delta Tables:

• Offer ACID transactions, time travel, and optimized query performance.
• Persisted and can be used across Databricks sessions.
• It is ideal for storing intermediate or temporary data that needs to be reused or accessed multiple times.
• Provide more flexibility and control over the data compared to temp views.

Example:

SQL

CREATE TABLE temp_customer (

  id INT,

  name STRING

) USING delta;

INSERT INTO temp_customer

SELECT id, name FROM customer WHERE country = ‘USA’;

SELECT * FROM temp_customer;

Choosing the Right Approach:

• Temp Views: For simple, session-specific transformations or quick analysis.
• Delta Tables: For complex transformations, data that needs to persist across sessions, or when you need more advanced features like time travel or ACID transactions.

Note:

Databricks also supports CREATE TEMPORARY TABLE syntax in Delta Live Tables (DLT). However, DLT is a separate feature for building reliable data pipelines, and its temporary tables behave differently from traditional SQL temporary tables.