Star Schema Data Warehouse in DuckDB

Model a sales data warehouse using Kimball dimensional modeling in Python.

Introduction

30 Second Summary

Imagine pulling up a sales report and seeing that revenue looks 30% higher than it actually is. This happens all the time when businesses store their data in one giant spreadsheet where customer and product details repeat on every single row.

In this project, you will build a star schema data warehouse using Python and DuckDB. Starting from a flat CSV of sales data, you will experience the fan-out problem firsthand and then fix it by transforming the data into a dimensional model following the Kimball methodology.

What You'll Build

You will run the same business questions against a flat table and your finished star schema side by side, watching the star schema return correct numbers where the flat table silently inflated them.

By the end of this project, you'll have:

  • A data generator you can re-run to produce fresh synthetic sales data, simulating a real online store's order history.
  • Three dimension tables (dim_producto, dim_cliente, dim_fecha) with surrogate keys, where each customer, product, and date exists exactly once instead of being duplicated across hundreds of rows.
  • A fact table (fact_ventas) at the center of your star schema, connecting all dimensions so you can slice revenue by category, segment, city, or day of the week with a single JOIN.
  • Side-by-side proof that the same business questions return correct numbers on your star schema and inflated numbers on the flat table.
  • Secret Mission: Implement SCD Type 2 on the product dimension to track historical price changes with versioned records.

Are there any prerequisites?

This project assumes intermediate SQL knowledge (JOINs, GROUP BY, CTEs) and intermediate Python experience. You should also have Python already installed on your machine.

Before We Start

Before we write any code, let's lock in what you're about to build and why it matters.

Prepara tu Entorno y Genera Datos de Ventas

Ya entiendes el problema: las tablas planas y denormalizadas producen numeros inflados cuando intentas hacer analisis. Ahora necesitas las herramientas y los datos para construir la solucion.

En este paso vas a instalar DuckDB como motor de base de datos, generar datos de ventas sinteticos con Python, y cargarlos en una tabla de DuckDB. Esto te dara la materia prima para el modelado dimensional.

En este paso, preparate para:
  • Instalar DuckDB y crear la carpeta del proyecto.
  • Crear un script Python que genera datos de ventas sinteticos en CSV.
  • Cargar el CSV en DuckDB y explorar la estructura de la tabla.
Instalar DuckDB y crear la estructura del proyecto

DuckDB es una base de datos analitica embebida. Se instala con un solo comando de pip y no requiere configurar ningun servidor.

  • Presiona Cmd+Espacio (macOS) o la tecla Windows (Windows) para abrir la barra de busqueda.
  • Escribe Terminal (macOS) o PowerShell (Windows) y presiona Enter para abrirla.
  • Muevete al Escritorio ejecutando este comando:
cd ~/Desktop
  • Crea la carpeta del proyecto y entra en ella ejecutando estos comandos:
mkdir star-schema-project
cd star-schema-project
  • Confirma que la carpeta existe ejecutando:
ls

La carpeta esta vacia por ahora. Tu terminal deberia mostrar la carpeta star-schema-project como el directorio actual.

  • Instala DuckDB ejecutando este comando:
pip install duckdb==1.5.5

Veras mensajes de descarga e instalacion en la terminal. Cuando termine, deberia decir Successfully installed seguido de la version.

Error al instalar con pip?

En Windows, si ves un error que menciona DLL load failed, necesitas instalar el Microsoft Visual C++ Redistributable desde el sitio oficial de Microsoft.

Si pip no se reconoce como comando, intenta con pip3 o python -m pip en su lugar.

Necesito ayuda instalando DuckDB con pip

  • Verifica que DuckDB se instalo correctamente ejecutando:
python -c "import duckdb; print(duckdb.__version__)"

✔️ Veo 1.5.5

DuckDB 1.5.5 esta listo. Continuemos con la generacion de datos.

ⓧ Veo otra version

Si ves un numero de version diferente a 1.5.5, es posible que tengas otra version de DuckDB instalada previamente. Fuerza la instalacion de la version correcta ejecutando:

pip install duckdb==1.5.5 --force-reinstall

Si estas usando un entorno virtual, asegurate de haberlo activado antes de ejecutar el comando.

ⓧ Veo un error

Si ves ModuleNotFoundError: No module named 'duckdb', la instalacion no se completo. Intenta ejecutar:

pip3 install duckdb==1.5.5

Si el problema persiste, verifica que pip esta apuntando a la misma instalacion de Python que usas. Ejecuta python --version para confirmar tu version de Python.

Por que DuckDB en lugar de PostgreSQL?

DuckDB es una base de datos columnar optimizada para analisis (OLAP). Se instala con un solo comando y no necesita ningun servidor corriendo en segundo plano.

PostgreSQL requeriria instalar y configurar un servidor, lo cual agrega complejidad sin aportar al aprendizaje de modelado dimensional. Con DuckDB, todo tu tiempo va directo a construir el esquema estrella.

Crear el script generador de datos sinteticos

Necesitas datos de ventas realistas para modelar. Vas a crear un script Python que genera un CSV plano con datos denormalizados de una tienda en linea ficticia.

  • Presiona Cmd+Espacio (macOS) o la tecla Windows (Windows) para abrir la barra de busqueda.
  • Escribe VS Code (o Cursor si lo prefieres) y presiona Enter para abrirlo.
  • Haz clic en File en la barra de menu superior.
  • Selecciona Open Folder y abre la carpeta star-schema-project en tu Escritorio.
  • Crea un archivo nuevo llamado generate_data.py haciendo clic en el icono de nuevo archivo en el panel Explorer de la izquierda.

Este script define los productos, clientes y fechas que componen los datos de tu tienda ficticia. Vas a construirlo en tres partes.

  • En generate_data.py, copia el siguiente codigo que define los catalogos de productos y clientes:
import csv
import random
from datetime import date, timedelta

random.seed(42)

products = [
    ("P001", "Laptop Pro 15", "Electronica", 1299.99),
    ("P002", "Mouse Inalambrico", "Electronica", 29.99),
    ("P003", "Teclado Mecanico", "Electronica", 89.99),
    ("P004", "Monitor 27 4K", "Electronica", 449.99),
    ("P005", "Silla Ergonomica", "Mobiliario", 349.99),
    ("P006", "Escritorio Ajustable", "Mobiliario", 599.99),
    ("P007", "Lampara LED", "Mobiliario", 45.99),
    ("P008", "Webcam HD", "Accesorios", 79.99),
    ("P009", "Audifonos Bluetooth", "Accesorios", 159.99),
    ("P010", "Hub USB-C", "Accesorios", 39.99),
]

