dbt how to create custom table without using CTAS?

1
votes

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

1
sqldbt
And the table will be empty? – dfundako
yes table would be empty. @dfundako – Shah lodhi
Why are you wanting to do that through dbt? – dfundako
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 – Shah lodhi
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

1 Answers

0
votes

DBT isn't allowed to create an empty table(empty table with no CTAS). To create custom columns reference a table and add your columns as follow:

Select ID, cast(NULL AS NEW_COLUMN) FROM <REFRENCE_TABLE>

If you want to create a table with values which not frequently change use seed instead. Documentation link