⚠️ Advertencia crítica antes de correr cualquier script SQL
No ejecutes consultas SQL contra bases de datos reales, servidores de producción, tablas oficiales, data sensible o ambientes institucionales sin autorización formal.
SQL puede leer, modificar, insertar, actualizar o borrar miles o millones de registros en segundos. Una consulta mal probada puede romper reportes, afectar procesos ETL, alterar datos oficiales, exponer información sensible o generar incidentes de ciberseguridad y cumplimiento.
Regla obligatoria del training: primero se trabaja con tablas temporales o data sintética, luego con una copia controlada, después se valida el resultado, y solo entonces se considera usar fuentes reales siguiendo permisos, backup, change control, data governance y protocolos de ciberseguridad.
- Permisos: usa solo bases, tablas o vistas donde tengas autorización explícita.
- Backups: nunca ejecutes cambios sin respaldo, rollback o ambiente de prueba.
- Data governance: respeta privacidad, clasificación de datos y controles de acceso.
- Producción: evita UPDATE, DELETE, INSERT o CREATE en producción sin revisión y aprobación.
⚠️ Critical warning before running any SQL script
Do not run SQL queries against real databases, production servers, official tables, sensitive data, or institutional environments without formal authorization.
SQL can read, modify, insert, update, or delete thousands or millions of records in seconds. A poorly tested query can break reports, affect ETL processes, alter official data, expose sensitive information, or create cybersecurity and compliance incidents.
Mandatory training rule: start with temporary tables or synthetic data, then use a controlled copy, validate the result, and only then consider real sources while following permissions, backups, change control, data governance, and cybersecurity protocols.
- Permissions: use only databases, tables, or views where you have explicit authorization.
- Backups: never run changes without backup, rollback, or test environment.
- Data governance: respect privacy, data classification, and access controls.
- Production: avoid UPDATE, DELETE, INSERT, or CREATE in production without review and approval.
Objetivo del Topic 18
Centraliza reglas de limpieza para que el dashboard consuma una fuente más confiable.
Topic 18 Objective
Centralize cleaning rules so the dashboard consumes a more reliable source.
Ambiente de práctica
El ejercicio usa tablas temporales para evitar tocar data real:
Practice environment
The exercise uses temporary tables to avoid touching real data:
-- SQL Server / T-SQL practice
-- Run in a controlled training database or query window
-- Main objects use #temporary tablesQué vamos a aprender
- Crear tablas temporales con data sintética.
- Ejecutar una consulta SQL segura de práctica.
- Aplicar una regla de calidad, cruce, resumen o auditoría.
- Verificar conteos o excepciones.
- Adaptar el patrón solo con permisos y revisión.
What you will learn
- Create temporary tables with synthetic data.
- Run a safe practice SQL query.
- Apply a quality, join, summary, or audit rule.
- Verify counts or exceptions.
- Adapt the pattern only with permissions and review.
Script 1 — Crear data sintética
Este script crea tablas temporales con datos de práctica para ejecutar el caso sin tocar tablas oficiales.
Script 1 — Create synthetic data
This script creates temporary tables with practice data so the case can run without touching official tables.
-- SQL Topic 18 - Script 1
-- Training version uses a temp table.
-- In production, CREATE VIEW requires permissions and governance approval.
IF OBJECT_ID('tempdb..#EmployeeMaster') IS NOT NULL DROP TABLE #EmployeeMaster;
CREATE TABLE #EmployeeMaster (
Employee_ID int,
Full_Name varchar(100),
Status_Code varchar(10),
Department_ID int,
Hire_Date datetime
);
INSERT INTO #EmployeeMaster VALUES
(10001, ' John Smith ', ' a ', 10, '2024-01-15 08:00:00'),
(10002, ' Maria Garcia ', ' i ', 20, '2024-02-20 09:30:00'),
(NULL, 'Bad Record', 'x', 30, '2024-03-01');Script 2 — Ejecutar la consulta principal
Este script aplica la lógica central del topic: exploración, calidad, cruce, resumen, auditoría o preparación de datos.
Script 2 — Run the main query
This script applies the core topic logic: exploration, quality, joining, summary, audit, or data preparation.
-- SQL Topic 18 - Script 2
-- Safe preview of the logic that would go into a clean reporting view
SELECT
Employee_ID,
LTRIM(RTRIM(Full_Name)) AS Full_Name,
UPPER(LTRIM(RTRIM(Status_Code))) AS Status_Code,
Department_ID,
CAST(Hire_Date AS date) AS Hire_Date
FROM #EmployeeMaster
WHERE Employee_ID IS NOT NULL;Script 3 — Verificar resultados
Este script confirma conteos, estructura, excepciones o evidencia del proceso.
Script 3 — Verify results
This script confirms counts, structure, exceptions, or process evidence.
-- SQL Topic 18 - Script 3
-- Production-style pattern, shown for learning only.
-- Do not run CREATE VIEW in production without approval.
/*
CREATE VIEW reporting.vw_Employee_Clean AS
SELECT
Employee_ID,
LTRIM(RTRIM(Full_Name)) AS Full_Name,
UPPER(LTRIM(RTRIM(Status_Code))) AS Status_Code,
Department_ID,
CAST(Hire_Date AS date) AS Hire_Date
FROM dbo.EmployeeMaster
WHERE Employee_ID IS NOT NULL;
*/
SELECT 'View pattern documented for review' AS Verification_Message;Explicación simple del script
- Las tablas #temporales reducen riesgo en el entrenamiento.
- El Script 1 crea data sintética en memoria temporal.
- El Script 2 ejecuta la consulta principal del topic.
- El Script 3 valida resultados, conteos o excepciones.
- En producción, usa vistas, staging o tablas reales solo con aprobación.
Simple script explanation
- #temporary tables reduce risk in training.
- Script 1 creates synthetic data in temporary memory.
- Script 2 runs the main topic query.
- Script 3 validates results, counts, or exceptions.
- In production, use views, staging, or real tables only with approval.
Ejemplo esperado
| Caso | Resultado |
|---|---|
| Full_Name trimmed | Expected result |
| Status_Code uppercased | Expected result |
| NULL Employee_ID excluded | Expected result |
Expected example
| Case | Result |
|---|---|
| Full_Name trimmed | Expected result |
| Status_Code uppercased | Expected result |
| NULL Employee_ID excluded | Expected result |
Conclusión
Centraliza reglas de limpieza para que el dashboard consuma una fuente más confiable.
Conclusion
Centralize cleaning rules so the dashboard consumes a more reliable source.