Nivel Avanzado: SQL Maestría

Módulo 1: Arquitectura de Bases de Datos Distribuidas

Diseña sistemas que escalan horizontalmente, manejan millones de transacciones y garantizan disponibilidad 24/7.

Lección 1.1: Particionamiento de tablas (horizontal y vertical)

Divide tablas masivas en fragmentos manejables. Horizontal separa filas por rangos. Vertical separa columnas por grupos de acceso.

-- Particionamiento horizontal por fecha
                    CREATE PARTITION FUNCTION PF_FechaPedidos (DATE)
                    AS RANGE RIGHT FOR VALUES
                    ('2024-01-01', '2024-04-01', '2024-07-01', '2024-10-01');

                    CREATE PARTITION SCHEME PS_FechaPedidos
                    AS PARTITION PF_FechaPedidos
                    TO ([PRIMARY], [FG_Q1], [FG_Q2], [FG_Q3], [FG_Q4]);

                    CREATE TABLE Pedidos_2024
                    (
                    PedidoID INT IDENTITY(1,1),
                    FechaPedido DATE,
                    Total MONEY,
                    ClienteID INT
                    ) ON PS_FechaPedidos(FechaPedido);

Lección 1.2: Replicación y sincronización de datos

Implementa estrategias de replicación para alta disponibilidad y distribución geográfica.

-- Configurar publicación transaccional
                    USE [master]
                    EXEC sp_adddistributor @distributor = N'ServidorPrincipal'
                    EXEC sp_adddistributiondb @database = N'distribution'

                    -- Agregar suscriptor
                    EXEC sp_addsubscription
                    @publication = N'PubVentas',
                    @subscriber = N'ServidorSecundario',
                    @destination_db = N'VentasReplica',
                    @subscription_type = N'Push'

Lección 1.3: Sharding y estrategias de distribución

Diseña sistemas de sharding para escalar más allá de un solo servidor.

-- Función de sharding por hash de cliente
                    CREATE FUNCTION dbo.fn_ClienteShard(@ClienteID INT)
                    RETURNS TINYINT
                    AS
                    BEGIN
                    DECLARE @Hash INT = CHECKSUM(@ClienteID)
                    RETURN @Hash % 4 -- 4 shards
                    END

                    -- Vista distribuida
                    CREATE VIEW vw_ClientesGlobal
                    AS
                    SELECT * FROM Shard1.dbo.Clientes
                    UNION ALL
                    SELECT * FROM Shard2.dbo.Clientes
                    UNION ALL
                    SELECT * FROM Shard3.dbo.Clientes
                    UNION ALL
                    SELECT * FROM Shard4.dbo.Clientes

Lección 1.4: Estrategias de consistencia eventual

Implementa CAP theorem trade-offs en sistemas distribuidos.

Pro Tip: Usa particionamiento por fecha para datos de series temporales. Los queries recientes serán más rápidos al estar en particiones activas.

Ejercicios de Práctica del Módulo 1

Diseña un esquema de particionamiento para una tabla de logs con 1TB de datos.
Implementa una estrategia de replicación multi-región para una aplicación global.
Crea una función de sharding que distribuya datos equitativamente.
Diseña un sistema que maneje 100,000 transacciones por segundo.
Implementa consistencia eventual con conflict resolution.

Módulo 2: OLAP y Data Warehousing Avanzado

Construye data warehouses corporativos con cubos OLAP, ETL complejos y análisis en tiempo real.

Lección 2.1: Cubos OLAP y operaciones MDX

Crea cubos multidimensionales para análisis complejo con MDX (Multidimensional Expressions).

-- Consulta MDX para ventas por dimensión tiempo
                    SELECT
                    [Measures].[Ventas] ON COLUMNS,
                    [Tiempo].[Año].[2024].Children ON ROWS
                    FROM [VentasCube]
                    WHERE [Producto].[Categoria].[Electrónicos]

                    -- Cálculo avanzado con MDX
                    WITH MEMBER [Measures].[Crecimiento%] AS
                    ([Measures].[Ventas], [Tiempo].[Año].CurrentMember) /
                    ([Measures].[Ventas], [Tiempo].[Año].PrevMember) - 1,
                    FORMAT_STRING = "Percent"
                    SELECT [Measures].[Crecimiento%] ON COLUMNS
                    FROM [VentasCube]

Lección 2.2: Diseño de star y snowflake schemas

Optimiza modelos dimensionales para análisis empresarial.