customers = [
    ("C001", "Ana Garcia", "Madrid", "Premium"),
    ("C002", "Carlos Lopez", "Barcelona", "Premium"),
    ("C003", "Maria Rodriguez", "Valencia", "Estandar"),
    ("C004", "Pedro Martinez", "Sevilla", "Estandar"),
    ("C005", "Laura Fernandez", "Madrid", "Premium"),
    ("C006", "Diego Sanchez", "Barcelona", "Estandar"),
    ("C007", "Sofia Ramirez", "Valencia", "Estandar"),
    ("C008", "Andres Torres", "Sevilla", "Premium"),
]

Que hace este codigo?

  • random.seed(42) fija la semilla aleatoria para que el CSV generado sea identico cada vez que ejecutes el script.
  • products contiene 10 productos de tres categorias (Electronica, Mobiliario, Accesorios) con sus precios.
  • customers define 8 clientes de 4 ciudades, divididos entre segmentos Premium y Estandar.
  • Guarda generate_data.py.
  • Verifica que no hay errores de sintaxis ejecutando este comando en tu terminal:
python generate_data.py

El comando deberia completarse sin imprimir nada y regresar al prompt de tu terminal. Si no ves ningun error, tus importaciones y datos estan correctos.

  • Regresa a generate_data.py en tu editor y agrega el siguiente codigo debajo de la lista customers. Este bloque configura el rango de fechas y genera las filas de ventas:
start_date = date(2024, 1, 1)
end_date = date(2024, 6, 30)
date_range = (end_date - start_date).days

rows = []
order_id = 1000

for _ in range(80):
    order_id += 1
    order_date = start_date + timedelta(days=random.randint(0, date_range))
    customer = random.choice(customers)
    num_items = random.randint(1, 4)
    for _ in range(num_items):
        product = random.choice(products)
        quantity = random.randint(1, 5)
        total_amount = round(product[3] * quantity, 2)
        rows.append([
            order_id,
            order_date.isoformat(),
            customer[0],
            customer[1],
            customer[2],
            customer[3],
            product[0],
            product[1],
            product[2],
            product[3],
            quantity,
            total_amount,
        ])

Que hace este codigo?

  • El bucle exterior crea 80 ordenes de compra, cada una con una fecha aleatoria entre enero y junio de 2024.
  • El bucle interior genera entre 1 y 4 lineas de pedido por orden, eligiendo un producto y cantidad aleatorios.
  • Cada fila de rows repite todos los datos del cliente y producto. Esta denormalizacion intencional es lo que causa problemas en el analisis.
  • Agrega el ultimo bloque debajo del bucle. Este codigo escribe todas las filas a un archivo CSV:
with open("ventas_raw.csv", "w", newline="", encoding="utf-8") as f:
    writer = csv.writer(f)
    writer.writerow([
        "order_id", "order_date", "customer_id", "customer_name",
        "customer_city", "customer_segment", "product_id", "product_name",
        "product_category", "product_price", "quantity", "total_amount",
    ])
    writer.writerows(rows)

print(f"Generadas {len(rows)} filas en ventas_raw.csv")

Que hace este codigo?

  • writer.writerow() escribe la fila de encabezados con los 12 nombres de columna.
  • writer.writerows(rows) escribe todas las filas de datos generadas por los bucles.
  • La linea print() al final confirma cuantas filas se generaron.
  • Guarda generate_data.py.

Antes de ejecutar el script, cuantas filas crees que generara? Recuerda que son 80 ordenes con 1 a 4 items cada una.

  • Ejecuta el script completo en tu terminal:
python generate_data.py

Deberias ver un mensaje como Generadas 196 filas en ventas_raw.csv (el numero exacto depende de la distribucion aleatoria, pero estara cerca de 200).

  • Confirma que el archivo CSV fue creado ejecutando:
ls

Deberias ver generate_data.py y ventas_raw.csv en la salida.

No ves ventas_raw.csv?

Asegurate de haber guardado generate_data.py antes de ejecutarlo. Los cambios sin guardar no se ejecutan.

Verifica que tu terminal esta dentro de la carpeta star-schema-project. Si no estas seguro, ejecuta pwd para ver tu directorio actual.

Mi script generate_data.py no genera el archivo CSV

✔️ Tengo todo listo

Perfecto. Verifica que guardaste generate_data.py y continuemos.

ⓧ Quiero revisar el codigo completo

El archivo generate_data.py se construyo en tres bloques. Desplazate hacia arriba y confirma que cada uno esta presente, en este orden:

  • Bloque 1: importaciones, random.seed(42), listas products y customers.
  • Bloque 2: variables de fecha, el bucle doble for que genera las filas.
  • Bloque 3: el bloque with open() que escribe el CSV y el print() final.

Por que los datos estan denormalizados?

Cada fila del CSV repite el nombre, ciudad y segmento del cliente junto con el nombre y precio del producto. Un cliente como "Ana Garcia" aparece en tantas filas como productos haya comprado.

Esta repeticion es exactamente lo que causa el fan-out problem que vas a experimentar en el siguiente paso.

Cargar los datos en DuckDB y explorarlos

El CSV ya existe, pero es solo un archivo de texto. Para hacer queries SQL sobre los datos, necesitas cargarlos en una tabla de DuckDB.

  • En tu editor, crea un archivo nuevo llamado warehouse.py dentro de star-schema-project.
  • Copia el siguiente codigo que conecta a DuckDB y carga el CSV en una tabla:
import duckdb

con = duckdb.connect("warehouse.duckdb")

# ============================================================
# STEP 1: Cargar datos crudos
# ============================================================
con.execute("DROP TABLE IF EXISTS raw_ventas")
con.execute("""
    CREATE TABLE raw_ventas AS
    SELECT * FROM read_csv('ventas_raw.csv')
""")

row_count = con.execute("SELECT count(*) FROM raw_ventas").fetchone()[0]
print(f"raw_ventas cargada: {row_count} filas")
con.execute("DESCRIBE raw_ventas").fetchall()

