NB01_ExploreDWH.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Explore Munich Traffic DWH\n",
    "\n",
    "## Connect"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import configparser\n",
    "%load_ext sql"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "['dwh.cfg']"
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "config = configparser.ConfigParser()\n",
    "config.read(\"dwh.cfg\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "connstring = config['Red']['DWH_CS']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'Connected: dwhuser@sbmd1'"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%sql connstring"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Explore Objetcs\n",
    "\n",
    "### Tables"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * postgresql://dwhuser:***@redshift-cluster-1sbmd.cg1lpo6kzhm9.eu-central-1.redshift.amazonaws.com:5439/sbmd1\n",
      "14 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>tablename</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>t03_dim_conn</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>t_gmap01_live</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>t_db01_live</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>t_w01_live</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>zz_gmap_cities</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>zz_w_cities</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>zz_db_cities</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>t01_delay_fact</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>t02_dim_time</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>t04_dim_part</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>t_w02_weather_codes</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>t_gmap01_stagings</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>t_w01_stagings</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>t_db01_stagings</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[('t03_dim_conn',),\n",
       " ('t_gmap01_live',),\n",
       " ('t_db01_live',),\n",
       " ('t_w01_live',),\n",
       " ('zz_gmap_cities',),\n",
       " ('zz_w_cities',),\n",
       " ('zz_db_cities',),\n",
       " ('t01_delay_fact',),\n",
       " ('t02_dim_time',),\n",
       " ('t04_dim_part',),\n",
       " ('t_w02_weather_codes',),\n",
       " ('t_gmap01_stagings',),\n",
       " ('t_w01_stagings',),\n",
       " ('t_db01_stagings',)]"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "SELECT\n",
    "tablename\n",
    "FROM\n",
    "   pg_catalog.pg_tables\n",
    "WHERE\n",
    "   schemaname != 'pg_catalog'\n",
    "AND schemaname != 'information_schema';"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Views"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * postgresql://dwhuser:***@redshift-cluster-1sbmd.cg1lpo6kzhm9.eu-central-1.redshift.amazonaws.com:5439/sbmd1\n",
      "5 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>schemaname</th>\n",
       "        <th>viewname</th>\n",
       "        <th>viewowner</th>\n",
       "        <th>definition</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>public</td>\n",
       "        <td>v01_gmap_fact</td>\n",
       "        <td>dwhuser</td>\n",
       "        <td>SELECT (((g.start_loc)::text || &#x27;_&#x27;::text) || (g.end_loc)::text) AS conn_detail, to_char(g.&quot;timestamp&quot;, &#x27;yyyy-mm-dd HH24&#x27;::text) AS dept_hour, 2 AS part, avg(g.duration_value) AS reg_duration_sec, avg(g.duration_traffic_value) AS real_duration_sec, (avg(g.duration_traffic_value) - avg(g.duration_value)) AS delay_sec FROM t_gmap01_live g GROUP BY (((g.start_loc)::text || &#x27;_&#x27;::text) || (g.end_loc)::text), to_char(g.&quot;timestamp&quot;, &#x27;yyyy-mm-dd HH24&#x27;::text);</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>public</td>\n",
       "        <td>check_w_records</td>\n",
       "        <td>dwhuser</td>\n",
       "        <td>SELECT to_char(t_w01_live.reference_time, &#x27;YYYY-MM-DD&#x27;::text) AS to_char, count(*) AS count FROM t_w01_live GROUP BY to_char(t_w01_live.reference_time, &#x27;YYYY-MM-DD&#x27;::text);</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>public</td>\n",
       "        <td>check_gmap_records</td>\n",
       "        <td>dwhuser</td>\n",
       "        <td>SELECT to_char(t_gmap01_live.&quot;timestamp&quot;, &#x27;YYYY-MM-DD&#x27;::text) AS to_char, count(*) AS count FROM t_gmap01_live GROUP BY to_char(t_gmap01_live.&quot;timestamp&quot;, &#x27;YYYY-MM-DD&#x27;::text) ORDER BY to_char(t_gmap01_live.&quot;timestamp&quot;, &#x27;YYYY-MM-DD&#x27;::text);</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>public</td>\n",
       "        <td>check_db_records</td>\n",
       "        <td>dwhuser</td>\n",
       "        <td>SELECT to_char(t_db01_live.&quot;timestamp&quot;, &#x27;YYYY-MM-DD&#x27;::text) AS to_char, count(*) AS count FROM t_db01_live GROUP BY to_char(t_db01_live.&quot;timestamp&quot;, &#x27;YYYY-MM-DD&#x27;::text) ORDER BY to_char(t_db01_live.&quot;timestamp&quot;, &#x27;YYYY-MM-DD&#x27;::text);</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>public</td>\n",
       "        <td>v02_weather_dim</td>\n",
       "        <td>dwhuser</td>\n",
       "        <td>SELECT w.loc_name, c.city_name AS city, to_char(w.reference_time, &#x27;yyyy-mm-dd HH24&#x27;::text) AS dept_hour, min(w.sunrise_time) AS sunrise_time, &quot;max&quot;(w.sunset_time) AS sunset_time, avg(w.clouds) AS clouds, avg(w.rain_1h) AS rain_1h, avg(w.rain_3h) AS rain_3h, avg(w.snow_1h) AS snow_1h, avg(w.snow_3h) AS snow_3h, avg(w.wind_deg) AS wind_deg, avg(w.wind_gust) AS wind_gust, avg(w.wind_speed) AS wind_speed, avg(w.humidity) AS humidity, avg(w.press) AS press, avg(w.sea_level) AS sea_level, round(avg(w.temperature), (2)::numeric) AS temperature, round(avg(w.temperature_kf), (2)::numeric) AS temperature_kf, round(min(w.temperature_min), (2)::numeric) AS temperature_min, round(&quot;max&quot;(w.temperature_max), (2)::numeric) AS temperature_max, avg(w.visibility_distance) AS visibility_distance, round((avg(w.weather_code))::numeric, 0) AS weather_code FROM (t_w01_live w LEFT JOIN zz_w_cities c ON (((c.start_loc)::text = (w.loc_name)::text))) GROUP BY w.loc_name, c.city_name, to_char(w.reference_time, &#x27;yyyy-mm-dd HH24&#x27;::text);</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[('public', 'v01_gmap_fact', 'dwhuser', 'SELECT (((g.start_loc)::text || \\'_\\'::text) || (g.end_loc)::text) AS conn_detail, to_char(g.\"timestamp\", \\'yyyy-mm-dd HH24\\'::text) AS dept_hour, 2  ... (165 characters truncated) ... lay_sec FROM t_gmap01_live g GROUP BY (((g.start_loc)::text || \\'_\\'::text) || (g.end_loc)::text), to_char(g.\"timestamp\", \\'yyyy-mm-dd HH24\\'::text);'),\n",
       " ('public', 'check_w_records', 'dwhuser', \"SELECT to_char(t_w01_live.reference_time, 'YYYY-MM-DD'::text) AS to_char, count(*) AS count FROM t_w01_live GROUP BY to_char(t_w01_live.reference_time, 'YYYY-MM-DD'::text);\"),\n",
       " ('public', 'check_gmap_records', 'dwhuser', 'SELECT to_char(t_gmap01_live.\"timestamp\", \\'YYYY-MM-DD\\'::text) AS to_char, count(*) AS count FROM t_gmap01_live GROUP BY to_char(t_gmap01_live.\"timestamp\", \\'YYYY-MM-DD\\'::text) ORDER BY to_char(t_gmap01_live.\"timestamp\", \\'YYYY-MM-DD\\'::text);'),\n",
       " ('public', 'check_db_records', 'dwhuser', 'SELECT to_char(t_db01_live.\"timestamp\", \\'YYYY-MM-DD\\'::text) AS to_char, count(*) AS count FROM t_db01_live GROUP BY to_char(t_db01_live.\"timestamp\", \\'YYYY-MM-DD\\'::text) ORDER BY to_char(t_db01_live.\"timestamp\", \\'YYYY-MM-DD\\'::text);'),\n",
       " ('public', 'v02_weather_dim', 'dwhuser', 'SELECT w.loc_name, c.city_name AS city, to_char(w.reference_time, \\'yyyy-mm-dd HH24\\'::text) AS dept_hour, min(w.sunrise_time) AS sunrise_time, \"max\" ... (727 characters truncated) ... z_w_cities c ON (((c.start_loc)::text = (w.loc_name)::text))) GROUP BY w.loc_name, c.city_name, to_char(w.reference_time, \\'yyyy-mm-dd HH24\\'::text);')]"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "SELECT\n",
    "   *\n",
    "FROM\n",
    "   pg_catalog.pg_views\n",
    "WHERE\n",
    "   schemaname != 'pg_catalog'\n",
    "AND schemaname != 'information_schema';"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Explore Raw Data\n",
    "This is about exploring the stage and loive tables for all three data sources\n",
    "\n",
    "### Train / DB Data\n",
    "Currently there are only 71k records for 6 departure dates in it and have a glimpse at the table."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * postgresql://dwhuser:***@redshift-cluster-1sbmd.cg1lpo6kzhm9.eu-central-1.redshift.amazonaws.com:5439/sbmd1\n",
      "1 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>count</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>637776</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[(637776,)]"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%sql SELECT COUNT(*) FROM t_db01_live;"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * postgresql://dwhuser:***@redshift-cluster-1sbmd.cg1lpo6kzhm9.eu-central-1.redshift.amazonaws.com:5439/sbmd1\n",
      "5 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>db_id</th>\n",
       "        <th>start_loc</th>\n",
       "        <th>end_loc</th>\n",
       "        <th>timestamp</th>\n",
       "        <th>departure</th>\n",
       "        <th>arrival</th>\n",
       "        <th>transfers</th>\n",
       "        <th>total_time</th>\n",
       "        <th>products</th>\n",
       "        <th>price</th>\n",
       "        <th>ontime</th>\n",
       "        <th>cancelled</th>\n",
       "        <th>total_delay</th>\n",
       "        <th>delay_departure</th>\n",
       "        <th>delay_arrival</th>\n",
       "        <th>dep_date</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>74602</td>\n",
       "        <td>Ingolstadt Hbf</td>\n",
       "        <td>Eching</td>\n",
       "        <td>2020-03-14 00:31:03.373876</td>\n",
       "        <td>2020-03-13 00:33:00</td>\n",
       "        <td>2020-03-13 02:12:00</td>\n",
       "        <td>1</td>\n",
       "        <td>-5940</td>\n",
       "        <td>[&#x27;ICE&#x27;, &#x27;S&#x27;]</td>\n",
       "        <td>31.6</td>\n",
       "        <td>True</td>\n",
       "        <td>False</td>\n",
       "        <td>0</td>\n",
       "        <td>0</td>\n",
       "        <td>0</td>\n",
       "        <td>2020-03-13</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>74666</td>\n",
       "        <td>Bad Aibling Kurpark</td>\n",
       "        <td>Steinebach an der Wied Ort</td>\n",
       "        <td>2020-03-13 19:28:03.325736</td>\n",
       "        <td>2020-03-13 07:14:00</td>\n",
       "        <td>2020-03-13 16:25:00</td>\n",
       "        <td>5</td>\n",
       "        <td>-33060</td>\n",
       "        <td>[&#x27;M&#x27;, &#x27;IC&#x27;, &#x27;ICE&#x27;, &#x27;RB&#x27;, &#x27;RB&#x27;, &#x27;BUS&#x27;]</td>\n",
       "        <td>95.9</td>\n",
       "        <td>True</td>\n",
       "        <td>False</td>\n",
       "        <td>0</td>\n",
       "        <td>0</td>\n",
       "        <td>0</td>\n",
       "        <td>2020-03-13</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>74730</td>\n",
       "        <td>München-Pasing</td>\n",
       "        <td>Ingolstadt Hbf</td>\n",
       "        <td>2020-03-13 14:55:05.605864</td>\n",
       "        <td>2020-03-13 14:05:00</td>\n",
       "        <td>2020-03-13 15:24:00</td>\n",
       "        <td>1</td>\n",
       "        <td>-4740</td>\n",
       "        <td>[&#x27;S&#x27;, &#x27;RB&#x27;]</td>\n",
       "        <td></td>\n",
       "        <td>False</td>\n",
       "        <td>False</td>\n",
       "        <td>3</td>\n",
       "        <td>0</td>\n",
       "        <td>3</td>\n",
       "        <td>2020-03-13</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>74794</td>\n",
       "        <td>München-Freiham</td>\n",
       "        <td>Hohenbrunn</td>\n",
       "        <td>2020-03-13 15:46:37.701021</td>\n",
       "        <td>2020-03-13 14:56:00</td>\n",
       "        <td>2020-03-13 15:55:00</td>\n",
       "        <td>1</td>\n",
       "        <td>-3540</td>\n",
       "        <td>[&#x27;S&#x27;]</td>\n",
       "        <td></td>\n",
       "        <td>False</td>\n",
       "        <td>False</td>\n",
       "        <td>1</td>\n",
       "        <td>0</td>\n",
       "        <td>1</td>\n",
       "        <td>2020-03-13</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>74858</td>\n",
       "        <td>Eichstätt Stadt</td>\n",
       "        <td>München-Feldmoching</td>\n",
       "        <td>2020-03-13 15:42:03.429814</td>\n",
       "        <td>2020-03-13 15:44:00</td>\n",
       "        <td>2020-03-13 17:57:00</td>\n",
       "        <td>2</td>\n",
       "        <td>-7980</td>\n",
       "        <td>[&#x27;BRB&#x27;, &#x27;RB&#x27;, &#x27;BUS&#x27;]</td>\n",
       "        <td>23.9</td>\n",
       "        <td>True</td>\n",
       "        <td>False</td>\n",
       "        <td>0</td>\n",
       "        <td>0</td>\n",
       "        <td>0</td>\n",
       "        <td>2020-03-13</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[(74602, 'Ingolstadt Hbf', 'Eching', datetime.datetime(2020, 3, 14, 0, 31, 3, 373876), datetime.datetime(2020, 3, 13, 0, 33), datetime.datetime(2020, 3, 13, 2, 12), 1, '-5940', \"['ICE', 'S']\", '31.6', True, False, 0, 0, 0, datetime.date(2020, 3, 13)),\n",
       " (74666, 'Bad Aibling Kurpark', 'Steinebach an der Wied Ort', datetime.datetime(2020, 3, 13, 19, 28, 3, 325736), datetime.datetime(2020, 3, 13, 7, 14), datetime.datetime(2020, 3, 13, 16, 25), 5, '-33060', \"['M', 'IC', 'ICE', 'RB', 'RB', 'BUS']\", '95.9', True, False, 0, 0, 0, datetime.date(2020, 3, 13)),\n",
       " (74730, 'München-Pasing', 'Ingolstadt Hbf', datetime.datetime(2020, 3, 13, 14, 55, 5, 605864), datetime.datetime(2020, 3, 13, 14, 5), datetime.datetime(2020, 3, 13, 15, 24), 1, '-4740', \"['S', 'RB']\", '', False, False, 3, 0, 3, datetime.date(2020, 3, 13)),\n",
       " (74794, 'München-Freiham', 'Hohenbrunn', datetime.datetime(2020, 3, 13, 15, 46, 37, 701021), datetime.datetime(2020, 3, 13, 14, 56), datetime.datetime(2020, 3, 13, 15, 55), 1, '-3540', \"['S']\", '', False, False, 1, 0, 1, datetime.date(2020, 3, 13)),\n",
       " (74858, 'Eichstätt Stadt', 'München-Feldmoching', datetime.datetime(2020, 3, 13, 15, 42, 3, 429814), datetime.datetime(2020, 3, 13, 15, 44), datetime.datetime(2020, 3, 13, 17, 57), 2, '-7980', \"['BRB', 'RB', 'BUS']\", '23.9', True, False, 0, 0, 0, datetime.date(2020, 3, 13))]"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%sql SELECT * from t_db01_live LIMIT 5;"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * postgresql://dwhuser:***@redshift-cluster-1sbmd.cg1lpo6kzhm9.eu-central-1.redshift.amazonaws.com:5439/sbmd1\n",
      "1 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>count</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>51</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[(51,)]"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%sql SELECT COUNT (DISTINCT dep_date) FROM t_db01_live;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Google maps data\n",
    "Currently there are 38k records for 38 departure dates in it and have a glimpse at the table."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * postgresql://dwhuser:***@redshift-cluster-1sbmd.cg1lpo6kzhm9.eu-central-1.redshift.amazonaws.com:5439/sbmd1\n",
      "1 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>count</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>49523</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[(49523,)]"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    " %sql SELECT COUNT(*) FROM t_gmap01_live;"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * postgresql://dwhuser:***@redshift-cluster-1sbmd.cg1lpo6kzhm9.eu-central-1.redshift.amazonaws.com:5439/sbmd1\n",
      "5 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>gmap_id</th>\n",
       "        <th>start_loc</th>\n",
       "        <th>end_loc</th>\n",
       "        <th>timestamp</th>\n",
       "        <th>start_loc_lat</th>\n",
       "        <th>start_loc_lng</th>\n",
       "        <th>start_adress</th>\n",
       "        <th>end_loc_lat</th>\n",
       "        <th>end_loc_lng</th>\n",
       "        <th>end_adress</th>\n",
       "        <th>distance_text</th>\n",
       "        <th>distance_value</th>\n",
       "        <th>duration_text</th>\n",
       "        <th>duration_value</th>\n",
       "        <th>duration_traffic_text</th>\n",
       "        <th>duration_traffic_value</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>73849</td>\n",
       "        <td>Nürnberg</td>\n",
       "        <td>Rosenheim</td>\n",
       "        <td>2020-02-10 10:51:23</td>\n",
       "        <td>49.4518817</td>\n",
       "        <td>11.0766489</td>\n",
       "        <td>Nuremberg, Germany</td>\n",
       "        <td>47.8568072</td>\n",
       "        <td>47.8568072</td>\n",
       "        <td>Rosenheim, Germany</td>\n",
       "        <td>239 km</td>\n",
       "        <td>238695.0</td>\n",
       "        <td>2 hours 33 mins</td>\n",
       "        <td>9159.0</td>\n",
       "        <td>2 hours 33 mins</td>\n",
       "        <td>9154.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>73913</td>\n",
       "        <td>München-Ost</td>\n",
       "        <td>Nürnberg</td>\n",
       "        <td>2020-02-10 20:51:05</td>\n",
       "        <td>48.1268711</td>\n",
       "        <td>11.606603</td>\n",
       "        <td>München Ost, 81667 München, Germany</td>\n",
       "        <td>49.4518817</td>\n",
       "        <td>49.4518817</td>\n",
       "        <td>Nuremberg, Germany</td>\n",
       "        <td>170 km</td>\n",
       "        <td>169940.0</td>\n",
       "        <td>1 hour 51 mins</td>\n",
       "        <td>6682.0</td>\n",
       "        <td>1 hour 45 mins</td>\n",
       "        <td>6286.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>73919</td>\n",
       "        <td>Rosenheim</td>\n",
       "        <td>München-Marienplatz</td>\n",
       "        <td>2020-02-10 12:51:29</td>\n",
       "        <td>47.8568072</td>\n",
       "        <td>12.1188533</td>\n",
       "        <td>Rosenheim, Germany</td>\n",
       "        <td>48.136656</td>\n",
       "        <td>48.136656</td>\n",
       "        <td>Marienplatz, 80331 München, Germany</td>\n",
       "        <td>66.5 km</td>\n",
       "        <td>66458.0</td>\n",
       "        <td>57 mins</td>\n",
       "        <td>3433.0</td>\n",
       "        <td>57 mins</td>\n",
       "        <td>3432.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>73925</td>\n",
       "        <td>München-Pasing</td>\n",
       "        <td>Fürstenfeldbruck</td>\n",
       "        <td>2020-02-10 17:51:10</td>\n",
       "        <td>48.1505857</td>\n",
       "        <td>11.4621758</td>\n",
       "        <td>München-Pasing, Pasinger Bahnhofspl. 9, 81241 München, Germany</td>\n",
       "        <td>48.1738243</td>\n",
       "        <td>48.1738243</td>\n",
       "        <td>82256 Fürstenfeldbruck, Germany</td>\n",
       "        <td>25.9 km</td>\n",
       "        <td>25899.0</td>\n",
       "        <td>26 mins</td>\n",
       "        <td>1567.0</td>\n",
       "        <td>26 mins</td>\n",
       "        <td>1583.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>73977</td>\n",
       "        <td>Nürnberg</td>\n",
       "        <td>Rosenheim</td>\n",
       "        <td>2020-02-11 09:51:25</td>\n",
       "        <td>49.4518817</td>\n",
       "        <td>11.0766489</td>\n",
       "        <td>Nuremberg, Germany</td>\n",
       "        <td>47.8568072</td>\n",
       "        <td>47.8568072</td>\n",
       "        <td>Rosenheim, Germany</td>\n",
       "        <td>239 km</td>\n",
       "        <td>238695.0</td>\n",
       "        <td>2 hours 33 mins</td>\n",
       "        <td>9159.0</td>\n",
       "        <td>2 hours 45 mins</td>\n",
       "        <td>9894.0</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[(73849, 'Nürnberg', 'Rosenheim', datetime.datetime(2020, 2, 10, 10, 51, 23), '49.4518817', '11.0766489', 'Nuremberg, Germany', '47.8568072', '47.8568072', 'Rosenheim, Germany', '239 km', 238695.0, '2 hours 33 mins', 9159.0, '2 hours 33 mins', 9154.0),\n",
       " (73913, 'München-Ost', 'Nürnberg', datetime.datetime(2020, 2, 10, 20, 51, 5), '48.1268711', '11.606603', 'München Ost, 81667 München, Germany', '49.4518817', '49.4518817', 'Nuremberg, Germany', '170 km', 169940.0, '1 hour 51 mins', 6682.0, '1 hour 45 mins', 6286.0),\n",
       " (73919, 'Rosenheim', 'München-Marienplatz', datetime.datetime(2020, 2, 10, 12, 51, 29), '47.8568072', '12.1188533', 'Rosenheim, Germany', '48.136656', '48.136656', 'Marienplatz, 80331 München, Germany', '66.5 km', 66458.0, '57 mins', 3433.0, '57 mins', 3432.0),\n",
       " (73925, 'München-Pasing', 'Fürstenfeldbruck', datetime.datetime(2020, 2, 10, 17, 51, 10), '48.1505857', '11.4621758', 'München-Pasing, Pasinger Bahnhofspl. 9, 81241 München, Germany', '48.1738243', '48.1738243', '82256 Fürstenfeldbruck, Germany', '25.9 km', 25899.0, '26 mins', 1567.0, '26 mins', 1583.0),\n",
       " (73977, 'Nürnberg', 'Rosenheim', datetime.datetime(2020, 2, 11, 9, 51, 25), '49.4518817', '11.0766489', 'Nuremberg, Germany', '47.8568072', '47.8568072', 'Rosenheim, Germany', '239 km', 238695.0, '2 hours 33 mins', 9159.0, '2 hours 45 mins', 9894.0)]"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    " %sql SELECT * from t_gmap01_live LIMIT 5;"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * postgresql://dwhuser:***@redshift-cluster-1sbmd.cg1lpo6kzhm9.eu-central-1.redshift.amazonaws.com:5439/sbmd1\n",
      "1 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>count</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>47</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[(47,)]"
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%sql SELECT COUNT (DISTINCT TO_CHAR(\"timestamp\", 'YYYY-MM-DD')) FROM t_gmap01_live;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Weather data\n",
    "Currently there are 85k records for 42 departure dates in it and have a glimpse at the table."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * postgresql://dwhuser:***@redshift-cluster-1sbmd.cg1lpo6kzhm9.eu-central-1.redshift.amazonaws.com:5439/sbmd1\n",
      "1 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>count</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>207764</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[(207764,)]"
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    " %sql SELECT COUNT(*) FROM t_w01_live;"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * postgresql://dwhuser:***@redshift-cluster-1sbmd.cg1lpo6kzhm9.eu-central-1.redshift.amazonaws.com:5439/sbmd1\n",
      "5 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>weather_id</th>\n",
       "        <th>loc_name</th>\n",
       "        <th>loc_id</th>\n",
       "        <th>clouds</th>\n",
       "        <th>detailed_status</th>\n",
       "        <th>dewpoint</th>\n",
       "        <th>heat_index</th>\n",
       "        <th>humidex</th>\n",
       "        <th>humidity</th>\n",
       "        <th>press</th>\n",
       "        <th>sea_level</th>\n",
       "        <th>rain_1h</th>\n",
       "        <th>rain_3h</th>\n",
       "        <th>reference_time</th>\n",
       "        <th>snow_1h</th>\n",
       "        <th>snow_3h</th>\n",
       "        <th>status</th>\n",
       "        <th>sunrise_time</th>\n",
       "        <th>sunset_time</th>\n",
       "        <th>temperature</th>\n",
       "        <th>temperature_kf</th>\n",
       "        <th>temperature_max</th>\n",
       "        <th>temperature_min</th>\n",
       "        <th>visibility_distance</th>\n",
       "        <th>weather_code</th>\n",
       "        <th>weather_iconname</th>\n",
       "        <th>wind_deg</th>\n",
       "        <th>wind_gust</th>\n",
       "        <th>wind_speed</th>\n",
       "        <th>reception_time</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>10</td>\n",
       "        <td>Altomünster</td>\n",
       "        <td>2956951</td>\n",
       "        <td>75</td>\n",
       "        <td>light rain</td>\n",
       "        <td></td>\n",
       "        <td></td>\n",
       "        <td></td>\n",
       "        <td>86</td>\n",
       "        <td>1003</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>2020-03-06 18:45:54</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>Rain</td>\n",
       "        <td>2020-03-06 05:46:08</td>\n",
       "        <td>2020-03-06 17:06:28</td>\n",
       "        <td>3.77000000000004</td>\n",
       "        <td>None</td>\n",
       "        <td>5.0</td>\n",
       "        <td>2.22000000000003</td>\n",
       "        <td>10000</td>\n",
       "        <td>500</td>\n",
       "        <td>10n</td>\n",
       "        <td>260.0</td>\n",
       "        <td>None</td>\n",
       "        <td>8.2</td>\n",
       "        <td>2020-03-06 18:45:55</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>17</td>\n",
       "        <td>Augsburg</td>\n",
       "        <td>2954172</td>\n",
       "        <td>100</td>\n",
       "        <td>light rain</td>\n",
       "        <td></td>\n",
       "        <td></td>\n",
       "        <td></td>\n",
       "        <td>49</td>\n",
       "        <td>1006</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>0.25</td>\n",
       "        <td>2020-02-13 15:52:50</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>Rain</td>\n",
       "        <td>2020-02-13 06:28:23</td>\n",
       "        <td>2020-02-13 16:33:13</td>\n",
       "        <td>7.74000000000001</td>\n",
       "        <td>None</td>\n",
       "        <td>9.44</td>\n",
       "        <td>5.56</td>\n",
       "        <td>10000</td>\n",
       "        <td>500</td>\n",
       "        <td>10d</td>\n",
       "        <td>230.0</td>\n",
       "        <td>None</td>\n",
       "        <td>4.1</td>\n",
       "        <td>2020-02-13 15:55:07</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>24</td>\n",
       "        <td>Altomünster</td>\n",
       "        <td>2956951</td>\n",
       "        <td>55</td>\n",
       "        <td>broken clouds</td>\n",
       "        <td></td>\n",
       "        <td></td>\n",
       "        <td></td>\n",
       "        <td>81</td>\n",
       "        <td>1018</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>2020-03-11 19:37:46</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>Clouds</td>\n",
       "        <td>2020-03-11 05:35:58</td>\n",
       "        <td>2020-03-11 17:14:01</td>\n",
       "        <td>11.14</td>\n",
       "        <td>None</td>\n",
       "        <td>12.78</td>\n",
       "        <td>9.44</td>\n",
       "        <td>10000</td>\n",
       "        <td>803</td>\n",
       "        <td>04n</td>\n",
       "        <td>240.0</td>\n",
       "        <td>None</td>\n",
       "        <td>3.1</td>\n",
       "        <td>2020-03-11 19:37:46</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>39</td>\n",
       "        <td>Altomünster</td>\n",
       "        <td>2956951</td>\n",
       "        <td>90</td>\n",
       "        <td>overcast clouds</td>\n",
       "        <td></td>\n",
       "        <td></td>\n",
       "        <td></td>\n",
       "        <td>93</td>\n",
       "        <td>1012</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>2020-03-09 07:37:35</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>Clouds</td>\n",
       "        <td>2020-03-09 05:40:03</td>\n",
       "        <td>2020-03-09 17:11:01</td>\n",
       "        <td>4.93000000000001</td>\n",
       "        <td>None</td>\n",
       "        <td>6.11000000000001</td>\n",
       "        <td>3.33000000000004</td>\n",
       "        <td>10000</td>\n",
       "        <td>804</td>\n",
       "        <td>04d</td>\n",
       "        <td>210.0</td>\n",
       "        <td>None</td>\n",
       "        <td>2.6</td>\n",
       "        <td>2020-03-09 07:37:36</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>46</td>\n",
       "        <td>Altomünster</td>\n",
       "        <td>2956951</td>\n",
       "        <td>75</td>\n",
       "        <td>broken clouds</td>\n",
       "        <td></td>\n",
       "        <td></td>\n",
       "        <td></td>\n",
       "        <td>80</td>\n",
       "        <td>1018</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>2020-03-09 19:37:36</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>Clouds</td>\n",
       "        <td>2020-03-09 05:40:03</td>\n",
       "        <td>2020-03-09 17:11:01</td>\n",
       "        <td>4.40000000000003</td>\n",
       "        <td>None</td>\n",
       "        <td>6.67000000000002</td>\n",
       "        <td>2.22000000000003</td>\n",
       "        <td>10000</td>\n",
       "        <td>803</td>\n",
       "        <td>04n</td>\n",
       "        <td>240.0</td>\n",
       "        <td>None</td>\n",
       "        <td>3.1</td>\n",
       "        <td>2020-03-09 19:37:37</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[(10, 'Altomünster', 2956951, 75, 'light rain', '', '', '', 86, 1003, None, None, None, datetime.datetime(2020, 3, 6, 18, 45, 54), None, None, 'Rain', datetime.datetime(2020, 3, 6, 5, 46, 8), datetime.datetime(2020, 3, 6, 17, 6, 28), 3.77000000000004, None, 5.0, 2.22000000000003, 10000, 500, '10n', 260.0, None, 8.2, datetime.datetime(2020, 3, 6, 18, 45, 55)),\n",
       " (17, 'Augsburg', 2954172, 100, 'light rain', '', '', '', 49, 1006, None, None, 0.25, datetime.datetime(2020, 2, 13, 15, 52, 50), None, None, 'Rain', datetime.datetime(2020, 2, 13, 6, 28, 23), datetime.datetime(2020, 2, 13, 16, 33, 13), 7.74000000000001, None, 9.44, 5.56, 10000, 500, '10d', 230.0, None, 4.1, datetime.datetime(2020, 2, 13, 15, 55, 7)),\n",
       " (24, 'Altomünster', 2956951, 55, 'broken clouds', '', '', '', 81, 1018, None, None, None, datetime.datetime(2020, 3, 11, 19, 37, 46), None, None, 'Clouds', datetime.datetime(2020, 3, 11, 5, 35, 58), datetime.datetime(2020, 3, 11, 17, 14, 1), 11.14, None, 12.78, 9.44, 10000, 803, '04n', 240.0, None, 3.1, datetime.datetime(2020, 3, 11, 19, 37, 46)),\n",
       " (39, 'Altomünster', 2956951, 90, 'overcast clouds', '', '', '', 93, 1012, None, None, None, datetime.datetime(2020, 3, 9, 7, 37, 35), None, None, 'Clouds', datetime.datetime(2020, 3, 9, 5, 40, 3), datetime.datetime(2020, 3, 9, 17, 11, 1), 4.93000000000001, None, 6.11000000000001, 3.33000000000004, 10000, 804, '04d', 210.0, None, 2.6, datetime.datetime(2020, 3, 9, 7, 37, 36)),\n",
       " (46, 'Altomünster', 2956951, 75, 'broken clouds', '', '', '', 80, 1018, None, None, None, datetime.datetime(2020, 3, 9, 19, 37, 36), None, None, 'Clouds', datetime.datetime(2020, 3, 9, 5, 40, 3), datetime.datetime(2020, 3, 9, 17, 11, 1), 4.40000000000003, None, 6.67000000000002, 2.22000000000003, 10000, 803, '04n', 240.0, None, 3.1, datetime.datetime(2020, 3, 9, 19, 37, 37))]"
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    " %sql SELECT * from t_w01_live LIMIT 5;"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * postgresql://dwhuser:***@redshift-cluster-1sbmd.cg1lpo6kzhm9.eu-central-1.redshift.amazonaws.com:5439/sbmd1\n",
      "1 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>count</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>51</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[(51,)]"
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%sql SELECT COUNT (DISTINCT TO_CHAR(reception_time, 'YYYY-MM-DD')) FROM t_w01_live;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Explore intermediate Objects (zz tables and views)\n",
    "\n",
    "### weather codes\n",
    "Currently there are 31 different weather codes (i.e. 9 states / several detailed states) in this table, but of course this can be extended in future loads."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * postgresql://dwhuser:***@redshift-cluster-1sbmd.cg1lpo6kzhm9.eu-central-1.redshift.amazonaws.com:5439/sbmd1\n",
      "10 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>weather_code</th>\n",
       "        <th>status</th>\n",
       "        <th>detailed_status</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>200</td>\n",
       "        <td>Thunderstorm</td>\n",
       "        <td>thunderstorm with light rain</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>211</td>\n",
       "        <td>Thunderstorm</td>\n",
       "        <td>thunderstorm</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>300</td>\n",
       "        <td>Drizzle</td>\n",
       "        <td>light intensity drizzle</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>301</td>\n",
       "        <td>Drizzle</td>\n",
       "        <td>drizzle</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>310</td>\n",
       "        <td>Drizzle</td>\n",
       "        <td>light intensity drizzle rain</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>311</td>\n",
       "        <td>Drizzle</td>\n",
       "        <td>drizzle rain</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>500</td>\n",
       "        <td>Rain</td>\n",
       "        <td>light rain</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>501</td>\n",
       "        <td>Rain</td>\n",
       "        <td>moderate rain</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>502</td>\n",
       "        <td>Rain</td>\n",
       "        <td>heavy intensity rain</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>503</td>\n",
       "        <td>Rain</td>\n",
       "        <td>very heavy rain</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[(200, 'Thunderstorm', 'thunderstorm with light rain'),\n",
       " (211, 'Thunderstorm', 'thunderstorm'),\n",
       " (300, 'Drizzle', 'light intensity drizzle'),\n",
       " (301, 'Drizzle', 'drizzle'),\n",
       " (310, 'Drizzle', 'light intensity drizzle rain'),\n",
       " (311, 'Drizzle', 'drizzle rain'),\n",
       " (500, 'Rain', 'light rain'),\n",
       " (501, 'Rain', 'moderate rain'),\n",
       " (502, 'Rain', 'heavy intensity rain'),\n",
       " (503, 'Rain', 'very heavy rain')]"
      ]
     },
     "execution_count": 19,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%sql SELECT * FROM t_w02_weather_codes LIMIT 10;"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * postgresql://dwhuser:***@redshift-cluster-1sbmd.cg1lpo6kzhm9.eu-central-1.redshift.amazonaws.com:5439/sbmd1\n",
      "9 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>status</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Clear</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Clouds</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Drizzle</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Fog</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Mist</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Rain</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Snow</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Squall</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Thunderstorm</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[('Clear',),\n",
       " ('Clouds',),\n",
       " ('Drizzle',),\n",
       " ('Fog',),\n",
       " ('Mist',),\n",
       " ('Rain',),\n",
       " ('Snow',),\n",
       " ('Squall',),\n",
       " ('Thunderstorm',)]"
      ]
     },
     "execution_count": 20,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%sql SELECT DISTINCT status FROM t_w02_weather_codes ORDER BY 1;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### ZZ Cities table\n",
    "Basically these tables are structured in the same manner for all three data sources. Their purpose is to match the locations name to standardized city names, so that these 3 data sources can be connected and compared with this attribute.\n",
    "For demo purposes, I only show some data of the train cities."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * postgresql://dwhuser:***@redshift-cluster-1sbmd.cg1lpo6kzhm9.eu-central-1.redshift.amazonaws.com:5439/sbmd1\n",
      "5 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>start_loc</th>\n",
       "        <th>city_name</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Augsburg-Oberhausen</td>\n",
       "        <td>Augsburg</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Eching</td>\n",
       "        <td>Eching</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Eurasburg b Wolfratshausen</td>\n",
       "        <td>Wolfratshausen</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Grafing Bahnhof</td>\n",
       "        <td>Grafing</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Grafing b. München Stadt</td>\n",
       "        <td>Grafing</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[('Augsburg-Oberhausen', 'Augsburg'),\n",
       " ('Eching', 'Eching'),\n",
       " ('Eurasburg b Wolfratshausen', 'Wolfratshausen'),\n",
       " ('Grafing Bahnhof', 'Grafing'),\n",
       " ('Grafing b. München Stadt', 'Grafing')]"
      ]
     },
     "execution_count": 21,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%sql SELECT * FROM zz_db_cities LIMIT 5;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### v01_gmap_fact\n",
    "This view prepares the gmap data for inserts into the fact table of the data model. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * postgresql://dwhuser:***@redshift-cluster-1sbmd.cg1lpo6kzhm9.eu-central-1.redshift.amazonaws.com:5439/sbmd1\n",
      "5 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>conn_detail</th>\n",
       "        <th>dept_hour</th>\n",
       "        <th>part</th>\n",
       "        <th>reg_duration_sec</th>\n",
       "        <th>real_duration_sec</th>\n",
       "        <th>delay_sec</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Landshut_Augsburg-Rathausplatz</td>\n",
       "        <td>2020-02-10 15</td>\n",
       "        <td>2</td>\n",
       "        <td>5028.0</td>\n",
       "        <td>5019.5</td>\n",
       "        <td>-8.5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>München-Pasing_Rosenheim</td>\n",
       "        <td>2020-02-11 05</td>\n",
       "        <td>2</td>\n",
       "        <td>3884.0</td>\n",
       "        <td>3895.0</td>\n",
       "        <td>11.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Nürnberg_Rosenheim</td>\n",
       "        <td>2020-02-10 17</td>\n",
       "        <td>2</td>\n",
       "        <td>9159.0</td>\n",
       "        <td>8619.0</td>\n",
       "        <td>-540.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Landshut_Augsburg-Rathausplatz</td>\n",
       "        <td>2020-02-11 18</td>\n",
       "        <td>2</td>\n",
       "        <td>5156.0</td>\n",
       "        <td>4992.0</td>\n",
       "        <td>-164.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Fürstenfeldbruck_Landshut</td>\n",
       "        <td>2020-02-11 15</td>\n",
       "        <td>2</td>\n",
       "        <td>3897.0</td>\n",
       "        <td>3966.0</td>\n",
       "        <td>69.0</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[('Landshut_Augsburg-Rathausplatz', '2020-02-10 15', 2, 5028.0, 5019.5, -8.5),\n",
       " ('München-Pasing_Rosenheim', '2020-02-11 05', 2, 3884.0, 3895.0, 11.0),\n",
       " ('Nürnberg_Rosenheim', '2020-02-10 17', 2, 9159.0, 8619.0, -540.0),\n",
       " ('Landshut_Augsburg-Rathausplatz', '2020-02-11 18', 2, 5156.0, 4992.0, -164.0),\n",
       " ('Fürstenfeldbruck_Landshut', '2020-02-11 15', 2, 3897.0, 3966.0, 69.0)]"
      ]
     },
     "execution_count": 22,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%sql SELECT * FROM v01_gmap_fact LIMIT 5;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### v02_weather_dim\n",
    "This view prepares the weather data for inserts into the weather dimension table of the data model. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * postgresql://dwhuser:***@redshift-cluster-1sbmd.cg1lpo6kzhm9.eu-central-1.redshift.amazonaws.com:5439/sbmd1\n",
      "5 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>loc_name</th>\n",
       "        <th>city</th>\n",
       "        <th>dept_hour</th>\n",
       "        <th>sunrise_time</th>\n",
       "        <th>sunset_time</th>\n",
       "        <th>clouds</th>\n",
       "        <th>rain_1h</th>\n",
       "        <th>rain_3h</th>\n",
       "        <th>snow_1h</th>\n",
       "        <th>snow_3h</th>\n",
       "        <th>wind_deg</th>\n",
       "        <th>wind_gust</th>\n",
       "        <th>wind_speed</th>\n",
       "        <th>humidity</th>\n",
       "        <th>press</th>\n",
       "        <th>sea_level</th>\n",
       "        <th>temperature</th>\n",
       "        <th>temperature_kf</th>\n",
       "        <th>temperature_min</th>\n",
       "        <th>temperature_max</th>\n",
       "        <th>visibility_distance</th>\n",
       "        <th>weather_code</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Bad Aibling</td>\n",
       "        <td>Bad Aibling</td>\n",
       "        <td>2020-03-01 06</td>\n",
       "        <td>2020-03-01 05:52:29</td>\n",
       "        <td>2020-03-01 16:56:20</td>\n",
       "        <td>20</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>210.0</td>\n",
       "        <td>None</td>\n",
       "        <td>5.7</td>\n",
       "        <td>80</td>\n",
       "        <td>1004</td>\n",
       "        <td>None</td>\n",
       "        <td>4.54</td>\n",
       "        <td>None</td>\n",
       "        <td>2.78</td>\n",
       "        <td>6.11</td>\n",
       "        <td>10000</td>\n",
       "        <td>801</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Bayrischzell</td>\n",
       "        <td>Bayrischzell</td>\n",
       "        <td>2020-03-12 05</td>\n",
       "        <td>2020-03-12 05:30:37</td>\n",
       "        <td>2020-03-12 17:12:48</td>\n",
       "        <td>7</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>1.0</td>\n",
       "        <td>84</td>\n",
       "        <td>1018</td>\n",
       "        <td>None</td>\n",
       "        <td>1.91</td>\n",
       "        <td>None</td>\n",
       "        <td>-2.22</td>\n",
       "        <td>6.67</td>\n",
       "        <td>10000</td>\n",
       "        <td>800</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Bayrischzell</td>\n",
       "        <td>Bayrischzell</td>\n",
       "        <td>2020-03-08 13</td>\n",
       "        <td>2020-03-08 05:38:38</td>\n",
       "        <td>2020-03-08 17:06:57</td>\n",
       "        <td>20</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>1.5</td>\n",
       "        <td>39</td>\n",
       "        <td>1018</td>\n",
       "        <td>None</td>\n",
       "        <td>9.73</td>\n",
       "        <td>None</td>\n",
       "        <td>7.22</td>\n",
       "        <td>11.67</td>\n",
       "        <td>10000</td>\n",
       "        <td>801</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Baldham</td>\n",
       "        <td>Baldham</td>\n",
       "        <td>2020-03-08 19</td>\n",
       "        <td>2020-03-08 05:39:45</td>\n",
       "        <td>2020-03-08 17:07:31</td>\n",
       "        <td>82</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>340.0</td>\n",
       "        <td>None</td>\n",
       "        <td>1.5</td>\n",
       "        <td>56</td>\n",
       "        <td>1015</td>\n",
       "        <td>None</td>\n",
       "        <td>4.32</td>\n",
       "        <td>None</td>\n",
       "        <td>1.67</td>\n",
       "        <td>6.67</td>\n",
       "        <td>10000</td>\n",
       "        <td>803</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Buchenau</td>\n",
       "        <td>Buchenau</td>\n",
       "        <td>2020-03-10 16</td>\n",
       "        <td>2020-03-10 05:45:17</td>\n",
       "        <td>2020-03-10 17:17:10</td>\n",
       "        <td>75</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>200.0</td>\n",
       "        <td>None</td>\n",
       "        <td>4.1</td>\n",
       "        <td>93</td>\n",
       "        <td>1005</td>\n",
       "        <td>None</td>\n",
       "        <td>8.1</td>\n",
       "        <td>None</td>\n",
       "        <td>7.22</td>\n",
       "        <td>9.44</td>\n",
       "        <td>4500</td>\n",
       "        <td>300</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[('Bad Aibling', 'Bad Aibling', '2020-03-01 06', datetime.datetime(2020, 3, 1, 5, 52, 29), datetime.datetime(2020, 3, 1, 16, 56, 20), 20, None, None, None, None, 210.0, None, 5.7, 80, 1004, None, 4.54, None, 2.78, 6.11, 10000, Decimal('801')),\n",
       " ('Bayrischzell', 'Bayrischzell', '2020-03-12 05', datetime.datetime(2020, 3, 12, 5, 30, 37), datetime.datetime(2020, 3, 12, 17, 12, 48), 7, None, None, None, None, None, None, 1.0, 84, 1018, None, 1.91, None, -2.22, 6.67, 10000, Decimal('800')),\n",
       " ('Bayrischzell', 'Bayrischzell', '2020-03-08 13', datetime.datetime(2020, 3, 8, 5, 38, 38), datetime.datetime(2020, 3, 8, 17, 6, 57), 20, None, None, None, None, None, None, 1.5, 39, 1018, None, 9.73, None, 7.22, 11.67, 10000, Decimal('801')),\n",
       " ('Baldham', 'Baldham', '2020-03-08 19', datetime.datetime(2020, 3, 8, 5, 39, 45), datetime.datetime(2020, 3, 8, 17, 7, 31), 82, None, None, None, None, 340.0, None, 1.5, 56, 1015, None, 4.32, None, 1.67, 6.67, 10000, Decimal('803')),\n",
       " ('Buchenau', 'Buchenau', '2020-03-10 16', datetime.datetime(2020, 3, 10, 5, 45, 17), datetime.datetime(2020, 3, 10, 17, 17, 10), 75, None, None, None, None, 200.0, None, 4.1, 93, 1005, None, 8.1, None, 7.22, 9.44, 4500, Decimal('300'))]"
      ]
     },
     "execution_count": 23,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%sql SELECT * FROM v02_weather_dim LIMIT 5;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Explore Datas Model Objects\n",
    "\n",
    "### Fact Table"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * postgresql://dwhuser:***@redshift-cluster-1sbmd.cg1lpo6kzhm9.eu-central-1.redshift.amazonaws.com:5439/sbmd1\n",
      "0 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>f_id</th>\n",
       "        <th>conn_id</th>\n",
       "        <th>time_id</th>\n",
       "        <th>part_id</th>\n",
       "        <th>w_id</th>\n",
       "        <th>duration_sec</th>\n",
       "        <th>delay_sec</th>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[]"
      ]
     },
     "execution_count": 24,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%sql SELECT * FROM t01_delay_fact LIMIT 5;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Dimension tables"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * postgresql://dwhuser:***@redshift-cluster-1sbmd.cg1lpo6kzhm9.eu-central-1.redshift.amazonaws.com:5439/sbmd1\n",
      "0 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>time_id</th>\n",
       "        <th>time_key</th>\n",
       "        <th>year</th>\n",
       "        <th>month</th>\n",
       "        <th>week</th>\n",
       "        <th>day</th>\n",
       "        <th>weekday</th>\n",
       "        <th>is_weekend</th>\n",
       "        <th>hour</th>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[]"
      ]
     },
     "execution_count": 37,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%sql SELECT * FROM t02_dim_time LIMIT 5;"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 38,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * postgresql://dwhuser:***@redshift-cluster-1sbmd.cg1lpo6kzhm9.eu-central-1.redshift.amazonaws.com:5439/sbmd1\n",
      "0 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>conn_id</th>\n",
       "        <th>conn_detail_name</th>\n",
       "        <th>stat1</th>\n",
       "        <th>stat2</th>\n",
       "        <th>conn_city_name</th>\n",
       "        <th>stat1_city</th>\n",
       "        <th>stat2_city</th>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[]"
      ]
     },
     "execution_count": 38,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%sql SELECT * FROM t03_dim_conn LIMIT 5"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 39,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * postgresql://dwhuser:***@redshift-cluster-1sbmd.cg1lpo6kzhm9.eu-central-1.redshift.amazonaws.com:5439/sbmd1\n",
      "2 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>part_id</th>\n",
       "        <th>part_name</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>2</td>\n",
       "        <td>gmap</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>1</td>\n",
       "        <td>train</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[(2, 'gmap'), (1, 'train')]"
      ]
     },
     "execution_count": 39,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%sql SELECT * FROM t04_dim_part;"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 40,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * postgresql://dwhuser:***@redshift-cluster-1sbmd.cg1lpo6kzhm9.eu-central-1.redshift.amazonaws.com:5439/sbmd1\n",
      "(psycopg2.errors.UndefinedTable) relation \"t05_dim_weather\" does not exist\n",
      "\n",
      "[SQL: SELECT * FROM t05_dim_weather LIMIT 5;]\n",
      "(Background on this error at: http://sqlalche.me/e/f405)\n"
     ]
    }
   ],
   "source": [
    "%sql SELECT * FROM t05_dim_weather LIMIT 5;"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.7.4"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}