ADX Patterns: Performing Bulk Updates in an Append-Only World

Vyankatesh Goski

4 min read

·

Jan 6, 2026

--

Listen

Share

If you come from a traditional SQL background, your first interaction with Azure Data Explorer (ADX/Kusto) might be a shock. You define your schema, ingest terabytes of logs, and everything is blazing fast.

But then, the requirements change. You need to fix a typo in a column, backfill a category, or correct a logic error for specific records. You type UPDATE logs SET category = 'Error', and... nothing happens.

ADX is immutable by design. It is optimized for high-throughput appending, not random access updates.

However, recent updates to KQL have introduced the .update command. In this post, I’ll share how I implemented a robust mechanism to update specific column values—handling both single-row fixes and massive bulk updates efficiently using C# and KQL.

The Problem: Immutability vs. Agility

In my recent project, we stored critical transaction logs in ADX. We encountered a scenario where we needed to retroactively update the category column for specific requests based on a re-calculation logic.

We explored the standard options:

1. .set-or-replace: This replaces the entire extent (data shard). It is too heavy and expensive for updating just a few rows.
2. .set-or-append: This only adds new data; it doesn’t fix old data, leading to duplicates.

We needed a transactional way to remove the old record and insert the corrected one simultaneously. Enter the .update command.

The Strategy: Delete “D” and Append “A”

The .update command in ADX works by defining two datasets:

1. D (Delete): The records you want to remove (the old versions).
2. A (Append): The records you want to add (the new versions).

Here is the logic for a single record update:

.update table logs
delete D
append A <|
    let D = logs
        | where requestRefNo == "GUID-123"; // Identify the old row
    let A = logs
        | where requestRefNo == "GUID-123"  // Take the old row...
        | extend category = dynamic({"NewCategory"}); // ...and modify the column

This works perfectly for one-offs. But what happens when you have a C# application that detects mismatches and needs to update 5,000 rows at once?

The “Aha!” Moment: Bulk Updates via KQL Datatables

Running a loop in C# to execute the query above 5,000 times would kill performance. It creates network overhead and puts unnecessary strain on the ADX engine’s command queue.

Instead, I devised a solution using KQL’s datatable operator. We can pass the entire batch of updates as a constructed table inside the query string.

Step 1: constructing the Payload in C#

First, we generate a KQL-compliant string that represents our lookup data (the IDs to update and their new values).

C#

// 'allMismatchResults' is a Dictionary<string, string> 
// containing { RequestRefNo, NewCategoryJSON }
// 1. Create a comma-separated list of GUIDs for the WHERE clause
string requestRefNos = string.Join(", ", 
    allMistmatchResults.Keys.Select(k => $"guid('{EscapeKqlString(k)}')")); 
// 2. Create the data rows for the KQL datatable
string lookupData = string.Join(",\n",
    allMistmatchResults.Select(kvp => 
        $"guid('{EscapeKqlString(kvp.Key)}'), dynamic({kvp.Value})"));

Step 2: The Bulk Update Query

Now, we construct the query. This is where the magic happens. We create a temporary table updates inside the query, join it with the existing data, and perform the swap.

C#

string bulkUpdateQuery = $"""
    .update table logs
    delete D
    append A <|
    
    // Define the new values as an in-memory table
    let updates = datatable(requestRefNo: guid, mismatch_categories: dynamic)
    [
        {lookupData}
    ];
  // Define what to delete (D)
    let D = logs
        | where requestRefNo in ({requestRefNos});
    // Define what to append (A)
    // We take the rows to be deleted (D), join them with our updates, 
    // and overwrite the specific column.
    let A = D 
        | join kind=leftouter (updates) on requestRefNo
        | extend category = mismatch_categories // Overwrite the column
        | project-away mismatch_categories, requestRefNo1; // Cleanup join columns
    """;

Why This Approach Works

1. Atomicity: The .update command is transactional. Either all rows update, or none do. You won't end up with "half-updated" states.
2. Performance: We are sending a single command to ADX. The join happens in-memory on the cluster, which is what Kusto is optimized for.
3. Clean Syntax: By utilizing C# 11 Raw String Literals ($"""..."""), the code remains readable and maintainable.

Important Considerations

While this pattern is powerful, there are limits:

• Query Size: If you are updating millions of rows, the string lookupData will become too large for the request body. In that case, the best practice is to upload a CSV to Azure Blob Storage and use an externaldata operator instead of a datatable literal.
• CPU Cost: The .update command effectively re-ingests data. Frequent updates can fragment your data shards (extents), requiring ADX to work harder to merge them later. Use this for corrections, not as part of your standard hot-path write logic.

Conclusion

ADX is a beast for log analytics, but it requires a shift in mindset when it comes to data manipulation. By leveraging the .update command combined with datatable injection, we can bridge the gap between immutable logs and changing business requirements.

Have you faced similar challenges with KQL? Let me know in the comments how you handled data corrections!