-- Tabla de hechos de ventas
                    CREATE TABLE FactVentas
                    (
                    VentaID BIGINT IDENTITY(1,1),
                    FechaKey INT,
                    ClienteKey INT,
                    ProductoKey INT,
                    TiendaKey INT,
                    Cantidad INT,
                    PrecioUnitario MONEY,
                    Descuento MONEY,
                    PRIMARY KEY (FechaKey, ClienteKey, ProductoKey, TiendaKey)
                    );

                    -- Dimensión de tiempo
                    CREATE TABLE DimTiempo
                    (
                    FechaKey INT PRIMARY KEY,
                    Fecha DATE,
                    DiaSemana NVARCHAR(10),
                    Mes NVARCHAR(10),
                    Trimestre NVARCHAR(2),
                    Año INT,
                    EsFestivo BIT
                    );

Lección 2.3: ETL avanzado con SSIS

Automatiza pipelines de datos complejos con SQL Server Integration Services.

-- Procedimiento almacenado para ETL incremental
                    CREATE PROCEDURE sp_ETL_VentasDiarias
                    AS
                    BEGIN
                    -- Paso 1: Extracción incremental
                    SELECT * INTO #VentasNuevas
                    FROM StagingVentas
                    WHERE FechaCarga > (SELECT MAX(FechaUltimaCarga) FROM ControlCarga);

                    -- Paso 2: Transformación de datos
                    UPDATE #VentasNuevas
                    SET MontoNeto = MontoBruto - ISNULL(Descuento, 0);

                    -- Paso 3: Carga al data warehouse
                    INSERT INTO FactVentas (FechaKey, ClienteKey, ProductoKey, Monto)
                    SELECT d.FechaKey, c.ClienteKey, p.ProductoKey, v.MontoNeto
                    FROM #VentasNuevas v
                    JOIN DimTiempo d ON v.Fecha = d.Fecha
                    JOIN DimCliente c ON v.ClienteID = c.ClienteID
                    JOIN DimProducto p ON v.ProductoID = p.ProductoID;
                    END

Lección 2.4: Análisis de series temporales

Implementa predicciones y análisis de tendencias.

