will DBT support temp table creation like create table #temp1 as select * from tab1 or it works only CTE way

Question

I found out a way to handle the temp tables in DBT, write all those in pre-hook and call the final temp table in the outside of the pre-hook, tested and is working fine, able to reduce the code running time from more than 20 mins to 1 min. But I see one problem that we can't see the lineage graph in the DBT documents. Is there any way to handle the temp tables other than pre-hook and with lineage in Docs?

Answer 1

You're right in thinking that dbt does not support temporary tables. That's because temporary tables only persist in a single session, and dbt opens one connection/session per thread. Therefore any temporary tables created on one thread would not be visible to a model running on a different thread.

It sounds like CTEs are a performance drag for you though — out of interest, which warehouse are you using?

You've identified two workarounds, and there's another one worth discussing:

Option 1: Materialize your model as CTEs using the ephemeral materialization (docs)

Pros:

• The models show up in the lineage graph
• You can re-use these transformations in multiple downstream models by ref-ing them
• You can test and document these models

Cons:

• At some point there is a performance degradation with too many stacked CTEs (especially on older versions of postgres, where CTEs are an optimization fence)
• Compiled SQL can be harder to debug

Option 2: Use pre-hooks to create temp tables

I would generally recommend against this — you can't test or document your models, and they won't be in the lineage graph (as you've noted).

Option 3: Materialize these models as tables in a separate schema, and drop the schema at the end of a run

I think Michael's suggestion is a good one! I'd tweak it just a little bit:

1. Use the schema config to materialize a model in a separate schema

{{ config(
  materialized='table',
  schema='my_temporary_schema'
) }}

2. Then, at the end of a run, use an on-run-end hook (docs) to drop that schema — in your dbt_project.yml:

on-run-end: "drop schema my_temporary_schema cascade"

Pros:

• All the benefits of Option 1
• Sounds like it might be more performant than using CTEs

Cons:

• Make sure you don't have any dependent views on top of that schema! They might get dropped when you run a drop cascade command! This introduces fragility into your project!