topic Re: CREATE TEMP TABLE in Data Engineering

CREATE TEMP TABLE

dkxxx-rc — Fri, 21 Feb 2025 21:44:10 GMT

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?

Re: CREATE TEMP TABLE

KaranamS — Sat, 22 Feb 2025 01:43:55 GMT

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

Re: CREATE TEMP TABLE

ashraf1395 — Sat, 22 Feb 2025 05:52:58 GMT

You can create temp tables in dlt pipelines as well

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

def temp_table():

return <any_query>

Re: CREATE TEMP TABLE

dkxxx-rc — Fri, 28 Mar 2025 15:10:07 GMT

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/highlight/true#M13584.