Generate table with type parameters in BigQuery

[jonrb]

The problem I’m having

Hello. I’m new using dbt and Bigquery as datawarehouse.
I’d like to keep the schema including types parameteres between models …

What I’ve already tried

i have a source table , called T, with a field of type STRING(30)
The output of this model in Bigquery is another table with a field of type STRING
and not STRING(30)

it is possible to generate in dbt with BigQuery a STRING(30) field as the result of a model?

Bigquery does not support casting with parameter types…
SELECT “1” as STRING(30) does not work
so a sql "create or replace table as select … " does not work.

I have already tested in Bigquery using contracts and defining every columns.
Maybe a new feature of dbt for Bigquery is to use the contract info to validate and create the target table with the contract info.

If i test this in Snowflake and it works for me.

Any ideas?
Thank you in advance.

[a_slack_user]

What does your model’s yaml definition look like? Does data_type: string(30) not work under the column definition?

_{Note: @Mike Stanley originally posted this reply in Slack. It might not have transferred perfectly.}

[jonrb]

Yes, my source model has string(30). The target model is created with only STRING

[a_slack_user]

so in the target model, what happens if you put data_type: string(30) under the column definition?

_{Note: @Mike Stanley originally posted this reply in Slack. It might not have transferred perfectly.}

[jonrb]

Target model has String(30) data_type. Target table in Bigquery is created with STRING , not with STRING(30)

dbt create the target table in Bigquery with CREATE OR REPLACE TABLE… as SELECT… and it does not keep types parameter

[jonrb]

Solved with contracts.

Forcing the contract in the dbt model, dbt create the target model column ,materialized as table, with the expected data_type and with the type parameter .

models → name: my_model → config → contract: → enforced: true …

columns → name: my_column → data_type: string(30) …

i got my answer looking at dbt-bigquery adapter code , the bigquery__create_table_as …