14

I want to create an empty table with specific columns and data types, I don't have any reference table from where I can do SELECT * FROM . The following link has an image which I intend to do Please find the attached image

Shahrukh lodhi
  • 360
  • 2
  • 13
  • 2
    And the table will be empty? – dfundako Sep 29 '21 at 13:51
  • 2
    yes table would be empty. @dfundako – Shahrukh lodhi Sep 29 '21 at 13:54
  • 1
    Why are you wanting to do that through dbt? – dfundako Sep 29 '21 at 14:08
  • Dbt is a transformation tool, it can create table with CTAS why couldn't it create with the simple command "create table test". I run into a scenario where I want to create a table with 10 columns and after that put value in only 5 columns of the new table. @dfundako – Shahrukh lodhi Sep 29 '21 at 14:14
  • I know what dbt is, but what you're trying to accomplish isn't what dbt is used for. The standard dbt approach is using a cte to query from another source/model and materialize a new view/table. You're trying to do a step to make an empty table, which usually doesn't happen in dbt. You're probably better off doing a seed file and defining the datatypes in your yml files. – dfundako Sep 29 '21 at 14:49
  • Thank you! I saw seed in the documentation. So, it means we can't create an empty table on the go. But we lay down the foundation of tables by using seeds. – Shahrukh lodhi Sep 29 '21 at 17:49

1 Answers1

2

You can use pre-hook/post-hook.

In pre-hook/post-hook you can write a create table query (or any other query).

In your use case, I would do something like:

{{
   config(
       pre-hook = "create table test (a int)"
   )
}}

select * 
from test
HagaiA
  • 193
  • 3
  • 15