Engineering

How to Send Slack Notifications via Metabase when a New Row is Added

November 10, 2023

Automate Slack Notification via Metabase when a New Row is Added

TL;DR

This article talks about two methods of automating Slack notifications via Metabase whenever a new row is added to a database table

Method 1: Manual setup through Metabase & Python Script:

  1. Set up a server (local or cloud) and install necessary software.
  2. Log in to Metabase and identify Metabase API for the table to monitor.
  3. Create a Slack App in your workspace and obtain a webhook URL.
  4. Create a Python script to regularly check for new rows via the Metabase API and send Slack notifications using the webhook URL.

Method 2: Trigger Notification through Locale:

  1. Connect your database to Locale.ai.
  2. Set up frequency to automatically run SQL queries.
  3. Configure Slack message templates and recipients.
  4. Save the alert, and Locale will automatically trigger Slack notifications based on the defined conditions.

While Metabase offers Slack integration, it has limitations such as lack of customization and real-time alerts. The manual setup with Python scripting provides more flexibility but requires ongoing maintenance and setup complexity. In contrast, Locale.ai simplifies the process with minimal setup and allows for dynamic message customization and recipient selection. Locale also offers additional features like clear playbooks, escalation rules, and multiple integration options beyond Slack.

For every business, achieving optimal observability is crucial. Metabase is an excellent data visualization tool which can transform raw data into actionable insights to help you make informed decisions. In the context of business operations, it becomes important to implement a solution that ensures real-time notifications to platforms like Slack whenever a new entry is added. So that, you can be promptly informed about significant events such as new customer sign-ups or a new order being placed.

Metabase has some Slack integration, but it comes with its own set of limitations:

  • It also doesn’t provide you with much customization to the message the user will receive.
  • It is only capable of sending alerts as images or files, which doesn’t give actionable insight quickly.
  • At best, you can schedule these alerts to run hourly, which would not cater to the need for immediate alerts.

In this detailed guide, we’ll walk you through easy methods to trigger notifications on Slack whenever a new row gets added.

  1. Method #1: Manual setup through Metabase & Python Script
  2. Method #2: Trigger Notification through Locale

Method #1: Manual setup through Metabase & Python Script

In the first method, we will discuss how to send Slack notifications using a Python script. This method involves calling the Metabase card API regularly to check for any new rows. If new rows are found, a Slack message is sent for each of them. To keep track of the last seen message, we use the variable last_id. Before running the script, you can modify this variable to specify the starting row for receiving messages. Please note that for long-term usage or offline scenarios, it would be necessary to store the last_id in a persistent store, such as a database.

1. Server Setup

  1. If you already have a server (local or on the cloud) to run a Python script, skip ahead to step 2.
  2. Choose a cloud service provider like AWS, Google Cloud, or Azure to set up a server. You could use your own machine as well to test this method.
  3. If using the cloud, create a new instance (e.g., EC2 on AWS), and follow the setup instructions provided by the cloud service.
  4. Install the necessary software on your server: Python, and any other dependencies.

2. Metabase Setup

  • Log in to your Metabase instance.
  • Identify the URL of the Metabase API for the table you want to monitor. It usually looks like https://your-metabase-instance/api/card/{card_id}/query/json.

3. Slack Setup

  • Create a new Slack App in your Slack workspace.
  • Under 'Features' on the left sidebar, click on 'Incoming Webhooks', then activate incoming webhooks and create a new webhook to the desired Slack channel. Copy the Webhook URL.
Activating incoming webhook on Slack

4. Python Scripting

Now, write a Python script that encompasses the following functions:


pythonCopy code
import requests
import time
import pandas as pd

METABASE_API_URL = "https://your-metabase-instance/api/card/{card_id}/query/json"
METABASE_HEADERS = {"Content-Type": "application/json", "X-Metabase-Session": ""}
SLACK_WEBHOOK_URL = "https://hooks.slack.com/services/T00000000/B00000000/XXXXXXXXXXXXXXXXXXXXXXXX"

def get_new_data(last_id):
    response = requests.post(METABASE_API_URL, headers=METABASE_HEADERS)
    response.raise_for_status()  # Check for a valid response
    data = response.json()
    new_data = [row for row in data if row['id'] > last_id]
    return new_data

def process_data(new_data):
    messages = [f"New row added: {row}" for row in new_data]
    return messages

def send_to_slack(messages):
    for message in messages:
        payload = {"text": message}
        response = requests.post(SLACK_WEBHOOK_URL, json=payload)
        response.raise_for_status()  # Check for a valid response

def main():
    last_id = 0  # Initialize with the ID of the last known record
    while True:
        new_data = get_new_data(last_id)
        if new_data:
            last_id = new_data[-1]['id']  # Update last_id to the latest record's ID
            messages = process_data(new_data)
            send_to_slack(messages)
        time.sleep(300)  # Wait for 5 minutes before polling again

if __name__ == "__main__":
    main()

5. Final Step

  • Upload your script to your server.
  • Run your script using the command: python script_name.py.

When running for the first, you can avoid an overflow of Slack messages by changing the last_id to something more recent. If you need to save the state of the script (like the last_id) you will need to keep storing this data on to persistent storage like a database or a text file.

Method #2: Trigger Notification through Locale

Locale significantly simplifies the process of sending Slack messages from SQL triggers. With just a few clicks, you can automate the notification process without the need for additional scripts or external monitoring tools.

Login to Locale, Go to Org Setting and Connect your database from the data source. Now go to Alerts → Create New Alerts. Choose your database → Click on Setup data to be monitored.

Database Connection setup on Locale platform

Write SQL Query to set the alert condition that will trigger Slack notification when the query results match:

SQL Query to set the alert condition that will trigger alerts

Now Setup Alert Frequency based on your requirement, this would automatically run the query and send a Slack message based on the frequency you set.

Scheduling alert runs in Locale by setting alert frequency

You can also configure the Slack message to give the user a better idea. Click on Configure incidents and enter your message in the Incident Title.

Configure incidents on Locale to provide better context to the users receiving alerts

💡 You can also customise the message based on the query result by clicking on { } icon.

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.

Configuring Slack alerts to define whom to send alerts on Locale
💡 If you want to trigger notifications to Slack users, you can also dynamically send Slack messages based on query results.

Save the alert. It will automatically run itself and trigger a Slack notification.

Sample Slack alerts for new user signed up from Locale

Conclusion

Now, you have two methods to send Slack notifications for new rows via Metabase.

Metabase can be a great tool for your data visualization needs, relying solely on its dashboard capabilities would not suffice. Metabase's data visualization is labour-intensive and necessitates constant human monitoring, making it susceptible to errors. Also, setting up alerts through it requires significant setup and ongoing maintenance. It’s also prone to errors, and troubleshooting issues can be time-consuming. Also, It can only send notifications to a fixed user and doesn’t allow you to send messages to multiple users based on query results.

On the other hand, Locale handles the heavy lifting allowing you to sit back and receive notifications directly in Slack with minimal one-time setup. You can dynamically choose people to send Slack message based on the query result, also provide you with the option to choose a schedule of sending messages based on your requirement.

It also allows you to set up and provide a clear playbook for users to follow in case of any issue so that it can be resolved efficiently and 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.

Excited to get started? Reserve your spot - talk to our specialist today.

Receive Latest InsideOps Updates

Thank you! Your submission has been received!
Oops! Something went wrong while submitting the form.