Home Blog Service Analytics

Prevent Data Loss: Set Up BigQuery Error Notifications

Author Image
Rustam is an analytics expert at VIDEN with 2+ years of experience in data analytics, specializing in BigQuery, Google Analytics 4, Google Tag Manager and Data Visualization
Prevent Data Loss: Set Up BigQuery Error Notifications

BigQuery has gained significant popularity and is increasingly utilized in numerous projects. Google’s cloud-based data warehouse solution is known for its scalability, speed, and ability to handle large datasets efficiently. As more organizations integrate BigQuery into their data processing and analytics workflows, robust monitoring and alerting mechanisms are essential.

Implementing alerts for jobs that encounter errors is crucial to maintaining a reliable data pipeline. Errors in BigQuery can arise from various sources, including scheduled queries that fail to execute correctly, issues during data load operations, and daily exports from Google Analytics 4 (GA4), among others. If left unchecked, these errors can disrupt data processing activities and lead to inaccuracies in data analysis.

By setting up effective alerting systems, teams can promptly identify and address issues, ensuring the smooth functioning of their data pipelines. Since BigQuery does not provide native error alerting, one possible solution is to use Log-based alerting. Keep reading to find a script that helps set up Big Query error notifications.

Table of Contents:

  1. Exploring the Logs
  2. Alert Creation
  3. Semi-automated Alarm Installation 

Exploring the Logs

Start from exploring the logs. To explore them for the project navigate to https://console.cloud.google.com/logs/query. Here, you can find all logged interactions across services in the project*. Depending on your needs, you can write a query to explore log records. For example, the query below will select all records for BigQuery service with a severity of more or equal NOTICE*.

resource.type=”bigquery_resource” — used to include only BigQuery related logs severity>=NOTICE — used to select severity

Side notes:
1. AND between clauses may be omitted.
2. Quotes also may be omitted.
3. *EMERGENCY > ALERT > CRITICAL > ERROR > WARNING > NOTICE > INFO > DEBUG > DEFAULT

However, dry-runs* errors are also presented in the list, and to avoid viewing these records, we should add some extra conditions to exclude them. Otherwise, we will receive a notification for these errors as well. *Dry runs don’t use query slots, and you are not charged for performing a dry run.

Also, it’s better to exclude jobs that ran manually from the BigQuery console, as we know that errors and notifications will be redundant. Let’s add some clauses to do that:

– – include only Errors for BigQuery service
severity=ERROR
AND resource.type=”bigquery_resource”

– – New clauses:
– – exclude jobs which contains “bqux” as these jobs are run from the interface
AND NOT protoPayload.serviceData.jobCompletedEvent.job.jobName.jobId:”bqux”
– – include only completed jobs
AND protoPayload.methodName=”jobservice.jobcompleted”

After running that query you may see the records that met our conditions. If we are good with the output we receive, we may create notifications based on these error records. So, let’s create an alert based on them.

Alert Creation

Then let’s see how you can create an alarm in Big Query.

  • Enter your query and then click the “Create alert” button.

  • Enter your Policy Name and Severity Level. Optionally, you can write a piece of Documentation about your policy.

  • Ensure that the query you have written is correct. To do that, click the “Preview logs” button and review the logs.

In that section, you can also create log-based labels, allowing you to extract parts of logs and then provide them as separate fields in the alert.

  • Configure how often you will receive alerts between incidents and when the incident will be auto-closed.

  • The final and most important step in setting up our notifications is to choose the channels to be notified about our incidents.

If you have not configured notification channels, click Manage Notification Channels and configure the required channels there, such as Google Chat, Email, or Slack.

After configuring and choosing channels, click save to save your policy. When an error occurs, you will be notified about the incident.

Semi-automated Alarm Installation

To simplify the above steps, our expert, Rustam Faskhutdinov, created a script that allows setting the alerting up within a few minutes. Just follow these 6 steps.

First of all, to use it, you should activate Cloud Shell:

To execute a script that will configure the notification policy run the following command in a shell:

