Optimizing Azure Data Explorer for Large-Scale IoT Telemetry Data

Azure Data Explorer (ADX) is a fast, fully managed analytics database optimized for high-volume streaming data like IoT telemetry. In energy and smart city scenarios, ADX can ingest millions of sensor readings and provide sub-second analytical queries. This overview covers schema design best practices, performance tuning, delta (incremental) querying, cost considerations, and how ADX compares to other time-series solutions.

Designing ADX Tables for Massive IoT Telemetry

Columnstore vs. Rowstore

ADX is a columnar database by design – it stores each column of a table separately for compression and fast aggregation. This suits IoT telemetry, where queries often scan a few columns (e.g. timestamps, device IDs, sensor values) across billions of rows. ADX can temporarily use a row store for streaming ingestion (small micro-batches in memory) to achieve near-real-time availability. In practice, this means for most high-volume pipelines you’ll use queued/batch ingestion directly into columnar storage, and only enable streaming ingestion (row store) if you need end-to-end latency of a few seconds or less.

Wide vs. Narrow Tables

Favor narrow table schemas over extremely wide tables. Wide tables with hundreds of columns can degrade performance and are harder to manage. Instead, design tables with a focused set of columns (for example, timestamp, deviceId, sensorType, value, etc.), and use ADX’s dynamic data type for infrequently used or semi-structured fields. If you have >20 very sparse columns (mostly nulls), consider storing them as a JSON map in a single dynamic column rather than as separate columns. This keeps the core table “lean” for the most common queries. ADX’s columnar storage will efficiently compress nulls, so splitting rarely-used fields into a dynamic property bag can improve ingestion and query efficiency.

Single vs. Multiple Tables

Organize tables by data type or use case. If all IoT events share a similar schema, a single table simplifies management. However, for vastly different telemetry types, using separate tables can isolate each schema for better compression and performance. For example, energy meter readings vs. environmental sensor logs might reside in different tables. You can always use ADX’s query union to analyze across tables when needed. The key is to avoid one table that tries to hold completely unrelated data, which would either create many sparse columns or require heavy use of dynamic JSON parsing.

Ingestion Best Practices

Ingest IoT data through scalable pipelines (e.g. Azure IoT Hub or Event Hub into ADX). Use queued ingestion with an appropriate batching policy so that small messages are aggregated into larger batches before indexing. By default, ADX will batch up to 5 minutes or 1000 events or 1 GB of data per batch. For high-throughput IoT scenarios, you can decrease the flush interval to ingest data more frequently (e.g. flush every 30 seconds or on smaller byte thresholds) to balance latency and throughput. Batching events reduces overhead by creating fewer large data extents instead of many tiny ones, which improves query speed and reduces storage fragmentation. In practice, most solutions stick with queued ingestion (batching) as it’s more efficient for large scale, and only use streaming ingestion if absolutely necessary for real-time monitoring.

Denormalize When Possible

To avoid expensive joins at query time, enrich your telemetry with static data at ingestion time. For example, if you have a device metadata table (locations, types, etc.), you might attach relevant attributes to each event record on ingestion. ADX can do this via update policies (which transform incoming data) or client-side enrichment. By storing a “deviceType” or “city” along with each event, you eliminate the need to join in most queries. This does increase storage (duplicating some info per row) but greatly speeds up queries. If the reference data changes (e.g. device info updates), you can maintain a latest snapshot in a separate table and use materialized views to periodically refresh enriched data.

Schema Design Considerations

Designing your ADX schema thoughtfully has major impacts on performance, cost, and scalability:

Data Types

Use appropriate column types. Numeric sensor readings should use numeric types (long or real), timestamps should use the native datetime type (not strings or longs), and boolean flags should use bool. Avoid using dynamic type for fields that are frequently filtered or aggregated – instead, promote them to dedicated columns. Reserve dynamic for semi-structured data or rarely-queried attributes. Proper types ensure ADX can index and compress data optimally.

Indexing and Encoding

