02_data.sql

/* ============================================================
   02_data.sql
   Proyecto SQL – Plataforma de cursos online

   Este script crea una tabla RAW con datos sintéticos preparados
   específicamente para el proyecto, aplica transformaciones de negocio
   y carga las dimensiones y la tabla de hechos.
   ============================================================ */

USE course_platform_dw;

-- Nos aseguramos de utilizar la base de datos para que se realicen las operaciones sobre la base que queremos.
-- ------------------------------------------------------------
-- 0. TABLA STAGING (RAW)
-- La tabla RAW se recrea y carga antes de la transacción.
-- En MySQL, DROP TABLE y CREATE TABLE provocan commits implícitos.
-- ------------------------------------------------------------

DROP TABLE IF EXISTS courses_raw;

CREATE TABLE courses_raw (
    course_title VARCHAR(255),
    subject VARCHAR(100),
    level VARCHAR(50),
    is_paid BOOLEAN,
    price DECIMAL(6,2),
    num_subscribers INT,
    num_reviews INT,
    num_lectures INT,
    content_duration DECIMAL(6,2),
    avg_rating DECIMAL(3,2),
    completion_rate DECIMAL(4,2),
    active_students INT,
    published_date DATE,
    url VARCHAR(255)
);

-- ------------------------------------------------------------
-- 1. INSERCIÓN DE DATOS RAW
-- Los registros son sintéticos y se han creado específicamente
-- para practicar modelado dimensional, transformación y análisis.
-- ------------------------------------------------------------

INSERT INTO courses_raw VALUES
('Complete Python Bootcamp','Development','All Levels',TRUE,199.99,150000,32000,150,22.5,4.7,0.72,45000,'2018-01-15','https://example.com/courses/python'),
('Java Programming Masterclass','Development','All Levels',TRUE,189.99,110000,25000,160,24.0,4.6,0.68,38000,'2019-07-22','https://example.com/courses/java'),
('Web Development Masterclass','Development','Intermediate',TRUE,149.99,60000,9000,120,18.0,4.4,0.65,21000,'2021-02-20','https://example.com/courses/web'),
('React for Beginners','Development','Beginner',TRUE,89.99,75000,11000,95,15.0,4.5,0.70,26000,'2021-03-12','https://example.com/courses/react'),
('Advanced JavaScript','Development','Advanced',TRUE,159.99,42000,6800,110,17.5,4.3,0.62,14000,'2020-09-18','https://example.com/courses/js-advanced'),

('Machine Learning A-Z','Data Science','Advanced',TRUE,249.99,95000,18000,200,25.0,4.6,0.66,32000,'2018-11-11','https://example.com/courses/machine-learning'),
('Statistics for Data Science','Data Science','Intermediate',TRUE,159.99,52000,8000,100,16.5,4.4,0.64,18000,'2020-04-18','https://example.com/courses/statistics'),
('Deep Learning with TensorFlow','Data Science','Advanced',TRUE,249.99,68000,14000,180,27.0,4.5,0.67,24000,'2019-10-08','https://example.com/courses/tensorflow'),
('Python for Data Analysis','Data Science','Beginner',TRUE,119.99,83000,12000,90,14.0,4.5,0.71,29000,'2021-06-10','https://example.com/courses/data-python'),
('R Programming Essentials','Data Science','Beginner',FALSE,0.00,45000,5000,70,11.0,4.2,0.60,12000,'2022-01-25','https://example.com/courses/r'),

