Don’t Lose Your Billing History: Preserve Historical Data during a Billing ID Change

There may be instances where you need to change the billing ID associated with your projects. However, there is a risk of losing historical billing data when changing the ID. This guide will assist you in preserving your data while changing the billing ID.

Method 1: Use Union ALL Option

To view all of your historical data in one place, you may consider creating a consolidated view in BigQuery that aggregates the data across different datasets. The UNION ALL operator is a powerful tool that can be used to combine data from multiple tables or datasets into a single view.

Steps to use this method:

1. Identify the tables that have your historical billing data.
2. Use the following syntax to create a UNION ALL query:

SELECT * FROM <PROJECTNAME.DATASETNAME.TABLE1NAME>
UNION ALL
SELECT * FROM <PROJECTNAME.DATASETNAME.TABLE2NAME>

This query will combine the data from table1 and table2 into a single view. The asterisk (*) in the SELECT clause tells BigQuery to select all columns from each table.

3. Click on SAVE RESULTS to export the merged data in a new table.

Example:

The UNION ALL operator returns all rows from both tables, including duplicate rows.

Method 2: Merge historical data in the current system generated table

Traditionally, we would use UPDATE and INSERT commands to merge data from one table to another. However, billing tables in BigQuery are time-partitioned by default, which means that they are divided into smaller tables based on time. This makes it more difficult to use UPDATE and INSERT commands, as we would need to update or insert data into each individual time-partitioned table.

Therefore to merge these types of tables we use different methods. One way to merge these tables is to copy the previous billing records into the current table.

Requirements and steps to copy time-partitioned tables:

Roles required:

To get the permissions to copy tables and partitions you should have Data Editor (roles/bigquery.dataEditor) IAM role on the source and destination datasets.

Steps to copy the previous data into the current table:

1. In the Google Cloud console, activate Cloud Shell.
2. Issue the bq cp command. Optional flags can be used to control the write disposition of the destination table:

• -a or — append_table appends the data from the source table to an existing table in the destination dataset.
• -f or — force overwrites an existing table in the destination dataset and doesn’t prompt you for confirmation.
• The -n or — no_clobber flag returns an error message if the table already exists in the destination dataset. If you do not specify this flag, you will be prompted to choose whether to replace the destination table.
• — destination_kms_key is the customer-managed Cloud KMS key used to encrypt the destination table.

bq — location=location cp
-a -f -n
project_id:dataset.source_table
project_id:dataset.destination_table

Replace the following in the given command:

• location: the name of your location. The — location flag is optional.
• project_id: your project ID.
• dataset: the name of the source or destination dataset.
• source_table: the table you’re copying.
• destination_table: the name of the table in the destination dataset.

This command will copy the table named source_table to the table named destination_table.

Example:

To view more on this click here.

Use this method for a seamless transition while switching the billing accounts.

Conclusion:

By following these methods, you can successfully preserve your billing history while changing the billing ID associated with your projects. It is essential to safeguard this data for financial analysis, audits, and compliance purposes. Implementing these techniques will help you maintain a seamless transition and ensure that your historical billing data remains accessible and intact throughout the process.