Transform Data with SQL & KQL

Two query languages, two data stores

T-SQL transformations in warehouses

Views for reusable transformations

-- Create a view that denormalizes orders with customer info
CREATE VIEW vw_OrdersWithCustomers AS
SELECT
    o.order_id,
    o.order_date,
    c.customer_name,
    c.region,
    o.quantity,
    o.revenue
FROM FactOrders o
INNER JOIN DimCustomer c ON o.customer_key = c.customer_key
WHERE c.is_current = 1;   -- SCD Type 2: only current dimension rows

Stored procedures for repeatable ETL

-- Stored procedure: load and deduplicate daily orders
CREATE PROCEDURE usp_LoadDailyOrders
    @load_date DATE
AS
BEGIN
    -- Step 1: Stage new data
    INSERT INTO staging.DailyOrders
    SELECT * FROM external_source.Orders
    WHERE order_date = @load_date;

    -- Step 2: Deduplicate (keep latest per order_id)
    WITH RankedOrders AS (
        SELECT *,
            ROW_NUMBER() OVER (
                PARTITION BY order_id
                ORDER BY modified_date DESC
            ) AS rn
        FROM staging.DailyOrders
    )
    DELETE FROM RankedOrders WHERE rn > 1;

    -- Step 3: MERGE into fact table
    MERGE INTO FactOrders AS target
    USING staging.DailyOrders AS source
    ON target.order_id = source.order_id
    WHEN MATCHED THEN
        UPDATE SET target.quantity = source.quantity,
                   target.revenue = source.revenue,
                   target.status = source.status
    WHEN NOT MATCHED THEN
        INSERT (order_id, order_date, customer_key, quantity, revenue, status)
        VALUES (source.order_id, source.order_date, source.customer_key,
                source.quantity, source.revenue, source.status);

    -- Step 4: Clean staging
    TRUNCATE TABLE staging.DailyOrders;
END;

Handling duplicates with T-SQL

-- Find duplicates
SELECT order_id, COUNT(*) as dupe_count
FROM FactOrders
GROUP BY order_id
HAVING COUNT(*) > 1;

-- Remove duplicates (keep the row with latest modified_date)
WITH Dupes AS (
    SELECT *,
        ROW_NUMBER() OVER (PARTITION BY order_id ORDER BY modified_date DESC) AS rn
    FROM FactOrders
)
DELETE FROM Dupes WHERE rn > 1;

KQL transformations in Eventhouses

KQL uses a pipe syntax — each line transforms the output of the previous line.

Basic KQL patterns

// Count events by hour for the last 24 hours
OrderEvents
| where Timestamp > ago(24h)                    // Filter: last 24 hours
| summarize EventCount = count() by bin(Timestamp, 1h)  // Group by hour
| order by Timestamp asc                         // Sort chronologically
| render timechart                               // Visualize as line chart

Aggregation patterns

// Revenue by region, with percentiles
OrderEvents
| where EventType == "purchase"
| summarize
    TotalRevenue = sum(Amount),
    AvgRevenue = avg(Amount),
    P95Revenue = percentile(Amount, 95),
    OrderCount = count()
    by Region
| order by TotalRevenue desc

Time-series functions

// Create a time series of hourly order counts (fills gaps with 0)
OrderEvents
| make-series OrderCount = count() on Timestamp step 1h
| render timechart

PlaybackEvents
| where Timestamp > ago(1h)
| where EventType == "video_play"
| summarize
    AvgWatchSeconds = avg(WatchDuration),
    TotalViews = count(),
    DropOffRate = countif(WatchDuration < 30) * 100.0 / count()
    by VideoId, VideoTitle
| top 10 by TotalViews desc

T-SQL vs KQL

Handling data quality issues

Missing data

Duplicate data

Next up: Eventstreams & Spark Streaming: Real-Time Ingestion — process streaming data as it arrives using Eventstreams and Spark Structured Streaming.