[Bug] Using grants on materialized_view raises an error

[AcidFlow]

Is this a new bug in dbt-bigquery?

• I believe this is a new bug in dbt-bigquery
• I have searched the existing issues, and I could not find an existing issue for this bug

Current Behavior

When using the grants configuration on a materialized view, the generated grant/revoke statement results in:

grant <PRIVILEGE> on materialized_view <MODEL_NAME> to "<GRANTEE_1>","<GRANTEE_2>"

and respectively:

revoke <PRIVILEGE> on materialized_view <MODEL_NAME> from "<GRANTEE_1>","<GRANTEE_2>"

This results in an error because BigQuery expect the relation type to be materialized view instead of materialized_view.

See documentation

Expected Behavior

The grant/revoke statements execute successfully.

Steps To Reproduce

1. Create a models like the following:

base_table.sql:

{{
    config(
        materialized='table'
    )
}}
SELECT 1 AS test

grants_materialized_view.sql:

{{
    config(
        materialized='materialized_view',
        grants={
            "roles/bigquery.dataViewer": ["user:[email protected]"]
        }
    )
}}
SELECT * FROM {{ ref('base_table') }}

2. Create the models using dbt run

3. Models should be created and the described privileges granted but the following error occurs:

Database Error in model grants_materialized_view (models/grants_materialized_view.sql)
  Invalid object type for GRANT statement: materialized_view at [6:5]
  compiled Code at <REDACTED>/grants_materialized_view.sql

Relevant log output

Database Error in model grants_materialized_view (models/grants_materialized_view.sql)
  Invalid object type for GRANT statement: materialized_view at [6:5]
  compiled Code at <REDACTED>/grants_materialized_view.sql

Environment

- OS: MacOS
- Python: 3.12
- dbt-core: 1.8.1
- dbt-bigquery: 1.8.1

Additional Context

Proposed fix: dbt-labs/dbt-bigquery#1267

[mikealfare]

We should align on how to handle this in general. There are two approaches that are being considered:

• create a render() method for relation_type that produces the correct string
  • most of the time this is the same (table/view), sometimes it's not (materialized_view > materialized view)
  • sometimes this is macro/platform specific (e.g. postgres views: drop table my_view)
• create a macro per relation type, similar to how materialized views were implemented
  • this is very explicit with little logic in the template
  • there is a lot of copy past involved, though hopefully it does not change very often
  • for many statement types and platforms, this aligns 1:1 with platform docs, making the template act as an implementation of the platform docs (read "protocol")

We have not decided on an approach, and there's the chance someone thinks of a third approach. This is here merely for documentation of a scoping discussion.