SCD_maintnance_keyMan.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 77,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "The sql extension is already loaded. To reload it, use:\n",
      "  %reload_ext sql\n"
     ]
    }
   ],
   "source": [
    "#libraries that we need to use \n",
    "from sqlalchemy import create_engine\n",
    "import pgspecial\n",
    "#load sql\n",
    "%load_ext sql\n",
    "#we get access to the dabatabase \n",
    "%sql postgresql://postgres:arnold@localhost/partsunlimited"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# SCD Maintnance - Key Management"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "In this document, I am going to demonstrate scd maintenance and how to manage pk,sk, and increment automatically and default values for scd fields"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 1. ***SCD Type 0 - Ignore any changes*** "
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "In this type scd we need to make sure that there will not be anychaging. Normally I will not use this type because I believe there is going to be always some change\n",
    "in the data but since this is a project requirement. I used month time dimension. Basically we will not change anything in this table, we will ignore it"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 2. ***SCD Type 1 - Overwrite the changes*** "
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "For scd one we check for unique keys and when we find the key we update the values \n",
    "- When there is a dublicate key we need dedect and over write \n",
    "- if the key is like a combination of ddmmyyyy you will end up with the same key and if it is in the dabase you can overwrite \n",
    "\n",
    "In this example we are going to change original date is pk 20200210 (2020-02-10) with same code pk 20200210 (2020-02-12).Then we are going to delete the record so that we do keep the original data "
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**origianal record**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 78,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "[(20200210, 12, 2, 2020, datetime.datetime(2020, 2, 10, 0, 0))]\n"
     ]
    }
   ],
   "source": [
    "import psycopg2 as pg # PostgreSQL\n",
    "# from psycopg2 import  extensions\n",
    "#establishing the connection\n",
    "conn = pg.connect(\n",
    "    host=\"localhost\",\n",
    "    database=\"partsunlimited\",\n",
    "    user=\"postgres\",\n",
    "    password=\"arnold\")\n",
    "#Creating a cursor object using the cursor() method\n",
    "cursor = conn.cursor()\n",
    "#Droping if the table if already exists.\n",
    "\n",
    "#altering the table for the constraint\n",
    "query =''' SELECT * \n",
    "FROM date_dim\n",
    "where date_dim.date_id = 20200210  ;\n",
    "'''\n",
    "#executing the query\n",
    "cursor.execute(query)\n",
    "conn.commit()\n",
    "\n",
    "tables = cursor.fetchall()\n",
    "print(tables)"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We use sql query to update the record below is the single record update but we can also create a logic to overwrite multiple records, showen in the second rcode cell"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 79,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * postgresql://postgres:***@localhost/partsunlimited\n",
      "(psycopg2.errors.SyntaxError) syntax error at or near \"#\"\n",
      "LINE 1: # updating single code \n",
      "        ^\n",
      "\n",
      "[SQL: # updating single code \n",
      "# UPDATE date_dim\n",
      "# SET month = 12\n",
      "#     date  = 2\n",
      "#     year = 2020\n",
      "#     date = '2020-02-10'::date\n",
      "# WHERE customer_id = 20200210;]\n",
      "(Background on this error at: https://sqlalche.me/e/20/f405)\n"
     ]
    }
   ],
   "source": [
    "%%sql \n",
    "# updating single code \n",
    "# UPDATE date_dim\n",
    "# SET month = 12\n",
    "#     date  = 2\n",
    "#     year = 2020\n",
    "#     date = '2020-02-10'::date\n",
    "# WHERE customer_id = 20200210;"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "in here we update table with new records dated 2025 "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 80,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Table has been altered successfully........\n"
     ]
    }
   ],
   "source": [
    "import psycopg2 as pg # PostgreSQL\n",
    "# from psycopg2 import  extensions\n",
    "#establishing the connection\n",
    "conn = pg.connect(\n",
    "    host=\"localhost\",\n",
    "    database=\"partsunlimited\",\n",
    "    user=\"postgres\",\n",
    "    password=\"arnold\")\n",
    "#Creating a cursor object using the cursor() method\n",
    "cursor = conn.cursor()\n",
    "#Droping if the table if already exists.\n",
    "\n",
    "#altering the table for the constraint\n",
    "query =''' INSERT INTO date_dim (date_id, day, month, \n",
    "year, date)\n",
    "VALUES  (20200210, 12, 2, 2020, '2020-02-10'::date),\n",
    "  (20250225, 25, 2, 2025, '2025-02-25'::date),\n",
    "  (20250214, 14, 2, 2025, '2025-02-14'::date)\n",
    "ON CONFLICT (date_id) DO UPDATE SET\n",
    "  day = excluded.day,\n",
    "  month = excluded.month,\n",
    "  year = excluded.year,\n",
    "date = excluded.date;\n",
    "\n",
    "'''\n",
    "##Creating a database\n",
    "cursor.execute(query)\n",
    "print(\"Table has been altered successfully........\")\n",
    "conn.commit()\n"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "we run same query again to see if  our value is updated "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 81,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Table has been altered successfully........\n",
      "[(20200210, 12, 2, 2020, datetime.datetime(2020, 2, 10, 0, 0))]\n"
     ]
    }
   ],
   "source": [
    "import psycopg2 as pg # PostgreSQL\n",
    "# from psycopg2 import  extensions\n",
    "#establishing the connection\n",
    "conn = pg.connect(\n",
    "    host=\"localhost\",\n",
    "    database=\"partsunlimited\",\n",
    "    user=\"postgres\",\n",
    "    password=\"arnold\")\n",
    "#Creating a cursor object using the cursor() method\n",
    "cursor = conn.cursor()\n",
    "#Droping if the table if already exists.\n",
    "\n",
    "#altering the table for the constraint\n",
    "query =''' SELECT * \n",
    "FROM date_dim\n",
    "where date_dim.date_id = 20200210  ;\n",
    "\n",
    "'''\n",
    "##Creating a database\n",
    "cursor.execute(query)\n",
    "print(\"Table has been altered successfully........\")\n",
    "conn.commit()\n",
    "\n",
    "tables = cursor.fetchall()\n",
    "print(tables)"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "now we can delete the 2025 records because there is no matching record in our database. We use in operator to specify the pk that we want to delete"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 82,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * postgresql://postgres:***@localhost/partsunlimited\n",
      "2 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "[]"
      ]
     },
     "execution_count": 82,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "DELETE FROM date_dim\n",
    "WHERE date_id IN (20250225, 20250214);\n"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "----------------"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 3. SCD Type 2 (always adding, not updating, recoding record history)"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "I picked ev charging stations as scd type 2 because we want to keep track of all the changes in the table and we believe \n",
    "- the station data might change over time and we want to full history of it, we are interested to know all the changes in table \n",
    "-  this will allow us an accurate reporting \n",
    "- this will help when we audit our records\n",
    "I picked the record below and the change we are going to implement is that their status_code is going to be closed (status code shows if they are currently open or not)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 83,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * postgresql://postgres:***@localhost/partsunlimited\n",
      "1 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <thead>\n",
       "        <tr>\n",
       "            <th>ev_cs_dim_id</th>\n",
       "            <th>sk_ev_dim_id</th>\n",
       "            <th>charging_station_id</th>\n",
       "            <th>station_name</th>\n",
       "            <th>status_code</th>\n",
       "            <th>access_code</th>\n",
       "            <th>currency</th>\n",
       "            <th>row_effective_date</th>\n",
       "            <th>row_expiration_date</th>\n",
       "            <th>row_status</th>\n",
       "        </tr>\n",
       "    </thead>\n",
       "    <tbody>\n",
       "        <tr>\n",
       "            <td>24</td>\n",
       "            <td>24</td>\n",
       "            <td>7184</td>\n",
       "            <td>Los Angeles International Airport (LAX) - Lot 6</td>\n",
       "            <td>open</td>\n",
       "            <td>public</td>\n",
       "            <td>USD</td>\n",
       "            <td>2023-04-21</td>\n",
       "            <td>None</td>\n",
       "            <td>active</td>\n",
       "        </tr>\n",
       "    </tbody>\n",
       "</table>"
      ],
      "text/plain": [
       "[(24, 24, 7184, 'Los Angeles International Airport (LAX) - Lot 6', 'open', 'public', 'USD', datetime.date(2023, 4, 21), None, 'active')]"
      ]
     },
     "execution_count": 83,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "\n",
    "SELECT * \n",
    "FROM ev_charging_stations\n",
    "where ev_charging_stations.ev_cs_dim_id = 24 "
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "how do we know if there is any change? <br>\n",
    "There are multiple ways to dedect the change\n",
    "1.  we can scan the data (station name, status_sode, access_code, currency)\n",
    "2.  if there is no match we instert the data \n",
    "3.  if there is a match it is a dublicate record"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 84,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The len(delta_df) index value ensures that the new row is added to the end of the DataFrame, regardless of the current number of rows in the DataFrame"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 162,
   "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>charging_station_id</th>\n",
       "      <th>station_name</th>\n",
       "      <th>status_code</th>\n",
       "      <th>access_code</th>\n",
       "      <th>currency</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>7184</td>\n",
       "      <td>Los Angeles International Airport (LAX) - Lot 6</td>\n",
       "      <td>closed</td>\n",
       "      <td>public</td>\n",
       "      <td>USD</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1234</td>\n",
       "      <td>Fake Station 1</td>\n",
       "      <td>closed</td>\n",
       "      <td>private</td>\n",
       "      <td>EUR</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>5678</td>\n",
       "      <td>Fake Station 2</td>\n",
       "      <td>open</td>\n",
       "      <td>public</td>\n",
       "      <td>GBP</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>9101</td>\n",
       "      <td>Fake Station 3</td>\n",
       "      <td>under maintenance</td>\n",
       "      <td>private</td>\n",
       "      <td>USD</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   charging_station_id                                     station_name  \\\n",
       "0                 7184  Los Angeles International Airport (LAX) - Lot 6   \n",
       "1                 1234                                   Fake Station 1   \n",
       "2                 5678                                   Fake Station 2   \n",
       "3                 9101                                   Fake Station 3   \n",
       "\n",
       "         status_code access_code currency  \n",
       "0             closed      public      USD  \n",
       "1             closed     private      EUR  \n",
       "2               open      public      GBP  \n",
       "3  under maintenance     private      USD  "
      ]
     },
     "execution_count": 162,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "delta_df = pd.DataFrame(columns=['charging_station_id', 'station_name'\t,'status_code',\t'access_code',\t'currency'])\n",
    "delta_df.loc[len(delta_df)]= [7184,'Los Angeles International Airport (LAX) - Lot 6',\t'closed'\t,'public',\t'USD']\n",
    "delta_df.loc[len(delta_df)] = [1234, 'Fake Station 1', 'closed', 'private', 'EUR']\n",
    "delta_df.loc[len(delta_df)] = [5678, 'Fake Station 2', 'open', 'public', 'GBP']\n",
    "delta_df.loc[len(delta_df)] = [9101, 'Fake Station 3', 'under maintenance', 'private', 'USD']\n",
    "delta_df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 115,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "query excecuted\n"
     ]
    }
   ],
   "source": [
    "import psycopg2 as pg # PostgreSQL\n",
    "# from psycopg2 import  extensions\n",
    "#establishing the connection\n",
    "conn = pg.connect(host=\"localhost\", database=\"partsunlimited\",user=\"postgres\",password=\"arnold\")\n",
    "#Creating a cursor object using the cursor() method\n",
    "cursor = conn.cursor()\n",
    "#queriying \n",
    "query =''' SELECT * \n",
    "FROM ev_charging_stations;\n",
    "'''\n",
    "cursor.execute(query)\n",
    "conn.commit()\n",
    "\n",
    "tables = cursor.fetchall()\n",
    "columns = ['ev_cs_dim_id', 'sk_ev_dim_id', 'charging_station_id', \n",
    "           'station_name', 'status_code', 'access_code', 'currency', \n",
    "           'row_effective_date','row_expiration_date', 'row_status']\n",
    "database_df = pd.DataFrame(tables, columns=columns)\n",
    "print('query excecuted')\n",
    "conn.close()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 116,
   "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>ev_cs_dim_id</th>\n",
       "      <th>sk_ev_dim_id</th>\n",
       "      <th>charging_station_id</th>\n",
       "      <th>station_name</th>\n",
       "      <th>status_code</th>\n",
       "      <th>access_code</th>\n",
       "      <th>currency</th>\n",
       "      <th>row_effective_date</th>\n",
       "      <th>row_expiration_date</th>\n",
       "      <th>row_status</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1517</td>\n",
       "      <td>LADWP - Truesdale Center</td>\n",
       "      <td>open</td>\n",
       "      <td>private</td>\n",
       "      <td>USD</td>\n",
       "      <td>2023-04-21</td>\n",
       "      <td>None</td>\n",
       "      <td>active</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>1519</td>\n",
       "      <td>LADWP - West LA District Office</td>\n",
       "      <td>open</td>\n",
       "      <td>private</td>\n",
       "      <td>USD</td>\n",
       "      <td>2023-04-21</td>\n",
       "      <td>None</td>\n",
       "      <td>active</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "      <td>1520</td>\n",
       "      <td>Southern California Edison - Rosemead Office B...</td>\n",
       "      <td>temprorally_closed</td>\n",
       "      <td>private</td>\n",
       "      <td>USD</td>\n",
       "      <td>2023-04-21</td>\n",
       "      <td>None</td>\n",
       "      <td>active</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>1523</td>\n",
       "      <td>Los Angeles Convention Center</td>\n",
       "      <td>open</td>\n",
       "      <td>public</td>\n",
       "      <td>USD</td>\n",
       "      <td>2023-04-21</td>\n",
       "      <td>None</td>\n",
       "      <td>active</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>5</td>\n",
       "      <td>5</td>\n",
       "      <td>1525</td>\n",
       "      <td>LADWP - John Ferraro Building</td>\n",
       "      <td>open</td>\n",
       "      <td>private</td>\n",
       "      <td>USD</td>\n",
       "      <td>2023-04-21</td>\n",
       "      <td>None</td>\n",
       "      <td>active</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   ev_cs_dim_id  sk_ev_dim_id  charging_station_id  \\\n",
       "0             1             1                 1517   \n",
       "1             2             2                 1519   \n",
       "2             3             3                 1520   \n",
       "3             4             4                 1523   \n",
       "4             5             5                 1525   \n",
       "\n",
       "                                        station_name         status_code  \\\n",
       "0                           LADWP - Truesdale Center                open   \n",
       "1                    LADWP - West LA District Office                open   \n",
       "2  Southern California Edison - Rosemead Office B...  temprorally_closed   \n",
       "3                      Los Angeles Convention Center                open   \n",
       "4                      LADWP - John Ferraro Building                open   \n",
       "\n",
       "  access_code currency row_effective_date row_expiration_date row_status  \n",
       "0     private      USD         2023-04-21                None     active  \n",
       "1     private      USD         2023-04-21                None     active  \n",
       "2     private      USD         2023-04-21                None     active  \n",
       "3      public      USD         2023-04-21                None     active  \n",
       "4     private      USD         2023-04-21                None     active  "
      ]
     },
     "execution_count": 116,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "database_df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 118,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index(['ev_cs_dim_id', 'sk_ev_dim_id', 'charging_station_id', 'station_name',\n",
       "       'status_code', 'access_code', 'currency', 'row_effective_date',\n",
       "       'row_expiration_date', 'row_status'],\n",
       "      dtype='object')"
      ]
     },
     "execution_count": 118,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "database_df.columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 119,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "47087\n",
      "47087\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "0"
      ]
     },
     "execution_count": 119,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "print(database_df['charging_station_id'].nunique() )\n",
    "print(len(database_df))\n",
    "\n",
    "database_df['charging_station_id'].nunique()- len(database_df)\n",
    "delta_df.dtypes\n",
    "database_df['charging_station_id'].isnull().sum()"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Now we can compare by left joinning our data "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 163,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_merged = pd.merge(delta_df,database_df, how='outer', on = ['charging_station_id'], indicator=True)"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "left only it means there is no match , so it is means there is no match<br>\n",
    "we can simply insert these records to the database \n",
    "(I used charging stations as indicator but normally we could use a combo like station_name, city, state but for this we need to run the fact table and i need location information etc. To simplify this process I am using the source code indicator charging station id.) "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 203,
   "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>charging_station_id</th>\n",
       "      <th>station_name</th>\n",
       "      <th>status_code</th>\n",
       "      <th>access_code</th>\n",
       "      <th>currency</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1234</td>\n",
       "      <td>Fake Station 1</td>\n",
       "      <td>closed</td>\n",
       "      <td>private</td>\n",
       "      <td>EUR</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>5678</td>\n",
       "      <td>Fake Station 2</td>\n",
       "      <td>open</td>\n",
       "      <td>public</td>\n",
       "      <td>GBP</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>9101</td>\n",
       "      <td>Fake Station 3</td>\n",
       "      <td>under maintenance</td>\n",
       "      <td>private</td>\n",
       "      <td>USD</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   charging_station_id    station_name        status_code access_code currency\n",
       "1                 1234  Fake Station 1             closed     private      EUR\n",
       "2                 5678  Fake Station 2               open      public      GBP\n",
       "3                 9101  Fake Station 3  under maintenance     private      USD"
      ]
     },
     "execution_count": 203,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "new_record_index_list = df_merged.loc[df_merged['_merge'] =='left_only', :].index\n",
    "new_records= delta_df.iloc[new_record_index_list]\n",
    "new_records"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 228,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * postgresql://postgres:***@localhost/partsunlimited\n",
      "Done.\n",
      "1 rows affected.\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "[]"
      ]
     },
     "execution_count": 228,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "-- create sequence\n",
    "CREATE SEQUENCE ev_cs_dim_id_seq OWNED BY public.ev_charging_stations.ev_cs_dim_id;\n",
    "\n",
    "-- set the current value of the sequence to the max value from that column\n",
    "-- (ev_cs_dim_id column in this scenario)\n",
    "SELECT setval('ev_cs_dim_id_seq', COALESCE((SELECT MAX(ev_cs_dim_id) FROM public.ev_charging_stations), 0), false);\n",
    "\n",
    "-- use sequence for the target column\n",
    "ALTER TABLE public.ev_charging_stations ALTER COLUMN ev_cs_dim_id SET DEFAULT nextval('ev_cs_dim_id_seq');\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 229,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * postgresql://postgres:***@localhost/partsunlimited\n",
      "Done.\n",
      "1 rows affected.\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "[]"
      ]
     },
     "execution_count": 229,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "-- create a sequence for sk_ev_dim_id\n",
    "CREATE SEQUENCE sk_ev_dim_id_seq OWNED BY ev_charging_stations.sk_ev_dim_id;\n",
    "\n",
    "-- set the current value of the sequence to the max value from that column\n",
    "-- (sk_ev_dim_id column in this scenario)\n",
    "SELECT SETVAL('sk_ev_dim_id_seq', (SELECT MAX(sk_ev_dim_id) FROM ev_charging_stations), false);\n",
    "\n",
    "-- use sequence for the target column\n",
    "ALTER TABLE ev_charging_stations ALTER COLUMN sk_ev_dim_id SET DEFAULT nextval('sk_ev_dim_id_seq');\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 231,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * postgresql://postgres:***@localhost/partsunlimited\n",
      "0 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <thead>\n",
       "        <tr>\n",
       "            <th>ev_cs_dim_id</th>\n",
       "            <th>sk_ev_dim_id</th>\n",
       "            <th>charging_station_id</th>\n",
       "            <th>station_name</th>\n",
       "            <th>status_code</th>\n",
       "            <th>access_code</th>\n",
       "            <th>currency</th>\n",
       "            <th>row_effective_date</th>\n",
       "            <th>row_expiration_date</th>\n",
       "            <th>row_status</th>\n",
       "        </tr>\n",
       "    </thead>\n",
       "    <tbody>\n",
       "    </tbody>\n",
       "</table>"
      ],
      "text/plain": [
       "[]"
      ]
     },
     "execution_count": 231,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "SELECT * FROM ev_charging_stations WHERE sk_ev_dim_id IS NULL;"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 207,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * postgresql://postgres:***@localhost/partsunlimited\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "[]"
      ]
     },
     "execution_count": 207,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "-- add the DEFAULT constraint to the existing column\n",
    "ALTER TABLE ev_charging_stations\n",
    "ALTER COLUMN row_effective_date SET DEFAULT CURRENT_TIMESTAMP;\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 208,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * postgresql://postgres:***@localhost/partsunlimited\n",
      "(psycopg2.errors.DuplicateColumn) column \"row_expiration_date\" of relation \"ev_charging_stations\" already exists\n",
      "\n",
      "[SQL: -- add the new column with a default value of NULL\n",
      "ALTER TABLE ev_charging_stations\n",
      "ADD COLUMN row_expiration_date DATE DEFAULT NULL;]\n",
      "(Background on this error at: https://sqlalche.me/e/20/f405)\n"
     ]
    }
   ],
   "source": [
    "%%sql\n",
    "-- add the new column with a default value of NULL\n",
    "ALTER TABLE ev_charging_stations\n",
    "ADD COLUMN row_expiration_date DATE DEFAULT NULL;\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 213,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * postgresql://postgres:***@localhost/partsunlimited\n",
      "(psycopg2.errors.SyntaxError) syntax error at or near \"PRIMARY\"\n",
      "LINE 2: ALTER COLUMN sk_ev_dim_id SET DATA TYPE SERIAL PRIMARY KEY;\n",
      "                                                       ^\n",
      "\n",
      "[SQL: ALTER TABLE ev_charging_stations\n",
      "ALTER COLUMN sk_ev_dim_id SET DATA TYPE SERIAL PRIMARY KEY;]\n",
      "(Background on this error at: https://sqlalche.me/e/20/f405)\n"
     ]
    }
   ],
   "source": [
    "%%sql\n",
    "ALTER TABLE ev_charging_stations\n",
    "ALTER COLUMN sk_ev_dim_id SET DATA TYPE SERIAL PRIMARY KEY;"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 232,
   "metadata": {},
   "outputs": [],
   "source": [
    "from sqlalchemy import create_engine\n",
    "# establish connections\n",
    "conn_string = 'postgresql://postgres:arnold@localhost/partsunlimited'\n",
    "  \n",
    "db = create_engine(conn_string)\n",
    "conn = db.connect()\n",
    "print\n",
    "#converting data to sql\n",
    "new_records.to_sql('ev_charging_stations', conn, if_exists= 'append', index=False)\n",
    "conn.commit()\n",
    "db.dispose() \n",
    "conn.close()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 233,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * postgresql://postgres:***@localhost/partsunlimited\n",
      "3 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <thead>\n",
       "        <tr>\n",
       "            <th>ev_cs_dim_id</th>\n",
       "            <th>sk_ev_dim_id</th>\n",
       "            <th>charging_station_id</th>\n",
       "            <th>station_name</th>\n",
       "            <th>status_code</th>\n",
       "            <th>access_code</th>\n",
       "            <th>currency</th>\n",
       "            <th>row_effective_date</th>\n",
       "            <th>row_expiration_date</th>\n",
       "            <th>row_status</th>\n",
       "        </tr>\n",
       "    </thead>\n",
       "    <tbody>\n",
       "        <tr>\n",
       "            <td>47087</td>\n",
       "            <td>47087</td>\n",
       "            <td>1234</td>\n",
       "            <td>Fake Station 1</td>\n",
       "            <td>closed</td>\n",
       "            <td>private</td>\n",
       "            <td>EUR</td>\n",
       "            <td>2023-04-25</td>\n",
       "            <td>None</td>\n",
       "            <td>None</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>47088</td>\n",
       "            <td>47088</td>\n",
       "            <td>5678</td>\n",
       "            <td>Fake Station 2</td>\n",
       "            <td>open</td>\n",
       "            <td>public</td>\n",
       "            <td>GBP</td>\n",
       "            <td>2023-04-25</td>\n",
       "            <td>None</td>\n",
       "            <td>None</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>47089</td>\n",
       "            <td>47089</td>\n",
       "            <td>9101</td>\n",
       "            <td>Fake Station 3</td>\n",
       "            <td>under maintenance</td>\n",
       "            <td>private</td>\n",
       "            <td>USD</td>\n",
       "            <td>2023-04-25</td>\n",
       "            <td>None</td>\n",
       "            <td>None</td>\n",
       "        </tr>\n",
       "    </tbody>\n",
       "</table>"
      ],
      "text/plain": [
       "[(47087, 47087, 1234, 'Fake Station 1', 'closed', 'private', 'EUR', datetime.date(2023, 4, 25), None, None),\n",
       " (47088, 47088, 5678, 'Fake Station 2', 'open', 'public', 'GBP', datetime.date(2023, 4, 25), None, None),\n",
       " (47089, 47089, 9101, 'Fake Station 3', 'under maintenance', 'private', 'USD', datetime.date(2023, 4, 25), None, None)]"
      ]
     },
     "execution_count": 233,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "SELECT *\n",
    "FROM ev_charging_stations\n",
    "WHERE row_effective_date = '2023-04-25'\n"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "these is the dublicated values if there is not change in the data we skip but if there is any change we need to update our records by inserting a new row with thechanges\n",
    "\n",
    "compare method :\n",
    "\n",
    "keep_shape: This parameter ensures that the output dataframe has the same shape as the original dataframes, even if there are differences in the values. Set it to True to keep the shape, or False to show only the differences.\n",
    "\n",
    "keep_equal: This parameter specifies whether to include rows that are the same in both dataframes. Set it to True to keep the rows that are equal, or False to show only the differences"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 183,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Index(['station_name', 'status_code', 'access_code', 'currency'], dtype='object')\n",
      "Index(['station_name', 'status_code', 'access_code', 'currency'], dtype='object')\n",
      "Int64Index([7184], dtype='int64', name='charging_station_id')\n",
      "Int64Index([7184], dtype='int64', name='charging_station_id')\n"
     ]
    },
    {
     "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 tr th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe thead tr:last-of-type th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr>\n",
       "      <th></th>\n",
       "      <th colspan=\"2\" halign=\"left\">station_name</th>\n",
       "      <th colspan=\"2\" halign=\"left\">status_code</th>\n",
       "      <th colspan=\"2\" halign=\"left\">access_code</th>\n",
       "      <th colspan=\"2\" halign=\"left\">currency</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th></th>\n",
       "      <th>self</th>\n",
       "      <th>other</th>\n",
       "      <th>self</th>\n",
       "      <th>other</th>\n",
       "      <th>self</th>\n",
       "      <th>other</th>\n",
       "      <th>self</th>\n",
       "      <th>other</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>charging_station_id</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>7184</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>closed</td>\n",
       "      <td>open</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                    station_name       status_code       access_code        \\\n",
       "                            self other        self other        self other   \n",
       "charging_station_id                                                          \n",
       "7184                         NaN   NaN      closed  open         NaN   NaN   \n",
       "\n",
       "                    currency        \n",
       "                        self other  \n",
       "charging_station_id                 \n",
       "7184                     NaN   NaN  "
      ]
     },
     "execution_count": 183,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# get the list of the dublicated records \n",
    "dublicated = [df_merged.loc[df_merged['_merge'] =='both', :]['charging_station_id'].values]\n",
    "x = delta_df.loc[delta_df['charging_station_id'].isin(dublicated), :].set_index('charging_station_id')\n",
    "y = database_df.loc[database_df['charging_station_id'].isin(dublicated), :]\n",
    "y = y[['charging_station_id','station_name'\t,'status_code',\t'access_code',\t'currency']].set_index('charging_station_id')\n",
    "print(x.columns)\n",
    "print(y.columns)\n",
    "print(x.index)\n",
    "print(y.index)\n",
    "comparison_df = x.compare(y, keep_shape=True, keep_equal=False)\n",
    "comparison_df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 262,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "   charging_station_id                                     station_name  \\\n",
      "0                 7184  Los Angeles International Airport (LAX) - Lot 6   \n",
      "\n",
      "  status_code access_code currency  \n",
      "0      closed      public      USD  \n",
      "24\n",
      "24\n"
     ]
    }
   ],
   "source": [
    "# this is going to be the new row that we are goign to create\n",
    "change_required_df = delta_df.loc[delta_df['charging_station_id']==7184]\n",
    "print(change_required_df)\n",
    "\n",
    "\n",
    "# getting pk and sk values \n",
    "pk_adjument_needed = database_df.loc[database_df['charging_station_id']==7184]['ev_cs_dim_id'].iloc[0]\n",
    "pk_adjument_needed= int(pk_adjument_needed) \n",
    "print(pk_adjument_needed)\n",
    "sk_keeping = database_df.loc[database_df['charging_station_id']==7184]['sk_ev_dim_id'].iloc[0]\n",
    "sk_keeping= int(sk_keeping)\n",
    "print(sk_keeping)"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "- we need to update old record and change\n",
    "    - row expiration date to today\n",
    "    - row status to expired\n",
    "- then we need to add a new record\n",
    "    - keeping the sk is the same \n",
    "    - row effective is default to today so no action \n",
    "    - row expiration date is default none so no action \n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 260,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "query excecuted\n"
     ]
    }
   ],
   "source": [
    "import psycopg2 as pg # PostgreSQL\n",
    "# from psycopg2 import  extensions\n",
    "#establishing the connection\n",
    "conn = pg.connect(host=\"localhost\", database=\"partsunlimited\",user=\"postgres\",password=\"arnold\")\n",
    "#Creating a cursor object using the cursor() method\n",
    "cursor = conn.cursor()\n",
    "#queriying \n",
    "query ='''UPDATE ev_charging_stations SET row_expiration_date = CURRENT_DATE, row_status = 'expired' WHERE ev_cs_dim_id = %s;'''\n",
    "cursor.execute(query, (pk_adjument_needed,))\n",
    "\n",
    "\n",
    "conn.commit()\n",
    "\n",
    "\n",
    "\n",
    "print('query excecuted')\n",
    "cursor.close()\n",
    "conn.close()"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "now we can commit our new changes"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 263,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/tmp/ipykernel_5194/2668156993.py:1: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame.\n",
      "Try using .loc[row_indexer,col_indexer] = value instead\n",
      "\n",
      "See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "  change_required_df['sk_ev_dim_id']= sk_keeping\n"
     ]
    }
   ],
   "source": [
    "change_required_df['sk_ev_dim_id']= sk_keeping"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 264,
   "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>charging_station_id</th>\n",
       "      <th>station_name</th>\n",
       "      <th>status_code</th>\n",
       "      <th>access_code</th>\n",
       "      <th>currency</th>\n",
       "      <th>sk_ev_dim_id</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>7184</td>\n",
       "      <td>Los Angeles International Airport (LAX) - Lot 6</td>\n",
       "      <td>closed</td>\n",
       "      <td>public</td>\n",
       "      <td>USD</td>\n",
       "      <td>24</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   charging_station_id                                     station_name  \\\n",
       "0                 7184  Los Angeles International Airport (LAX) - Lot 6   \n",
       "\n",
       "  status_code access_code currency  sk_ev_dim_id  \n",
       "0      closed      public      USD            24  "
      ]
     },
     "execution_count": 264,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "change_required_df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 267,
   "metadata": {},
   "outputs": [],
   "source": [
    "from sqlalchemy import create_engine\n",
    "# establish connections\n",
    "conn_string = 'postgresql://postgres:arnold@localhost/partsunlimited'\n",
    "  \n",
    "db = create_engine(conn_string)\n",
    "conn = db.connect()\n",
    "print\n",
    "#converting data to sql\n",
    "change_required_df.to_sql('ev_charging_stations', conn, if_exists= 'append', index=False)\n",
    "conn.commit()\n",
    "db.dispose() \n",
    "conn.close()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 272,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * postgresql://postgres:***@localhost/partsunlimited\n",
      "4 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <thead>\n",
       "        <tr>\n",
       "            <th>ev_cs_dim_id</th>\n",
       "            <th>sk_ev_dim_id</th>\n",
       "            <th>charging_station_id</th>\n",
       "            <th>station_name</th>\n",
       "            <th>status_code</th>\n",
       "            <th>access_code</th>\n",
       "            <th>currency</th>\n",
       "            <th>row_effective_date</th>\n",
       "            <th>row_expiration_date</th>\n",
       "            <th>row_status</th>\n",
       "        </tr>\n",
       "    </thead>\n",
       "    <tbody>\n",
       "        <tr>\n",
       "            <td>47087</td>\n",
       "            <td>47087</td>\n",
       "            <td>1234</td>\n",
       "            <td>Fake Station 1</td>\n",
       "            <td>closed</td>\n",
       "            <td>private</td>\n",
       "            <td>EUR</td>\n",
       "            <td>2023-04-25</td>\n",
       "            <td>None</td>\n",
       "            <td>None</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>47088</td>\n",
       "            <td>47088</td>\n",
       "            <td>5678</td>\n",
       "            <td>Fake Station 2</td>\n",
       "            <td>open</td>\n",
       "            <td>public</td>\n",
       "            <td>GBP</td>\n",
       "            <td>2023-04-25</td>\n",
       "            <td>None</td>\n",
       "            <td>None</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>47089</td>\n",
       "            <td>47089</td>\n",
       "            <td>9101</td>\n",
       "            <td>Fake Station 3</td>\n",
       "            <td>under maintenance</td>\n",
       "            <td>private</td>\n",
       "            <td>USD</td>\n",
       "            <td>2023-04-25</td>\n",
       "            <td>None</td>\n",
       "            <td>None</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>47090</td>\n",
       "            <td>24</td>\n",
       "            <td>7184</td>\n",
       "            <td>Los Angeles International Airport (LAX) - Lot 6</td>\n",
       "            <td>closed</td>\n",
       "            <td>public</td>\n",
       "            <td>USD</td>\n",
       "            <td>2023-04-25</td>\n",
       "            <td>None</td>\n",
       "            <td>None</td>\n",
       "        </tr>\n",
       "    </tbody>\n",
       "</table>"
      ],
      "text/plain": [
       "[(47087, 47087, 1234, 'Fake Station 1', 'closed', 'private', 'EUR', datetime.date(2023, 4, 25), None, None),\n",
       " (47088, 47088, 5678, 'Fake Station 2', 'open', 'public', 'GBP', datetime.date(2023, 4, 25), None, None),\n",
       " (47089, 47089, 9101, 'Fake Station 3', 'under maintenance', 'private', 'USD', datetime.date(2023, 4, 25), None, None),\n",
       " (47090, 24, 7184, 'Los Angeles International Airport (LAX) - Lot 6', 'closed', 'public', 'USD', datetime.date(2023, 4, 25), None, None)]"
      ]
     },
     "execution_count": 272,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "SELECT *\n",
    "FROM ev_charging_stations\n",
    "WHERE row_effective_date = '2023-04-25'; "
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "As we see in the query below we set the expiration date from null to todays date and row status to expired "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 273,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * postgresql://postgres:***@localhost/partsunlimited\n",
      "1 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <thead>\n",
       "        <tr>\n",
       "            <th>ev_cs_dim_id</th>\n",
       "            <th>sk_ev_dim_id</th>\n",
       "            <th>charging_station_id</th>\n",
       "            <th>station_name</th>\n",
       "            <th>status_code</th>\n",
       "            <th>access_code</th>\n",
       "            <th>currency</th>\n",
       "            <th>row_effective_date</th>\n",
       "            <th>row_expiration_date</th>\n",
       "            <th>row_status</th>\n",
       "        </tr>\n",
       "    </thead>\n",
       "    <tbody>\n",
       "        <tr>\n",
       "            <td>24</td>\n",
       "            <td>24</td>\n",
       "            <td>7184</td>\n",
       "            <td>Los Angeles International Airport (LAX) - Lot 6</td>\n",
       "            <td>open</td>\n",
       "            <td>public</td>\n",
       "            <td>USD</td>\n",
       "            <td>2023-04-21</td>\n",
       "            <td>2023-04-25</td>\n",
       "            <td>expired</td>\n",
       "        </tr>\n",
       "    </tbody>\n",
       "</table>"
      ],
      "text/plain": [
       "[(24, 24, 7184, 'Los Angeles International Airport (LAX) - Lot 6', 'open', 'public', 'USD', datetime.date(2023, 4, 21), datetime.date(2023, 4, 25), 'expired')]"
      ]
     },
     "execution_count": 273,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "SELECT *\n",
    "FROM ev_charging_stations\n",
    "WHERE row_expiration_date = '2023-04-25'; "
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "let's delete the fake stations"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 275,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * postgresql://postgres:***@localhost/partsunlimited\n",
      "3 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "[]"
      ]
     },
     "execution_count": 275,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "DELETE FROM ev_charging_stations WHERE ev_cs_dim_id in (47087, 47088, 47089) ;"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 4  scd type 3 implementation "
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "we are going to find a update a station lat info 5009196563441751369\n",
    "- first we update the record then \n",
    "-  update the record from 31.7619 to 31.7618\n",
    "-  save the previus record as prev 31.7619 \n",
    "-  update updated date "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 276,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * postgresql://postgres:***@localhost/partsunlimited\n",
      "1 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <thead>\n",
       "        <tr>\n",
       "            <th>location_id</th>\n",
       "            <th>city</th>\n",
       "            <th>state</th>\n",
       "            <th>region</th>\n",
       "            <th>country</th>\n",
       "            <th>latitude</th>\n",
       "            <th>longitude</th>\n",
       "            <th>city_lat</th>\n",
       "            <th>city_long</th>\n",
       "            <th>lat_prev</th>\n",
       "            <th>long_prev</th>\n",
       "            <th>updated_at</th>\n",
       "        </tr>\n",
       "    </thead>\n",
       "    <tbody>\n",
       "        <tr>\n",
       "            <td>5009196563441751369</td>\n",
       "            <td>El Paso</td>\n",
       "            <td>TX</td>\n",
       "            <td>South</td>\n",
       "            <td>US</td>\n",
       "            <td>31.7619</td>\n",
       "            <td>106.485</td>\n",
       "            <td>31.7619</td>\n",
       "            <td>106.485</td>\n",
       "            <td>0.0</td>\n",
       "            <td>0.0</td>\n",
       "            <td>None</td>\n",
       "        </tr>\n",
       "    </tbody>\n",
       "</table>"
      ],
      "text/plain": [
       "[(5009196563441751369, 'El Paso', 'TX', 'South', 'US', 31.7619, 106.485, 31.7619, 106.485, 0.0, 0.0, None)]"
      ]
     },
     "execution_count": 276,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "select *\n",
    "from location_dim \n",
    "where location_id = 5009196563441751369;"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 280,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * postgresql://postgres:***@localhost/partsunlimited\n",
      "1 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "[]"
      ]
     },
     "execution_count": 280,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "UPDATE location_dim \n",
    "SET latitude = 31.7618, \n",
    "    lat_prev = 31.7619, \n",
    "    updated_at = CURRENT_TIMESTAMP \n",
    "WHERE location_dim.location_id = '5009196563441751369';"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "now we see the update"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 281,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * postgresql://postgres:***@localhost/partsunlimited\n",
      "1 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <thead>\n",
       "        <tr>\n",
       "            <th>location_id</th>\n",
       "            <th>city</th>\n",
       "            <th>state</th>\n",
       "            <th>region</th>\n",
       "            <th>country</th>\n",
       "            <th>latitude</th>\n",
       "            <th>longitude</th>\n",
       "            <th>city_lat</th>\n",
       "            <th>city_long</th>\n",
       "            <th>lat_prev</th>\n",
       "            <th>long_prev</th>\n",
       "            <th>updated_at</th>\n",
       "        </tr>\n",
       "    </thead>\n",
       "    <tbody>\n",
       "        <tr>\n",
       "            <td>5009196563441751369</td>\n",
       "            <td>El Paso</td>\n",
       "            <td>TX</td>\n",
       "            <td>South</td>\n",
       "            <td>US</td>\n",
       "            <td>31.7618</td>\n",
       "            <td>106.485</td>\n",
       "            <td>31.7619</td>\n",
       "            <td>106.485</td>\n",
       "            <td>31.7619</td>\n",
       "            <td>0.0</td>\n",
       "            <td>2023-04-25 19:31:41.917946</td>\n",
       "        </tr>\n",
       "    </tbody>\n",
       "</table>"
      ],
      "text/plain": [
       "[(5009196563441751369, 'El Paso', 'TX', 'South', 'US', 31.7618, 106.485, 31.7619, 106.485, 31.7619, 0.0, datetime.datetime(2023, 4, 25, 19, 31, 41, 917946))]"
      ]
     },
     "execution_count": 281,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "select *\n",
    "from location_dim \n",
    "where location_id = 5009196563441751369;"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 5 fact table update"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 282,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * postgresql://postgres:***@localhost/partsunlimited\n",
      "1 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <thead>\n",
       "        <tr>\n",
       "            <th>manu_fact_id</th>\n",
       "            <th>manufacture_id</th>\n",
       "            <th>product_id</th>\n",
       "            <th>date_id</th>\n",
       "            <th>location_id</th>\n",
       "            <th>weight</th>\n",
       "            <th>price</th>\n",
       "            <th>quantity</th>\n",
       "        </tr>\n",
       "    </thead>\n",
       "    <tbody>\n",
       "        <tr>\n",
       "            <td>1</td>\n",
       "            <td>1</td>\n",
       "            <td>1</td>\n",
       "            <td>20200210</td>\n",
       "            <td>3757140647215491119</td>\n",
       "            <td>0.0</td>\n",
       "            <td>799.0</td>\n",
       "            <td>1</td>\n",
       "        </tr>\n",
       "    </tbody>\n",
       "</table>"
      ],
      "text/plain": [
       "[(1, 1, 1, 20200210, 3757140647215491119, 0.0, 799.0, 1)]"
      ]
     },
     "execution_count": 282,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "select *\n",
    "from manufacturer_fact\n",
    "where product_id =1;"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 283,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * postgresql://postgres:***@localhost/partsunlimited\n",
      "1 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "[]"
      ]
     },
     "execution_count": 283,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "UPDATE manufacturer_fact \n",
    "SET price  = 798\n",
    "WHERE  product_id =1;"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 284,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * postgresql://postgres:***@localhost/partsunlimited\n",
      "1 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <thead>\n",
       "        <tr>\n",
       "            <th>manu_fact_id</th>\n",
       "            <th>manufacture_id</th>\n",
       "            <th>product_id</th>\n",
       "            <th>date_id</th>\n",
       "            <th>location_id</th>\n",
       "            <th>weight</th>\n",
       "            <th>price</th>\n",
       "            <th>quantity</th>\n",
       "        </tr>\n",
       "    </thead>\n",
       "    <tbody>\n",
       "        <tr>\n",
       "            <td>1</td>\n",
       "            <td>1</td>\n",
       "            <td>1</td>\n",
       "            <td>20200210</td>\n",
       "            <td>3757140647215491119</td>\n",
       "            <td>0.0</td>\n",
       "            <td>798.0</td>\n",
       "            <td>1</td>\n",
       "        </tr>\n",
       "    </tbody>\n",
       "</table>"
      ],
      "text/plain": [
       "[(1, 1, 1, 20200210, 3757140647215491119, 0.0, 798.0, 1)]"
      ]
     },
     "execution_count": 284,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "select *\n",
    "from manufacturer_fact\n",
    "WHERE  product_id =1;"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "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"
  },
  "orig_nbformat": 4
 },
 "nbformat": 4,
 "nbformat_minor": 2
}