Concepts

• 1: Data purge
• 2: Data soft delete
• 3: Delete data
• 4: Fact and dimension tables
• 5: Kusto query result set exceeds internal limit
• 6: Overflows
• 7: Partial query failures
• 8: Query consistency
• 9: Query limits
• 10: Runaway queries
• 11: Sandboxes

1 - Data purge

The data platform supports the ability to delete individual records, by using Kusto .purge and related commands. You can also purge an entire table or purge records in a materialized view.

Purge guidelines

Carefully design your data schema and investigate relevant policies before storing personal data.

1. In a best-case scenario, the retention period on this data is sufficiently short and data is automatically deleted.
2. If retention period usage isn’t possible, isolate all data that is subject to privacy rules in a few tables. Optimally, use just one table and link to it from all other tables. This isolation allows you to run the data purge process on a few tables holding sensitive data, and avoid all other tables.
3. The caller should make every attempt to batch the execution of .purge commands to 1-2 commands per table per day. Don’t issue multiple commands with unique user identity predicates. Instead, send a single command whose predicate includes all user identities that require purging.

Purge process

The process of selectively purging data happens in the following steps:

1. Phase 1: Give an input with a table name and a per-record predicate, indicating which records to delete. Kusto scans the table looking to identify data extents that would participate in the data purge. The extents identified are those having one or more records for which the predicate returns true.

2. Phase 2: (Soft Delete) Replace each data extent in the table (identified in step (1)) with a reingested version. The reingested version shouldn’t have the records for which the predicate returns true. If new data isn’t being ingested into the table, then by the end of this phase, queries will no longer return data for which the predicate returns true. The duration of the purge soft delete phase depends on the following parameters:

   • The number of records that must be purged
   • Record distribution across the data extents in the cluster
   • The number of nodes in the cluster
   • The spare capacity it has for purge operations
   • Several other factors

   The duration of phase 2 can vary between a few seconds to many hours.

3. Phase 3: (Hard Delete) Work back all storage artifacts that may have the “poison” data, and delete them from storage. This phase is done at least five days after the completion of the previous phase, but no longer than 30 days after the initial command. These timelines are set to follow data privacy requirements.

Issuing a .purge command triggers this process, which takes a few days to complete. If the density of records for which the predicate applies is sufficiently large, the process will effectively reingest all the data in the table. This reingestion has a significant impact on performance and COGS (cost of goods sold).

Purge limitations and considerations

• The purge process is final and irreversible. It isn’t possible to undo this process or recover data that has been purged. Commands such as undo table drop can’t recover purged data. Rollback of the data to a previous version can’t go to before the latest purge command.

• Before running the purge, verify the predicate by running a query and checking that the results match the expected outcome. You can also use the two-step process that returns the expected number of records that will be purged.

• The .purge command is executed against the Data Management endpoint: https://ingest-[YourClusterName].[region].kusto.windows.net. The command requires database admin permissions on the relevant databases.

• Due to the purge process performance impact, and to guarantee that purge guidelines have been followed, the caller is expected to modify the data schema so that minimal tables include relevant data, and batch commands per table to reduce the significant COGS impact of the purge process.

• The predicate parameter of the .purge command is used to specify which records to purge. Predicate size is limited to 1 MB. When constructing the predicate:

  • Use the ‘in’ operator, for example, where [ColumnName] in ('Id1', 'Id2', .. , 'Id1000').
  • Note the limits of the ‘in’ operator (list can contain up to 1,000,000 values).
  • If the query size is large, use externaldata operator, for example where UserId in (externaldata(UserId:string) ["https://...blob.core.windows.net/path/to/file?..."]). The file stores the list of IDs to purge.
  • The total query size, after expanding all externaldata blobs (total size of all blobs), can’t exceed 64 MB.

Purge performance

Only one purge request can be executed on the cluster, at any given time. All other requests are queued in Scheduled state. Monitor the purge request queue size, and keep within adequate limits to match the requirements applicable for your data.

To reduce purge execution time:

• Follow the purge guidelines to decrease the amount of purged data.

• Adjust the caching policy since purge takes longer on cold data.

• Scale out the cluster

• Increase cluster purge capacity, after careful consideration, as detailed in Extents purge rebuild capacity.

Trigger the purge process

Purge table TableName records command

Purge command may be invoked in two ways for differing usage scenarios:

• Programmatic invocation: A single step that is intended to be invoked by applications. Calling this command directly triggers purge execution sequence.

  Syntax

  // Connect to the Data Management service
  #connect "https://ingest-[YourClusterName].[region].kusto.windows.net"
  
  // To purge table records
  .purge table [TableName] records in database [DatabaseName] with (noregrets='true') <| [Predicate]
  
   // To purge materialized view records
  .purge materialized-view [MaterializedViewName] records in database [DatabaseName] with (noregrets='true') <| [Predicate]
  

• Human invocation: A two-step process that requires an explicit confirmation as a separate step. First invocation of the command returns a verification token, which should be provided to run the actual purge. This sequence reduces the risk of inadvertently deleting incorrect data.

[!NOTE] The first step in the two-step invocation requires running a query on the entire dataset, to identify records to be purged. This query may time-out or fail on large tables, especially with significant amount of cold cache data. In case of failures, validate the predicate yourself and after verifying correctness use the single-step purge with the noregrets option.

Syntax