Que hace este codigo?

  • duckdb.connect("warehouse.duckdb") crea un archivo de base de datos persistente. Tus tablas se guardaran en este archivo.
  • read_csv('ventas_raw.csv') es una funcion de DuckDB que lee un CSV directamente en una query SQL, sin necesidad de codigo Python adicional.
  • DROP TABLE IF EXISTS antes del CREATE garantiza que puedas re-ejecutar el script sin errores de tabla duplicada.
  • Guarda warehouse.py.

Que resultado esperas ver? Piensa cuantas filas deberia tener raw_ventas basandote en lo que generate_data.py imprimio.

  • Ejecuta el script en tu terminal:
python warehouse.py

Deberias ver algo como raw_ventas cargada: 196 filas. El numero debe coincidir exactamente con lo que generate_data.py reporto antes.

Ves un error al ejecutar warehouse.py?

Si ves FileNotFoundError mencionando ventas_raw.csv, asegurate de que ambos archivos (warehouse.py y ventas_raw.csv) estan dentro de la carpeta star-schema-project y que tu terminal esta en esa misma carpeta.

Si ves ModuleNotFoundError: No module named 'duckdb', regresa al paso anterior y reinstala DuckDB con pip install duckdb==1.5.5.

No puedo cargar ventas_raw.csv en DuckDB

✔️ Tengo todo listo

Excelente. Verifica que guardaste warehouse.py y continuemos.

ⓧ Quiero revisar el codigo completo

Asi deberia verse tu archivo warehouse.py completo hasta este punto:

import duckdb

con = duckdb.connect("warehouse.duckdb")

# ============================================================
# STEP 1: Cargar datos crudos
# ============================================================
con.execute("DROP TABLE IF EXISTS raw_ventas")
con.execute("""
    CREATE TABLE raw_ventas AS
    SELECT * FROM read_csv('ventas_raw.csv')
""")

row_count = con.execute("SELECT count(*) FROM raw_ventas").fetchone()[0]
print(f"raw_ventas cargada: {row_count} filas")
con.execute("DESCRIBE raw_ventas").fetchall()

Tienes tus datos crudos cargados en DuckDB. 12 columnas con toda la informacion de clientes, productos y ventas mezclada en una sola tabla plana. En el siguiente paso, vas a intentar hacerle preguntas de negocio a esta tabla y descubrir por que esa estructura plana da respuestas equivocadas.

Intenta Responder Preguntas de Negocio con la Tabla Plana

In the previous step you loaded raw sales data into DuckDB and confirmed the raw_ventas table has your ~200 rows ready for analysis.

Before building anything new, you need to feel the problem that dimensional modeling solves. You'll run three business queries on the flat table and discover that some results are misleading.

In this step, get ready to:
  • Write three analytical queries against the flat table.
  • Identify where the flat table produces incorrect or misleading numbers due to the fan-out problem.
Write analytical queries on the flat table

Imagine your manager asks three business questions: how much revenue does each product category generate, how many unique customers belong to each segment, and what is the average order ticket per city. These are standard analytics questions that any data team would face.

  • In warehouse.py, find the last line that says con.execute("DESCRIBE raw_ventas").fetchall().
  • Add the first business query below it by copying this code:
# ============================================================
# STEP 2: Queries sobre la tabla plana (resultados incorrectos)
# ============================================================
print("\n=== RESULTADOS TABLA PLANA (potencialmente incorrecto) ===\n")

print("1. Ingreso total por categoria:")
flat_revenue = con.execute("""
    SELECT product_category, SUM(total_amount) AS total_revenue
    FROM raw_ventas
    GROUP BY product_category
    ORDER BY total_revenue DESC
""").fetchall()
for row in flat_revenue:
    print(f"   {row[0]}: ${row[1]:,.2f}")

What does this code do?

The header labels all output from this section as "potencialmente incorrecto" so you can compare it later against the star schema results.

The first query groups every row in raw_ventas by product_category and sums total_amount to show revenue per category.

  • Save warehouse.py.
  • Run the script to see the first query's output:
python warehouse.py

You should see the raw_ventas row count from Step 1, followed by a revenue breakdown by category (Electronica, Mobiliario, Accesorios).