ADX automatically indexes data as it ingests. You can fine-tune this by disabling indexing on columns never used in searches (using an encoding policy). For instance, large text blobs or JSON stored in a dynamic column could use the BigObject encoding to skip indexing, saving ingestion time and index storage. Conversely, for high-cardinality string IDs (like device IDs or sensor IDs), keep them as string type and consider the Identifier encoding which optimizes equality lookups. Note that only string columns can be used as partition keys, so defining device IDs as strings (even if they look numeric) is beneficial for both indexing and potential partitioning.

High Cardinality and Partitioning

If you have millions of distinct device IDs or tenants, and queries often filter by one ID or a small set, consider using a partitioning policy. Partitioning will physically group data by a key (e.g. DeviceId or TenantId) into separate shards, so queries with where DeviceId == "X" only scan that partition. This can drastically speed up per-device queries on very large tables. ADX supports hash-partitioning on a string or GUID column for this scenario. For example, IoT telemetry from many sensors (high cardinality) could partition on SensorID. Be aware partitioning adds some overhead – it triggers a background reorganization of data into partitions after ingestion. Microsoft advises using it only in specific cases (typically >10,000 distinct values with frequent per-value filters). In most cases, the default time-based extent split (by ingestion time) is sufficient. Only apply partitioning if your workload justifies it (e.g. a multi-tenant environment where each tenant’s data is usually queried in isolation).

Scalability

ADX is built to scale out to billions of records by sharding data into extents (chunks) and distributing them across cluster nodes. The schema you choose affects how well ADX can compress and merge those extents. Narrow schemas (fewer columns) typically lead to better compression and faster merges, which improves query speed at scale. Wide schemas with many columns or using a lot of dynamic JSON may result in more CPU usage during queries (for parsing) and slower compression. Additionally, ensure the cluster’s capacity (CPU/memory) is sized for your data volume and query concurrency – a well-designed schema reduces the needed resources by making queries efficient. ADX separates compute from persistent storage behind the scenes (data is stored in Azure Blob, with nodes caching recent data), which means you can scale up compute independently as your query load grows without necessarily retaining all data in expensive storage. Use ADX’s built-in retention policies to drop or archive old data you no longer need to query to keep the data size manageable and costs in check.

Query Optimization Strategies for Performance SLAs

When dealing with massive IoT datasets, optimizing queries in ADX is key to meeting tight response time SLAs. ADX provides several features and tuning knobs to accelerate queries:

Time Filtering and Partition Pruning

Almost all IoT queries should include a time filter (e.g. WHERE timestamp > ago(7d)). This ensures ADX only scans the relevant time range. Because data is ingested in time-order, filtering by time prunes entire extents (shards) that fall outside the range, dramatically reducing scan volume. Similarly, if you implemented a partition by device or tenant, include that filter so ADX skips other partitions. Always filter as early as possible in the query.

Column Projection

Only select the columns you actually need in the query (project in KQL). Avoid SELECT * (or its KQL equivalent of using the entire row) on a wide table. By projecting away unused columns, ADX reads less data from disk. This is especially important in a column-store – scanning 3 columns out of 50 is much faster than scanning all 50, even if the query ultimately only uses 3. One best practice is to use the KQL project-away operator to explicitly drop columns not needed in your result.

Caching (Hot Data Cache)

ADX employs an intelligent caching system. Hot cache keeps recently ingested data on local SSDs or in memory on the cluster nodes. Queries over hot cache data (for example, the last few days) are extremely fast since the data is readily available, compressed and indexed on the nodes. You can configure the cache policy to control how long data stays in the hot cache at the table or database level. For IoT workloads with stringent SLAs on recent data, consider extending the hot cache duration (e.g. keep last 7 days hot). ADX also has a query results cache for repeated queries: if the same query is run frequently and the underlying data hasn’t changed, ADX can return a cached result instead of recomputing it. This is useful for dashboards or APIs hitting ADX with identical queries. You can enable it via the set query_results_cache_max_age query option to cache results for a specified time. Cached query results skip scanning entirely, saving time and compute costs, especially for expensive aggregations that refresh periodically.