// Connect to the Data Management service - this command only works in Kusto.Explorer
#connect "https://ingest-[YourClusterName].[region].kusto.windows.net"

// Step #1 - retrieve a verification token (no records will be purged until step #2 is executed)
.purge table [TableName] records in database [DatabaseName] <| [Predicate]

// Step #2 - input the verification token to execute purge
.purge table [TableName] records in database [DatabaseName] with (verificationtoken=h'<verification token from step #1>') <| [Predicate]

To purge a materialized view, replace the table keyword with materialized-view, and replace TableName with the MaterializedViewName.

Purge predicate limitations

• The predicate must be a simple selection (for example, where [ColumnName] == ‘X’ / where [ColumnName] in (‘X’, ‘Y’, ‘Z’) and [OtherColumn] == ‘A’).
• Multiple filters must be combined with an ‘and’, rather than separate where clauses (for example, where [ColumnName] == 'X' and OtherColumn] == 'Y' and not where [ColumnName] == 'X' | where [OtherColumn] == 'Y').
• The predicate can’t reference tables other than the table being purged (TableName). The predicate can only include the selection statement (where). It can’t project specific columns from the table (output schema when running ‘table | Predicate’ must match table schema).
• System functions (such as, ingestion_time(), extent_id()) aren’t supported.

Example: Two-step purge

To start purge in a two-step activation scenario, run step #1 of the command:

   // Connect to the Data Management service
   #connect "https://ingest-[YourClusterName].[region].kusto.windows.net"

   .purge table MyTable records in database MyDatabase <| where CustomerId in ('X', 'Y')

   .purge materialized-view MyView records in database MyDatabase <| where CustomerId in ('X', 'Y')

Output

Then, validate the NumRecordsToPurge before running step #2.

To complete a purge in a two-step activation scenario, use the verification token returned from step #1 to run step #2:

.purge table MyTable records in database MyDatabase
 with(verificationtoken=h'e43c7....')
<| where CustomerId in ('X', 'Y')

.purge materialized-view MyView records in database MyDatabase
 with(verificationtoken=h'e43c7....')
<| where CustomerId in ('X', 'Y')

Output

Example: Single-step purge

To trigger a purge in a single-step activation scenario, run the following command:

// Connect to the Data Management service
 #connect "https://ingest-[YourClusterName].[region].kusto.windows.net"

.purge table MyTable records in database MyDatabase with (noregrets='true') <| where CustomerId in ('X', 'Y')

.purge materialized-view MyView records in database MyDatabase with (noregrets='true') <| where CustomerId in ('X', 'Y')

Output

Cancel purge operation command

If needed, you can cancel pending purge requests.

Syntax

 // Cancel of a single purge operation
 .cancel purge <OperationId>

  // Cancel of all pending purge requests in a database
 .cancel all purges in database <DatabaseName>

 // Cancel of all pending purge requests, for all databases
 .cancel all purges

Example: Cancel a single purge operation

 .cancel purge aa894210-1c60-4657-9d21-adb2887993e1

Output

The output of this command is the same as the ‘show purges OperationId’ command output, showing the updated status of the purge operation being canceled. If the attempt is successful, the operation state is updated to Canceled. Otherwise, the operation state isn’t changed.

Example: Cancel all pending purge operations in a database

 .cancel all purges in database MyDatabase

Output

The output of this command is the same as the show purges command output, showing all operations in the database with their updated status. Operations that were canceled successfully will have their status updated to Canceled. Otherwise, the operation state isn’t changed.

Track purge operation status

Status = ‘Completed’ indicates successful completion of the first phase of the purge operation, that is records are soft-deleted and are no longer available for querying. Customers aren’t expected to track and verify the second phase (hard-delete) completion. This phase is monitored internally.

Show purges command

Show purges command shows purge operation status by specifying the operation ID within the requested time period.

.show purges <OperationId>
.show purges [in database <DatabaseName>]
.show purges from '<StartDate>' [in database <DatabaseName>]
.show purges from '<StartDate>' to '<EndDate>' [in database <DatabaseName>]

Examples

.show purges
.show purges c9651d74-3b80-4183-90bb-bbe9e42eadc4
.show purges from '2018-01-30 12:00'
.show purges from '2018-01-30 12:00' to '2018-02-25 12:00'
.show purges from '2018-01-30 12:00' to '2018-02-25 12:00' in database MyDatabase

Output

• OperationId - the DM operation ID returned when executing purge.
• DatabaseName** - database name (case sensitive).
• TableName - table name (case sensitive).
• ScheduledTime - time of executing purge command to the DM service.
• Duration - total duration of the purge operation, including the execution DM queue wait time.
• EngineOperationId - the operation ID of the actual purge executing in the engine.
• State - purge state, can be one of the following values:
  • Scheduled - purge operation is scheduled for execution. If job remains Scheduled, there’s probably a backlog of purge operations. See purge performance to clear this backlog. If a purge operation fails on a transient error, it will be retried by the DM and set to Scheduled again (so you may see an operation transition from Scheduled to InProgress and back to Scheduled).
  • InProgress - the purge operation is in-progress in the engine.
  • Completed - purge completed successfully.
  • BadInput - purge failed on bad input and won’t be retried. This failure may be due to various issues such as a syntax error in the predicate, an illegal predicate for purge commands, a query that exceeds limits (for example, over 1M entities in an externaldata operator or over 64 MB of total expanded query size), and 404 or 403 errors for externaldata blobs.
  • Failed - purge failed and won’t be retried. This failure may happen if the operation was waiting in the queue for too long (over 14 days), due to a backlog of other purge operations or a number of failures that exceed the retry limit. The latter will raise an internal monitoring alert and will be investigated by the team.
