snowflake/tutorial/04-writing-data.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "959f612e-92e3-4bde-8f48-22ca66604625",
   "metadata": {},
   "source": [
    "# Tutorial 4: Saving Your Results"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "6506f8c9-73c3-4450-909e-af2f8db0d6a6",
   "metadata": {},
   "source": [
    "<div class=\"alert alert-block alert-info\"> <b>Before we get started: </b> \n",
    "    <ul style=\"list-style-type: none;margin: 0;padding: 0;\">\n",
    "        <li>✍️ To run this notebook, you need to have Ponder installed and set up on your machine. If you have not done so already, please refer to our <a href=\"https://docs.ponder.io/getting_started/quickstart.html\">Quickstart guide</a> to get started.</li>\n",
    "        <li>📖 Otherwise, if you're just interested in browsing through the tutorial, keep reading below!</li>\n",
    "    </ul>\n",
    "</div>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "643150ee-2715-4508-8b77-18801ca4c1c9",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "import os; os.chdir(\"..\")\n",
    "import credential\n",
    "import ponder; ponder.init()\n",
    "import modin.pandas as pd\n",
    "import snowflake.connector\n",
    "snowflake_con = snowflake.connector.connect(user=credential.params[\"user\"],password=credential.params[\"password\"],account=credential.params[\"account\"],role=credential.params[\"role\"],database=credential.params[\"database\"],schema=credential.params[\"schema\"],warehouse=credential.params[\"warehouse\"])"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "267738c2-7b12-4e77-8097-929e77e9dea5",
   "metadata": {},
   "source": [
    "Let's say that you used Ponder to run some analysis and you want to store the results back to the database. In this tutorial, we will show how you can use the ``to_sql`` command to write your dataframe to table in your database. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "f62f06a0-7270-4ab7-a5a8-339bb313537f",
   "metadata": {},
   "outputs": [],
   "source": [
    "df = pd.read_sql(\"PONDER_CITIBIKE\",con=snowflake_con)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "55bffaf2-1840-48c9-9261-27b8a8e30ec6",
   "metadata": {},
   "source": [
    "After connecting to our `PONDER_CITIBIKE` table, we see that there are a lot of records with missing values. So we drop these rows to clean up our dataset."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "758b69fb-e1d8-415d-a28f-0348c8470fba",
   "metadata": {},
   "outputs": [],
   "source": [
    "df_cleaned = df.dropna()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "2b34d144-91a8-4c2e-ab73-f16a94320fb5",
   "metadata": {},
   "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>tripduration</th>\n",
       "      <th>starttime</th>\n",
       "      <th>stoptime</th>\n",
       "      <th>bikeid</th>\n",
       "      <th>usertype</th>\n",
       "      <th>gender</th>\n",
       "      <th>start_station_name</th>\n",
       "      <th>start_station_id</th>\n",
       "      <th>end_station_name</th>\n",
       "      <th>end_station_id</th>\n",
       "      <th>start_lat</th>\n",
       "      <th>start_lng</th>\n",
       "      <th>end_lat</th>\n",
       "      <th>end_lng</th>\n",
       "      <th>birth_year</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>732.0</td>\n",
       "      <td>2019-11-06 15:11:28.721</td>\n",
       "      <td>2019-11-06 15:23:41.2970</td>\n",
       "      <td>25785.0</td>\n",
       "      <td>Subscriber</td>\n",
       "      <td>1.0</td>\n",
       "      <td>E 47 St &amp; 1 Ave</td>\n",
       "      <td>516.0</td>\n",
       "      <td>E 43 St &amp; 5 Ave</td>\n",
       "      <td>3680.0</td>\n",
       "      <td>40.752069</td>\n",
       "      <td>-73.967844</td>\n",
       "      <td>40.754121</td>\n",
       "      <td>-73.980252</td>\n",
       "      <td>1994.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>313.0</td>\n",
       "      <td>2019-02-21 17:17:19.919</td>\n",
       "      <td>2019-02-21 17:22:33.9070</td>\n",
       "      <td>26506.0</td>\n",
       "      <td>Subscriber</td>\n",
       "      <td>2.0</td>\n",
       "      <td>E 41 St &amp; Madison Ave</td>\n",
       "      <td>3235.0</td>\n",
       "      <td>Broadway &amp; W 32 St</td>\n",
       "      <td>498.0</td>\n",
       "      <td>40.752165</td>\n",
       "      <td>-73.979922</td>\n",
       "      <td>40.748549</td>\n",
       "      <td>-73.988084</td>\n",
       "      <td>1960.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>754.0</td>\n",
       "      <td>2015-07-29 06:58:54.000</td>\n",
       "      <td>7/29/2015 07:11:29</td>\n",
       "      <td>23061.0</td>\n",
       "      <td>Subscriber</td>\n",
       "      <td>1.0</td>\n",
       "      <td>W 15 St &amp; 7 Ave</td>\n",
       "      <td>482.0</td>\n",
       "      <td>E 47 St &amp; Park Ave</td>\n",
       "      <td>359.0</td>\n",
       "      <td>40.739355</td>\n",
       "      <td>-73.999318</td>\n",
       "      <td>40.755103</td>\n",
       "      <td>-73.974987</td>\n",
       "      <td>1981.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>353.0</td>\n",
       "      <td>2015-07-29 17:39:44.000</td>\n",
       "      <td>7/29/2015 17:45:37</td>\n",
       "      <td>14927.0</td>\n",
       "      <td>Subscriber</td>\n",
       "      <td>1.0</td>\n",
       "      <td>Lafayette St &amp; E 8 St</td>\n",
       "      <td>293.0</td>\n",
       "      <td>St Marks Pl &amp; 2 Ave</td>\n",
       "      <td>236.0</td>\n",
       "      <td>40.730287</td>\n",
       "      <td>-73.990765</td>\n",
       "      <td>40.728419</td>\n",
       "      <td>-73.987140</td>\n",
       "      <td>1954.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>175.0</td>\n",
       "      <td>2016-04-20 11:58:42.000</td>\n",
       "      <td>4/20/2016 12:01:38</td>\n",
       "      <td>16823.0</td>\n",
       "      <td>Subscriber</td>\n",
       "      <td>1.0</td>\n",
       "      <td>Reade St &amp; Broadway</td>\n",
       "      <td>330.0</td>\n",
       "      <td>Duane St &amp; Greenwich St</td>\n",
       "      <td>276.0</td>\n",
       "      <td>40.714505</td>\n",
       "      <td>-74.005628</td>\n",
       "      <td>40.717488</td>\n",
       "      <td>-74.010455</td>\n",
       "      <td>1948.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>118858</th>\n",
       "      <td>379.0</td>\n",
       "      <td>2019-04-30 10:48:37.091</td>\n",
       "      <td>2019-04-30 10:54:56.5590</td>\n",
       "      <td>20959.0</td>\n",
       "      <td>Customer</td>\n",
       "      <td>1.0</td>\n",
       "      <td>Canal St &amp; Rutgers St</td>\n",
       "      <td>307.0</td>\n",
       "      <td>Cleveland Pl &amp; Spring St</td>\n",
       "      <td>151.0</td>\n",
       "      <td>40.714275</td>\n",
       "      <td>-73.989900</td>\n",
       "      <td>40.722104</td>\n",
       "      <td>-73.997249</td>\n",
       "      <td>1996.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>118859</th>\n",
       "      <td>354.0</td>\n",
       "      <td>2019-02-05 18:05:10.292</td>\n",
       "      <td>2019-02-05 18:11:04.9850</td>\n",
       "      <td>26144.0</td>\n",
       "      <td>Subscriber</td>\n",
       "      <td>1.0</td>\n",
       "      <td>W 21 St &amp; 6 Ave</td>\n",
       "      <td>435.0</td>\n",
       "      <td>8 Ave &amp; W 31 St</td>\n",
       "      <td>3255.0</td>\n",
       "      <td>40.741740</td>\n",
       "      <td>-73.994156</td>\n",
       "      <td>40.750585</td>\n",
       "      <td>-73.994685</td>\n",
       "      <td>1978.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>118860</th>\n",
       "      <td>300.0</td>\n",
       "      <td>2019-02-01 18:28:37.926</td>\n",
       "      <td>2019-02-01 18:33:38.4590</td>\n",
       "      <td>19127.0</td>\n",
       "      <td>Subscriber</td>\n",
       "      <td>1.0</td>\n",
       "      <td>Lispenard St &amp; Broadway</td>\n",
       "      <td>257.0</td>\n",
       "      <td>Fulton St &amp; Broadway</td>\n",
       "      <td>319.0</td>\n",
       "      <td>40.719392</td>\n",
       "      <td>-74.002472</td>\n",
       "      <td>40.711066</td>\n",
       "      <td>-74.009447</td>\n",
       "      <td>1972.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>118862</th>\n",
       "      <td>1615.0</td>\n",
       "      <td>2017-06-09 14:15:17.000</td>\n",
       "      <td>2017-06-09 14:42:13</td>\n",
       "      <td>27010.0</td>\n",
       "      <td>Subscriber</td>\n",
       "      <td>1.0</td>\n",
       "      <td>Madison Ave &amp; E 82 St</td>\n",
       "      <td>3362.0</td>\n",
       "      <td>E 52 St &amp; 2 Ave</td>\n",
       "      <td>441.0</td>\n",
       "      <td>40.778131</td>\n",
       "      <td>-73.960694</td>\n",
       "      <td>40.756014</td>\n",
       "      <td>-73.967416</td>\n",
       "      <td>1986.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>118863</th>\n",
       "      <td>196.0</td>\n",
       "      <td>2019-11-25 08:10:51.949</td>\n",
       "      <td>2019-11-25 08:14:08.6030</td>\n",
       "      <td>38776.0</td>\n",
       "      <td>Subscriber</td>\n",
       "      <td>2.0</td>\n",
       "      <td>11 Ave &amp; W 27 St</td>\n",
       "      <td>458.0</td>\n",
       "      <td>W 22 St &amp; 10 Ave</td>\n",
       "      <td>462.0</td>\n",
       "      <td>40.751396</td>\n",
       "      <td>-74.005226</td>\n",
       "      <td>40.746920</td>\n",
       "      <td>-74.004519</td>\n",
       "      <td>1978.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>69724 rows x 15 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "        tripduration                starttime                  stoptime  \\\n",
       "0              732.0  2019-11-06 15:11:28.721  2019-11-06 15:23:41.2970   \n",
       "1              313.0  2019-02-21 17:17:19.919  2019-02-21 17:22:33.9070   \n",
       "3              754.0  2015-07-29 06:58:54.000        7/29/2015 07:11:29   \n",
       "4              353.0  2015-07-29 17:39:44.000        7/29/2015 17:45:37   \n",
       "7              175.0  2016-04-20 11:58:42.000        4/20/2016 12:01:38   \n",
       "...              ...                      ...                       ...   \n",
       "118858         379.0  2019-04-30 10:48:37.091  2019-04-30 10:54:56.5590   \n",
       "118859         354.0  2019-02-05 18:05:10.292  2019-02-05 18:11:04.9850   \n",
       "118860         300.0  2019-02-01 18:28:37.926  2019-02-01 18:33:38.4590   \n",
       "118862        1615.0  2017-06-09 14:15:17.000       2017-06-09 14:42:13   \n",
       "118863         196.0  2019-11-25 08:10:51.949  2019-11-25 08:14:08.6030   \n",
       "\n",
       "         bikeid    usertype  gender       start_station_name  \\\n",
       "0       25785.0  Subscriber     1.0          E 47 St & 1 Ave   \n",
       "1       26506.0  Subscriber     2.0    E 41 St & Madison Ave   \n",
       "3       23061.0  Subscriber     1.0          W 15 St & 7 Ave   \n",
       "4       14927.0  Subscriber     1.0    Lafayette St & E 8 St   \n",
       "7       16823.0  Subscriber     1.0      Reade St & Broadway   \n",
       "...         ...         ...     ...                      ...   \n",
       "118858  20959.0    Customer     1.0    Canal St & Rutgers St   \n",
       "118859  26144.0  Subscriber     1.0          W 21 St & 6 Ave   \n",
       "118860  19127.0  Subscriber     1.0  Lispenard St & Broadway   \n",
       "118862  27010.0  Subscriber     1.0    Madison Ave & E 82 St   \n",
       "118863  38776.0  Subscriber     2.0         11 Ave & W 27 St   \n",
       "\n",
       "        start_station_id          end_station_name  end_station_id  start_lat  \\\n",
       "0                  516.0           E 43 St & 5 Ave          3680.0  40.752069   \n",
       "1                 3235.0        Broadway & W 32 St           498.0  40.752165   \n",
       "3                  482.0        E 47 St & Park Ave           359.0  40.739355   \n",
       "4                  293.0       St Marks Pl & 2 Ave           236.0  40.730287   \n",
       "7                  330.0   Duane St & Greenwich St           276.0  40.714505   \n",
       "...                  ...                       ...             ...        ...   \n",
       "118858             307.0  Cleveland Pl & Spring St           151.0  40.714275   \n",
       "118859             435.0           8 Ave & W 31 St          3255.0  40.741740   \n",
       "118860             257.0      Fulton St & Broadway           319.0  40.719392   \n",
       "118862            3362.0           E 52 St & 2 Ave           441.0  40.778131   \n",
       "118863             458.0          W 22 St & 10 Ave           462.0  40.751396   \n",
       "\n",
       "        start_lng    end_lat    end_lng  birth_year  \n",
       "0      -73.967844  40.754121 -73.980252      1994.0  \n",
       "1      -73.979922  40.748549 -73.988084      1960.0  \n",
       "3      -73.999318  40.755103 -73.974987      1981.0  \n",
       "4      -73.990765  40.728419 -73.987140      1954.0  \n",
       "7      -74.005628  40.717488 -74.010455      1948.0  \n",
       "...           ...        ...        ...         ...  \n",
       "118858 -73.989900  40.722104 -73.997249      1996.0  \n",
       "118859 -73.994156  40.750585 -73.994685      1978.0  \n",
       "118860 -74.002472  40.711066 -74.009447      1972.0  \n",
       "118862 -73.960694  40.756014 -73.967416      1986.0  \n",
       "118863 -74.005226  40.746920 -74.004519      1978.0  \n",
       "\n",
       "[69724 rows x 15 columns]"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_cleaned"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "cb7fd65b-23d2-4f57-af56-19045d84edb9",
   "metadata": {},
   "source": [
    "Like in pandas, when you are working with a dataframe, you are always working on a temporary copy of the data. \n",
    "\n",
    "By default, all pandas operations returns a copy of the dataframe that was operated on. Ponder never make any modifications to your original data. This is especially important in the data warehouse context as the tables are often regarded as the “source of truth” and can be shared across many teams."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "4c9556ad-40d4-4596-a739-eb515413ee42",
   "metadata": {},
   "source": [
    "This also means that the reference to your dataframe df is only accessible throughout the session. That means that once you exit the session, the dataframe is no longer accessible. \n",
    "\n",
    "So if we plan to use the cleaned up data for our analysis later, we can persist it in a table via `to_sql`."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "955d8fb3-6f3c-4a4c-83bd-3fcb25ffa3d3",
   "metadata": {},
   "outputs": [],
   "source": [
    "df_cleaned.to_sql(\"PONDER_CITIBIKE_CLEANED\", con=snowflake_con, index=False, if_exists='replace')"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "8c765dbf-88f5-429f-a4a4-7c765b92ca9f",
   "metadata": {},
   "source": [
    "Now we can access new new table and continue our analysis whereever we want:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "e4eb4377-f6c8-4bbe-b0bd-9bbe19292ff8",
   "metadata": {},
   "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>tripduration</th>\n",
       "      <th>starttime</th>\n",
       "      <th>stoptime</th>\n",
       "      <th>bikeid</th>\n",
       "      <th>usertype</th>\n",
       "      <th>gender</th>\n",
       "      <th>start_station_name</th>\n",
       "      <th>start_station_id</th>\n",
       "      <th>end_station_name</th>\n",
       "      <th>end_station_id</th>\n",
       "      <th>start_lat</th>\n",
       "      <th>start_lng</th>\n",
       "      <th>end_lat</th>\n",
       "      <th>end_lng</th>\n",
       "      <th>birth_year</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>732.0</td>\n",
       "      <td>2019-11-06 15:11:28.721</td>\n",
       "      <td>2019-11-06 15:23:41.2970</td>\n",
       "      <td>25785.0</td>\n",
       "      <td>Subscriber</td>\n",
       "      <td>1.0</td>\n",
       "      <td>E 47 St &amp; 1 Ave</td>\n",
       "      <td>516.0</td>\n",
       "      <td>E 43 St &amp; 5 Ave</td>\n",
       "      <td>3680.0</td>\n",
       "      <td>40.752069</td>\n",
       "      <td>-73.967844</td>\n",
       "      <td>40.754121</td>\n",
       "      <td>-73.980252</td>\n",
       "      <td>1994.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>313.0</td>\n",
       "      <td>2019-02-21 17:17:19.919</td>\n",
       "      <td>2019-02-21 17:22:33.9070</td>\n",
       "      <td>26506.0</td>\n",
       "      <td>Subscriber</td>\n",
       "      <td>2.0</td>\n",
       "      <td>E 41 St &amp; Madison Ave</td>\n",
       "      <td>3235.0</td>\n",
       "      <td>Broadway &amp; W 32 St</td>\n",
       "      <td>498.0</td>\n",
       "      <td>40.752165</td>\n",
       "      <td>-73.979922</td>\n",
       "      <td>40.748549</td>\n",
       "      <td>-73.988084</td>\n",
       "      <td>1960.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>754.0</td>\n",
       "      <td>2015-07-29 06:58:54.000</td>\n",
       "      <td>7/29/2015 07:11:29</td>\n",
       "      <td>23061.0</td>\n",
       "      <td>Subscriber</td>\n",
       "      <td>1.0</td>\n",
       "      <td>W 15 St &amp; 7 Ave</td>\n",
       "      <td>482.0</td>\n",
       "      <td>E 47 St &amp; Park Ave</td>\n",
       "      <td>359.0</td>\n",
       "      <td>40.739355</td>\n",
       "      <td>-73.999318</td>\n",
       "      <td>40.755103</td>\n",
       "      <td>-73.974987</td>\n",
       "      <td>1981.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>353.0</td>\n",
       "      <td>2015-07-29 17:39:44.000</td>\n",
       "      <td>7/29/2015 17:45:37</td>\n",
       "      <td>14927.0</td>\n",
       "      <td>Subscriber</td>\n",
       "      <td>1.0</td>\n",
       "      <td>Lafayette St &amp; E 8 St</td>\n",
       "      <td>293.0</td>\n",
       "      <td>St Marks Pl &amp; 2 Ave</td>\n",
       "      <td>236.0</td>\n",
       "      <td>40.730287</td>\n",
       "      <td>-73.990765</td>\n",
       "      <td>40.728419</td>\n",
       "      <td>-73.987140</td>\n",
       "      <td>1954.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>175.0</td>\n",
       "      <td>2016-04-20 11:58:42.000</td>\n",
       "      <td>4/20/2016 12:01:38</td>\n",
       "      <td>16823.0</td>\n",
       "      <td>Subscriber</td>\n",
       "      <td>1.0</td>\n",
       "      <td>Reade St &amp; Broadway</td>\n",
       "      <td>330.0</td>\n",
       "      <td>Duane St &amp; Greenwich St</td>\n",
       "      <td>276.0</td>\n",
       "      <td>40.714505</td>\n",
       "      <td>-74.005628</td>\n",
       "      <td>40.717488</td>\n",
       "      <td>-74.010455</td>\n",
       "      <td>1948.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>69719</th>\n",
       "      <td>379.0</td>\n",
       "      <td>2019-04-30 10:48:37.091</td>\n",
       "      <td>2019-04-30 10:54:56.5590</td>\n",
       "      <td>20959.0</td>\n",
       "      <td>Customer</td>\n",
       "      <td>1.0</td>\n",
       "      <td>Canal St &amp; Rutgers St</td>\n",
       "      <td>307.0</td>\n",
       "      <td>Cleveland Pl &amp; Spring St</td>\n",
       "      <td>151.0</td>\n",
       "      <td>40.714275</td>\n",
       "      <td>-73.989900</td>\n",
       "      <td>40.722104</td>\n",
       "      <td>-73.997249</td>\n",
       "      <td>1996.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>69720</th>\n",
       "      <td>354.0</td>\n",
       "      <td>2019-02-05 18:05:10.292</td>\n",
       "      <td>2019-02-05 18:11:04.9850</td>\n",
       "      <td>26144.0</td>\n",
       "      <td>Subscriber</td>\n",
       "      <td>1.0</td>\n",
       "      <td>W 21 St &amp; 6 Ave</td>\n",
       "      <td>435.0</td>\n",
       "      <td>8 Ave &amp; W 31 St</td>\n",
       "      <td>3255.0</td>\n",
       "      <td>40.741740</td>\n",
       "      <td>-73.994156</td>\n",
       "      <td>40.750585</td>\n",
       "      <td>-73.994685</td>\n",
       "      <td>1978.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>69721</th>\n",
       "      <td>300.0</td>\n",
       "      <td>2019-02-01 18:28:37.926</td>\n",
       "      <td>2019-02-01 18:33:38.4590</td>\n",
       "      <td>19127.0</td>\n",
       "      <td>Subscriber</td>\n",
       "      <td>1.0</td>\n",
       "      <td>Lispenard St &amp; Broadway</td>\n",
       "      <td>257.0</td>\n",
       "      <td>Fulton St &amp; Broadway</td>\n",
       "      <td>319.0</td>\n",
       "      <td>40.719392</td>\n",
       "      <td>-74.002472</td>\n",
       "      <td>40.711066</td>\n",
       "      <td>-74.009447</td>\n",
       "      <td>1972.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>69722</th>\n",
       "      <td>1615.0</td>\n",
       "      <td>2017-06-09 14:15:17.000</td>\n",
       "      <td>2017-06-09 14:42:13</td>\n",
       "      <td>27010.0</td>\n",
       "      <td>Subscriber</td>\n",
       "      <td>1.0</td>\n",
       "      <td>Madison Ave &amp; E 82 St</td>\n",
       "      <td>3362.0</td>\n",
       "      <td>E 52 St &amp; 2 Ave</td>\n",
       "      <td>441.0</td>\n",
       "      <td>40.778131</td>\n",
       "      <td>-73.960694</td>\n",
       "      <td>40.756014</td>\n",
       "      <td>-73.967416</td>\n",
       "      <td>1986.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>69723</th>\n",
       "      <td>196.0</td>\n",
       "      <td>2019-11-25 08:10:51.949</td>\n",
       "      <td>2019-11-25 08:14:08.6030</td>\n",
       "      <td>38776.0</td>\n",
       "      <td>Subscriber</td>\n",
       "      <td>2.0</td>\n",
       "      <td>11 Ave &amp; W 27 St</td>\n",
       "      <td>458.0</td>\n",
       "      <td>W 22 St &amp; 10 Ave</td>\n",
       "      <td>462.0</td>\n",
       "      <td>40.751396</td>\n",
       "      <td>-74.005226</td>\n",
       "      <td>40.746920</td>\n",
       "      <td>-74.004519</td>\n",
       "      <td>1978.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>69724 rows x 15 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "       tripduration                starttime                  stoptime  \\\n",
       "0             732.0  2019-11-06 15:11:28.721  2019-11-06 15:23:41.2970   \n",
       "1             313.0  2019-02-21 17:17:19.919  2019-02-21 17:22:33.9070   \n",
       "2             754.0  2015-07-29 06:58:54.000        7/29/2015 07:11:29   \n",
       "3             353.0  2015-07-29 17:39:44.000        7/29/2015 17:45:37   \n",
       "4             175.0  2016-04-20 11:58:42.000        4/20/2016 12:01:38   \n",
       "...             ...                      ...                       ...   \n",
       "69719         379.0  2019-04-30 10:48:37.091  2019-04-30 10:54:56.5590   \n",
       "69720         354.0  2019-02-05 18:05:10.292  2019-02-05 18:11:04.9850   \n",
       "69721         300.0  2019-02-01 18:28:37.926  2019-02-01 18:33:38.4590   \n",
       "69722        1615.0  2017-06-09 14:15:17.000       2017-06-09 14:42:13   \n",
       "69723         196.0  2019-11-25 08:10:51.949  2019-11-25 08:14:08.6030   \n",
       "\n",
       "        bikeid    usertype  gender       start_station_name  start_station_id  \\\n",
       "0      25785.0  Subscriber     1.0          E 47 St & 1 Ave             516.0   \n",
       "1      26506.0  Subscriber     2.0    E 41 St & Madison Ave            3235.0   \n",
       "2      23061.0  Subscriber     1.0          W 15 St & 7 Ave             482.0   \n",
       "3      14927.0  Subscriber     1.0    Lafayette St & E 8 St             293.0   \n",
       "4      16823.0  Subscriber     1.0      Reade St & Broadway             330.0   \n",
       "...        ...         ...     ...                      ...               ...   \n",
       "69719  20959.0    Customer     1.0    Canal St & Rutgers St             307.0   \n",
       "69720  26144.0  Subscriber     1.0          W 21 St & 6 Ave             435.0   \n",
       "69721  19127.0  Subscriber     1.0  Lispenard St & Broadway             257.0   \n",
       "69722  27010.0  Subscriber     1.0    Madison Ave & E 82 St            3362.0   \n",
       "69723  38776.0  Subscriber     2.0         11 Ave & W 27 St             458.0   \n",
       "\n",
       "               end_station_name  end_station_id  start_lat  start_lng  \\\n",
       "0               E 43 St & 5 Ave          3680.0  40.752069 -73.967844   \n",
       "1            Broadway & W 32 St           498.0  40.752165 -73.979922   \n",
       "2            E 47 St & Park Ave           359.0  40.739355 -73.999318   \n",
       "3           St Marks Pl & 2 Ave           236.0  40.730287 -73.990765   \n",
       "4       Duane St & Greenwich St           276.0  40.714505 -74.005628   \n",
       "...                         ...             ...        ...        ...   \n",
       "69719  Cleveland Pl & Spring St           151.0  40.714275 -73.989900   \n",
       "69720           8 Ave & W 31 St          3255.0  40.741740 -73.994156   \n",
       "69721      Fulton St & Broadway           319.0  40.719392 -74.002472   \n",
       "69722           E 52 St & 2 Ave           441.0  40.778131 -73.960694   \n",
       "69723          W 22 St & 10 Ave           462.0  40.751396 -74.005226   \n",
       "\n",
       "         end_lat    end_lng  birth_year  \n",
       "0      40.754121 -73.980252      1994.0  \n",
       "1      40.748549 -73.988084      1960.0  \n",
       "2      40.755103 -73.974987      1981.0  \n",
       "3      40.728419 -73.987140      1954.0  \n",
       "4      40.717488 -74.010455      1948.0  \n",
       "...          ...        ...         ...  \n",
       "69719  40.722104 -73.997249      1996.0  \n",
       "69720  40.750585 -73.994685      1978.0  \n",
       "69721  40.711066 -74.009447      1972.0  \n",
       "69722  40.756014 -73.967416      1986.0  \n",
       "69723  40.746920 -74.004519      1978.0  \n",
       "\n",
       "[69724 rows x 15 columns]"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.read_sql(\"PONDER_CITIBIKE_CLEANED\",con=snowflake_con)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "faa0e41a-c232-4fe1-a85c-e81a7ad573ad",
   "metadata": {},
   "source": [
    "# Summary\n",
    "\n",
    "In this tutorial, we learned how to leverage the same pandas API for `pd.to_sql` to save your dataframe to your database. This is often useful if you want to persist the work done on the dataframe beyond your current session. \n",
    "\n",
    "In our [next tutorial](https://github.com/ponder-org/ponder-notebooks/blob/main/snowflake/tutorial/05-advanced-sql.ipynb), we will discuss how you can easily move between using Ponder and using SQL when developing your data workflows."
   ]
  }
 ],
 "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.6"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}