Skip to content
DP-700 Fabric Engineer Hub

Query and Transform Data in KQL Databases

Query and Transform Data in KQL Databases

Source

Summary

This module focuses on authoring Kusto Query Language (KQL) queries, setting up Update Policies, creating Materialized Views, and managing real-time data transformations in Microsoft Fabric KQL Databases.

KQL Transformation Architecture

1. Update Policies (In-Flight Transformation)

An Update Policy acts like a real-time database trigger that automatically transforms and moves ingested data from a raw staging table into a curated target table at ingestion time.

// Define a function that parses and cleans raw JSON payload
.create-or-alter function TransformRawLogs() {
    RawIngestionTable
    | project
        Timestamp = todatetime(RawData.timestamp),
        DeviceId = tostring(RawData.deviceId),
        Reading = todouble(RawData.value),
        Status = tostring(RawData.status)
    | where Status == "OK"
}


// Attach the function as an Update Policy on the CuratedLogs table
.alter table CuratedLogs policy update
@'[{ "IsEnabled": true, "Source": "RawIngestionTable", "Query": "TransformRawLogs()", "IsTransactional": true }]'

2. Materialized Views

  • Continuously pre-aggregates time-series data without reprocessing history during query runtime.
.create materialized-view HourlyDeviceMetrics on table CuratedLogs
{
    CuratedLogs
    | summarize AvgReading = avg(Reading), MaxReading = max(Reading), TotalEvents = count()
      by DeviceId, bin(Timestamp, 1h)
}

3. KQL Query Primitives

  • Tabular operators: where, project, extend, summarize, join, lookup, bin().
  • String parsing: parse_json(), extract(), split().
  • Time-series operators: make-series, series_decompose(), series_outliers().

Exam Traps & Gotchas

[!WARNING]

  • Transactional vs Non-Transactional Update Policy: Setting IsTransactional: true ensures that if the transformation fails, the raw ingestion transaction also rolls back, preventing data loss or out-of-sync states.
  • Update Policy Staging Table Retention: Set a short retention period (e.g., 0 days or soft delete 1 day) on the raw staging table if only the curated target table is needed, minimizing storage overhead.