Materialized Views

For heavy analytical queries or reports that aggregate large data sets, materialized views are a game changer. A materialized view in ADX is a persisting pre-computed result (often an aggregation) that updates continuously as new data arrives. For example, you might maintain a materialized view that summarizes hourly energy consumption per city. Queries against this view will be extremely fast (since the computation is pre-done) and will scan far less data than querying the raw table. The trade-off is a bit of extra compute on ingestion to maintain the view and some storage for the view itself. Microsoft notes that this incurs overhead during data ingestion, but significantly speeds up queries that use the view. Use materialized views for your most critical aggregations or when you need to meet strict query response times that raw data scans can’t achieve. Unlike manual ETL processes, ADX materialized views update automatically and ensure query results are always up-to-date without needing external jobs.

Query Patterns and Operators

Use KQL wisely for performance. Prefer operators that can use indexes. For example, use == or has for string matching rather than wildcards; use startswith() if searching prefixes, etc. Avoid full-text searches across all columns (*), which are expensive. If you have very large result sets, consider using top or take to limit output, or aggregate to reduce data. When joining tables, join on indexed columns (like device ID) and put the smaller table on the left side of the join to minimize data shuffle. For high-cardinality aggregations or joins that can’t be avoided, use query hints like hint.shufflekey to distribute work by a key. Many of these are advanced techniques, but they can help squeeze out extra performance for complex queries.

Data Partitioning (Shard Key)

If you implemented a partitioning policy on the table (as discussed earlier), ensure your queries include the partition key in filters or group-bys so that the engine can leverage it. Partitioning’s benefit is most visible when queries target a subset of the data. Also, be mindful that after enabling partitioning, newly ingested data might take some time to get re-partitioned in the background. ADX will still ingest and make data available immediately, but the reorganization means very recent data might not yet be in perfect partitions until the process completes. This mainly matters for query consistency when measuring performance.

Ingestion-Time Transformations

Though not a direct query optimization, using features like update policies to pre-compute certain values can speed up queries. For instance, computing a hourBucket = startofhour(timestamp) during ingestion (via an update policy or computed column) means queries don’t have to calculate it on the fly. This saves time for repetitive calculations at query runtime, essentially trading a bit of ingestion work for faster queries.

Efficient Delta Querying with ADX Cursors

Often, data engineers need to periodically fetch “what’s new” in an ever-growing telemetry table – for example, reading the latest IoT events every minute to feed a dashboard. ADX cursors make this delta querying efficient and reliable. A database cursor in ADX represents a point in the data timeline; using it allows you to get exactly-once delivery of new records without overlap or missing gaps.

How it works

ADX tables can have an ingestion time policy enabled, which tags each record with a hidden timestamp (ingestion_time()). The cursor is essentially a bookmark in that ingestion timeline. You use the function cursor_after(<last_cursor>) in your query’s where clause to get only records ingested after the last cursor position. For example:

Logs 
| where cursor_after("{CursorValue}")

On the first run, you use an empty string or very old cursor to get all data (or all data since some start point). On each subsequent run, supply the last returned cursor value to get new data.

When you use cursor_after() in a query, ADX will return the current cursor up to which the query read data as part of the query results (in the ExtendedProperties with name “Cursor”). You can retrieve it in the query response metadata – it looks like a opaque string token (e.g. "Cursor": "636040929866477946" as shown in docs). Your application should treat this token as an opaque bookmark (don’t try to interpret it) and store it externally (for example, in Azure Storage or state). Next time, use cursor_after(storedCursor) to fetch the next batch.

Best Practices for Cursors

