get_relations_by_pattern macro fails forclone tables in BigQuery

[lachlanclulow]

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 a

my_model.yml

version: 2
models:
  - name: my_model

my_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_model

Execute the following:

dbt build -s my_model

bq cp --clone -n project1:myDataset.my_model project1:myDataset.my_model_clone

dbt build -s my_clone_model

Expected 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.1

Which 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