Adding Labels to Scheduled Queries and Dataform

Google Cloud Data Analytics
1

Hi I’m looking to attribute cost to scheduled queries and Dataform using labels. For the former, this means every job run that runs from a scheduled query (whether it’s an external query from Cloud SQL to GBQ, or GBQ to GBQ as a transformation) is tied to a custom label.

I consulted the help document and used Cloud Assist but it wasn’t clear as there were conflicting information.

What I have tried

  • Check but failed to find Label option in the Scheduled Query / Dataform UI.
  • bq update command in Cloud Shell
  • SET Label within the SQL

All of them didn’t work as I tried to find my custom label but nothing came out.

My question is, is custom label of the job runs from scheduled query and Dataform for cost attribution even supported? If yes, can you advise how to do so?

Thank you.

PS: I found this but it was 3 years ago - thought things might have changed since - Is it possible to add labels to big query's schedule queries for cost tracking?

6

Hi fredngjq,

It looks like you are encountering an issue with applying custom labels to BigQuery jobs generated by Scheduled Queries and Dataform, which is making it difficult to track costs accurately.

Here are the potential ways that might help with your use case:

  • Scheduling Queries: You may want to apply labels to scheduled queries by using the bq mk --transfer command with the --labels flag during creation. If you’re using orchestration tools like Composer, be sure to integrate labels into the BigQuery operator.
  • Dataform: You may want to define project-wide labels using defaultLabels in your dataform.json file, and apply more targeted labels by configuring the labels property within individual SQLX files for specific tables or workflows.
  • External Sources: Make sure your custom applications and Data Transfer Service setups are configured to attach labels when submitting BigQuery jobs.
  • Verification: Be sure to check your labels first in the BigQuery Job History, and then confirm they appear correctly in your Billing reports.

To answer your question:

  1. Is the custom label of the job runs from scheduled query and Dataform for cost attribution even supported? If yes, can you advise how to do so?
  • Yes, custom labels are supported for cost attribution in BigQuery Scheduled Queries and Dataform, but must be set via API or CLI (or by Dataform’s configuration which leverages these). They appear in job metadata, and for billing analysis, you query your Cloud Billing export in BigQuery, which includes these labels, allowing you to filter and group costs accordingly.

For more information, you can check the following documentation below:

1 Like
7

Thanks Marvin for the quick response. Sounds like for scheduling queries, I must only apply by using the command line at the time of creating the query. We cannot go through the UI. Nor can we use the command line to update the query to include the label.

That means for existing scheduled queries, I need to recreate them via CLI to be able to add label to them. Correct?

8

Hi @MarvinLlamas , I tried using this to create a new scheduled query but the labels are not added to the jobs. Is this because my setup is wrong?

bq mk --transfer_config \
  --project_id='my-project-id' \
  --location='my-region' \
  --data_source='scheduled_query' \
  --display_name='DEV_Test' \
  --schedule='every day 06:00' \
  --label='application:test-purpose' \
  --label='environment:dev' \
  --params="{
    \"query\": \"CREATE OR REPLACE VIEW \`my-project-id.my_dataset.vw_SampleView\` AS
      SELECT
        customer_code,
      FROM \`my-project-id.my_dataset.sample_source_table\`
      WHERE DATE(last_updated) >= DATE_SUB(CURRENT_DATE(), INTERVAL 30 DAY);\" 
  }"

Because I saw this - Is it possible to add labels to big query's schedule queries for cost tracking? . Not sure if it’s available as a feature or not since this was 2022.