Módulo 3: Star Vs Snowflake Vs One Big Table
Normalizando una dimensión: el snowflake schema
Descripción
dim_product, tal como la dejó el módulo 2, tiene una columna category de texto: "beverages" para P001 y P003, "snacks" para P002, "electronics" para P004. Esa columna funciona perfectamente para consultar —cualquier JOIN contra dim_product te da la categoría en el mismo paso—, pero tiene una propiedad que vale la pena hacer explícita: el texto "beverages" está repetido, una vez por cada producto que pertenece a esa categoría. Esta lección saca esa columna a su propia tabla —dim_category, con category_id y category_name— y construye, por primera vez en esta guía, un snowflake schema real: una dimensión que apunta a otra dimensión, en vez de tener todos sus atributos como columnas propias.
Conexión con el módulo. Esta es la primera pieza ejecutable del módulo: construye la mitad "más normalizada" de la comparación de tres formas que el resto del módulo desarrolla. La lección 3 va a medir, con EXPLAIN, el costo exacto de la tabla que construyes aquí.
Una analogía: la caja etiquetada dentro del clóset
Retoma el clóset ordenado del módulo 2, pero ahora fíjate en un detalle que esa lección no exploró: dentro del clóster, cada prenda tenía una etiqueta de tela cosida con su tipo —"ropa de verano", "ropa de invierno"—. Si compraste diez camisas de verano, las diez etiquetas dicen, literalmente, la misma palabra, cosida diez veces por separado. Funciona, pero es redundante: si algún día decides llamarle "ropa liviana" en vez de "ropa de verano", tienes que descoser y volver a coser diez etiquetas, una por una.
La alternativa que esta lección construye es la caja etiquetada: en vez de coser la palabra "verano" en cada camisa, guardas todas las camisas de verano dentro de una caja que dice, una sola vez, "ropa de verano" —o, en el vocabulario que vas a usar de aquí en adelante, le asignas un número de caja, y en un índice aparte escribes qué significa cada número—. Cada camisa ahora solo necesita saber "estoy en la caja 3"; el significado de "caja 3" vive en un solo lugar, el índice. Eso es exactamente lo que dim_category va a ser: el índice de cajas, con un número (category_id) y su significado (category_name), separado por completo de los productos que pertenecen a cada categoría.
Ejemplo trabajado: dim_category y dim_product_normalized
Parte de dim_product_natural tal como la dejó el módulo 1 —con category como columna de texto, sin ninguna llave sustituta todavía— y construye las dos tablas nuevas: dim_category primero, dim_product_normalized después, usando exactamente el mismo patrón de ROW_NUMBER() OVER (ORDER BY ...) que ya usaste en la lección 3 del módulo 2 para store_key y product_key.
# normalize_category.py
import duckdb
from kiosko import DIM_PRODUCT
con = duckdb.connect()
con.execute("CREATE TABLE dim_product_natural (product_id VARCHAR, product_name VARCHAR, category VARCHAR, unit_cost DOUBLE)")
con.executemany(
"INSERT INTO dim_product_natural VALUES (?, ?, ?, ?)",
[(p["product_id"], p["product_name"], p["category"], p["unit_cost"]) for p in DIM_PRODUCT],
)
print("=== dim_product_natural: category como columna de texto (heredado del modulo 2) ===")
print(con.sql("SELECT * FROM dim_product_natural ORDER BY product_id"))
print("=== dim_category: las categorias distintas, normalizadas en su propia tabla ===")
con.execute("""
CREATE TABLE dim_category AS
SELECT
ROW_NUMBER() OVER (ORDER BY category) AS category_id,
category AS category_name
FROM (SELECT DISTINCT category FROM dim_product_natural) t
""")
print(con.sql("SELECT * FROM dim_category ORDER BY category_id"))
print("=== dim_product_normalized: category_id reemplaza a category texto ===")
con.execute("""
CREATE TABLE dim_product_normalized AS
SELECT
ROW_NUMBER() OVER (ORDER BY n.product_id) AS product_key,
n.product_id,
n.product_name,
c.category_id,
n.unit_cost
FROM dim_product_natural n
JOIN dim_category c ON n.category = c.category_name
""")
print(con.sql("SELECT * FROM dim_product_normalized ORDER BY product_id"))
print("=== Verificacion 1: dim_product_normalized no perdio ni duplico productos ===")
before = con.sql("SELECT COUNT(*) FROM dim_product_natural").fetchone()[0]
after = con.sql("SELECT COUNT(*) FROM dim_product_normalized").fetchone()[0]
print(f"dim_product_natural: {before} filas -> dim_product_normalized: {after} filas")
assert before == after, "la normalizacion perdio o duplico productos"
print("\n=== Verificacion 2: dim_category tiene exactamente las categorias distintas esperadas ===")
n_categories = con.sql("SELECT COUNT(*) FROM dim_category").fetchone()[0]
print(f"categorias distintas normalizadas: {n_categories}")
print("\n=== Verificacion 3: el JOIN de vuelta reconstruye exactamente el texto original ===")
print(con.sql("""
SELECT p.product_id, p.product_name, c.category_name, p.unit_cost
FROM dim_product_normalized p
JOIN dim_category c ON p.category_id = c.category_id
ORDER BY p.product_id
"""))Qué esperar. Al correr python3 normalize_category.py, la salida es exactamente esta:
=== dim_product_natural: category como columna de texto (heredado del modulo 2) ===
┌────────────┬───────────────────────┬─────────────┬───────────┐
│ product_id │ product_name │ category │ unit_cost │
│ varchar │ varchar │ varchar │ double │
├────────────┼───────────────────────┼─────────────┼───────────┤
│ P001 │ Bottled Water 600ml │ beverages │ 0.4 │
│ P002 │ Energy Bar │ snacks │ 0.6 │
│ P003 │ Instant Coffee Sachet │ beverages │ 0.35 │
│ P004 │ Phone Charger Cable │ electronics │ 2.1 │
└────────────┴───────────────────────┴─────────────┴───────────┘
=== dim_category: las categorias distintas, normalizadas en su propia tabla ===
┌─────────────┬───────────────┐
│ category_id │ category_name │
│ int64 │ varchar │
├─────────────┼───────────────┤
│ 1 │ beverages │
│ 2 │ electronics │
│ 3 │ snacks │
└─────────────┴───────────────┘
=== dim_product_normalized: category_id reemplaza a category texto ===
┌─────────────┬────────────┬───────────────────────┬─────────────┬───────────┐
│ product_key │ product_id │ product_name │ category_id │ unit_cost │
│ int64 │ varchar │ varchar │ int64 │ double │
├─────────────┼────────────┼───────────────────────┼─────────────┼───────────┤
│ 1 │ P001 │ Bottled Water 600ml │ 1 │ 0.4 │
│ 2 │ P002 │ Energy Bar │ 3 │ 0.6 │
│ 3 │ P003 │ Instant Coffee Sachet │ 1 │ 0.35 │
│ 4 │ P004 │ Phone Charger Cable │ 2 │ 2.1 │
└─────────────┴────────────┴───────────────────────┴─────────────┴───────────┘
=== Verificacion 1: dim_product_normalized no perdio ni duplico productos ===
dim_product_natural: 4 filas -> dim_product_normalized: 4 filas
=== Verificacion 2: dim_category tiene exactamente las categorias distintas esperadas ===
categorias distintas normalizadas: 3
=== Verificacion 3: el JOIN de vuelta reconstruye exactamente el texto original ===
┌────────────┬───────────────────────┬───────────────┬───────────┐
│ product_id │ product_name │ category_name │ unit_cost │
│ varchar │ varchar │ varchar │ double │
├────────────┼───────────────────────┼───────────────┼───────────┤
│ P001 │ Bottled Water 600ml │ beverages │ 0.4 │
│ P002 │ Energy Bar │ snacks │ 0.6 │
│ P003 │ Instant Coffee Sachet │ beverages │ 0.35 │
│ P004 │ Phone Charger Cable │ electronics │ 2.1 │
└────────────┴───────────────────────┴───────────────┴───────────┘
Cuatro productos entraron, cuatro productos salieron —dim_product_normalized no perdió ni duplicó ninguno—. Y algo igual de importante: solo aparecieron tres categorías distintas (beverages, electronics, snacks) en dim_category, aunque dim_product_natural tuviera cuatro filas con la palabra category repetida — beverages aparecía dos veces (en P001 y P003), pero dim_category la guarda una sola vez, con category_id = 1. La Verificación 3 confirma que esta normalización no perdió información: unir dim_product_normalized de vuelta contra dim_category reconstruye, palabra por palabra, el mismo texto que tenías en dim_product_natural desde el principio.
Fíjate en el nombre elegido para la tabla nueva: dim_product_normalized, no dim_product. Esto es deliberado. dim_product —la tabla original, con category como columna de texto, construida en el módulo 2— sigue siendo la tabla canónica que el resto de esta guía va a usar de aquí en adelante (el módulo 4, por ejemplo, historiza dim_product, no esta versión normalizada). dim_product_normalized es una estructura paralela, construida específicamente para esta comparación de tres formas, que no reemplaza a la original.
Diagrama: de una columna de texto a dos tablas unidas por llave
flowchart TD
subgraph Antes["dim_product (star, modulo 2)"]
A["product_id | product_name | category | unit_cost\nP001 | Bottled Water | beverages | 0.40\nP003 | Instant Coffee | beverages | 0.35\n(\"beverages\" repetido 2 veces, como texto)"]
end
subgraph Despues["snowflake (esta leccion)"]
B["dim_product_normalized\nproduct_id | ... | category_id | unit_cost\nP001 | ... | 1 | 0.40\nP003 | ... | 1 | 0.35"]
C["dim_category\ncategory_id | category_name\n1 | beverages"]
B -->|"JOIN ON category_id"| C
end
Antes -->|"normalizar"| Despues
Profundización: por qué ROW_NUMBER() OVER (ORDER BY category) y no un índice arbitrario
Fíjate en un detalle que vale la pena entender con precisión: category_id se genera con ROW_NUMBER() OVER (ORDER BY category), exactamente el mismo patrón que ya usaste para store_key y product_key en el módulo 2. El ORDER BY category dentro de la función de ventana no es decorativo — determina en qué orden ROW_NUMBER() asigna los números, y ese orden es lo que hace que category_id = 1 corresponda siempre a "beverages" (la primera categoría en orden alfabético), category_id = 2 a "electronics", y category_id = 3 a "snacks". Sin ese ORDER BY, DuckDB podría asignar los números en cualquier orden interno, y aunque el resultado seguiría siendo válido —cada categoría seguiría teniendo un category_id único—, dejaría de ser determinista: correr el mismo script dos veces podría, en teoría, producir asignaciones distintas.
Esta es la misma disciplina de reproducibilidad que esta guía exige en cada bloque ejecutable —nada de random, nada que dependa del reloj del sistema—, ahora aplicada a la generación de llaves sustitutas. ORDER BY category convierte una operación que podría ser no determinista (el orden interno en el que un motor recorre las filas de una subconsulta) en una operación completamente determinista: el mismo dato de entrada siempre produce, byte a byte, la misma asignación de category_id.
Hay una segunda observación, igual de importante, sobre la subconsulta (SELECT DISTINCT category FROM dim_product_natural) t. El DISTINCT es lo que hace que dim_category tenga tres filas y no cuatro —sin él, ROW_NUMBER() OVER (ORDER BY category) numeraría las cuatro filas de dim_product_natural una por una, y "beverages" terminaría con dos category_id distintos (uno para P001, otro para P003), rompiendo exactamente la propiedad que esta lección busca: que cada categoría exista una sola vez en dim_category.
Errores comunes
Olvidar el DISTINCT y terminar con categorías duplicadas. Qué pasa: alguien escribe ROW_NUMBER() OVER (ORDER BY category) AS category_id, category AS category_name FROM dim_product_natural, sin la subconsulta DISTINCT, y dim_category termina con cuatro filas —una por producto— en vez de tres. Por qué pasa: la sintaxis sin DISTINCT compila y corre sin ningún error; el problema es puramente lógico, no sintáctico, así que no hay ningún mensaje de advertencia que lo señale. Cómo detectarlo: si SELECT COUNT(*) FROM dim_category te da el mismo número que SELECT COUNT(*) FROM dim_product_natural, en vez de el número de categorías realmente distintas, tienes este error — la Verificación 2 de esta lección existe exactamente para atraparlo. Cómo corregirlo: siempre construye dim_category a partir de una subconsulta con DISTINCT sobre la columna que estás normalizando, nunca directamente sobre la tabla completa.
Comparar category (texto) contra category_id (entero) directamente, sin pasar por dim_category. Qué pasa: alguien, después de construir dim_product_normalized, intenta filtrar productos de la categoría "beverages" escribiendo WHERE category_id = 'beverages', esperando que el motor "entienda" la comparación. Por qué pasa: antes de esta lección, category era una columna de texto, y filtrar por su valor era tan simple como escribir el texto directamente; es fácil olvidar que, después de normalizar, ese texto ya no vive en dim_product_normalized. Cómo detectarlo: DuckDB va a lanzar un error de conversión de tipos —category_id es INTEGER, no puede compararse contra un literal de texto sin conversión—, o en el peor caso (si el motor permite la conversión implícita) el filtro simplemente no va a encontrar ninguna fila. Cómo corregirlo: para filtrar por nombre de categoría después de normalizar, siempre necesitas el JOIN contra dim_category primero —JOIN dim_category c ON p.category_id = c.category_id WHERE c.category_name = 'beverages'—, exactamente el costo adicional que la lección 3 va a medir con EXPLAIN.
Asumir que dim_product_normalized reemplaza a dim_product en el resto de la guía. Qué pasa: alguien, satisfecho con la versión normalizada recién construida, empieza a usar dim_product_normalized en vez de dim_product para consultas futuras, o espera que el módulo 4 (SCD) historice esta versión. Por qué pasa: dim_product_normalized es, en un sentido real, "más correcta" desde el punto de vista de normalización de base de datos — es fácil asumir que "más correcta" significa "la que se usa de aquí en adelante". Cómo detectarlo: si en algún ejercicio de un módulo posterior escribes JOIN dim_product_normalized en vez de JOIN dim_product esperando el mismo comportamiento, mezclaste las dos versiones. Cómo corregirlo: dim_product —con category como columna de texto— sigue siendo la dimensión canónica de esta guía a partir del módulo 4. dim_product_normalized y dim_category existen únicamente dentro de este módulo, como la mitad "normalizada" de una comparación de tres formas.
Ejercicios
Ejercicio 1 — Verifica que cada producto normalizado apunta a exactamente una categoría. Usando dim_product_normalized, escribe una consulta que confirme que ningún product_id tiene más de un category_id — una verificación de integridad que debería ser trivialmente cierta dado cómo se construyó la tabla, pero vale la pena confirmarla con evidencia, no con intuición.
Ver solución
print(con.sql("""
SELECT product_id, COUNT(DISTINCT category_id) AS distinct_categories
FROM dim_product_normalized
GROUP BY product_id
HAVING COUNT(DISTINCT category_id) > 1
"""))Salida esperada:
┌────────────┬──────────────────────┐
│ product_id │ distinct_categories │
│ varchar │ int64 │
├────────────┼──────────────────────┤
└────────────┴──────────────────────┘
0 rows
Cero filas — ningún producto normalizado apunta a más de una categoría, confirmando que la normalización de esta lección preserva la propiedad más básica de una dimensión bien formada: cada fila de dim_product_normalized tiene un único category_id, sin ambigüedad.
Ejercicio 2 — Cuenta cuántos productos tiene cada categoría, a través de la versión normalizada. Usando dim_product_normalized unida a dim_category, escribe una consulta que agrupe por category_name y cuente cuántos productos pertenecen a cada categoría.
Ver solución
print(con.sql("""
SELECT c.category_name, COUNT(*) AS product_count
FROM dim_product_normalized p
JOIN dim_category c ON p.category_id = c.category_id
GROUP BY c.category_name
ORDER BY c.category_name
"""))Salida esperada:
┌───────────────┬───────────────┐
│ category_name │ product_count │
│ varchar │ int64 │
├───────────────┼───────────────┤
│ beverages │ 2 │
│ electronics │ 1 │
│ snacks │ 1 │
└───────────────┴───────────────┘
beverages tiene dos productos (P001 y P003), mientras que electronics y snacks tienen uno cada una (P004 y P002). Esta consulta necesita el JOIN contra dim_category porque dim_product_normalized, por sí sola, solo conoce el category_id numérico — no el nombre legible de la categoría. Esto es, exactamente, el primer costo tangible de haber normalizado: cualquier pregunta que necesite el nombre de la categoría, no solo su identificador, requiere un salto adicional.
Ejercicio 3 — Explica, sin código, qué pasaría si dim_category tuviera un valor de category_name duplicado. Imagina que, por un error de construcción, dim_category terminara con dos filas distintas —category_id = 1 y category_id = 4— ambas con category_name = 'beverages'. En 2-3 frases, explica qué problema causaría esto al hacer JOIN dim_product_normalized p JOIN dim_category c ON p.category_id = c.category_id, y por qué el DISTINCT de esta lección existe precisamente para prevenirlo.
Ver solución
El JOIN en sí no fallaría —cada product_id normalizado sigue apuntando a un único category_id válido—, así que técnicamente el resultado seguiría siendo correcto fila por fila. El problema real aparecería en cualquier consulta que agrupara o contara por category_name: los productos de category_id = 1 y los de un hipotético category_id = 4, ambos con el mismo nombre "beverages", aparecerían como dos grupos separados en un GROUP BY category_name, en vez de sumarse juntos como una sola categoría — inflando artificialmente el número de categorías distintas que reporta cualquier análisis. El DISTINCT de esta lección, al construir dim_category a partir de los valores únicos de category, garantiza que esta situación nunca ocurra: cada nombre de categoría existe con un único category_id, sin duplicados que puedan fragmentar un análisis posterior.
Resumen y siguiente paso
En esta lección normalizaste category fuera de dim_product, construyendo tu primer snowflake schema real: dim_category (tres filas, category_id/category_name) y dim_product_normalized (cuatro filas, con category_id reemplazando al texto original). Verificaste, con evidencia ejecutada, que la normalización no perdió ningún producto y que el JOIN de vuelta reconstruye exactamente el mismo texto que tenías antes.
Antes de avanzar deberías poder: explicar de memoria por qué dim_category tiene tres filas y no cuatro; escribir el patrón ROW_NUMBER() OVER (ORDER BY ...) sobre una subconsulta DISTINCT sin mirar el ejemplo; y nombrar la diferencia entre dim_product (canónica, category como texto) y dim_product_normalized (paralela, solo para esta comparación).
La lección 3 toma esta tabla recién normalizada y mide, con EXPLAIN, cuánto cuesta realmente el salto adicional de JOIN que acabas de introducir — no en teoría, sino en el plan de ejecución real que DuckDB genera para cada consulta.
Recursos
- Kimball Group — "Star Schema / OLAP Cube" — la fuente que define formalmente el snowflake schema como la normalización de una o más dimensiones de un star schema. kimballgroup.com/data-warehouse-business-intelligence-resources/kimball-techniques/dimensional-modeling-techniques/star-schema-olap-cube. En inglés.
- DuckDB — documentación de funciones de ventana (
ROW_NUMBER), el mismo patrón que ya usaste en el módulo 2 para llaves sustitutas, aplicado aquí acategory_id. duckdb.org/docs/current/sql/functions/window_functions. En inglés. - DuckDB — documentación oficial de
SELECT DISTINCT, la cláusula que garantiza quedim_categorytenga una fila por categoría, no una fila por producto. duckdb.org/docs/current/sql/query_syntax/select. En inglés. - Microsoft Learn — "Understand star schema and the importance for Power BI" — incluye una sección sobre por qué Power BI recomienda evitar el snowflaking salvo casos específicos, útil como contraste práctico a esta lección. learn.microsoft.com/en-us/power-bi/guidance/star-schema. En inglés.