How to Automate Triggers to Send Slack Messages from BigQuery

Operations teams often need to monitor key metrics closely, such as daily sales performance, Inventory levels, Issue tracking, etc. Manually monitoring these metrics can be time-consuming and error-prone. Teams need to be proactive to stay ahead of the issue, and the most effective way to achieve this is through triggering the alerts to the right individuals. These automated triggers not only alert the team at the right time, it can also provide crucial info through which teams can resolve the issue quickly.

These alert triggers can be set up on any database or data warehouse. For those who uses BigQuery, In this detailed guide, we’ll walk you through how to automate triggers to send Slack messages from BigQuery

1. Method #1: Manually Using Custom Python Script
2. Method #2: Automating trigger through Locale

‍

Method #1: Manually Using Custom Python Script

Let's explore how this can be achieved by fetching sales data from Google BigQuery and sending alerts to a Slack channel using Python when sales drop below a certain threshold.

‍

Authenticate BigQuery

To interact with BigQuery, first set up authentication by creating a service account in Google Cloud Platform (GCP) and downloading the JSON key file. The service account must have read access to BigQuery and the datasets required.

Set the environment variable GOOGLE_APPLICATION_CREDENTIALS to the path of this JSON key file.

export GOOGLE_APPLICATION_CREDENTIALS="/path/to/your/keyfile.json"

‍

Get Slack Webhook URL

Create a new Slack app in your workspace and navigate to the 'Incoming Webhooks' section. Activate incoming webhooks and create a new webhook to the desired Slack channel. Slack will provide a Webhook URL which will be used to send messages to this channel.

‍

Python Script for Data Retrieval and Slack Notification

import time
from google.cloud import bigquery
from slack_sdk import WebClient
from slack_sdk.errors import SlackApiError
import os

# Set up Google Cloud credentials
os.environ['GOOGLE_APPLICATION_CREDENTIALS'] = '/path/to/your/keyfile.json'

# Initialize BigQuery client
client = bigquery.Client()

# Slack webhook URL
slack_webhook_url = 'your_slack_webhook_url'

def fetch_and_send():
    try:
        # SQL query to fetch products with sales below threshold
        query = """
            SELECT product_id, SUM(sales_amount) AS total_sales
            FROM `your_project.your_dataset.sales_data`
            WHERE DATE(sales_date) = CURRENT_DATE()
            GROUP BY product_id
            HAVING total_sales < 1000;
        """

        # Execute query
        query_job = client.query(query)
        results = query_job.result()

        # Format message for Slack
        message = "Sales Alert! Products with low sales today:\n"
        for row in results:
            message += f"Product ID: {row.product_id}, Total Sales: {row.total_sales}\n"

        # Send message to Slack
        slack_client = WebClient(url=slack_webhook_url)
        response = slack_client.chat_postMessage(channel='#your-channel', text=message)

    except Exception as e:
        print(f"An error occurred: {e}")

# Main loop
while True:
    fetch_and_send()
    time.sleep(3600)  # Sleep for 1 hour (3600 seconds)

Replace 'your_slack_bot_token', #your-channel, and the BigQuery table reference with appropriate values. This method uses Python to connect to BigQuery, execute the SQL query, process the results, and then format and send a message to a Slack channel using the Slack SDK.

‍

This method can help you receive slack alerts but it comes with some evident limitations:

• Maintenance The script currently includes basic error handling, which means if something goes wrong, it will let you know by printing an error message to the console. This is helpful for troubleshooting, but in a real-world scenario, you might want more comprehensive error management
• Lack of Monitoring: Without external monitoring, you may not know if the script fails or stops running.
• No Failover or Recovery: If the script encounters an error, it will not recover or retry unless explicitly programmed to do so.

‍

Method #2: Automating triggers through Locale

Locale, no-code platfrom handles the heavy lifting, providing a maintenance-free, reliable, and user-friendly experience. With just a few clicks, you can automate alert triggers to Slack.

‍

Step 1: Login to Locale, Go to Org Setting and Connect your BigQuery database from the data source.

‍

Step 2: Now go to Alerts → Create New Alerts. Choose your database → Click on Setup data to be monitored.

‍

Step 3: Write your own SQL Query and click Run to verify the data. Once you are satisfied, click Done.

In the above example, if you want to get notified when sales drop below a certain threshold, you can use the below SQL script:

SELECT CURRENT_DATE() AS current_date, product_id, SUM(sales_amount) AS total_sales
    FROM `your_project.your_dataset.sales_data`
    WHERE DATE(sales_date) = CURRENT_DATE()
    GROUP BY product_id
    HAVING total_sales < 1000;

‍

Step 4: Now Setup your schedule based on your requirement, this would automatically run the query based on the frequency you set. Click on Run the query at frequency and choose your schedule

‍

Step 5: With Locale, You can also customize the Slack message to get the better idea. To configure the Slack message, Click on Configure incidents and enter your message in the Incident Title.

‍

Step 6: Final Step, Set up whom or on which channel the Slack alert should be sent. Go to the Setup Notification section and click on Add Notification then select Send Slack Message. Select whether you want the Slack message to be sent to a channel or a user. You can also preview and send test notifications.

Save the alert. It will automatically run itself on the pre-set schedule and trigger a Slack notification.

‍

Conclusion

Automating triggers to Slack helps your team to be notified on their preferred channel so that they can quickly response to changes or issues. In this detailed guide, we looked at two methods which can help you to automate Slack alerts from BigQuery database.  Although Method #1, through Python Script is a viable option, it can be complex and error-prone to implement, especially if you need to handle complex notification scenarios.

Locale’s no-code platform makes it easy to automate alerts to Slack from your BigQuery database with minimal one-time setup. Locale helps you to:

• Easy automation of triggers to Slack
• Choose a schedule for sending notifications based on your requirements.
• Dynamically choose people to send Slack message based on the query result allowing alerts only go to the person it needs.
• Group the notifications ensuring less noise to the users.
• Set up and provide a clear playbook for users to follow in case of any issue so that it can be resolved efficiently.
• Set up an escalation rule to ensure issues are being resolved.
• Moreover, if you want to expand sending reports/data to other destinations like Gmail, WhatsApp, MS Teams etc, you get to do that with 100+ integration options.

‍

If you are looking for a simple and reliable way to automate triggers to Slack from your BigQuery Database, try Locale. Reserve your spot - talk to our specialist today.