SQL Operator with multiple destinations

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')