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

user_8277605 0
Viewed 785

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]
dbt google-bigquery
1 Answers

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’
);
user_169291450
Related