• StateDetails - a description of the State.
• EngineStartTime - the time the command was issued to the engine. If there’s a large difference between this time and ScheduledTime, there’s usually a significant backlog of purge operations and the cluster isn’t keeping up with the pace.
• EngineDuration - time of actual purge execution in the engine. If purge was retried several times, it’s the sum of all the execution durations.
• Retries - number of times the operation was retried by the DM service due to a transient error.
• ClientRequestId - client activity ID of the DM purge request.
• Principal - identity of the purge command issuer.

Purging an entire table

Purging a table includes dropping the table, and marking it as purged so that the hard delete process described in Purge process runs on it. Dropping a table without purging it doesn’t delete all its storage artifacts. These artifacts are deleted according to the hard retention policy initially set on the table. The purge table allrecords command is quick and efficient and is preferable to the purge records process, if applicable for your scenario.

Purge table TableName allrecords command

Similar to ‘.purge table records ’ command, this command can be invoked in a programmatic (single-step) or in a manual (two-step) mode.

1. Programmatic invocation (single-step):

   Syntax

   // Connect to the Data Management service
   #connect "https://ingest-[YourClusterName].[Region].kusto.windows.net"
   
   .purge table [TableName] in database [DatabaseName] allrecords with (noregrets='true')
   

2. Human invocation (two-steps):

   Syntax

   
   // Connect to the Data Management service
   #connect "https://ingest-[YourClusterName].[Region].kusto.windows.net"
   
   // Step #1 - retrieve a verification token (the table will not be purged until step #2 is executed)
   
   .purge table [TableName] in database [DatabaseName] allrecords
   
   // Step #2 - input the verification token to execute purge
   .purge table [TableName] in database [DatabaseName] allrecords with (verificationtoken=h'<verification token from step #1>')
   

   Parameters	Description
   DatabaseName	Name of the database.
   TableName	Name of the table.
   noregrets	If set, triggers a single-step activation.
   verificationtoken	In two-step activation scenario (noregrets isn’t set), this token can be used to execute the second step and commit the action. If verificationtoken isn’t specified, it will trigger the command’s first step. In this step, a token is returned to pass back to the command and do step #2.

Example: Two-step purge

1. To start purge in a two-step activation scenario, run step #1 of the command:

   // Connect to the Data Management service
    #connect "https://ingest-[YourClusterName].[Region].kusto.windows.net"
   
   .purge table MyTable in database MyDatabase allrecords
   

   Output

   VerificationToken
   e43c7184ed22f4f23c7a9d7b124d196be2e570096987e5baadf65057fa65736b

2. To complete a purge in a two-step activation scenario, use the verification token returned from step #1 to run step #2:

   .purge table MyTable in database MyDatabase allrecords
   with (verificationtoken=h'eyJT.....')
   

   The output is the same as the ‘.show tables’ command output (returned without the purged table).

   Output

   TableName	DatabaseName	Folder	DocString
   OtherTable	MyDatabase	—	—

Example: Single-step purge

To trigger a purge in a single-step activation scenario, run the following command:

// Connect to the Data Management service
#connect "https://ingest-[YourClusterName].[Region].kusto.windows.net"

.purge table MyTable in database MyDatabase allrecords with (noregrets='true')

The output is the same as the ‘.show tables’ command output (returned without the purged table).

Output

Related content

• Enable data purge on your Azure Data Explorer cluster

2 - Data soft delete

The ability to delete individual records is supported. Record deletion is commonly achieved using one of the following methods:

• To delete records with a system guarantee that the storage artifacts containing these records are deleted as well, use .purge
• To delete records without such a guarantee, use .delete as described in this article - this command marks records as deleted but doesn’t necessarily delete the data from storage artifacts. This deletion method is faster than purge.

For information on how to use the command, see Syntax

Use cases

This deletion method should only be used for the unplanned deletion of individual records. For example, if you discover that an IoT device is reporting corrupt telemetry for some time, you should consider using this method to delete the corrupt data.

If you need to frequently delete records for deduplication or updates, we recommend using materialized views. See choose between materialized views and soft delete for data deduplication.

Deletion process

The soft delete process is performed using the following steps:

1. Run predicate query: The table is scanned to identify data extents that contain records to be deleted. The extents identified are those with one or more records returned by the predicate query.
2. Extents replacement: The identified extents are replaced with new extents that point to the original data blobs, and also have a new hidden column of type bool that indicates per record whether it was deleted or not. Once completed, if no new data is ingested, the predicate query won’t return any records if run again.

Limitations and considerations

• The deletion process is final and irreversible. It isn’t possible to undo this process or recover data that has been deleted, even though the storage artifacts aren’t necessarily deleted following the operation.

• Soft delete is supported for native tables and materialized views. It isn’t supported for external tables.

• Before running soft delete, verify the predicate by running a query and checking that the results match the expected outcome. You can also run the command in whatif mode, which returns the number of records that are expected to be deleted.

• Don’t run multiple parallel soft delete operations on the same table, as this may result in failures of some or all the commands. However, it’s possible to run multiple parallel soft delete operations on different tables.

• Don’t run soft delete and purge commands on the same table in parallel. First wait for one command to complete and only then run the other command.