('SQL for Data Analysis','Business','Beginner',TRUE,99.99,85000,12000,80,12.0,4.6,0.74,31000,'2019-03-10','https://example.com/courses/sql'),
('Excel from Zero to Hero','Business','Beginner',FALSE,0.00,200000,45000,60,10.0,4.7,0.76,78000,'2020-06-05','https://example.com/courses/excel'),
('Advanced Excel Techniques','Business','Advanced',TRUE,149.99,46000,6200,85,13.5,4.4,0.69,17000,'2020-11-05','https://example.com/courses/excel-advanced'),
('Project Management Essentials','Business','All Levels',TRUE,99.99,54000,8800,75,12.5,4.5,0.71,20000,'2018-06-19','https://example.com/courses/project-management'),
('Financial Analysis Fundamentals','Business','Intermediate',TRUE,129.99,39000,5100,90,14.5,4.3,0.63,14000,'2021-09-30','https://example.com/courses/financial-analysis'),

('Digital Marketing Basics','Marketing','Beginner',FALSE,0.00,90000,15000,55,9.0,4.4,0.73,36000,'2022-05-30','https://example.com/courses/digital-marketing'),
('SEO Fundamentals','Marketing','Intermediate',TRUE,59.99,31000,4200,65,10.0,4.2,0.61,11000,'2020-02-14','https://example.com/courses/seo'),
('Google Ads Mastery','Marketing','Advanced',TRUE,139.99,27000,3900,85,13.0,4.3,0.64,9000,'2019-08-21','https://example.com/courses/ads'),
('Social Media Marketing','Marketing','Beginner',TRUE,79.99,62000,9800,70,11.5,4.5,0.72,24000,'2021-11-02','https://example.com/courses/social-media'),

('Linux Administration Basics','IT & Software','Beginner',TRUE,69.99,48000,6000,75,12.0,4.4,0.66,18000,'2019-05-14','https://example.com/courses/linux'),
('AWS Cloud Practitioner','IT & Software','Beginner',TRUE,109.99,95000,16000,90,14.0,4.6,0.74,35000,'2020-08-09','https://example.com/courses/cloud'),
('Docker & Kubernetes','IT & Software','Advanced',TRUE,179.99,52000,8700,120,18.0,4.5,0.69,19000,'2021-01-27','https://example.com/courses/containers'),
('Cybersecurity Essentials','IT & Software','Intermediate',TRUE,149.99,41000,6200,95,15.0,4.4,0.65,15000,'2018-10-03','https://example.com/courses/cybersecurity'),
('Networking Fundamentals','IT & Software','Beginner',FALSE,0.00,36000,4200,60,9.5,4.1,0.58,10000,'2022-03-12','https://example.com/courses/networking');

-- ------------------------------------------------------------
-- 2. TRANSACCIÓN ETL
-- La transacción cubre la limpieza y la carga del modelo dimensional.
-- No se implementa un rollback automático mediante handlers.
-- ------------------------------------------------------------

START TRANSACTION;

-- ------------------------------------------------------------
-- 3. LIMPIEZA PREVIA
-- Se elimina primero la tabla de hechos y después las dimensiones
-- para respetar las relaciones de clave foránea.
-- ------------------------------------------------------------

DELETE FROM fact_courses;
DELETE FROM dim_course;
DELETE FROM dim_subject;
DELETE FROM dim_level;
DELETE FROM dim_price_segment;
DELETE FROM dim_date;

-- ------------------------------------------------------------
-- 4. CARGA DE DIMENSIONES
-- En la dim date extraemos fechas unicas, evitamos duplicados. Empezamos con las dimensiones antes 
-- que la tabla de hechos.
-- ------------------------------------------------------------

/* DIM_DATE */
INSERT INTO dim_date (full_date, day, month, month_name, quarter, year)
SELECT DISTINCT
    published_date,
    DAY(published_date),
    MONTH(published_date),
    MONTHNAME(published_date),
    QUARTER(published_date),
    YEAR(published_date)
FROM courses_raw;

-- En dim subject y dim level normalizariamos valores categoricos.
/* DIM_SUBJECT */
INSERT INTO dim_subject (subject_name)
SELECT DISTINCT subject
FROM courses_raw;

/* DIM_LEVEL */
INSERT INTO dim_level (level_name)
SELECT DISTINCT level
FROM courses_raw;

