notebooks/databricks_daily_cost_slack/databricks_cost_slack.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {
    "application/vnd.databricks.v1+cell": {
     "cellMetadata": {
      "byteLimit": 2048000,
      "rowLimit": 10000
     },
     "inputWidgets": {},
     "nuid": "2101dea2-6085-4982-aec2-b961bd745dd1",
     "showTitle": false,
     "title": ""
    }
   },
   "source": [
    "# Databricks Cost to Slack 🧱\n",
    "\n",
    "![](https://img.shields.io/badge/Slack-4A154B.svg?style=for-the-badge&logo=Slack&logoColor=white)\n",
    "![](https://img.shields.io/badge/Databricks-FF3621.svg?style=for-the-badge&logo=Databricks&logoColor=white)\n",
    "\n",
    "> Authors: [Eshwaran Venkat](github.com/cricksmaidiene)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 0,
   "metadata": {
    "application/vnd.databricks.v1+cell": {
     "cellMetadata": {
      "byteLimit": 2048000,
      "rowLimit": 10000
     },
     "inputWidgets": {},
     "nuid": "07c123a1-3fa9-47e6-ac0d-545959aaee6e",
     "showTitle": true,
     "title": "Install Packages"
    }
   },
   "outputs": [],
   "source": [
    "!pip install tabulate -q"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 0,
   "metadata": {
    "application/vnd.databricks.v1+cell": {
     "cellMetadata": {
      "byteLimit": 2048000,
      "rowLimit": 10000
     },
     "inputWidgets": {},
     "nuid": "3b6078c1-599f-4805-bc32-d92fa236258c",
     "showTitle": true,
     "title": "Imports"
    }
   },
   "outputs": [],
   "source": [
    "import warnings\n",
    "warnings.filterwarnings(\"ignore\")\n",
    "\n",
    "import pandas as pd\n",
    "import os\n",
    "import requests"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 0,
   "metadata": {
    "application/vnd.databricks.v1+cell": {
     "cellMetadata": {
      "byteLimit": 2048000,
      "rowLimit": 10000
     },
     "inputWidgets": {},
     "nuid": "960f5304-ba21-4664-a960-019843601c98",
     "showTitle": true,
     "title": "Declare and Consume Notebook Parameters"
    }
   },
   "outputs": [],
   "source": [
    "dbutils.widgets.removeAll()\n",
    "dbutils.widgets.text(\"DAY_COUNT\", \"2\")\n",
    "dbutils.widgets.text(\"SLACK_WEBHOOK\", \"\")\n",
    "\n",
    "N: int = int(dbutils.widgets.get(\"DAY_COUNT\"))  # Example value for N days ago, adjust as needed\n",
    "SLACK_WEBHOOK = dbutils.widgets.get(\"SLACK_WEBHOOK\")\n",
    "\n",
    "assert all([N, SLACK_WEBHOOK]), \"One or more required parameters not set\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 0,
   "metadata": {
    "application/vnd.databricks.v1+cell": {
     "cellMetadata": {
      "byteLimit": 2048000,
      "rowLimit": 10000
     },
     "inputWidgets": {},
     "nuid": "10496414-7426-43ea-9b61-2eee1bc520c0",
     "showTitle": true,
     "title": "Declare Slack Message Payload Template"
    }
   },
   "outputs": [],
   "source": [
    "# The message payload using Block Kit for formatting\n",
    "message_payload = {\n",
    "    \"blocks\": [\n",
    "        {\n",
    "            \"type\": \"section\",\n",
    "            \"text\": {\n",
    "                \"type\": \"mrkdwn\",\n",
    "                \"text\": \"\"\n",
    "            }\n",
    "        },\n",
    "        {\n",
    "            \"type\": \"divider\"\n",
    "        },\n",
    "        {\n",
    "            \"type\": \"section\",\n",
    "            \"text\": {\n",
    "                \"type\": \"mrkdwn\",\n",
    "                \"text\": \"\"\n",
    "            }\n",
    "        }\n",
    "    ]\n",
    "}"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 0,
   "metadata": {
    "application/vnd.databricks.v1+cell": {
     "cellMetadata": {
      "byteLimit": 2048000,
      "rowLimit": 10000
     },
     "inputWidgets": {},
     "nuid": "47bec7b8-8abd-4fd4-9c8c-b0867798d307",
     "showTitle": true,
     "title": "Read Billing Data"
    }
   },
   "outputs": [],
   "source": [
    "df = spark.sql(\n",
    "    f\"\"\"\n",
    "    SELECT *, identity_metadata.run_as as user FROM system.billing.usage \n",
    "    WHERE usage_date >= date_trunc('day', NOW()) - interval '{N} day' \n",
    "    AND usage_date < date_trunc('day', NOW()) - interval '{N-1} day'\n",
    "    \"\"\"\n",
    ").toPandas()\n",
    "\n",
    "print(df.shape)\n",
    "df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 0,
   "metadata": {
    "application/vnd.databricks.v1+cell": {
     "cellMetadata": {
      "byteLimit": 2048000,
      "implicitDf": true,
      "rowLimit": 10000
     },
     "inputWidgets": {},
     "nuid": "15612e57-f6e2-40ca-9c22-b02fee194135",
     "showTitle": true,
     "title": "Calculate cost by SKU"
    }
   },
   "outputs": [],
   "source": [
    "dbu_usd_prices = spark.sql(\n",
    "    f\"\"\"\n",
    "    SELECT sku_name, round(pricing.default, 2) as usd_price \n",
    "    FROM system.billing.list_prices \n",
    "    WHERE sku_name in (\n",
    "        SELECT sku_name FROM system.billing.usage \n",
    "        WHERE usage_date >= date_trunc('day', NOW()) - interval '{N} day' \n",
    "    AND usage_date < date_trunc('day', NOW()) - interval '{N-1} day'\n",
    "    )\n",
    "    \"\"\"\n",
    ").toPandas()\n",
    "\n",
    "print(dbu_usd_prices.shape)\n",
    "dbu_usd_prices.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 0,
   "metadata": {
    "application/vnd.databricks.v1+cell": {
     "cellMetadata": {
      "byteLimit": 2048000,
      "rowLimit": 10000
     },
     "inputWidgets": {},
     "nuid": "103a8a1a-b2c4-4933-8e63-0543414257bc",
     "showTitle": true,
     "title": "USD Calculation"
    }
   },
   "outputs": [],
   "source": [
    "df[\"usd\"] = (\n",
    "    df[\"sku_name\"].map(dbu_usd_prices.set_index(\"sku_name\").to_dict()[\"usd_price\"])\n",
    "    * df[\"usage_quantity\"]\n",
    ")\n",
    "df[\"usd\"] = df[\"usd\"].astype(float).round(2)\n",
    "print(df[\"usd\"].info())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 0,
   "metadata": {
    "application/vnd.databricks.v1+cell": {
     "cellMetadata": {
      "byteLimit": 2048000,
      "rowLimit": 10000
     },
     "inputWidgets": {},
     "nuid": "10f51852-a121-4c5f-900c-6079d0036cdd",
     "showTitle": true,
     "title": "Calculate Final Daily Bill"
    }
   },
   "outputs": [],
   "source": [
    "report = df.groupby([\"billing_origin_product\"])[[\"usd\"]].sum().reset_index()\n",
    "report.columns = [\"product\", \"cost\"]\n",
    "report['product'] = report['product'].str.replace(\"_\", \" \").str.title()\n",
    "report"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 0,
   "metadata": {
    "application/vnd.databricks.v1+cell": {
     "cellMetadata": {
      "byteLimit": 2048000,
      "rowLimit": 10000
     },
     "inputWidgets": {},
     "nuid": "d80ac4cc-4a32-48bb-b726-55f9ee7c119f",
     "showTitle": true,
     "title": "Prepare Messages"
    }
   },
   "outputs": [],
   "source": [
    "usage_date = df[\"usage_date\"].iloc[0].strftime(r\"%b %d\")\n",
    "day_cost = report[\"cost\"].sum()\n",
    "message_color = \"info\" if day_cost < 22 else \"error\"\n",
    "\n",
    "message_title = f\"🧱 *Databricks Cost* for *{usage_date}* is *${day_cost:,.2f}*\"\n",
    "message_log = f\"```{report.to_markdown(index=False)}```\"\n",
    "\n",
    "message_payload['blocks'][0]['text']['text'] = message_title\n",
    "message_payload['blocks'][2]['text']['text'] = message_log"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 0,
   "metadata": {
    "application/vnd.databricks.v1+cell": {
     "cellMetadata": {
      "byteLimit": 2048000,
      "rowLimit": 10000
     },
     "inputWidgets": {},
     "nuid": "091f0117-c47e-4c99-85e9-0dab4ca6a462",
     "showTitle": true,
     "title": "Post to Slack using Incoming Webhooks"
    }
   },
   "outputs": [],
   "source": [
    "# Sending the POST request to the Slack webhook URL\n",
    "response = requests.post(SLACK_WEBHOOK, json=message_payload)\n",
    "assert response.status_code == 200, \"Unable to post to slack\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 0,
   "metadata": {
    "application/vnd.databricks.v1+cell": {
     "cellMetadata": {},
     "inputWidgets": {},
     "nuid": "ae70af1e-12eb-4c34-b201-84eadf746212",
     "showTitle": false,
     "title": ""
    }
   },
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "application/vnd.databricks.v1+notebook": {
   "dashboards": [],
   "environmentMetadata": {
    "base_environment": "",
    "client": "1"
   },
   "language": "python",
   "notebookMetadata": {
    "mostRecentlyExecutedCommandWithImplicitDF": {
     "commandId": -1,
     "dataframes": [
      "_sqldf"
     ]
    },
    "pythonIndentUnit": 4
   },
   "notebookName": "databricks_cost_slack",
   "widgets": {
    "DAY_COUNT": {
     "currentValue": "1",
     "nuid": "7d1b9b75-b7db-4c39-ad5f-16c884db15c9",
     "typedWidgetInfo": {
      "autoCreated": false,
      "defaultValue": "2",
      "label": null,
      "name": "DAY_COUNT",
      "options": {
       "widgetDisplayType": "Text",
       "validationRegex": null
      },
      "parameterDataType": "String"
     },
     "widgetInfo": {
      "widgetType": "text",
      "defaultValue": "2",
      "label": null,
      "name": "DAY_COUNT",
      "options": {
       "widgetType": "text",
       "autoCreated": null,
       "validationRegex": null
      }
     }
    },
    "SLACK_WEBHOOK": {
     "currentValue": "",
     "nuid": "9a846535-f013-40e2-835c-8f8a29da9807",
     "typedWidgetInfo": {
      "autoCreated": false,
      "defaultValue": "",
      "label": null,
      "name": "SLACK_WEBHOOK",
      "options": {
       "widgetDisplayType": "Text",
       "validationRegex": null
      },
      "parameterDataType": "String"
     },
     "widgetInfo": {
      "widgetType": "text",
      "defaultValue": "",
      "label": null,
      "name": "SLACK_WEBHOOK",
      "options": {
       "widgetType": "text",
       "autoCreated": null,
       "validationRegex": null
      }
     }
    }
   }
  }
 },
 "nbformat": 4,
 "nbformat_minor": 0
}