/* # Copyright 2026 Google LLC # # Licensed under the Apache License, Version 2.0 (the "License"); # you may not use this file except in compliance with the License. # You may obtain a copy of the License at # # http://www.apache.org/licenses/LICENSE-2.0 # # Unless required by applicable law or agreed to in writing, software # distributed under the License is distributed on an "AS IS" BASIS, # WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. # See the License for the specific language governing permissions and # limitations under the License. */ -- This script builds the helper table for labels config { //disabled: true, type: "incremental", tags: ["updateTagsLabels"], name: dataform.projectConfig.vars.labels_table_name, bigquery: { partitionBy: "usage_start_date", partitionExpirationDays: -1 }, description: "Helper table to unnest labels, partitioned by usage date." } pre_operations { -- BEGIN --pre_operations DECLARE v_dates_string STRING; DECLARE v_where_clause STRING; DECLARE v_process_row_id INT64 DEFAULT NULL; DECLARE v_is_incremental BOOLEAN; DECLARE v_target_table_fqn STRING DEFAULT '${self()}'; DECLARE v_records_added INT64; DECLARE v_usage_dates_processed ARRAY; DECLARE v_max_dest_date DATE; -- Get the active process ID for logging. SET v_process_row_id = ( SELECT process_id FROM ${ref(dataform.projectConfig.vars.log_table_name)} WHERE process_end_time IS NULL ); -- Determine the dates that need to be re-processed in the aggregates via usage_dates_processed column SET v_usage_dates_processed = ( SELECT usage_dates_processed FROM ${ref(dataform.projectConfig.vars.log_table_name)} WHERE process_id = v_process_row_id ); --If the array is empty, run a full refresh. TODO: set load type in step 01? or is this deterministic enough? SET v_is_incremental = ${when(incremental(),`TRUE`,`FALSE`)} ; -- Get the pre-formatted string of dates to process ${when(incremental(), `SET v_dates_string = (SELECT (SELECT FORMAT("'%s'", STRING_AGG(CAST(d AS STRING), "','")) FROM UNNEST(usage_dates_processed) AS d ) AS usage_dates_string FROM ${ref(dataform.projectConfig.vars.log_table_name)} WHERE process_end_time IS NULL )`, `SET v_dates_string = NULL`)} ; -- if incremental then delete all dates to reprocess... ${when(incremental(),` IF v_dates_string IS NOT NULL THEN EXECUTE IMMEDIATE FORMAT( "DELETE FROM %s WHERE usage_start_date IN (%s)", v_target_table_fqn, v_dates_string ); END IF;` ,``)} } -- Main query to unnest labels WITH gcp_export_labels as (SELECT pk, usage_start_date, (SELECT SPLIT(trim(label_array),':::')[1] from UNNEST(SPLIT(billing_export.all_labels,',')) as label_array where SPLIT(trim(label_array),':::')[0]='application-name') AS label_application_name, (SELECT SPLIT(trim(label_array),':::')[1] from UNNEST(SPLIT(billing_export.all_labels,',')) as label_array where SPLIT(trim(label_array),':::')[0]='carts-ticket') AS label_carts_ticket, (SELECT SPLIT(trim(label_array),':::')[1] from UNNEST(SPLIT(billing_export.all_labels,',')) as label_array where SPLIT(trim(label_array),':::')[0]='creation-date') AS label_creation_date, (SELECT SPLIT(trim(label_array),':::')[1] from UNNEST(SPLIT(billing_export.all_labels,',')) as label_array where SPLIT(trim(label_array),':::')[0]='creator-name') AS label_creator_name, (SELECT SPLIT(trim(label_array),':::')[1] from UNNEST(SPLIT(billing_export.all_labels,',')) as label_array where SPLIT(trim(label_array),':::')[0]='environment-type') AS label_environment_type, (SELECT SPLIT(trim(label_array),':::')[1] from UNNEST(SPLIT(billing_export.all_labels,',')) as label_array where SPLIT(trim(label_array),':::')[0]='team-name') AS label_team_name, (SELECT SPLIT(trim(label_array),':::')[1] from UNNEST(SPLIT(billing_export.all_labels,',')) as label_array where SPLIT(trim(label_array),':::')[0]='support-group-email') AS label_support_group_email, (SELECT SPLIT(trim(label_array),':::')[1] from UNNEST(SPLIT(billing_export.all_labels,',')) as label_array where SPLIT(trim(label_array),':::')[0] ='data-roles') AS label_data_roles, (SELECT SPLIT(trim(label_array),':::')[1] from UNNEST(SPLIT(billing_export.all_labels,',')) as label_array where SPLIT(trim(label_array),':::')[0]='cmdb-application-service') AS label_cmdb_application_service, (SELECT SPLIT(trim(label_array),':::')[1] from UNNEST(SPLIT(billing_export.all_labels,',')) as label_array where SPLIT(trim(label_array),':::')[0]='cmdb-support-group') AS label_cmdb_support_group, (SELECT SPLIT(trim(label_array),':::')[1] from UNNEST(SPLIT(billing_export.all_labels,',')) as label_array where SPLIT(trim(label_array),':::')[0]='cost-center-gl-account') AS label_cost_center_gl_account, (SELECT SPLIT(trim(label_array),':::')[1] from UNNEST(SPLIT(billing_export.all_labels,',')) as label_array where SPLIT(trim(label_array),':::')[0]='data-classification') AS label_data_classification, (SELECT SPLIT(trim(label_array),':::')[1] from UNNEST(SPLIT(billing_export.all_labels,',')) as label_array where SPLIT(trim(label_array),':::')[0]='data-regulation') AS label_data_regulation, (SELECT SPLIT(trim(label_array),':::')[1] from UNNEST(SPLIT(billing_export.all_labels,',')) as label_array where SPLIT(trim(label_array),':::')[0]='organization-name') AS label_organization_name, --- UDP Labels --- (SELECT SPLIT(trim(label_array),':::')[1] from UNNEST(SPLIT(billing_export.all_labels,',')) as label_array where SPLIT(trim(label_array),':::')[0]='data-domain') AS label_data_domain, (SELECT SPLIT(trim(label_array),':::')[1] from UNNEST(SPLIT(billing_export.all_labels,',')) as label_array where SPLIT(trim(label_array),':::')[0]='data-platform') AS label_data_platform, (SELECT SPLIT(trim(label_array),':::')[1] from UNNEST(SPLIT(billing_export.all_labels,',')) as label_array where SPLIT(trim(label_array),':::')[0]='application-team') AS label_application_team, (SELECT SPLIT(trim(label_array),':::')[1] from UNNEST(SPLIT(billing_export.all_labels,',')) as label_array where SPLIT(trim(label_array),':::')[0]='reporting-tool') AS label_reporting_tool, (SELECT SPLIT(trim(label_array),':::')[1] from UNNEST(SPLIT(billing_export.all_labels,',')) as label_array where SPLIT(trim(label_array),':::')[0]='digital-data-product') AS label_digital_data_product, (SELECT SPLIT(trim(label_array),':::')[1] from UNNEST(SPLIT(billing_export.all_labels,',')) as label_array where SPLIT(trim(label_array),':::')[0]='bq-pricing-model') AS label_bq_pricing_model, (SELECT SPLIT(trim(label_array),':::')[1] from UNNEST(SPLIT(billing_export.all_labels,',')) as label_array where SPLIT(trim(label_array),':::')[0]='job-type') AS label_job_type, (SELECT SPLIT(trim(label_array),':::')[1] from UNNEST(SPLIT(billing_export.all_labels,',')) as label_array where SPLIT(trim(label_array),':::')[0]='data-certification') AS label_data_certification, --- K8 Labels --- (SELECT SPLIT(trim(label_array),':::')[1] from UNNEST(SPLIT(billing_export.all_labels,',')) as label_array where SPLIT(trim(label_array),':::')[0]='goog-k8s-cluster-name') AS label_goog_k8s_cluster_name, (SELECT SPLIT(trim(label_array),':::')[1] from UNNEST(SPLIT(billing_export.all_labels,',')) as label_array where SPLIT(trim(label_array),':::')[0]='goog-k8s-cluster-location') AS label_goog_k8s_cluster_location, (SELECT SPLIT(trim(label_array),':::')[1] from UNNEST(SPLIT(billing_export.all_labels,',')) as label_array where SPLIT(trim(label_array),':::')[0]='goog-k8s-namespace') AS label_goog_k8s_namespace, (SELECT SPLIT(trim(label_array),':::')[1] from UNNEST(SPLIT(billing_export.all_labels,',')) as label_array where SPLIT(trim(label_array),':::')[0]='goog-k8s-node-pool-name') AS label_goog_k8s_node_pool_name, (SELECT SPLIT(trim(label_array),':::')[1] from UNNEST(SPLIT(billing_export.all_labels,',')) as label_array where SPLIT(trim(label_array),':::')[0]='k8s-namespace') AS label_k8s_namespace, (SELECT SPLIT(trim(label_array),':::')[1] from UNNEST(SPLIT(billing_export.all_labels,',')) as label_array where SPLIT(trim(label_array),':::')[0]='k8s-workload-name') AS label_k8s_workload_name, (SELECT SPLIT(trim(label_array),':::')[1] from UNNEST(SPLIT(billing_export.all_labels,',')) as label_array where SPLIT(trim(label_array),':::')[0]='k8s-workload-type') AS label_k8s_workload_type, (SELECT SPLIT(trim(label_array),':::')[1] from UNNEST(SPLIT(billing_export.all_labels,',')) as label_array where SPLIT(trim(label_array),':::')[0]='k8s-label/app') AS label_k8s_label_app, (SELECT SPLIT(trim(label_array),':::')[1] from UNNEST(SPLIT(billing_export.all_labels,',')) as label_array where SPLIT(trim(label_array),':::')[0]='k8s-namespace-labels/app.kubernetes.io/managed-by') AS label_k8s_namespace_managed_by, (SELECT SPLIT(trim(label_array),':::')[1] from UNNEST(SPLIT(billing_export.all_labels,',')) as label_array where SPLIT(trim(label_array),':::')[0]='k8s-namespace-labels/departmentCode') AS label_k8s_namespace_department_code, (SELECT SPLIT(trim(label_array),':::')[1] from UNNEST(SPLIT(billing_export.all_labels,',')) as label_array where SPLIT(trim(label_array),':::')[0]='k8s-namespace-labels/ecp.costco.com/tenant') AS label_k8s_namespace_ecp_costco_tenant, (SELECT SPLIT(trim(label_array),':::')[1] from UNNEST(SPLIT(billing_export.all_labels,',')) as label_array where SPLIT(trim(label_array),':::')[0]='k8s-namespace-labels/istio-injection') AS label_k8s_namespace_istio_injection, (SELECT SPLIT(trim(label_array),':::')[1] from UNNEST(SPLIT(billing_export.all_labels,',')) as label_array where SPLIT(trim(label_array),':::')[0]='k8s-namespace-labels/kubernetes.io/metadata.name') AS label_k8s_namespace_metadata_name, (SELECT SPLIT(trim(label_array),':::')[1] from UNNEST(SPLIT(billing_export.all_labels,',')) as label_array where SPLIT(trim(label_array),':::')[0]='k8s-namespace-labels/tenant') AS label_k8s_namespace_tenant FROM ${ref(dataform.projectConfig.vars.daily_summary_table_name)} AS billing_export WHERE ${when(incremental(),`usage_start_date IN UNNEST(v_usage_dates_processed)`, `usage_start_date >= '${dataform.projectConfig.vars.default_start_date}'`)} ) SELECT * FROM gcp_export_labels