-- En dim price lo que hacemos es definir reglas de negocio que no dependen del dataset.(Es parte del modelo, no de los datos)
/* DIM_PRICE_SEGMENT */
INSERT INTO dim_price_segment (segment_name, min_price, max_price)
VALUES
('Free', 0.00, 0.00),
('Low price', 0.01, 50.00),
('Medium price', 50.01, 150.00),
('High price', 150.01, 1000.00);

/* DIM_COURSE */
-- En dim course transformamos datos crudos y añadimos una segmentación de negocio utilizando el case.
-- Esto simplifica el analisis posterior.
INSERT INTO dim_course (
    course_title,
    is_paid,
    content_duration,
    num_lectures,
    course_size,
    url
)
SELECT DISTINCT
    course_title,
    is_paid,
    content_duration,
    num_lectures,
    CASE
        WHEN num_lectures < 60 THEN 'Short'
        WHEN num_lectures BETWEEN 60 AND 120 THEN 'Medium'
        ELSE 'Long'
    END AS course_size,
    url
FROM courses_raw;

-- ------------------------------------------------------------
-- 5. CARGA DE LA TABLA DE HECHOS
-- En la carga de los datos de la tabla de hechos relacionamos los datos con las dimensiones. Insertamos las
-- métricas que ya tienen un contexto. Los JOIN hacen que solamente entren registros validos
-- La tabla de hechos se carga una vez que las dimensiones estan disponibles así tenemos consistencia.
-- ------------------------------------------------------------

INSERT INTO fact_courses (
    course_id,
    subject_id,
    level_id,
    price_segment_id,
    date_id,
    price,
    num_subscribers,
    num_reviews,
    avg_rating,
    completion_rate,
    active_students
)
SELECT
    c.course_id,
    s.subject_id,
    l.level_id,
    ps.price_segment_id,
    d.date_id,
    r.price,
    r.num_subscribers,
    r.num_reviews,
    r.avg_rating,
    r.completion_rate,
    r.active_students
FROM courses_raw r
INNER JOIN dim_course c
    ON r.course_title = c.course_title
INNER JOIN dim_subject s
    ON r.subject = s.subject_name
INNER JOIN dim_level l
    ON r.level = l.level_name
INNER JOIN dim_date d
    ON r.published_date = d.full_date
INNER JOIN dim_price_segment ps
    ON r.price BETWEEN ps.min_price AND ps.max_price;

-- ------------------------------------------------------------
-- 6. LIMPIEZA Y AJUSTES
-- Con esto corregiriamos valores incorrectos y simulamos una limpieza despues de la carga de datos.
-- Lo hemos hecho simplemente para dejar en evidencia que no siempre se corrigen las errores antes
-- de la carga de datos, a veces se puede realizar limpieza y ajustes tras la carga.
-- ------------------------------------------------------------

/* Garantizar que los cursos gratuitos tienen precio 0 */
UPDATE fact_courses f
JOIN dim_course c ON f.course_id = c.course_id
SET f.price = 0
WHERE c.is_paid = FALSE;

/* Ajuste de cursos muy antiguos */
UPDATE fact_courses f
JOIN dim_date d ON f.date_id = d.date_id
SET f.active_students = ROUND(f.active_students * 0.9)
WHERE d.year < 2019;

-- ------------------------------------------------------------
-- 7. DELETE de registros no relevantes
-- Se eliminan registros que no tienen un impacto real en el analisis para no generar ruido.
-- ------------------------------------------------------------
DELETE FROM fact_courses
WHERE num_subscribers = 0
  AND num_reviews = 0;
-- ------------------------------------------------------------
-- 8. COMMIT
-- La transacción se confirma al finalizar el script.
-- No hay un handler que ejecute ROLLBACK automáticamente si se produce un error.
-- ------------------------------------------------------------

COMMIT;

-- Si las sentencias se ejecutan manualmente y se detecta un error antes del COMMIT,
-- puede ejecutarse ROLLBACK de forma explícita.