• Soft delete is executed against your cluster URI: https://[YourClusterName].[region].kusto.windows.net. The command requires database admin permissions on the relevant database.

• Deleting records from a table that is a source table of a materialized view, can have an impact on the materialized view. If records being deleted were not yet processed by the materialization cycle, these records will be missing in the view, since they will never be processed. Similarly, the deletion will not have an impact on the materialized view if the records have already been processed.

• Limitations on the predicate:

  • It must contain at least one where operator.
  • It can only reference the table from which records are to be deleted.
  • Only the following operators are allowed: extend, order, project, take and where. Within toscalar(), the summarize operator is also allowed.

Deletion performance

The main considerations that can impact the deletion process performance are:

• Run predicate query: The performance of this step is very similar to the performance of the predicate itself. It might be slightly faster or slower depending on the predicate, but the difference is expected to be insignificant.
• Extents replacement: The performance of this step depends on the following:
  • Record distribution across the data extents in the cluster
  • The number of nodes in the cluster

Unlike .purge, the .delete command doesn’t reingest the data. It just marks records that are returned by the predicate query as deleted and is therefore much faster.

Query performance after deletion

Query performance isn’t expected to noticeably change following the deletion of records.

Performance degradation isn’t expected because the filter that is automatically added on all queries that filter out records that were deleted is efficient.

However, query performance is also not guaranteed to improve. While performance improvement may happen for some types of queries, it may not happen for some others. In order to improve query performance, extents in which most of the records are deleted are periodically compacted by replacing them with new extents that only contain the records that haven’t been deleted.

Impact on COGS (cost of goods sold)

In most cases, the deletion of records won’t result in a change of COGS.

• There will be no decrease, because no records are actually deleted. Records are only marked as deleted using a hidden column of type bool, the size of which is negligible.
• In most cases, there will be no increase because the .delete operation doesn’t require the provisioning of extra resources.
• In some cases, extents in which the majority of the records are deleted are periodically compacted by replacing them with new extents that only contain the records that haven’t been deleted. This causes the deletion of the old storage artifacts that contain a large number of deleted records. The new extents are smaller and therefore consume less space in both the Storage account and in the hot cache. However, in most cases, the effect of this on COGS is negligible.

3 - Delete data

Delete data from a table is supported in several ways. Use the following information to help you choose which deletion method is best for your use case.

The following sections describe the different deletion methods.

Delete all data in a table

To delete all data in a table, use the .clear table data command. This command is the most efficient way to remove all data from a table.

Syntax:

.clear table <TableName> data

Delete data using a retention policy

Automatically delete data based on a retention policy. You can set the retention policy at the database or table level. There’s no guarantee as to when the deletion occurs, but it will not be deleted before the retention period. This is an efficient and convenient way to remove old data.

Consider a database or table that is set for 90 days of retention. If only 60 days of data are needed, delete the older data as follows:

.alter-merge database <DatabaseName> policy retention softdelete = 60d

.alter-merge table <TableName> policy retention softdelete = 60d

Delete data by dropping extents

Extent (data shard) is the internal structure where data is stored. Each extent can hold up to millions of records. Extents can be deleted individually or as a group using drop extent(s) commands.

Examples

You can delete all rows in a table or just a specific extent.

• Delete all rows in a table:

  .drop extents from TestTable
  

• Delete a specific extent:

  .drop extent e9fac0d2-b6d5-4ce3-bdb4-dea052d13b42
  

Delete individual rows

Both purge and soft delete can be used for deleting individual rows. Soft delete doesn’t necessarily delete the storage artifacts that contain records to delete, and purge does delete all such storage artifacts.

Both methods prevent deleted records from being recovered, regardless of any retention or recoverability settings. The deletion process is final and irreversible.

Soft delete

With soft delete, data isn’t necessarily deleted from storage artifacts. This method marks all matching records as deleted, so that they’ll be filtered out in queries, and doesn’t require significant system resources.

Purge

With purge, extents that have one or more records to be deleted, are replaced with new extents in which those records don’t exist. This deletion process isn’t immediate, requires significant system resources, and can take a whole day to complete.

Soft delete can be used for deleting individual rows. Data isn’t necessarily deleted from storage artifacts. Soft delete prevent deleted records from being recovered, regardless of any retention or recoverability settings. The deletion process is final and irreversible. This method marks all matching records as deleted, so that they’ll be filtered out in queries, and doesn’t require significant system resources.

4 - Fact and dimension tables

When designing the schema for a database, think of tables as broadly belonging to one of two categories.

• Fact tables
• Dimension tables

Fact tables

Fact tables are tables whose records are immutable “facts”, such as service logs and measurement information. Records are progressively appended into the table in a streaming fashion or in large chunks. The records stay there until they’re removed because of cost or because they’ve lost their value. Records are otherwise never updated.

Entity data is sometimes held in fact tables, where the entity data changes slowly. For example, data about some physical entity, such as a piece of office equipment that infrequently changes location. Since data in Kusto is immutable, the common practice is to have each table hold two columns:

• An identity (string) column that identifies the entity
• A last-modified (datetime) timestamp column

Only the last record for each entity identity is then retrieved.

Dimension tables

Dimension tables:

• Hold reference data, such as lookup tables from an entity identifier to its properties
• Hold snapshot-like data in tables whose entire contents change in a single transaction

