offbi/snowflake-to-slack

Send Slack messages spiced :hot_pepper: with data from Snowflake :snowflake:

snowflake-to-slack

Easily send Slack messages templated with data from Snowflake.

snowflake-to-slack can be executed as:

• Github Action
• Docker container
• Python script

Use cases

• send daily / weekly / monthly reports to your SDR or any channel in human words.
• send information on whether anomalies were detected in the selected metrics. With e.g. SQL anomaly detection.
• view information about yesterday's credit spend in Slack.
• monitor dbt model execution and dependencies from Slack with dbt_artifacts.

How to setup snowflake-to-slack

1. First you need to generate Slack Token.
2. Design your Slack message with Slack Block Kit
3. Inside your repository (it can be also dbt repository) create new folder. e.g. slack_templates and copy designed message here. I recommend you to use .j2 suffix, e.g. test.j2.
4. Inside your Slack template, replace all dynamic values you want to replace with Snowflake data with {{<COLUMN NAME>}}. E.g.

[
	{
		"type": "section",
		"text": {
			"type": "mrkdwn",
			"text": "Hello {{GREETINGS}}"
		}
	}
]

Then if I will have SQL query in Snowflake like this:

SELECT 'guys!' AS GREETINGS

The message Hello guys! will be sent to Slack.

BEWARE you need to uppercase your variable names in template since Snowflake is uppercasing column names by default!!

Inside templates you can use anything from Jinja templating language (as you probably know from dbt), e.g.:

-- default
"text": "Hello {{GREETINGS|default('girls!')}}"

-- conditions
{% if SLACK_CHANNEL == '#general' %}
    "text": "Hello people"
{% else %}
   "text": "Hello {{USERNAME}}!"
{% endif %}

-- for loop
"text": "You have:
{% for i in range(3) %}
 {{ i }}
{% endfor %}
seconds to answer!
"

You can also include and import other templates!

See Template Designer Documentation for full reference of Jinja2 syntax.

5. Create Snowflake SQL script. Here are some things you should know:

   • Every row from SQL will be send as separated Slack Message. It means that if you want to send PM to different Slack users you do not have to create separete SQL for her/him, but you create one SQL and add column SLACK_CHANNEL with Slack user id. With this you are able to burst it.
   • There are some special columns that influence the message distribution:
     • SLACK_MESSAGE_TEMPLATE: full name of Jinja template (e.g. test.j2) from template path. This value can be overriden with cli parameters (see later).
     • SLACK_MESSAGE_TEXT: useful if you want to send just simple message without block kit and without templating. So you can use SLACK_MESSAGE_TEMPLATE or SLACK_MESSAGE_TEXT. If you use both, SLACK_MESSAGE_TEMPLATE will be used for main message in Slack and SLACK_MESSAGE_TEXT for notification message. This value can be overriden with cli parameters (see later).
     • SLACK_CHANNEL: name of Slack channel (with #) where you want to send your message. Can also be the name (john.doe) or ID of Slack user.
     • SLACK_FREQUENCY: useful e.g. in cases when you have one SQL and you want to burst it to many users. Some users wants this report daily but some weekly. Specify list of values separated with comma e.g. weekly,monthly. Allowed values are: daily,weekly,monthly,quartely,yearly,monday,tuesday,wednesday,thursday,friday,saturday,sunday,never,always
   • Name of column can be used in template (but it doesn't have to).

   Example

   SELECT
      'test.j2' AS SLACK_MESSAGE_TEMPLATE,
      'This is notification' AS SLACK_MESSAGE_TEXT,
      'weekly,monthly' AS SLACK_FREQUENCY,
      '#random' as SLACK_CHANNEL,
      -------
      'guys!' as GREETINGS
   

6. You of course need some Snowflake credentials :). You can use simple name/password or key-pair authorization.

Sending messages

You can send Slack messages with:

• Github Action
• Docker container
• Python script

Github Actions

Quickstart

• inside your Github repository create folder .github/workflows (unless it already exists).
• create new file e.g. main.yml
• specify your workflow, e.g.:

on:
  # to allow manual run
  workflow_dispatch:
  schedule:
    # * is a special character in YAML so you have to quote this string
    - cron:  '0 0 * * *'

jobs:
  snowflake-to-slack:
    runs-on: ubuntu-latest
    name: snowflake to slack
    steps:
      - name: Checkout repository
        uses: actions/checkout@v2.3.3
      - name: Slack message 1
        uses: offbi/snowflake-to-slack@1.0.0
        with:
          user: ${{ secrets.USER }}
          password: "${{ secrets.PASSWORD }}"
          account: "<your Snowflake account name>"
          warehouse: "<your Snowflake warehouse name>"
          database: "<your Snowflake database name>"
          role: "<your Snowflake role name>"
          slack-token: ${{ secrets.SLACK_TOKEN }}
          sql: "<your Snowflake select statement>"
          template-path: /github/workspace/<name of template folder in your repo>

Beware before you run snowflake-to-slack you need to run actions/checkout action. template-path hash to be specified ad /github/workspace/<name of template folder>

• it is adviced to use Github Secrets to specify passwords and slack-tokens.
• push to Github :)
• if you want to use rsa key instead of password (recommended) - fill RSA_KEY secret and use this workflow:

