search

dbt-labs / dbt-external-tables

dbt macros to stage external sources
https://hub.getdbt.com/dbt-labs/dbt_external_tables/latest/
Apache License 2.0
314 stars 123 forks source link

Make schema creation optional (at least on BigQuery) #304

Closed nicolas-gaillard closed 5 months ago

nicolas-gaillard commented 5 months ago

Describe the feature

I think the objective of this package is mainly to create external tables and it should probably not handle the schema creation (or make it optional at least). Indeed, it is common practice to manage databases and schema using an infrastructure-as-code tool such as Terraform.

On BigQuery, it's really painful right now because it queries the INFORMATION_SCHEMA.SCHEMATA table as a first step which requires permissions on the whole GCP project and I think it goes against the principle of least privilege.

Describe alternatives you've considered

Maybe we can add a parameter which is True by default not to change the current behavior. This parameter would control the schema creation.

Additional context

I don't think this is a database-specific feature and to be honest, I don't know if this is the case on databases other than BigQuery. Is it the kind of feature we can develop for only one provider?

Who will this benefit?

It will be useful to improve security and to be more in line with the README of the package.

jeremyyeo commented 5 months ago

This is an interesting one - and if you want some context on the introduction of this package ultimately being able to create schemas - you can see this issue: https://github.com/dbt-labs/dbt-external-tables/issues/100 where there was quite a lot of support for being able to do so - i.e. being able to create schemas on the fly.

I think in hindsight, one would have perhaps added this feature and make it toggle-able create_schemas_if_not_exist: True.

At the same time, there this other related issue: https://github.com/dbt-labs/dbt-external-tables/issues/222 and I think this comment in particular: https://github.com/dbt-labs/dbt-external-tables/issues/222#issuecomment-1683032778 - worth keeping the discussion in that issue imho.