Telemetry Container That Writes Too Expensive and Reads Too Much

Switch from the default /* include to a selective index. Only four properties are ever filtered or sorted on, so the other ~56 are pure index-maintenance cost on every write. Set the policy to:

{
  "indexingMode": "consistent",
  "includedPaths": [{"path": "/type/?"}, {"path": "/recordedAt/?"}, {"path": "/region/?"}],
  "excludedPaths": [{"path": "/*"}]
}

/deviceId is the partition key path and is indexed automatically; it cannot be excluded and does not need listing (listing it is harmless).

For a 4 KB document with ~56 unnecessary index entries, dropping them typically removes the majority of index-maintenance RU, which is a large share of the 22 RU write. Whatever is left is mostly payload size and replication.

Trade-off accepted: any future query that filters or sorts on an excluded property becomes a full scan of the partition (or of the container, if it is not partition-scoped). That is a real regression risk for a schema the team may extend. Mitigate by putting the four known query paths in the policy today and requiring a policy change, with review, when a new access pattern appears.

Operational risk: changing the indexing policy triggers a reindex. It is online - the container stays available - but it consumes RU from the provisioned budget and writes are not throttled in its favour, so an 8,000 docs/sec ingestion could start seeing 429s. Ship the policy change during the ingestion trough, and watch the IndexUtilization/reindex progress metrics before declaring success. Also expect reindexing to be slow on 6 TB - plan for hours, not minutes.

Add one composite index on (type ASC, recordedAt DESC) and make sure both paths are in includedPaths:

"compositeIndexes": [[
  {"path": "/type", "order": "ascending"},
  {"path": "/recordedAt", "order": "descending"}
]]

Why the range index alone fails: a single-property range index supports ORDER BY on one property, or an equality filter on one property. It does not support sorting on recordedAt while also applying an equality predicate on a different property (type). The query engine cannot produce results in recordedAt order without either sorting in memory (rejected above a small threshold) or walking a composite index whose key order matches the predicate-then-sort shape. Hence the error.

The sort direction in the composite index must match the query. ORDER BY c.recordedAt DESC with type ascending is the shape above; if a future query needs ORDER BY c.recordedAt ASC with the same filter, that is a second composite index, and each one adds write cost - which is why you add it deliberately rather than indexing everything.

Because deviceId is the partition key and the query supplies it, this is a single-partition query, so the composite index is scoped to that partition and is cheap to maintain. If the query ever drops the deviceId predicate it becomes a fan-out and the RU climbs with partition count - watch for that regression in code review.

LOWER() - data model. Function calls on a property defeat the index; the engine must evaluate LOWER(c.region) for every candidate document, which is why a 40 GB index read turns into a scan. Options:

• Best: store a normalized field. Have ingestion write regionKey: "emea" alongside region. Field names cannot change, but adding a derived field does not violate that. Query WHERE c.regionKey = 'emea' AND c.recordedAt > @t and index /regionKey/? - with a composite index on (regionKey ASC, recordedAt DESC) if the planner still needs ordering help.
• Acceptable stopgap: c.region IN ('emea','EMEA','Emea', ...) if and only if the set of spellings is genuinely closed and small. It is index-usable, but it is a bug farm the day someone writes 'Emea ' with a trailing space.
• Do not try to solve it with a UDF or a full-text workaround; both scan.

The dashboard count - architecture. Running SELECT COUNT(1) over alerts per device per day on every dashboard load charges RU per document scanned and will get worse linearly with retention. Do not put it on the operational path.

• Maintain a pre-aggregated alertCounts container keyed (deviceId, yyyy-mm-dd, type) and updated by a change-feed processor using PatchOperation.Increment. The dashboard reads one small document per device-day - single-digit RU. Handle out-of-order and duplicate change-feed delivery by making the increment idempotent (dedupe on the event id in a small side document, or count from a monotonic sequence rather than blind incrementing).
• Alternatively enable the analytical store and run the aggregation in Synapse Link or Fabric. Cleaner, but adds a serving layer and sync latency.

Given ingestion already peaks at 8,000 docs/sec and dashboards are interactive, the change-feed pre-aggregation is the right default; reach for the analytical store when the questions become ad hoc rather than fixed.