Dimension tables aren’t regularly ingested with new data. Instead, the entire data content is updated at once, using operations such as .set-or-replace, .move extents, or .rename tables.

Sometimes, dimension tables might be derived from fact tables. This process can be done via a materialized view on the fact table, with a query on the table that takes the last record for each entity.

Differentiate fact and dimension tables

There are processes in Kusto that differentiate between fact tables and dimension tables. One of them is continuous export.

These mechanisms are guaranteed to process data in fact tables precisely once. They rely on the database cursor mechanism.

For example, every execution of a continuous export job, exports all records that were ingested since the last update of the database cursor. Continuous export jobs must differentiate between fact tables and dimension tables. Fact tables only process newly ingested data, and dimension tables are used as lookups. As such, the entire table must be taken into account.

There’s no way to “mark” a table as being a “fact table” or a “dimension table”. The way data is ingested into the table, and how the table is used, is what identifies its type.

The way data is ingested into the table, and how the table is used, is what identifies its type.

5 - Kusto query result set exceeds internal limit

A query result set has exceeded the internal … limit is a kind of partial query failure that happens when the query’s result has exceeded one of two limits:

• A limit on the number of records (record count limit, set by default to 500,000)
• A limit on the total amount of data (data size limit, set by default to 67,108,864 (64MB))

There are several possible courses of action:

• Change the query to consume fewer resources. For example, you can:
  • Limit the number of records returned by the query using the take operator or adding additional where clauses.
  • Try to reduce the number of columns returned by the query. Use the project operator, the project-away operator, or the project-keep operator.
  • Use the summarize operator to get aggregated data
• Increase the relevant query limit temporarily for that query. For more information, see Result truncation under query limits.

[!NOTE] We don’t recommend that you increase the query limit, since the limits exist to protect the database. The limits make sure that a single query doesn’t disrupt concurrent queries running on the database.

6 - Overflows

An overflow occurs when the result of a computation is too large for the destination type. The overflow usually leads to a partial query failure.

For example, the following query will result in an overflow.

let Weight = 92233720368547758;
range x from 1 to 3 step 1
| summarize percentilesw(x, Weight * 100, 50)

Kusto’s percentilesw() implementation accumulates the Weight expression for values that are “close enough”. In this case, the accumulation triggers an overflow because it doesn’t fit into a signed 64-bit integer.

Usually, overflows are a result of a “bug” in the query, since Kusto uses 64-bit types for arithmetic computations. The best course of action is to look at the error message, and identify the function or aggregation that triggered the overflow. Make sure the input arguments evaluate to values that make sense.

7 - Partial query failures

A partial query failure is a failure to run the query that gets detected only after the query has started the actual execution phase. By that time, Kusto has already returned the HTTP status line 200 OK back to the client, and can’t “take it back,” so it has to indicate the failure in the result stream that carries the query results back to the client. (In fact, it may have already returned some result data back to the caller.)

There are several kinds of partial query failures:

• Runaway queries: Queries that take up too many resources.
• Result truncation: Queries whose result set has been truncated as it exceeded some limit.
• Overflows: Queries that trigger an overflow error.
• Other runtime errors: For example, network errors when invoking a cross-cluster query, or errors received from a plugin, etc.

Partial query failures can be reported back to the client in one of two ways:

• By default, a JSON property bag holding the failure information appears as part of the result data, where a JSON array is normally expected.
• As part of the “QueryStatus” table in the result stream. To prevent failure information from appearing as part of the result data, set the deferpartialqueryfailures option in the request’s properties slot (Kusto.Data.Common.ClientRequestProperties.OptionDeferPartialQueryFailures). Clients that do that take on the responsibility to consume the entire result stream from the service, locate the QueryStatus result, and make sure no record in this result has a Severity of 2 or smaller.

8 - Query consistency

Query consistency refers to how queries and updates are synchronized. There are two supported modes of query consistency:

• Strong consistency: Strong consistency ensures immediate access to the most recent updates, such as data appends, deletions, and schema modifications. Strong consistency is the default consistency mode. Due to synchronization, this consistency mode performs slightly less well than weak consistency mode in terms of concurrency.

• Weak consistency: With weak consistency, there may be a delay before query results reflect the latest database updates. Typically, this delay ranges from 1 to 2 minutes. Weak consistency can support higher query concurrency rates than strong consistency.

For example, if 1000 records are ingested each minute into a table in the database, queries over that table running with strong consistency will have access to the most-recently ingested records, whereas queries over that table running with weak consistency may not have access to some of records from the last few minutes.

Use cases for strong consistency

If you have a strong dependency on updates that occurred in the database in the last few minutes, use strong consistency.

For example, the following query counts the number of error records in the 5 minutes and triggers an alert that count is larger than 0. This use case is best handled with strong consistency, since your insights may be altered you don’t have access to records ingested in the past few minutes, as may be the case with weak consistency.

my_table
| where timestamp between(ago(5m)..now())
| where level == "error"
| count

In addition, strong consistency should be used when database metadata is large. For instance. there are millions of data extents in the database, using weak consistency would result in query heads downloading and deserializing extensive metadata artifacts from persistent storage, which may increase the likelihood of transient failures in downloads and related operations.

Use cases for weak consistency

If you don’t have a strong dependency on updates that occurred in the database in the last few minutes, and you need high query concurrency, use weak consistency.

For example, the following query counts the number of error records per week in the last 90 days. Weak consistency is appropriate in this case, since your insights are unlikely to be impacted records ingested in the past few minutes are omitted.

