scd_python.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Implementation of SCD Type 1, 2, 3, and 4 in Python\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Importing Libraries and Data Preparation\n",
    "In this step, we import necessary libraries like pandas and datetime. We then create two datasets: \n",
    "- The \"old\" dataset representing the original data.\n",
    "- The \"new\" dataset representing the updated data with changes. \n",
    "These datasets will be used to demonstrate different types of Slowly Changing Dimensions (SCD).\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Old Data:\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 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>id</th>\n",
       "      <th>name</th>\n",
       "      <th>city</th>\n",
       "      <th>salary</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>John Doe</td>\n",
       "      <td>New York</td>\n",
       "      <td>70000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>Jane Smith</td>\n",
       "      <td>Los Angeles</td>\n",
       "      <td>80000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "      <td>Mary Johnson</td>\n",
       "      <td>Chicago</td>\n",
       "      <td>120000</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   id          name         city  salary\n",
       "0   1      John Doe     New York   70000\n",
       "1   2    Jane Smith  Los Angeles   80000\n",
       "2   3  Mary Johnson      Chicago  120000"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "\n",
      "New Data:\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 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>id</th>\n",
       "      <th>name</th>\n",
       "      <th>city</th>\n",
       "      <th>salary</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>John Doe</td>\n",
       "      <td>New York</td>\n",
       "      <td>75000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>Jane Doe</td>\n",
       "      <td>San Francisco</td>\n",
       "      <td>85000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "      <td>Mary Johnson</td>\n",
       "      <td>Chicago</td>\n",
       "      <td>120000</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   id          name           city  salary\n",
       "0   1      John Doe       New York   75000\n",
       "1   2      Jane Doe  San Francisco   85000\n",
       "2   3  Mary Johnson        Chicago  120000"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "# Import necessary libraries\n",
    "import pandas as pd\n",
    "from datetime import datetime\n",
    "\n",
    "# Old dataset\n",
    "old_data = pd.DataFrame({\n",
    "    'id': [1, 2, 3],\n",
    "    'name': ['John Doe', 'Jane Smith', 'Mary Johnson'],\n",
    "    'city': ['New York', 'Los Angeles', 'Chicago'],\n",
    "    'salary': [70000, 80000, 120000]\n",
    "})\n",
    "\n",
    "# New dataset\n",
    "new_data = pd.DataFrame({\n",
    "    'id': [1, 2, 3],\n",
    "    'name': ['John Doe', 'Jane Doe', 'Mary Johnson'],\n",
    "    'city': ['New York', 'San Francisco', 'Chicago'],\n",
    "    'salary': [75000, 85000, 120000]\n",
    "})\n",
    "\n",
    "# Display the initial datasets\n",
    "print(\"Old Data:\")\n",
    "display(old_data)\n",
    "print(\"\\nNew Data:\")\n",
    "display(new_data)\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## SCD Type 1 - Overwrite\n",
    "SCD Type 1 simply overwrites the existing data with new values. This means any previous values are lost, and only the most recent information is retained. \n",
    "We loop through the new data and update the corresponding records in the old data where changes have occurred.\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "\n",
      "SCD Type 1 (Overwrite):\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "C:\\Users\\hamza\\AppData\\Local\\Temp\\ipykernel_51912\\1003437780.py:6: FutureWarning: Setting an item of incompatible dtype is deprecated and will raise an error in a future version of pandas. Value '[nan]' has dtype incompatible with int64, please explicitly cast to a compatible dtype first.\n",
      "  scd_type1.loc[scd_type1['id'] == new_row['id'], ['name', 'city', 'salary']] = new_row[['name', 'city', 'salary']]\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 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>id</th>\n",
       "      <th>name</th>\n",
       "      <th>city</th>\n",
       "      <th>salary</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   id name city  salary\n",
       "0   1  NaN  NaN     NaN\n",
       "1   2  NaN  NaN     NaN\n",
       "2   3  NaN  NaN     NaN"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "# SCD Type 1: Overwrite\n",
    "scd_type1 = old_data.copy()\n",
    "\n",
    "# Updating rows where changes occurred\n",
    "for index, new_row in new_data.iterrows():\n",
    "    scd_type1.loc[scd_type1['id'] == new_row['id'], ['name', 'city', 'salary']] = new_row[['name', 'city', 'salary']]\n",
    "\n",
    "print(\"\\nSCD Type 1 (Overwrite):\")\n",
    "display(scd_type1)\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## SCD Type 2 - Add New Row (Preserve History)\n",
    "In SCD Type 2, we retain a history of changes by adding new rows for each change. Each row contains start and end dates to indicate the period of validity, along with a column indicating whether the record is currently active (`is_current`). \n",
    "This approach ensures that we maintain a complete history of all changes made over time.\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "\n",
      "SCD Type 2 (Add New Row):\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 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>id</th>\n",
       "      <th>name</th>\n",
       "      <th>city</th>\n",
       "      <th>salary</th>\n",
       "      <th>is_current</th>\n",
       "      <th>effective_date</th>\n",
       "      <th>expiry_date</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>John Doe</td>\n",
       "      <td>New York</td>\n",
       "      <td>70000</td>\n",
       "      <td>N</td>\n",
       "      <td>2024-09-23</td>\n",
       "      <td>2024-09-23</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>Jane Smith</td>\n",
       "      <td>Los Angeles</td>\n",
       "      <td>80000</td>\n",
       "      <td>N</td>\n",
       "      <td>2024-09-23</td>\n",
       "      <td>2024-09-23</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "      <td>Mary Johnson</td>\n",
       "      <td>Chicago</td>\n",
       "      <td>120000</td>\n",
       "      <td>N</td>\n",
       "      <td>2024-09-23</td>\n",
       "      <td>2024-09-23</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1</td>\n",
       "      <td>John Doe</td>\n",
       "      <td>New York</td>\n",
       "      <td>75000</td>\n",
       "      <td>Y</td>\n",
       "      <td>2024-09-23</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2</td>\n",
       "      <td>Jane Doe</td>\n",
       "      <td>San Francisco</td>\n",
       "      <td>85000</td>\n",
       "      <td>Y</td>\n",
       "      <td>2024-09-23</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>3</td>\n",
       "      <td>Mary Johnson</td>\n",
       "      <td>Chicago</td>\n",
       "      <td>120000</td>\n",
       "      <td>Y</td>\n",
       "      <td>2024-09-23</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   id          name           city  salary is_current effective_date  \\\n",
       "0   1      John Doe       New York   70000          N     2024-09-23   \n",
       "1   2    Jane Smith    Los Angeles   80000          N     2024-09-23   \n",
       "2   3  Mary Johnson        Chicago  120000          N     2024-09-23   \n",
       "3   1      John Doe       New York   75000          Y     2024-09-23   \n",
       "4   2      Jane Doe  San Francisco   85000          Y     2024-09-23   \n",
       "5   3  Mary Johnson        Chicago  120000          Y     2024-09-23   \n",
       "\n",
       "  expiry_date  \n",
       "0  2024-09-23  \n",
       "1  2024-09-23  \n",
       "2  2024-09-23  \n",
       "3        None  \n",
       "4        None  \n",
       "5        None  "
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "# Adding effective and expiry dates to track the history\n",
    "current_date = datetime.now().strftime('%Y-%m-%d')\n",
    "\n",
    "# Adding necessary columns for SCD Type 2\n",
    "old_data_scd2 = old_data.copy()\n",
    "old_data_scd2['is_current'] = 'Y'\n",
    "old_data_scd2['effective_date'] = current_date\n",
    "old_data_scd2['expiry_date'] = None\n",
    "\n",
    "new_rows = []\n",
    "\n",
    "for index, new_row in new_data.iterrows():\n",
    "    # Find matching rows in the old data\n",
    "    match = old_data_scd2[(old_data_scd2['id'] == new_row['id']) & (old_data_scd2['is_current'] == 'Y')]\n",
    "    \n",
    "    if not match.empty and not match.iloc[0].equals(new_row):\n",
    "        # Update the old record to be no longer current\n",
    "        old_data_scd2.loc[old_data_scd2['id'] == new_row['id'], 'is_current'] = 'N'\n",
    "        old_data_scd2.loc[old_data_scd2['id'] == new_row['id'], 'expiry_date'] = current_date\n",
    "        \n",
    "        # Insert the new row as the current version\n",
    "        new_row['is_current'] = 'Y'\n",
    "        new_row['effective_date'] = current_date\n",
    "        new_row['expiry_date'] = None\n",
    "        new_rows.append(new_row)\n",
    "\n",
    "# Append new rows to old data\n",
    "scd_type2 = pd.concat([old_data_scd2, pd.DataFrame(new_rows)], ignore_index=True)\n",
    "\n",
    "print(\"\\nSCD Type 2 (Add New Row):\")\n",
    "display(scd_type2)\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## SCD Type 3 - Add New Column\n",
    "SCD Type 3 adds a new column to store the previous value of the changed attribute. This approach captures the immediate past value while maintaining the current value. \n",
    "It allows us to track the most recent change but does not retain the full history of changes.\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "\n",
      "SCD Type 3 (Add New Column):\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "C:\\Users\\hamza\\AppData\\Local\\Temp\\ipykernel_51912\\2438326019.py:9: FutureWarning: Setting an item of incompatible dtype is deprecated and will raise an error in a future version of pandas. Value '[nan]' has dtype incompatible with int64, please explicitly cast to a compatible dtype first.\n",
      "  scd_type3.loc[scd_type3['id'] == new_row['id'], ['name', 'city', 'salary']] = new_row[['name', 'city', 'salary']]\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 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>id</th>\n",
       "      <th>name</th>\n",
       "      <th>city</th>\n",
       "      <th>salary</th>\n",
       "      <th>previous_city</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>New York</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Los Angeles</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Chicago</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   id name city  salary previous_city\n",
       "0   1  NaN  NaN     NaN      New York\n",
       "1   2  NaN  NaN     NaN   Los Angeles\n",
       "2   3  NaN  NaN     NaN       Chicago"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "# Copying old data for SCD Type 3\n",
    "scd_type3 = old_data.copy()\n",
    "scd_type3['previous_city'] = scd_type3['city']\n",
    "\n",
    "# Updating with the new values\n",
    "for index, new_row in new_data.iterrows():\n",
    "    match = scd_type3[scd_type3['id'] == new_row['id']]\n",
    "    if not match.empty:\n",
    "        scd_type3.loc[scd_type3['id'] == new_row['id'], ['name', 'city', 'salary']] = new_row[['name', 'city', 'salary']]\n",
    "\n",
    "print(\"\\nSCD Type 3 (Add New Column):\")\n",
    "display(scd_type3)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## SCD Type 4 - Using a History Table\n",
    "In SCD Type 4, we maintain a separate history table that stores all historical changes. The main table contains only the current data. \n",
    "This method allows us to keep a detailed history of changes without modifying the structure of the main table, making it an efficient way to track changes over time.\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "\n",
      "SCD Type 4 (Using History Table):\n",
      "Main Table:\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "C:\\Users\\hamza\\AppData\\Local\\Temp\\ipykernel_51912\\256994068.py:12: 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",
      "  match['record_version'] = 1\n",
      "C:\\Users\\hamza\\AppData\\Local\\Temp\\ipykernel_51912\\256994068.py:12: 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",
      "  match['record_version'] = 1\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 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>id</th>\n",
       "      <th>name</th>\n",
       "      <th>city</th>\n",
       "      <th>salary</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>John Doe</td>\n",
       "      <td>New York</td>\n",
       "      <td>75000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>Jane Doe</td>\n",
       "      <td>San Francisco</td>\n",
       "      <td>85000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "      <td>Mary Johnson</td>\n",
       "      <td>Chicago</td>\n",
       "      <td>120000</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   id          name           city  salary\n",
       "0   1      John Doe       New York   75000\n",
       "1   2      Jane Doe  San Francisco   85000\n",
       "2   3  Mary Johnson        Chicago  120000"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "\n",
      "History Table:\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 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>id</th>\n",
       "      <th>name</th>\n",
       "      <th>city</th>\n",
       "      <th>salary</th>\n",
       "      <th>record_version</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>John Doe</td>\n",
       "      <td>New York</td>\n",
       "      <td>70000</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>Jane Smith</td>\n",
       "      <td>Los Angeles</td>\n",
       "      <td>80000</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "      <td>Mary Johnson</td>\n",
       "      <td>Chicago</td>\n",
       "      <td>120000</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1</td>\n",
       "      <td>John Doe</td>\n",
       "      <td>New York</td>\n",
       "      <td>70000</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2</td>\n",
       "      <td>Jane Smith</td>\n",
       "      <td>Los Angeles</td>\n",
       "      <td>80000</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   id          name         city  salary  record_version\n",
       "0   1      John Doe     New York   70000               1\n",
       "1   2    Jane Smith  Los Angeles   80000               1\n",
       "2   3  Mary Johnson      Chicago  120000               1\n",
       "3   1      John Doe     New York   70000               1\n",
       "4   2    Jane Smith  Los Angeles   80000               1"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "# Main table will contain the latest data\n",
    "scd_type4_main = new_data.copy()\n",
    "\n",
    "# History table will contain all previous records\n",
    "scd_type4_history = old_data.copy()\n",
    "scd_type4_history['record_version'] = 1  # Add version tracking\n",
    "\n",
    "# Version tracking for new data changes\n",
    "for index, new_row in new_data.iterrows():\n",
    "    match = old_data[old_data['id'] == new_row['id']]\n",
    "    if not match.empty and not match.iloc[0].equals(new_row):\n",
    "        match['record_version'] = 1\n",
    "        scd_type4_history = pd.concat([scd_type4_history, match], ignore_index=True)\n",
    "\n",
    "print(\"\\nSCD Type 4 (Using History Table):\")\n",
    "print(\"Main Table:\")\n",
    "display(scd_type4_main)\n",
    "print(\"\\nHistory Table:\")\n",
    "display(scd_type4_history)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "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.12.4"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}