DBT - Syntax error: Expected "(" or keyword SELECT or keyword WITH but got keyword CREATE at

Question

I am trying to make an sql file in dbt in order to update models with a new column

{{
    config(materialized='table'
    , retain_previous_version_flg=false
    , migrate_data_over_flg=true
)
}}

CREATE OR REPLACE TABLE {{ ref ('my_table') }} (
SELECT *, new_columns_ts TIMESTAMP
);

Is there a way to use CREATE directly rather than having to use SELECT or WITH clause?

Syntax error: Expected "(" or keyword SELECT or keyword WITH but got keyword CREATE at [16:1]

Answer 1

In this particular case, you don’t need to use the statement CREATE OR REPLACE TABLE, to create a materialized table. You only need to write the SELECT statement.

There are no create or replace statements written in model statements. This means that dbt does not offer methods for issuing CREATE TABLE statements which can be used for source tables.

You can see this example.

{{
    config(materialized='table'
    , retain_previous_version_flg=false
    , migrate_data_over_flg=true
)
}}


SELECT *, new_columns_ts TIMESTAMP from ‘dataset.table’

You can see this option using SQL.

CREATE OR REPLACE TABLE dataset.table (
SELECT *, new_columns_ts TIMESTAMP from ‘dataset.table’
);