[FEAT] support users that do not have schema creation permission

[dataders]

Describe the feature

related: #214 #221

below is an interaction summary of:

1. a user's permission to create new schemas, and
2. if the intended external schema exists already

	1	2	3	4
schema already exists?	T	F	T	F
can user create new schemas?	T	T	F	F
dbt-external-tables supported?	T	T	F	F

It the the third scenario that folks would like to have enabled (see #214 #221). Restated:

If a user does not have the "create schema" permission, but the target's schema already exists, this package should not execute a CREATE SCHEMA {SCHEMA} IF EXISTS statement.

Possible solutions to replace current behavior. The package should either:

• verify that the schema exists before creating the table, or
• skip schema verification via
  • a new argument or variable
  • decoupling the external stage/schema creation from the table creation

Describe alternatives you've considered

• break the schema
• Adding a schema check before privilege dependent ddl in Snowflake #215 and BigQuery check if schema already exists before trying to create it #220 are existing examples

Additional context

The canonical user of dbt works inside of a database in which she has permissions to CREATE and DROP schema. However, there exist workarounds for situations when an analytics engineer does not have these permissions. Unfortunately, these workarounds do not apply to this package, as a CREATE SCHEMA {SCHEMA} IF EXISTS is issued each time the stage_external_sources macro is invoked.

I also don't like that there is no useful default__ implementation for the create_external_schema and get_external_build_plan macros. Ideally I'd like to see this solved in one place. Especially if we have a long-term aspiration to move this package's functionality into dbt-core

Who will this benefit?

What kind of use case will this feature be useful for? Please be specific and provide examples, this will help us prioritize properly.

[thomas-vl]

In the most ideal situation the schema that is being used is prefixed with the same prefix as the other personal schema's. All my users can create personalised schemas like: dbt_thomas_raw and the create if exists works as a charm if they would do that. They just don't have permission to do this on the production schema's.

[Dungarova]

I agree that it would be useful to add this functionality to be able opt out of the schema creation step. We don't necessarily want every data scientist who has the ability create tables to be able to create a bunch of datasets as well.

[jackwelty]

FWIW I am on BigQuery and I didn't have this problem on 0.8.0. I updated to 0.8.6 and now suddenly the package is trying to create schemas (which my org's permission model does not allow). We use a number of packages and this is the only time we've hit this issue.

would love to get #220 merged!

[github-actions]

This issue has been marked as Stale because it has been open for 180 days with no activity. If you would like the issue to remain open, please comment on the issue or else it will be closed in 7 days.

[github-actions]

Although we are closing this issue as stale, it's not gone forever. Issues can be reopened if there is renewed community interest. Just add a comment to notify the maintainers.

[jeremyyeo]

Perhaps worth adding to:

Describe alternatives you've considered

• Make on the fly schema creation optional via a config:

vars:
  dbt_external_tables__config:
    on_the_fly_schema_creation: False

[github-actions]

This issue has been marked as Stale because it has been open for 180 days with no activity. If you would like the issue to remain open, please comment on the issue or else it will be closed in 7 days.

[github-actions]

Although we are closing this issue as stale, it's not gone forever. Issues can be reopened if there is renewed community interest. Just add a comment to notify the maintainers.