BigQuery labels on schemas/dataset

[shedd]

Describe the feature

We use labels on Google Cloud assets for inventory tracking purposes. For our Bigquery datasets, we need labels at the schema/dataset level.

It looks like it's possible to add other Bigquery schema-level configuration to the profile - e.g. dataset location.

Are there any plans to support labels at the dataset level?

Describe alternatives you've considered

We tested table-level labels implemented as per dbt-labs/dbt-core#1942 but our inventory tracking service (Vanta for SOC2 purposes) expects the labels at the dataset level.

Who will this benefit?

Other Bigquery users who are working with labels at the dataset level.

[jtcohen6]

Thanks for the idea @shedd! It's helpful to know that this a requirement for integrating with other tools. I'm totally in favor of coming up with an approach here, I'm just not sure what it should be.

There's some helpful discussion over in dbt-labs/dbt-core#1714 around a similar idea, how to persist docs for BigQuery datasets. The same questions apply here:

• how can labels can be attached to a schema/dataset?
• how dbt actually sets the labels for the schemas/datasets -- does this only happen when dbt creates a schema/dataset? Or can these descriptions be updated? (relates to Update BQ labels on tables as they change  #21, we only update labels on models when they're created today)

Dataset location (#969) feels a bit different because it's an attribute of the connection, specified in profiles.yml. Of course, we could add a profile attribute that instructs dbt to label the target.dataset if it's creating it for the first time. What about other datasets dbt may create, though, in the case that a project is using custom schemas? Should that same label apply to them all?

For the purpose of integrating with your inventory tracking service, would it be sufficient to simply put a dbt label each dataset dbt creates? This wouldn't support custom labels, but it'd be easy enough to code up without developing new constructs, and it feels in line with our expectation that dbt creates and manages its own datasets of transformed models.

I'm tagging this bigquery to start, but if we come up with an approach we really like, we could see about extending it to other databases.

[sparrovv]

Hey there,

Eventually this problem was solved with this macro:

{% macro update_dataset_labels(dataset_name, project) %}

   {% set update_labels_query %}
        ALTER schema `{{project}}`.{{dataset_name}}
        SET OPTIONS (
            labels=[("foo-name", "foo-value")]
        )
   {% endset %}

   {% set get_labels %}
        SELECT
            option_value
        FROM
            `{{project}}`.INFORMATION_SCHEMA.SCHEMATA_OPTIONS
        WHERE
          schema_name="{{dataset_name}}"
          and option_name="labels"
  {% endset%}

  {% do run_query(update_labels_query) %}
  {% set result =  run_query(get_labels).columns[0].values() %}
  {% do log(result, True) %}

{% endmacro %}

 dbt run-operation update_dataset_labels --args '{"dataset_name": "dbt_test", "project": "foo"}'

Tha macro could be made more generic, and source labels from the project.yml, but for the time being it works as expected.

[BenHizak]

@sparrovv this is great. Thank you 👍

[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 remove the stale label or comment on the issue, or it will be closed in 7 days.

[BenHizak]

Is there any plan to make this native functionality (without a macro) in DBT?

[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 remove the stale label or comment on the issue, or it will be closed in 7 days.