Keep One Asset’s History Together Without Growing Forever

The useful question is small: what happened to P7 around the reported fault? Storing all sensors together makes that request search unrelated histories. Putting P7’s entire lifetime into one partition eventually creates another problem: the group has no useful bound.

We will divide an asset’s history into time buckets and order observations inside each bucket. Those two decisions explain the partition key and clustering key used by a wide-column design.

Choose the partition, apply the time interval, order equal-time events by identity and take the requested result count.

The partition key identifies a bounded group. Our key combines tenant, asset and day. The clustering order arranges rows within that group by event time and a tie-breaking event ID.

Two sensors can report at the same timestamp, so time alone may not identify an observation. Include the actual identity needed to prevent accidental replacement. An asset label without tenant scope can also refer to the wrong customer’s pump.

The complete primary key identifies the row. Its prefix locates the group we can read together.

A daily bucket is convenient for the example, but its suitability depends on measurement rate and item size. A busy asset may need smaller time buckets or another bounded split. Very small buckets create more queries and merge work for long intervals.

Estimate the largest expected partition and test it with the database’s documented guidance. A partition containing one quiet day is not evidence about the busiest customer’s busiest day. Retention and late-arrival rules belong in the estimate too.

The bucket size is a workload choice, not an automatic property of wide-column storage.

The local Python model first checks the complete tenant, asset and day selector. It then keeps events in a half-open interval: start is included and end is excluded. Finally it sorts by time and ID before applying a result limit.

The dictionary list makes these operations visible. A real database can use its stored layout to avoid scanning every record; our Python list cannot demonstrate that performance difference.

A result limit must follow the ordering if the request means the earliest matching events.

events = [{"id":"B","time":12},{"id":"A","time":12},{"id":"C","time":10}]
ordered = sorted(events,key=lambda r:(r["time"],r["id"]))
print([r["id"] for r in ordered])

['C', 'A', 'B']

CQL looks familiar to a SQL reader, but its supported access paths follow Cassandra’s data model. The inner parentheses below identify a compound partition key; the remaining primary-key columns give the clustering order.

Use a separate table or supported index for a materially different access pattern when justified. ALLOW FILTERING is not a general substitute for designing a bounded query. Its cost can grow with the data the server must examine.

The browser model runs without Cassandra. This reference needs an external development cluster.

from cassandra.cluster import Cluster
cluster = Cluster(["127.0.0.1"])
session = cluster.connect("maintenance")
session.execute("""
CREATE TABLE IF NOT EXISTS readings_by_asset_day (
 tenant text, asset text, day date, event_time timestamp,
 event_id text, value double,
 PRIMARY KEY ((tenant, asset, day), event_time, event_id)
) WITH CLUSTERING ORDER BY (event_time ASC, event_id ASC)
""")
cluster.shutdown()

A request crossing a bucket boundary needs every overlapping bucket. Apply the time bounds within each, then merge by the same event ordering. Taking a limit independently from each bucket is not the same as proving a global result is complete; the merge still has to select the requested global prefix.

Paging needs a stable continuation meaning. A cursor can include the bucket and final clustering key, or use an opaque database paging token according to the driver contract. Changing the query while reusing a token can invalidate its meaning.

An empty result from one day does not establish that an asset has no readings on the next day.

Retention can expire old measurements, but storage engines may retain deletion markers until it is safe to remove them. Cassandra uses tombstones to represent deleted data. Large scans through expired or deleted entries can still be expensive before compaction and repair requirements are satisfied.

Keep retention, repair and compaction settings together in the operating plan. Do not change a deletion-grace setting solely to make disk space disappear faster without understanding how replicas learn the deletion.

Logical disappearance and physical reclamation are different events.

A table by asset and day may serve the technician while a separate analytical system serves fleet-wide comparisons. If the same fact appears in several query views, define which source owns it and how updates or deletions reach the copies.

We will return to projections later. For now, test that an event in another tenant, asset or day cannot leak into this bucket lookup. A fast query that returns the wrong partition is still the wrong query.

The workshop’s changed day leaves the earlier records untouched while changing which partition is addressed.