my_table
| where timestamp between(ago(90d) .. now())
| where level == "error"
| summarize count() by level, startofweek(Timestamp)

Weak consistency modes

The following table summarizes the four modes of weak query consistency.

Affinity by database

The affinity by database mode ensures that queries running against the same database are executed against the same version of the database, although not necessarily the most recent version of the database. This mode is useful when ensuring consistent execution within a specific database is important. However. there’s an imbalance in the number of queries across databases, then this mode may result in uneven load distribution.

Affinity by query text

The affinity by query text mode is beneficial when queries leverage the Query results cache. This mode routes repeating queries frequently executed by the same identity to the same query head, allowing them to benefit from cached results and reducing the load on the cluster.

Affinity by session ID

The affinity by session ID mode ensures that queries belonging to the same user activity or session are executed against the same version of the database, although not necessarily the most recent one. To use this mode, the session ID needs to be explicitly specified in each query’s client request properties. This mode is helpful in scenarios where consistent execution within a session is essential.

How to specify query consistency

You can specify the query consistency mode by the client sending the request or using a server side policy. If it isn’t specified by either, the default mode of strong consistency applies.

• Client sending the request: Use the queryconsistency client request property. This method sets the query consistency mode for a specific query and doesn’t affect the overall effective consistency mode, which is determined by the default or the server-side policy. For more information, see client request properties.

• Server side policy: Use the QueryConsistency property of the Query consistency policy. This method sets the query consistency mode at the workload group level, which eliminates the need for users to specify the consistency mode in their client request properties and allows for enforcing desired consistency modes. For more information, see Query consistency policy.

Related content

• To customize parameters for queries running with weak consistency, use the Query weak consistency policy.

9 - Query limits

Kusto is an ad-hoc query engine that hosts large datasets and attempts to satisfy queries by holding all relevant data in-memory. There’s an inherent risk that queries will monopolize the service resources without bounds. Kusto provides several built-in protections in the form of default query limits. If you’re considering removing these limits, first determine whether you actually gain any value by doing so.

Limit on request concurrency

Request concurrency is a limit that is imposed on several requests running at the same time.

• The default value of the limit depends on the SKU the database is running on, and is calculated as: Cores-Per-Node x 10.
  • For example, for a database that’s set up on D14v2 SKU, where each machine has 16 vCores, the default limit is 16 cores x10 = 160.
• The default value can be changed by configuring the request rate limit policy of the default workload group.
  • The actual number of requests that can run concurrently on a database depends on various factors. The most dominant factors are database SKU, database’s available resources, and usage patterns. The policy can be configured based on load tests performed on production-like usage patterns.

For more information, see Optimize for high concurrency with Azure Data Explorer.

Limit on result set size (result truncation)

Result truncation is a limit set by default on the result set returned by the query. Kusto limits the number of records returned to the client to 500,000, and the overall data size for those records to 64 MB. When either of these limits is exceeded, the query fails with a “partial query failure”. Exceeding overall data size will generate an exception with the message:

The Kusto DataEngine has failed to execute a query: 'Query result set has exceeded the internal data size limit 67108864 (E_QUERY_RESULT_SET_TOO_LARGE).'

Exceeding the number of records will fail with an exception that says:

The Kusto DataEngine has failed to execute a query: 'Query result set has exceeded the internal record count limit 500000 (E_QUERY_RESULT_SET_TOO_LARGE).'

There are several strategies for dealing with this error.

• Reduce the result set size by modifying the query to only return interesting data. This strategy is useful when the initial failing query is too “wide”. For example, the query doesn’t project away data columns that aren’t needed.
• Reduce the result set size by shifting post-query processing, such as aggregations, into the query itself. The strategy is useful in scenarios where the output of the query is fed to another processing system, and that then does other aggregations.
• Switch from queries to using data export when you want to export large sets of data from the service.
• Instruct the service to suppress this query limit using set statements listed below or flags in client request properties.

Methods for reducing the result set size produced by the query include:

• Use the summarize operator group and aggregate over similar records in the query output. Potentially sample some columns by using the take_any aggregation function.
• Use a take operator to sample the query output.
• Use the substring function to trim wide free-text columns.
• Use the project operator to drop any uninteresting column from the result set.

You can disable result truncation by using the notruncation request option. We recommend that some form of limitation is still put in place.

For example:

set notruncation;
MyTable | take 1000000

It’s also possible to have more refined control over result truncation by setting the value of truncationmaxsize (maximum data size in bytes, defaults to 64 MB) and truncationmaxrecords (maximum number of records, defaults to 500,000). For example, the following query sets result truncation to happen at either 1,105 records or 1 MB, whichever is exceeded.

set truncationmaxsize=1048576;
set truncationmaxrecords=1105;
MyTable | where User=="UserId1"

Removing the result truncation limit means that you intend to move bulk data out of Kusto.

You can remove the result truncation limit either for export purposes by using the .export command or for later aggregation. If you choose later aggregation, consider aggregating by using Kusto.

Kusto provides a number of client libraries that can handle “infinitely large” results by streaming them to the caller. Use one of these libraries, and configure it to streaming mode. For example, use the .NET Framework client (Microsoft.Azure.Kusto.Data) and either set the streaming property of the connection string to true, or use the ExecuteQueryV2Async() call that always streams results. For an example of how to use ExecuteQueryV2Async(), see the HelloKustoV2 application.

