L1 E1 - Step 5.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# STEP 5: ETL the data from 3NF tables to Facts & Dimension Tables\n",
    "**IMPORTANT:** The following exercise depends on first having successing completed Exercise 1: Step 4. \n",
    "\n",
    "Start by running the code in the cell below to connect to the database. If you are coming back to this exercise, then uncomment and run the first cell to recreate the database. If you recently completed steps 1 through 4, then skip to the second cell."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# !PGPASSWORD=student createdb -h 127.0.0.1 -U student pagila\n",
    "# !PGPASSWORD=student psql -q -h 127.0.0.1 -U student -d pagila -f Data/pagila-schema.sql\n",
    "# !PGPASSWORD=student psql -q -h 127.0.0.1 -U student -d pagila -f Data/pagila-data.sql"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "The sql extension is already loaded. To reload it, use:\n",
      "  %reload_ext sql\n",
      "postgresql://student:student@127.0.0.1:5432/pagila\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "'Connected: student@pagila'"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%load_ext sql\n",
    "\n",
    "DB_ENDPOINT = \"127.0.0.1\"\n",
    "DB = 'pagila'\n",
    "DB_USER = 'student'\n",
    "DB_PASSWORD = 'student'\n",
    "DB_PORT = '5432'\n",
    "\n",
    "# postgresql://username:password@host:port/database\n",
    "conn_string = \"postgresql://{}:{}@{}:{}/{}\" \\\n",
    "                        .format(DB_USER, DB_PASSWORD, DB_ENDPOINT, DB_PORT, DB)\n",
    "\n",
    "print(conn_string)\n",
    "%sql $conn_string"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Introducing SQL to SQL ETL\n",
    "When writing SQL to SQL ETL, you first create a table then use the INSERT and SELECT statements together to populate the table. Here's a simple example."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "First, you create a table called test_table."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * postgresql://student:***@127.0.0.1:5432/pagila\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "[]"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "CREATE TABLE test_table\n",
    "(\n",
    "  date timestamp,\n",
    "  revenue  decimal(5,2)\n",
    ");"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Then you use the INSERT and SELECT statements to populate the table. In this case, the SELECT statement extracts data from the `payment` table and INSERTs it INTO the `test_table`."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * postgresql://student:***@127.0.0.1:5432/pagila\n",
      "16049 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "[]"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "INSERT INTO test_table (date, revenue)\n",
    "SELECT payment_date AS date,\n",
    "       amount AS revenue\n",
    "FROM payment;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Then you can use a SELECT statement to take a look at your new table."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * postgresql://student:***@127.0.0.1:5432/pagila\n",
      "5 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>date</th>\n",
       "        <th>revenue</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>2017-01-24 21:40:19.996577</td>\n",
       "        <td>1.99</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>2017-01-25 15:16:50.996577</td>\n",
       "        <td>0.99</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>2017-01-28 21:44:14.996577</td>\n",
       "        <td>6.99</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>2017-01-29 00:58:02.996577</td>\n",
       "        <td>0.99</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>2017-01-29 08:10:06.996577</td>\n",
       "        <td>4.99</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[(datetime.datetime(2017, 1, 24, 21, 40, 19, 996577), Decimal('1.99')),\n",
       " (datetime.datetime(2017, 1, 25, 15, 16, 50, 996577), Decimal('0.99')),\n",
       " (datetime.datetime(2017, 1, 28, 21, 44, 14, 996577), Decimal('6.99')),\n",
       " (datetime.datetime(2017, 1, 29, 0, 58, 2, 996577), Decimal('0.99')),\n",
       " (datetime.datetime(2017, 1, 29, 8, 10, 6, 996577), Decimal('4.99'))]"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%sql SELECT * FROM test_table LIMIT 5;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "If you need to delete the table and start over, use the DROP TABLE command, like below."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * postgresql://student:***@127.0.0.1:5432/pagila\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "[]"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%sql DROP TABLE test_table"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Great! Now you'll do the same thing below to create the dimension and fact tables for the Star Schema using the data in the 3NF database.\n",
    "\n",
    "## ETL from 3NF to Star Schema"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 3NF - Entity Relationship Diagram\n",
    "\n",
    "<img src=\"./pagila-3nf.png\" width=\"50%\"/>\n",
    "\n",
    "### Star Schema - Entity Relationship Diagram\n",
    "\n",
    "<img src=\"pagila-star.png\" width=\"50%\"/>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "In this section, you'll populate the tables in the Star schema. You'll `extract` data from the normalized database, `transform` it, and `load` it into the new tables. \n",
    "\n",
    "To serve as an example, below is the query that populates the `dimDate` table with data from the `payment` table.\n",
    "* NOTE 1: The EXTRACT function extracts date parts from the payment_date variable.\n",
    "* NOTE 2: If you get an error that says that the `dimDate` table doesn't exist, then go back to Exercise 1: Step 4 and recreate the tables."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * postgresql://student:***@127.0.0.1:5432/pagila\n",
      "40 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "[]"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "INSERT INTO dimDate (date_key, date, year, quarter, month, day, week, is_weekend)\n",
    "SELECT DISTINCT(TO_CHAR(payment_date :: DATE, 'yyyyMMDD')::integer) AS date_key,\n",
    "       date(payment_date)                                           AS date,\n",
    "       EXTRACT(year FROM payment_date)                              AS year,\n",
    "       EXTRACT(quarter FROM payment_date)                           AS quarter,\n",
    "       EXTRACT(month FROM payment_date)                             AS month,\n",
    "       EXTRACT(day FROM payment_date)                               AS day,\n",
    "       EXTRACT(week FROM payment_date)                              AS week,\n",
    "       CASE WHEN EXTRACT(ISODOW FROM payment_date) IN (6, 7) THEN true ELSE false END AS is_weekend\n",
    "FROM payment;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "TODO: Now it's your turn. Populate the `dimCustomer` table with data from the `customer`, `address`, `city`, and `country` tables. Use the starter code as a guide."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * postgresql://student:***@127.0.0.1:5432/pagila\n",
      "599 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "[]"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "INSERT INTO dimCustomer (customer_key, customer_id, first_name, last_name, email, address, \n",
    "                         address2, district, city, country, postal_code, phone, active, \n",
    "                         create_date, start_date, end_date)\n",
    "SELECT  c.customer_id   AS customer_key,\n",
    "        c.customer_id,\n",
    "        c.first_name,\n",
    "        c.last_name,\n",
    "        c.email,\n",
    "        a.address,\n",
    "        a.address2,\n",
    "        a.district,\n",
    "        ci.city,\n",
    "        co.country,\n",
    "        a.postal_code,\n",
    "        a.phone,\n",
    "        c.active,\n",
    "        c.create_date,\n",
    "        now()         AS start_date,\n",
    "        now()         AS end_date\n",
    "FROM customer c\n",
    "JOIN address a  ON (c.address_id = a.address_id)\n",
    "JOIN city ci    ON (a.city_id = ci.city_id)\n",
    "JOIN country co ON (ci.country_id = co.country_id);"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "TODO: Populate the `dimMovie` table with data from the `film` and `language` tables. Use the starter code as a guide."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * postgresql://student:***@127.0.0.1:5432/pagila\n",
      "1000 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "[]"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "INSERT INTO dimMovie (movie_key, film_id, title, description, release_year, \n",
    "                      language, original_language, rental_duration, length, \n",
    "                      rating, special_features)\n",
    "SELECT      f.film_id          AS movie_key,\n",
    "            f.film_id, \n",
    "            f.title,\n",
    "            f.description,\n",
    "            f.release_year,\n",
    "            l.name             AS language, \n",
    "            orig_lang.name     AS original_language, \n",
    "            f.rental_duration,\n",
    "            f.length,\n",
    "            f.rating,\n",
    "            f.special_features\n",
    "FROM film f\n",
    "JOIN language l              ON (f.language_id=l.language_id)\n",
    "LEFT JOIN language orig_lang ON (f.original_language_id = orig_lang.language_id);"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "TODO: Populate the `dimStore` table with data from the `store`, `staff`, `address`, `city`, and `country` tables. This time, there's no guide. You should write the query from scratch. Use the previous queries as a reference."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * postgresql://student:***@127.0.0.1:5432/pagila\n",
      "2 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "[]"
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "INSERT INTO dimStore (store_key, store_id, address, address2, district,\n",
    "                      city, country, postal_code, manager_first_name, manager_last_name, \n",
    "                      start_date, end_date)\n",
    "SELECT          s.store_id      AS store_key,\n",
    "                s.store_id,\n",
    "                a.address,\n",
    "                a.address2,\n",
    "                a.district,\n",
    "                ci.city,\n",
    "                co.country,\n",
    "                a.postal_code,\n",
    "                sf.first_name   AS manager_first_name,\n",
    "                sf.last_name    AS manager_last_name,\n",
    "                NOW()           AS start_date,\n",
    "                NOW()           AS end_date\n",
    "FROM store s\n",
    "JOIN staff sf   ON (s.manager_staff_id = sf.staff_id)\n",
    "JOIN address a  ON (s.address_id = a.address_id)\n",
    "JOIN city ci    ON (a.city_id = ci.city_id)\n",
    "JOIN country co ON (ci.country_id = co.country_id);"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "TODO: Populate the `factSales` table with data from the `payment`, `rental`, and `inventory` tables. This time, there's no guide. You should write the query from scratch. Use the previous queries as a reference."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * postgresql://student:***@127.0.0.1:5432/pagila\n",
      "16049 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "[]"
      ]
     },
     "execution_count": 22,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "INSERT INTO factSales (date_key, customer_key, \n",
    "                       movie_key, store_key, sales_amount)\n",
    "\n",
    "SELECT      TO_CHAR(p.payment_date :: DATE, 'yyyyMMDD')::integer   AS date_key,\n",
    "            p.customer_id                                          AS customer_key,\n",
    "            i.film_id                                              AS movie_key,\n",
    "            i.store_id                                             AS store_id,\n",
    "            p.amount                                               AS sales_amount\n",
    "\n",
    "FROM payment p\n",
    "JOIN rental r     ON ( p.rental_id = r.rental_id )\n",
    "JOIN inventory i  ON ( r.inventory_id = i.inventory_id );"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * postgresql://student:***@127.0.0.1:5432/pagila\n",
      "5 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>sales_key</th>\n",
       "        <th>date_key</th>\n",
       "        <th>customer_key</th>\n",
       "        <th>movie_key</th>\n",
       "        <th>store_key</th>\n",
       "        <th>sales_amount</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>1</td>\n",
       "        <td>20170124</td>\n",
       "        <td>269</td>\n",
       "        <td>870</td>\n",
       "        <td>2</td>\n",
       "        <td>1.99</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>2</td>\n",
       "        <td>20170125</td>\n",
       "        <td>269</td>\n",
       "        <td>651</td>\n",
       "        <td>1</td>\n",
       "        <td>0.99</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>3</td>\n",
       "        <td>20170128</td>\n",
       "        <td>269</td>\n",
       "        <td>818</td>\n",
       "        <td>1</td>\n",
       "        <td>6.99</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>4</td>\n",
       "        <td>20170129</td>\n",
       "        <td>269</td>\n",
       "        <td>249</td>\n",
       "        <td>2</td>\n",
       "        <td>0.99</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>5</td>\n",
       "        <td>20170129</td>\n",
       "        <td>269</td>\n",
       "        <td>159</td>\n",
       "        <td>2</td>\n",
       "        <td>4.99</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[(1, 20170124, 269, 870, 2, Decimal('1.99')),\n",
       " (2, 20170125, 269, 651, 1, Decimal('0.99')),\n",
       " (3, 20170128, 269, 818, 1, Decimal('6.99')),\n",
       " (4, 20170129, 269, 249, 2, Decimal('0.99')),\n",
       " (5, 20170129, 269, 159, 2, Decimal('4.99'))]"
      ]
     },
     "execution_count": 23,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql \n",
    "SELECT * FROM factSales\n",
    "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.6.3"
  },
  "toc": {
   "base_numbering": 1,
   "nav_menu": {},
   "number_sections": true,
   "sideBar": true,
   "skip_h1_title": false,
   "title_cell": "Table of Contents",
   "title_sidebar": "Contents",
   "toc_cell": false,
   "toc_position": {},
   "toc_section_display": true,
   "toc_window_display": false
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}