main.ipynb

{
 "cells": [
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Load Data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "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>eticket_num</th>\n",
       "      <th>confirmation</th>\n",
       "      <th>ticket_date</th>\n",
       "      <th>price</th>\n",
       "      <th>seat</th>\n",
       "      <th>status</th>\n",
       "      <th>airline.name</th>\n",
       "      <th>airline.iata</th>\n",
       "      <th>airline.icao</th>\n",
       "      <th>airline.callsign</th>\n",
       "      <th>...</th>\n",
       "      <th>passenger.last_name</th>\n",
       "      <th>passenger.gender</th>\n",
       "      <th>passenger.birth_date</th>\n",
       "      <th>passenger.email</th>\n",
       "      <th>passenger.street</th>\n",
       "      <th>passenger.city</th>\n",
       "      <th>passenger.state</th>\n",
       "      <th>passenger.zip</th>\n",
       "      <th>origin</th>\n",
       "      <th>destination</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>498-938211-0795</td>\n",
       "      <td>ZVFDC4</td>\n",
       "      <td>2022-03-23</td>\n",
       "      <td>723.42</td>\n",
       "      <td>31I</td>\n",
       "      <td>active</td>\n",
       "      <td>China Eastern Airlines</td>\n",
       "      <td>MU</td>\n",
       "      <td>CES</td>\n",
       "      <td>CHINA EASTERN</td>\n",
       "      <td>...</td>\n",
       "      <td>Brown</td>\n",
       "      <td>M</td>\n",
       "      <td>1969-02-17</td>\n",
       "      <td>robert.brown.69@hotmail.com</td>\n",
       "      <td>5007 Thomas Way</td>\n",
       "      <td>Lake Hollystad</td>\n",
       "      <td>DC</td>\n",
       "      <td>20027</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>482-850738-6048</td>\n",
       "      <td>IL5GUI</td>\n",
       "      <td>2022-03-23</td>\n",
       "      <td>765.18</td>\n",
       "      <td>29B</td>\n",
       "      <td>active</td>\n",
       "      <td>Hawaiian Airlines</td>\n",
       "      <td>HA</td>\n",
       "      <td>HAL</td>\n",
       "      <td>HAWAIIAN</td>\n",
       "      <td>...</td>\n",
       "      <td>Kent</td>\n",
       "      <td>F</td>\n",
       "      <td>1998-08-05</td>\n",
       "      <td>laura.kent.98@hotmail.com</td>\n",
       "      <td>13991 Davis Village</td>\n",
       "      <td>North Catherineborough</td>\n",
       "      <td>PA</td>\n",
       "      <td>16516</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>275-207321-8092</td>\n",
       "      <td>CYEFBC</td>\n",
       "      <td>2022-03-21</td>\n",
       "      <td>753.89</td>\n",
       "      <td>26I</td>\n",
       "      <td>active</td>\n",
       "      <td>Wizz Air</td>\n",
       "      <td>W6</td>\n",
       "      <td>WZZ</td>\n",
       "      <td>WIZZ AIR</td>\n",
       "      <td>...</td>\n",
       "      <td>Tucker</td>\n",
       "      <td>F</td>\n",
       "      <td>1965-01-22</td>\n",
       "      <td>lisa.tucker.65@hotmail.com</td>\n",
       "      <td>04135 Marvin Via</td>\n",
       "      <td>North Kristabury</td>\n",
       "      <td>MA</td>\n",
       "      <td>01093</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>246-793315-3102</td>\n",
       "      <td>ZNGPC2</td>\n",
       "      <td>2022-03-22</td>\n",
       "      <td>793.89</td>\n",
       "      <td>15A</td>\n",
       "      <td>active</td>\n",
       "      <td>AirAsia</td>\n",
       "      <td>AK</td>\n",
       "      <td>AXM</td>\n",
       "      <td>ASIAN EXPRESS</td>\n",
       "      <td>...</td>\n",
       "      <td>Yates</td>\n",
       "      <td>NB</td>\n",
       "      <td>1975-03-31</td>\n",
       "      <td>matthew.yates.75@yahoo.com</td>\n",
       "      <td>76045 Samantha Road Suite 111</td>\n",
       "      <td>Lake Jeffrey</td>\n",
       "      <td>DE</td>\n",
       "      <td>19898</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>091-128904-1226</td>\n",
       "      <td>MGSBD9</td>\n",
       "      <td>2022-03-24</td>\n",
       "      <td>820.25</td>\n",
       "      <td>17F</td>\n",
       "      <td>active</td>\n",
       "      <td>Xiamen Airlines</td>\n",
       "      <td>MF</td>\n",
       "      <td>CXA</td>\n",
       "      <td>XIAMEN AIR</td>\n",
       "      <td>...</td>\n",
       "      <td>Villanueva</td>\n",
       "      <td>NB</td>\n",
       "      <td>1945-08-14</td>\n",
       "      <td>megan.villanueva.45@hotmail.com</td>\n",
       "      <td>848 Melissa Springs Suite 947</td>\n",
       "      <td>Kellerstad</td>\n",
       "      <td>TX</td>\n",
       "      <td>76177</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>115-196069-8963</td>\n",
       "      <td>XFYQC0</td>\n",
       "      <td>2022-03-23</td>\n",
       "      <td>892.69</td>\n",
       "      <td>18C</td>\n",
       "      <td>active</td>\n",
       "      <td>Air New Zealand</td>\n",
       "      <td>NZ</td>\n",
       "      <td>ANZ</td>\n",
       "      <td>NEW ZEALAND</td>\n",
       "      <td>...</td>\n",
       "      <td>Hall</td>\n",
       "      <td>NB</td>\n",
       "      <td>1944-08-31</td>\n",
       "      <td>sarah.hall.44@gmail.com</td>\n",
       "      <td>75420 Michael Mountains Suite 485</td>\n",
       "      <td>New Victoria</td>\n",
       "      <td>HI</td>\n",
       "      <td>96727</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>396-673460-1326</td>\n",
       "      <td>N5UOOZ</td>\n",
       "      <td>2022-03-23</td>\n",
       "      <td>889.53</td>\n",
       "      <td>3C</td>\n",
       "      <td>active</td>\n",
       "      <td>Jeju Air</td>\n",
       "      <td>7C</td>\n",
       "      <td>JJA</td>\n",
       "      <td>JEJU AIR</td>\n",
       "      <td>...</td>\n",
       "      <td>Thompson</td>\n",
       "      <td>M</td>\n",
       "      <td>1968-05-02</td>\n",
       "      <td>seth.thompson.68@yahoo.com</td>\n",
       "      <td>22455 Higgins Junction Apt. 042</td>\n",
       "      <td>New Keith</td>\n",
       "      <td>OR</td>\n",
       "      <td>97405</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>380-894599-8109</td>\n",
       "      <td>PAA19Y</td>\n",
       "      <td>2022-03-22</td>\n",
       "      <td>706.78</td>\n",
       "      <td>7D</td>\n",
       "      <td>active</td>\n",
       "      <td>American Airlines</td>\n",
       "      <td>AA</td>\n",
       "      <td>AAL</td>\n",
       "      <td>AMERICAN</td>\n",
       "      <td>...</td>\n",
       "      <td>Garcia</td>\n",
       "      <td>F</td>\n",
       "      <td>1950-02-12</td>\n",
       "      <td>jennifer.garcia.50@gmail.com</td>\n",
       "      <td>6607 Sharp Common</td>\n",
       "      <td>Chadstad</td>\n",
       "      <td>VA</td>\n",
       "      <td>22121</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>614-960971-2686</td>\n",
       "      <td>EF4BHJ</td>\n",
       "      <td>2022-03-23</td>\n",
       "      <td>486.40</td>\n",
       "      <td>24J</td>\n",
       "      <td>active</td>\n",
       "      <td>Juneyao Airlines</td>\n",
       "      <td>HO</td>\n",
       "      <td>DKH</td>\n",
       "      <td>JUNEYAO AIRLINES</td>\n",
       "      <td>...</td>\n",
       "      <td>Clark</td>\n",
       "      <td>F</td>\n",
       "      <td>1991-11-09</td>\n",
       "      <td>becky.clark.91@gmail.com</td>\n",
       "      <td>691 Jones Cliffs</td>\n",
       "      <td>Michaelburgh</td>\n",
       "      <td>TX</td>\n",
       "      <td>76003</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>481-321233-0702</td>\n",
       "      <td>FVM9EE</td>\n",
       "      <td>2022-03-23</td>\n",
       "      <td>855.93</td>\n",
       "      <td>16A</td>\n",
       "      <td>active</td>\n",
       "      <td>Royal Air Maroc</td>\n",
       "      <td>AT</td>\n",
       "      <td>RAM</td>\n",
       "      <td>ROYALAIR MAROC</td>\n",
       "      <td>...</td>\n",
       "      <td>Cook</td>\n",
       "      <td>M</td>\n",
       "      <td>1976-07-29</td>\n",
       "      <td>ronald.cook.76@hotmail.com</td>\n",
       "      <td>93328 Davis Island</td>\n",
       "      <td>Rodriguezside</td>\n",
       "      <td>MD</td>\n",
       "      <td>21408</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>10 rows × 40 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "       eticket_num confirmation ticket_date   price seat  status  \\\n",
       "0  498-938211-0795       ZVFDC4  2022-03-23  723.42  31I  active   \n",
       "1  482-850738-6048       IL5GUI  2022-03-23  765.18  29B  active   \n",
       "2  275-207321-8092       CYEFBC  2022-03-21  753.89  26I  active   \n",
       "3  246-793315-3102       ZNGPC2  2022-03-22  793.89  15A  active   \n",
       "4  091-128904-1226       MGSBD9  2022-03-24  820.25  17F  active   \n",
       "5  115-196069-8963       XFYQC0  2022-03-23  892.69  18C  active   \n",
       "6  396-673460-1326       N5UOOZ  2022-03-23  889.53   3C  active   \n",
       "7  380-894599-8109       PAA19Y  2022-03-22  706.78   7D  active   \n",
       "8  614-960971-2686       EF4BHJ  2022-03-23  486.40  24J  active   \n",
       "9  481-321233-0702       FVM9EE  2022-03-23  855.93  16A  active   \n",
       "\n",
       "             airline.name airline.iata airline.icao  airline.callsign  ...  \\\n",
       "0  China Eastern Airlines           MU          CES     CHINA EASTERN  ...   \n",
       "1       Hawaiian Airlines           HA          HAL          HAWAIIAN  ...   \n",
       "2                Wizz Air           W6          WZZ          WIZZ AIR  ...   \n",
       "3                 AirAsia           AK          AXM     ASIAN EXPRESS  ...   \n",
       "4         Xiamen Airlines           MF          CXA        XIAMEN AIR  ...   \n",
       "5         Air New Zealand           NZ          ANZ       NEW ZEALAND  ...   \n",
       "6                Jeju Air           7C          JJA          JEJU AIR  ...   \n",
       "7       American Airlines           AA          AAL          AMERICAN  ...   \n",
       "8        Juneyao Airlines           HO          DKH  JUNEYAO AIRLINES  ...   \n",
       "9         Royal Air Maroc           AT          RAM    ROYALAIR MAROC  ...   \n",
       "\n",
       "  passenger.last_name passenger.gender passenger.birth_date  \\\n",
       "0               Brown                M           1969-02-17   \n",
       "1                Kent                F           1998-08-05   \n",
       "2              Tucker                F           1965-01-22   \n",
       "3               Yates               NB           1975-03-31   \n",
       "4          Villanueva               NB           1945-08-14   \n",
       "5                Hall               NB           1944-08-31   \n",
       "6            Thompson                M           1968-05-02   \n",
       "7              Garcia                F           1950-02-12   \n",
       "8               Clark                F           1991-11-09   \n",
       "9                Cook                M           1976-07-29   \n",
       "\n",
       "                   passenger.email                   passenger.street  \\\n",
       "0      robert.brown.69@hotmail.com                    5007 Thomas Way   \n",
       "1        laura.kent.98@hotmail.com                13991 Davis Village   \n",
       "2       lisa.tucker.65@hotmail.com                   04135 Marvin Via   \n",
       "3       matthew.yates.75@yahoo.com      76045 Samantha Road Suite 111   \n",
       "4  megan.villanueva.45@hotmail.com      848 Melissa Springs Suite 947   \n",
       "5          sarah.hall.44@gmail.com  75420 Michael Mountains Suite 485   \n",
       "6       seth.thompson.68@yahoo.com    22455 Higgins Junction Apt. 042   \n",
       "7     jennifer.garcia.50@gmail.com                  6607 Sharp Common   \n",
       "8         becky.clark.91@gmail.com                   691 Jones Cliffs   \n",
       "9       ronald.cook.76@hotmail.com                 93328 Davis Island   \n",
       "\n",
       "           passenger.city  passenger.state  passenger.zip  origin destination  \n",
       "0          Lake Hollystad               DC          20027     NaN         NaN  \n",
       "1  North Catherineborough               PA          16516     NaN         NaN  \n",
       "2        North Kristabury               MA          01093     NaN         NaN  \n",
       "3            Lake Jeffrey               DE          19898     NaN         NaN  \n",
       "4              Kellerstad               TX          76177     NaN         NaN  \n",
       "5            New Victoria               HI          96727     NaN         NaN  \n",
       "6               New Keith               OR          97405     NaN         NaN  \n",
       "7                Chadstad               VA          22121     NaN         NaN  \n",
       "8            Michaelburgh               TX          76003     NaN         NaN  \n",
       "9           Rodriguezside               MD          21408     NaN         NaN  \n",
       "\n",
       "[10 rows x 40 columns]"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "import os\n",
    "import sys\n",
    "import pandas as pd\n",
    "import logging\n",
    "from google.cloud import bigquery\n",
    "from hashlib import md5\n",
    "from typing import List\n",
    "import json\n",
    "\n",
    "# **** SETUP ****\n",
    "\n",
    "# change to match your filesystem\n",
    "DATA_DIR = \"./data/air_travel/\"\n",
    "DEFAULT_RECEIPTS_FILE = os.path.join(DATA_DIR, \"tickets.json\")\n",
    "PROJECT_NAME = \"deb-dev-dw\"\n",
    "DATASET_NAME = \"air_travel\"\n",
    "\n",
    "data = []\n",
    "with open('./data/air_travel/tickets.json', 'r') as f:\n",
    "    for line in f:\n",
    "        data.append(json.loads(line))\n",
    "\n",
    "df = pd.DataFrame.from_dict(pd.json_normalize(data), orient='columns')\n",
    "\n",
    "display(df.head(n=10))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/Users/drewwhite/Desktop/Epicodus/data_week_8/venv/lib/python3.7/site-packages/ipykernel_launcher.py:2: FutureWarning: The default value of regex will change from True to False in a future version. In addition, single character regular expressions will *not* be treated as literal strings when regex=True.\n",
      "  \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>eticket_num</th>\n",
       "      <th>confirmation</th>\n",
       "      <th>ticket_date</th>\n",
       "      <th>price</th>\n",
       "      <th>seat</th>\n",
       "      <th>status</th>\n",
       "      <th>airline_name</th>\n",
       "      <th>airline_iata</th>\n",
       "      <th>airline_icao</th>\n",
       "      <th>airline_callsign</th>\n",
       "      <th>...</th>\n",
       "      <th>passenger_last_name</th>\n",
       "      <th>passenger_gender</th>\n",
       "      <th>passenger_birth_date</th>\n",
       "      <th>passenger_email</th>\n",
       "      <th>passenger_street</th>\n",
       "      <th>passenger_city</th>\n",
       "      <th>passenger_state</th>\n",
       "      <th>passenger_zip</th>\n",
       "      <th>origin</th>\n",
       "      <th>destination</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>498-938211-0795</td>\n",
       "      <td>ZVFDC4</td>\n",
       "      <td>2022-03-23</td>\n",
       "      <td>723.42</td>\n",
       "      <td>31I</td>\n",
       "      <td>active</td>\n",
       "      <td>China Eastern Airlines</td>\n",
       "      <td>MU</td>\n",
       "      <td>CES</td>\n",
       "      <td>CHINA EASTERN</td>\n",
       "      <td>...</td>\n",
       "      <td>Brown</td>\n",
       "      <td>M</td>\n",
       "      <td>1969-02-17</td>\n",
       "      <td>robert.brown.69@hotmail.com</td>\n",
       "      <td>5007 Thomas Way</td>\n",
       "      <td>Lake Hollystad</td>\n",
       "      <td>DC</td>\n",
       "      <td>20027</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>482-850738-6048</td>\n",
       "      <td>IL5GUI</td>\n",
       "      <td>2022-03-23</td>\n",
       "      <td>765.18</td>\n",
       "      <td>29B</td>\n",
       "      <td>active</td>\n",
       "      <td>Hawaiian Airlines</td>\n",
       "      <td>HA</td>\n",
       "      <td>HAL</td>\n",
       "      <td>HAWAIIAN</td>\n",
       "      <td>...</td>\n",
       "      <td>Kent</td>\n",
       "      <td>F</td>\n",
       "      <td>1998-08-05</td>\n",
       "      <td>laura.kent.98@hotmail.com</td>\n",
       "      <td>13991 Davis Village</td>\n",
       "      <td>North Catherineborough</td>\n",
       "      <td>PA</td>\n",
       "      <td>16516</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>275-207321-8092</td>\n",
       "      <td>CYEFBC</td>\n",
       "      <td>2022-03-21</td>\n",
       "      <td>753.89</td>\n",
       "      <td>26I</td>\n",
       "      <td>active</td>\n",
       "      <td>Wizz Air</td>\n",
       "      <td>W6</td>\n",
       "      <td>WZZ</td>\n",
       "      <td>WIZZ AIR</td>\n",
       "      <td>...</td>\n",
       "      <td>Tucker</td>\n",
       "      <td>F</td>\n",
       "      <td>1965-01-22</td>\n",
       "      <td>lisa.tucker.65@hotmail.com</td>\n",
       "      <td>04135 Marvin Via</td>\n",
       "      <td>North Kristabury</td>\n",
       "      <td>MA</td>\n",
       "      <td>01093</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>246-793315-3102</td>\n",
       "      <td>ZNGPC2</td>\n",
       "      <td>2022-03-22</td>\n",
       "      <td>793.89</td>\n",
       "      <td>15A</td>\n",
       "      <td>active</td>\n",
       "      <td>AirAsia</td>\n",
       "      <td>AK</td>\n",
       "      <td>AXM</td>\n",
       "      <td>ASIAN EXPRESS</td>\n",
       "      <td>...</td>\n",
       "      <td>Yates</td>\n",
       "      <td>NB</td>\n",
       "      <td>1975-03-31</td>\n",
       "      <td>matthew.yates.75@yahoo.com</td>\n",
       "      <td>76045 Samantha Road Suite 111</td>\n",
       "      <td>Lake Jeffrey</td>\n",
       "      <td>DE</td>\n",
       "      <td>19898</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>091-128904-1226</td>\n",
       "      <td>MGSBD9</td>\n",
       "      <td>2022-03-24</td>\n",
       "      <td>820.25</td>\n",
       "      <td>17F</td>\n",
       "      <td>active</td>\n",
       "      <td>Xiamen Airlines</td>\n",
       "      <td>MF</td>\n",
       "      <td>CXA</td>\n",
       "      <td>XIAMEN AIR</td>\n",
       "      <td>...</td>\n",
       "      <td>Villanueva</td>\n",
       "      <td>NB</td>\n",
       "      <td>1945-08-14</td>\n",
       "      <td>megan.villanueva.45@hotmail.com</td>\n",
       "      <td>848 Melissa Springs Suite 947</td>\n",
       "      <td>Kellerstad</td>\n",
       "      <td>TX</td>\n",
       "      <td>76177</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>115-196069-8963</td>\n",
       "      <td>XFYQC0</td>\n",
       "      <td>2022-03-23</td>\n",
       "      <td>892.69</td>\n",
       "      <td>18C</td>\n",
       "      <td>active</td>\n",
       "      <td>Air New Zealand</td>\n",
       "      <td>NZ</td>\n",
       "      <td>ANZ</td>\n",
       "      <td>NEW ZEALAND</td>\n",
       "      <td>...</td>\n",
       "      <td>Hall</td>\n",
       "      <td>NB</td>\n",
       "      <td>1944-08-31</td>\n",
       "      <td>sarah.hall.44@gmail.com</td>\n",
       "      <td>75420 Michael Mountains Suite 485</td>\n",
       "      <td>New Victoria</td>\n",
       "      <td>HI</td>\n",
       "      <td>96727</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>396-673460-1326</td>\n",
       "      <td>N5UOOZ</td>\n",
       "      <td>2022-03-23</td>\n",
       "      <td>889.53</td>\n",
       "      <td>3C</td>\n",
       "      <td>active</td>\n",
       "      <td>Jeju Air</td>\n",
       "      <td>7C</td>\n",
       "      <td>JJA</td>\n",
       "      <td>JEJU AIR</td>\n",
       "      <td>...</td>\n",
       "      <td>Thompson</td>\n",
       "      <td>M</td>\n",
       "      <td>1968-05-02</td>\n",
       "      <td>seth.thompson.68@yahoo.com</td>\n",
       "      <td>22455 Higgins Junction Apt. 042</td>\n",
       "      <td>New Keith</td>\n",
       "      <td>OR</td>\n",
       "      <td>97405</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>380-894599-8109</td>\n",
       "      <td>PAA19Y</td>\n",
       "      <td>2022-03-22</td>\n",
       "      <td>706.78</td>\n",
       "      <td>7D</td>\n",
       "      <td>active</td>\n",
       "      <td>American Airlines</td>\n",
       "      <td>AA</td>\n",
       "      <td>AAL</td>\n",
       "      <td>AMERICAN</td>\n",
       "      <td>...</td>\n",
       "      <td>Garcia</td>\n",
       "      <td>F</td>\n",
       "      <td>1950-02-12</td>\n",
       "      <td>jennifer.garcia.50@gmail.com</td>\n",
       "      <td>6607 Sharp Common</td>\n",
       "      <td>Chadstad</td>\n",
       "      <td>VA</td>\n",
       "      <td>22121</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>614-960971-2686</td>\n",
       "      <td>EF4BHJ</td>\n",
       "      <td>2022-03-23</td>\n",
       "      <td>486.40</td>\n",
       "      <td>24J</td>\n",
       "      <td>active</td>\n",
       "      <td>Juneyao Airlines</td>\n",
       "      <td>HO</td>\n",
       "      <td>DKH</td>\n",
       "      <td>JUNEYAO AIRLINES</td>\n",
       "      <td>...</td>\n",
       "      <td>Clark</td>\n",
       "      <td>F</td>\n",
       "      <td>1991-11-09</td>\n",
       "      <td>becky.clark.91@gmail.com</td>\n",
       "      <td>691 Jones Cliffs</td>\n",
       "      <td>Michaelburgh</td>\n",
       "      <td>TX</td>\n",
       "      <td>76003</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>481-321233-0702</td>\n",
       "      <td>FVM9EE</td>\n",
       "      <td>2022-03-23</td>\n",
       "      <td>855.93</td>\n",
       "      <td>16A</td>\n",
       "      <td>active</td>\n",
       "      <td>Royal Air Maroc</td>\n",
       "      <td>AT</td>\n",
       "      <td>RAM</td>\n",
       "      <td>ROYALAIR MAROC</td>\n",
       "      <td>...</td>\n",
       "      <td>Cook</td>\n",
       "      <td>M</td>\n",
       "      <td>1976-07-29</td>\n",
       "      <td>ronald.cook.76@hotmail.com</td>\n",
       "      <td>93328 Davis Island</td>\n",
       "      <td>Rodriguezside</td>\n",
       "      <td>MD</td>\n",
       "      <td>21408</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>10 rows × 40 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "       eticket_num confirmation ticket_date   price seat  status  \\\n",
       "0  498-938211-0795       ZVFDC4  2022-03-23  723.42  31I  active   \n",
       "1  482-850738-6048       IL5GUI  2022-03-23  765.18  29B  active   \n",
       "2  275-207321-8092       CYEFBC  2022-03-21  753.89  26I  active   \n",
       "3  246-793315-3102       ZNGPC2  2022-03-22  793.89  15A  active   \n",
       "4  091-128904-1226       MGSBD9  2022-03-24  820.25  17F  active   \n",
       "5  115-196069-8963       XFYQC0  2022-03-23  892.69  18C  active   \n",
       "6  396-673460-1326       N5UOOZ  2022-03-23  889.53   3C  active   \n",
       "7  380-894599-8109       PAA19Y  2022-03-22  706.78   7D  active   \n",
       "8  614-960971-2686       EF4BHJ  2022-03-23  486.40  24J  active   \n",
       "9  481-321233-0702       FVM9EE  2022-03-23  855.93  16A  active   \n",
       "\n",
       "             airline_name airline_iata airline_icao  airline_callsign  ...  \\\n",
       "0  China Eastern Airlines           MU          CES     CHINA EASTERN  ...   \n",
       "1       Hawaiian Airlines           HA          HAL          HAWAIIAN  ...   \n",
       "2                Wizz Air           W6          WZZ          WIZZ AIR  ...   \n",
       "3                 AirAsia           AK          AXM     ASIAN EXPRESS  ...   \n",
       "4         Xiamen Airlines           MF          CXA        XIAMEN AIR  ...   \n",
       "5         Air New Zealand           NZ          ANZ       NEW ZEALAND  ...   \n",
       "6                Jeju Air           7C          JJA          JEJU AIR  ...   \n",
       "7       American Airlines           AA          AAL          AMERICAN  ...   \n",
       "8        Juneyao Airlines           HO          DKH  JUNEYAO AIRLINES  ...   \n",
       "9         Royal Air Maroc           AT          RAM    ROYALAIR MAROC  ...   \n",
       "\n",
       "  passenger_last_name passenger_gender passenger_birth_date  \\\n",
       "0               Brown                M           1969-02-17   \n",
       "1                Kent                F           1998-08-05   \n",
       "2              Tucker                F           1965-01-22   \n",
       "3               Yates               NB           1975-03-31   \n",
       "4          Villanueva               NB           1945-08-14   \n",
       "5                Hall               NB           1944-08-31   \n",
       "6            Thompson                M           1968-05-02   \n",
       "7              Garcia                F           1950-02-12   \n",
       "8               Clark                F           1991-11-09   \n",
       "9                Cook                M           1976-07-29   \n",
       "\n",
       "                   passenger_email                   passenger_street  \\\n",
       "0      robert.brown.69@hotmail.com                    5007 Thomas Way   \n",
       "1        laura.kent.98@hotmail.com                13991 Davis Village   \n",
       "2       lisa.tucker.65@hotmail.com                   04135 Marvin Via   \n",
       "3       matthew.yates.75@yahoo.com      76045 Samantha Road Suite 111   \n",
       "4  megan.villanueva.45@hotmail.com      848 Melissa Springs Suite 947   \n",
       "5          sarah.hall.44@gmail.com  75420 Michael Mountains Suite 485   \n",
       "6       seth.thompson.68@yahoo.com    22455 Higgins Junction Apt. 042   \n",
       "7     jennifer.garcia.50@gmail.com                  6607 Sharp Common   \n",
       "8         becky.clark.91@gmail.com                   691 Jones Cliffs   \n",
       "9       ronald.cook.76@hotmail.com                 93328 Davis Island   \n",
       "\n",
       "           passenger_city  passenger_state  passenger_zip  origin destination  \n",
       "0          Lake Hollystad               DC          20027     NaN         NaN  \n",
       "1  North Catherineborough               PA          16516     NaN         NaN  \n",
       "2        North Kristabury               MA          01093     NaN         NaN  \n",
       "3            Lake Jeffrey               DE          19898     NaN         NaN  \n",
       "4              Kellerstad               TX          76177     NaN         NaN  \n",
       "5            New Victoria               HI          96727     NaN         NaN  \n",
       "6               New Keith               OR          97405     NaN         NaN  \n",
       "7                Chadstad               VA          22121     NaN         NaN  \n",
       "8            Michaelburgh               TX          76003     NaN         NaN  \n",
       "9           Rodriguezside               MD          21408     NaN         NaN  \n",
       "\n",
       "[10 rows x 40 columns]"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "# modify column name to work better with big query\n",
    "df.columns = df.columns.str.replace(\".\", \"_\")\n",
    "display(df.head(n=10))"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Define dimension table schema"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "[DEBUG][2023-01-08 15:16:03,235][3082655590:0060] : Creating bigquery client\n",
      "[INFO ][2023-01-08 15:16:03,238][3082655590:0063] : Setup Completed\n"
     ]
    }
   ],
   "source": [
    "# setting table schema for dims tables\n",
    "\n",
    "DIMS_TABLE_METADATA = {  \n",
    "    # for airlines\n",
    "    'airlines': {\n",
    "        'table_name': 'airlines',\n",
    "        'schema': [\n",
    "            bigquery.SchemaField('airline_iata', 'STRING', mode='REQUIRED'),\n",
    "            bigquery.SchemaField('airline_name', 'STRING', mode='REQUIRED'),\n",
    "            bigquery.SchemaField('airline_icao', 'STRING', mode='NULLABLE'),\n",
    "            bigquery.SchemaField('airline_callsign', 'STRING', mode='NULLABLE'),\n",
    "            bigquery.SchemaField('airline_country', 'STRING', mode='NULLABLE'),\n",
    "        ],\n",
    "    },\n",
    "    # for airports\n",
    "    'airports': {\n",
    "        'table_name': 'airports',\n",
    "        'schema': [\n",
    "            bigquery.SchemaField('airport_iata', 'STRING', mode='REQUIRED'),\n",
    "            bigquery.SchemaField('airport_name', 'STRING', mode='REQUIRED'),\n",
    "            bigquery.SchemaField('airport_city', 'STRING', mode='NULLABLE'),\n",
    "            bigquery.SchemaField('airport_icao', 'STRING', mode='NULLABLE'),\n",
    "            bigquery.SchemaField('airport_latitude', 'FLOAT', mode='NULLABLE'),\n",
    "            bigquery.SchemaField('airport_longitude', 'FLOAT', mode='NULLABLE'),\n",
    "            bigquery.SchemaField('airport_altitude', 'FLOAT', mode='NULLABLE'),\n",
    "            bigquery.SchemaField('airport_tz_timezone', 'STRING', mode='NULLABLE'),\n",
    "        ],\n",
    "    },\n",
    "    # for passengers\n",
    "    'passengers': {\n",
    "        'table_name': 'passengers',\n",
    "        'schema': [\n",
    "            bigquery.SchemaField('passenger_sk', 'STRING', mode='REQUIRED'),\n",
    "            bigquery.SchemaField('passenger_email', 'STRING', mode='REQUIRED'),\n",
    "            bigquery.SchemaField('passenger_first_name', 'STRING', mode='NULLABLE'),\n",
    "            bigquery.SchemaField('passenger_last_name', 'STRING', mode='NULLABLE'),\n",
    "            bigquery.SchemaField('passenger_birth_date', 'STRING', mode='NULLABLE'),\n",
    "            bigquery.SchemaField('passenger_street', 'STRING', mode='NULLABLE'),\n",
    "            bigquery.SchemaField('passenger_city', 'STRING', mode='NULLABLE'),\n",
    "            bigquery.SchemaField('passenger_state', 'STRING', mode='NULLABLE'),\n",
    "            bigquery.SchemaField('passenger_zip', 'INTEGER', mode='NULLABLE'),\n",
    "            bigquery.SchemaField('effective_start_date', 'STRING', mode='NULLABLE'),\n",
    "            bigquery.SchemaField('effective_end_date', 'STRING', mode='NULLABLE'),\n",
    "        ],\n",
    "    },\n",
    "}\n",
    "\n",
    "# **** SETUP LOGGING ****\n",
    "# setup logging and logger\n",
    "logging.basicConfig(            # setting up the root logger\n",
    "    format='[%(levelname)-5s][%(asctime)s][%(module)s:%(lineno)04d] : %(message)s',\n",
    "    level=logging.INFO,\n",
    "    stream=sys.stdout\n",
    ")\n",
    "logger: logging.Logger = logging.getLogger('root')      # alias the root logger as `logger`\n",
    "logger.setLevel(logging.DEBUG)                          # programmatically reassign the logging level\n",
    "\n",
    "\n",
    "# **** BIGQUERY CLIENT ****\n",
    "logger.debug(f\"Creating bigquery client\")\n",
    "client = bigquery.Client()\n",
    "\n",
    "logger.info(f\"Setup Completed\")"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Create dataset in bigquery"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "[INFO ][2023-01-08 15:16:07,542][1320071499:0006] : Created air_travel dataset: deb-dev-dw:air_travel\n"
     ]
    }
   ],
   "source": [
    "\n",
    "dataset_id = f\"{PROJECT_NAME}.{DATASET_NAME}\"\n",
    "dataset = bigquery.Dataset(dataset_id)\n",
    "dataset.location = \"US\"\n",
    "dataset = client.create_dataset(dataset, exists_ok=True)\n",
    "# tells me that dataset was created with name\n",
    "logger.info(f\"Created air_travel dataset: {dataset.full_dataset_id}\")"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Load original data frame into separate data frames to make manipulations for dimensions"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "[INFO ][2023-01-08 15:16:09,424][1400822048:0009] : airlines - found 48 rows\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>airline_iata</th>\n",
       "      <th>airline_name</th>\n",
       "      <th>airline_icao</th>\n",
       "      <th>airline_callsign</th>\n",
       "      <th>airline_country</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>3U</td>\n",
       "      <td>Sichuan Airlines</td>\n",
       "      <td>CSC</td>\n",
       "      <td>SI CHUAN</td>\n",
       "      <td>China</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>7C</td>\n",
       "      <td>Jeju Air</td>\n",
       "      <td>JJA</td>\n",
       "      <td>JEJU AIR</td>\n",
       "      <td>Republic of Korea</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>9K</td>\n",
       "      <td>Cape Air</td>\n",
       "      <td>KAP</td>\n",
       "      <td>CAIR</td>\n",
       "      <td>United States</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>9S</td>\n",
       "      <td>Spring Airlines</td>\n",
       "      <td>CQH</td>\n",
       "      <td>AIR SPRING</td>\n",
       "      <td>China</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>AA</td>\n",
       "      <td>American Airlines</td>\n",
       "      <td>AAL</td>\n",
       "      <td>AMERICAN</td>\n",
       "      <td>United States</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>AC</td>\n",
       "      <td>Air Canada</td>\n",
       "      <td>ACA</td>\n",
       "      <td>AIR CANADA</td>\n",
       "      <td>Canada</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>AF</td>\n",
       "      <td>Air France</td>\n",
       "      <td>AFR</td>\n",
       "      <td>AIRFRANS</td>\n",
       "      <td>France</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>AK</td>\n",
       "      <td>AirAsia</td>\n",
       "      <td>AXM</td>\n",
       "      <td>ASIAN EXPRESS</td>\n",
       "      <td>Malaysia</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>AS</td>\n",
       "      <td>Alaska Airlines</td>\n",
       "      <td>ASA</td>\n",
       "      <td>Inc.</td>\n",
       "      <td>ALASKA</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>AT</td>\n",
       "      <td>Royal Air Maroc</td>\n",
       "      <td>RAM</td>\n",
       "      <td>ROYALAIR MAROC</td>\n",
       "      <td>Morocco</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>B6</td>\n",
       "      <td>JetBlue Airways</td>\n",
       "      <td>JBU</td>\n",
       "      <td>JETBLUE</td>\n",
       "      <td>United States</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>BA</td>\n",
       "      <td>British Airways</td>\n",
       "      <td>BAW</td>\n",
       "      <td>SPEEDBIRD</td>\n",
       "      <td>United Kingdom</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>BC</td>\n",
       "      <td>Skymark Airlines</td>\n",
       "      <td>SKY</td>\n",
       "      <td>SKYMARK</td>\n",
       "      <td>Japan</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>CA</td>\n",
       "      <td>Air China</td>\n",
       "      <td>CCA</td>\n",
       "      <td>AIR CHINA</td>\n",
       "      <td>China</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>CZ</td>\n",
       "      <td>China Southern Airlines</td>\n",
       "      <td>CSN</td>\n",
       "      <td>CHINA SOUTHERN</td>\n",
       "      <td>China</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>DD</td>\n",
       "      <td>Nok Air</td>\n",
       "      <td>NOK</td>\n",
       "      <td>NOK AIR</td>\n",
       "      <td>Thailand</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>DL</td>\n",
       "      <td>Delta Air Lines</td>\n",
       "      <td>DAL</td>\n",
       "      <td>DELTA</td>\n",
       "      <td>United States</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>ET</td>\n",
       "      <td>Ethiopian Airlines</td>\n",
       "      <td>ETH</td>\n",
       "      <td>ETHIOPIAN</td>\n",
       "      <td>Ethiopia</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>F9</td>\n",
       "      <td>Frontier Airlines</td>\n",
       "      <td>FFT</td>\n",
       "      <td>FRONTIER FLIGHT</td>\n",
       "      <td>United States</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>FD</td>\n",
       "      <td>Thai AirAsia</td>\n",
       "      <td>AIQ</td>\n",
       "      <td>THAI ASIA</td>\n",
       "      <td>Thailand</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td>FM</td>\n",
       "      <td>Shanghai Airlines</td>\n",
       "      <td>CSH</td>\n",
       "      <td>SHANGHAI AIR</td>\n",
       "      <td>China</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21</th>\n",
       "      <td>G4</td>\n",
       "      <td>Allegiant Air</td>\n",
       "      <td>AAY</td>\n",
       "      <td>ALLEGIANT</td>\n",
       "      <td>United States</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>22</th>\n",
       "      <td>HA</td>\n",
       "      <td>Hawaiian Airlines</td>\n",
       "      <td>HAL</td>\n",
       "      <td>HAWAIIAN</td>\n",
       "      <td>United States</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23</th>\n",
       "      <td>HO</td>\n",
       "      <td>Juneyao Airlines</td>\n",
       "      <td>DKH</td>\n",
       "      <td>JUNEYAO AIRLINES</td>\n",
       "      <td>China</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24</th>\n",
       "      <td>HR</td>\n",
       "      <td>China United Airlines</td>\n",
       "      <td>CUA</td>\n",
       "      <td>LIANHANG</td>\n",
       "      <td>China</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25</th>\n",
       "      <td>HU</td>\n",
       "      <td>Hainan Airlines</td>\n",
       "      <td>CHH</td>\n",
       "      <td>HAINAN</td>\n",
       "      <td>China</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>26</th>\n",
       "      <td>IW</td>\n",
       "      <td>Wings Air</td>\n",
       "      <td>WON</td>\n",
       "      <td>WINGS ABADI</td>\n",
       "      <td>Indonesia</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>27</th>\n",
       "      <td>MF</td>\n",
       "      <td>Xiamen Airlines</td>\n",
       "      <td>CXA</td>\n",
       "      <td>XIAMEN AIR</td>\n",
       "      <td>China</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>28</th>\n",
       "      <td>MH</td>\n",
       "      <td>Malaysia Airlines</td>\n",
       "      <td>MAS</td>\n",
       "      <td>MALAYSIAN</td>\n",
       "      <td>Malaysia</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>29</th>\n",
       "      <td>MU</td>\n",
       "      <td>China Eastern Airlines</td>\n",
       "      <td>CES</td>\n",
       "      <td>CHINA EASTERN</td>\n",
       "      <td>China</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>30</th>\n",
       "      <td>MW</td>\n",
       "      <td>Maya Island Air</td>\n",
       "      <td>MYD</td>\n",
       "      <td>MYLAND</td>\n",
       "      <td>Belize</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>31</th>\n",
       "      <td>NK</td>\n",
       "      <td>Spirit Airlines</td>\n",
       "      <td>NKS</td>\n",
       "      <td>SPIRIT WINGS</td>\n",
       "      <td>United States</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>32</th>\n",
       "      <td>NZ</td>\n",
       "      <td>Air New Zealand</td>\n",
       "      <td>ANZ</td>\n",
       "      <td>NEW ZEALAND</td>\n",
       "      <td>New Zealand</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>33</th>\n",
       "      <td>PM</td>\n",
       "      <td>Tropic Air</td>\n",
       "      <td>TOS</td>\n",
       "      <td>TROPISER</td>\n",
       "      <td>Belize</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>34</th>\n",
       "      <td>PR</td>\n",
       "      <td>Philippine Airlines</td>\n",
       "      <td>PAL</td>\n",
       "      <td>PHILIPPINE</td>\n",
       "      <td>Philippines</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>35</th>\n",
       "      <td>QR</td>\n",
       "      <td>Qatar Airways</td>\n",
       "      <td>QTR</td>\n",
       "      <td>QATARI</td>\n",
       "      <td>Qatar</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>36</th>\n",
       "      <td>QZ</td>\n",
       "      <td>Indonesia AirAsia</td>\n",
       "      <td>AWQ</td>\n",
       "      <td>WAGON AIR</td>\n",
       "      <td>Indonesia</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>37</th>\n",
       "      <td>S7</td>\n",
       "      <td>S7 Airlines</td>\n",
       "      <td>SBI</td>\n",
       "      <td>SIBERIAN AIRLINES</td>\n",
       "      <td>Russia</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>38</th>\n",
       "      <td>SC</td>\n",
       "      <td>Shandong Airlines</td>\n",
       "      <td>CDG</td>\n",
       "      <td>SHANDONG</td>\n",
       "      <td>China</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>39</th>\n",
       "      <td>SJ</td>\n",
       "      <td>Sriwijaya Air</td>\n",
       "      <td>SJY</td>\n",
       "      <td>SRIWIJAYA</td>\n",
       "      <td>Indonesia</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>40</th>\n",
       "      <td>TK</td>\n",
       "      <td>Turkish Airlines</td>\n",
       "      <td>THY</td>\n",
       "      <td>TURKAIR</td>\n",
       "      <td>Turkey</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>41</th>\n",
       "      <td>U6</td>\n",
       "      <td>Ural Airlines</td>\n",
       "      <td>SVR</td>\n",
       "      <td>SVERDLOVSK AIR</td>\n",
       "      <td>Russia</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>42</th>\n",
       "      <td>UA</td>\n",
       "      <td>United Airlines</td>\n",
       "      <td>UAL</td>\n",
       "      <td>UNITED</td>\n",
       "      <td>United States</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>43</th>\n",
       "      <td>VN</td>\n",
       "      <td>Vietnam Airlines</td>\n",
       "      <td>HVN</td>\n",
       "      <td>VIET NAM AIRLINES</td>\n",
       "      <td>Vietnam</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>44</th>\n",
       "      <td>W6</td>\n",
       "      <td>Wizz Air</td>\n",
       "      <td>WZZ</td>\n",
       "      <td>WIZZ AIR</td>\n",
       "      <td>Hungary</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>45</th>\n",
       "      <td>WN</td>\n",
       "      <td>Southwest Airlines</td>\n",
       "      <td>SWA</td>\n",
       "      <td>SOUTHWEST</td>\n",
       "      <td>United States</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>46</th>\n",
       "      <td>WY</td>\n",
       "      <td>Oman Air</td>\n",
       "      <td>OMA</td>\n",
       "      <td>OMAN AIR</td>\n",
       "      <td>Oman</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>47</th>\n",
       "      <td>ZH</td>\n",
       "      <td>Shenzhen Airlines</td>\n",
       "      <td>CSZ</td>\n",
       "      <td>SHENZHEN AIR</td>\n",
       "      <td>China</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   airline_iata             airline_name airline_icao   airline_callsign  \\\n",
       "0            3U         Sichuan Airlines          CSC           SI CHUAN   \n",
       "1            7C                 Jeju Air          JJA           JEJU AIR   \n",
       "2            9K                 Cape Air          KAP               CAIR   \n",
       "3            9S          Spring Airlines          CQH         AIR SPRING   \n",
       "4            AA        American Airlines          AAL           AMERICAN   \n",
       "5            AC               Air Canada          ACA         AIR CANADA   \n",
       "6            AF               Air France          AFR           AIRFRANS   \n",
       "7            AK                  AirAsia          AXM      ASIAN EXPRESS   \n",
       "8            AS          Alaska Airlines          ASA               Inc.   \n",
       "9            AT          Royal Air Maroc          RAM     ROYALAIR MAROC   \n",
       "10           B6          JetBlue Airways          JBU            JETBLUE   \n",
       "11           BA          British Airways          BAW          SPEEDBIRD   \n",
       "12           BC         Skymark Airlines          SKY            SKYMARK   \n",
       "13           CA                Air China          CCA          AIR CHINA   \n",
       "14           CZ  China Southern Airlines          CSN     CHINA SOUTHERN   \n",
       "15           DD                  Nok Air          NOK            NOK AIR   \n",
       "16           DL          Delta Air Lines          DAL              DELTA   \n",
       "17           ET       Ethiopian Airlines          ETH          ETHIOPIAN   \n",
       "18           F9        Frontier Airlines          FFT    FRONTIER FLIGHT   \n",
       "19           FD             Thai AirAsia          AIQ          THAI ASIA   \n",
       "20           FM        Shanghai Airlines          CSH       SHANGHAI AIR   \n",
       "21           G4            Allegiant Air          AAY          ALLEGIANT   \n",
       "22           HA        Hawaiian Airlines          HAL           HAWAIIAN   \n",
       "23           HO         Juneyao Airlines          DKH   JUNEYAO AIRLINES   \n",
       "24           HR    China United Airlines          CUA           LIANHANG   \n",
       "25           HU          Hainan Airlines          CHH             HAINAN   \n",
       "26           IW                Wings Air          WON        WINGS ABADI   \n",
       "27           MF          Xiamen Airlines          CXA         XIAMEN AIR   \n",
       "28           MH        Malaysia Airlines          MAS          MALAYSIAN   \n",
       "29           MU   China Eastern Airlines          CES      CHINA EASTERN   \n",
       "30           MW          Maya Island Air          MYD             MYLAND   \n",
       "31           NK          Spirit Airlines          NKS       SPIRIT WINGS   \n",
       "32           NZ          Air New Zealand          ANZ        NEW ZEALAND   \n",
       "33           PM               Tropic Air          TOS           TROPISER   \n",
       "34           PR      Philippine Airlines          PAL         PHILIPPINE   \n",
       "35           QR            Qatar Airways          QTR             QATARI   \n",
       "36           QZ        Indonesia AirAsia          AWQ          WAGON AIR   \n",
       "37           S7              S7 Airlines          SBI  SIBERIAN AIRLINES   \n",
       "38           SC        Shandong Airlines          CDG           SHANDONG   \n",
       "39           SJ            Sriwijaya Air          SJY          SRIWIJAYA   \n",
       "40           TK         Turkish Airlines          THY            TURKAIR   \n",
       "41           U6            Ural Airlines          SVR     SVERDLOVSK AIR   \n",
       "42           UA          United Airlines          UAL             UNITED   \n",
       "43           VN         Vietnam Airlines          HVN  VIET NAM AIRLINES   \n",
       "44           W6                 Wizz Air          WZZ           WIZZ AIR   \n",
       "45           WN       Southwest Airlines          SWA          SOUTHWEST   \n",
       "46           WY                 Oman Air          OMA           OMAN AIR   \n",
       "47           ZH        Shenzhen Airlines          CSZ       SHENZHEN AIR   \n",
       "\n",
       "      airline_country  \n",
       "0               China  \n",
       "1   Republic of Korea  \n",
       "2       United States  \n",
       "3               China  \n",
       "4       United States  \n",
       "5              Canada  \n",
       "6              France  \n",
       "7            Malaysia  \n",
       "8              ALASKA  \n",
       "9             Morocco  \n",
       "10      United States  \n",
       "11     United Kingdom  \n",
       "12              Japan  \n",
       "13              China  \n",
       "14              China  \n",
       "15           Thailand  \n",
       "16      United States  \n",
       "17           Ethiopia  \n",
       "18      United States  \n",
       "19           Thailand  \n",
       "20              China  \n",
       "21      United States  \n",
       "22      United States  \n",
       "23              China  \n",
       "24              China  \n",
       "25              China  \n",
       "26          Indonesia  \n",
       "27              China  \n",
       "28           Malaysia  \n",
       "29              China  \n",
       "30             Belize  \n",
       "31      United States  \n",
       "32        New Zealand  \n",
       "33             Belize  \n",
       "34        Philippines  \n",
       "35              Qatar  \n",
       "36          Indonesia  \n",
       "37             Russia  \n",
       "38              China  \n",
       "39          Indonesia  \n",
       "40             Turkey  \n",
       "41             Russia  \n",
       "42      United States  \n",
       "43            Vietnam  \n",
       "44            Hungary  \n",
       "45      United States  \n",
       "46               Oman  \n",
       "47              China  "
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "# airlines dimension table\n",
    "airlines_df = df\n",
    "# columns to keep for dimension\n",
    "airline_expected_cols = ['airline_iata', 'airline_name', 'airline_icao', 'airline_callsign', 'airline_country']\n",
    "# groupby above columns\n",
    "airlines_df = airlines_df.groupby(airline_expected_cols).all()\n",
    "airlines_df = airlines_df.reset_index().loc[:, airline_expected_cols]\n",
    "\n",
    "logger.info(f\"airlines - found {len(airlines_df.index)} rows\")\n",
    "\n",
    "display(airlines_df)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "[INFO ][2023-01-08 15:16:14,189][4159366420:0058] : airports - found 386 rows\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>airport_name</th>\n",
       "      <th>airport_city</th>\n",
       "      <th>airport_country</th>\n",
       "      <th>airport_iata</th>\n",
       "      <th>airport_icao</th>\n",
       "      <th>airport_latitude</th>\n",
       "      <th>airport_longitude</th>\n",
       "      <th>airport_altitude</th>\n",
       "      <th>airport_tz_timezone</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Abu Dhabi International Airport</td>\n",
       "      <td>Abu Dhabi</td>\n",
       "      <td>United Arab Emirates</td>\n",
       "      <td>AUH</td>\n",
       "      <td>OMAA</td>\n",
       "      <td>24.43</td>\n",
       "      <td>54.65</td>\n",
       "      <td>88.0</td>\n",
       "      <td>Asia/Dubai</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Adolfo Suárez Madrid–Barajas Airport</td>\n",
       "      <td>Madrid</td>\n",
       "      <td>Spain</td>\n",
       "      <td>MAD</td>\n",
       "      <td>LEMD</td>\n",
       "      <td>40.47</td>\n",
       "      <td>-3.56</td>\n",
       "      <td>1998.0</td>\n",
       "      <td>Europe/Madrid</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Afonso Pena Airport</td>\n",
       "      <td>Curitiba</td>\n",
       "      <td>Brazil</td>\n",
       "      <td>CWB</td>\n",
       "      <td>SBCT</td>\n",
       "      <td>-25.53</td>\n",
       "      <td>-49.18</td>\n",
       "      <td>2988.0</td>\n",
       "      <td>America/Sao_Paulo</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Alberto Alcolumbre Airport</td>\n",
       "      <td>Macapa</td>\n",
       "      <td>Brazil</td>\n",
       "      <td>MCP</td>\n",
       "      <td>SBMQ</td>\n",
       "      <td>0.05</td>\n",
       "      <td>-51.07</td>\n",
       "      <td>56.0</td>\n",
       "      <td>America/Fortaleza</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Albuquerque International Sunport</td>\n",
       "      <td>Albuquerque</td>\n",
       "      <td>United States</td>\n",
       "      <td>ABQ</td>\n",
       "      <td>KABQ</td>\n",
       "      <td>35.04</td>\n",
       "      <td>-106.61</td>\n",
       "      <td>5355.0</td>\n",
       "      <td>America/Denver</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",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>381</th>\n",
       "      <td>Zhuhai Jinwan Airport</td>\n",
       "      <td>Zhuhai</td>\n",
       "      <td>China</td>\n",
       "      <td>ZUH</td>\n",
       "      <td>ZGSD</td>\n",
       "      <td>22.01</td>\n",
       "      <td>113.38</td>\n",
       "      <td>23.0</td>\n",
       "      <td>Asia/Shanghai</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>382</th>\n",
       "      <td>Zumbi dos Palmares Airport</td>\n",
       "      <td>Maceio</td>\n",
       "      <td>Brazil</td>\n",
       "      <td>MCZ</td>\n",
       "      <td>SBMO</td>\n",
       "      <td>-9.51</td>\n",
       "      <td>-35.79</td>\n",
       "      <td>387.0</td>\n",
       "      <td>America/Fortaleza</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>383</th>\n",
       "      <td>Zürich Airport</td>\n",
       "      <td>Zurich</td>\n",
       "      <td>Switzerland</td>\n",
       "      <td>ZRH</td>\n",
       "      <td>LSZH</td>\n",
       "      <td>47.46</td>\n",
       "      <td>8.55</td>\n",
       "      <td>1416.0</td>\n",
       "      <td>Europe/Zurich</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>384</th>\n",
       "      <td>Ástor Piazzola International Airport</td>\n",
       "      <td>Mar Del Plata</td>\n",
       "      <td>Argentina</td>\n",
       "      <td>MDQ</td>\n",
       "      <td>SAZM</td>\n",
       "      <td>-37.93</td>\n",
       "      <td>-57.57</td>\n",
       "      <td>72.0</td>\n",
       "      <td>America/Buenos_Aires</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>385</th>\n",
       "      <td>Ürümqi Diwopu International Airport</td>\n",
       "      <td>Urumqi</td>\n",
       "      <td>China</td>\n",
       "      <td>URC</td>\n",
       "      <td>ZWWW</td>\n",
       "      <td>43.91</td>\n",
       "      <td>87.47</td>\n",
       "      <td>2125.0</td>\n",
       "      <td>Asia/Shanghai</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>386 rows × 9 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                             airport_name   airport_city  \\\n",
       "0         Abu Dhabi International Airport      Abu Dhabi   \n",
       "1    Adolfo Suárez Madrid–Barajas Airport         Madrid   \n",
       "2                     Afonso Pena Airport       Curitiba   \n",
       "3              Alberto Alcolumbre Airport         Macapa   \n",
       "4       Albuquerque International Sunport    Albuquerque   \n",
       "..                                    ...            ...   \n",
       "381                 Zhuhai Jinwan Airport         Zhuhai   \n",
       "382            Zumbi dos Palmares Airport         Maceio   \n",
       "383                        Zürich Airport         Zurich   \n",
       "384  Ástor Piazzola International Airport  Mar Del Plata   \n",
       "385   Ürümqi Diwopu International Airport         Urumqi   \n",
       "\n",
       "          airport_country airport_iata airport_icao  airport_latitude  \\\n",
       "0    United Arab Emirates          AUH         OMAA             24.43   \n",
       "1                   Spain          MAD         LEMD             40.47   \n",
       "2                  Brazil          CWB         SBCT            -25.53   \n",
       "3                  Brazil          MCP         SBMQ              0.05   \n",
       "4           United States          ABQ         KABQ             35.04   \n",
       "..                    ...          ...          ...               ...   \n",
       "381                 China          ZUH         ZGSD             22.01   \n",
       "382                Brazil          MCZ         SBMO             -9.51   \n",
       "383           Switzerland          ZRH         LSZH             47.46   \n",
       "384             Argentina          MDQ         SAZM            -37.93   \n",
       "385                 China          URC         ZWWW             43.91   \n",
       "\n",
       "     airport_longitude  airport_altitude   airport_tz_timezone  \n",
       "0                54.65              88.0            Asia/Dubai  \n",
       "1                -3.56            1998.0         Europe/Madrid  \n",
       "2               -49.18            2988.0     America/Sao_Paulo  \n",
       "3               -51.07              56.0     America/Fortaleza  \n",
       "4              -106.61            5355.0        America/Denver  \n",
       "..                 ...               ...                   ...  \n",
       "381             113.38              23.0         Asia/Shanghai  \n",
       "382             -35.79             387.0     America/Fortaleza  \n",
       "383               8.55            1416.0         Europe/Zurich  \n",
       "384             -57.57              72.0  America/Buenos_Aires  \n",
       "385              87.47            2125.0         Asia/Shanghai  \n",
       "\n",
       "[386 rows x 9 columns]"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "# airports dimension table\n",
    "\n",
    "origin_df = df\n",
    "dest_df = df\n",
    "\n",
    "airport_expected_cols = ['name', 'city','country', 'iata', 'icao','latitude','longitude','altitude', 'tz_timezone']\n",
    "\n",
    "origin_col = ['origin_' + col for col in airport_expected_cols]\n",
    "dest_col = ['destination_' + col for col in airport_expected_cols]\n",
    "airport_col = ['airport_' + col for col in airport_expected_cols]\n",
    "\n",
    "# group unique origin airports\n",
    "origin_df = origin_df.groupby(origin_col).all()\n",
    "origin_df = origin_df.reset_index().loc[:, origin_col]\n",
    "\n",
    "# rename columns\n",
    "\n",
    "origin_df = origin_df.rename(columns={\n",
    "    'origin_name': 'airport_name', \n",
    "    'origin_city':'airport_city', \n",
    "    'origin_country':'airport_country',\n",
    "    'origin_iata':'airport_iata',\n",
    "    'origin_icao':'airport_icao',\n",
    "    'origin_latitude':'airport_latitude',\n",
    "    'origin_longitude':'airport_longitude',\n",
    "    'origin_altitude':'airport_altitude',\n",
    "    'origin_tz_timezone':'airport_tz_timezone',\n",
    "    })\n",
    "\n",
    "# group unique destination airports\n",
    "dest_df = dest_df.groupby(dest_col).all()\n",
    "dest_df = dest_df.reset_index().loc[:, dest_col]\n",
    "\n",
    "# rename columns\n",
    "\n",
    "dest_df = dest_df.rename(columns={\n",
    "    'destination_name': 'airport_name', \n",
    "    'destination_city':'airport_city', \n",
    "    'destination_country':'airport_country',\n",
    "    'destination_iata':'airport_iata',\n",
    "    'destination_icao':'airport_icao',\n",
    "    'destination_latitude':'airport_latitude',\n",
    "    'destination_longitude':'airport_longitude',\n",
    "    'destination_altitude':'airport_altitude',\n",
    "    'destination_tz_timezone':'airport_tz_timezone',\n",
    "    })\n",
    "\n",
    "# combine origin and destination\n",
    "concat_df = [origin_df, dest_df]\n",
    "\n",
    "airports_df = pd.concat(concat_df)\n",
    "\n",
    "# group unique combined airports data\n",
    "\n",
    "airports_df = airports_df.groupby(airport_col).all()\n",
    "airports_df = airports_df.reset_index().loc[:, airport_col]\n",
    "\n",
    "logger.info(f\"airports - found {len(airports_df.index)} rows\")\n",
    "\n",
    "display(airports_df)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "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>passenger_sk</th>\n",
       "      <th>passenger_email</th>\n",
       "      <th>passenger_first_name</th>\n",
       "      <th>passenger_last_name</th>\n",
       "      <th>passenger_birth_date</th>\n",
       "      <th>passenger_street</th>\n",
       "      <th>passenger_city</th>\n",
       "      <th>passenger_state</th>\n",
       "      <th>passenger_zip</th>\n",
       "      <th>effective_start_date</th>\n",
       "      <th>effective_end_date</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>f80507ee-6fb5-4980-80e4-21d56bdb4a69</td>\n",
       "      <td>annette.hawkins.43@yahoo.com</td>\n",
       "      <td>Annette</td>\n",
       "      <td>Hawkins</td>\n",
       "      <td>1943-07-11</td>\n",
       "      <td>361 Robinson Green Apt. 635</td>\n",
       "      <td>North Lynntown</td>\n",
       "      <td>NV</td>\n",
       "      <td>89825</td>\n",
       "      <td>2006-06-06</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>24887511-ab41-411e-b61a-d4ff08fb61ec</td>\n",
       "      <td>autumn.morse.60@hotmail.com</td>\n",
       "      <td>Autumn</td>\n",
       "      <td>Morse</td>\n",
       "      <td>1960-01-18</td>\n",
       "      <td>6984 Price Shoals</td>\n",
       "      <td>Erictown</td>\n",
       "      <td>HI</td>\n",
       "      <td>96818</td>\n",
       "      <td>2006-06-06</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1c9bbcdd-dc23-41a3-94ac-333122241e8a</td>\n",
       "      <td>becky.clark.91@gmail.com</td>\n",
       "      <td>Becky</td>\n",
       "      <td>Clark</td>\n",
       "      <td>1991-11-09</td>\n",
       "      <td>691 Jones Cliffs</td>\n",
       "      <td>Michaelburgh</td>\n",
       "      <td>TX</td>\n",
       "      <td>76003</td>\n",
       "      <td>2006-06-06</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>e532ce0d-3379-4555-8d6d-644b7bb3121e</td>\n",
       "      <td>belinda.cook.91@hotmail.com</td>\n",
       "      <td>Belinda</td>\n",
       "      <td>Cook</td>\n",
       "      <td>1991-01-26</td>\n",
       "      <td>1965 Kelly Field Apt. 094</td>\n",
       "      <td>Jonesberg</td>\n",
       "      <td>IL</td>\n",
       "      <td>60613</td>\n",
       "      <td>2006-06-06</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>70ddc90d-ef8c-40c4-9ad3-dafbd9c91585</td>\n",
       "      <td>carl.wilson.80@hotmail.com</td>\n",
       "      <td>Carl</td>\n",
       "      <td>Wilson</td>\n",
       "      <td>1980-04-24</td>\n",
       "      <td>2814 Houston Hills</td>\n",
       "      <td>Rodriguezside</td>\n",
       "      <td>IA</td>\n",
       "      <td>51971</td>\n",
       "      <td>2006-06-06</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>2deffd76-1283-40f1-833c-80e9ead122a3</td>\n",
       "      <td>cheryl.hughes.45@gmail.com</td>\n",
       "      <td>Cheryl</td>\n",
       "      <td>Hughes</td>\n",
       "      <td>1945-05-20</td>\n",
       "      <td>00992 Garcia Plaza Suite 367</td>\n",
       "      <td>North Chelseamouth</td>\n",
       "      <td>CT</td>\n",
       "      <td>6315</td>\n",
       "      <td>2006-06-06</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>eb32e652-ee1b-4752-b32e-a0e3e5166955</td>\n",
       "      <td>christian.stevenson.93@hotmail.com</td>\n",
       "      <td>Christian</td>\n",
       "      <td>Stevenson</td>\n",
       "      <td>1993-06-14</td>\n",
       "      <td>75945 Jennifer Loaf</td>\n",
       "      <td>Pooleland</td>\n",
       "      <td>KY</td>\n",
       "      <td>40009</td>\n",
       "      <td>2006-06-06</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>6a7dbec7-2b3a-4dd6-8168-00dc8ae34b03</td>\n",
       "      <td>corey.cook.83@gmail.com</td>\n",
       "      <td>Corey</td>\n",
       "      <td>Cook</td>\n",
       "      <td>1983-06-14</td>\n",
       "      <td>9606 Barton Station Apt. 271</td>\n",
       "      <td>Jacquelinemouth</td>\n",
       "      <td>IN</td>\n",
       "      <td>47081</td>\n",
       "      <td>2006-06-06</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>2a7044f4-6763-4976-a898-c66bc68fa461</td>\n",
       "      <td>danielle.henderson.70@hotmail.com</td>\n",
       "      <td>Danielle</td>\n",
       "      <td>Henderson</td>\n",
       "      <td>1970-08-11</td>\n",
       "      <td>7389 Alec Squares Suite 508</td>\n",
       "      <td>Port Jonathan</td>\n",
       "      <td>NM</td>\n",
       "      <td>87320</td>\n",
       "      <td>2006-06-06</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>5074c5fd-a544-41ad-a322-12d6e3d8dcab</td>\n",
       "      <td>hannah.smith.66@gmail.com</td>\n",
       "      <td>Hannah</td>\n",
       "      <td>Smith</td>\n",
       "      <td>1966-07-01</td>\n",
       "      <td>230 Donna Street</td>\n",
       "      <td>Lake Adrianstad</td>\n",
       "      <td>MN</td>\n",
       "      <td>56413</td>\n",
       "      <td>2006-06-06</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>df4da838-d123-43f6-8d76-81155e1fe80d</td>\n",
       "      <td>janice.zamora.00@gmail.com</td>\n",
       "      <td>Janice</td>\n",
       "      <td>Zamora</td>\n",
       "      <td>2000-06-14</td>\n",
       "      <td>4759 William Haven Apt. 194</td>\n",
       "      <td>West Corey</td>\n",
       "      <td>KS</td>\n",
       "      <td>66153</td>\n",
       "      <td>2006-06-06</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>f2f9be8f-5d0b-4ded-bb20-42eba2515e12</td>\n",
       "      <td>jennifer.garcia.50@gmail.com</td>\n",
       "      <td>Jennifer</td>\n",
       "      <td>Garcia</td>\n",
       "      <td>1950-02-12</td>\n",
       "      <td>6607 Sharp Common</td>\n",
       "      <td>Chadstad</td>\n",
       "      <td>VA</td>\n",
       "      <td>22121</td>\n",
       "      <td>2006-06-06</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>631b959a-0557-4abb-a3af-604a67c3e681</td>\n",
       "      <td>julie.carlson.77@hotmail.com</td>\n",
       "      <td>Julie</td>\n",
       "      <td>Carlson</td>\n",
       "      <td>1977-04-05</td>\n",
       "      <td>5792 Tammy Centers</td>\n",
       "      <td>Port Fernando</td>\n",
       "      <td>MO</td>\n",
       "      <td>65169</td>\n",
       "      <td>2006-06-06</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>5a7f126e-67a0-48f9-8f80-30dddc4c8694</td>\n",
       "      <td>kevin.moore.79@gmail.com</td>\n",
       "      <td>Kevin</td>\n",
       "      <td>Moore</td>\n",
       "      <td>1979-04-11</td>\n",
       "      <td>8332 Watson Shore</td>\n",
       "      <td>Port Davidtown</td>\n",
       "      <td>DC</td>\n",
       "      <td>20026</td>\n",
       "      <td>2006-06-06</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>321acc73-b8a9-42b9-b3a3-b9df4dff6d6f</td>\n",
       "      <td>laura.kent.98@hotmail.com</td>\n",
       "      <td>Laura</td>\n",
       "      <td>Kent</td>\n",
       "      <td>1998-08-05</td>\n",
       "      <td>13991 Davis Village</td>\n",
       "      <td>North Catherineborough</td>\n",
       "      <td>PA</td>\n",
       "      <td>16516</td>\n",
       "      <td>2006-06-06</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>f0bbaf49-fe10-45bd-8833-eabd551fb87e</td>\n",
       "      <td>laurie.york.84@gmail.com</td>\n",
       "      <td>Laurie</td>\n",
       "      <td>York</td>\n",
       "      <td>1984-04-28</td>\n",
       "      <td>248 Cook Park</td>\n",
       "      <td>Sherriport</td>\n",
       "      <td>TN</td>\n",
       "      <td>37845</td>\n",
       "      <td>2006-06-06</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>6db47f33-f752-4ec9-abcb-de4d561b417a</td>\n",
       "      <td>lisa.tucker.65@hotmail.com</td>\n",
       "      <td>Lisa</td>\n",
       "      <td>Tucker</td>\n",
       "      <td>1965-01-22</td>\n",
       "      <td>04135 Marvin Via</td>\n",
       "      <td>North Kristabury</td>\n",
       "      <td>MA</td>\n",
       "      <td>1093</td>\n",
       "      <td>2006-06-06</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>d9f29ef6-0a3c-4883-9e48-cf6981f67c87</td>\n",
       "      <td>matthew.yates.75@yahoo.com</td>\n",
       "      <td>Matthew</td>\n",
       "      <td>Yates</td>\n",
       "      <td>1975-03-31</td>\n",
       "      <td>76045 Samantha Road Suite 111</td>\n",
       "      <td>Lake Jeffrey</td>\n",
       "      <td>DE</td>\n",
       "      <td>19898</td>\n",
       "      <td>2006-06-06</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>465f9c90-97c9-4830-bd9c-4d59279b8804</td>\n",
       "      <td>megan.villanueva.45@hotmail.com</td>\n",
       "      <td>Megan</td>\n",
       "      <td>Villanueva</td>\n",
       "      <td>1945-08-14</td>\n",
       "      <td>848 Melissa Springs Suite 947</td>\n",
       "      <td>Kellerstad</td>\n",
       "      <td>TX</td>\n",
       "      <td>76177</td>\n",
       "      <td>2006-06-06</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>78e78cee-a826-4c55-83a7-42d04c67348f</td>\n",
       "      <td>nicholas.phillips.00@hotmail.com</td>\n",
       "      <td>Nicholas</td>\n",
       "      <td>Phillips</td>\n",
       "      <td>2000-04-23</td>\n",
       "      <td>80132 Kevin Trace</td>\n",
       "      <td>Riveraburgh</td>\n",
       "      <td>NJ</td>\n",
       "      <td>8640</td>\n",
       "      <td>2006-06-06</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td>77e96f0e-6556-4b9e-be23-38667b1c90da</td>\n",
       "      <td>paul.baird.91@hotmail.com</td>\n",
       "      <td>Paul</td>\n",
       "      <td>Baird</td>\n",
       "      <td>1991-10-21</td>\n",
       "      <td>19344 Craig Walk</td>\n",
       "      <td>Burtontown</td>\n",
       "      <td>NC</td>\n",
       "      <td>27568</td>\n",
       "      <td>2006-06-06</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21</th>\n",
       "      <td>f46e6cdf-38cb-4df3-a4e3-d49352360ee2</td>\n",
       "      <td>rachel.duffy.60@hotmail.com</td>\n",
       "      <td>Rachel</td>\n",
       "      <td>Duffy</td>\n",
       "      <td>1960-05-14</td>\n",
       "      <td>18868 Dougherty Drive</td>\n",
       "      <td>Snowchester</td>\n",
       "      <td>OK</td>\n",
       "      <td>73022</td>\n",
       "      <td>2006-06-06</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>22</th>\n",
       "      <td>430dce00-471b-4a79-9a8e-ea254ee89655</td>\n",
       "      <td>rebecca.wilson.73@yahoo.com</td>\n",
       "      <td>Rebecca</td>\n",
       "      <td>Wilson</td>\n",
       "      <td>1973-10-24</td>\n",
       "      <td>24000 Erin Point Suite 590</td>\n",
       "      <td>Josephmouth</td>\n",
       "      <td>DC</td>\n",
       "      <td>20028</td>\n",
       "      <td>2006-06-06</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23</th>\n",
       "      <td>a1b77424-b7d9-465f-bfa3-fad0be1ae0c4</td>\n",
       "      <td>robert.brown.69@hotmail.com</td>\n",
       "      <td>Robert</td>\n",
       "      <td>Brown</td>\n",
       "      <td>1969-02-17</td>\n",
       "      <td>5007 Thomas Way</td>\n",
       "      <td>Lake Hollystad</td>\n",
       "      <td>DC</td>\n",
       "      <td>20027</td>\n",
       "      <td>2006-06-06</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24</th>\n",
       "      <td>2f16de6b-e8ed-43da-99c9-cb8348029059</td>\n",
       "      <td>ronald.cook.76@hotmail.com</td>\n",
       "      <td>Ronald</td>\n",
       "      <td>Cook</td>\n",
       "      <td>1976-07-29</td>\n",
       "      <td>93328 Davis Island</td>\n",
       "      <td>Rodriguezside</td>\n",
       "      <td>MD</td>\n",
       "      <td>21408</td>\n",
       "      <td>2006-06-06</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25</th>\n",
       "      <td>3c4c668b-10d6-4766-b702-6c5f884f08b4</td>\n",
       "      <td>sarah.hall.44@gmail.com</td>\n",
       "      <td>Sarah</td>\n",
       "      <td>Hall</td>\n",
       "      <td>1944-08-31</td>\n",
       "      <td>75420 Michael Mountains Suite 485</td>\n",
       "      <td>New Victoria</td>\n",
       "      <td>HI</td>\n",
       "      <td>96727</td>\n",
       "      <td>2006-06-06</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>26</th>\n",
       "      <td>e8639f38-7d47-4aa6-b3ce-65c3c18a859f</td>\n",
       "      <td>sarah.rivera.04@hotmail.com</td>\n",
       "      <td>Sarah</td>\n",
       "      <td>Rivera</td>\n",
       "      <td>2004-02-26</td>\n",
       "      <td>5938 Ramos Pike Suite 080</td>\n",
       "      <td>Lake Marytown</td>\n",
       "      <td>CT</td>\n",
       "      <td>6314</td>\n",
       "      <td>2006-06-06</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>27</th>\n",
       "      <td>3a48cce7-1db4-4e52-874c-0bbbbde3cc4a</td>\n",
       "      <td>sean.murray.53@yahoo.com</td>\n",
       "      <td>Sean</td>\n",
       "      <td>Murray</td>\n",
       "      <td>1953-02-11</td>\n",
       "      <td>809 Erika Valley Apt. 634</td>\n",
       "      <td>Petersenfort</td>\n",
       "      <td>NY</td>\n",
       "      <td>13184</td>\n",
       "      <td>2006-06-06</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>28</th>\n",
       "      <td>9b425a89-ef64-4510-b8e3-7a29cd5ea257</td>\n",
       "      <td>seth.thompson.68@yahoo.com</td>\n",
       "      <td>Seth</td>\n",
       "      <td>Thompson</td>\n",
       "      <td>1968-05-02</td>\n",
       "      <td>22455 Higgins Junction Apt. 042</td>\n",
       "      <td>New Keith</td>\n",
       "      <td>OR</td>\n",
       "      <td>97405</td>\n",
       "      <td>2006-06-06</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>29</th>\n",
       "      <td>f6cb3f09-04bd-4fe5-84b6-a7e5fd7cad4f</td>\n",
       "      <td>shannon.rhodes.78@gmail.com</td>\n",
       "      <td>Shannon</td>\n",
       "      <td>Rhodes</td>\n",
       "      <td>1978-10-09</td>\n",
       "      <td>3039 Daniel Mount Suite 582</td>\n",
       "      <td>New Tonyaberg</td>\n",
       "      <td>NM</td>\n",
       "      <td>88155</td>\n",
       "      <td>2006-06-06</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>30</th>\n",
       "      <td>1ea56280-0125-4e8d-be70-ffcfb2cd41da</td>\n",
       "      <td>timothy.santos.86@hotmail.com</td>\n",
       "      <td>Timothy</td>\n",
       "      <td>Santos</td>\n",
       "      <td>1986-01-07</td>\n",
       "      <td>64183 Kimberly Mills Suite 375</td>\n",
       "      <td>North Rachelton</td>\n",
       "      <td>VT</td>\n",
       "      <td>5249</td>\n",
       "      <td>2006-06-06</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>31</th>\n",
       "      <td>ed516be0-20fe-4aac-8cd7-338245ccf49d</td>\n",
       "      <td>tony.hoffman.03@gmail.com</td>\n",
       "      <td>Tony</td>\n",
       "      <td>Hoffman</td>\n",
       "      <td>2003-03-18</td>\n",
       "      <td>4706 Amy Roads Apt. 206</td>\n",
       "      <td>Stewartborough</td>\n",
       "      <td>AZ</td>\n",
       "      <td>85030</td>\n",
       "      <td>2006-06-06</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                            passenger_sk                     passenger_email  \\\n",
       "0   f80507ee-6fb5-4980-80e4-21d56bdb4a69        annette.hawkins.43@yahoo.com   \n",
       "1   24887511-ab41-411e-b61a-d4ff08fb61ec         autumn.morse.60@hotmail.com   \n",
       "2   1c9bbcdd-dc23-41a3-94ac-333122241e8a            becky.clark.91@gmail.com   \n",
       "3   e532ce0d-3379-4555-8d6d-644b7bb3121e         belinda.cook.91@hotmail.com   \n",
       "4   70ddc90d-ef8c-40c4-9ad3-dafbd9c91585          carl.wilson.80@hotmail.com   \n",
       "5   2deffd76-1283-40f1-833c-80e9ead122a3          cheryl.hughes.45@gmail.com   \n",
       "6   eb32e652-ee1b-4752-b32e-a0e3e5166955  christian.stevenson.93@hotmail.com   \n",
       "7   6a7dbec7-2b3a-4dd6-8168-00dc8ae34b03             corey.cook.83@gmail.com   \n",
       "8   2a7044f4-6763-4976-a898-c66bc68fa461   danielle.henderson.70@hotmail.com   \n",
       "9   5074c5fd-a544-41ad-a322-12d6e3d8dcab           hannah.smith.66@gmail.com   \n",
       "10  df4da838-d123-43f6-8d76-81155e1fe80d          janice.zamora.00@gmail.com   \n",
       "11  f2f9be8f-5d0b-4ded-bb20-42eba2515e12        jennifer.garcia.50@gmail.com   \n",
       "12  631b959a-0557-4abb-a3af-604a67c3e681        julie.carlson.77@hotmail.com   \n",
       "13  5a7f126e-67a0-48f9-8f80-30dddc4c8694            kevin.moore.79@gmail.com   \n",
       "14  321acc73-b8a9-42b9-b3a3-b9df4dff6d6f           laura.kent.98@hotmail.com   \n",
       "15  f0bbaf49-fe10-45bd-8833-eabd551fb87e            laurie.york.84@gmail.com   \n",
       "16  6db47f33-f752-4ec9-abcb-de4d561b417a          lisa.tucker.65@hotmail.com   \n",
       "17  d9f29ef6-0a3c-4883-9e48-cf6981f67c87          matthew.yates.75@yahoo.com   \n",
       "18  465f9c90-97c9-4830-bd9c-4d59279b8804     megan.villanueva.45@hotmail.com   \n",
       "19  78e78cee-a826-4c55-83a7-42d04c67348f    nicholas.phillips.00@hotmail.com   \n",
       "20  77e96f0e-6556-4b9e-be23-38667b1c90da           paul.baird.91@hotmail.com   \n",
       "21  f46e6cdf-38cb-4df3-a4e3-d49352360ee2         rachel.duffy.60@hotmail.com   \n",
       "22  430dce00-471b-4a79-9a8e-ea254ee89655         rebecca.wilson.73@yahoo.com   \n",
       "23  a1b77424-b7d9-465f-bfa3-fad0be1ae0c4         robert.brown.69@hotmail.com   \n",
       "24  2f16de6b-e8ed-43da-99c9-cb8348029059          ronald.cook.76@hotmail.com   \n",
       "25  3c4c668b-10d6-4766-b702-6c5f884f08b4             sarah.hall.44@gmail.com   \n",
       "26  e8639f38-7d47-4aa6-b3ce-65c3c18a859f         sarah.rivera.04@hotmail.com   \n",
       "27  3a48cce7-1db4-4e52-874c-0bbbbde3cc4a            sean.murray.53@yahoo.com   \n",
       "28  9b425a89-ef64-4510-b8e3-7a29cd5ea257          seth.thompson.68@yahoo.com   \n",
       "29  f6cb3f09-04bd-4fe5-84b6-a7e5fd7cad4f         shannon.rhodes.78@gmail.com   \n",
       "30  1ea56280-0125-4e8d-be70-ffcfb2cd41da       timothy.santos.86@hotmail.com   \n",
       "31  ed516be0-20fe-4aac-8cd7-338245ccf49d           tony.hoffman.03@gmail.com   \n",
       "\n",
       "   passenger_first_name passenger_last_name passenger_birth_date  \\\n",
       "0               Annette             Hawkins           1943-07-11   \n",
       "1                Autumn               Morse           1960-01-18   \n",
       "2                 Becky               Clark           1991-11-09   \n",
       "3               Belinda                Cook           1991-01-26   \n",
       "4                  Carl              Wilson           1980-04-24   \n",
       "5                Cheryl              Hughes           1945-05-20   \n",
       "6             Christian           Stevenson           1993-06-14   \n",
       "7                 Corey                Cook           1983-06-14   \n",
       "8              Danielle           Henderson           1970-08-11   \n",
       "9                Hannah               Smith           1966-07-01   \n",
       "10               Janice              Zamora           2000-06-14   \n",
       "11             Jennifer              Garcia           1950-02-12   \n",
       "12                Julie             Carlson           1977-04-05   \n",
       "13                Kevin               Moore           1979-04-11   \n",
       "14                Laura                Kent           1998-08-05   \n",
       "15               Laurie                York           1984-04-28   \n",
       "16                 Lisa              Tucker           1965-01-22   \n",
       "17              Matthew               Yates           1975-03-31   \n",
       "18                Megan          Villanueva           1945-08-14   \n",
       "19             Nicholas            Phillips           2000-04-23   \n",
       "20                 Paul               Baird           1991-10-21   \n",
       "21               Rachel               Duffy           1960-05-14   \n",
       "22              Rebecca              Wilson           1973-10-24   \n",
       "23               Robert               Brown           1969-02-17   \n",
       "24               Ronald                Cook           1976-07-29   \n",
       "25                Sarah                Hall           1944-08-31   \n",
       "26                Sarah              Rivera           2004-02-26   \n",
       "27                 Sean              Murray           1953-02-11   \n",
       "28                 Seth            Thompson           1968-05-02   \n",
       "29              Shannon              Rhodes           1978-10-09   \n",
       "30              Timothy              Santos           1986-01-07   \n",
       "31                 Tony             Hoffman           2003-03-18   \n",
       "\n",
       "                     passenger_street          passenger_city passenger_state  \\\n",
       "0         361 Robinson Green Apt. 635          North Lynntown              NV   \n",
       "1                   6984 Price Shoals                Erictown              HI   \n",
       "2                    691 Jones Cliffs            Michaelburgh              TX   \n",
       "3           1965 Kelly Field Apt. 094               Jonesberg              IL   \n",
       "4                  2814 Houston Hills           Rodriguezside              IA   \n",
       "5        00992 Garcia Plaza Suite 367      North Chelseamouth              CT   \n",
       "6                 75945 Jennifer Loaf               Pooleland              KY   \n",
       "7        9606 Barton Station Apt. 271         Jacquelinemouth              IN   \n",
       "8         7389 Alec Squares Suite 508           Port Jonathan              NM   \n",
       "9                    230 Donna Street         Lake Adrianstad              MN   \n",
       "10        4759 William Haven Apt. 194              West Corey              KS   \n",
       "11                  6607 Sharp Common                Chadstad              VA   \n",
       "12                 5792 Tammy Centers           Port Fernando              MO   \n",
       "13                  8332 Watson Shore          Port Davidtown              DC   \n",
       "14                13991 Davis Village  North Catherineborough              PA   \n",
       "15                      248 Cook Park              Sherriport              TN   \n",
       "16                   04135 Marvin Via        North Kristabury              MA   \n",
       "17      76045 Samantha Road Suite 111            Lake Jeffrey              DE   \n",
       "18      848 Melissa Springs Suite 947              Kellerstad              TX   \n",
       "19                  80132 Kevin Trace             Riveraburgh              NJ   \n",
       "20                   19344 Craig Walk              Burtontown              NC   \n",
       "21              18868 Dougherty Drive             Snowchester              OK   \n",
       "22         24000 Erin Point Suite 590             Josephmouth              DC   \n",
       "23                    5007 Thomas Way          Lake Hollystad              DC   \n",
       "24                 93328 Davis Island           Rodriguezside              MD   \n",
       "25  75420 Michael Mountains Suite 485            New Victoria              HI   \n",
       "26          5938 Ramos Pike Suite 080           Lake Marytown              CT   \n",
       "27          809 Erika Valley Apt. 634            Petersenfort              NY   \n",
       "28    22455 Higgins Junction Apt. 042               New Keith              OR   \n",
       "29        3039 Daniel Mount Suite 582           New Tonyaberg              NM   \n",
       "30     64183 Kimberly Mills Suite 375         North Rachelton              VT   \n",
       "31            4706 Amy Roads Apt. 206          Stewartborough              AZ   \n",
       "\n",
       "    passenger_zip effective_start_date effective_end_date  \n",
       "0           89825           2006-06-06               None  \n",
       "1           96818           2006-06-06               None  \n",
       "2           76003           2006-06-06               None  \n",
       "3           60613           2006-06-06               None  \n",
       "4           51971           2006-06-06               None  \n",
       "5            6315           2006-06-06               None  \n",
       "6           40009           2006-06-06               None  \n",
       "7           47081           2006-06-06               None  \n",
       "8           87320           2006-06-06               None  \n",
       "9           56413           2006-06-06               None  \n",
       "10          66153           2006-06-06               None  \n",
       "11          22121           2006-06-06               None  \n",
       "12          65169           2006-06-06               None  \n",
       "13          20026           2006-06-06               None  \n",
       "14          16516           2006-06-06               None  \n",
       "15          37845           2006-06-06               None  \n",
       "16           1093           2006-06-06               None  \n",
       "17          19898           2006-06-06               None  \n",
       "18          76177           2006-06-06               None  \n",
       "19           8640           2006-06-06               None  \n",
       "20          27568           2006-06-06               None  \n",
       "21          73022           2006-06-06               None  \n",
       "22          20028           2006-06-06               None  \n",
       "23          20027           2006-06-06               None  \n",
       "24          21408           2006-06-06               None  \n",
       "25          96727           2006-06-06               None  \n",
       "26           6314           2006-06-06               None  \n",
       "27          13184           2006-06-06               None  \n",
       "28          97405           2006-06-06               None  \n",
       "29          88155           2006-06-06               None  \n",
       "30           5249           2006-06-06               None  \n",
       "31          85030           2006-06-06               None  "
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "import uuid\n",
    "\n",
    "pass_df = df\n",
    "\n",
    "pass_expected_cols = ['email','first_name','last_name', 'birth_date', 'street','city','state','zip']\n",
    "pass_col = ['passenger_' + col for col in pass_expected_cols]\n",
    "\n",
    "# group unique passengers\n",
    "pass_df = pass_df.groupby(pass_col).all()\n",
    "pass_df = pass_df.reset_index().loc[:, pass_col]\n",
    "\n",
    "# generate uuid for passenger id\n",
    "pass_df['passenger_sk'] = pass_df.index.to_series().map(lambda x: uuid.uuid4())\n",
    "first_col = pass_df.pop('passenger_sk')\n",
    "pass_df.insert(0, 'passenger_sk', first_col)\n",
    "pass_df = pass_df.astype({'passenger_sk': str, 'passenger_zip': int})\n",
    "\n",
    "# add start and end dates\n",
    "pass_df['effective_start_date'] = \"2006-06-06\"\n",
    "pass_df['effective_end_date'] = None\n",
    "\n",
    "display(pass_df)\n",
    "\n"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Define fact table schema"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "metadata": {},
   "outputs": [],
   "source": [
    "# setting table schema for fact table\n",
    "\n",
    "FACTS_TABLE_METADATA = {\n",
    "    'tickets': {\n",
    "        'table_name': 'tickets',\n",
    "        'schema': [\n",
    "            # indexes are written if only named in the schema\n",
    "            bigquery.SchemaField('eticket_num', 'string', mode='REQUIRED'),\n",
    "            bigquery.SchemaField('confirmation', 'string', mode='REQUIRED'),\n",
    "            bigquery.SchemaField('ticket_date', 'string', mode='NULLABLE'),\n",
    "            bigquery.SchemaField('price', 'float64', mode='NULLABLE'),\n",
    "            bigquery.SchemaField('seat', 'string', mode='NULLABLE'),\n",
    "            bigquery.SchemaField('status', 'string', mode='NULLABLE'),\n",
    "            bigquery.SchemaField('origin_iata', 'string', mode='NULLABLE'),\n",
    "            bigquery.SchemaField('destination_iata', 'string', mode='NULLABLE'),\n",
    "            bigquery.SchemaField('airline_iata', 'string', mode='NULLABLE'),\n",
    "            bigquery.SchemaField('passenger_sk', 'string', mode='NULLABLE'),\n",
    "        ],\n",
    "    },\n",
    "}"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "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>eticket_num</th>\n",
       "      <th>confirmation</th>\n",
       "      <th>ticket_date</th>\n",
       "      <th>price</th>\n",
       "      <th>seat</th>\n",
       "      <th>status</th>\n",
       "      <th>origin_iata</th>\n",
       "      <th>destination_iata</th>\n",
       "      <th>airline_iata</th>\n",
       "      <th>passenger_sk</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>000-238065-9359</td>\n",
       "      <td>KXDIKE</td>\n",
       "      <td>2022-03-22</td>\n",
       "      <td>752.01</td>\n",
       "      <td>10D</td>\n",
       "      <td>active</td>\n",
       "      <td>JJN</td>\n",
       "      <td>ZCO</td>\n",
       "      <td>NZ</td>\n",
       "      <td>df4da838-d123-43f6-8d76-81155e1fe80d</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>26</th>\n",
       "      <td>007-431058-7762</td>\n",
       "      <td>MJYSLN</td>\n",
       "      <td>2022-03-24</td>\n",
       "      <td>720.21</td>\n",
       "      <td>29E</td>\n",
       "      <td>active</td>\n",
       "      <td>OAK</td>\n",
       "      <td>SRE</td>\n",
       "      <td>MH</td>\n",
       "      <td>df4da838-d123-43f6-8d76-81155e1fe80d</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>41</th>\n",
       "      <td>011-433525-9702</td>\n",
       "      <td>5MF358</td>\n",
       "      <td>2022-03-23</td>\n",
       "      <td>988.02</td>\n",
       "      <td>20F</td>\n",
       "      <td>active</td>\n",
       "      <td>ORF</td>\n",
       "      <td>PRG</td>\n",
       "      <td>SJ</td>\n",
       "      <td>df4da838-d123-43f6-8d76-81155e1fe80d</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>49</th>\n",
       "      <td>013-115939-1473</td>\n",
       "      <td>90IN8Z</td>\n",
       "      <td>2022-03-23</td>\n",
       "      <td>364.47</td>\n",
       "      <td>30B</td>\n",
       "      <td>active</td>\n",
       "      <td>KWL</td>\n",
       "      <td>YNT</td>\n",
       "      <td>QR</td>\n",
       "      <td>df4da838-d123-43f6-8d76-81155e1fe80d</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>84</th>\n",
       "      <td>025-257697-5811</td>\n",
       "      <td>NT8E2A</td>\n",
       "      <td>2022-03-23</td>\n",
       "      <td>507.59</td>\n",
       "      <td>28F</td>\n",
       "      <td>active</td>\n",
       "      <td>REC</td>\n",
       "      <td>NTE</td>\n",
       "      <td>MW</td>\n",
       "      <td>df4da838-d123-43f6-8d76-81155e1fe80d</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",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3901</th>\n",
       "      <td>979-413386-4728</td>\n",
       "      <td>FU66JE</td>\n",
       "      <td>2022-03-22</td>\n",
       "      <td>419.36</td>\n",
       "      <td>4E</td>\n",
       "      <td>active</td>\n",
       "      <td>MSY</td>\n",
       "      <td>HEL</td>\n",
       "      <td>CZ</td>\n",
       "      <td>f2f9be8f-5d0b-4ded-bb20-42eba2515e12</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3920</th>\n",
       "      <td>983-119488-7703</td>\n",
       "      <td>98BV7H</td>\n",
       "      <td>2022-03-22</td>\n",
       "      <td>713.44</td>\n",
       "      <td>28C</td>\n",
       "      <td>active</td>\n",
       "      <td>POS</td>\n",
       "      <td>LGA</td>\n",
       "      <td>NK</td>\n",
       "      <td>f2f9be8f-5d0b-4ded-bb20-42eba2515e12</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3926</th>\n",
       "      <td>984-245886-5879</td>\n",
       "      <td>KIQDQM</td>\n",
       "      <td>2022-03-24</td>\n",
       "      <td>861.75</td>\n",
       "      <td>30E</td>\n",
       "      <td>active</td>\n",
       "      <td>TYN</td>\n",
       "      <td>CLO</td>\n",
       "      <td>DL</td>\n",
       "      <td>f2f9be8f-5d0b-4ded-bb20-42eba2515e12</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3934</th>\n",
       "      <td>986-705619-4205</td>\n",
       "      <td>EAH78F</td>\n",
       "      <td>2022-03-22</td>\n",
       "      <td>853.21</td>\n",
       "      <td>18D</td>\n",
       "      <td>active</td>\n",
       "      <td>SJU</td>\n",
       "      <td>SXM</td>\n",
       "      <td>DL</td>\n",
       "      <td>f2f9be8f-5d0b-4ded-bb20-42eba2515e12</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3972</th>\n",
       "      <td>998-693749-7580</td>\n",
       "      <td>ZH3MXV</td>\n",
       "      <td>2022-03-24</td>\n",
       "      <td>742.64</td>\n",
       "      <td>21G</td>\n",
       "      <td>active</td>\n",
       "      <td>FUK</td>\n",
       "      <td>UDI</td>\n",
       "      <td>S7</td>\n",
       "      <td>f2f9be8f-5d0b-4ded-bb20-42eba2515e12</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>3981 rows × 10 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "          eticket_num confirmation ticket_date   price seat  status  \\\n",
       "0     000-238065-9359       KXDIKE  2022-03-22  752.01  10D  active   \n",
       "26    007-431058-7762       MJYSLN  2022-03-24  720.21  29E  active   \n",
       "41    011-433525-9702       5MF358  2022-03-23  988.02  20F  active   \n",
       "49    013-115939-1473       90IN8Z  2022-03-23  364.47  30B  active   \n",
       "84    025-257697-5811       NT8E2A  2022-03-23  507.59  28F  active   \n",
       "...               ...          ...         ...     ...  ...     ...   \n",
       "3901  979-413386-4728       FU66JE  2022-03-22  419.36   4E  active   \n",
       "3920  983-119488-7703       98BV7H  2022-03-22  713.44  28C  active   \n",
       "3926  984-245886-5879       KIQDQM  2022-03-24  861.75  30E  active   \n",
       "3934  986-705619-4205       EAH78F  2022-03-22  853.21  18D  active   \n",
       "3972  998-693749-7580       ZH3MXV  2022-03-24  742.64  21G  active   \n",
       "\n",
       "     origin_iata destination_iata airline_iata  \\\n",
       "0            JJN              ZCO           NZ   \n",
       "26           OAK              SRE           MH   \n",
       "41           ORF              PRG           SJ   \n",
       "49           KWL              YNT           QR   \n",
       "84           REC              NTE           MW   \n",
       "...          ...              ...          ...   \n",
       "3901         MSY              HEL           CZ   \n",
       "3920         POS              LGA           NK   \n",
       "3926         TYN              CLO           DL   \n",
       "3934         SJU              SXM           DL   \n",
       "3972         FUK              UDI           S7   \n",
       "\n",
       "                              passenger_sk  \n",
       "0     df4da838-d123-43f6-8d76-81155e1fe80d  \n",
       "26    df4da838-d123-43f6-8d76-81155e1fe80d  \n",
       "41    df4da838-d123-43f6-8d76-81155e1fe80d  \n",
       "49    df4da838-d123-43f6-8d76-81155e1fe80d  \n",
       "84    df4da838-d123-43f6-8d76-81155e1fe80d  \n",
       "...                                    ...  \n",
       "3901  f2f9be8f-5d0b-4ded-bb20-42eba2515e12  \n",
       "3920  f2f9be8f-5d0b-4ded-bb20-42eba2515e12  \n",
       "3926  f2f9be8f-5d0b-4ded-bb20-42eba2515e12  \n",
       "3934  f2f9be8f-5d0b-4ded-bb20-42eba2515e12  \n",
       "3972  f2f9be8f-5d0b-4ded-bb20-42eba2515e12  \n",
       "\n",
       "[3981 rows x 10 columns]"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "# creating df for ticket fact table\n",
    "\n",
    "ticket_df = df\n",
    "\n",
    "ticket_col = ['eticket_num', 'confirmation', 'ticket_date', 'price', 'seat', 'status', 'origin_iata', 'destination_iata', 'airline_iata', 'passenger_email']\n",
    "\n",
    "# group unique tickets\n",
    "ticket_df = ticket_df.groupby(ticket_col).all()\n",
    "ticket_df = ticket_df.reset_index().loc[:, ticket_col]\n",
    "\n",
    "# join passenger df on email to get passenger id and dropping email column\n",
    "\n",
    "join_pass = pass_df.reset_index()\n",
    "join_pass = join_pass[['passenger_email', 'passenger_sk']]\n",
    "join_pass = join_pass.set_index('passenger_email')\n",
    "\n",
    "join_comp = ticket_df.join(join_pass, on='passenger_email', how='inner')\n",
    "ticket_df = join_comp.drop(columns='passenger_email')\n",
    "\n",
    "display(ticket_df)\n",
    "\n"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Load all data into BQ"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "metadata": {},
   "outputs": [],
   "source": [
    "# create load function\n",
    "\n",
    "def load_table(\n",
    "    df: pd.DataFrame, \n",
    "    client: bigquery.Client, \n",
    "    table_name: str, \n",
    "    schema: List[bigquery.SchemaField], \n",
    "    create_disposition: str = 'CREATE_IF_NEEDED', \n",
    "    write_disposition: str = 'WRITE_TRUNCATE'\n",
    "    ) -> None:\n",
    "    \"\"\"load dataframe into bigquery table\n",
    "\n",
    "    Args:\n",
    "        df (pd.DataFrame): dataframe to load\n",
    "        client (bigquery.Client): bigquery client\n",
    "        table_name (str): full table name including project and dataset id\n",
    "        schema (List[bigquery.SchemaField]): table schema with data types\n",
    "        create_disposition (str, optional): create table disposition. Defaults to 'CREATE_IF_NEEDED'.\n",
    "        write_disposition (str, optional): overwrite table disposition. Defaults to 'WRITE_TRUNCATE'.\n",
    "    \"\"\"\n",
    "    # *** run some checks ***\n",
    "    # test table name to be full table name including project and dataset name. It must contain to dots\n",
    "    assert len(table_name.split('.')) == 3, f\"Table name must be a full bigquery table name including project and dataset id: '{table_name}'\"\n",
    "    # setup bigquery load job:\n",
    "    #  create table if needed, replace rows, define the table schema\n",
    "    job_config = bigquery.LoadJobConfig(\n",
    "        create_disposition=create_disposition,\n",
    "        write_disposition=write_disposition,\n",
    "        schema=schema\n",
    "    )\n",
    "    logger.info(f\"loading table: '{table_name}'\")\n",
    "    job = client.load_table_from_dataframe(df, destination=table_name, job_config=job_config)\n",
    "    job.result()        # wait for the job to finish\n",
    "    # get the resulting table\n",
    "    table = client.get_table(table_name)\n",
    "    logger.info(f\"loaded {table.num_rows} rows into {table.full_table_id}\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 38,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "[INFO ][2023-01-08 15:18:41,679][2964262717:0031] : loading table: 'deb-dev-dw.air_travel.airlines'\n",
      "[INFO ][2023-01-08 15:18:45,031][2964262717:0036] : loaded 48 rows into deb-dev-dw:air_travel.airlines\n",
      "[INFO ][2023-01-08 15:18:45,033][1879359360:0009] : loaded airlines\n",
      "[INFO ][2023-01-08 15:18:45,035][2964262717:0031] : loading table: 'deb-dev-dw.air_travel.airports'\n",
      "[INFO ][2023-01-08 15:18:48,640][2964262717:0036] : loaded 386 rows into deb-dev-dw:air_travel.airports\n",
      "[INFO ][2023-01-08 15:18:48,643][1879359360:0017] : loaded airports\n",
      "[INFO ][2023-01-08 15:18:48,644][2964262717:0031] : loading table: 'deb-dev-dw.air_travel.passengers'\n",
      "[INFO ][2023-01-08 15:18:52,373][2964262717:0036] : loaded 32 rows into deb-dev-dw:air_travel.passengers\n",
      "[INFO ][2023-01-08 15:18:52,376][1879359360:0025] : loaded passengers\n",
      "[INFO ][2023-01-08 15:18:52,377][2964262717:0031] : loading table: 'deb-dev-dw.air_travel.tickets'\n",
      "[INFO ][2023-01-08 15:18:55,794][2964262717:0036] : loaded 3981 rows into deb-dev-dw:air_travel.tickets\n",
      "[INFO ][2023-01-08 15:18:55,797][1879359360:0032] : loaded tickets facts\n"
     ]
    }
   ],
   "source": [
    "# loading all tables to bq\n",
    "\n",
    "# load airlines\n",
    "table_name = f\"{PROJECT_NAME}.{DATASET_NAME}.{DIMS_TABLE_METADATA['airlines']['table_name']}\"\n",
    "schema = DIMS_TABLE_METADATA['airlines']['schema']\n",
    "# load dataframe to bq\n",
    "load_table(airlines_df, client, table_name, schema)\n",
    "\n",
    "logger.info(f\"loaded airlines\")\n",
    "\n",
    "# load airports\n",
    "table_name = f\"{PROJECT_NAME}.{DATASET_NAME}.{DIMS_TABLE_METADATA['airports']['table_name']}\"\n",
    "schema = DIMS_TABLE_METADATA['airports']['schema']\n",
    "# load dataframe to bq\n",
    "load_table(airports_df, client, table_name, schema)\n",
    "\n",
    "logger.info(f\"loaded airports\")\n",
    "\n",
    "# load passengers\n",
    "table_name = f\"{PROJECT_NAME}.{DATASET_NAME}.{DIMS_TABLE_METADATA['passengers']['table_name']}\"\n",
    "schema = DIMS_TABLE_METADATA['passengers']['schema']\n",
    "# load dataframe to bq\n",
    "load_table(pass_df, client, table_name, schema)\n",
    "\n",
    "logger.info(f\"loaded passengers\")\n",
    "\n",
    "table_name = f\"{PROJECT_NAME}.{DATASET_NAME}.{FACTS_TABLE_METADATA['tickets']['table_name']}\"\n",
    "schema = FACTS_TABLE_METADATA['tickets']['schema']\n",
    "# load dataframe to bq\n",
    "load_table(ticket_df, client, table_name, schema)\n",
    "\n",
    "logger.info(f\"loaded tickets facts\")"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "venv",
   "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.7.15"
  },
  "orig_nbformat": 4,
  "vscode": {
   "interpreter": {
    "hash": "d9241d1adb0635024b7a56c5a953bf2ca031fab3ab2a7350c16e992fc26dcb29"
   }
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}