-- Análisis de tendencias con window functions
                    SELECT
                    Fecha,
                    Ventas,
                    AVG(Ventas) OVER (ORDER BY Fecha ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS MediaMovil7Dias,
                    LAG(Ventas, 7) OVER (ORDER BY Fecha) AS VentasSemanaAnterior,
                    (Ventas - LAG(Ventas, 7) OVER (ORDER BY Fecha)) /
                    LAG(Ventas, 7) OVER (ORDER BY Fecha) * 100 AS VariacionPorcentual
                    FROM DailySales
                    WHERE Fecha >= DATEADD(MONTH, -3, GETDATE())

Ejercicios de Práctica del Módulo 2

Diseña un star schema para un negocio de e-commerce con 10 dimensiones.
Crea una consulta MDX que analice el rendimiento por región y tiempo.
Implementa un ETL incremental para 100M de registros diarios.
Desarrolla análisis de cohortes para retención de clientes.
Crea cubos OLAP con cálculos complejos de margen y ROI.

Módulo 3: Machine Learning en SQL Server

Implementa algoritmos de ML directamente en SQL Server con R, Python y ML Services.

Lección 3.1: Configuración de ML Services

Habilita R y Python en SQL Server para análisis avanzado.

-- Habilitar ML Services
                    EXEC sp_configure 'external scripts enabled', 1;
                    RECONFIGURE;

                    -- Verificar instalación
                    EXEC sp_execute_external_script
                    @language = N'Python',
                    @script = N'print("Python está funcionando en SQL Server!")';

Lección 3.2: Regresión y predicciones

Crea modelos predictivos directamente en la base de datos.

-- Predicción de ventas con regresión lineal
                    EXEC sp_execute_external_script
                    @language = N'Python',
                    @script = N'
                    import pandas as pd
                    from sklearn.linear_model import LinearRegression
                    from sklearn.model_selection import train_test_split

                    # Preparar datos
                    df = InputDataSet
                    X = df[["Precio", "Competencia", "Temp"]].values
                    y = df["Ventas"].values

                    # Entrenar modelo
                    model = LinearRegression()
                    model.fit(X, y)

                    # Predicciones
                    df["Prediccion"] = model.predict(X)
                    OutputDataSet = df
                    ',
                    @input_data_1 = N'SELECT Precio, Competencia, Temp, Ventas FROM DatosVentas',
                    @output_data_1_name = N'OutputDataSet';

Lección 3.3: Clustering y segmentación

Segmenta clientes automáticamente con algoritmos de clustering.

-- Segmentación de clientes con K-means
                    EXEC sp_execute_external_script
                    @language = N'R',
                    @script = N'
                    library(cluster)

                    # Normalizar datos
                    df_scaled <- scale(InputDataSet[, c("Edad", "Ingreso" , "GastoTotal" )]) # K-means clustering
                        set.seed(123) kmeans_result <- kmeans(df_scaled, centers=4) # Agregar segmentos OutputDataSet <-
                        cbind(InputDataSet, Segmento=kmeans_result$cluster) ',
    @input_data_1 = N' SELECT ClienteID, Edad, Ingreso, GastoTotal FROM Clientes';

Lección 3.4: Detección de anomalías y fraudes

Identifica comportamientos sospechosos en tiempo real.

-- Detección de transacciones fraudulentas
                            CREATE PROCEDURE sp_DetectarFraude
                            AS
                            BEGIN
                            EXEC sp_execute_external_script
                            @language = N'Python',
                            @script = N'
                            from sklearn.ensemble import IsolationForest

                            # Isolation Forest para detección de anomalías
                            model = IsolationForest(contamination=0.1, random_state=42)
                            df["Fraude"] = model.fit_predict(df[["Monto", "Frecuencia", "Hora"]])

                            # Filtrar anomalías
                            fraudes = df[df["Fraude"] == -1]
                            OutputDataSet = fraudes
                            ',
                            @input_data_1 = N'SELECT TransaccionID, Monto, Frecuencia, Hora FROM Transacciones',
                            @output_data_1_name = N'OutputDataSet';
                            END

Ejercicios de Práctica del Módulo 3

Crea un modelo predictivo de demanda usando series temporales.
Implementa segmentación RFM (Recency, Frequency, Monetary) con clustering.
Desarrolla un sistema de recomendaciones colaborativas.
Crea un pipeline de ML para predicción de churn.
Implementa detección de fraudes en tiempo real.

Módulo 4: Seguridad Avanzada y Auditoría

Protege datos sensibles con cifrado, máscaras y auditoría completa de accesos.

Lección 4.1: Transparent Data Encryption (TDE)

Protege datos en reposo con cifrado a nivel de base de datos.

-- Configurar TDE
                        USE master;
                        CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'ClaveMaestra2024!@#';

                        CREATE CERTIFICATE TDE_Certificate
                        WITH SUBJECT = 'Certificado TDE';

                        USE MiBaseDatos;
                        CREATE DATABASE ENCRYPTION KEY
                        WITH ALGORITHM = AES_256
                        ENCRYPTION BY SERVER CERTIFICATE TDE_Certificate;

                        ALTER DATABASE MiBaseDatos SET ENCRYPTION ON;

                        -- Verificar estado
                        SELECT db.name, db.is_encrypted, dm.encryption_state
                        FROM sys.databases db
                        LEFT JOIN sys.dm_database_encryption_keys dm
                        ON db.database_id = dm.database_id;

Lección 4.2: Dynamic Data Masking

Máscara datos sensibles dinámicamente según el rol del usuario.

-- Aplicar máscaras de datos
                        ALTER TABLE Clientes
                        ALTER COLUMN Email ADD MASKED WITH (FUNCTION = 'email()');

                        ALTER TABLE Clientes
                        ALTER COLUMN TarjetaCredito ADD MASKED WITH (FUNCTION = 'partial(4,"XXXX-XXXX-XXXX-",4)');

                        -- Verificar máscaras
                        SELECT TOP 5 ClienteID, Email, TarjetaCredito
                        FROM Clientes;

Lección 4.3: Auditoría y compliance (GDPR, SOX)

Implementa auditoría completa para cumplimiento normativo.

-- Configurar auditoría de accesos
                        CREATE DATABASE AUDIT SPECIFICATION Auditoria_Accesos_Clientes
                        FOR SERVER AUDIT Auditoria_Servidor
                        ADD (SELECT, INSERT, UPDATE, DELETE ON dbo.Clientes BY public),
                        ADD (SELECT, INSERT, UPDATE, DELETE ON dbo.TarjetasCredito BY public)
                        WITH (STATE = ON);

                        -- Consultar auditoría
                        SELECT TOP 100 *
                        FROM sys.fn_get_audit_file('C:\Auditoria\*.sqlaudit', DEFAULT, DEFAULT)
                        WHERE statement LIKE '%TarjetasCredito%';

Lección 4.4: Row-Level Security (RLS)

Restringe acceso a filas específicas según políticas de seguridad.

-- Función de seguridad
                        CREATE FUNCTION fn_SeguridadCliente(@ClienteID INT)
                        RETURNS TABLE
                        WITH SCHEMABINDING
                        AS
                        RETURN (
                        SELECT 1 AS Resultado
                        WHERE @ClienteID = SESSION_CONTEXT(N'ClienteID')
                        OR USER_NAME() = 'admin'
                        );

                        -- Aplicar política
                        CREATE SECURITY POLICY ClientePolicy
                        ADD FILTER PREDICATE dbo.fn_SeguridadCliente(ClienteID) ON dbo.Clientes
                        WITH (STATE = ON);

Importante: Siempre respalda tus certificados TDE. Perderlos significa pérdida permanente de datos.

Ejercicios de Práctica del Módulo 4

Implementa TDE en una base de datos con 500GB de datos sensibles.
Crea máscaras de datos para cumplir con GDPR.
Configura auditoría para todas las operaciones de tarjetas de crédito.
Implementa RLS para que los gerentes solo vean datos de su región.
Crea un sistema de clasificación automática de datos sensibles.

Módulo 5: Big Data y Streaming Analytics

Procesa millones de eventos en tiempo real con SQL Server Big Data Clusters y Azure Stream Analytics.

Lección 5.1: SQL Server Big Data Clusters

Implementa clústeres que combinan SQL Server, Spark y HDFS.

-- Crear tabla externa en HDFS
                        CREATE EXTERNAL DATA SOURCE HDFS_DataSource
                        WITH (
                        TYPE = HADOOP,
                        LOCATION = 'hdfs://bigdata-cluster:9000'
                        );

                        CREATE EXTERNAL TABLE dbo.WebLogs
                        (
                        LogID BIGINT,
                        Timestamp DATETIME2,
                        UserID INT,
                        Page NVARCHAR(255),
                        Duration INT,
                        IPAddress NVARCHAR(45)
                        )
                        WITH (
                        LOCATION = '/logs/web/',
                        DATA_SOURCE = HDFS_DataSource,
                        FILE_FORMAT = ParquetFormat
                        );

Lección 5.2: Streaming con Azure Stream Analytics

Procesa millones de eventos por segundo con SQL en tiempo real.

-- Análisis de tweets en tiempo real
                        SELECT
                        System.Timestamp AS WindowEnd,
                        Sentiment,
                        COUNT(*) AS TweetCount
                        FROM TwitterStream
                        TIMESTAMP BY CreatedAt
                        GROUP BY
                        Sentiment,
                        TumblingWindow(second, 10)

                        -- Detección de picos de tráfico
                        SELECT
                        System.Timestamp AS WindowEnd,
                        AVG(TrafficVolume) AS AvgTraffic,
                        STDEV(TrafficVolume) AS StdDevTraffic,
                        CASE
                        WHEN TrafficVolume > AVG(TrafficVolume) + 3 * STDEV(TrafficVolume)
                        THEN 'PICO_DETECTADO'
                        ELSE 'NORMAL'
                        END AS AlertStatus
                        FROM TrafficStream
                        TIMESTAMP BY EventTime
                        GROUP BY TumblingWindow(minute, 5)

Lección 5.3: Lambda architecture con SQL

Combina procesamiento por lotes y en tiempo real para análisis híbridos.

-- Vista que combina batch y streaming
                        CREATE VIEW vw_AnalyticsRealTime
                        AS
                        -- Datos históricos del batch
                        SELECT Fecha, Producto, Ventas, 'Batch' AS Origen
                        FROM FactVentasHistoricas
                        WHERE Fecha < DATEADD(HOUR, -1, GETDATE()) UNION ALL -- Datos en tiempo real del último hour
                            SELECT CAST(EventTime AS DATE) AS Fecha, Producto, SUM(Cantidad) AS Ventas, 'Streaming' AS
                            Origen FROM IoTVentasStream WHERE EventTime>= DATEADD(HOUR, -1, GETDATE())
                            GROUP BY CAST(EventTime AS DATE), Producto

Lección 5.4: Optimización para IoT y sensores

Diseña arquitecturas para ingestión masiva de datos de sensores.

-- Tabla optimizada para datos de sensores IoT
                        CREATE TABLE IoT_Data
                        (
                        SensorID BIGINT,
                        Timestamp DATETIME2(3),
                        Temperatura DECIMAL(5,2),
                        Humedad DECIMAL(5,2),
                        Presion DECIMAL(7,2),
                        INDEX IX_IoT_Time CLUSTERED COLUMNSTORE
                        ) WITH (MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_AND_DATA);

                        -- Procesamiento de ventanas de tiempo
                        SELECT
                        SensorID,
                        DATEADD(minute, DATEDIFF(minute, 0, Timestamp) / 5 * 5, 0) AS Ventana5Min,
                        AVG(Temperatura) AS TempPromedio,
                        PERCENTILE_CONT(0.95) WITHIN GROUP (ORDER BY Temperatura) AS TempPercentil95
                        FROM IoT_Data
                        WHERE Timestamp >= DATEADD(hour, -1, GETDATE())
                        GROUP BY SensorID, DATEADD(minute, DATEDIFF(minute, 0, Timestamp) / 5 * 5, 0)

Ejercicios de Práctica del Módulo 5

Diseña un sistema para procesar 1 millón de eventos IoT por segundo.
Implementa un dashboard de analytics en tiempo real con 5 segundos de latencia.
Crea una arquitectura Lambda para análisis de ventas y predicciones.
Desarrolla detección de anomalías en streaming para sensores industriales.
Implementa procesamiento de datos financieros de alta frecuencia.