{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "426e6d6a",
   "metadata": {},
   "source": [
    "# RelationalAI Native App Cost Monitoring\n",
    "\n",
    "Using RelationalAI (RAI) on Snowflake incurs costs from both Snowflake and RAI itself. Snowflake costs are based on compute and storage resource usage, detailed in the [Snowflake User Guide](https://docs.snowflake.com/en/guides-overview-cost).\n",
    "\n",
    "RAI costs include a surcharge for compute usage, primarily from RAI engines. There’s no surcharge for storage used by the application. Similar to Snowflake, RAI’s cloud architecture separates storage and compute usage, allowing for cost optimization based on specific needs. For more information, please visit [the RelationalAI website](https://docs.relational.ai/manage/costs).\n",
    "\n",
    "> :warning: Before the end of 2024, Snowflake will add support for detailed cost monitoring of their native apps within the standard Snowflake cost monitoring tools. In the meantime, this notebook can be used to get an understanding of the number of units that are used by RAI engines. You can also use these queries in Snowflake Dashboards to visualize the estimated RAI units used.\n",
    "\n",
    "## List Active Engines\n",
    "\n",
    "This query lists all the RAI engines that are currently running in the account:\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "18fff159",
   "metadata": {
    "language": "sql"
   },
   "outputs": [],
   "source": [
    "SELECT * FROM relationalai.api.engines;"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "99ab16d3",
   "metadata": {},
   "source": [
    "## Show Total Number Of RAI Units Per Month\n",
    "\n",
    "This query provides the total number of RAI units used by RAI engines for the past month:\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "d8c9253e",
   "metadata": {
    "language": "sql"
   },
   "outputs": [],
   "source": [
    "SELECT\n",
    "  DATE_TRUNC('MONTH', hour) AS first_day_of_month,\n",
    "  SUM(credits) AS sum_of_rai_units\n",
    "FROM\n",
    "  relationalai.consumption.engine_credit_hours\n",
    "GROUP BY\n",
    "  first_day_of_month\n",
    "ORDER BY\n",
    "  first_day_of_month DESC;"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "dc68b7b4",
   "metadata": {},
   "source": [
    "## Snow Number Of RAI Units Used Per Day\n",
    "\n",
    "This query provides a daily breakdown of the total number of RAI units used by RAI engines for the past month.\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "1e0544d0",
   "metadata": {
    "language": "sql"
   },
   "outputs": [],
   "source": [
    "SELECT\n",
    "  DATE_TRUNC('day',c.HOUR) as day, sum(c.credits) AS total_daily_rai_units\n",
    "FROM\n",
    "  relationalai.consumption.engine_credit_hours c\n",
    "WHERE\n",
    "  hour >= DATEADD(month, -1, CURRENT_TIMESTAMP())\n",
    "GROUP BY all ORDER BY 1 DESC;"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "416a334d",
   "metadata": {},
   "source": [
    "## Show Number Of RAI Units Used Per Engine\n",
    "\n",
    "This query provides an breakdown by RAI engine of the total number of RAI units used during the past month.\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "ea2683f7",
   "metadata": {
    "language": "sql"
   },
   "outputs": [],
   "source": [
    "SELECT\n",
    "  engine_name,\n",
    "  SUM(credits) AS total_credits,\n",
    "  COUNT(hour) AS hours\n",
    "FROM\n",
    "  relationalai.consumption.engine_credit_hours\n",
    "WHERE\n",
    "  hour >= DATEADD(month, -1, CURRENT_TIMESTAMP())\n",
    "GROUP BY 1 ORDER BY 2 DESC;"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "c4d90ff7",
   "metadata": {},
   "source": [
    "## Compute Pools Cost For the RAI Native App\n",
    "\n",
    "Your account incurs compute costs from two main sources when the RAI Native App is installed:\n",
    "\n",
    "- Central Service Compute Pool: Allocated to the native app for RelationalAI service operations, without any RAI compute surcharge. The RAI service compute pool is created automatically when the app is activated, and suspended automatically when the app is deactivated.\n",
    "- Engine Compute Pools: Used to query RAI models. Like Snowflake virtual warehouses, engines can be independently created for workload isolation or scaling. These pools have a RAI cost surcharge based on their uptime. RAI Engines are managed through the RelationalAI CLI or provided SQL procedures (see queries above for engine uptimes).\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "6f1b16fc",
   "metadata": {
    "language": "sql"
   },
   "outputs": [],
   "source": [
    "SELECT\n",
    "    DATE_TRUNC('MONTH', START_TIME) AS first_day_of_month,\n",
    "    compute_pool_name,\n",
    "    SUM(credits_used) as total_credits\n",
    "FROM\n",
    "   SNOWFLAKE.ACCOUNT_USAGE.SNOWPARK_CONTAINER_SERVICES_HISTORY\n",
    "WHERE\n",
    "    APPLICATION_NAME = 'RELATIONALAI'\n",
    "GROUP BY\n",
    "    first_day_of_month, compute_pool_name\n",
    "ORDER BY\n",
    "    first_day_of_month DESC,\n",
    "    compute_pool_name;"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "f76a1281",
   "metadata": {},
   "source": [
    "## Compute Warehouse Costs For the RAI Native App\n",
    "\n",
    "The RAI Native App utilizes a Snowflake virtual warehouse for Snowflake interoperability. This warehouse is created automatically when the RAI application is activated, and suspended automatically when the app is deactivated. There’s no RAI cost surcharge for using the virtual warehouse.\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "0cae568c",
   "metadata": {
    "language": "sql"
   },
   "outputs": [],
   "source": [
    "SELECT\n",
    "    DATE_TRUNC('MONTH', START_TIME) AS first_day_of_month,\n",
    "    SUM(credits_used) as total_credits\n",
    "FROM\n",
    "    SNOWFLAKE.ACCOUNT_USAGE.WAREHOUSE_METERING_HISTORY\n",
    "WHERE\n",
    "    WAREHOUSE_NAME = 'RELATIONAL_AI_ERP_WAREHOUSE'\n",
    "GROUP BY\n",
    "  first_day_of_month\n",
    "ORDER BY\n",
    "  first_day_of_month DESC"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "b8e764fb",
   "metadata": {},
   "source": [
    "## Transactions Performed by All Engines in the Past Month\n",
    "\n",
    "To help understand which queries or other transactions are consuming the most credits, you can run the following query to see all transactions performed by all engines in the past month sorted by duration in descending order:\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "75090258",
   "metadata": {
    "language": "sql"
   },
   "outputs": [],
   "source": [
    "SELECT\n",
    "  *\n",
    "FROM\n",
    "  relationalai.api.transactions\n",
    "WHERE\n",
    "  created_on >= DATEADD(month, -1, CURRENT_TIMESTAMP())\n",
    "ORDER BY\n",
    "  duration DESC;"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "c1736c11",
   "metadata": {},
   "source": [
    "> **Note**: Although duration alone does not determine a transaction's cost—other factors such like the [engine size](https://docs.relational.ai/manage/compute-resources#engine-sizes) also affect cost—you can use this query to identify transactions that may be responsible for high costs. You can then investigate these transactions further to determine if they can be optimized.\n"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Streamlit Notebook",
   "name": "streamlit"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}