Open
Description
Compilation of models using get_relations_by_pattern macro fail if the macro returns BigQuery clone type tables.
Runtime Error
Field "type" of type Optional[RelationType] in BigQueryRelation has invalid value 'clone'
Steps to reproduce
my_model.sql
SELECT
1 AS amy_model.yml
version: 2
models:
- name: my_modelmy_clone_model.sql
{%- set table_relations = dbt_utils.get_relations_by_pattern("myDataset", "%_clone") -%}
{%- for table in table_relations -%}
{% if not loop.first %}
UNION ALL
{% endif %}
SELECT * FROM {{ table }}
{%- endfor -%}my_clone_model.yml
version: 2
models:
- name: my_clone_modelExecute the following:
dbt build -s my_modelbq cp --clone -n project1:myDataset.my_model project1:myDataset.my_model_clonedbt build -s my_clone_modelExpected results
A model (view or table) should be created based on the clone tables retrieved via dbt_utils.get_relations_by_pattern
Actual results
Compilation Error:
Field "type" of type Optional[RelationType] in BigQueryRelation has invalid value 'clone'
Screenshots and log output
❯ dbt build -s my_clone_model
01:50:33 Running with dbt=1.8.6
01:50:34 Registered adapter: bigquery=1.8.2
01:50:34 Found 164 models, 7 seeds, 692 data tests, 29 snapshots, 20 sources, 1130 macros, 37 unit tests
01:50:34
01:50:37 Concurrency: 16 threads (target='dev')
01:50:37
01:50:37 1 of 1 START sql view model my_dataset.my_clone_model ...... [RUN]
01:50:39 Unhandled error while executing
Field "type" of type Optional[RelationType] in BigQueryRelation has invalid value 'clone'
01:50:39 1 of 1 ERROR creating sql view model my_dataset.my_clone_model [ERROR in 2.28s]
01:50:39
01:50:39 Finished running 1 view model in 0 hours 0 minutes and 4.61 seconds (4.61s).
01:50:39
01:50:39 Completed with 1 error and 0 warnings:
01:50:39
01:50:39 Field "type" of type Optional[RelationType] in BigQueryRelation has invalid value 'clone'
01:50:39
01:50:39 Done. PASS=0 WARN=0 ERROR=1 SKIP=0 TOTAL=1
System information
The contents of your packages.yml file:
packages:
- package: Datavault-UK/automate_dv
version: 0.10.1
# https://hub.getdbt.com/Datavault-UK/automate_dv/latest/
- package: calogica/dbt_expectations
version: 0.10.3
# https://hub.getdbt.com/calogica/dbt_expectations/latest/
- package: dbt-labs/dbt_utils
version: 1.1.1Which database are you using dbt with?
- postgres
- redshift
- bigquery
- snowflake
- other (specify: ____________)
The output of dbt --version:
❯ dbt --version
Core:
- installed: 1.8.6
- latest: 1.8.7 - Update available!
Your version of dbt-core is out of date!
You can find instructions for upgrading here:
https://docs.getdbt.com/docs/installation
Plugins:
- bigquery: 1.8.2 - Up to date!
Additional context
We should treat clone type tables as regular tables in BigQuery. From what I can tell, we can add an additional jinja block here to allow clone tables.
...
{% macro bigquery__get_table_types_sql() %}
case table_type
when 'BASE TABLE' then 'table'
when 'EXTERNAL TABLE' then 'external'
when 'MATERIALIZED VIEW' then 'materializedview'
when 'CLONE' then 'table'
else lower(table_type)
end as {{ adapter.quote('table_type') }}
{% endmacro %}
...Are you interested in contributing the fix?
Yes, see my PR
Activity