CREATE TEMP TABLE

[dkxxx-rc]

The Databricks assistant tells me (sometimes) that `CREATE TEMP TABLE` is a valid SQL operation.  And other sources (e.g., https://www.freecodecamp.org/news/sql-temp-table-how-to-create-a-temporary-sql-table/) say the same.

But in actual practice, this generates a syntax error.  And also, sometimes the Assistant tells me that it's not a valid operation, and I should use CREATE TEMP VIEW instead.  

But I don't want a view.  I want the data materialized.  Is there an option to actually do TEMP TABLE, or something like it?

[KaranamS · Accepted solution]

Hi @dkxxx-rc ,

You can try to create a temp view and cache it. Caching will materialize it in memory.

CREATE TEMP VIEW temp_view AS

SELECT * FROM some_table WHERE condition

CACHE TABLE temp_view

[ashraf1395]

You can create temp tables in dlt pipelines as well

simply
@Dlt.table(name ="temp_table", temporary = True)

def temp_table():

return <any_query>

[dkxxx-rc]

In addition to accepting KaranamS's answer, I will note a longer and useful discussion, with caveats, at https://community.databricks.com/t5/data-engineering/how-do-temp-views-actually-work/m-p/20137/highl....