Ensure the ingestion_time policy is enabled on your table (this is required for cursor functions to work). This policy is usually easy to turn on when creating the table or via an alter command.
Always use the cursor functions (cursor_after or its counterpart cursor_before_or_at) for correctness. They guarantee you don’t see duplicates or miss records, even if ingestion and retention are happening concurrently. For example, if new data arrives during your query, ADX ensures that anything not included this round will appear after the cursor next round.
Treat the cursor string as a checkpoint. After each query, persist the new cursor value in reliable storage. If your consumer job crashes or restarts, use the last saved cursor to resume. This way you don’t reprocess old data or skip ahead.
Be aware of retention: if your cursor is very old (earlier than the retention period of the table), those records might have been dropped. Typically you use cursors for ongoing incremental loads, so this isn’t an issue unless the process was down for a long time. If starting fresh after a long gap, you might need to reset to a known timestamp or backfill separately.
Latency considerations: ADX assigns cursor values upon ingestion commit. There’s a slight chance that right after an ingestion, if you query with a cursor, the newest records might not yet have a cursor assigned and thus won’t appear. In practice, this is a minor timing detail – to be safe, if running on a schedule, you could overlap queries by a minute or use cursor_before_or_at on the end cursor from last run to ensure you included everything. Most use cases find cursor_after with last saved token to be sufficient and reliable for continuous export of new data.

Using ADX cursors is far more efficient than filtering by timestamp (which might double count or skip data if clocks are skewed) and avoids maintaining your own watermarks. It offloads the “exactly-once” logic to ADX itself, which is ideal for delta ingestion scenarios.

Cost Implications of Design and Query Choices

Cost in Azure Data Explorer is primarily driven by compute resources (cluster size), storage volume, and ingress/egress. Your design and querying patterns directly influence these:

Storage Cost (and Schema Design)

Every column you store and every record impacts storage. A denormalized schema (embedding device metadata in each row) uses more storage than a normalized approach, but it saves on compute during queries. It’s a trade-off: storage is relatively cheap compared to CPU. Given ADX’s compression, even duplicated fields may compress well. For instance, repeating a city name or device type in every row compresses to almost nothing if there are few unique values. Thus, favoring query performance (denormalization) can be cost-effective by reducing the need for a larger compute cluster to do joins. Wide tables with many columns might increase storage for indexing, and if most columns are seldom used, you’re paying a small cost for those indexes. Grouping sparse columns into a dynamic bag (as mentioned) can cut storage overhead by having fewer indexes. Also consider retention policies – dropping data you no longer need (or moving it to cheap Azure Data Lake storage via continuous export) will directly reduce storage costs.

Ingestion Cost

ADX doesn’t charge per ingestion operation like some cloud databases, but heavy ingestion can require a more powerful cluster (more CPU to index data quickly). Batching events efficiently means fewer overall ingestion operations and better use of resources, which lets you handle more data on a given cluster size. If you trickle in single records and force ADX to commit tiny extents, the system will spend more effort merging and managing metadata. This could nudge you to scale up the cluster for no good reason. So, an optimized ingestion pipeline (using Event Hub batching, etc.) keeps costs down by maximizing throughput on a smaller cluster.

Compute/Query Cost

ADX clusters are billed per node (hourly), so the goal is to do more with less nodes. Efficient schema and queries reduce the CPU seconds needed per query. For example, a query that scans 100 GB of data to find a result might require a large cluster to return quickly, whereas a query scanning 1 GB (thanks to good filtering, partitioning, or a materialized view) can run fast on a much smaller cluster. Features like caching and query results cache help avoid recomputation – if you serve a popular dashboard that refreshes every minute, using the results cache for unchanged intervals can drastically cut the query load, meaning you don’t need as many query cores. Similarly, materialized views offload heavy aggregation work to ingestion time (spread out over many small increments), which can be cheaper than doing huge aggregations on-demand. The view maintenance does consume CPU, so you’re effectively paying upfront to save bigger costs later. Generally, if a query runs many times, pre-aggregate or cache it; if it’s ad-hoc, ensure it’s well-filtered. This reduces overall CPU consumption and allows a given cluster to handle more queries (or lets you scale down nodes).

Scaling Strategies

