intro-to-dimension-modeling.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 46,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "\u001b[33mDEPRECATION: Python 2.7 reached the end of its life on January 1st, 2020. Please upgrade your Python as Python 2.7 is no longer maintained. pip 21.0 will drop support for Python 2.7 in January 2021. More details about Python 2 support in pip can be found at https://pip.pypa.io/en/latest/development/release-process/#python-2-support pip 21.0 will remove support for this functionality.\u001b[0m\n",
      "Requirement already satisfied: psycopg2 in /artifacts/virtualenv/python2/lib/python2.7/site-packages (2.7.3.1)\n",
      "Requirement already satisfied: sqlalchemy in /artifacts/virtualenv/python2/lib/python2.7/site-packages (1.0.6)\n",
      "\u001b[33mWARNING: You are using pip version 20.3.1; however, version 20.3.4 is available.\n",
      "You should consider upgrading via the '/artifacts/virtualenv/python2/bin/python -m pip install --upgrade pip' command.\u001b[0m\n"
     ]
    }
   ],
   "source": [
    "!pip install psycopg2 sqlalchemy"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<div align=\"right\"><img src=\"./img/logos.png\" alt=\"drawing\" width=\"400\" /> <div/> \n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "\n",
    "# Data modeling at scale: introduction to dimension modeling\n",
    "_Diego Ardila - Staff Data Scientist at Shopify_\n",
    "\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "* [Data modeling at scale?](#data_modeling_at_scale)\n",
    "  * [What is data warehouse?](#why_a_data_warehouse)\n",
    "  * [Why a data warehouse?](#what_is_data_warehouse)\n",
    "  * [What is ETL/ELT?](#what_is_etl)\n",
    "* [Methodology (dimensional modeling)](#dimensional_modeling)\n",
    "  * [Example 1: Who are the best movie directors?](#dimensional_modeling_example_1)\n",
    "    * [Why dimensional modeling](#why_dimensional_modeling_example_1)\n",
    "    * [Dimensions](#dimensional_modeling_dimensions)\n",
    "    * [Facts](#dimensional_modeling_facts)\n",
    "  * [Example 2: Whhen did a show started, and ended? What was the total runtime?](#dimensional_modeling_example_2)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Data modeling at scale? <a class=\"anchor\" id=\"data_modeling_at_scale\"></a>\n",
    "\n",
    "*Infrastructure + ETL/ELT + Methodology (dimensional modeling)  = Data warehouse/Data lake*\n",
    "\n",
    "## What is data warehouse? <a class=\"anchor\" id=\"what_is_data_warehouse\"></a>\n",
    "\n",
    "1. Database \n",
    "2. Data collected from one or multiple sources\n",
    "3. Designed to support reporting and data analytics\n",
    "\n",
    "## Why a data warehouse? <a class=\"anchor\" id=\"why_a_data_warehouse\"></a>\n",
    "1. Keep historic data\n",
    "2. Central view\n",
    "3. Data quality and Common model\n",
    "\n",
    "![alt](https://imgs.xkcd.com/comics/standards_2x.png)\n",
    "\n",
    "4. Query performance without impacting production systems\n",
    "5. Augment source system  \n",
    "\n",
    "\n",
    "\n",
    "## What is ETL/ELT? <a class=\"anchor\" id=\"what_is_etl\"></a>\n",
    "\n",
    "**Extraction + Transformation + Loading**\n",
    "\n",
    "- Extract: move data from the source to the data lake. Technlogies such as: [iceberg](https://iceberg.apache.org/), [kafka](https://kafka.apache.org/)  \n",
    "- Transformation: augment/transform the sources into a consistent format. Technologies such as [spark](https://spark.apache.org/), [dbt](https://www.getdbt.com/), [flink](https://flink.apache.org/), [apache beam](https://beam.apache.org/).   \n",
    "- Load: load data into frontroom tables for consumption. \n",
    "\n",
    "All this typically wrapped around some scheduling solution in batch aplications, e.g. [airflow](https://airflow.apache.org/), [prefect](https://www.prefect.io/), [luigi](https://github.com/spotify/luigi). \n",
    "\n",
    "Alternatively, we can talk about ELT\n",
    "\n",
    "**Extraction + Loading + Transformation**\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Methodology (dimensional modeling) <a class=\"anchor\" id=\"dimensional_modeling_example_2\"></a>\n",
    "- Data warehouse design technique, developed by [Kimball and Ross](https://www.kimballgroup.com/data-warehouse-business-intelligence-resources/books/data-warehouse-dw-toolkit/). \n",
    "- Latest edition (3rd) of the K & R book created in 2013. Right before Redshift and pre-cloud. Still makes sense for understability.\n",
    "- Based on the \"star\" model. Two main elements: conformed dimensions and facts:\n",
    "\n",
    "![alt text](https://cdn.holistics.io/guidebook/star-schema.png \"Title\")\n",
    "\n",
    "- Four large steps: \n",
    "  - Select the business process\n",
    "  - Identify grain\n",
    "  - Identify the dimensions\n",
    "  - Identify facts\n",
    "  - Implement model. We can use a SQL transformation engine. Check out [DBT](https://www.getdbt.com/).\n",
    "- Let's use an example to introduce the main concepts\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Our database: IMDB database (RAW!).\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "\n",
    "\n",
    "[IMDB datasets](https://www.imdb.com/interfaces/)\n",
    "\n",
    "\n",
    "<div align=\"center\"><img src=\"./img/imdb_raw.png\" width=\"500\" /> <div/> "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Let's take a look... "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [],
   "source": [
    "import psycopg2\n",
    "import sqlalchemy\n",
    "import urllib\n",
    "from IPython.display import display\n",
    "\n",
    "username = 'ssc_workshop'\n",
    "password = 'sql_for_ds'\n",
    "host = \"ssc-2022-workshop.ct6ghoz7smhy.us-east-1.rds.amazonaws.com\"\n",
    "port = '5432'\n",
    "engine = sqlalchemy.create_engine('postgresql://{username}:{password}@{host}:{port}/imdb_raw'.format(username=username, password=password, host=host, port=port))\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Ratings sample is...\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>index</th>\n",
       "      <th>tconst</th>\n",
       "      <th>averageRating</th>\n",
       "      <th>numVotes</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0</td>\n",
       "      <td>tt0000001</td>\n",
       "      <td>5.7</td>\n",
       "      <td>1884</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1</td>\n",
       "      <td>tt0000002</td>\n",
       "      <td>5.9</td>\n",
       "      <td>250</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2</td>\n",
       "      <td>tt0000003</td>\n",
       "      <td>6.5</td>\n",
       "      <td>1668</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>3</td>\n",
       "      <td>tt0000004</td>\n",
       "      <td>5.8</td>\n",
       "      <td>163</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>4</td>\n",
       "      <td>tt0000005</td>\n",
       "      <td>6.2</td>\n",
       "      <td>2490</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>5</td>\n",
       "      <td>tt0000006</td>\n",
       "      <td>5.2</td>\n",
       "      <td>166</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>6</td>\n",
       "      <td>tt0000007</td>\n",
       "      <td>5.4</td>\n",
       "      <td>774</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>7</td>\n",
       "      <td>tt0000008</td>\n",
       "      <td>5.4</td>\n",
       "      <td>2025</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>8</td>\n",
       "      <td>tt0000009</td>\n",
       "      <td>5.3</td>\n",
       "      <td>195</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>9</td>\n",
       "      <td>tt0000010</td>\n",
       "      <td>6.9</td>\n",
       "      <td>6809</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   index     tconst  averageRating  numVotes\n",
       "0      0  tt0000001            5.7      1884\n",
       "1      1  tt0000002            5.9       250\n",
       "2      2  tt0000003            6.5      1668\n",
       "3      3  tt0000004            5.8       163\n",
       "4      4  tt0000005            6.2      2490\n",
       "5      5  tt0000006            5.2       166\n",
       "6      6  tt0000007            5.4       774\n",
       "7      7  tt0000008            5.4      2025\n",
       "8      8  tt0000009            5.3       195\n",
       "9      9  tt0000010            6.9      6809"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Title basics sample...\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>index</th>\n",
       "      <th>tconst</th>\n",
       "      <th>titleType</th>\n",
       "      <th>primaryTitle</th>\n",
       "      <th>originalTitle</th>\n",
       "      <th>isAdult</th>\n",
       "      <th>startYear</th>\n",
       "      <th>endYear</th>\n",
       "      <th>runtimeMinutes</th>\n",
       "      <th>genres</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>19162</td>\n",
       "      <td>tt0019444</td>\n",
       "      <td>movie</td>\n",
       "      <td>Take Me Home</td>\n",
       "      <td>Take Me Home</td>\n",
       "      <td>0</td>\n",
       "      <td>1928.0</td>\n",
       "      <td>None</td>\n",
       "      <td>60.0</td>\n",
       "      <td>Comedy,Drama,Romance</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>19163</td>\n",
       "      <td>tt0019445</td>\n",
       "      <td>short</td>\n",
       "      <td>Tall Timber</td>\n",
       "      <td>Tall Timber</td>\n",
       "      <td>0</td>\n",
       "      <td>1928.0</td>\n",
       "      <td>None</td>\n",
       "      <td>8.0</td>\n",
       "      <td>Animation,Comedy,Short</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>19164</td>\n",
       "      <td>tt0019446</td>\n",
       "      <td>movie</td>\n",
       "      <td>Der Tanzstudent</td>\n",
       "      <td>Der Tanzstudent</td>\n",
       "      <td>0</td>\n",
       "      <td>1928.0</td>\n",
       "      <td>None</td>\n",
       "      <td>NaN</td>\n",
       "      <td>\\N</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>19165</td>\n",
       "      <td>tt0019447</td>\n",
       "      <td>movie</td>\n",
       "      <td>Tarzan the Mighty</td>\n",
       "      <td>Tarzan the Mighty</td>\n",
       "      <td>0</td>\n",
       "      <td>1928.0</td>\n",
       "      <td>None</td>\n",
       "      <td>300.0</td>\n",
       "      <td>Action,Adventure</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>19166</td>\n",
       "      <td>tt0019448</td>\n",
       "      <td>movie</td>\n",
       "      <td>Taxi 13</td>\n",
       "      <td>Taxi 13</td>\n",
       "      <td>0</td>\n",
       "      <td>1928.0</td>\n",
       "      <td>None</td>\n",
       "      <td>64.0</td>\n",
       "      <td>Comedy,Crime,Drama</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>19167</td>\n",
       "      <td>tt0019449</td>\n",
       "      <td>movie</td>\n",
       "      <td>Tearin' Into Trouble</td>\n",
       "      <td>Tearin' Into Trouble</td>\n",
       "      <td>0</td>\n",
       "      <td>1927.0</td>\n",
       "      <td>None</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Western</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>19168</td>\n",
       "      <td>tt0019450</td>\n",
       "      <td>movie</td>\n",
       "      <td>Telling the World</td>\n",
       "      <td>Telling the World</td>\n",
       "      <td>0</td>\n",
       "      <td>1928.0</td>\n",
       "      <td>None</td>\n",
       "      <td>80.0</td>\n",
       "      <td>Comedy,Drama</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>19169</td>\n",
       "      <td>tt0019451</td>\n",
       "      <td>movie</td>\n",
       "      <td>Tempest</td>\n",
       "      <td>Tempest</td>\n",
       "      <td>0</td>\n",
       "      <td>1928.0</td>\n",
       "      <td>None</td>\n",
       "      <td>102.0</td>\n",
       "      <td>Drama,Romance</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>19170</td>\n",
       "      <td>tt0019452</td>\n",
       "      <td>movie</td>\n",
       "      <td>Tenderfeet</td>\n",
       "      <td>Tenderfeet</td>\n",
       "      <td>0</td>\n",
       "      <td>1928.0</td>\n",
       "      <td>None</td>\n",
       "      <td>NaN</td>\n",
       "      <td>\\N</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>19171</td>\n",
       "      <td>tt0019453</td>\n",
       "      <td>movie</td>\n",
       "      <td>Tenderloin</td>\n",
       "      <td>Tenderloin</td>\n",
       "      <td>0</td>\n",
       "      <td>1928.0</td>\n",
       "      <td>None</td>\n",
       "      <td>85.0</td>\n",
       "      <td>Drama</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   index     tconst titleType          primaryTitle         originalTitle  \\\n",
       "0  19162  tt0019444     movie          Take Me Home          Take Me Home   \n",
       "1  19163  tt0019445     short           Tall Timber           Tall Timber   \n",
       "2  19164  tt0019446     movie       Der Tanzstudent       Der Tanzstudent   \n",
       "3  19165  tt0019447     movie     Tarzan the Mighty     Tarzan the Mighty   \n",
       "4  19166  tt0019448     movie               Taxi 13               Taxi 13   \n",
       "5  19167  tt0019449     movie  Tearin' Into Trouble  Tearin' Into Trouble   \n",
       "6  19168  tt0019450     movie     Telling the World     Telling the World   \n",
       "7  19169  tt0019451     movie               Tempest               Tempest   \n",
       "8  19170  tt0019452     movie            Tenderfeet            Tenderfeet   \n",
       "9  19171  tt0019453     movie            Tenderloin            Tenderloin   \n",
       "\n",
       "  isAdult  startYear endYear  runtimeMinutes                  genres  \n",
       "0       0     1928.0    None            60.0    Comedy,Drama,Romance  \n",
       "1       0     1928.0    None             8.0  Animation,Comedy,Short  \n",
       "2       0     1928.0    None             NaN                      \\N  \n",
       "3       0     1928.0    None           300.0        Action,Adventure  \n",
       "4       0     1928.0    None            64.0      Comedy,Crime,Drama  \n",
       "5       0     1927.0    None             NaN                 Western  \n",
       "6       0     1928.0    None            80.0            Comedy,Drama  \n",
       "7       0     1928.0    None           102.0           Drama,Romance  \n",
       "8       0     1928.0    None             NaN                      \\N  \n",
       "9       0     1928.0    None            85.0                   Drama  "
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Crew sample is...\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>index</th>\n",
       "      <th>tconst</th>\n",
       "      <th>directors</th>\n",
       "      <th>writers</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>458253</td>\n",
       "      <td>tt0476898</td>\n",
       "      <td>nm0499969</td>\n",
       "      <td>nm0499969,nm1425526</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>458254</td>\n",
       "      <td>tt0476899</td>\n",
       "      <td>nm1291436</td>\n",
       "      <td>nm0275425</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>458255</td>\n",
       "      <td>tt0476900</td>\n",
       "      <td>nm1950030</td>\n",
       "      <td>\\N</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>458256</td>\n",
       "      <td>tt0476901</td>\n",
       "      <td>nm1950030</td>\n",
       "      <td>\\N</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>458257</td>\n",
       "      <td>tt0476902</td>\n",
       "      <td>\\N</td>\n",
       "      <td>\\N</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>458258</td>\n",
       "      <td>tt0476903</td>\n",
       "      <td>\\N</td>\n",
       "      <td>\\N</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>458259</td>\n",
       "      <td>tt0476904</td>\n",
       "      <td>\\N</td>\n",
       "      <td>\\N</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>458260</td>\n",
       "      <td>tt0476905</td>\n",
       "      <td>nm0472016</td>\n",
       "      <td>nm0472016</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>458261</td>\n",
       "      <td>tt0476906</td>\n",
       "      <td>\\N</td>\n",
       "      <td>\\N</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>458262</td>\n",
       "      <td>tt0476907</td>\n",
       "      <td>\\N</td>\n",
       "      <td>\\N</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    index     tconst  directors              writers\n",
       "0  458253  tt0476898  nm0499969  nm0499969,nm1425526\n",
       "1  458254  tt0476899  nm1291436            nm0275425\n",
       "2  458255  tt0476900  nm1950030                   \\N\n",
       "3  458256  tt0476901  nm1950030                   \\N\n",
       "4  458257  tt0476902         \\N                   \\N\n",
       "5  458258  tt0476903         \\N                   \\N\n",
       "6  458259  tt0476904         \\N                   \\N\n",
       "7  458260  tt0476905  nm0472016            nm0472016\n",
       "8  458261  tt0476906         \\N                   \\N\n",
       "9  458262  tt0476907         \\N                   \\N"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Episodes_sample sample is...\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>index</th>\n",
       "      <th>tconst</th>\n",
       "      <th>parentTconst</th>\n",
       "      <th>seasonNumber</th>\n",
       "      <th>episodeNumber</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0</td>\n",
       "      <td>tt0020666</td>\n",
       "      <td>tt15180956</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1</td>\n",
       "      <td>tt0020829</td>\n",
       "      <td>tt15180956</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2</td>\n",
       "      <td>tt0021166</td>\n",
       "      <td>tt15180956</td>\n",
       "      <td>1</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>3</td>\n",
       "      <td>tt0021612</td>\n",
       "      <td>tt15180956</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>4</td>\n",
       "      <td>tt0021655</td>\n",
       "      <td>tt15180956</td>\n",
       "      <td>2</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>5</td>\n",
       "      <td>tt0021663</td>\n",
       "      <td>tt15180956</td>\n",
       "      <td>2</td>\n",
       "      <td>6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>6</td>\n",
       "      <td>tt0021664</td>\n",
       "      <td>tt15180956</td>\n",
       "      <td>2</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>7</td>\n",
       "      <td>tt0021701</td>\n",
       "      <td>tt15180956</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>8</td>\n",
       "      <td>tt0021802</td>\n",
       "      <td>tt15180956</td>\n",
       "      <td>2</td>\n",
       "      <td>11</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>9</td>\n",
       "      <td>tt0022009</td>\n",
       "      <td>tt15180956</td>\n",
       "      <td>2</td>\n",
       "      <td>10</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   index     tconst parentTconst seasonNumber episodeNumber\n",
       "0      0  tt0020666   tt15180956            1             2\n",
       "1      1  tt0020829   tt15180956            1             1\n",
       "2      2  tt0021166   tt15180956            1             3\n",
       "3      3  tt0021612   tt15180956            2             2\n",
       "4      4  tt0021655   tt15180956            2             5\n",
       "5      5  tt0021663   tt15180956            2             6\n",
       "6      6  tt0021664   tt15180956            2             4\n",
       "7      7  tt0021701   tt15180956            2             1\n",
       "8      8  tt0021802   tt15180956            2            11\n",
       "9      9  tt0022009   tt15180956            2            10"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "title_basics_sample = pd.read_sql(\"select * from title_basics limit 10\", engine)\n",
    "crew_sample = pd.read_sql(\"select * from crew limit 10\", engine)\n",
    "ratings_sample = pd.read_sql(\"select * from title_ratings limit 10\", engine)\n",
    "episodes_sample = pd.read_sql(\"select * from episodes limit 10\", engine)\n",
    "print(\"Ratings sample is...\")\n",
    "display(ratings_sample)\n",
    "print(\"Title basics sample...\")\n",
    "display(title_basics_sample)\n",
    "print(\"Crew sample is...\")\n",
    "display(crew_sample)\n",
    "print(\"Episodes_sample sample is...\")\n",
    "display(episodes_sample)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Example 1: Who are the best movie directors? <a class=\"anchor\" id=\"dimensional_modeling_example_1\"></a>\n",
    "\n",
    "#### Why dimensional modeling? <a class=\"anchor\" id=\"why_dimensional_modeling_example_1\"></a>\n",
    "\n",
    "Let's do first the SQL query....\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "who_are_best_movie_directors = \"\"\"\n",
    "with directors as (\n",
    "  select  tconst, d as director\n",
    "  from crew c, unnest(regexp_split_to_array(directors, ',')) d\n",
    "),\n",
    "ratings_directors as (\n",
    "select d.director, avg(r.\"averageRating\") as averageRating\n",
    "from \n",
    "    title_basics t \n",
    "    join directors d on t.tconst = d.tconst\n",
    "    join title_ratings r on t.tconst = r.tconst\n",
    "group by 1\n",
    ")\n",
    "select rd.director, rd.averageRating\n",
    "from ratings_directors rd\n",
    "order by averageRating desc\n",
    "limit 10 \n",
    "\"\"\"\n",
    "print(pd.read_sql(who_are_best_movie_directors, engine))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "This works, so what are the problems? \n",
    "\n",
    "- We carry the naming and standards of the source database\n",
    "- Okay to perform on ad-hoc basis, but what if you have to regularly use this information?\n",
    "- Agregations in production are costly. You don't want to do this\n",
    "\n",
    "Ideally we have something simpler, \n",
    "\n",
    "```\n",
    "select director_id, avg(average_rating) as average_rating\n",
    "from directors_facts join directors_dimension using(director_id)\n",
    "group by 1\n",
    "order by 2 desc\n",
    "limit 10\n",
    "```"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Dimensions <a class=\"anchor\" id=\"dimensional_modeling_dimensions\"></a>\n",
    "\n",
    "- Business entity. In our example, `directors_dimension`\n",
    "- There are several types of dimensions:\n",
    "  - Type 1 dimension\n",
    "  - Type 2 dimension\n",
    "- Simple case: type 1 dimension:\n",
    "\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Movie dimension\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>movie_id</th>\n",
       "      <th>primary_title</th>\n",
       "      <th>release_year</th>\n",
       "      <th>runtime_minutes</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>tt0010047</td>\n",
       "      <td>Daring Hearts</td>\n",
       "      <td>1919.0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>tt0010048</td>\n",
       "      <td>A Dark Lantern</td>\n",
       "      <td>1920.0</td>\n",
       "      <td>60.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>tt0010049</td>\n",
       "      <td>The Dark Star</td>\n",
       "      <td>1919.0</td>\n",
       "      <td>60.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>tt0010050</td>\n",
       "      <td>A Daughter of Eve</td>\n",
       "      <td>1919.0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>tt0010051</td>\n",
       "      <td>Daughter of Mine</td>\n",
       "      <td>1919.0</td>\n",
       "      <td>50.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    movie_id      primary_title  release_year  runtime_minutes\n",
       "0  tt0010047      Daring Hearts        1919.0              NaN\n",
       "1  tt0010048     A Dark Lantern        1920.0             60.0\n",
       "2  tt0010049      The Dark Star        1919.0             60.0\n",
       "3  tt0010050  A Daughter of Eve        1919.0              NaN\n",
       "4  tt0010051   Daughter of Mine        1919.0             50.0"
      ]
     },
     "execution_count": 20,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "create_dimensions = \"\"\"\n",
    "drop table if exists directors_dimension_diego_ardila;\n",
    "create table directors_dimension_diego_ardila as\n",
    "select d as director_id\n",
    "from crew c, unnest(regexp_split_to_array(directors, ',')) d\n",
    "limit 10000;\n",
    "\n",
    "drop table if exists movie_dimension_diego_ardila;\n",
    "create table movie_dimension_diego_ardila as\n",
    "select t.tconst as movie_id, \"primaryTitle\" as primary_title, \"startYear\" as release_year, \"runtimeMinutes\" as runtime_minutes\n",
    "from title_basics t\n",
    "limit 10000;\n",
    "\n",
    "\"\"\"\n",
    "engine.execute(create_dimensions)\n",
    "print(\"Movie dimension \\n ...\")\n",
    "pd.read_sql(\"select * from movie_dimension_diego_ardila\", engine).head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Fact tables <a class=\"anchor\" id=\"dimensional_modeling_facts\"></a>\n",
    "\n",
    "- Table containing measurements.\n",
    "- Grain defined by related dimensions.\n",
    "- Facts are usually additive, but not always.\n",
    "- Resolves many to many relationships.\n",
    "- There are several types of fact tables\n",
    "  - *Transaction Fact Table* represents an event that occurs at the primary point.\n",
    "  - *Snapshot Fact Table* describes the state of things at a particular time.\n",
    "  - *Accumulated Fact Table* is used to show the activity of a process that has a beginning and an end. \n",
    "- Simple case: Transaction Fact Table of movies directed by a director.\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Directors Facts\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>director_id</th>\n",
       "      <th>movie_id</th>\n",
       "      <th>average_rating</th>\n",
       "      <th>movie_released_on_year</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>nm0005690</td>\n",
       "      <td>tt0000001</td>\n",
       "      <td>5.7</td>\n",
       "      <td>1894.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>nm0721526</td>\n",
       "      <td>tt0000002</td>\n",
       "      <td>5.9</td>\n",
       "      <td>1892.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>nm0721526</td>\n",
       "      <td>tt0000003</td>\n",
       "      <td>6.5</td>\n",
       "      <td>1892.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>nm0721526</td>\n",
       "      <td>tt0000004</td>\n",
       "      <td>5.8</td>\n",
       "      <td>1892.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>nm0005690</td>\n",
       "      <td>tt0000005</td>\n",
       "      <td>6.2</td>\n",
       "      <td>1893.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  director_id   movie_id  average_rating  movie_released_on_year\n",
       "0   nm0005690  tt0000001             5.7                  1894.0\n",
       "1   nm0721526  tt0000002             5.9                  1892.0\n",
       "2   nm0721526  tt0000003             6.5                  1892.0\n",
       "3   nm0721526  tt0000004             5.8                  1892.0\n",
       "4   nm0005690  tt0000005             6.2                  1893.0"
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "create_facts = \"\"\"\n",
    "-- An entry for every movie a director has directed\n",
    "drop table if exists directors_facts_diego_ardila;\n",
    "create table directors_facts_diego_ardila as\n",
    "with directors as (\n",
    "  select  tconst, d as director_id\n",
    "  from crew c, unnest(regexp_split_to_array(directors, ',')) d\n",
    ")\n",
    "select d.director_id, t.tconst as movie_id, \"averageRating\" as average_rating, \"startYear\" as movie_released_on_year\n",
    "from \n",
    "    title_basics t \n",
    "    join title_ratings r on t.tconst = r.tconst\n",
    "    join directors d on t.tconst = d.tconst\n",
    "limit 100;\n",
    "\n",
    "\"\"\"\n",
    "engine.execute(create_facts)\n",
    "print(\"Directors Facts\")\n",
    "pd.read_sql(\"select * from directors_facts_diego_ardila\", engine).head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "How does the query looks now? \n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "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>director_id</th>\n",
       "      <th>average_rating</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>nm0718201</td>\n",
       "      <td>7.800000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>nm0000320</td>\n",
       "      <td>7.433333</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>nm0003593</td>\n",
       "      <td>7.300000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>nm0210701</td>\n",
       "      <td>7.200000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>nm0412235</td>\n",
       "      <td>7.000000</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  director_id  average_rating\n",
       "0   nm0718201        7.800000\n",
       "1   nm0000320        7.433333\n",
       "2   nm0003593        7.300000\n",
       "3   nm0210701        7.200000\n",
       "4   nm0412235        7.000000"
      ]
     },
     "execution_count": 19,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "who_are_best_movie_directors_using_dm = \"\"\"\n",
    "select director_id, avg(average_rating) as average_rating\n",
    "from directors_facts_diego_ardila join directors_dimension_diego_ardila using(director_id)\n",
    "group by 1\n",
    "order by 2 desc\n",
    "limit 10 \n",
    "\"\"\"\n",
    "pd.read_sql(who_are_best_movie_directors_using_dm, engine).head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "##### What if we have a new use case: first year a director directed a movie?\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "first_year_director_directed_movie_sql = \"\"\"\n",
    "select director_id, min(movie_released_on_year) as first_year_released_movie\n",
    "from directors_facts_diego_ardila join directors_dimension_diego_ardila using(director_id)\n",
    "group by 1\n",
    "order by 2 desc\n",
    "limit 10 \n",
    "\"\"\"\n",
    "pd.read_sql(first_year_director_directed_movie_sql, engine).head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Example 2: When did a show started and ended? What was the total runtime? <a class=\"anchor\" id=\"dimensional_modeling_example_2\"></a>\n",
    "\n",
    "1. What grain?: tv show\n",
    "2. What kind of query are we looking for? \n",
    "\n",
    "```\n",
    "select show_id, primary_title, first_episode_started_on, last_episode_ended_on, number_of_episodes, number_of_seasons, total_run_time\n",
    "from show_accumulating_facts\n",
    "```\n",
    "\n",
    "3. Let's do first the SQL query\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "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>series_id</th>\n",
       "      <th>first_episode_started_on</th>\n",
       "      <th>last_episode_ended_on</th>\n",
       "      <th>number_of_episodes</th>\n",
       "      <th>number_of_seasons</th>\n",
       "      <th>total_run_time</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>tt0025509</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>3</td>\n",
       "      <td>1</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>tt0035599</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>tt0035803</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>tt0038276</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>tt0039120</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   series_id first_episode_started_on last_episode_ended_on  \\\n",
       "0  tt0025509                     None                  None   \n",
       "1  tt0035599                     None                  None   \n",
       "2  tt0035803                     None                  None   \n",
       "3  tt0038276                     None                  None   \n",
       "4  tt0039120                     None                  None   \n",
       "\n",
       "   number_of_episodes  number_of_seasons total_run_time  \n",
       "0                   3                  1           None  \n",
       "1                   0                  0           None  \n",
       "2                   0                  0           None  \n",
       "3                   2                  2           None  \n",
       "4                   2                  2           None  "
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "when_did_a_show_started_and_ended_sql = \"\"\"\n",
    "with series as (\n",
    "  select tconst, \"primaryTitle\", \"startYear\", \"titleType\"\n",
    "  from title_basics t\n",
    "  where \"titleType\" = 'tvSeries'\n",
    "),\n",
    "episodes_series as (\n",
    "  select tconst, \"primaryTitle\", \"startYear\", \"runtimeMinutes\"\n",
    "  from title_basics t\n",
    "  where \"titleType\" = 'tvSeries'\n",
    "),\n",
    "series_facts as (\n",
    "select s.tconst as series_id\n",
    "   \n",
    "    , min(es.\"startYear\") as first_episode_started_on\n",
    "    , max(es.\"startYear\") as last_episode_ended_on\n",
    "    , count(distinct \"seasonNumber\") as number_of_seasons\n",
    "    , count(distinct \"episodeNumber\") as number_of_episodes\n",
    "    , sum(cast(es.\"runtimeMinutes\" as float)) as total_run_time\n",
    "from \n",
    "    series s \n",
    "    left join episodes e on s.tconst = e.\"parentTconst\"\n",
    "    left join episodes_series es on e.tconst = es.tconst\n",
    " group by 1\n",
    ")\n",
    "select series_id, first_episode_started_on, last_episode_ended_on, number_of_episodes, number_of_seasons, total_run_time\n",
    "from series_facts\n",
    "limit 5\n",
    "\"\"\"\n",
    "pd.read_sql(when_did_a_show_started_and_ended_sql, engine)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "4. Enters dimensional modeling....\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Which dimensions?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "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>series_id</th>\n",
       "      <th>primary_title</th>\n",
       "      <th>series_started_on</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>tt0115124</td>\n",
       "      <td>Buzzkill</td>\n",
       "      <td>1996.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>tt0118330</td>\n",
       "      <td>Gun</td>\n",
       "      <td>1997.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>tt0025509</td>\n",
       "      <td>Les Misérables</td>\n",
       "      <td>1934.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>tt0035599</td>\n",
       "      <td>Voice of Firestone Televues</td>\n",
       "      <td>1943.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>tt0035803</td>\n",
       "      <td>The German Weekly Review</td>\n",
       "      <td>1940.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   series_id                primary_title  series_started_on\n",
       "0  tt0115124                     Buzzkill             1996.0\n",
       "1  tt0118330                          Gun             1997.0\n",
       "2  tt0025509               Les Misérables             1934.0\n",
       "3  tt0035599  Voice of Firestone Televues             1943.0\n",
       "4  tt0035803     The German Weekly Review             1940.0"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "create_dimensions = \"\"\"\n",
    "drop table if exists series_dimension_diego_ardila;\n",
    "create table series_dimension_diego_ardila as\n",
    "select tconst as series_id, \"primaryTitle\" as primary_title, \"startYear\" as series_started_on\n",
    "from title_basics t\n",
    "where \"titleType\" = 'tvSeries'\n",
    "\"\"\"\n",
    "engine.execute(create_dimensions)\n",
    "pd.read_sql(\"select * from series_dimension_diego_ardila limit 100\", engine).head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Which facts?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "<sqlalchemy.engine.result.ResultProxy at 0x7f23580dd650>"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "create_facts =  \"\"\"\n",
    "drop table if exists series_accumulating_facts_diego_ardila;\n",
    "create table series_accumulating_facts_diego_ardila as\n",
    "with series as (\n",
    "  select tconst, \"primaryTitle\", \"startYear\", \"titleType\"\n",
    "  from title_basics t\n",
    "  where \"titleType\" = 'tvSeries'\n",
    "),\n",
    "episodes_series as (\n",
    "  select tconst, \"primaryTitle\", \"startYear\", \"runtimeMinutes\"\n",
    "  from title_basics t\n",
    "  where \"titleType\" = 'tvSeries'\n",
    "),\n",
    "series_facts as (\n",
    "select s.tconst as series_id \n",
    "    , min(es.\"startYear\") as first_episode_started_on\n",
    "    , max(es.\"startYear\") as last_episode_ended_on\n",
    "    , count(distinct \"seasonNumber\") as number_of_seasons\n",
    "    , count(distinct \"episodeNumber\") as number_of_episodes\n",
    "    , sum(cast(es.\"runtimeMinutes\" as float)) as total_run_time\n",
    "from \n",
    "    series s \n",
    "    left join episodes e on s.tconst = e.\"parentTconst\"\n",
    "    left join episodes_series es on e.tconst = es.tconst\n",
    " group by 1\n",
    ")\n",
    "select series_id, first_episode_started_on, last_episode_ended_on, number_of_episodes, number_of_seasons, total_run_time\n",
    "from series_facts limit 10\n",
    "\"\"\"\n",
    "engine.execute(create_facts)\n",
    "\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "*How does the query looks now?*"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "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>series_id</th>\n",
       "      <th>first_episode_started_on</th>\n",
       "      <th>last_episode_ended_on</th>\n",
       "      <th>number_of_episodes</th>\n",
       "      <th>number_of_seasons</th>\n",
       "      <th>total_run_time</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>tt0025509</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>3</td>\n",
       "      <td>1</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>tt0035599</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>tt0035803</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>tt0038276</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>tt0039120</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   series_id first_episode_started_on last_episode_ended_on  \\\n",
       "0  tt0025509                     None                  None   \n",
       "1  tt0035599                     None                  None   \n",
       "2  tt0035803                     None                  None   \n",
       "3  tt0038276                     None                  None   \n",
       "4  tt0039120                     None                  None   \n",
       "\n",
       "   number_of_episodes  number_of_seasons total_run_time  \n",
       "0                   3                  1           None  \n",
       "1                   0                  0           None  \n",
       "2                   0                  0           None  \n",
       "3                   2                  2           None  \n",
       "4                   2                  2           None  "
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "\n",
    "pd.read_sql(\"select * from series_accumulating_facts_diego_ardila limit 10\", engine).head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Example 3: What was the first and last shows of a director? What was the best and worst shows? How many shows has a director had?  <a class=\"anchor\" id=\"dimensional_modeling_example_3\"></a>\n",
    "\n",
    "1. What grain?: tv series \n",
    "2. What kind of query are we looking for?\n",
    "\n",
    "```\n",
    "select director_id\n",
    "    , first_series_started_on\n",
    "    , last_series_started_on\n",
    "    , fsd.series_id as best_series_id\n",
    "    , lsd.series_id as last_series_id\n",
    "    , number_of_series\n",
    "from directors_accumulating_facts d\n",
    "    join series_dimension fsd on  d.best_series_id = fsd.series_id\n",
    "    join series_dimension lsd on  d.last_series_id = lsd.series_id\n",
    "```\n",
    "3. Let's do first the SQL query \n",
    "4. Which dimensions? Which facts?\n",
    "\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Other topics that we did not covered\n",
    "\n",
    "\n",
    "- Slow changing dimensions\n",
    "- Junk dimensions\n",
    "- Outrigger dimension\n",
    "- Role playing dimensions\n",
    "- Bus Architecture\n",
    "- ... :)  "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**Thanks for joining and ...**\n",
    "<div align=\"center\"><img src=\"./img/thats_all_folks.png\" width=\"500\" /> <div/> "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3_starscream"
  },
  "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.13"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}