Hi Akhil,
thnks for your reply and hints to double check.
- All output aliases used in the query must explicitly added to the Output section of the SQL editor: Can verify this is the case.
- CTEs should be defined cleanly (ideally within a single WITH block): Several CTEs but only one WITH
- Each SELECT INTO works independently from the other: Check
- Keep naming simple (no special characters): Check
WRT "Current limitation or bug": Bugs happen of course, I hope it gets fixed soon. "Current limitation" - Several Fabric Update Blog entries hint this limitation has been removed since the Preview. Maybe the feature has not been rolled out yet to my client's tenant? How can I check this?
Best
Martin
Stripped down SQL Operator:
(edit 2026-05-04: Fixed a typo - renamed initial "WITH FilterEvents" to "WITH FilteredEvents" - as used in prod)
WITH FilteredEvents AS (
-- Step 1:
-- Filter incoming events (json)
-- convert unix-ms timestamp to event timestamp + use this event timestamp
SELECT
*,
DATEADD(MILLISECOND, CAST(metadata.timestamp AS BIGINT), '1970-01-01') As [eventtime]
FROM [machine-data-stream] TIMESTAMP BY DATEADD(MILLISECOND, CAST(metadata.timestamp AS BIGINT), '1970-01-01')
WHERE
-- some simple mathces
),
MappedEvents AS (
-- Step 2: Extract some nested fields to "top-level"
SELECT
filtered.eventtime AS [eventtime],
filtered.metadata.cell AS [cellId],
-- some more of this kind ...
measureKV.PropertyName AS [measure_name],
TRY_CAST(GetRecordPropertyValue(
GetRecordPropertyValue(filtered, measureKV.PropertyName),
'value') AS NVARCHAR(MAX)) AS [measure_value_text],
filtered.metadata AS [metadata_raw]
FROM FilteredEvents AS filtered
CROSS APPLY
GetRecordProperties(filtered) AS measureKV
WHERE
-- Exclude ALL known structural top-level keys here: This is the ONE PLACE to update if new structural keys are added
measureKV.PropertyName NOT IN ('metadata', 'topic', 'PartitionId', 'EventProcessedUtcTime', 'EventEnqueuedUtcTime', 'eventtime')
),
ValueMappedEvents AS (
SELECT
*,
TRY_CAST(measure_value_text AS FLOAT) AS [measure_value]
FROM MappedEvents
),
AggregatedEvents AS (
-- Step 3: Aggregate over 60s Tumbling Windows
SELECT
System.Timestamp() AS [window_end],
cellId AS [cellId],
-- some more fields ...
measure_name AS [measure],
MIN(measure_value) AS [min_value],
AVG(measure_value) AS [avg_value],
MAX(measure_value) AS [max_value],
COUNT(*) AS [sample_count],
topone() OVER (ORDER BY eventtime ASC) AS [earliest_record],
topone() OVER (ORDER BY eventtime DESC) AS [latest_record]
FROM ValueMappedEvents
GROUP BY
cellId,
-- more gourping ...
measure,
TumblingWindow(minute, 1)
)
-- DESTINATION #1: Output to aggregated EH table
SELECT
-- tons of fields
INTO [aggregatedevents]
FROM AggregatedEvents as aggregated
-- DESTINATION #2: Output of subset of kpis to LH table
SELECT
System.Timestamp() AS [event_timestamp],
--tons of fields
INTO [operationalevents]
FROM ValueMappedEvents AS mapped
WHERE mapped.measure in ('a', 'b', 'c')