ADX allows scaling cluster size up or down (or even pausing it for dev/test environments). A best practice is to start with a smaller cluster in early stages and only scale up once data volume or query concurrency grows. Thanks to the separation of compute and storage, you won’t lose data by scaling down compute – your data persists in Azure Blob. You pay for the storage separately (which is low cost per TB). So you can optimize cost by retaining data in ADX for as long as needed without necessarily keeping a huge cluster running 24/7. If query demand is low during off-peak hours, you might even use scheduled scaling or Azure policies to adjust cluster SKU.

Comparative Cost (ADX vs others)

It’s useful to note that unlike Azure Cosmos DB (which charges per RU operation), ADX’s pricing model can be more predictable for analytics: you size the cluster for your expected workload. For massive IoT data, this often ends up cheaper than trying to serve large analytical queries on a transactional store that would incur huge RU charges. Azure Time Series Insights (when it was available) charged per amount of data and query units, but had limits on retention and ingress rate – ADX now fills that gap by letting you handle “massive volumes of data effortlessly” albeit with careful tuning to avoid over-provisioning.

In summary, design choices that reduce data scanned or processed (like efficient schema, use of partitions, caching, materialized views) will directly translate to lower compute time per query – allowing you to meet SLAs with fewer resources, thereby saving cost. On the flip side, storing a bit more data (for denormalization or pre-aggregation) is usually a good trade for lowering expensive CPU cycles. Always monitor your cluster’s utilization and adjust these levers accordingly for an optimal cost/performance balance.

ADX vs. Other Time-Series Data Solutions

Finally, how does Azure Data Explorer stack up against some other popular options for IoT/time-series data (like Azure Time Series Insights, Azure Cosmos DB, and InfluxDB)?

Azure Time Series Insights (TSI)

TSI was a specialized PaaS for IoT telemetry with a built-in user interface and analytics, but Microsoft has retired TSI as of mid-2024, encouraging customers to migrate to ADX. In fact, ADX underpins much of that functionality now. Compared to TSI, ADX offers far greater flexibility (full Kusto Query Language, custom analysis, integration with Power BI/Grafana, etc.) and virtually unlimited scalability. ADX requires more hands-on setup (TSI had a turn-key UI), but you can achieve similar interactive charting using ADX Dashboards or third-party tools.

Schema flexibility: TSI did not require an explicit schema – it could ingest JSON payloads and let you query by keys. ADX prefers a defined schema (for performance), but you can ingest JSON into ADX with mappings and even store unknown fields as dynamic columns.

Performance: ADX is built on a powerful column store and can analyze thousands of time series in seconds using its time series functions. TSI was tuned for up to a certain data volume and retention (TSI Gen2 supported large volumes but ultimately had caps and a higher cost at scale). Now that TSI is discontinued, ADX is the go-to solution for time-series on Azure, combining TSI’s features (like warm/cold data split, instant querying) with a much richer platform for data engineering. Pricing wise, TSI was based on capacity units (with charges for ingress, storage, query) whereas ADX is based on cluster and storage. For large deployments, ADX often ends up more cost-effective and certainly more scalable.

Azure Cosmos DB

Cosmos DB is a globally distributed NoSQL database, which some IoT solutions use for device telemetry. It excels at schema-less ingestion and flexible JSON storage, and is optimized for quick point-lookups or small range queries by keys. However, Cosmos is not designed for heavy analytical queries over billions of records – those would incur very high RUs (request units) and cost. A common pattern is to use Cosmos DB for operational data (for example, the latest state of each device, or powering a real-time app that needs per-device data on demand), and simultaneously send the stream to ADX for analytics. In fact, an Azure Architecture guidance suggests using Cosmos to store IoT messages for real-time apps, while ADX ingests the same data for analytical queries in parallel.

Schema flexibility: Cosmos lets you store any JSON – no upfront schema – which is easy for evolving IoT data, but you then have to handle query logic for different fields. In ADX, you’d typically evolve the table schema as needed or use dynamic columns for new fields.