Seeing a syntax error?

  • Check that the triple-quoted SQL string uses three double quotes (""") on both the opening and closing sides.
  • Make sure the new code sits after the DESCRIBE line, not inside it.
  • Help me debug a syntax error in warehouse.py
  • Add the second business query below the revenue code you just added:
print("\n2. Clientes unicos por segmento:")
flat_customers = con.execute("""
    SELECT customer_segment, COUNT(DISTINCT customer_id) AS num_customers
    FROM raw_ventas
    GROUP BY customer_segment
""").fetchall()
for row in flat_customers:
    print(f"   {row[0]}: {row[1]}")

What does this query do?

This counts unique customers per segment using COUNT(DISTINCT customer_id). The DISTINCT keyword deduplicates customers who appear on multiple order lines. Without it, the count would be wildly inflated.

  • Save warehouse.py and run it again:
python warehouse.py

You should now see the revenue breakdown followed by a customer count split between Premium and Estandar segments.

  • Add the third and final business query below the customer code:
print("\n3. Ticket promedio por ciudad:")
flat_ticket = con.execute("""
    SELECT customer_city,
           ROUND(SUM(total_amount) / COUNT(DISTINCT order_id), 2) AS avg_ticket
    FROM raw_ventas
    GROUP BY customer_city
    ORDER BY avg_ticket DESC
""").fetchall()
for row in flat_ticket:
    print(f"   {row[0]}: ${row[1]:,.2f}")

What does this query do?

This calculates the average order ticket per city by dividing total revenue by the number of distinct orders. It tries to answer "how much does a typical order cost in each city."

The problem is that total_amount lives at the line-item level while order_id lives at the order level. Mixing these two grains in one flat table makes the result fragile and hard to verify.

  • Save warehouse.py and run it one more time:
python warehouse.py

You should now see all three result sets printed under the "RESULTADOS TABLA PLANA" header.

Missing one of the three result sections?

  • Make sure each query block sits below the previous one, not inside it. Check that the for row in ... loop for each query is properly closed before the next print statement starts.
  • Verify that all three query blocks are at the same indentation level (no accidental nesting).
  • Help me figure out why only some of my queries are printing output

✔️ Awesome, I've got everything!

Great. Make sure you've saved warehouse.py with all three queries in place.

ⓧ I'd like to double check the full code

Scroll up and confirm each of these sections is present in warehouse.py, in this order:

  • The Step 1 code that loads ventas_raw.csv into raw_ventas and prints the row count.
  • The Step 2 header and the revenue-by-category query stored in flat_revenue.
  • The unique-customers-by-segment query stored in flat_customers.
  • The average-ticket-by-city query stored in flat_ticket.
Identify the fan-out problem

Before reading further, look at the three sets of results in your terminal. Do you think all three accurately answer the business questions?

The numbers look plausible, but the flat table is hiding a trap.

What is the fan-out problem?

In a flat, denormalized table every order line item repeats the customer's name, city, and segment. A customer who bought 4 items appears on 4 rows. If you count customers without DISTINCT, you get inflated numbers. Your query used COUNT(DISTINCT customer_id) to work around this, but that only works because you knew the trap was there.

The ticket promedio query has a subtler issue. It mixes two different grains in the same table: line-item amounts and order-level identifiers. The flat table forces you to juggle both grains manually with every query.

On a flat table, you are always one careless query away from wrong numbers. The star schema you'll build next eliminates these traps structurally.

What is grain?

The grain of a table defines what one row represents. In raw_ventas, one row is one line item within an order. But the same table also carries order-level data (order_id, order_date) and customer-level data (customer_name, customer_city). Mixing grains in one table is what makes flat-table analytics fragile.

Take note of the numbers your three queries produced. You will compare them side-by-side against the star schema results in Step 5 to see exactly where they diverge.

You've seen the trap firsthand. The flat table gives you numbers, but you can never fully trust them without double-checking every grain and every DISTINCT. Next up, you'll build three dimension tables that separate customer, product, and date attributes into their own clean structures.

Disena y Construye las Tablas de Dimensiones

In the previous step you ran three business queries on the flat table and saw the numbers come back inflated or misleading. The fan-out problem is real, and now you know what it feels like firsthand.

The fix is to separate the descriptive attributes (product names, customer cities, dates) into independent dimension tables, each with its own surrogate key. This eliminates duplication and gives the model clean structure.

In this step, get ready to:
  • Create the product dimension table with a surrogate key sequence.
  • Create the customer and date dimension tables with deduplicated data.
  • Verify that each dimension has the correct number of unique rows.
Create the product dimension table

A dimension table holds the descriptive attributes for one business entity (a product, a customer, a date). Each row gets a surrogate key, an auto-generated integer that replaces the original business ID for all joins.

  • In warehouse.py, scroll to the bottom of the file, after the last for row in flat_ticket: loop.
  • Add the following code to clean up any previous runs and create the product dimension:
# ============================================================
# STEP 3: Crear tablas de dimensiones
# ============================================================
con.execute("DROP TABLE IF EXISTS fact_ventas")
con.execute("DROP TABLE IF EXISTS dim_producto")
con.execute("DROP TABLE IF EXISTS dim_cliente")
con.execute("DROP TABLE IF EXISTS dim_fecha")
con.execute("DROP SEQUENCE IF EXISTS seq_producto")
con.execute("DROP SEQUENCE IF EXISTS seq_cliente")
con.execute("DROP SEQUENCE IF EXISTS seq_fecha")

# Dimension: Producto
con.execute("CREATE SEQUENCE seq_producto START 1")
con.execute("""
    CREATE TABLE dim_producto (
        producto_sk INTEGER PRIMARY KEY DEFAULT nextval('seq_producto'),
        product_id VARCHAR,
        product_name VARCHAR,
        product_category VARCHAR,
        product_price DECIMAL(10, 2)
    )
""")
con.execute("""
    INSERT INTO dim_producto (product_id, product_name, product_category, product_price)
    SELECT DISTINCT product_id, product_name, product_category, product_price
    FROM raw_ventas
    ORDER BY product_id
""")

What does this code do?

The seven DROP statements clean up tables and sequences from previous runs so you can rerun the script safely.

CREATE SEQUENCE seq_producto START 1 creates a counter that auto-generates integers starting at 1. The producto_sk column uses nextval('seq_producto') to pull the next number from that counter every time a row is inserted.

The INSERT ... SELECT DISTINCT pulls unique product combinations from raw_ventas. A product that appeared in 20 order lines becomes a single row in the dimension.

  • Save warehouse.py.
  • Run the script to confirm it executes without errors:
python warehouse.py

You should see the same Step 1 and Step 2 output as before. No new output appears yet because you have not added verification prints, but the script completing without errors confirms dim_producto was created successfully.

Seeing a syntax error?

  • Check that each con.execute(""" has a matching closing """). Missing triple quotes are the most common cause.
  • Make sure the new code starts on a new line after the Step 2 section, not inside an existing function or loop.
  • Help me debug a syntax error in my warehouse.py dimension table code.

Why surrogate keys instead of the original product_id?

The original product_id (like P001) is a natural key controlled by the source system. Surrogate keys are integers controlled by the warehouse, making joins faster and insulating the model from upstream ID changes.

The natural key stays in the dimension as a reference column so you can always trace back to the source.

Create the customer and date dimensions

The customer dimension follows the exact same pattern as the product dimension. The date dimension adds a twist: it extracts temporal attributes (year, month, day of week) from each unique date.

  • In warehouse.py, add the customer dimension code directly below the dim_producto INSERT block:
# Dimension: Cliente
con.execute("CREATE SEQUENCE seq_cliente START 1")
con.execute("""
    CREATE TABLE dim_cliente (
        cliente_sk INTEGER PRIMARY KEY DEFAULT nextval('seq_cliente'),
        customer_id VARCHAR,
        customer_name VARCHAR,
        customer_city VARCHAR,
        customer_segment VARCHAR
    )
""")
con.execute("""
    INSERT INTO dim_cliente (customer_id, customer_name, customer_city, customer_segment)
    SELECT DISTINCT customer_id, customer_name, customer_city, customer_segment
    FROM raw_ventas
    ORDER BY customer_id
""")

What does this code do?

This follows the same three-step pattern: create a sequence (seq_cliente), define the table structure with a surrogate key (cliente_sk), and populate it with SELECT DISTINCT to pull one row per unique customer.

  • Now add the date dimension directly below the dim_cliente INSERT block:
# Dimension: Fecha
con.execute("CREATE SEQUENCE seq_fecha START 1")
con.execute("""
    CREATE TABLE dim_fecha (
        fecha_sk INTEGER PRIMARY KEY DEFAULT nextval('seq_fecha'),
        full_date DATE,
        year INTEGER,
        month INTEGER,
        day INTEGER,
        day_of_week VARCHAR
    )
""")
con.execute("""
    INSERT INTO dim_fecha (full_date, year, month, day, day_of_week)
    SELECT DISTINCT
        order_date::DATE AS full_date,
        YEAR(order_date::DATE) AS year,
        MONTH(order_date::DATE) AS month,
        DAY(order_date::DATE) AS day,
        DAYNAME(order_date::DATE) AS day_of_week
    FROM raw_ventas
    ORDER BY full_date
""")

What does the date dimension do differently?

Instead of just storing the raw date, the dimension enriches it with pre-calculated attributes. YEAR(), MONTH(), and DAY() extract numeric components. DAYNAME() returns the day name in English (Monday, Tuesday, etc.).

The ::DATE cast converts the string stored in raw_ventas into a proper date type so the functions work correctly.

  • Save warehouse.py and run the script to confirm both new dimensions are created without errors:
python warehouse.py

The script should complete with the same output as before and no errors. All three dimensions now exist in warehouse.duckdb.

Seeing a 'Catalog Error' or 'already exists'?

  • Make sure the seven DROP TABLE IF EXISTS and DROP SEQUENCE IF EXISTS lines appear before any CREATE statements. These let you rerun the script cleanly.
  • Confirm you have exactly three sequence names: seq_producto, seq_cliente, and seq_fecha. A typo in any name causes a mismatch.
  • Help me fix a Catalog Error when creating dimension tables in DuckDB.
Verify the dimensions have the correct row counts

The dimensions exist, but you need to confirm they hold the right number of unique rows. Each dimension should match the distinct count of its entity in raw_ventas.

  • Add the verification code at the bottom of warehouse.py, below the dim_fecha INSERT block:
# Verificar dimensiones
prod_count = con.execute("SELECT count(*) FROM dim_producto").fetchone()[0]
cli_count = con.execute("SELECT count(*) FROM dim_cliente").fetchone()[0]
fecha_count = con.execute("SELECT count(*) FROM dim_fecha").fetchone()[0]
print(f"\n=== DIMENSIONES CREADAS ===")
print(f"dim_producto: {prod_count} filas")
print(f"dim_cliente:  {cli_count} filas")
print(f"dim_fecha:    {fecha_count} filas")

What does this code do?

Each SELECT count(*) returns the total rows in a dimension table. fetchone()[0] grabs the single integer result. The print statements display all three counts so you can confirm deduplication worked.

  • Save warehouse.py.

Before you run the script, think about how many rows you expect in each dimension. There are 10 products, 8 customers, and roughly 6 months of dates in the dataset. Do you think the counts will match?

  • Run the script to see the dimension counts:
python warehouse.py

At the bottom of the output, you should see the dimension counts. dim_producto should show 10 rows (one per unique product). dim_cliente should show 8 rows (one per unique customer). dim_fecha will show the number of unique order dates in the dataset.

Why does dim_fecha have more rows than 6?

The data spans January to June 2024, but the date dimension has one row per unique calendar date that appears in the orders. With 80 orders spread across 182 days, many distinct dates end up in the dimension. This is expected.

Counts look wrong or dimension is empty?

  • If a dimension shows 0 rows, check that the INSERT INTO statement references the correct source columns. Column names must match exactly (product_id, not producto_id).
  • If dim_producto shows more than 10 rows, make sure you used SELECT DISTINCT in the INSERT. Without it, every order line creates a duplicate product row.
  • Help me debug why my dimension table row counts are wrong.

✔️ Awesome, I've got everything!

Great. Double check you have saved warehouse.py.

ⓧ I'd like to double check the full code

The full warehouse.py is over 30 lines at this point, so scroll through and confirm each section is present in this order:

  • Step 1 section: import duckdb, connect, CREATE TABLE raw_ventas, row count print, and DESCRIBE.
  • Step 2 section: three flat-table queries (revenue by category, unique customers by segment, average ticket by city) with the RESULTADO TABLA PLANA header.
  • Step 3 section: seven DROP statements, then the dim_producto block (sequence + create + insert), then dim_cliente (sequence + create + insert), then dim_fecha (sequence + create + insert), then the verification prints.

If anything is missing or out of order, scroll up to the matching code block in this step and copy it again.

Your three dimension tables are built and verified. Next up, you will create the fact table that sits at the center of the star schema and connects all three dimensions together.

Construye la Tabla de Hechos y Conecta el Esquema Estrella

In Step 3, you built three dimension tables that separate and deduplicate your sales data. Each one holds clean descriptive attributes with its own surrogate key.

Those dimensions are the arms of your star schema, but the star has no center yet. The fact table is the missing piece that stores the numeric business measures and uses surrogate keys to point at each dimension.

In this step, get ready to:
  • Create the fact_ventas table with surrogate key columns pointing to each dimension.
  • Populate fact_ventas by joining raw_ventas with all three dimension tables.
  • Validate the model by reconstructing complete records across all four tables.
Define the fact table structure

Every fact table has a grain: the level of detail each row represents. For fact_ventas, each row represents one order line item (one product within one order).

  • In warehouse.py, find the dimension verification prints at the bottom of the file (the lines that print dim_producto, dim_cliente, and dim_fecha row counts).
  • Add the following code below those lines to create the fact table structure:
# ============================================================
# STEP 4: Crear tabla de hechos
# ============================================================
con.execute("DROP SEQUENCE IF EXISTS seq_venta")
con.execute("CREATE SEQUENCE seq_venta START 1")
con.execute("""
    CREATE TABLE fact_ventas (
        venta_id INTEGER PRIMARY KEY DEFAULT nextval('seq_venta'),
        producto_sk INTEGER,
        cliente_sk INTEGER,
        fecha_sk INTEGER,
        order_id INTEGER,
        quantity INTEGER,
        total_amount DECIMAL(10, 2)
    )
""")

What does this code do?

  • seq_venta is a new sequence that generates auto-incrementing IDs for venta_id, the fact table's own surrogate key.
  • producto_sk, cliente_sk, and fecha_sk are foreign keys that point to the three dimension tables. They replace all the descriptive attributes that used to live in the flat table.
  • order_id stays directly in the fact table as a degenerate dimension. It's a natural key that doesn't need its own dimension table because it has no descriptive attributes beyond the ID itself.
  • quantity and total_amount are additive measures. You can SUM them across any combination of dimensions and get a meaningful result.
  • Save warehouse.py.
Populate the fact table with JOINs to each dimension

The fact table is empty. To populate it, you need to join raw_ventas with each dimension on their shared natural keys, pulling back the surrogate keys to store in fact_ventas.

  • Add this code below the CREATE TABLE fact_ventas block you just wrote:
con.execute("""
    INSERT INTO fact_ventas (producto_sk, cliente_sk, fecha_sk, order_id, quantity, total_amount)
    SELECT
        p.producto_sk,
        c.cliente_sk,
        d.fecha_sk,
        r.order_id,
        r.quantity,
        r.total_amount
    FROM raw_ventas r
    JOIN dim_producto p ON r.product_id = p.product_id
    JOIN dim_cliente c ON r.customer_id = c.customer_id
    JOIN dim_fecha d ON r.order_date::DATE = d.full_date
""")

fact_count = con.execute("SELECT count(*) FROM fact_ventas").fetchone()[0]
print(f"\nfact_ventas: {fact_count} filas")

What does this code do?

  • The INSERT INTO ... SELECT pattern reads every row from raw_ventas and joins it to all three dimensions at once.
  • Each JOIN matches on a natural key (product_id, customer_id, order_date) but only stores the corresponding surrogate key (producto_sk, cliente_sk, fecha_sk) in the fact row.
  • The final two lines count the rows and print the total so you can confirm nothing was lost or duplicated.

Before you run this, think: how many rows should fact_ventas have? More than raw_ventas, fewer, or exactly the same?

  • Save warehouse.py and run the script by running this command:
python warehouse.py

The row count for fact_ventas should match raw_ventas exactly. The grain is the same (one row per order line item), so the transformation should preserve every row without adding or losing any.

Seeing fewer rows than expected?

  • If fact_ventas has fewer rows than raw_ventas, one of the JOIN conditions is not matching. Check that the ON clauses use the correct column names from each table.
  • Make sure the date JOIN uses r.order_date::DATE (with the cast) to match d.full_date. Without the cast, the types may not match and rows get dropped.
  • Help me debug why my fact_ventas has fewer rows than raw_ventas after the INSERT INTO ... SELECT with JOINs.
Validate the star schema integrity

The row count matches, but that alone does not prove the data is correct. The real test is reconstructing a complete record by joining fact_ventas back to all three dimensions and confirming you get the original product names, customer names, and dates.

  • Add this validation code below the fact_count print line:
# Validar integridad: reconstruir una fila completa
print("\nValidacion - fila reconstruida del esquema estrella:")
sample = con.execute("""
    SELECT f.venta_id, f.order_id, p.product_name, c.customer_name,
           d.full_date, f.quantity, f.total_amount
    FROM fact_ventas f
    JOIN dim_producto p ON f.producto_sk = p.producto_sk
    JOIN dim_cliente c ON f.cliente_sk = c.cliente_sk
    JOIN dim_fecha d ON f.fecha_sk = d.fecha_sk
    LIMIT 3
""").fetchall()
for row in sample:
    print(f"   Venta {row[0]}: Orden {row[1]}, {row[2]}, {row[3]}, {row[4]}, qty={row[5]}, ${row[6]:,.2f}")

What does this code do?

This query joins fact_ventas back to all three dimension tables using surrogate keys. If the JOINs return real product names, customer names, and dates, the star schema is wired correctly.

Notice that fact_ventas itself stores no descriptive text. Every human-readable attribute lives in a dimension, and the fact table points to it by surrogate key. That separation is exactly what eliminates the duplication problem you saw in Step 2.

  • Save warehouse.py and run the script again:
python warehouse.py

At the bottom of the output, you should see three reconstructed rows. Each row shows a venta_id, an order_id, a real product name, a real customer name, a date, a quantity, and a dollar amount. Those descriptive values came from the dimension tables, not the fact table.

Seeing NULL values in the reconstructed rows?

  • NULL values mean a JOIN failed to match. Check that your validation query joins on surrogate keys (f.producto_sk = p.producto_sk), not on natural keys.
  • Make sure all three JOIN lines are present. A missing JOIN will cause an error or return incomplete rows.
  • Help me fix NULL values in my star schema validation query.

✔️ Awesome, I've got everything!

Great. Double check you've saved warehouse.py and that your output shows the fact table row count plus three reconstructed rows with real names and dates.

ⓧ I'd like to double check the full code

Your warehouse.py is now too long to show in one block. Scroll through your file and confirm these sections exist, in order:

  • STEP 1: Cargar datos crudos section at the top, with CREATE TABLE raw_ventas and the row count print.
  • STEP 2: Queries sobre la tabla plana section with all three flat table queries (revenue, customers, ticket).
  • STEP 3: Crear tablas de dimensiones section with dim_producto, dim_cliente, and dim_fecha creation, plus dimension count prints.
  • STEP 4: Crear tabla de hechos section with the sequence, CREATE TABLE fact_ventas, the INSERT INTO ... SELECT with three JOINs, the row count print, and the validation query that prints 3 reconstructed rows.

Your star schema is fully connected. The fact table sits at the center, and each dimension branches out from it. Next up, you'll put this model to the test by re-running the same business questions from Step 2 and comparing the results side by side.

Ejecuta Queries Analiticas sobre el Esquema Estrella

En el paso anterior construiste la tabla de hechos fact_ventas y conectaste las tres dimensiones al centro del esquema estrella. Tu data warehouse tiene estructura, pero todavia no has probado si realmente resuelve los problemas de la tabla plana.

Este es el momento de la verdad. Vas a ejecutar las mismas tres preguntas de negocio del Step 2 sobre el esquema estrella y comparar los resultados lado a lado. Si el modelo dimensional funciona, los numeros ahora deben ser correctos y limpios.

In this step, get ready to:
  • Reescribir las tres queries analiticas usando JOINs con las tablas de dimensiones.
  • Comparar los resultados de la tabla plana vs el esquema estrella lado a lado.
  • Ejecutar una query avanzada que aprovecha las dimensiones de fecha y cliente.
Escribe la query de ingreso por categoria sobre el esquema estrella

La primera pregunta de negocio que respondiste en el Step 2 fue "ingreso total por categoria de producto". Ahora la vas a responder usando un JOIN entre fact_ventas y dim_producto en lugar de la tabla plana.

  • En warehouse.py, agrega el siguiente bloque al final del archivo (debajo de la ultima linea que imprime la fila reconstruida). Este es el encabezado de la seccion y la primera query del esquema estrella:
# ============================================================
# STEP 5: Queries analiticas sobre el esquema estrella
# ============================================================
print("\n=== RESULTADOS ESQUEMA ESTRELLA (correcto) ===\n")

# Query 1: Ingreso total por categoria usando JOIN con dim_producto
print("1. Ingreso total por categoria:")
star_revenue = con.execute("""
    SELECT p.product_category, SUM(f.total_amount) AS total_revenue
    FROM fact_ventas f
    JOIN dim_producto p ON f.producto_sk = p.producto_sk
    GROUP BY p.product_category
    ORDER BY total_revenue DESC
""").fetchall()
for row in star_revenue:
    print(f"   {row[0]}: ${row[1]:,.2f}")

What does this code do?

En vez de leer product_category directamente de la tabla plana, ahora haces un JOIN entre fact_ventas y dim_producto usando la surrogate key producto_sk. La tabla de hechos solo tiene numeros y claves. Los atributos descriptivos vienen de la dimension.

  • Guarda warehouse.py y ejecuta el script con este comando:
python warehouse.py
  • Al final de la salida, debajo de la validacion del Step 4, deberias ver el encabezado === RESULTADOS ESQUEMA ESTRELLA (correcto) === seguido del ingreso por categoria.

No ves los resultados del esquema estrella?

Confirma que pegaste el codigo despues de la ultima linea existente del archivo, no dentro de otro bloque de codigo. Revisa que la indentacion del for loop este al nivel del print, sin indentacion extra.

Todavia tienes problemas?

Consulta clientes unicos directamente desde la dimension

La segunda pregunta de negocio es "cuantos clientes unicos hay por segmento". En la tabla plana necesitabas COUNT(DISTINCT customer_id) para evitar contar duplicados. Con el esquema estrella, puedes ir directamente a dim_cliente sin tocar la tabla de hechos.

  • Agrega la segunda query al final de warehouse.py, debajo del for loop de la query anterior:
# Query 2: Clientes unicos por segmento (fuente de verdad: la dimension)
print("\n2. Clientes unicos por segmento (directamente de la dimension):")
star_customers = con.execute("""
    SELECT customer_segment, COUNT(*) AS num_customers
    FROM dim_cliente
    GROUP BY customer_segment
""").fetchall()
for row in star_customers:
    print(f"   {row[0]}: {row[1]}")

Por que esta query no toca fact_ventas?

Esta es la ventaja clave del modelado dimensional. dim_cliente ya tiene exactamente un registro por cliente. No necesitas hacer JOIN con la tabla de hechos ni usar DISTINCT para deduplicar. La dimension ES la fuente de verdad para atributos de cliente.

Si intentaras contar clientes unicos haciendo JOIN con fact_ventas y olvidaras el DISTINCT, obtendrias numeros inflados. La dimension elimina ese riesgo por completo.

  • Guarda warehouse.py y ejecuta el script de nuevo:
python warehouse.py
  • En la seccion RESULTADOS ESQUEMA ESTRELLA ahora deberias ver las queries 1 y 2. Observa que la query de clientes usa un simple COUNT(*) en vez de COUNT(DISTINCT ...).

Ves un error de tabla no encontrada?

Si ves un error como Catalog Error: Table ... does not exist, verifica que escribiste dim_cliente (no dim_clientes con s). Los nombres de tabla deben coincidir exactamente con los que creaste en el Step 3.

Necesitas ayuda?

Agrega la query de ticket promedio y compara los resultados

La tercera pregunta de negocio es el ticket promedio por ciudad. En la tabla plana, esta query mezclaba el grano de linea de pedido con el grano de orden. Ahora la estructura del esquema estrella mantiene el grano limpio.

  • Agrega la tercera query al final de warehouse.py:
# Query 3: Ticket promedio por ciudad usando JOIN con dim_cliente
print("\n3. Ticket promedio por ciudad:")
star_ticket = con.execute("""
    SELECT c.customer_city,
           ROUND(SUM(f.total_amount) / COUNT(DISTINCT f.order_id), 2) AS avg_ticket
    FROM fact_ventas f
    JOIN dim_cliente c ON f.cliente_sk = c.cliente_sk
    GROUP BY c.customer_city
    ORDER BY avg_ticket DESC
""").fetchall()
for row in star_ticket:
    print(f"   {row[0]}: ${row[1]:,.2f}")

What does this code do?

Esta query hace JOIN entre fact_ventas y dim_cliente para obtener customer_city. Calcula el ticket promedio dividiendo el ingreso total entre el numero de ordenes unicas por ciudad.

En el esquema estrella, los atributos del cliente viven en un solo lugar (dim_cliente). Si un cliente cambia de ciudad, solo actualizas una fila en la dimension, no cientos de filas en la tabla plana.

  • Guarda warehouse.py y ejecuta el script:
python warehouse.py

Ahora tu terminal muestra ambos bloques de resultados. Desplazate hacia arriba para ver RESULTADOS TABLA PLANA (potencialmente incorrecto) y luego hacia abajo para ver RESULTADOS ESQUEMA ESTRELLA (correcto).

Que diferencias ves entre los dos bloques de resultados?

La query de ingreso por categoria produce los mismos numeros en ambos casos porque SUM(total_amount) funciona correctamente a nivel de linea de pedido. La diferencia real esta en la confiabilidad del enfoque.

La query de clientes unicos por segmento es donde brilla el esquema estrella. En la tabla plana necesitabas COUNT(DISTINCT customer_id) para evitar inflacion. En el esquema estrella, simplemente haces COUNT(*) directamente sobre dim_cliente. Sin riesgo de error, sin escanear la tabla grande.

Los numeros no coinciden con lo esperado?

Verifica que el archivo ventas_raw.csv no fue regenerado entre pasos. Si ejecutaste generate_data.py mas de una vez, los datos pueden haber cambiado. Los datos del CSV deben ser los mismos que usaste en el Step 2.

Algo no cuadra?

Ejecuta una query avanzada que solo es posible con el esquema estrella

Las tres queries anteriores replicaron lo que ya habias hecho en la tabla plana. Ahora vas a hacer algo nuevo: una query que cruza la dimension de fecha con la dimension de cliente para analizar ventas por dia de la semana y segmento.

  • Agrega la query avanzada y el cierre de conexion al final de warehouse.py:
# Query 4: Ventas por dia de semana y segmento (multi-dimension JOIN)
print("\n4. Query avanzada - Ventas por dia de semana y segmento:")
advanced = con.execute("""
    SELECT d.day_of_week,
           c.customer_segment,
           SUM(f.total_amount) AS total_revenue,
           COUNT(*) AS num_transactions
    FROM fact_ventas f
    JOIN dim_fecha d ON f.fecha_sk = d.fecha_sk
    JOIN dim_cliente c ON f.cliente_sk = c.cliente_sk
    GROUP BY d.day_of_week, c.customer_segment
    ORDER BY total_revenue DESC
    LIMIT 10
""").fetchall()
for row in advanced:
    print(f"   {row[0]} | {row[1]}: ${row[2]:,.2f} ({row[3]} transacciones)")

# Cerrar conexion y guardar el warehouse
con.close()
print("\nData warehouse guardado en warehouse.duckdb")

What does this code do?

Esta query hace JOIN con dos dimensiones a la vez: dim_fecha para obtener day_of_week y dim_cliente para obtener customer_segment. El resultado muestra que dias y segmentos generan mas ingresos.

En la tabla plana original no existia la columna day_of_week. La dimension de fecha la derivo automaticamente a partir de order_date. Las dimensiones enriquecen el analisis mas alla de lo que los datos planos permiten.

Antes de ejecutar el script final, piensa: que dias de la semana crees que generaran mas ventas? Los datos son sinteticos con distribucion aleatoria, asi que los resultados podrian sorprenderte.

  • Guarda warehouse.py y ejecuta el script por ultima vez:
python warehouse.py

Al final de la salida veras la query avanzada con 10 filas mostrando dia de semana, segmento, ingreso total y numero de transacciones. La ultima linea dira Data warehouse guardado en warehouse.duckdb.

Ves un error al cerrar la conexion?

Si ves un error en con.close(), verifica que no haya otra llamada a con.close() mas arriba en el archivo. La conexion solo puede cerrarse una vez.

Necesitas ayuda con el error?

✔️ Awesome, I've got everything!

Perfecto. Confirma que guardaste warehouse.py y que la ultima linea de la salida del script dice Data warehouse guardado en warehouse.duckdb.

ⓧ I'd like to double check the full code

Tu archivo warehouse.py es demasiado largo para mostrarlo completo aqui. Desplazate hacia arriba y confirma que tu archivo contiene estas secciones, en este orden:

  • STEP 1: Importar duckdb, conectar a warehouse.duckdb, y cargar raw_ventas.
  • STEP 2: Tres queries sobre la tabla plana con label RESULTADOS TABLA PLANA.
  • STEP 3: Creacion de dim_producto, dim_cliente, y dim_fecha con verificacion de conteos.
  • STEP 4: Creacion de fact_ventas con INSERT via JOINs y validacion de integridad.
  • STEP 5: Cuatro queries sobre el esquema estrella con label RESULTADOS ESQUEMA ESTRELLA, seguidas de con.close().

Secret mission

Implementa SCD Type 2 en la Dimension de Producto

In the real world, dimension attributes change. A product's price goes up, a customer moves to a new city. Your current star schema overwrites these changes and loses history. Can you extend dim_producto with SCD Type 2 columns to track every version of a product over time?

Clean Up Your Resources

Clean Up Your Resources

This project runs entirely locally, so there are no ongoing costs. Decide whether to keep your resources on disk to revisit later, or delete them entirely.

Resources you used:

  • Local project folder star-schema-project on your Desktop, containing generate_data.py, warehouse.py, warehouse_scd2.py, ventas_raw.csv, and warehouse.duckdb.
  • DuckDB Python package installed via pip.

Keep everything running

No action needed. Choose this if you want to keep experimenting with your star schema warehouse or add more dimensions and facts.

  • Your star-schema-project folder and all its files stay on your Desktop.
  • The DuckDB Python package remains installed. It takes up minimal disk space.

Pause - I'll come back to this later

There are no running processes to stop. Your files and database sit on disk until you return.

  • Your star-schema-project folder stays on your Desktop with all scripts and the warehouse.duckdb database intact.
  • When you come back, open your terminal, navigate to the folder, and run python warehouse.py to rebuild the warehouse from scratch.

Delete - I don't want to use this again

Remove all project files and uninstall DuckDB to start fresh.

  • Delete the project folder by running this command in your terminal.

macOS

rm -rf ~/Desktop/star-schema-project

Windows

rmdir /s /q %USERPROFILE%\Desktop\star-schema-project
  • Uninstall the DuckDB Python package by running:
pip uninstall duckdb
  • When prompted, enter y to confirm the uninstall.
  • Confirm deletion by running ls ~/Desktop (macOS) or checking your Desktop. The star-schema-project folder should no longer appear.

Nice Work!

Nice Work!

Nice work! You just built a fully functional star schema data warehouse from raw CSV data using Python and DuckDB.

You've learned how to:

  • Experience the fan-out problem firsthand on a denormalized flat table, then prove that a Kimball-style dimensional model produces correct analytical results.
  • Design and build three dimension tables (dim_producto, dim_cliente, dim_fecha) with surrogate keys and a central fact table (fact_ventas) with additive measures and foreign key relationships.
  • Run side-by-side analytical queries comparing a flat table against the star schema, including an advanced query combining dim_fecha.day_of_week with customer segment to unlock insights impossible on flat data.
  • Secret Mission: Took on an optional challenge to push your data warehouse skills further.

Ready to quiz yourself?