You may also find the C# streaming ingestion sample application helpful.

Result truncation is applied by default, not just to the result stream returned to the client. It’s also applied by default to any subquery that one cluster issues to another cluster in a cross-cluster query, with similar effects.

It’s also applied by default to any subquery that one Eventhouse issues to another Eventhouse in a cross-Eventhouse query, with similar effects.

Setting multiple result truncation properties

The following apply when using set statements, and/or when specifying flags in client request properties.

• If notruncation is set, and any of truncationmaxsize, truncationmaxrecords, or query_take_max_records are also set - notruncation is ignored.
• If truncationmaxsize, truncationmaxrecords and/or query_take_max_records are set multiple times - the lower value for each property applies.

Limit on memory consumed by query operators (E_RUNAWAY_QUERY)

Kusto limits the memory that each query operator can consume to protect against “runaway” queries. This limit might be reached by some query operators, such as join and summarize, that operate by holding significant data in memory. By default the limit is 5GB (per node), and it can be increased by setting the request option maxmemoryconsumptionperiterator:

set maxmemoryconsumptionperiterator=16106127360;
MyTable | summarize count() by Use

When this limit is reached, a partial query failure is emitted with a message that includes the text E_RUNAWAY_QUERY.

The ClusterBy operator has exceeded the memory budget during evaluation. Results may be incorrect or incomplete E_RUNAWAY_QUERY.

The DemultiplexedResultSetCache operator has exceeded the memory budget during evaluation. Results may be incorrect or incomplete (E_RUNAWAY_QUERY).

The ExecuteAndCache operator has exceeded the memory budget during evaluation. Results may be incorrect or incomplete (E_RUNAWAY_QUERY).

The HashJoin operator has exceeded the memory budget during evaluation. Results may be incorrect or incomplete (E_RUNAWAY_QUERY).

The Sort operator has exceeded the memory budget during evaluation. Results may be incorrect or incomplete (E_RUNAWAY_QUERY).

The Summarize operator has exceeded the memory budget during evaluation. Results may be incorrect or incomplete (E_RUNAWAY_QUERY).

The TopNestedAggregator operator has exceeded the memory budget during evaluation. Results may be incorrect or incomplete (E_RUNAWAY_QUERY).

The TopNested operator has exceeded the memory budget during evaluation. Results may be incorrect or incomplete (E_RUNAWAY_QUERY).

If maxmemoryconsumptionperiterator is set multiple times, for example in both client request properties and using a set statement, the lower value applies.

The maximum supported value for this request option is 32212254720 (30 GB).

An additional limit that might trigger an E_RUNAWAY_QUERY partial query failure is a limit on the max accumulated size of strings held by a single operator. This limit cannot be overridden by the request option above:

Runaway query (E_RUNAWAY_QUERY). Aggregation over string column exceeded the memory budget of 8GB during evaluation.

When this limit is exceeded, most likely the relevant query operator is a join, summarize, or make-series. To work-around the limit, one should modify the query to use the shuffle query strategy. (This is also likely to improve the performance of the query.)

In all cases of E_RUNAWAY_QUERY, an additional option (beyond increasing the limit by setting the request option and changing the query to use a shuffle strategy) is to switch to sampling. The two queries below show how to do the sampling. The first query is a statistical sampling, using a random number generator. The second query is deterministic sampling, done by hashing some column from the dataset, usually some ID.

T | where rand() < 0.1 | ...

T | where hash(UserId, 10) == 1 | ...

Limit on memory per node

Max memory per query per node is another limit used to protect against “runaway” queries. This limit, represented by the request option max_memory_consumption_per_query_per_node, sets an upper bound on the amount of memory that can be used on a single node for a specific query.

set max_memory_consumption_per_query_per_node=68719476736;
MyTable | ...

If max_memory_consumption_per_query_per_node is set multiple times, for example in both client request properties and using a set statement, the lower value applies.

If the query uses summarize, join, or make-series operators, you can use the shuffle query strategy to reduce memory pressure on a single machine.

Limit execution timeout

Server timeout is a service-side timeout that is applied to all requests. Timeout on running requests (queries and management commands) is enforced at multiple points in the Kusto:

• client library (if used)
• service endpoint that accepts the request
• service engine that processes the request

By default, timeout is set to four minutes for queries, and 10 minutes for management commands. This value can be increased if needed (capped at one hour).

• Various client tools support changing the timeout as part of their global or per-connection settings. For example, in Kusto.Explorer, use Tools > Options* > Connections > Query Server Timeout.
• Programmatically, SDKs support setting the timeout through the servertimeout property. For example, in .NET SDK this is done through a client request property, by setting a value of type System.TimeSpan.

Notes about timeouts

• On the client side, the timeout is applied from the request being created until the time that the response starts arriving to the client. The time it takes to read the payload back at the client isn’t treated as part of the timeout. It depends on how quickly the caller pulls the data from the stream.
• Also on the client side, the actual timeout value used is slightly higher than the server timeout value requested by the user. This difference, is to allow for network latencies.
• To automatically use the maximum allowed request timeout, set the client request property norequesttimeout to true.

Limit on query CPU resource usage

Kusto lets you run queries and use all the available CPU resources that the database has. It attempts to do a fair round-robin between queries if more than one is running. This method yields the best performance for query-defined functions. At other times, you may want to limit the CPU resources used for a particular query. If you run a “background job”, for example, the system might tolerate higher latencies to give concurrent inline queries high priority.