on:
  workflow_dispatch:
  schedule:
    # * is a special character in YAML so you have to quote this string
    - cron:  '0 0 * * *'

jobs:
  snowflake-to-slack:
    runs-on: ubuntu-latest
    name: snowflake to slack
    steps:
      - name: Checkout repository
        uses: actions/checkout@v2.3.3
      - run: 'echo "$RSA_KEY" > rsa_key.p8'
        shell: bash
        env:
          RSA_KEY: ${{secrets.RSA_KEY}}
      - name: Slack message 1
        uses: offbi/snowflake-to-slack@1.0.1
        with:
          user: ${{ secrets.USER }}
          rsa-key-uri: /github/workspace/rsa_key.p8
          private-key-pass: ${{ secrets.PRIVATE_KEY_PASS }}
          account: "<your Snowflake account name>"
          warehouse: "<your Snowflake warehouse name>"
          database: "<your Snowflake database name>"
           role: "<your Snowflake role name>"
          slack-token: ${{ secrets.SLACK_TOKEN }}
          sql: | 
            SELECT
              'test.j2' AS SLACK_MESSAGE_TEMPLATE,
              'This is notification' AS SLACK_MESSAGE_TEXT,
              '#random' as SLACK_CHANNEL,
              -------
              'guys!' as GREETINGS
          template-path: /github/workspace/<name of template folder in your repo>

To learn more about Github actions see https://docs.github.com/en/actions/reference/workflow-syntax-for-github-actions.

Github Action parameters

• user: Snowflake Username. Required: true
• password: Snowflake Password. Required: false
• rsa-key-uri: URI of RSA key for authorization. Required: false
• private-key-pass: RSA key password. Required: false
• account: Snowflake Account. Required: true
• warehouse: Snowflake Warehouse. Required: true
• database: Snowflake Database. Required: true
• role: Snowflake Role. Required: true
• slack-token: Slack Token. Required: true
• slack-channel: Slack Channel. This parameter overrides value from database Required: false
• fail-fast: Raise error and stop execution if error shows during sending message. Required: false
• dry-run: Just print message into stdout. Do not send message to Slack. Required: false
• date-valid: Date valid for deciding if message should be executed. Default current date. Required: false
• sql: SQL command to run. Required: true
• template-path: Path with your Jinja templates. Required: true
• slack-frequency: Frequency. Together with date-valid determines whether the message is sent. This parameter overrides value from database. Required: false.
• slack-message-template: Message template. It overrides SLACK_MESSAGE_TEMPLATE from Snowflake. Required: false.
• slack-message-text: Message text. It overrides SLACK_MESSAGE_TEXT from Snowflake. Required: false.

Docker container

To run Docker container execute:

docker run <list of envs> offbi/snowflake-to-slack <params>

Python script

Install snowflake-to-slack with:

pip install snowflake-to-slack=='1.0.0'
snowflake-to-slack <params>

List of snowflake-to-slack params

• --user: Snowflake Username. Required: true. Env variable SNOWFLAKE_USER.
• --password: Snowflake Password. Required: false. Env variable SNOWFLAKE_PASS.
• --rsa-key-uri: URI of RSA key for authorization. Required: false. Env variable SNOWFLAKE_RSA_KEY_URI.
• --private-key-pass: RSA key password. Required: false. Env variable SNOWFLAKE_PRIVATE_KEY_PASS.
• --account: Snowflake Account. Required: true. Env variable SNOWFLAKE_ACCOUNT.
• --warehouse: Snowflake Warehouse. Required: true. Env variable SNOWFLAKE_WAREHOUSE.
• --database: Snowflake Database. Required: true. Env variable SNOWFLAKE_USER.
• --role: Snowflake Role. Required: true. Env variable SNOWFLAKE_ROLE.
• --slack-token: Slack Token. Required: true. Env variable SLACK_TOKEN.
• --slack-channel: Slack Channel. This parameter overrides value from database Required: false. Env variable SLACK_CHANNEL.
• --fail-fast: Raise error and stop execution if error shows during sending message. Required: false. Env variable FAIL_FAST.
• --dry-run: Just print message into stdout. Do not send message to Slack. Required: false. Env variable DRY_RUN.
• --date-valid: Date valid for deciding if message should be executed. Default current date. Required: false. Env variable DATE_VALID.
• --sql: SQL command to run. Required: true. Env variable SQL.
• --template-path: Path with your Jinja templates. Required: true. Env variable TEMPLATE_PATH.
• --slack-frequency: Frequency. Together with date-valid determines whether the message is sent. This parameter overrides value from database. Required: false. Env variable SLACK_FREQUENCY.
• --slack-message-template: Message template. It overrides SLACK_MESSAGE_TEMPLATE from Snowflake. Required: false. Env variable SLACK_MESSAGE_TEMPLATE.
• --slack-message-text: Message text. It overrides SLACK_MESSAGE_TEXT from Snowflake. Required: false. Env variable SLACK_MESSAGE_TEXT.