{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# El almacén, el lago, y por qué se copian los datos a propósito\n",
    "\n",
    "La base con la que trabajaste todo el libro está hecha para escribir. Un almacén está hecho para leer, y eso cambia cómo se diseña.\n",
    "\n",
    "Cuaderno de práctica del capítulo 15 de **SQL desde cero**, de Miss Yera.\n",
    "\n",
    "Corre de arriba abajo. Si lo abres en Google Colab no necesitas instalar nada.\n",
    "\n",
    "Capítulo completo: https://missyera.com/guias/sql-desde-cero/warehouse-lake-y-capas/\n",
    "\n",
    "Los ejercicios están al final y traen una celda vacía debajo de cada uno. Las\n",
    "respuestas viven en el cuaderno de soluciones, y merece la pena pelearse un\n",
    "rato antes de abrirlo 💛"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Antes de empezar\n",
    "\n",
    "Se baja la base y se deja lista una función `q()` que corre las consultas."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "import sqlite3\n",
    "import urllib.request\n",
    "\n",
    "import pandas as pd\n",
    "\n",
    "urllib.request.urlretrieve(\"https://missyera.com/static/datasets/tienda.db\", \"tienda.db\")\n",
    "con = sqlite3.connect(\"tienda.db\")\n",
    "\n",
    "def q(sql):\n",
    "    \"\"\"Corre las sentencias del bloque y devuelve la ultima como tabla.\n",
    "\n",
    "    Parte por sentencias igual que la consola de SQLite, porque un bloque puede\n",
    "    traer varias y un CREATE TRIGGER lleva punto y coma dentro de su cuerpo.\n",
    "    \"\"\"\n",
    "    resultado = None\n",
    "    trozo = \"\"\n",
    "    for linea in sql.splitlines(keepends=True):\n",
    "        trozo += linea\n",
    "        if sqlite3.complete_statement(trozo):\n",
    "            if trozo.strip():\n",
    "                cur = con.execute(trozo.strip())\n",
    "                resultado = (pd.DataFrame(cur.fetchall(),\n",
    "                                          columns=[d[0] for d in cur.description])\n",
    "                             if cur.description else None)\n",
    "            trozo = \"\"\n",
    "    if trozo.strip():\n",
    "        cur = con.execute(trozo.strip())\n",
    "        resultado = (pd.DataFrame(cur.fetchall(),\n",
    "                                  columns=[d[0] for d in cur.description])\n",
    "                     if cur.description else None)\n",
    "    con.commit()\n",
    "    return resultado\n",
    "\n",
    "q(\"SELECT name FROM sqlite_master WHERE type = 'table' ORDER BY name\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Antes de empezar\n",
    "\n",
    "Esta celda baja el ayudante que corrige tus ejercicios. Después, en cada\n",
    "ejercicio que se pueda corregir solo, vas a ver `%%revisa` arriba de la celda:\n",
    "escribe tu respuesta debajo, ejecuta, y te digo si te salió 💛"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "import urllib.request\n",
    "\n",
    "# El ayudante de los cuadernos. Trae la corrección de los ejercicios y, en los\n",
    "# capítulos de consola, la celda mágica que ejecuta los comandos. Se baja en\n",
    "# vez de venir pegado aquí para que siempre sea el último.\n",
    "urllib.request.urlretrieve(\n",
    "    \"https://missyera.com/static/cuadernos/revisa.py\", \"revisa.py\")\n",
    "import revisa\n",
    "revisa.carga({\n",
    "    1: \"bWVzICAgICAgZW5faGVjaG9zICBwZWRpZG9zX2RlbF9tZXMKLS0tLS0tLSAgLS0tLS0tLS0tICAtLS0tLS0tLS0tLS0tLS0KMjAyNi0wNiAgODM4NTkuMjYgICA1MAoyMDI2LTA1ICA5NzA1NC44NyAgIDU0CjIwMjYtMDQgIDg0NjY2LjkzICAgNDkKMjAyNi0wMyAgODc3ODcuNzEgICA0OA==\",\n",
    "    2: \"c2VnbWVudG8gIGNhdGVnb3JpYSAgdmVudGEgICAgICB1bmlkYWRlcwotLS0tLS0tLSAgLS0tLS0tLS0tICAtLS0tLS0tLS0gIC0tLS0tLS0tCkJvZGVnYSAgICBMaW1waWV6YSAgIDEwNDU3My4xOCAgMjg4NgpIb3JlY2EgICAgTGltcGllemEgICAxMDM5MjYuMDMgIDI3NDEKQm9kZWdhICAgIFNuYWNrcyAgICAgOTc2MDYuNjkgICAxOTA5CkhvcmVjYSAgICBCZWJpZGFzICAgIDk1NjMwLjg2ICAgMTUzNgpCb2RlZ2EgICAgQWJhcnJvdGVzICA5MDU5NS40NCAgIDIwMzk=\",\n",
    "    4: \"ZW5fbGFfdGFibGFfZGVfaGVjaG9zICBlbl9lbF9tYWVzdHJvX2RlX2hveSAgbGluZWFzCi0tLS0tLS0tLS0tLS0tLS0tLS0tLSAgLS0tLS0tLS0tLS0tLS0tLS0tLS0gIC0tLS0tLQpDaGljbGF5byAgICAgICAgICAgICAgIEN1c2NvICAgICAgICAgICAgICAgICAxNjk=\",\n",
    "    5: \"ZW5fZWxfb3JpZ2VuICBlbl9sYV9jYXBhX2NydWRhICBlbl9sYV9jYXBhX2xpc3RhCi0tLS0tLS0tLS0tLSAgLS0tLS0tLS0tLS0tLS0tLSAgLS0tLS0tLS0tLS0tLS0tLQowICAgICAgICAgICAgIDI2ODIgICAgICAgICAgICAgIDI2ODI=\",\n",
    "}, lenguaje=\"sql\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Todo el libro trabajaste contra `tienda.db`, que es la base de un\n",
    "sistema: cinco tablas, cada dato en un solo sitio, y claves que las unen. En el\n",
    "capítulo 10 vimos por qué está así.\n",
    "\n",
    "Ahora mira lo que cuesta una pregunta de negocio de las normales:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "q(\"\"\"\n",
    "SELECT c.ciudad, p.canal, round(sum(d.cantidad * d.precio_unit), 2) AS venta\n",
    "FROM detalle d\n",
    "JOIN pedidos  p ON p.id = d.id_pedido\n",
    "JOIN clientes c ON c.id = p.id_cliente\n",
    "GROUP BY c.ciudad, p.canal\n",
    "ORDER BY venta DESC\n",
    "LIMIT 5;\n",
    "\"\"\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Dos JOIN para saber cuánto se vendió por ciudad y canal. Y esa consulta la\n",
    "escribiste tú, que llevas dieciocho capítulos. La jefa de ventas no la va a\n",
    "escribir nunca 🙃"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Escribir y leer piden formas distintas"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "La base del sistema tiene que **escribir** bien: que un pedido\n",
    "entre sin bloquear a nadie, que la ciudad de un cliente esté en un solo lugar\n",
    "para que cambiarla sea un `UPDATE` y no cuarenta.\n",
    "\n",
    "El almacén tiene que **leer** bien: que la pregunta salga en una\n",
    "consulta, sin JOIN y sin que quien la hace tenga que saberse el modelo.\n",
    "\n",
    "Son dos objetivos que tiran para lados contrarios, y por eso son dos bases y\n",
    "no una. El almacén se llena copiando desde el sistema, y esa copia\n",
    "**repite datos a propósito**: la ciudad del cliente va escrita en\n",
    "cada línea de venta. En el capítulo de diseño eso era un pecado; acá es el\n",
    "diseño."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Las tres palabras que vas a oír"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "| Nombre | Qué guarda | Cuándo se le da forma |\n",
    "|---|---|---|\n",
    "| Data warehouse | tablas con columnas y tipos, ya ordenadas | antes de guardar |\n",
    "| Data lake | los archivos tal como llegaron, incluidos PDF y fotos | al leer, si es que alguna vez |\n",
    "| Lakehouse | archivos en el lago, pero consultables con SQL | al leer, con el catálogo puesto |"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "El lago tiene una fama que conviene conocer: se llena mucho más rápido de lo\n",
    "que se ordena, y cuando nadie documentó qué hay dentro se le empieza a llamar\n",
    "*pantano*. No es una broma del sector, es el resultado normal de guardar\n",
    "todo sin catálogo.\n",
    "\n",
    "Y el lakehouse es lo que hiciste en el capítulo\n",
    "19 cuando pusiste `FROM 'archivo.parquet'`:\n",
    "archivos sueltos que se consultan como tablas. La idea no es rara ni nueva, lo\n",
    "nuevo es el nombre."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## ETL y ELT, que es la misma sopa en otro orden"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**ETL**: extraer, transformar, cargar. Los datos se limpian por\n",
    "el camino y al almacén entra solo el resultado.\n",
    "\n",
    "**ELT**: extraer, cargar, transformar. Entra todo tal cual y la\n",
    "limpieza se hace ya dentro, con SQL.\n",
    "\n",
    "El ELT ganó terreno por una razón práctica: si guardas lo crudo, puedes\n",
    "volver a transformarlo cuando cambie la regla. Con ETL, el día que descubres que\n",
    "la regla de descuentos estaba mal, lo que se transformó mal ya no existe y toca\n",
    "volver a pedirle los datos al sistema, si es que todavía los tiene 🧾"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Las tres capas"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Vas a oírlas con muchos nombres, bronce y plata y oro entre ellos. Son tres:\n",
    "\n",
    "- **Cruda**: copia fiel de lo que llegó, sin arreglar nada, con\n",
    "la fecha en que se cargó. No se toca nunca.\n",
    "\n",
    "- **Limpia**: tipos arreglados, duplicados fuera, nulos\n",
    "decididos.\n",
    "\n",
    "- **Lista**: las tablas que se consultan, anchas y sin JOIN.\n",
    "\n",
    "Empecemos por la cruda, que es una línea:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "q(\"\"\"\n",
    "CREATE TABLE cruda_detalle AS\n",
    "SELECT *, '2026-08-21' AS cargado_en FROM detalle;\n",
    "\n",
    "SELECT count(*) AS filas, min(cargado_en) AS primera_carga FROM cruda_detalle;\n",
    "\"\"\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "La columna `cargado_en` parece un adorno y es media capa: es lo\n",
    "que te deja contestar \"esto entró el jueves\" cuando alguien pregunte por un\n",
    "número raro."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## La tabla de hechos, y el susto"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Ahora la capa lista. Una fila por línea de venta, con todo escrito al lado:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "q(\"\"\"\n",
    "CREATE TABLE hechos_ventas AS\n",
    "SELECT d.id         AS id_linea,\n",
    "       p.id         AS id_pedido,\n",
    "       p.fecha      AS fecha,\n",
    "       p.canal      AS canal,\n",
    "       c.ciudad     AS ciudad,\n",
    "       c.segmento   AS segmento,\n",
    "       pr.categoria AS categoria,\n",
    "       d.cantidad   AS unidades,\n",
    "       round(d.cantidad * d.precio_unit, 2) AS monto\n",
    "FROM cruda_detalle d\n",
    "JOIN pedidos   p  ON p.id  = d.id_pedido\n",
    "JOIN clientes  c  ON c.id  = p.id_cliente\n",
    "JOIN productos pr ON pr.id = d.id_producto;\n",
    "\n",
    "SELECT count(*) AS lineas, count(DISTINCT id_pedido) AS pedidos FROM hechos_ventas;\n",
    "\"\"\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Salió. Corrió sin un error, la tabla existe y las consultas contra ella\n",
    "funcionan.\n",
    "\n",
    "Y le faltan filas 😐"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "q(\"\"\"\n",
    "SELECT (SELECT count(*) FROM cruda_detalle) - (SELECT count(*) FROM hechos_ventas) AS lineas_perdidas,\n",
    "       round((SELECT sum(cantidad * precio_unit) FROM cruda_detalle)\n",
    "             - (SELECT sum(monto) FROM hechos_ventas), 2) AS soles_perdidos;\n",
    "\"\"\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Setenta y siete líneas y treinta y ocho mil soles que estaban en el origen y\n",
    "no están en el almacén. Nadie se enteró, porque un `JOIN` que no\n",
    "encuentra pareja **no falla: devuelve menos**.\n",
    "\n",
    "Son pedidos cuyo `id_cliente` no está en la tabla de clientes.\n",
    "Pasa en todas las bases de verdad: un cliente que se borró, una migración vieja,\n",
    "un pedido cargado a mano."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Arreglarlo, y el error de por medio"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Lo intuitivo es volver a crear la tabla. Prueba:"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**Esto revienta a propósito.** Se ejecuta dentro de un `try` para que puedas seguir con \"ejecutar todo\" y aun así ver la queja."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "try:\n",
    "    q(\"\"\"\n",
    "    CREATE TABLE hechos_ventas AS SELECT 1 AS ojo;\n",
    "    \"\"\")\n",
    "except Exception as e:\n",
    "    print(f'{type(e).__name__}: {e}')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Y la queja que tiene que salir es esta:\n",
    "\n",
    "```\n",
    "OperationalError: table hechos_ventas already exists\n",
    "```"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Por eso las cargas empiezan siempre con un `DROP TABLE IF EXISTS`:\n",
    "la carga de mañana tiene que poder correr aunque la de hoy haya dejado la tabla\n",
    "puesta.\n",
    "\n",
    "Y el arreglo de fondo es cambiar el JOIN por un `LEFT JOIN` con la\n",
    "etiqueta puesta, que es la técnica del capítulo 7 usada acá para no\n",
    "perder plata:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "q(\"\"\"\n",
    "DROP TABLE IF EXISTS hechos_ventas;\n",
    "\n",
    "CREATE TABLE hechos_ventas AS\n",
    "SELECT d.id    AS id_linea,\n",
    "       p.id    AS id_pedido,\n",
    "       p.fecha AS fecha,\n",
    "       p.canal AS canal,\n",
    "       coalesce(c.ciudad,   'sin cliente') AS ciudad,\n",
    "       coalesce(c.segmento, 'sin cliente') AS segmento,\n",
    "       pr.categoria AS categoria,\n",
    "       d.cantidad   AS unidades,\n",
    "       round(d.cantidad * d.precio_unit, 2) AS monto\n",
    "FROM cruda_detalle d\n",
    "JOIN pedidos   p  ON p.id  = d.id_pedido\n",
    "LEFT JOIN clientes c ON c.id = p.id_cliente\n",
    "JOIN productos pr ON pr.id = d.id_producto;\n",
    "\n",
    "SELECT (SELECT count(*) FROM cruda_detalle) - (SELECT count(*) FROM hechos_ventas) AS lineas_perdidas,\n",
    "       round((SELECT sum(cantidad * precio_unit) FROM cruda_detalle)\n",
    "             - (SELECT sum(monto) FROM hechos_ventas), 2) AS soles_perdidos;\n",
    "\"\"\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Cero y cero. Y lo que antes desaparecía ahora se puede mirar de frente:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "q(\"\"\"\n",
    "SELECT ciudad, count(*) AS lineas, round(sum(monto), 2) AS venta\n",
    "FROM hechos_ventas\n",
    "GROUP BY ciudad\n",
    "ORDER BY venta\n",
    "LIMIT 3;\n",
    "\"\"\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Ahí está el agujero, con nombre y con monto. Ahora alguien puede decidir qué\n",
    "hacer con él, que es lo único que no se podía cuando el JOIN se lo comía en\n",
    "silencio 💛"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Y la consulta del principio, otra vez"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "q(\"\"\"\n",
    "SELECT ciudad, canal, round(sum(monto), 2) AS venta\n",
    "FROM hechos_ventas\n",
    "GROUP BY ciudad, canal\n",
    "ORDER BY venta DESC\n",
    "LIMIT 5;\n",
    "\"\"\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "El mismo resultado, cero JOIN. Eso es todo lo que hace un almacén, y por eso\n",
    "merece la pena copiar los datos."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Crear una tabla a partir de una consulta\n",
    "\n",
    "| PostgreSQL, MySQL y SQLite | `CREATE TABLE hechos AS SELECT ...` |\n",
    "|---|---|\n",
    "| SQL Server | `SELECT ... INTO hechos FROM ...` |\n",
    "\n",
    "PostgreSQL admite ademas CREATE TABLE ... AS con UNLOGGED para tablas intermedias que no hace falta recuperar si se cae el servidor, que es justo el caso de una capa cruda. SQL Server pone el destino en medio de la consulta, con INTO, y confunde la primera vez."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Comprueba que lo tienes\n",
    "\n",
    "Estás armando la tabla de ventas del almacén y sale de cruzar cuatro tablas con JOIN. ¿Qué es lo primero que hay que comprobar?\n",
    "\n",
    "a) Que no se hayan perdido filas por el camino, comparando el total contra el origen\n",
    "\n",
    "b) Que la consulta sea rápida\n",
    "\n",
    "c) Que los nombres de las columnas sean claros\n",
    "\n",
    "d) Que la tabla tenga un índice"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Ejercicios"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 1. El cuadre, que es la consulta más importante del capítulo\n",
    "\n",
    "Comprueba mes a mes que la tabla de hechos dice lo mismo\n",
    "que el origen."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%%revisa 1\n",
    "-- tu turno"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 2. La pregunta que antes no se podía hacer\n",
    "\n",
    "Venta por segmento y categoría, que en el modelo original\n",
    "pedía cuatro tablas."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%%revisa 2\n",
    "-- tu turno"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 3. El precio de hoy no es el precio al que se vendió\n",
    "\n",
    "Primero comprueba cómo están las cosas."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# tu turno"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 4. El cliente que se mudó\n",
    "\n",
    "Las bodegas de Chiclayo pasan a atenderse desde Cusco.\n",
    "Actualiza el maestro y mira qué dice la tabla de hechos."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%%revisa 4\n",
    "-- tu turno"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 5. Por qué la capa cruda no se toca\n",
    "\n",
    "Simula el desastre: el origen se vacía."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%%revisa 5\n",
    "-- tu turno"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 6. Dónde va cada cosa\n",
    "\n",
    "Cuatro pedidos que llegan un lunes. ¿Capa cruda, limpia o\n",
    "lista?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# tu turno"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "---\n",
    "\n",
    "Ese era el capítulo 15 de **SQL desde cero**. El texto completo, con las salidas de cada bloque, está en https://missyera.com/guias/sql-desde-cero/warehouse-lake-y-capas/\n",
    "\n",
    "Que tengas lindo día! 🌸"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "name": "python",
   "version": "3.11"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}