Kusto supports specifying two request properties when running a query. The properties are query_fanout_threads_percent and query_fanout_nodes_percent. Both properties are integers that default to the maximum value (100), but may be reduced for a specific query to some other value.

The first, query_fanout_threads_percent, controls the fanout factor for thread use. When this property is set 100%, all CPUs will be assigned on each node. For example, 16 CPUs deployed on Azure D14 nodes. When this property is set to 50%, then half of the CPUs will be used, and so on. The numbers are rounded up to a whole CPU, so it’s safe to set the property value to 0.

The second, query_fanout_nodes_percent, controls how many of the query nodes to use per subquery distribution operation. It functions in a similar manner.

If query_fanout_nodes_percent or query_fanout_threads_percent are set multiple times, for example, in both client request properties and using a set statement - the lower value for each property applies.

Limit on query complexity

During query execution, the query text is transformed into a tree of relational operators representing the query. If the tree depth exceeds an internal threshold, the query is considered too complex for processing, and will fail with an error code. The failure indicates that the relational operators tree exceeds its limits.

The following examples show common query patterns that can cause the query to exceed this limit and fail:

• a long list of binary operators that are chained together. For example:

T
| where Column == "value1" or
        Column == "value2" or
        .... or
        Column == "valueN"

For this specific case, rewrite the query using the in() operator.

T
| where Column in ("value1", "value2".... "valueN")

• a query which has a union operator that is running too wide schema analysis especially that the default flavor of union is to return “outer” union schema (meaning – that output will include all columns of the underlying table).

The suggestion in this case is to review the query and reduce the columns being used by the query.

Related content

• Optimize for high concurrency with Azure Data Explorer
• Query best practices

10 - Runaway queries

A runaway query is a kind of partial query failure that happens when some internal query limit was exceeded during query execution.

For example, the following error may be reported: HashJoin operator has exceeded the memory budget during evaluation. Results may be incorrect or incomplete.

There are several possible courses of action.

• Change the query to consume fewer resources. For example, if the error indicates that the query result set is too large, you can:
  • Limit the number of records returned by the query by
    • Using the take operator
    • Adding additional where clauses
  • Reduce the number of columns returned by the query by
    • Using the project operator
    • Using the project-away operator
    • Using the project-keep operator
  • Use the summarize operator to get aggregated data.
• Increase the relevant query limit temporarily for that query. For more information, see query limits - limit on memory per iterator. This method, however, isn’t recommended. The limits exist to protect the cluster and to make sure that a single query doesn’t disrupt concurrent queries running on the cluster.
• Increase the relevant query limit temporarily for that query. For more information, see query limits - limit on memory per iterator. This method, however, isn’t recommended. The limits exist to protect the Eventhouse and to make sure that a single query doesn’t disrupt concurrent queries running on the Eventhouse.

11 - Sandboxes

Kusto can run sandboxes for specific flows that must be run in a secure and isolated environment. Examples of these flows are user-defined scripts that run using the Python plugin or the R plugin.

Sandboxes are run locally (meaning, processing is done close to the data), with no extra latency for remote calls.

Prerequisites and limitations

• Sandboxes must run on VM sizes supporting nested virtualization, which implemented using Hyper-V technology and have no limitations.
• The image for running the sandboxes is deployed to every cluster node and requires dedicated SSD space to run.
  • The estimated size is between 10-20 GB.
  • This affects the cluster’s data capacity, and may affect the cost of the cluster.

Runtime

• A sandboxed query operator may use one or more sandboxes for its execution.
  • A sandbox is only used for a single query and is disposed of once that query completes.
  • When a node is restarted, for example, as part of a service upgrade, all running sandboxes on it are disposed of.
• Each node maintains a predefined number of sandboxes that are ready for running incoming requests.
  • Once a sandbox is used, a new one is automatically made available to replace it.
• If there are no pre-allocated sandboxes available to serve a query operator, it will be throttled until new sandboxes are available. For more information, see Errors. New sandbox allocation could take up to 10-15 seconds per sandbox, depending on the SKU and available resources on the data node.

Sandbox parameters

Some of the parameters can be controlled using a cluster-level sandbox policy, for each kind of sandbox.

• Number of sandboxes per node: The number of sandboxes per node is limited.
  • Requests that are made when there’s no available sandbox will be throttled.
• Initialize on startup: if set to false (default), sandboxes are lazily initialized on a node, the first time a query requires a sandbox for its execution. Otherwise, if set to true, sandboxes are initialized as part of service startup.
  • This means that the first execution of a plugin that uses sandboxes on a node will include a short warm-up period.
• CPU: The maximum rate of CPU a sandbox can consume of its host’s processors is limited (default is 50%).
  • When the limit is reached, the sandbox’s CPU use is throttled, but execution continues.
• Memory: The maximum amount of RAM a sandbox can consume of its host’s RAM is limited.
  • Default memory for Hyper-V technology is 1 GB, and for legacy sandboxes 20 GB.
  • Reaching the limit results in termination of the sandbox, and a query execution error.

Sandbox limitations

• Network: A sandbox can’t interact with any resource on the virtual machine (VM) or outside of it.
  • A sandbox can’t interact with another sandbox.

Errors

VM Sizes supporting nested virtualization

The following table lists all modern VM sizes that support Hyper-V sandbox technology.