Bigquery: dbt seed with ARRAY fields

Question

I'd like to load some small configuration table I have on BigQuery using seeds. I did that successfully till now, but now I have a table with an array field. I put the arrays in the usual BigQuery format ["blablabla"], but no luck. I tried forcing the datatype in dbt_prject.yml, but I get a "ARRAY is not a valid value" error. Did someone ever used seeding with structured fields? Daniele

Answer 1

I don't think this is possible, unfortunately. From a little online research, this appears to be a joint limitation of:

• the BigQuery LoadJobConfig API that dbt calls here
• the CSV file format, which doesn't really have a way to specify a nested schema (related issue)

A long-term resolution to this may be support for JSON-formatted seeds (dbt#2365).

In the meantime, I recommend that you set the seed column type to string and convert it to an array (using json_extract_array) in a staging model.