Un pipeline de datos totalmente automatizado utilizando la Arquitectura Medallion
Este proyecto presenta un pipeline de datos end-to-end diseñado para procesar, estructurar y analizar estadísticas históricas de criminalidad en México. Implementa una Arquitectura Medallion utilizando Databricks LakeFlow para la ingesta y transformación escalable de datos crudos gubernamentales. La capa analítica (Gold) se despliega en Supabase (PostgreSQL), actuando como el backend optimizado para alimentar un dashboard interactivo en Looker Studio, transformando un volumen complejo de datos públicos en inteligencia de negocio lista para consumo.
Flujo de datos: API del Gobierno → Databricks (Bronze, Silver, Gold) → Supabase PostgreSQL → Front-end con Looker Studio
-- ==========================================================================
-- == Carga incremental RAW 'Incidencia delictiva estatal.csv' ==
-- ==========================================================================
CREATE OR REFRESH STREAMING TABLE incidencia_delictiva_estatal_bronze
COMMENT "Datos crudos de 'Incidencia delictiva estatal.csv'"
TBLPROPERTIES (
'delta.columnMapping.mode' = 'name',
'delta.minReaderVersion' = '2',
'delta.minWriterVersion' = '5'
)
AS
SELECT
*,
_metadata.file_modification_time AS file_modification_time,
_metadata.file_path AS file_path,
_metadata.file_name AS source_file,
current_timestamp() AS ingestion_time
FROM STREAM(read_files(
'/Volumes/yucatan_project/security_visualization/security_volume/raw_data/',
format => 'csv',
header => true,
inferSchema => true,
encoding => 'ISO-8859-1',
pathGlobFilter => 'Incidencia delictiva estatal.csv'
));
-- ==========================================================================
-- == Carga incremental RAW 'Incidencia delictiva municipal.csv' ==
-- ==========================================================================
CREATE OR REFRESH STREAMING TABLE incidencia_delictiva_municipal_bronze
COMMENT "Datos crudos de 'Incidencia delictiva municipal.csv'"
TBLPROPERTIES (
'delta.columnMapping.mode' = 'name',
'delta.minReaderVersion' = '2',
'delta.minWriterVersion' = '5'
)
AS
SELECT
*,
_metadata.file_modification_time AS file_modification_time,
_metadata.file_path AS file_path,
_metadata.file_name AS source_file,
current_timestamp() AS ingestion_time
FROM STREAM(read_files(
'/Volumes/yucatan_project/security_visualization/security_volume/raw_data/',
format => 'csv',
header => true,
inferSchema => true,
encoding => 'ISO-8859-1',
pathGlobFilter => 'Incidencia delictiva municipal.csv'
));
-- ==========================================================================
-- == Carga incremental RAW 'Victimas del fuero comun.csv' ==
-- ==========================================================================
CREATE OR REFRESH STREAMING TABLE victimas_fuero_comun_bronze
COMMENT "Datos crudos de 'Victimas del fuero comun.csv'"
TBLPROPERTIES (
'delta.columnMapping.mode' = 'name',
'delta.minReaderVersion' = '2',
'delta.minWriterVersion' = '5'
)
AS
SELECT
*,
_metadata.file_modification_time AS file_modification_time,
_metadata.file_path AS file_path,
_metadata.file_name AS source_file,
current_timestamp() AS ingestion_time
FROM STREAM(read_files(
'/Volumes/yucatan_project/security_visualization/security_volume/raw_data/',
format => 'csv',
header => true,
inferSchema => true,
encoding => 'ISO-8859-1',
pathGlobFilter => 'Víctimas del fuero común.csv'
));
-- ==========================================================================
-- == Carga incremental RAW 'data_pesca_ilegal.csv' ==
-- ==========================================================================
CREATE OR REFRESH STREAMING TABLE incidencias_pesca_ilegal_bronze
COMMENT "Datos crudos de 'data_pesca_ilegal.csv'"
TBLPROPERTIES (
'delta.columnMapping.mode' = 'name',
'delta.minReaderVersion' = '2',
'delta.minWriterVersion' = '5'
)
AS
SELECT
*,
_metadata.file_modification_time AS file_modification_time,
_metadata.file_path AS file_path,
_metadata.file_name AS source_file,
current_timestamp() AS ingestion_time
FROM STREAM(read_files(
'/Volumes/yucatan_project/security_visualization/security_volume/raw_data/',
format => 'csv',
header => true,
inferSchema => true,
encoding => 'ISO-8859-1',
pathGlobFilter => 'data_pesca_ilegal.csv'
));
-- ==========================================================================
-- == Carga incremental RAW 'INM_estatal_dic25.csv' ==
-- ==========================================================================
CREATE OR REFRESH STREAMING TABLE incidencias_estatal_conglomerado_bronze
COMMENT "Datos crudos de 'INM_estatal_dic25.csv'"
TBLPROPERTIES (
'delta.columnMapping.mode' = 'name',
'delta.minReaderVersion' = '2',
'delta.minWriterVersion' = '5'
)
AS
SELECT
*,
_metadata.file_modification_time AS file_modification_time,
_metadata.file_path AS file_path,
_metadata.file_name AS source_file,
current_timestamp() AS ingestion_time
FROM STREAM(read_files(
'/Volumes/yucatan_project/security_visualization/security_volume/raw_data/',
format => 'csv',
header => true,
inferSchema => true,
encoding => 'ISO-8859-1',
pathGlobFilter => 'INM_estatal_dic25.csv'
));
Capa Bronze: ingesta incremental de 5 datasets desde archivos CSV con encoding ISO-8859-1. Cada tabla preserva los datos crudos junto con metadatos de auditoría: timestamp de modificación, ruta del archivo fuente y hora de ingesta.
-- ==========================================================================
-- == STREAMING TABLE: incidencias_municipales_silver ==
-- ==========================================================================
CREATE OR REFRESH STREAMING TABLE incidencias_municipales_silver (
anio INT COMMENT "Año del registro",
clave_estado INT COMMENT "Clave de la entidad federativa",
estado STRING COMMENT "Nombre de la entidad federativa",
clave_municipio INT COMMENT "Clave del municipio",
municipio STRING COMMENT "Nombre del municipio",
bien_juridico_afectado STRING COMMENT "Bien jurídico afectado",
tipo_de_delito STRING COMMENT "Tipo de delito",
subtipo_de_delito STRING COMMENT "Subtipo de delito",
modalidad_del_delito STRING COMMENT "Modalidad del delito",
enero INT COMMENT "Cantidad de delitos en enero",
febrero INT COMMENT "Cantidad de delitos en febrero",
marzo INT COMMENT "Cantidad de delitos en marzo",
abril INT COMMENT "Cantidad de delitos en abril",
mayo INT COMMENT "Cantidad de delitos en mayo",
junio INT COMMENT "Cantidad de delitos en junio",
julio INT COMMENT "Cantidad de delitos en julio",
agosto INT COMMENT "Cantidad de delitos en agosto",
septiembre INT COMMENT "Cantidad de delitos en septiembre",
octubre INT COMMENT "Cantidad de delitos en octubre",
noviembre INT COMMENT "Cantidad de delitos en noviembre",
diciembre INT COMMENT "Cantidad de delitos en diciembre"
)
COMMENT "Tabla con la cantidad de delitos cometidos en cada municipio de México"
AS
SELECT
CAST(`Año` AS INT) AS anio,
CAST(Clave_Ent AS INT) AS clave_estado,
Entidad AS estado,
CAST(`Cve. Municipio` AS INT) AS clave_municipio,
Municipio AS municipio,
`Bien jurídico afectado` AS bien_juridico_afectado,
`Tipo de delito` AS tipo_de_delito,
`Subtipo de delito` AS subtipo_de_delito,
`Modalidad` AS modalidad_del_delito,
CAST(Enero AS INT) AS enero,
CAST(Febrero AS INT) AS febrero,
CAST(Marzo AS INT) AS marzo,
CAST(Abril AS INT) AS abril,
CAST(Mayo AS INT) AS mayo,
CAST(Junio AS INT) AS junio,
CAST(Julio AS INT) AS julio,
CAST(Agosto AS INT) AS agosto,
CAST(Septiembre AS INT) AS septiembre,
CAST(Octubre AS INT) AS octubre,
CAST(Noviembre AS INT) AS noviembre,
CAST(Diciembre AS INT) AS diciembre
FROM STREAM (yucatan_project.security_visualization.incidencia_delictiva_municipal_bronze);
Capa Silver: tipado estricto y normalización de columnas desde la fuente Bronze. Se renombran columnas con caracteres especiales usando TBLPROPERTIES con column mapping, y se aplica CAST a todos los campos numéricos y de fecha para garantizar consistencia.
-- ==========================================================================
-- 1. JERARQUÍA DE GEOGRAFÍA (3NF)
-- ==========================================================================
CREATE OR REFRESH MATERIALIZED VIEW cat_estado
COMMENT "Catálogo de entidades federativas (3NF)"
AS
SELECT DISTINCT
clave_estado AS clave_ent,
estado AS entidad
FROM yucatan_project.security_visualization.incidencias_municipales_silver;
CREATE OR REFRESH MATERIALIZED VIEW dim_geografia
COMMENT "Dimensión de geografía a nivel municipio (3NF)"
AS
SELECT DISTINCT
(clave_estado * 1000) + clave_municipio AS id_geografia,
clave_municipio AS cve_municipio,
municipio,
clave_estado AS clave_ent
FROM yucatan_project.security_visualization.incidencias_municipales_silver;
-- ==========================================================================
-- 2. JERARQUÍA DE DELITOS (3NF)
-- ==========================================================================
CREATE OR REFRESH MATERIALIZED VIEW cat_bien_juridico
COMMENT "Catálogo de bienes jurídicos afectados (3NF)"
AS
SELECT DISTINCT
ABS(HASH(bien_juridico_afectado)) AS id_bien,
bien_juridico_afectado
FROM yucatan_project.security_visualization.incidencias_municipales_silver;
CREATE OR REFRESH MATERIALIZED VIEW cat_tipo_delito
COMMENT "Catálogo de tipos de delito (3NF)"
AS
SELECT DISTINCT
ABS(HASH(bien_juridico_afectado, tipo_de_delito)) AS id_tipo,
ABS(HASH(bien_juridico_afectado)) AS id_bien,
tipo_de_delito
FROM yucatan_project.security_visualization.incidencias_municipales_silver;
CREATE OR REFRESH MATERIALIZED VIEW cat_subtipo_delito
COMMENT "Catálogo de subtipos de delito (3NF)"
AS
SELECT DISTINCT
ABS(HASH(bien_juridico_afectado, tipo_de_delito, subtipo_de_delito)) AS id_subtipo,
ABS(HASH(bien_juridico_afectado, tipo_de_delito)) AS id_tipo,
subtipo_de_delito
FROM yucatan_project.security_visualization.incidencias_municipales_silver;
CREATE OR REFRESH MATERIALIZED VIEW dim_modalidad_delito
COMMENT "Dimensión de modalidades de delito (3NF)"
AS
SELECT DISTINCT
ABS(HASH(bien_juridico_afectado, tipo_de_delito, subtipo_de_delito, modalidad_del_delito)) AS id_modalidad,
ABS(HASH(bien_juridico_afectado, tipo_de_delito, subtipo_de_delito)) AS id_subtipo,
modalidad_del_delito AS modalidad
FROM yucatan_project.security_visualization.incidencias_municipales_silver;
-- ==========================================================================
-- 3. JERARQUÍA DE TIEMPO (3NF)
-- ==========================================================================
CREATE OR REFRESH MATERIALIZED VIEW cat_mes
COMMENT "Catálogo de meses del año (3NF)"
AS
SELECT explode(array(
named_struct('mes_numero', 1, 'mes_nombre', 'enero'),
named_struct('mes_numero', 2, 'mes_nombre', 'febrero'),
named_struct('mes_numero', 3, 'mes_nombre', 'marzo'),
named_struct('mes_numero', 4, 'mes_nombre', 'abril'),
named_struct('mes_numero', 5, 'mes_nombre', 'mayo'),
named_struct('mes_numero', 6, 'mes_nombre', 'junio'),
named_struct('mes_numero', 7, 'mes_nombre', 'julio'),
named_struct('mes_numero', 8, 'mes_nombre', 'agosto'),
named_struct('mes_numero', 9, 'mes_nombre', 'septiembre'),
named_struct('mes_numero', 10, 'mes_nombre', 'octubre'),
named_struct('mes_numero', 11, 'mes_nombre', 'noviembre'),
named_struct('mes_numero', 12, 'mes_nombre', 'diciembre')
)) AS mes;
CREATE OR REFRESH MATERIALIZED VIEW dim_tiempo
COMMENT "Dimensión de tiempo (3NF)"
AS
WITH anios AS (
SELECT DISTINCT anio FROM yucatan_project.security_visualization.incidencias_municipales_silver
)
SELECT
CAST(CONCAT(CAST(a.anio AS STRING), LPAD(CAST(c.mes.mes_numero AS STRING), 2, '0')) AS INT) AS id_tiempo,
a.anio,
c.mes.mes_numero
FROM anios a
CROSS JOIN cat_mes c;
-- ==========================================================================
-- 4. TABLA DE HECHOS (AQUÍ SÍ USAS STREAMING)
-- ==========================================================================
CREATE OR REFRESH STREAMING TABLE fact_incidencias
COMMENT "Tabla de hechos de delitos. Modelo Copo de Nieve (3NF)."
AS
SELECT
CAST(
CONCAT(CAST(anio AS STRING),
CASE mes_nombre
WHEN 'enero' THEN '01' WHEN 'febrero' THEN '02' WHEN 'marzo' THEN '03'
WHEN 'abril' THEN '04' WHEN 'mayo' THEN '05' WHEN 'junio' THEN '06'
WHEN 'julio' THEN '07' WHEN 'agosto' THEN '08' WHEN 'septiembre' THEN '09'
WHEN 'octubre' THEN '10' WHEN 'noviembre' THEN '11' WHEN 'diciembre' THEN '12'
END)
AS INT) AS id_tiempo,
(clave_estado * 1000) + clave_municipio AS id_geografia,
ABS(HASH(bien_juridico_afectado, tipo_de_delito, subtipo_de_delito, modalidad_del_delito)) AS id_modalidad,
cantidad_delitos
FROM STREAM(yucatan_project.security_visualization.incidencias_municipales_silver)
UNPIVOT (
cantidad_delitos FOR mes_nombre IN (
enero, febrero, marzo, abril, mayo, junio, julio, agosto, septiembre, octubre, noviembre, diciembre
)
)
WHERE cantidad_delitos > 0;
Capa Gold: modelo Copo de Nieve (3NF). Dimensiones normalizadas de geografía, delitos y tiempo con claves surrogate vía HASH(). Tabla de hechos fact_incidencias con UNPIVOT de columnas mensuales a filas, lista para consumo en Looker Studio.