pip3 install -r <(curl -s https://raw.githubusercontent.com/RustamFaskhutdinov/bigquery_error_alerting/main/requirements.txt) && python3 <(curl -s https://raw.githubusercontent.com/RustamFaskhutdinov/bigquery_error_alerting/main/create_bq_alert_policy.py)

Step 1

Select the mode currently only initialize is available. So enter 0. 

Step 2

Then, we need to select a project from the list by entering the project number or the full ID of the project.

Step 3

After choosing a project, the script will check if the policy with the name BigQuery Error Alert has already been created. If not, you need to select what notification channels you would like to use. Currently, two types of channels are available for setting up from the script: email and Google Space and also you may choose both (recommended).

Step 4

We choose to configure both Email and Google Space.

First, we need to type email addresses that will be used as Notification channels. Type the required email address (one per line) and an empty string to finish your input. Invalid email addresses will be ignored.

Then, we need to enter the Space name (only for the Notification channel display name) and Space ID* (how to create a space and find the ID will be shown below).

After that, you will see the notification channel brief. What channels already exist, and what will be created? To continue the creation process, please click Enter.

If Space does not exist, you will see the following error. Click enter to continue (only email channels will be used).

Step 5

Then, you should include or exclude jobs created by Looker Studio.

Step 6

The last step is Notification channel creation.

If the channel was created successfully, you will see the appropriate message and the link to the policy details.

 

If you need help optimizing this powerful tool, do not hesitate to contact VIDEN experts and receive support tailored to your business needs.

Get in touch

Got a question? We'd love to hear from you. Send us a message and we'll respond as soon as possible.



    By clicking submit, you agree to our Privacy Policy

    Latest Insights

    Get the tips from our experts to optimize and scale your campaigns

    3 October 2024

    Google Analytics 4 for NonProfit Advertising: A Comprehensive Guide

    Nonprofit organizations differ from businesses and have their unique mission and goals. However, like any organization, nonprofits need to market themselves effectively to reach their target audience and achieve their objectives. Effective advertising plays a big role (investments in digital advertising by nonprofits increased by 28% in 2023), but it’s equally important to measure and […]

    Learn more

    22 August 2024

    Guide to Attribution Models in Google Analytics 4 (GA4)

    Customers’ path to the conversion is complicated and nonlinear at times: users come and go and return to the site again using various sources such as organic searches, referral links, or an ad targeted to a user in one of the social networks. Here come attribution models in GA4 to determine which source made the […]

    Learn more

    20 June 2024

    The Importance of Using Analytics in Advertising

    Nowadays, businesses face the challenge of constantly refining their advertising campaigns to optimize performance. Given the complexity of the modern consumer’s journey, with its numerous touchpoints and channels, leveraging analytics in advertising plays a crucial role for businesses in achieving their desired outcomes. In 2022, Gartner reported that 53% of marketing decisions were driven by […]

    Learn more

    30 May 2024

    How To Create A Meaningful Ecommerce Analytics Dashboard: A Step-by-step Guide

    Are you drowning in a huge amount of e-commerce data? There’s no other way to escape it if you want your e-commerce store to succeed. However, you can make analyzing your data much more enjoyable and effective. The key is to present e-commerce data analytics visually. Data visualization plays a big role in data-driven decision-making. […]

    Learn more

    28 March 2024

    Navigating the Data Deluge: Prioritizing Key Metrics in the Age of Google Analytics 4

    With the advent of Google Analytics 4 (GA4), the key business challenge becomes not collecting data but discerning what truly matters in the sea of data. 64% of marketing executives believe data-driven marketing is crucial to success in a hyper-competitive global economy. By prioritizing key metrics and avoiding the trap of tracking everything, businesses can […]

    Learn more

    3 January 2024

    How to Create a Viable First-Party Data Strategy in 2024?

    Discover the 9 key steps to build a first-party data strategy that maximizes customer data and enhances your marketing efforts.

    Learn more

    30 November 2023

    Maximize Your Data Retention in Google Analytics 4 (GA4) 

    Learn how to manage data storage and protect privacy with GA4 data retention settings.

    Learn more

    24 November 2023

    How to Read and Interpret Google Analytics 4 Reports to Drive Business Success

    Get to know how to read Google Analytics 4 reports to uncover customer insights, optimize marketing strategies, and drive business growth.

    Learn more