temp tables in Databricks

[AMadan]

Hey Team,

We are in a migration phase from SQL server to Databricks.

In our sql server databricks, lot of times, #temp tables created and dropped like typical Datawarehousing sql.

In Databricks, we can do 2 things. I want to check which one is best approach and why?

1) create temp views in databricks and make use of views later in the query

2) create delta table with temp(prefix) and make use of temp table and at the end drop temp table.

I am looking forward for your response.

[Kaniz_Fatma]

Hi @AMadan, 

• The best approach for migrating from SQL Server to Databricks depends on specific requirements and use cases.

• Considerations for each approach:

  - Creating Temp Views:
     • Temp views in Databricks are similar to temporary tables in SQL Server
     • Temp views do not persist and are only available for the duration of the Databricks session
     • Can be used in subsequent queries like regular tables
     • Useful for ad hoc analysis or temporary transformations
     • Good approach for complex transformations or multiple queries referencing the same temporary data

  - Creating Delta Tables:
     • Delta tables in Databricks provide ACID transactions, time travel, and optimized query performance
     • Delta tables are persisted and can be used across Databricks sessions
     • Useful for storing and managing 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
     • Good approach for complex transformations or multiple queries referencing the same temporary data

• Choice between temp views and delta tables depends on specific requirements and use cases
• Temp views are suitable for ad hoc analysis or temporary transformations
• Delta tables offer more control and flexibility over temporary data

[berserkersap]

Just to add a bit more from experience

If you are doing to use the temp tables too many times, then using temp views is going to slow down your cluster . It is due to lazy evaluation unless you persist that view (dataframe cache or globaltempview).

Every time your temp view is called, it will execute the view definition/code. This also depends upon your cluster capacity, how complex the view definition is, how much data the temp view has.

Please correct me if I am wrong.