In many use cases, maintaining high availability for database objects is crucial. This is particularly important when models created in one project are referenced as sources in other projects. Or if there are other applications trying to access database objects while they are getting rebuilt in a pipeline.

However, when a pipeline runs, there is often a gap between table recreation and applying the grants. During this interim period, any applications attempting to access the tables might encounter an error such as:

Object <DB.SCHEMA.TABLE> does not exist or not authorized.

To overcome this issue, you can set the copy_grants config on your MATE project. This setting applies the "copy grants" DDL qualifier whenever tables and views are rebuilt.

Copy grants copies the permissions to the new table as the table is created.  This will happen before any hooks are executed, and so there will be a small time period when permissions have been applied, but any restrictions applied using hooks (such as masking policies) have not been applied.  There is also a possibility that a failure in the project between table creation and project hooks being executed would cause the policies not to be applied.  This can be avoided by only using copy grants on specific models that do not have other restrictions applied.

An example project dbt_project.yml file:

name: my_project
models:
  +copy_grants: true
  my_project:
    ...

This feature ensures that permissions are seamlessly transferred from the old to the new table, eliminating temporary downtime and preserving access continuity.

It’s important to note, however, that copy_grants does not revoke grants from the configuration.