tasty_bytes_snowpark_101.ipynb

{
 "cells": [
  {
   "attachments": {},
   "cell_type": "markdown",
   "id": "99fbd378-6297-420c-811e-d93eb47dca58",
   "metadata": {
    "name": "overview"
   },
   "source": [
    "# Overview\n",
    "\n",
    "Tasty Bytes is one of the largest food truck networks in the world with localized menu options spread across 30 major cities in 15 countries. **Tasty Bytes is aiming to achieve 25% YoY sales growth over 5 years.**\n",
    "\n",
    "As Tasty Bytes Data Scientists, we have been asked to support this goal by helping our food truck drivers more intelligently pick where to park for shifts. **We want to direct our trucks to locations that are expected to have the highest sales on a given shift. This will maximize our daily revenue across our fleet of trucks.**\n",
    "\n",
    "To provide this insight, we will use historical shift sales at each location to build a model. This data has been made available to us in Snowflake.\n",
    "\n",
    "Our model will provide the predicted sales at each location for the upcoming shift.\n",
    "\n",
    "\n",
    "**This is an introduction to Snowpark for Snowflake.\n",
    "We will use Snowpark to:**\n",
    "\n",
    "- Explore the data\n",
    "- Perform feature engineering\n",
    "- Train a model\n",
    "- Deploy the model in Snowflake\n",
    "\n",
    "\n",
    "**Why Snowpark?**\n",
    "\n",
    "- No copies or movement of data\n",
    "- Maintain governance\n",
    "- Leverage Snowflake scalable compute\n",
    "- ...and more!\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "ce110000-1111-2222-3333-ffffff000000",
   "metadata": {
    "name": "add_packages"
   },
   "source": [
    "# Before you begin\n",
    "\n",
    "You will be running this as a Snowflake Notebook, so you will need to add the following packages in the Packages drop-down menu in the upper right corner of the UI:\n",
    "- matplotlib \n",
    "- plotly\n",
    "- snowflake-ml-python"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "ede8aebb-1df7-4bb7-8e96-52cdde843707",
   "metadata": {
    "name": "SNOWPARK_101"
   },
   "source": [
    "--------------\n",
    "# ❄️ SNOWPARK 101 ❄️\n",
    "--------------"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "id": "b340af82-63a2-4f5d-b8a4-8eafa6d8a26f",
   "metadata": {
    "name": "cell4"
   },
   "source": [
    "\n",
    "Let's get to know Snowpark. We will see that Snowpark makes it easy for Python users to leverage the Snowflake platform. Bringing these users into the Snowflake platform will foster collaboration and  streamline architecture across all users and teams."
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "id": "a3011770-8623-4349-95e0-65b5051f53eb",
   "metadata": {
    "name": "cell5"
   },
   "source": [
    "## Import Packages\n",
    "Just like the Python packages we are importing, we will import the Snowpark modules that we need.<br>\n",
    "**Value**: Snowflake modules provide efficient ways to work with data and functions in Snowflake. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "bd87baf3-83da-4698-889f-b7e760acea51",
   "metadata": {
    "language": "python",
    "name": "imports"
   },
   "outputs": [],
   "source": [
    "# Import Python packages\n",
    "import pandas as pd\n",
    "import plotly.express as px\n",
    "import plotly.io as pio\n",
    "import matplotlib.pyplot as plt\n",
    "import json\n",
    "import sys\n",
    "import cachetools\n",
    "from datetime import timedelta\n",
    "import math\n",
    "\n",
    "# Import Snowflake modules\n",
    "from snowflake.snowpark import Session\n",
    "import snowflake.snowpark.functions as F\n",
    "import snowflake.snowpark.types as T\n",
    "from snowflake.snowpark import Window\n",
    "from snowflake.ml.modeling.impute import SimpleImputer\n",
    "from snowflake.ml.modeling.preprocessing import OneHotEncoder\n",
    "from snowflake.ml.modeling.pipeline import Pipeline\n",
    "from snowflake.ml.modeling.linear_model import LinearRegression\n",
    "from snowflake.ml.registry import Registry\n",
    "from snowflake.ml.modeling.metrics import mean_squared_error, mean_absolute_error, mean_absolute_percentage_error\n",
    "\n",
    "# For display purposes, suppress FutureWarnings and UserWarnings from printing below the cells\n",
    "import warnings\n",
    "for warning_category in [FutureWarning, UserWarning]:\n",
    "    warnings.filterwarnings('ignore', category=warning_category)"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "id": "906bfc65-cf90-498c-8128-170355082727",
   "metadata": {
    "name": "cell7"
   },
   "source": [
    "## Connect to Snowflake\n",
    "Our Snowflake role, **tasty_bytes_ds_role**, can access the data in the **analytics** schema of the **frostbyte_tasty_bytes_dev** database. We will use the **tasty_dsci_wh** warehouse that has been created as a dedicated compute for data science workloads.\n",
    "\n",
    "**Value:** Secure and governed. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "addad16c-ac0c-4951-8555-d8659737cd65",
   "metadata": {
    "language": "python",
    "name": "create_session"
   },
   "outputs": [],
   "source": [
    "# Get active session \n",
    "from snowflake.snowpark.context import get_active_session\n",
    "session = get_active_session()\n",
    "\n",
    "# Add a query tag to the session. This helps with debugging and performance monitoring.\n",
    "session.query_tag = {\"origin\":\"sf_sit\", \"name\":\"snowpark_101_ds\", \"version\":{\"major\":2, \"minor\":0},\"attributes\":{\"is_quickstart\":1, \"source\":\"notebook\"}}\n",
    "\n",
    "# Print the current role, warehouse, and database/schema\n",
    "print(f\"role: {session.get_current_role()} | WH: {session.get_current_warehouse()} | DB.SCHEMA: {session.get_fully_qualified_current_schema()}\")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "f3189407-20ae-4fea-8b75-5e7c398f672c",
   "metadata": {
    "name": "cell10"
   },
   "source": [
    "The context of the connection created above will have the role, warehouse, database, and schema that were specified when creating this Snowflake Notebook. \n",
    "\n",
    "If we want to change any aspects of that context, we can use Session methods as seen in the following cell."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "5070218b-7591-4609-b90b-40a4a8a777f4",
   "metadata": {
    "language": "python",
    "name": "change_context"
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "role: \"TASTY_BYTES_DS_ROLE\" | WH: \"TASTY_DSCI_WH\" | DB.SCHEMA: \"FROSTBYTE_TASTY_BYTES_DEV\".\"ANALYTICS\"\n"
     ]
    }
   ],
   "source": [
    "# Set session context\n",
    "session.use_role('tasty_bytes_ds_role') \n",
    "session.use_database('frostbyte_tasty_bytes_dev')\n",
    "session.use_schema(\"analytics\")\n",
    "session.use_warehouse(\"TASTY_DSCI_WH\")\n",
    "\n",
    "print(f\"role: {session.get_current_role()} | WH: {session.get_current_warehouse()} | DB.SCHEMA: {session.get_fully_qualified_current_schema()}\")"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "id": "2c91df24-38b1-4584-8422-c0a2a5e386da",
   "metadata": {
    "name": "cell12"
   },
   "source": [
    "## Snowpark DataFrame\n",
    "Let's create a Snowpark DataFrame containing our shift sales data from the **shift_sales_v** view in our Snowflake account using the Snowpark session.table function. A DataFrame is a data structure that contains rows and columns, similar to a SQL table.<br>\n",
    "**Value:** Familiar representation of data for Python users."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "id": "85142cce-9541-4808-979f-37bca023c74c",
   "metadata": {
    "language": "python",
    "name": "initial_df"
   },
   "outputs": [],
   "source": [
    "snowpark_df = session.table(\"frostbyte_tasty_bytes_dev.analytics.shift_sales_v\")"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "id": "416ba257-fe30-4023-b48d-f1732808c2a0",
   "metadata": {
    "name": "cell14"
   },
   "source": [
    "## Preview the Data\n",
    "With our Snowpark DataFrame defined, let’s use the .show() function to take a look at the first 10 rows. <br>\n",
    "**Value:** Instant access to data."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "id": "885fc013-037b-4b37-beb4-7f7fc869c309",
   "metadata": {
    "language": "python",
    "name": "show_df"
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------\n",
      "|\"LOCATION_ID\"  |\"CITY\"         |\"DATE\"      |\"SHIFT_SALES\"  |\"SHIFT\"  |\"MONTH\"  |\"DAY_OF_WEEK\"  |\"CITY_POPULATION\"  |\"LATITUDE\"  |\"LONGITUDE\"  |\"LOCATION_NAME\"               |\n",
      "--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------\n",
      "|2574           |New York City  |2023-05-02  |NULL           |AM       |5        |2              |8804190            |40.731148   |-74.003081   |Faicco's Italian Specialties  |\n",
      "|2574           |New York City  |2023-05-05  |NULL           |AM       |5        |5              |8804190            |40.731148   |-74.003081   |Faicco's Italian Specialties  |\n",
      "|2574           |New York City  |2023-05-01  |NULL           |AM       |5        |1              |8804190            |40.731148   |-74.003081   |Faicco's Italian Specialties  |\n",
      "|2574           |New York City  |2023-05-07  |NULL           |AM       |5        |0              |8804190            |40.731148   |-74.003081   |Faicco's Italian Specialties  |\n",
      "|2574           |New York City  |2023-05-04  |NULL           |AM       |5        |4              |8804190            |40.731148   |-74.003081   |Faicco's Italian Specialties  |\n",
      "|2574           |New York City  |2023-05-03  |NULL           |AM       |5        |3              |8804190            |40.731148   |-74.003081   |Faicco's Italian Specialties  |\n",
      "|2574           |New York City  |2023-05-06  |NULL           |AM       |5        |6              |8804190            |40.731148   |-74.003081   |Faicco's Italian Specialties  |\n",
      "|6461           |Cairo          |2023-05-02  |NULL           |PM       |5        |2              |1010166            |30.041063   |31.207879    |World Gym                     |\n",
      "|6461           |Cairo          |2023-05-05  |NULL           |PM       |5        |5              |1010166            |30.041063   |31.207879    |World Gym                     |\n",
      "|6461           |Cairo          |2023-05-01  |NULL           |PM       |5        |1              |1010166            |30.041063   |31.207879    |World Gym                     |\n",
      "--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------\n",
      "\n"
     ]
    }
   ],
   "source": [
    "snowpark_df.show()"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "id": "7e44f7be-b5ca-43de-895b-d7e9d8557f5e",
   "metadata": {
    "name": "cell16"
   },
   "source": [
    "## Select, Filter, Sort\n",
    "Notice the Null values for \"shift_sales\". Let's look at a single location.\n",
    "\n",
    "To do this, we will make another Snowpark DataFrame, location_df, from the above DataFrame and we will:\n",
    "1. Select columns\n",
    "2. Filter to a single location ID\n",
    "3. Sort by date\n",
    "\n",
    "**Value**: Efficient transformation pipelines using Python syntax and chained logic."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "id": "b9c64680-5815-416e-8493-2c49be720e30",
   "metadata": {
    "language": "python",
    "name": "demo_df_operations"
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "--------------------------------------------------------------------\n",
      "|\"DATE\"      |\"SHIFT\"  |\"SHIFT_SALES\"  |\"LOCATION_ID\"  |\"CITY\"     |\n",
      "--------------------------------------------------------------------\n",
      "|2023-05-07  |PM       |NULL           |1135           |Vancouver  |\n",
      "|2023-05-07  |AM       |NULL           |1135           |Vancouver  |\n",
      "|2023-05-06  |PM       |NULL           |1135           |Vancouver  |\n",
      "|2023-05-06  |AM       |NULL           |1135           |Vancouver  |\n",
      "|2023-05-05  |PM       |NULL           |1135           |Vancouver  |\n",
      "|2023-05-05  |AM       |NULL           |1135           |Vancouver  |\n",
      "|2023-05-04  |PM       |NULL           |1135           |Vancouver  |\n",
      "|2023-05-04  |AM       |NULL           |1135           |Vancouver  |\n",
      "|2023-05-03  |PM       |NULL           |1135           |Vancouver  |\n",
      "|2023-05-03  |AM       |NULL           |1135           |Vancouver  |\n",
      "|2023-05-02  |PM       |NULL           |1135           |Vancouver  |\n",
      "|2023-05-02  |AM       |NULL           |1135           |Vancouver  |\n",
      "|2023-05-01  |PM       |NULL           |1135           |Vancouver  |\n",
      "|2023-05-01  |AM       |NULL           |1135           |Vancouver  |\n",
      "|2023-02-25  |AM       |304.5          |1135           |Vancouver  |\n",
      "|2023-02-10  |PM       |492.75         |1135           |Vancouver  |\n",
      "|2023-01-23  |PM       |199.91         |1135           |Vancouver  |\n",
      "|2023-01-22  |PM       |733.5          |1135           |Vancouver  |\n",
      "|2023-01-05  |AM       |238.5          |1135           |Vancouver  |\n",
      "|2022-11-24  |AM       |266.32         |1135           |Vancouver  |\n",
      "--------------------------------------------------------------------\n",
      "\n"
     ]
    }
   ],
   "source": [
    "# Select\n",
    "location_df = snowpark_df.select(\"date\", \"shift\", \"shift_sales\", \"location_id\", \"city\")\n",
    "\n",
    "# Filter\n",
    "location_df = location_df.filter(F.col(\"location_id\") == 1135)\n",
    "\n",
    "# Sort\n",
    "location_df = location_df.order_by([\"date\", \"shift\"], ascending=[0, 0])\n",
    "\n",
    "# Display\n",
    "location_df.show(n=20)"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "id": "4125a8d3-316c-4c98-8832-d69f138d2929",
   "metadata": {
    "name": "cell18"
   },
   "source": [
    "We can see that shift sales are populated 8 days prior to the latest date in the data. The **missing values** represent future dates that do not have shift sales yet.\n",
    "\n",
    "\n",
    "## Snowpark works in two main ways:\n",
    "1. Snowpark code translated and executed as SQL on Snowflake\n",
    "2. Python functions deployed in a secure sandbox in Snowflake "
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "id": "b6f25f30-4b94-48b7-b651-dc1204374eb8",
   "metadata": {
    "name": "cell19"
   },
   "source": [
    "## Explain the Query\n",
    "Let's look at what was executed in Snowflake to create our location_df DataFrame.\n",
    "\n",
    "The translated SQL query can be seen in the Snowsight interface under *Activity* in the *Query History* or directly in our notebook by using the explain() function.\n",
    "\n",
    "**Value:** Transparent execution and compute usage."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "id": "76d99dbd-ca6d-4bd3-869f-ca3af99be331",
   "metadata": {
    "language": "python",
    "name": "query_explain_plan"
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "---------DATAFRAME EXECUTION PLAN----------\n",
      "Query List:\n",
      "1.\n",
      "SELECT \"DATE\", \"SHIFT\", \"SHIFT_SALES\", \"LOCATION_ID\", \"CITY\" FROM frostbyte_tasty_bytes_dev.analytics.shift_sales_v WHERE (\"LOCATION_ID\" = 1135 :: INT) ORDER BY \"DATE\" DESC NULLS LAST, \"SHIFT\" DESC NULLS LAST\n",
      "Logical Execution Plan:\n",
      "GlobalStats:\n",
      "    partitionsTotal=2\n",
      "    partitionsAssigned=2\n",
      "    bytesAssigned=27725312\n",
      "Operations:\n",
      "1:0     ->Result  SHIFT_SALES.DATE, SHIFT_SALES.SHIFT, SHIFT_SALES.SHIFT_SALES, SHIFT_SALES.LOCATION_ID, SHIFT_SALES.CITY  \n",
      "1:1          ->Sort  SHIFT_SALES.DATE DESC NULLS LAST, SHIFT_SALES.SHIFT DESC NULLS LAST  \n",
      "1:2               ->Filter  SHIFT_SALES.LOCATION_ID = 1135  \n",
      "1:3                    ->TableScan  FROSTBYTE_TASTY_BYTES_DEV.HARMONIZED.SHIFT_SALES  LOCATION_ID, CITY, DATE, SHIFT_SALES, SHIFT  {partitionsTotal=2, partitionsAssigned=2, bytesAssigned=27725312}\n",
      "\n",
      "--------------------------------------------\n"
     ]
    }
   ],
   "source": [
    "location_df.explain()"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "id": "7d9615a9-b694-4fd8-8a71-9eb3776f6b9c",
   "metadata": {
    "name": "cell21"
   },
   "source": [
    "## Compare DataFrame Size\n",
    "Let's bring a sample of our Snowflake dataset to our Python environment in a pandas DataFrame using the to_pandas() function. We will compare how much memory is used for the pandas DataFrame compared to the Snowpark DataFrame. As we will see, no memory is used for the Snowpark DataFrame in our Python environment. All data in the Snowpark DataFrame remains on Snowflake.<br>\n",
    "\n",
    "**Value:** No copies or movement of data when working with Snowpark DataFrames."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "id": "3c061225-8ce2-4a01-97c0-9263c7f46bda",
   "metadata": {
    "language": "python",
    "name": "compare_df_sizes"
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Snowpark DataFrame Size (snowpark_df): 0.00 MB\n",
      "Pandas DataFrame Size (pandas_df): 2.57 MB\n"
     ]
    }
   ],
   "source": [
    "# Bring 10,000 rows from Snowflake to pandas\n",
    "pandas_df = snowpark_df.limit(10000).to_pandas()\n",
    "\n",
    "# Get Snowpark DataFrame size\n",
    "snowpark_size = sys.getsizeof(snowpark_df) / (1024*1024)\n",
    "print(f\"Snowpark DataFrame Size (snowpark_df): {snowpark_size:.2f} MB\")\n",
    "\n",
    "# Get pandas DataFrame size\n",
    "pandas_size = sys.getsizeof(pandas_df) / (1024*1024)\n",
    "print(f\"Pandas DataFrame Size (pandas_df): {pandas_size:.2f} MB\")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "869208e9-42fa-441d-9529-cd1fa1428a20",
   "metadata": {
    "name": "DATA_EXPLORATION"
   },
   "source": [
    "--------------\n",
    "# ❄️ DATA EXPLORATION ❄️ \n",
    "--------------"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "id": "ebc46297-2025-4d24-98a2-4e07bbe65235",
   "metadata": {
    "name": "cell24"
   },
   "source": [
    "Here, we will use Snowpark to explore our data. A common pattern for exploration is to use Snowpark to manipulate our data and then bring an aggregate table to our Python environment for visualization. <br>\n",
    "\n",
    "**Value:** \n",
    "   - Native Snowflake performance and scale for aggregating large datasets. \n",
    "   - Easy transfer of aggregate data to the client-side environment for visualization.\n",
    "   \n",
    "<br>As we explore our data, we will highlight what is being done in Snowflake and what we are transferring to our client-side environment (Python notebook environment) for visualization."
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "id": "bb755197-debd-4fe0-8ad7-96e030a19fac",
   "metadata": {
    "name": "cell25"
   },
   "source": [
    "## How many rows are in our data?\n",
    "This will give us an idea of how we might need to approach working with this data. Do we have enough data to build a meaningful model? What compute might be required? Will we need to sample the data?<br>\n",
    "**What's happening where?:** Rows counted in Snowflake. No data transfer."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "id": "271c1817-e22f-4c5e-bc81-217ace15fbba",
   "metadata": {
    "language": "python",
    "name": "df_row_count"
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "1938202"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "snowpark_df.count()"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "id": "efbcbb7a-db54-4998-9acd-38be33afcc9b",
   "metadata": {
    "name": "cell27"
   },
   "source": [
    "## Let's calculate some descriptive statistics.\n",
    "We use the Snowpark describe() function to calculate summary statistics and then bring the aggregate results into a pandas DataFrame to visualize in a formatted table.<br>\n",
    "**What's happening where?:** Summary statistics calculated in Snowflake. Transfer aggregate summary statistics for client-side visualization."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "id": "a9b0788a-b4e2-42af-bccf-c293558528a8",
   "metadata": {
    "language": "python",
    "name": "df_describe"
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>SUMMARY</th>\n",
       "      <th>LOCATION_ID</th>\n",
       "      <th>CITY</th>\n",
       "      <th>SHIFT_SALES</th>\n",
       "      <th>SHIFT</th>\n",
       "      <th>MONTH</th>\n",
       "      <th>DAY_OF_WEEK</th>\n",
       "      <th>CITY_POPULATION</th>\n",
       "      <th>LATITUDE</th>\n",
       "      <th>LONGITUDE</th>\n",
       "      <th>LOCATION_NAME</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>count</td>\n",
       "      <td>1.938202e+06</td>\n",
       "      <td>1938202</td>\n",
       "      <td>1.755096e+06</td>\n",
       "      <td>1938202</td>\n",
       "      <td>1.938202e+06</td>\n",
       "      <td>1.938202e+06</td>\n",
       "      <td>1.938202e+06</td>\n",
       "      <td>1.938202e+06</td>\n",
       "      <td>1.938202e+06</td>\n",
       "      <td>1938202</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>stddev</td>\n",
       "      <td>4.234979e+03</td>\n",
       "      <td>None</td>\n",
       "      <td>4.381358e+02</td>\n",
       "      <td>None</td>\n",
       "      <td>3.223800e+00</td>\n",
       "      <td>1.996656e+00</td>\n",
       "      <td>4.566943e+06</td>\n",
       "      <td>2.839821e+01</td>\n",
       "      <td>7.830981e+01</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>max</td>\n",
       "      <td>1.551700e+04</td>\n",
       "      <td>Warsaw</td>\n",
       "      <td>4.785360e+03</td>\n",
       "      <td>PM</td>\n",
       "      <td>1.200000e+01</td>\n",
       "      <td>6.000000e+00</td>\n",
       "      <td>1.634983e+07</td>\n",
       "      <td>5.948668e+01</td>\n",
       "      <td>1.513234e+02</td>\n",
       "      <td>コタン コミチ</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>mean</td>\n",
       "      <td>8.119924e+03</td>\n",
       "      <td>None</td>\n",
       "      <td>6.092432e+02</td>\n",
       "      <td>None</td>\n",
       "      <td>6.169526e+00</td>\n",
       "      <td>3.000784e+00</td>\n",
       "      <td>4.325339e+06</td>\n",
       "      <td>3.168076e+01</td>\n",
       "      <td>-3.415415e+00</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>min</td>\n",
       "      <td>1.001000e+03</td>\n",
       "      <td>Barcelona</td>\n",
       "      <td>7.600000e-01</td>\n",
       "      <td>AM</td>\n",
       "      <td>1.000000e+00</td>\n",
       "      <td>0.000000e+00</td>\n",
       "      <td>1.056610e+05</td>\n",
       "      <td>-3.832745e+01</td>\n",
       "      <td>-1.232431e+02</td>\n",
       "      <td>5th Avenue</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  SUMMARY   LOCATION_ID       CITY   SHIFT_SALES    SHIFT         MONTH  \\\n",
       "0   count  1.938202e+06    1938202  1.755096e+06  1938202  1.938202e+06   \n",
       "1  stddev  4.234979e+03       None  4.381358e+02     None  3.223800e+00   \n",
       "2     max  1.551700e+04     Warsaw  4.785360e+03       PM  1.200000e+01   \n",
       "3    mean  8.119924e+03       None  6.092432e+02     None  6.169526e+00   \n",
       "4     min  1.001000e+03  Barcelona  7.600000e-01       AM  1.000000e+00   \n",
       "\n",
       "    DAY_OF_WEEK  CITY_POPULATION      LATITUDE     LONGITUDE LOCATION_NAME  \n",
       "0  1.938202e+06     1.938202e+06  1.938202e+06  1.938202e+06       1938202  \n",
       "1  1.996656e+00     4.566943e+06  2.839821e+01  7.830981e+01          None  \n",
       "2  6.000000e+00     1.634983e+07  5.948668e+01  1.513234e+02       コタン コミチ  \n",
       "3  3.000784e+00     4.325339e+06  3.168076e+01 -3.415415e+00          None  \n",
       "4  0.000000e+00     1.056610e+05 -3.832745e+01 -1.232431e+02    5th Avenue  "
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "snowpark_df.describe().to_pandas()"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "id": "6fb40d72-d323-4dbd-8789-a3fa8d10be29",
   "metadata": {
    "collapsed": false,
    "name": "cell29"
   },
   "source": [
    "## What are the numeric columns?\n",
    "We want to understand the data types in our data and how we might need to handle them in preparation for modeling. For numeric columns, this could include normalizing the data to the same scale or applying a transformation to change the distribution.\n",
    "\n",
    "**What's happening where?:** The Snowflake table schema is used to get metadata information about the data. No data transfer."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "id": "c26f23a9-938e-43dc-aefd-ee65b6ea69a9",
   "metadata": {
    "language": "python",
    "name": "get_numeric_cols"
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "['LOCATION_ID',\n",
       " 'SHIFT_SALES',\n",
       " 'MONTH',\n",
       " 'DAY_OF_WEEK',\n",
       " 'CITY_POPULATION',\n",
       " 'LATITUDE',\n",
       " 'LONGITUDE']"
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Define Snowflake numeric types\n",
    "numeric_types = [T.DecimalType, T.DoubleType, T.FloatType, T.IntegerType, T.LongType]\n",
    "\n",
    "# Get numeric columns\n",
    "numeric_columns = [col.name for col in snowpark_df.schema.fields if type(col.datatype) in numeric_types]\n",
    "numeric_columns"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "id": "817a696e-4d18-487c-88eb-1a4d1f03fbc3",
   "metadata": {
    "name": "cell31"
   },
   "source": [
    "## What are the categorical columns?\n",
    "Our model requires all features to be numeric. We want to identify columns that we will need to transform to a numeric representation if we would like to use them as features in our model.<br>\n",
    "**What's happening where?:** The Snowflake table schema is used to get metadata information about the data. No data transfer."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "id": "5e92adf6-5aa9-452f-b4da-5a3a34348ff5",
   "metadata": {
    "language": "python",
    "name": "get_categorical_cols"
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "['CITY', 'SHIFT', 'LOCATION_NAME']"
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Define Snowflake categorical types\n",
    "categorical_types = [T.StringType]\n",
    "\n",
    "# Get categorical columns\n",
    "categorical_columns = [col.name for col in snowpark_df.schema.fields if type(col.datatype) in categorical_types]\n",
    "categorical_columns"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "id": "4ffade58-610e-4d55-90f4-3eaf251c365e",
   "metadata": {
    "name": "cell33"
   },
   "source": [
    "## What are the average shift sales (USD) by city?\n",
    "Here, we are trying to understand what a \"normal\" shift sale looks like. What is the span of averages across cities? Are there any outlier cities that should be removed from our training data? Is there anything unexpected in the order of cities sorted by their average shift sales?<br>\n",
    "**What's happening where?:** Average sales by city calculated in Snowflake. Transfer city averages for client-side visualization."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "e8f94a13-53a8-40d6-a1e8-d35d21b56c0e",
   "metadata": {
    "collapsed": false,
    "language": "python",
    "name": "bar_chart_by_city"
   },
   "outputs": [],
   "source": [
    "# Group by city and average shift sales\n",
    "analysis_df = snowpark_df.group_by(\"city\").agg(F.mean(\"shift_sales\").alias(\"avg_shift_sales\"))\n",
    "\n",
    "# Sort by average shift sales\n",
    "analysis_df = analysis_df.sort(\"avg_shift_sales\", ascending=True)\n",
    "\n",
    "# Pull to pandas and plot\n",
    "analysis_df.to_pandas().plot.barh(x=\"CITY\", y=\"AVG_SHIFT_SALES\")"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "id": "73781b02-3671-434e-a453-2078cb88560a",
   "metadata": {
    "name": "cell35"
   },
   "source": [
    "## Looking at Vancouver, how many locations are there?\n",
    "We are going to use Vancouver, Canada as our test city when predicting the best location for future shifts. Let's get to know locations and shift sales in that city. First, we will see how many location options there are in Vancouver for a food truck to park.<br>\n",
    "**What's happening where?:** Data filtered, averages calculated by location, and locations counted in Snowflake. No data transfer."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "id": "885d58f9-8ff6-40fa-bb65-b8251f616a80",
   "metadata": {
    "language": "python",
    "name": "location_count"
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Vancouver location count: 435\n"
     ]
    }
   ],
   "source": [
    "# Filter to Vancouver\n",
    "analysis_df = snowpark_df.filter(F.col(\"city\") == \"Vancouver\")\n",
    "\n",
    "# Group by location and average shift sales\n",
    "analysis_df = analysis_df.group_by(\"location_id\").agg(F.mean(\"shift_sales\").alias(\"avg_shift_sales\"))\n",
    "\n",
    "# Get the location count\n",
    "print(\"Vancouver location count:\", analysis_df.count())"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "id": "6089108f-4c81-49b6-a598-49769d168574",
   "metadata": {
    "name": "cell37"
   },
   "source": [
    "## What is the distribution of average shift sales (USD) by location in Vancouver?\n",
    "The x-axis represents average shift sales (USD) and the y-axis represents the count of locations with that average. The highest bars indicate the most common average shift sales. We will keep this in mind when evaluating predictions.<br>\n",
    "**What's happening where?:** Data filtered and averages calculated by locations in Snowflake. Transfer location averages for client-side visualization.<br>\n",
    "**Value:** Aggregate where the data is stored using native Snowflake performance and scale. Easy transfer of aggregate data to client-side applications."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "id": "0a736c4f-ba74-43d9-80fe-aaa9a989a488",
   "metadata": {
    "collapsed": false,
    "language": "python",
    "name": "histogram_shift_sales"
   },
   "outputs": [
    {
     "data": {
      "image/png": "iVBORw0KGgoAAAANSUhEUgAAAiIAAAGxCAYAAABfrt1aAAAAOXRFWHRTb2Z0d2FyZQBNYXRwbG90bGliIHZlcnNpb24zLjguNCwgaHR0cHM6Ly9tYXRwbG90bGliLm9yZy8fJSN1AAAACXBIWXMAAA9hAAAPYQGoP6dpAAAuGElEQVR4nO3de3xU9Z3/8feEhEmCCSpIJtMEiBgVjZetUK6VbCVB5LqpigZvKxUsYItYUWR9OFgbkGqKNqsWCxFrs7hVVB4ql7hiwM0DDSheWEXdjYhAmqqRBAKTMfn+/vDHyDAJzGQmfHN5PR+PeSTzPd/zne/5ZJjz5syZOQ5jjBEAAIAFMbYnAAAAui6CCAAAsIYgAgAArCGIAAAAawgiAADAGoIIAACwhiACAACsIYgAAABrCCIAAMAaggjQBh599FE5HA5lZWX52x555BE5HA6tW7euxfWefPJJORwOrV692t/W1NSkZ555RmPGjFGfPn0UFxenU089VUOHDtVDDz2kr776Kuz5rV+/Xrm5uXK73XI6nXK73crOztbixYsD+vXv31/jx49vdoytW7fK4XDoqaee8rc99dRTcjgc2rp1q7/N4/HI4XA0eysqKlL//v1bXH707ejHOR5jjFatWqWf/vSn6tOnj+Lj45WWlqYxY8boz3/+c4vr/fjHP5bD4dBDDz3U7PLmtu1Yn3/++XG3wePxRDxPoLOJtT0BoDNasWKFJGnHjh166623NGTIEF133XW66667tGLFCl1++eXNrldcXKwzzjhDEyZMkCQdOnRIkyZN0muvvaYpU6bo0UcfldvtVm1trcrLy/X73/9eL730kjZv3hzy3J544gn98pe/1M9//nMVFRXp9NNP1+7du1VeXq7nnntOd999d+QFaMa6devUs2fPgLaMjAyNGDFCXq/X3/bnP/9Zy5cvD+o/YMCAkB5n/vz5evDBB3XLLbfozjvvVFJSknbt2qXXX39dL730kn7xi18ErbN9+3a9++67kqTly5frN7/5TWs20e+2225Tfn5+UHtaWlpE8wQ6JQMgqioqKowkM27cOCPJ3HLLLf5lV199tenevbv56quvgtb76KOPjCRzxx13+NumT59uJJmSkpJmH+vgwYNm2bJlYc2vb9++5tJLL212WWNjY8D9fv36mXHjxjXb98h2FhcX+9uKi4uNJFNRUeFvu++++4wk849//COk+YXb/2j19fXG6XSaG264odnlx27fEbNmzQr4m/33f/93UJ/mtu1YlZWVRpL5/e9/3ybzBDoj3poBomz58uWSpMWLF2v48OFatWqV6uvrJUnTpk1TQ0ODSkpKgtYrLi6WJN18882SpH379mnFihUaN26crr322mYfKzExUbfccktY8/v666+Vmpra7LKYmI79knDw4EF5vd6wtu/w4cMqKSnRJZdcoj/84Q+Sfjii1Z7mCXRWPNuBKDp06JD+4z/+Q4MHD1ZWVpZuvvlm1dXV6W9/+5skafTo0erXr1/Qjq6xsVF/+ctfNHToUJ133nmSpI0bN+q7777TxIkTozrHYcOG6fnnn5fH49F7772nxsbG4/Y3xui7774Lup1ovWM1NjZGtH4oevfurbPOOkuPPfaYCgsL9fHHH8uc4ALjq1evVk1NjW6++WZlZmZq5MiRevbZZ3XgwIFWz6OpqanZmkUyT6CzIogAUfTcc89p//79mjZtmiRpypQpOuWUU/xHSWJiYnTTTTcFnJMgSWvXrtW+ffv860nS7t27JUn9+vULepyWdnCheOKJJ3Tuuedq4cKFuvjii5WUlKTRo0fr3//93+Xz+YL6v/rqq4qLiwu6DR06NKzHdblcAes3t13RUFJSotNOO0133HGHBg4cqJ49e2rChAn6y1/+0uzOfvny5YqPj/ef0zFt2jQdOHBA//mf/9nqOdx1113N1uzNN99s9TyBzoogAkTR8uXLlZCQoGuuuUaSdMopp+iqq67S5s2b9emnn0qS/vVf/1UxMTEBR0WKi4vVo0cPTZky5YSPsX379qAdXDifnBkwYIDee+89lZWVaeHChRo9erQqKio0e/ZsDRs2TIcPHw7oP3LkSFVUVATdnn766ZAfU5Jee+21gPVfffXVsNYP1eDBg/XZZ59p3bp1uueeezRs2DD913/9l2644QZNnDgxYCdfWVmpjRs3Ki8vT6eeeqok6aqrrlJSUlJEb8/8+te/brZmF198cavmCXRmfGoGiJLPPvtMmzZt0s9//nMZY/Ttt99Kkq688koVFxdrxYoVWrRokfr166fLLrtMJSUleuihh1RXV6eXX35Z+fn5SkpK8o/Xt29fSdKuXbsCHuecc85RRUWFJGnZsmV68sknw55rTEyMLr30Ul166aWSvj9nYdq0aXr22We1YsUKzZw509+3Z8+eGjRoUNiPcayLLrpIvXv3jnicUMTFxWnMmDEaM2aMpO/Pi7nyyiv18ssva+3atbriiiskfX8uiDFGV155pf/vJUkTJ07UX//6V3388cc699xzw378tLS0kGoW6jyBzowjIkCUHNmpPffcczrttNP8t3HjxkmSVq5c6T8vYtq0afrmm2/00ksv6ZlnnlFDQ0PA2zKSlJ2drdjYWK1ZsyagPSEhQYMGDdKgQYPkdrujMvcePXpo/vz5kqQPP/wwKmO2J7169dKcOXMk/bB9TU1N/u8mycvLC/ib/fWvf5XU9iethjJPoLPjiAgQBY2NjVq5cqUGDBjQ7JdRvfzyy3r44Ye1du1ajR8/XpMnT1avXr20YsUK7du3T2effbZGjhwZsE5qaqpuvvlmLVu2TKtWrfK/3ROpffv2NftpjY8++kiSohZubPD5fKqtrVWvXr2Clh27fevXr9eXX36pWbNm6corrwzqP3v2bD399NMqKChQbGx0XyrDmSfQ2RFEgChYu3at9u7dqwcffFDZ2dlBy7OyslRUVKTly5dr/Pjxcjqdmjp1qv74xz/KGBP0jaZHLF26VJWVlZo6darWrFmjSZMmye12q76+Xh9//LFWrVql+Ph4xcXFhTzX888/X5dddpnGjh2rAQMG6PDhw3rrrbf08MMPKyUlJejITEeyf/9+9e/fX1dddZVGjx6t9PR0HThwQG+88YYeeeQRDRw4UHl5eZK+P58nNjZW99xzT7M7/RkzZuhXv/qVXnnlFU2aNMnf/vrrr+vzzz8P6n/02yhffPGFtmzZEtTnjDPO0IABA8KaJ9DpWfr+EqBTmTx5sunevbuprq5usc8111xjYmNjTVVVlTHGmPfee89IMt26dTN79+5tcb3Gxkbz9NNPm5ycHNO7d28TGxtrevbsaX7yk5+Ye++913z55ZdhzfVPf/qTycvLM2eeeaZJTEw03bt3NwMGDDC33nqr2b17d0DfjvaFZl6v1zz00ENm7Nixpm/fvsbpdJr4+HgzcOBAM2/ePPP1118bY4z5xz/+Ybp3724mT57c4lg1NTUmISHBTJgwIWDbWrpVVlb6v9CspdvUqVPDmifQFTiM4dRsAABgByerAgAAazhHBOgkmpqa1NTUdNw+0T7p8mRqbGw87ndrOBwOdevW7STOCEA0cEQE6CRuvvnmZr/N8+hbR3bZZZcdd9tCvTovgPaFc0SATuLzzz8/4TesRuOLyWzZuXOn6urqWlzudDp1wQUXnMQZAYgGgggAALCGt2YAAIA17e7MtaamJu3du1dJSUlyOBy2pwMAAEJgjFFdXZ3cbrdiYkI/ztHugsjevXuVnp5uexoAAKAVdu/erbS0tJD7t7sgcuTqo7t371ZycrKk76/LsGHDBuXm5nb4M/9toH6RoX6Ro4aRoX6RoX6RCbV+tbW1Sk9PD7iKeCjaXRA58nZMcnJyQBBJTExUcnIyT6JWoH6RoX6Ro4aRoX6RoX6RCbd+4Z5WwcmqAADAGoIIAACwhiACAACsIYgAAABrwg4ie/bs0XXXXadevXopMTFRF198sbZt2+ZfboyRx+OR2+1WQkKCsrOztWPHjqhOGgAAdA5hBZGamhqNGDFCcXFxWrt2rf7nf/5HDz/8sE499VR/nyVLlqiwsFBFRUWqqKiQy+VSTk7Oca8RAQAAuqawPr774IMPKj09XcXFxf62/v37+383xmjp0qVasGCB8vLyJEkrV65USkqKSkpKNGPGjOjMGgAAdAphBZE1a9ZozJgxuuqqq1RWVqYf/ehHmjlzpm655RZJUmVlpaqqqpSbm+tfx+l0atSoUSovL282iHi9Xnm9Xv/92tpaSd9/btnn8/l/P/onwkP9IkP9IkcNI0P9IkP9IhNq/Vpb37CuvhsfHy9Jmjt3rq666iq9/fbbmjNnjv70pz/phhtuUHl5uUaMGKE9e/bI7Xb715s+fbp27dql9evXB43p8Xi0cOHCoPaSkhIlJia2ZpsAAMBJVl9fr/z8fO3fv9//haShCOuISFNTkwYNGqSCggJJ0j/90z9px44devzxx3XDDTf4+x37rWrGmBa/aW3+/PmaO3eu//6Rr4jNzc0N+GbV0tJS5eTk8K14rUD9IkP9IkcNI0P9IkP9IhNq/Y68oxGusIJIamqqzjvvvIC2gQMH6vnnn5ckuVwuSVJVVZVSU1P9faqrq5WSktLsmE6nU06nM6g9Li4uaIOba0PoqF9kqF/kqGFkqF9kqF9kTlS/1tY2rE/NjBgxQjt37gxo++STT9SvXz9JUkZGhlwul0pLS/3LGxoaVFZWpuHDh7dqggAAoPMK64jI7bffruHDh6ugoEBXX3213n77bS1btkzLli2T9P1bMnPmzFFBQYEyMzOVmZmpgoICJSYmKj8/v002AAAAdFxhBZHBgwfrhRde0Pz583X//fcrIyNDS5cu1dSpU/195s2bp0OHDmnmzJmqqanRkCFDtGHDhrAvCwwAADq/sIKIJI0fP17jx49vcbnD4ZDH45HH44lkXgAs63/3K1Eby9nNaMlPpCzPeu38XcuvHwC6Hq41AwAArCGIAAAAawgiAADAGoIIAACwhiACAACsIYgAAABrCCIAAMAagggAALCGIAIAAKwhiAAAAGsIIgAAwJqwrzUDAO1VNK+Pc6zPF49rs7GBrowjIgAAwBqCCAAAsIYgAgAArCGIAAAAawgiAADAGj41A+CkastPtgDoeDgiAgAArCGIAAAAawgiAADAGoIIAACwhiACAACsIYgAAABrCCIAAMAagggAALCGIAIAAKwhiAAAAGsIIgAAwBqCCAAAsIYgAgAArCGIAAAAawgiAADAGoIIAACwhiACAACsIYgAAABrCCIAAMAagggAALCGIAIAAKwhiAAAAGsIIgAAwBqCCAAAsIYgAgAArCGIAAAAawgiAADAGoIIAACwhiACAACsIYgAAABrCCIAAMCasIKIx+ORw+EIuLlcLv9yY4w8Ho/cbrcSEhKUnZ2tHTt2RH3SAACgcwj7iMj555+vffv2+W8ffPCBf9mSJUtUWFiooqIiVVRUyOVyKScnR3V1dVGdNAAA6BzCDiKxsbFyuVz+2xlnnCHp+6MhS5cu1YIFC5SXl6esrCytXLlS9fX1KikpifrEAQBAxxcb7gqffvqp3G63nE6nhgwZooKCAp155pmqrKxUVVWVcnNz/X2dTqdGjRql8vJyzZgxo9nxvF6vvF6v/35tba0kyefzyefz+X8/+ifCQ/0i01Xr5+xmojdWjAn42RHZ/Pt31edgtFC/yIRav9bW12GMCfmVYe3ataqvr9fZZ5+tv//973rggQf08ccfa8eOHdq5c6dGjBihPXv2yO12+9eZPn26du3apfXr1zc7psfj0cKFC4PaS0pKlJiY2IpNAgAAJ1t9fb3y8/O1f/9+JScnh7xeWEHkWAcPHtSAAQM0b948DR06VCNGjNDevXuVmprq73PLLbdo9+7dWrduXbNjNHdEJD09XV999ZV/Q3w+n0pLS5WTk6O4uLjWTrfLon6R6ar1y/I0/5+H1nDGGP12UJPu3Rojb5MjauOeTB96xlh77K76HIwW6heZUOtXW1ur3r17hx1Ewn5r5mg9evTQBRdcoE8//VSTJ0+WJFVVVQUEkerqaqWkpLQ4htPplNPpDGqPi4sL2uDm2hA66heZrlY/b2P0A4O3ydEm454M7eFv39Weg9FG/SJzovq1trYRfY+I1+vVRx99pNTUVGVkZMjlcqm0tNS/vKGhQWVlZRo+fHgkDwMAADqpsI6I/OY3v9GECRPUt29fVVdX64EHHlBtba1uvPFGORwOzZkzRwUFBcrMzFRmZqYKCgqUmJio/Pz8tpo/AADowMIKIl9++aWuvfZaffXVVzrjjDM0dOhQbdmyRf369ZMkzZs3T4cOHdLMmTNVU1OjIUOGaMOGDUpKSmqTyQMAgI4trCCyatWq4y53OBzyeDzyeDyRzAkAAHQRXGsGAABYQxABAADWEEQAAIA1BBEAAGANQQQAAFhDEAEAANYQRAAAgDUEEQAAYA1BBAAAWEMQAQAA1hBEAACANQQRAABgDUEEAABYQxABAADWEEQAAIA1BBEAAGANQQQAAFhDEAEAANYQRAAAgDUEEQAAYA1BBAAAWEMQAQAA1hBEAACANQQRAABgDUEEAABYQxABAADWEEQAAIA1BBEAAGANQQQAAFhDEAEAANYQRAAAgDUEEQAAYA1BBAAAWEMQAQAA1hBEAACANQQRAABgDUEEAABYQxABAADWEEQAAIA1BBEAAGANQQQAAFgTa3sCANAR9L/7lTYZ9/PF49pkXKCj4IgIAACwhiACAACsIYgAAABrCCIAAMAagggAALCGIAIAAKwhiAAAAGsiCiKLFi2Sw+HQnDlz/G3GGHk8HrndbiUkJCg7O1s7duyIdJ4AAKATanUQqaio0LJly3ThhRcGtC9ZskSFhYUqKipSRUWFXC6XcnJyVFdXF/FkAQBA59KqIHLgwAFNnTpVTz75pE477TR/uzFGS5cu1YIFC5SXl6esrCytXLlS9fX1KikpidqkAQBA59Cqr3ifNWuWxo0bp9GjR+uBBx7wt1dWVqqqqkq5ubn+NqfTqVGjRqm8vFwzZswIGsvr9crr9frv19bWSpJ8Pp98Pp//96N/IjzULzJdtX7ObiZ6Y8WYgJ/4QSjPq676HIwW6heZUOvX2vqGHURWrVqld955RxUVFUHLqqqqJEkpKSkB7SkpKdq1a1ez4y1atEgLFy4Mat+wYYMSExMD2kpLS8OdLo5C/SLT1eq35CfRH/O3g5qiP2gH9+qrr4bct6s9B6ON+kXmRPWrr69v1bhhBZHdu3fr17/+tTZs2KD4+PgW+zkcjoD7xpigtiPmz5+vuXPn+u/X1tYqPT1dubm5Sk5OlvR9yiotLVVOTo7i4uLCmTJE/SLVVeuX5VkftbGcMUa/HdSke7fGyNvU/GtBV/WhZ8wJ+3TV52C0UL/IhFq/I+9ohCusILJt2zZVV1frkksu8bc1NjZq06ZNKioq0s6dOyV9f2QkNTXV36e6ujroKMkRTqdTTqczqD0uLi5og5trQ+ioX2S6Wv28jdEPDN4mR5uM25GF85zqas/BaKN+kTlR/Vpb27BOVr3sssv0wQcfaPv27f7boEGDNHXqVG3fvl1nnnmmXC5XwOGbhoYGlZWVafjw4a2aIAAA6LzCOiKSlJSkrKysgLYePXqoV69e/vY5c+aooKBAmZmZyszMVEFBgRITE5Wfnx+9WQMAgE6hVZ+aOZ558+bp0KFDmjlzpmpqajRkyBBt2LBBSUlJ0X4oAADQwUUcRN54442A+w6HQx6PRx6PJ9KhAQBAJ8e1ZgAAgDUEEQAAYA1BBAAAWEMQAQAA1hBEAACANVH/+C6Ak6f/3a/YngIiFMrf0NnNaMlPvv/a/XC+mfbzxeMimRpwUnBEBAAAWEMQAQAA1hBEAACANQQRAABgDUEEAABYQxABAADWEEQAAIA1BBEAAGANQQQAAFhDEAEAANYQRAAAgDUEEQAAYA1BBAAAWEMQAQAA1hBEAACANQQRAABgDUEEAABYQxABAADWEEQAAIA1BBEAAGANQQQAAFhDEAEAANYQRAAAgDUEEQAAYA1BBAAAWEMQAQAA1hBEAACANQQRAABgDUEEAABYQxABAADWEEQAAIA1BBEAAGANQQQAAFhDEAEAANYQRAAAgDUEEQAAYA1BBAAAWEMQAQAA1hBEAACANQQRAABgDUEEAABYQxABAADWhBVEHn/8cV144YVKTk5WcnKyhg0bprVr1/qXG2Pk8XjkdruVkJCg7Oxs7dixI+qTBgAAnUNYQSQtLU2LFy/W1q1btXXrVv3sZz/TpEmT/GFjyZIlKiwsVFFRkSoqKuRyuZSTk6O6uro2mTwAAOjYwgoiEyZM0BVXXKGzzz5bZ599tn73u9/plFNO0ZYtW2SM0dKlS7VgwQLl5eUpKytLK1euVH19vUpKStpq/gAAoAOLbe2KjY2N+tvf/qaDBw9q2LBhqqysVFVVlXJzc/19nE6nRo0apfLycs2YMaPZcbxer7xer/9+bW2tJMnn88nn8/l/P/onwkP9ItOe6+fsZmxPISTOGBPwE+Fpbf3a43PWhvb8b7gjCLV+ra2vwxgT1jP7gw8+0LBhw3T48GGdcsopKikp0RVXXKHy8nKNGDFCe/bskdvt9vefPn26du3apfXr1zc7nsfj0cKFC4PaS0pKlJiYGObmAAAAG+rr65Wfn6/9+/crOTk55PXCPiJyzjnnaPv27fr222/1/PPP68Ybb1RZWZl/ucPhCOhvjAlqO9r8+fM1d+5c//3a2lqlp6crNzfXvyE+n0+lpaXKyclRXFxcuFPu8qhfZNpz/bI8zQf89sYZY/TbQU26d2uMvE0tvx6gea2t34eeMW04q46jPf8b7ghCrd+RdzTCFXYQ6d69u8466yxJ0qBBg1RRUaFHHnlEd911lySpqqpKqamp/v7V1dVKSUlpcTyn0ymn0xnUHhcXF7TBzbUhdNQvMu2xft7GjrVT9zY5Otyc25Nw69fenq+2tcd/wx3JierX2tpG/D0ixhh5vV5lZGTI5XKptLTUv6yhoUFlZWUaPnx4pA8DAAA6obCOiNxzzz0aO3as0tPTVVdXp1WrVumNN97QunXr5HA4NGfOHBUUFCgzM1OZmZkqKChQYmKi8vPz22r+AACgAwsriPz973/X9ddfr3379qlnz5668MILtW7dOuXk5EiS5s2bp0OHDmnmzJmqqanRkCFDtGHDBiUlJbXJ5AEAQMcWVhBZvnz5cZc7HA55PB55PJ5I5gQAALoIrjUDAACsIYgAAABrCCIAAMAagggAALCGIAIAAKxp9UXvAADtW/+7X2mTcT9fPK5NxkXXxBERAABgDUEEAABYQxABAADWEEQAAIA1BBEAAGANn5oBToK2+vQCAHR0HBEBAADWEEQAAIA1BBEAAGANQQQAAFhDEAEAANYQRAAAgDUEEQAAYA1BBAAAWEMQAQAA1hBEAACANQQRAABgDUEEAABYQxABAADWEEQAAIA1BBEAAGANQQQAAFhDEAEAANYQRAAAgDUEEQAAYA1BBAAAWEMQAQAA1hBEAACANQQRAABgDUEEAABYQxABAADWEEQAAIA1BBEAAGANQQQAAFhDEAEAANYQRAAAgDUEEQAAYA1BBAAAWEMQAQAA1hBEAACANQQRAABgTaztCQDtRf+7X2m23dnNaMlPpCzPenkbHSd5VkD709K/lWj4fPG4Nhsb7RNHRAAAgDVhBZFFixZp8ODBSkpKUp8+fTR58mTt3LkzoI8xRh6PR263WwkJCcrOztaOHTuiOmkAANA5hBVEysrKNGvWLG3ZskWlpaX67rvvlJubq4MHD/r7LFmyRIWFhSoqKlJFRYVcLpdycnJUV1cX9ckDAICOLaxzRNatWxdwv7i4WH369NG2bdt06aWXyhijpUuXasGCBcrLy5MkrVy5UikpKSopKdGMGTOiN3MAANDhRXSy6v79+yVJp59+uiSpsrJSVVVVys3N9fdxOp0aNWqUysvLmw0iXq9XXq/Xf7+2tlaS5PP55PP5/L8f/RPhoX6hcXYzzbfHmICfCB81jExXql9bvE7xGhiZUOvX2vo6jDGtemYbYzRp0iTV1NRo8+bNkqTy8nKNGDFCe/bskdvt9vedPn26du3apfXr1weN4/F4tHDhwqD2kpISJSYmtmZqAADgJKuvr1d+fr7279+v5OTkkNdr9RGR2bNn6/3339ebb74ZtMzhCPyIozEmqO2I+fPna+7cuf77tbW1Sk9PV25urn9DfD6fSktLlZOTo7i4uNZOucuifqHJ8gQHZen7/4X+dlCT7t0aI28TH99tDWoYma5Uvw89Y6I+Jq+BkQm1fkfe0QhXq4LIbbfdpjVr1mjTpk1KS0vzt7tcLklSVVWVUlNT/e3V1dVKSUlpdiyn0ymn0xnUHhcXF7TBzbUhdNTv+E70HSHeJgffIxIhahiZrlC/tnyN4jUwMieqX2trG9anZowxmj17tlavXq3XX39dGRkZAcszMjLkcrlUWlrqb2toaFBZWZmGDx/eqgkCAIDOK6wjIrNmzVJJSYleeuklJSUlqaqqSpLUs2dPJSQkyOFwaM6cOSooKFBmZqYyMzNVUFCgxMRE5efnt8kGAACAjiusIPL4449LkrKzswPai4uLddNNN0mS5s2bp0OHDmnmzJmqqanRkCFDtGHDBiUlJUVlwgAAoPMIK4iE8gEbh8Mhj8cjj8fT2jkBAIAugmvNAAAAawgiAADAGoIIAACwhiACAACsIYgAAABrCCIAAMAagggAALCGIAIAAKwhiAAAAGsIIgAAwBqCCAAAsCasa80A7UH/u1+xPQUAQJRwRAQAAFhDEAEAANYQRAAAgDUEEQAAYA1BBAAAWEMQAQAA1hBEAACANQQRAABgDUEEAABYQxABAADWEEQAAIA1BBEAAGANQQQAAFhDEAEAANYQRAAAgDUEEQAAYA1BBAAAWEMQAQAA1hBEAACANQQRAABgDUEEAABYQxABAADWEEQAAIA1BBEAAGANQQQAAFhDEAEAANYQRAAAgDUEEQAAYA1BBAAAWEMQAQAA1hBEAACANQQRAABgDUEEAABYQxABAADWEEQAAIA1BBEAAGANQQQAAFgTdhDZtGmTJkyYILfbLYfDoRdffDFguTFGHo9HbrdbCQkJys7O1o4dO6I1XwAA0ImEHUQOHjyoiy66SEVFRc0uX7JkiQoLC1VUVKSKigq5XC7l5OSorq4u4skCAIDOJTbcFcaOHauxY8c2u8wYo6VLl2rBggXKy8uTJK1cuVIpKSkqKSnRjBkzIpstAADoVMIOIsdTWVmpqqoq5ebm+tucTqdGjRql8vLyZoOI1+uV1+v136+trZUk+Xw++Xw+/+9H/0R4Olv9nN3MyX28GBPwE+GjhpHpSvVri9epzvYaeLKFWr/W1tdhjGn1M9vhcOiFF17Q5MmTJUnl5eUaMWKE9uzZI7fb7e83ffp07dq1S+vXrw8aw+PxaOHChUHtJSUlSkxMbO3UAADASVRfX6/8/Hzt379fycnJIa8X1SMiRzgcjoD7xpigtiPmz5+vuXPn+u/X1tYqPT1dubm5/g3x+XwqLS1VTk6O4uLi2mLKnVpnq1+WJzjQtiVnjNFvBzXp3q0x8jY1/zzG8VHDyHSl+n3oGRP1MTvba+DJFmr9jryjEa6oBhGXyyVJqqqqUmpqqr+9urpaKSkpza7jdDrldDqD2uPi4oI2uLk2hK6z1M/baOeF2NvksPbYnQU1jExXqF9bvkZ1ltdAW05Uv9bWNqrfI5KRkSGXy6XS0lJ/W0NDg8rKyjR8+PBoPhQAAOgEwj4icuDAAX322Wf++5WVldq+fbtOP/109e3bV3PmzFFBQYEyMzOVmZmpgoICJSYmKj8/P6oTBwAAHV/YQWTr1q3653/+Z//9I+d33HjjjXrqqac0b948HTp0SDNnzlRNTY2GDBmiDRs2KCkpKXqzBgAAnULYQSQ7O1vH+6CNw+GQx+ORx+OJZF4AAERVlmd9m5xj8/nicVEfsyvhWjMAAMAagggAALCGIAIAAKwhiAAAAGsIIgAAwBqCCAAAsIYgAgAArCGIAAAAawgiAADAGoIIAACwhiACAACsCftaMwAAtJX+d78S9TGd3YyW/CTqwyJKOCICAACsIYgAAABrCCIAAMAagggAALCGk1XRJtrihDMAQOfDEREAAGANQQQAAFhDEAEAANYQRAAAgDUEEQAAYA1BBAAAWEMQAQAA1hBEAACANQQRAABgDUEEAABYQxABAADWcK0ZAAAi0FbX1vp88bg2Gbe94YgIAACwhiACAACsIYgAAABrCCIAAMAagggAALCGT80AANAOtdWncaT29YkcjogAAABrCCIAAMAagggAALCGIAIAAKwhiAAAAGsIIgAAwBqCCAAAsIYgAgAArCGIAAAAawgiAADAGoIIAACwpstda6Ytv7u/rbSnawIAABBNHBEBAADWtFkQeeyxx5SRkaH4+Hhdcskl2rx5c1s9FAAA6KDaJIg8++yzmjNnjhYsWKB3331XP/3pTzV27Fh98cUXbfFwAACgg2qTIFJYWKhp06bpF7/4hQYOHKilS5cqPT1djz/+eFs8HAAA6KCifrJqQ0ODtm3bprvvvjugPTc3V+Xl5UH9vV6vvF6v//7+/fslSd988418Pp8kyefzqb6+Xl9//bXi4uIiml/sdwcjWt+Gr7/+OqL1o1m/UHXEOrcktsmovr5Jsb4YNTY5bE+nQ6KGkaF+kaF+wcLZr4S6D6mrq5MkGWPCmkvUg8hXX32lxsZGpaSkBLSnpKSoqqoqqP+iRYu0cOHCoPaMjIxoT63D6v2w7Rkg3/YEOgFqGBnqFxnqF6gt9yt1dXXq2bNnyP3b7OO7Dkdg6jTGBLVJ0vz58zV37lz//aamJn3zzTfq1auXv39tba3S09O1e/duJScnt9WUOy3qFxnqFzlqGBnqFxnqF5lQ62eMUV1dndxud1jjRz2I9O7dW926dQs6+lFdXR10lESSnE6nnE5nQNupp57a7NjJyck8iSJA/SJD/SJHDSND/SJD/SITSv3CORJyRNRPVu3evbsuueQSlZaWBrSXlpZq+PDh0X44AADQgbXJWzNz587V9ddfr0GDBmnYsGFatmyZvvjiC916661t8XAAAKCDapMgMmXKFH399de6//77tW/fPmVlZenVV19Vv379WjWe0+nUfffdF/QWDkJD/SJD/SJHDSND/SJD/SLT1vVzmHA/ZwMAABAlXGsGAABYQxABAADWEEQAAIA1BBEAAGANQQQAAFhjLYhs2rRJEyZMkNvtlsPh0Isvvthi3xkzZsjhcGjp0qUB7V6vV7fddpt69+6tHj16aOLEifryyy/bduLtRCj1++ijjzRx4kT17NlTSUlJGjp0qL744gv/curXcv0OHDig2bNnKy0tTQkJCRo4cGDQ1aO7cv0WLVqkwYMHKykpSX369NHkyZO1c+fOgD7GGHk8HrndbiUkJCg7O1s7duwI6NNVa3ii+vl8Pt1111264IIL1KNHD7ndbt1www3au3dvwDhdtX5SaM/Bo7EfCRRq/U7GfsRaEDl48KAuuugiFRUVHbffiy++qLfeeqvZ766fM2eOXnjhBa1atUpvvvmmDhw4oPHjx6uxsbGtpt1unKh+//u//6uRI0fq3HPP1RtvvKH33ntP9957r+Lj4/19qF/L9bv99tu1bt06PfPMM/roo490++2367bbbtNLL73k79OV61dWVqZZs2Zpy5YtKi0t1Xfffafc3FwdPPjDVZeXLFmiwsJCFRUVqaKiQi6XSzk5Of4rdEpdt4Ynql99fb3eeecd3XvvvXrnnXe0evVqffLJJ5o4cWLAOF21flJoz8Ej2I8EC6V+J20/YtoBSeaFF14Iav/yyy/Nj370I/Phhx+afv36mT/84Q/+Zd9++62Ji4szq1at8rft2bPHxMTEmHXr1p2EWbcfzdVvypQp5rrrrmtxHer3g+bqd/7555v7778/oO3HP/6x+bd/+zdjDPU7VnV1tZFkysrKjDHGNDU1GZfLZRYvXuzvc/jwYdOzZ0/zxBNPGGOo4dGOrV9z3n77bSPJ7Nq1yxhD/Y7VUg3Zj4SmufqdrP1Iuz1HpKmpSddff73uvPNOnX/++UHLt23bJp/Pp9zcXH+b2+1WVlaWysvLT+ZU252mpia98sorOvvsszVmzBj16dNHQ4YMCXj7gfod38iRI7VmzRrt2bNHxhht3LhRn3zyicaMGSOJ+h1r//79kqTTTz9dklRZWamqqqqA+jidTo0aNcpfH2r4g2Pr11Ifh8Phvygo9QvUXA3Zj4Tu2PqdzP1Iuw0iDz74oGJjY/WrX/2q2eVVVVXq3r27TjvttID2lJSUoCv/djXV1dU6cOCAFi9erMsvv1wbNmzQv/zLvygvL09lZWWSqN+JPProozrvvPOUlpam7t276/LLL9djjz2mkSNHSqJ+RzPGaO7cuRo5cqSysrIkyV+DY6+4fXR9qOH3mqvfsQ4fPqy7775b+fn5/qufUr8ftFRD9iOhaa5+J3M/0ibXmonUtm3b9Mgjj+idd96Rw+EIa11jTNjrdDZNTU2SpEmTJun222+XJF188cUqLy/XE088oVGjRrW4LvX73qOPPqotW7ZozZo16tevnzZt2qSZM2cqNTVVo0ePbnG9rli/2bNn6/3339ebb74ZtOzYWoRSn65Ww+PVT/r+xNVrrrlGTU1Neuyxx044Xlern9R8DdmPhK65+p3M/Ui7PCKyefNmVVdXq2/fvoqNjVVsbKx27dqlO+64Q/3795ckuVwuNTQ0qKamJmDd6urqoP+FdTW9e/dWbGyszjvvvID2gQMH+s92pn4tO3TokO655x4VFhZqwoQJuvDCCzV79mxNmTJFDz30kCTqd8Rtt92mNWvWaOPGjUpLS/O3u1wuSQr6X9HR9aGGLdfvCJ/Pp6uvvlqVlZUqLS31Hw2RqN8RLdWQ/UhoWqrfydyPtMsgcv311+v999/X9u3b/Te3260777xT69evlyRdcskliouLU2lpqX+9ffv26cMPP9Tw4cNtTb1d6N69uwYPHhz0UaxPPvnEfwVk6tcyn88nn8+nmJjAfx7dunXz/y+hq9fPGKPZs2dr9erVev3115WRkRGwPCMjQy6XK6A+DQ0NKisr89enK9fwRPWTfgghn376qV577TX16tUrYHlXrp904hqyHzm+E9XvpO5HQj6tNcrq6urMu+++a959910jyRQWFpp3333Xf0b4sY4929kYY2699VaTlpZmXnvtNfPOO++Yn/3sZ+aiiy4y33333UnYArtOVL/Vq1ebuLg4s2zZMvPpp5+aP/7xj6Zbt25m8+bN/jGoX8v1GzVqlDn//PPNxo0bzf/93/+Z4uJiEx8fbx577DH/GF25fr/85S9Nz549zRtvvGH27dvnv9XX1/v7LF682PTs2dOsXr3afPDBB+baa681qamppra21t+nq9bwRPXz+Xxm4sSJJi0tzWzfvj2gj9fr9Y/TVetnTGjPwWOxH/lBKPU7WfsRa0Fk48aNRlLQ7cYbb2y2f3NPoEOHDpnZs2eb008/3SQkJJjx48ebL774ou0n3w6EUr/ly5ebs846y8THx5uLLrrIvPjiiwFjUL+W67dv3z5z0003GbfbbeLj480555xjHn74YdPU1OQfoyvXr7naSTLFxcX+Pk1NTea+++4zLpfLOJ1Oc+mll5oPPvggYJyuWsMT1a+ysrLFPhs3bvSP01XrZ0xoz8FjsR/5Qaj1Oxn7Ecf/nxAAAMBJ1y7PEQEAAF0DQQQAAFhDEAEAANYQRAAAgDUEEQAAYA1BBAAAWEMQAQAA1hBEAACANQQRAABgDUEEAABYQxABAADW/D9OFYFetVRhvgAAAABJRU5ErkJggg==",
      "text/plain": [
       "<Figure size 640x480 with 1 Axes>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "ax = analysis_df.to_pandas().hist(column=\"AVG_SHIFT_SALES\", bins=20, figsize=(3, 1.5))"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "ae86989e-b455-48c8-81d2-122ec6300c30",
   "metadata": {
    "name": "FEATURE_ENGINEERING"
   },
   "source": [
    "--------------\n",
    "# ❄️ FEATURE ENGINEERING ❄️ \n",
    "--------------"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "id": "6070f262-a805-4833-819d-f5e702cfa227",
   "metadata": {
    "name": "cell40"
   },
   "source": [
    "We will create a feature engineering pipeline by chaining transformations to prepare our data for model training.<br>\n",
    "**Value:** The Snowpark syntax makes pipelines easy to implement and understand. The syntax also allows for easy migration of Spark pipelines to Snowflake.<br>\n",
    "**All transformations for feature engineering in this notebook will be executed on Snowflake compute.**\n",
    "\n",
    "Notice what we haven't had to do? No tuning, maintenance, or operational overhead. We just need a role, warehouse, and access to the data.<br>\n",
    "**Value**: Near-zero maintenance. Focus on the work that brings value."
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "id": "d30d9c54-114e-4cbe-8cc1-e107c8cb1838",
   "metadata": {
    "name": "cell41"
   },
   "source": [
    "## Create a Rolling Average Feature\n",
    "We will use a Snowflake window function to get a **rolling shift average by location** over time. Window functions allow us to aggregate on a \"moving\" group of rows.\n",
    "\n",
    "**Step 1. Create a Window**\n",
    "\n",
    "Our window will partition the data by location and shift. It will order rows by date. It will include all rows prior to the current date of the observation it is aggregating for."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "id": "a3b2eec0-a63f-464e-9e2f-ca928c9ab0b3",
   "metadata": {
    "collapsed": false,
    "language": "python",
    "name": "create_window"
   },
   "outputs": [],
   "source": [
    "window_by_location_all_days = (\n",
    "    Window.partition_by(\"location_id\", \"shift\")\n",
    "    .order_by(\"date\")\n",
    "    .rows_between(Window.UNBOUNDED_PRECEDING, Window.CURRENT_ROW - 1)\n",
    ")"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "id": "5b9f50a1-0830-4c1c-ada8-ddc88dc4d366",
   "metadata": {
    "name": "cell43"
   },
   "source": [
    "**Step 2. Aggregate across the Window**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "id": "123d4094-5cac-4c5b-bf2c-ac358d3b9ad7",
   "metadata": {
    "collapsed": false,
    "language": "python",
    "name": "aggregate_across_window"
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------\n",
      "|\"LOCATION_ID\"  |\"CITY\"   |\"DATE\"      |\"SHIFT_SALES\"  |\"SHIFT\"  |\"MONTH\"  |\"DAY_OF_WEEK\"  |\"CITY_POPULATION\"  |\"LATITUDE\"  |\"LONGITUDE\"  |\"LOCATION_NAME\"        |\"AVG_LOCATION_SHIFT_SALES\"  |\n",
      "------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------\n",
      "|1303           |Toronto  |2015-04-06  |298.91         |PM       |4        |1              |2794356            |43.629078   |-79.581193   |Golf Canada SCOREGolf  |NULL                        |\n",
      "|1303           |Toronto  |2015-05-30  |550.72         |PM       |5        |6              |2794356            |43.629078   |-79.581193   |Golf Canada SCOREGolf  |298.91                      |\n",
      "------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------\n",
      "\n"
     ]
    }
   ],
   "source": [
    "snowpark_df = snowpark_df.with_column(\n",
    "    \"avg_location_shift_sales\", \n",
    "    F.avg(\"shift_sales\").over(window_by_location_all_days)\n",
    ")\n",
    "\n",
    "snowpark_df.show(2)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "05991ec7-af38-4c2c-b8b6-888925738be9",
   "metadata": {
    "name": "MODEL_TRAINING"
   },
   "source": [
    "--------------\n",
    "# ❄️ MODEL TRAINING ❄️ \n",
    "--------------"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "3e2dedd8-f9f4-40c5-9a28-3cf090c7a0cf",
   "metadata": {
    "name": "cell46"
   },
   "source": [
    "## Filter to Historical Data\n",
    "Our model will provide the predicted sales at each location for the upcoming shift (\"AM\" or \"PM\").\n",
    "\n",
    "Our data includes placeholders for future data with missing **shift_sales**. The **future data** represents the next 7 days of shifts for all locations. The **historical data** has shift sales for all locations where a food truck parked during a shift. We will only use historical data when training our model and will filter out future data (i.e. the dates where the **shift_sales** column is missing)."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "id": "7fb9cc3c-1991-4a70-b64b-676d3bb4b8f2",
   "metadata": {
    "language": "python",
    "name": "create_historical_df"
   },
   "outputs": [],
   "source": [
    "historical_snowpark_df = snowpark_df.filter(F.col(\"shift_sales\").is_not_null())"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "67985e55-bdbb-480e-b6de-c1a55f5464ba",
   "metadata": {
    "name": "cell48"
   },
   "source": [
    "## Split data into training and testing"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "b5083697-3917-40d6-b91a-d7860941030c",
   "metadata": {
    "name": "cell49"
   },
   "source": [
    "We will initially train our model on all historical data except the most recent year, which we will reserve as a test dataset. The test dataset will be used to evaluate how the model might perform on future data.   "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "id": "30c9d4aa-7502-4e1b-a359-3b3d6712a618",
   "metadata": {
    "language": "python",
    "name": "train_test_split"
   },
   "outputs": [],
   "source": [
    "# Obtain the min and max dates present in the historical DataFrame\n",
    "date_range = historical_snowpark_df.agg(F.min(\"DATE\").alias(\"MIN_DATE\"), F.max(\"DATE\").alias(\"MAX_DATE\")).collect()[0]\n",
    "min_date = date_range['MIN_DATE']\n",
    "max_date = date_range['MAX_DATE']\n",
    "\n",
    "# Obtain the start date for the test set (365 days before the max date)\n",
    "test_start_date = max_date - timedelta(days=365)\n",
    "\n",
    "# Create train and test sets (dropping the date column) \n",
    "train_snowpark_df = historical_snowpark_df.filter(F.col(\"DATE\") < test_start_date).drop(\"DATE\")\n",
    "test_snowpark_df = historical_snowpark_df.filter(F.col(\"DATE\") >= test_start_date).drop(\"DATE\")\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "01878f82-6df0-4303-8986-ce86c7f6d3f8",
   "metadata": {
    "name": "cell51"
   },
   "source": [
    "## Build a Snowpark ML Pipeline\n",
    "\n",
    "**Snowpark ML** is an integrated set of capabilities for end-to-end machine learning in a single platform on top of your governed data.\n",
    "\n",
    "In the next cell we will use a Pipeline to build our model. A Pipeline is a tool that sequentially applies a list of transforms and a final estimator, streamlining the process of preprocessing and modeling by combining these steps into a single object. \n",
    "\n",
    "This Pipeline first performs **Preprocessing** steps using Snowpark ML pre-processors (SimpleImputer and OneHotEncoder). These steps will replace any missing values in the **avg_location_shift_sales** column with 0. It will also convert the categorical column **SHIFT** into a numeric column with a value of 1 for \"AM\" shifts and 0 for \"PM\" shifts. \n",
    "\n",
    "Next the Pipeline trains a Snowpark ML **LinearRegression** model using the newly-transformed features and any other features that already existed in the dataset. Features listed in the **passthrough_cols** argument will not be used to train the model. \n",
    "\n",
    "Linear regression finds a line that best fits the data points used in training. We then use that line as an estimation of where output values will be for future scenarios. Here, training will use historical shift sales and features in our data to predict future shift sales of locations where our food trucks can park."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "id": "64940490-1e1d-415f-9f85-b0d0b4d1b4c7",
   "metadata": {
    "language": "python",
    "name": "create_model_pipeline"
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Model has been fit to training dataset.\n"
     ]
    }
   ],
   "source": [
    "# Build the pipeline\n",
    "pipeline = Pipeline(\n",
    "    [\n",
    "        (\n",
    "            \"SimpleImputer (numeric)\",\n",
    "            SimpleImputer(\n",
    "                input_cols=[\"AVG_LOCATION_SHIFT_SALES\"],\n",
    "                output_cols=[\"AVG_LOCATION_SHIFT_SALES\"],\n",
    "                strategy=\"constant\",\n",
    "                fill_value=0\n",
    "            ),\n",
    "        ),\n",
    "        (\n",
    "            \"OneHotEncoder\",\n",
    "            OneHotEncoder(\n",
    "                drop=[\"PM\"],\n",
    "                handle_unknown=\"ignore\",\n",
    "                input_cols=[\"SHIFT\"],\n",
    "                output_cols=[\"SHIFT\"],\n",
    "            ),\n",
    "        ),\n",
    "        (\n",
    "            \"LinearRegression\",\n",
    "            LinearRegression(\n",
    "                label_cols=[\"SHIFT_SALES\"],\n",
    "                passthrough_cols=['LOCATION_ID', 'CITY', 'DATE', 'SHIFT', 'LOCATION_NAME'],\n",
    "                output_cols=[\"FORECASTED_SHIFT_SALES\"],\n",
    "            ),\n",
    "        ),\n",
    "    ]\n",
    ")\n",
    "\n",
    "# Fit the pipeline on the training data\n",
    "# We will remove the first column which contains nulls from the rolling average\n",
    "pipeline.fit(train_snowpark_df.limit(train_snowpark_df.count() - 1, 1))\n",
    "\n",
    "print(\"Model has been fit to training dataset.\")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "ca40a5f9-fc14-4220-b10b-6fce438c7df8",
   "metadata": {
    "name": "cell53"
   },
   "source": [
    "## Examine Feature Contributions\n",
    "The model object from our fitted Pipeline contains model coefficient estimates, which can be interepreted as feature contributions. Here we temporarily convert the LinearRegression model (the last step of the fitted Pipeline) to an **scikit-learn** verison of the model. This will allow us to access the **coef_** attribute of the model object.\n",
    "\n",
    "We can see which features have the largest impact on shift sale predictions. The prediction provided by the linear regression model is a summation of the feature values multiplied by their respective coefficients (plus an additional bias term). We can see that there is a large negative coefficient on the SHIFT_AM feature, which suggests that people are hungrier in the afternoon! "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "id": "243afc28-ced7-442b-b4da-077eb82b897a",
   "metadata": {
    "language": "python",
    "name": "inspect_coefficients"
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Feature</th>\n",
       "      <th>Weight</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>SHIFT_AM</td>\n",
       "      <td>-18.769728</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>MONTH</td>\n",
       "      <td>-6.526725</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>DAY_OF_WEEK</td>\n",
       "      <td>2.733950</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>AVG_LOCATION_SHIFT_SALES</td>\n",
       "      <td>0.980475</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>LATITUDE</td>\n",
       "      <td>-0.321237</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>LONGITUDE</td>\n",
       "      <td>0.106510</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>CITY_POPULATION</td>\n",
       "      <td>0.000008</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                    Feature     Weight\n",
       "0                  SHIFT_AM -18.769728\n",
       "2                     MONTH  -6.526725\n",
       "3               DAY_OF_WEEK   2.733950\n",
       "1  AVG_LOCATION_SHIFT_SALES   0.980475\n",
       "5                  LATITUDE  -0.321237\n",
       "6                 LONGITUDE   0.106510\n",
       "4           CITY_POPULATION   0.000008"
      ]
     },
     "execution_count": 32,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Can use to_sklearn() method to convert the Snowflake model to a scikit-learn model\n",
    "model_step = pipeline.steps[-1][1].to_sklearn()\n",
    "\n",
    "# Create a dataframe of features and their coefficients (weights)\n",
    "feature_weights = pd.DataFrame({\"Feature\": model_step.feature_names_in_, \"Weight\": model_step.coef_})\n",
    "\n",
    "feature_weights.sort_values(\"Weight\", key=abs, ascending=False)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "da9aec9c-8e96-44bd-8517-a26bda3fe895",
   "metadata": {
    "name": "cell55"
   },
   "source": [
    "## Evaluate model performance\n",
    "To get an idea of how well our model will generalize to future unseen datasets, we will obtain predictions on the test dataset (which was not seen by the model during training) and compare those predictions to the actual values. The performance metrics we calculate can be used when comparing two different models to determine which one will potentially perform better in production. \n",
    "\n",
    "First we will predict on the test set. Then we will use Snowpark ML to calculate a handful of standard metrics used to evaluate regression models. For example, **mean absolute error (MAE)** lets us know how far on average our predictions are from the actual values. \n",
    "\n",
    "The metrics obtained here will be registered to the Model Registry when we deploy the model later on."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "id": "ce80b442-881d-4707-9d13-c092626f5920",
   "metadata": {
    "language": "python",
    "name": "evaluate_model"
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "------------------------------------------------------------------------------------------------\n",
      "|\"LOCATION_ID\"  |\"LOCATION_NAME\"          |\"SHIFT\"  |\"SHIFT_SALES\"  |\"FORECASTED_SHIFT_SALES\"  |\n",
      "------------------------------------------------------------------------------------------------\n",
      "|7906           |Four Points By Sheraton  |PM       |1045.13        |1056.7878763781996        |\n",
      "|7906           |Four Points By Sheraton  |PM       |760.43         |1068.2330499354484        |\n",
      "|7906           |Four Points By Sheraton  |PM       |1154.48        |1049.9126419768172        |\n",
      "------------------------------------------------------------------------------------------------\n",
      "\n",
      "{'rmse': 375.8557, 'mae': 261.9697, 'mape': 0.3354}\n"
     ]
    }
   ],
   "source": [
    "# Obtain predictions on the test set\n",
    "preds_test = pipeline.predict(test_snowpark_df)\n",
    "\n",
    "# Show the first few rows of predictions\n",
    "preds_test.select(\"LOCATION_ID\", \"LOCATION_NAME\", \"SHIFT\", \"SHIFT_SALES\", \"FORECASTED_SHIFT_SALES\").show(3)\n",
    "\n",
    "# Calculate Root Mean Square Error (RMSE)\n",
    "mse = mean_squared_error(df=preds_test, y_true_col_names=\"SHIFT_SALES\", y_pred_col_names=\"FORECASTED_SHIFT_SALES\")\n",
    "rmse = round(math.sqrt(mse), 4)\n",
    "\n",
    "# Calculate Mean Absolute Error (MAE)\n",
    "mae = mean_absolute_error(df=preds_test, y_true_col_names=\"SHIFT_SALES\", y_pred_col_names=\"FORECASTED_SHIFT_SALES\")\n",
    "mae = round(mae, 4)\n",
    "\n",
    "# Calculate Mean Absolute Percentage Error (MAPE)\n",
    "mape = mean_absolute_percentage_error(df=preds_test, y_true_col_names=\"SHIFT_SALES\", y_pred_col_names=\"FORECASTED_SHIFT_SALES\")\n",
    "mape = round(mape, 4)\n",
    "\n",
    "# Create a dictionary containing all the metrics, which will be registered along with the model\n",
    "test_metrics = {\"rmse\": rmse, \"mae\": mae, \"mape\": mape}\n",
    "print(test_metrics)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "1d046ceb-3783-46a7-91fa-380b34969963",
   "metadata": {
    "name": "cell57"
   },
   "source": [
    "## Fit the pipeline to the entire historical set\n",
    "\n",
    "Now that we have developed our model and obtained performance metrics on our test set, we can fit the model on the entire historical dataset to ensure that the deployed model will have been trained on the most recent data. We will still use the Pipeline we built above, but we will fit it on a different dataset."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "id": "dad92241-0339-49b1-8a54-10e7a9c188ff",
   "metadata": {
    "language": "python",
    "name": "fit_final_model"
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Model has been trained on all historical data\n"
     ]
    }
   ],
   "source": [
    "pipeline.fit(historical_snowpark_df.limit(historical_snowpark_df.count() - 1, 1).drop(\"DATE\"))\n",
    "\n",
    "print(\"Model has been trained on all historical data\")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "70da5804-3b23-4c2a-91a6-eade32663c59",
   "metadata": {
    "name": "MODEL_DEPLOYMENT"
   },
   "source": [
    "--------------\n",
    "# ❄️ MODEL DEPLOYMENT ❄️ \n",
    "--------------"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "e0635cbd-c77e-4025-b623-2fab9e814591",
   "metadata": {
    "name": "cell60"
   },
   "source": [
    "## Log the model in the Model Registry\n",
    "In ML, a model registry is a centralized repository or model store, similar to a library, that lets you effectively manage and organize machine learning models. It is where models are stored, tracked, versioned, and made accessible to anyone at the company involved in deploying and using models in production.\n",
    "\n",
    "Snowflake provides Model Registry capabilities that will allow you to store your models and model versions in a common location along with metadata related to each model. This greatly streamlines model deployment. Through the Model Registry, Snowflake handles storing the model object and making the model available for inference. \n",
    "\n",
    "Model Registries in Snowflake are schema-level objects, so an account can have a single model registry or several model registries for different purposes/business units, depending on the needs of the organization. If a Registry does not already exist in a schema, then one will be created the first time you log a model. \n",
    "\n",
    "Here we establish a Registry object, and then use the **log_model** method to place our fitted Pipeline into the Registry along with the evaluation metrics we obtained above. We name the model \"**shift_sales_forecaster**\", and any time we re-train and log a model with that name, the Registry will store it as a new version. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "id": "da1bcb8e-be88-415a-93a3-82657336af5e",
   "metadata": {
    "language": "python",
    "name": "register_model"
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>created_on</th>\n",
       "      <th>name</th>\n",
       "      <th>database_name</th>\n",
       "      <th>schema_name</th>\n",
       "      <th>comment</th>\n",
       "      <th>owner</th>\n",
       "      <th>default_version_name</th>\n",
       "      <th>versions</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2024-06-28 12:10:21.893000-07:00</td>\n",
       "      <td>SHIFT_SALES_FORECASTER</td>\n",
       "      <td>FROSTBYTE_TASTY_BYTES_DEV</td>\n",
       "      <td>ANALYTICS</td>\n",
       "      <td>None</td>\n",
       "      <td>TASTY_BYTES_DS_ROLE</td>\n",
       "      <td>STUPID_MOOSE_4</td>\n",
       "      <td>[\"BITTER_BAT_4\",\"GENTLE_BOBCAT_2\",\"GIANT_LEECH...</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                        created_on                    name  \\\n",
       "0 2024-06-28 12:10:21.893000-07:00  SHIFT_SALES_FORECASTER   \n",
       "\n",
       "               database_name schema_name comment                owner  \\\n",
       "0  FROSTBYTE_TASTY_BYTES_DEV   ANALYTICS    None  TASTY_BYTES_DS_ROLE   \n",
       "\n",
       "  default_version_name                                           versions  \n",
       "0       STUPID_MOOSE_4  [\"BITTER_BAT_4\",\"GENTLE_BOBCAT_2\",\"GIANT_LEECH...  "
      ]
     },
     "execution_count": 35,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "reg = Registry(session)\n",
    "reg.log_model(model=pipeline, model_name=\"shift_sales_forecaster\", metrics=test_metrics)\n",
    "\n",
    "# Show all models in the registry\n",
    "reg.show_models()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "id": "8728c130-ff7c-44c4-9be5-10ece7c4537c",
   "metadata": {
    "language": "python",
    "name": "show_model_versions"
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>created_on</th>\n",
       "      <th>name</th>\n",
       "      <th>aliases</th>\n",
       "      <th>comment</th>\n",
       "      <th>database_name</th>\n",
       "      <th>schema_name</th>\n",
       "      <th>module_name</th>\n",
       "      <th>is_default_version</th>\n",
       "      <th>functions</th>\n",
       "      <th>metadata</th>\n",
       "      <th>user_data</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2024-06-28 12:10:21.943000-07:00</td>\n",
       "      <td>STUPID_MOOSE_4</td>\n",
       "      <td>[\"DEFAULT\",\"FIRST\"]</td>\n",
       "      <td>None</td>\n",
       "      <td>FROSTBYTE_TASTY_BYTES_DEV</td>\n",
       "      <td>ANALYTICS</td>\n",
       "      <td>SHIFT_SALES_FORECASTER</td>\n",
       "      <td>true</td>\n",
       "      <td>[\"PREDICT\"]</td>\n",
       "      <td>{\"metrics\": {\"rmse\": 375.8557, \"mae\": 261.9697...</td>\n",
       "      <td>{}</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2024-06-28 12:12:41.551000-07:00</td>\n",
       "      <td>UGLY_ROBIN_3</td>\n",
       "      <td>[]</td>\n",
       "      <td>None</td>\n",
       "      <td>FROSTBYTE_TASTY_BYTES_DEV</td>\n",
       "      <td>ANALYTICS</td>\n",
       "      <td>SHIFT_SALES_FORECASTER</td>\n",
       "      <td>false</td>\n",
       "      <td>[\"PREDICT\"]</td>\n",
       "      <td>{\"metrics\": {\"rmse\": 375.8557, \"mae\": 261.9697...</td>\n",
       "      <td>{}</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2024-07-02 05:55:37.542000-07:00</td>\n",
       "      <td>BITTER_BAT_4</td>\n",
       "      <td>[]</td>\n",
       "      <td>None</td>\n",
       "      <td>FROSTBYTE_TASTY_BYTES_DEV</td>\n",
       "      <td>ANALYTICS</td>\n",
       "      <td>SHIFT_SALES_FORECASTER</td>\n",
       "      <td>false</td>\n",
       "      <td>[\"PREDICT\"]</td>\n",
       "      <td>{\"metrics\": {\"rmse\": 375.8557, \"mae\": 261.9697...</td>\n",
       "      <td>{}</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2024-07-02 06:29:20.995000-07:00</td>\n",
       "      <td>GENTLE_BOBCAT_2</td>\n",
       "      <td>[]</td>\n",
       "      <td>None</td>\n",
       "      <td>FROSTBYTE_TASTY_BYTES_DEV</td>\n",
       "      <td>ANALYTICS</td>\n",
       "      <td>SHIFT_SALES_FORECASTER</td>\n",
       "      <td>false</td>\n",
       "      <td>[\"PREDICT\"]</td>\n",
       "      <td>{\"metrics\": {\"rmse\": 375.8557, \"mae\": 261.9697...</td>\n",
       "      <td>{}</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2024-07-02 06:45:31.906000-07:00</td>\n",
       "      <td>MODERN_FIREFOX_3</td>\n",
       "      <td>[]</td>\n",
       "      <td>None</td>\n",
       "      <td>FROSTBYTE_TASTY_BYTES_DEV</td>\n",
       "      <td>ANALYTICS</td>\n",
       "      <td>SHIFT_SALES_FORECASTER</td>\n",
       "      <td>false</td>\n",
       "      <td>[\"PREDICT\"]</td>\n",
       "      <td>{\"metrics\": {\"rmse\": 375.8557, \"mae\": 261.9697...</td>\n",
       "      <td>{}</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>2024-07-02 07:55:01.386000-07:00</td>\n",
       "      <td>LOUD_FALCON_4</td>\n",
       "      <td>[\"LAST\"]</td>\n",
       "      <td>None</td>\n",
       "      <td>FROSTBYTE_TASTY_BYTES_DEV</td>\n",
       "      <td>ANALYTICS</td>\n",
       "      <td>SHIFT_SALES_FORECASTER</td>\n",
       "      <td>false</td>\n",
       "      <td>[\"PREDICT\"]</td>\n",
       "      <td>{\"metrics\": {\"rmse\": 375.8557, \"mae\": 261.9697...</td>\n",
       "      <td>{}</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                        created_on              name              aliases  \\\n",
       "0 2024-06-28 12:10:21.943000-07:00    STUPID_MOOSE_4  [\"DEFAULT\",\"FIRST\"]   \n",
       "1 2024-06-28 12:12:41.551000-07:00      UGLY_ROBIN_3                   []   \n",
       "2 2024-07-02 05:55:37.542000-07:00      BITTER_BAT_4                   []   \n",
       "3 2024-07-02 06:29:20.995000-07:00   GENTLE_BOBCAT_2                   []   \n",
       "4 2024-07-02 06:45:31.906000-07:00  MODERN_FIREFOX_3                   []   \n",
       "5 2024-07-02 07:55:01.386000-07:00     LOUD_FALCON_4             [\"LAST\"]   \n",
       "\n",
       "  comment              database_name schema_name             module_name  \\\n",
       "0    None  FROSTBYTE_TASTY_BYTES_DEV   ANALYTICS  SHIFT_SALES_FORECASTER   \n",
       "1    None  FROSTBYTE_TASTY_BYTES_DEV   ANALYTICS  SHIFT_SALES_FORECASTER   \n",
       "2    None  FROSTBYTE_TASTY_BYTES_DEV   ANALYTICS  SHIFT_SALES_FORECASTER   \n",
       "3    None  FROSTBYTE_TASTY_BYTES_DEV   ANALYTICS  SHIFT_SALES_FORECASTER   \n",
       "4    None  FROSTBYTE_TASTY_BYTES_DEV   ANALYTICS  SHIFT_SALES_FORECASTER   \n",
       "5    None  FROSTBYTE_TASTY_BYTES_DEV   ANALYTICS  SHIFT_SALES_FORECASTER   \n",
       "\n",
       "  is_default_version    functions  \\\n",
       "0               true  [\"PREDICT\"]   \n",
       "1              false  [\"PREDICT\"]   \n",
       "2              false  [\"PREDICT\"]   \n",
       "3              false  [\"PREDICT\"]   \n",
       "4              false  [\"PREDICT\"]   \n",
       "5              false  [\"PREDICT\"]   \n",
       "\n",
       "                                            metadata user_data  \n",
       "0  {\"metrics\": {\"rmse\": 375.8557, \"mae\": 261.9697...        {}  \n",
       "1  {\"metrics\": {\"rmse\": 375.8557, \"mae\": 261.9697...        {}  \n",
       "2  {\"metrics\": {\"rmse\": 375.8557, \"mae\": 261.9697...        {}  \n",
       "3  {\"metrics\": {\"rmse\": 375.8557, \"mae\": 261.9697...        {}  \n",
       "4  {\"metrics\": {\"rmse\": 375.8557, \"mae\": 261.9697...        {}  \n",
       "5  {\"metrics\": {\"rmse\": 375.8557, \"mae\": 261.9697...        {}  "
      ]
     },
     "execution_count": 29,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Show all versions of a specific model (\"SHIFT_SALES_FORECASTER\" in this case)\n",
    "reg.get_model(\"SHIFT_SALES_FORECASTER\").show_versions()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "2b89a3cc-c88f-4843-9eb0-443f276fcca5",
   "metadata": {
    "name": "INFERENCE"
   },
   "source": [
    "--------------\n",
    "# ❄️ INFERENCE ❄️ \n",
    "--------------"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "d4c46ff3-4ba4-47ee-a829-07e29f783f27",
   "metadata": {
    "name": "cell64"
   },
   "source": [
    "## Predict for tomorrow morning's shift in Vancouver\n",
    "\n",
    "Now that our model is deployed, we can use it in Snowflake on our data to get shift sales predictions. \n",
    "\n",
    "First we will filter to the morning shift of the first future date in our data and Vancouver locations. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "id": "85d8535e-8470-46a1-9f69-7ca58cc71167",
   "metadata": {
    "language": "python",
    "name": "get_inference_df"
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------\n",
      "|\"LOCATION_ID\"  |\"CITY\"     |\"SHIFT_SALES\"  |\"SHIFT\"  |\"LOCATION_NAME\"         |\"SHIFT_AM\"  |\"AVG_LOCATION_SHIFT_SALES\"  |\"MONTH\"  |\"DAY_OF_WEEK\"  |\"CITY_POPULATION\"  |\"LATITUDE\"  |\"LONGITUDE\"  |\"FORECASTED_SHIFT_SALES\"  |\n",
      "------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------\n",
      "|2424           |Vancouver  |NULL           |AM       |Flowers by Michael      |1           |164.18241758241757          |5        |1              |662248             |49.288758   |-123.128241  |217.78042678838736        |\n",
      "|5192           |Vancouver  |NULL           |AM       |Dreamgroup Productions  |1           |167.91547619047617          |5        |1              |662248             |49.265897   |-123.134523  |221.88828776020088        |\n",
      "|3016           |Vancouver  |NULL           |AM       |lululemon athletica     |1           |192.6601818181818           |5        |1              |662248             |49.284272   |-123.124617  |249.08059677150058        |\n",
      "------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------\n",
      "\n"
     ]
    }
   ],
   "source": [
    "# Get the date to predict\n",
    "date_tomorrow = snowpark_df.filter(F.col(\"shift_sales\").is_null()).select(F.min(\"date\")).collect()[0][0]\n",
    "# Filter to tomorrow's date and the morning shift in Vancouver\n",
    "location_predictions_df = snowpark_df.filter((F.col(\"date\") == date_tomorrow) \n",
    "                                             & (F.col(\"shift\") == \"AM\") \n",
    "                                             & (F.col(\"city\")==\"Vancouver\")).drop(\"DATE\")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "1214c0b3-3a29-4ba1-8645-73c75bda67b2",
   "metadata": {
    "name": "cell66"
   },
   "source": [
    "Next we will access the model in the Model Registry. We use the **.default** attribute to specify that we would like to use the \"default\" version of the model. You can set any version to be the \"default\" version. Often the \"default\" version is chosen to be the most recent version of the model or the one with the best performance metric.  \n",
    "\n",
    "To obtain our predictions we simply set function_name=\"predict\" in the **run** method."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 38,
   "id": "28078721-8f45-44b1-8aa1-ffd9a2d82ba2",
   "metadata": {
    "language": "python",
    "name": "make_predictions"
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------\n",
      "|\"LOCATION_ID\"  |\"CITY\"     |\"SHIFT_SALES\"  |\"SHIFT\"  |\"LOCATION_NAME\"         |\"SHIFT_AM\"  |\"AVG_LOCATION_SHIFT_SALES\"  |\"MONTH\"  |\"DAY_OF_WEEK\"  |\"CITY_POPULATION\"  |\"LATITUDE\"  |\"LONGITUDE\"  |\"FORECASTED_SHIFT_SALES\"  |\n",
      "------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------\n",
      "|2636           |Vancouver  |NULL           |AM       |Central Parking System  |1           |173.6669911504424           |5        |1              |662248             |49.289678   |-123.128364  |228.20432191891774        |\n",
      "|2127           |Vancouver  |NULL           |AM       |Formally Kirkpatrick    |1           |131.90425000000002          |5        |1              |662248             |49.24916    |-123.146497  |182.31282606296742        |\n",
      "|4149           |Vancouver  |NULL           |AM       |Canada Place            |1           |201.51303921568623          |5        |1              |662248             |49.2887     |-123.11117   |258.8104283980143         |\n",
      "------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------\n",
      "\n"
     ]
    }
   ],
   "source": [
    "# Obtain predictions\n",
    "inference_df = reg.get_model(\"SHIFT_SALES_FORECASTER\").default.run(X=location_predictions_df, function_name=\"predict\")\n",
    "inference_df.show(3)"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.10.14"
  },
  "vscode": {
   "interpreter": {
    "hash": "87393b85a9e84f5acf85b4897642fb1f420cfcaf148d9618810d560b47e94533"
   }
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}