dbt how to create custom table without using CTAS?

Question

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

Severson

And the table will be empty? – Decimalize

yes table would be empty. @Decimalize – Severson

Why are you wanting to do that through dbt? – Decimalize

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. @Decimalize – Severson

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. – Decimalize

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. – Severson

Answer 1

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

Bloodstain