What should I do instead of creating a temporary table in dbt?

Help
,
1

I am using snowflake and I need to create a temporary table from dbt and inserting that table using another table and using that temporary table i am updating another table and at the need to drop

2

You can materialize the table as an ephemeral model and it won’t actually create the model in the database, but you’ll be able to use it to populate other tables. Up the top of your model

{{
    config(
        materialized='ephemeral'
    )
}}
1 Like
3

thank you Fraser :+1:

4

dbt® deeper concepts: materialization | The Big Data Tools Blog (jetbrains.com)

5

Fraser’s description of ephemeral models is correct, and they can help to provide a temporary holding area for code, but they can also get complex over time (especially if you stack multiple ephemeral models on top of each other).

In general, the dbt mindset encourages you to move away from thinking about inserting data, then updating other data and dropping a temp table at the end. Instead, try to think of it as writing a select statement that describes the way your table should look at the end, regardless of how it gets there.

By default, dbt will drop and re-create a table during each dbt run, which might feel counterintuitive but dramatically reduces the amount of mental overhead you have to deal with while building models. At the point that this becomes a performance problem, you can look at creating incremental models, but even then you will still describe your model in terms of a select statement and just provide a bit of extra metadata around which rows to delete/insert/update.

If you are coming from a stored procedure world, you may find this article from our developer blog useful: Migrating from Stored Procedures to dbt | dbt Developer Blog

1 Like
6

This topic was automatically closed 7 days after the last reply. New replies are no longer allowed.