Performance: ADX, being columnar and indexed for analytics, will outperform Cosmos by orders of magnitude for large scans, aggregations, and complex filters (especially on telemetry spanning days or months). Cosmos would require each query to be carefully indexed and even then scanning a large date range or doing aggregates could be very slow and costly.

Pricing: Cosmos is billed by throughput (RUs) and storage; if used for time-series, you’d pay for every insert and for large query RUs. ADX is more about paying for a persistent cluster – which for steady high-volume IoT data can be more predictable and often cheaper. In summary, Cosmos DB is great for online, per-entity operations (and is schema-agnostic), whereas ADX is far superior for big-picture analytics on IoT data. They are often used together, but if you only need analytics, ADX is the specialized tool and likely more cost-efficient for that purpose.

InfluxDB

InfluxDB is a popular open-source time-series database. Like ADX, it is columnar for time-series and highly optimized for storage and retrieval of metrics. InfluxDB (especially the latest InfluxDB 3.0) also uses an in-memory hot cache and disk-based cold storage (often using Parquet files for compression), conceptually similar to ADX’s hot cache and persistent storage approach.

Schema flexibility: InfluxDB doesn’t enforce a schema per se; you write data points with measurement names, tag keys, and fields. It’s quite flexible – you can add new fields any time. However, this can lead to high cardinality issues if not managed (each unique tag value combination creates a series in the index). ADX handles high cardinality better by design (it can have millions of distinct IDs and still query efficiently with proper indexing).

Query language: InfluxDB has its SQL-like InfluxQL and the newer Flux language, which are powerful for time-centric queries but not as broad as Kusto Query Language in terms of joining with other data or advanced analytics.

Performance: Both systems can ingest high volumes and query large datasets. InfluxDB is often praised for its write throughput on time-series and can achieve very low-latency queries on recent data due to its memory cache. ADX is comparable in performance, and often more scalable in a multi-node scenario – InfluxDB clustering is possible (InfluxEnterprise or InfluxDB Cloud) but adds complexity.

Scalability: ADX is a managed service that can scale out simply by adding more nodes in Azure; InfluxDB might require manual sharding or using their cloud tier for similar scale-out.

Cost: InfluxDB OSS is free to run on your own hardware, which is attractive if you have small/medium workloads and ops expertise. InfluxDB Cloud and ADX will have cloud costs – InfluxDB Cloud offers usage-based pricing (and can run on AWS/GCP/Azure) whereas ADX is Azure-only and charged by cluster size. One point from InfluxData: they note that ADX ties compute and storage scaling together to some extent (you scale by node units), whereas InfluxDB Cloud separates compute and storage in their architecture (you don’t pay for unused compute if you just need storage). In practice, ADX’s separation of persistent storage (in blob) from compute nodes means you can scale storage independently (just keep data longer, pay for blob), but to get more query power you add nodes which comes with more cache storage too. For an Azure-centric shop, ADX’s tight integration (with Azure security, Azure ML, Synapse, etc.) and fully managed nature is a big plus. InfluxDB might be chosen for portability (multi-cloud/hybrid) or if one prefers its data model for IoT metrics. Both are excellent at time-series: ADX might edge out for complex analytics and huge multi-terabyte scenarios, whereas InfluxDB can be great for simpler or smaller deployments and where open-source or multi-cloud support is needed.

Conclusion

In summary, Azure Data Explorer is a powerful choice for large-scale IoT data in scenarios like energy grids and smart cities. By designing an efficient schema (narrow tables, appropriate data types, possibly partitioning by device), using ingestion best practices, and leveraging ADX features like caching, materialized views, and cursor-based querying, you can achieve interactive performance on massive telemetry datasets. These optimizations also help control costs by reducing unnecessary overhead. While other data stores each have their niches, ADX offers a balanced mix of schema flexibility, blazing query performance, and manageable cost for time-series analytics, especially now as it becomes the flagship time-series solution on Azure (taking over from Time Series Insights). With the tips outlined above – and references to Azure’s documentation for deeper dives – a data engineer can confidently architect an ADX solution that scales to billions of events while meeting the demands of real-world analytics and SLAs.