When I moved from tidy business data into the world of sensors, I did the natural thing: I reached for the tools I knew. I had a perfectly good relational data warehouse, so I started landing sensor readings in it. And it worked — for a while, and then progressively less well, in ways that took me an embarrassingly long time to diagnose as structural rather than something I'd misconfigured. The warehouse wasn't broken. It was doing exactly what warehouses do, and time-series data is simply a bad fit for what warehouses do. This is the post I wish I'd read before I found that out the slow way.
Let me be precise about the mismatch, because "use the right tool" is useless advice without understanding why the familiar tool is wrong here.
What a warehouse is actually built for
A traditional data warehouse is optimised, deep in its bones, for a particular shape of data and a particular shape of question. The data is business records — orders, customers, transactions — that arrive at a human pace and get loaded periodically. The questions are analytical joins across dimensions: sales by region by product by quarter. Everything about a warehouse — its indexing, its storage, its query planner — is tuned for that: moderate volumes of structured records, loaded in batches, queried by slicing across dimensions.
Time-series data violates nearly every one of those assumptions at once. And it doesn't violate them a little. It violates them relentlessly, by design, forever.
The four ways time-series breaks the assumptions
- The volume is relentless and only grows. Business data arrives at the speed of business. Sensor data arrives at the speed of time — every device, every few seconds, every day, with no weekends and no quiet nights. A warehouse expects loads that start and stop. Time-series is a tap that never turns off, and the table grows without bound in a way warehouse storage and indexing were never designed to absorb gracefully.
- It's almost all inserts, and warehouses don't love that. A warehouse's world is mixed — loads, then lots of reading and slicing. Time-series is overwhelmingly append: a firehose of new rows, forever, and comparatively little updating. That constant high-rate insertion is precisely the workload that makes a warehouse's indexes and structures groan, because keeping them maintained under an endless insert storm is expensive work the design assumed it wouldn't have to do continuously.
- The questions are the wrong shape. Warehouse questions slice across dimensions. Time-series questions are about time: "the average per five-minute window," "downsample this to hourly," "what's the trend over this range," "show me the readings around this event." These are temporal, windowed, sequential operations — and a general-purpose relational engine does them clumsily, because they're not the joins-across-dimensions it was optimised to fly through.
- Cardinality explodes. Give a warehouse a few hundred sensors reporting every few seconds for a year, and you have a table of a size and row-count that its indexing strategies simply weren't built to keep performant. The thing that makes a warehouse fast for millions of business records doesn't scale the same way to billions of readings that all look nearly identical except for a timestamp.
None of these is a bug. Each is a warehouse faithfully being a warehouse, applied to a workload shaped to defeat it.
What time-series stores do differently
The reason purpose-built time-series stores exist — and in the Azure world, why something like Azure Data Explorer (Kusto) is such a revelation for this data — is that they make the opposite assumptions from the ground up:
- They assume relentless append, so ingesting an endless firehose of new readings is the normal case they're optimised for, not the stress case that grinds them down.
- They assume time is the primary axis, so they store and index data by time in ways that make "give me this window, downsampled to this resolution" fast and natural rather than a fight.
- They assume enormous volumes of similar records, and compress and organise them accordingly, so a billion sensor readings is a Tuesday, not a crisis.
- They speak the language of time-series questions — windowing, aggregation over intervals, trend and pattern operations — as first-class citizens rather than things you bolt together awkwardly.
The first time I ran a time-windowed aggregation over a huge sensor dataset in a tool actually built for it, after months of coaxing the same query out of a struggling warehouse, the difference wasn't incremental. It was the difference between swimming with the current and against it.
A warehouse and a time-series store aren't competitors where one is better. They're specialists for opposite workloads. Using a warehouse for time-series isn't using a worse tool — it's using the wrong species of tool, and no amount of tuning changes what it fundamentally is.
A concrete example: the query that wouldn't finish
Let me make the mismatch tangible with the moment it finally clicked for me. I had months of sensor readings in the warehouse, and a perfectly reasonable question: give me the average reading per fifteen-minute window, per sensor, over the last quarter. In a time-series world that's a bread-and-butter query. Against the warehouse, it ran, and ran, and I sat there watching it the way you watch a kettle that isn't boiling, eventually killing it because it clearly wasn't going to finish in any time I'd call acceptable.
Nothing was misconfigured. The query was fine. The warehouse was simply doing the worst possible amount of work: grinding through an enormous, relentlessly-appended table, computing time-window aggregates using machinery built for dimensional joins, on a data shape it was never optimised for. I rewrote the exact same logic against a store built for time-series, and it came back in seconds — not because that tool is "faster" in some general sense, but because windowed aggregation over huge volumes of timestamped data is the one job it's built to do, and the warehouse's whole design points the other way. Same question, same data, two orders of magnitude apart — purely because of which engine's assumptions matched the workload.
The architecture this actually implies
So the lesson isn't "warehouses are bad" — I still love a warehouse for the business data it's brilliant at. The lesson is that a smart-building platform, or any serious IoT system, usually needs both, each doing its own job:
The high-volume, relentless, time-stamped sensor stream goes into a store built for time-series, where it can be ingested without strain and queried by time efficiently. And the business context — the tidy dimensional data about rooms, buildings, tenants, the structured records that give the readings meaning — lives happily in the relational warehouse it's suited to. You bring them together when you need to, but you don't force one to do the other's job.
The mistake I made, and the one I'm trying to save you from, was assuming that because I had a data store, and sensor data is data, it should go in the store I had. It's the same reflex I keep bumping into everywhere: reaching for the familiar tool because it's familiar, rather than because it fits the shape of the problem. Time-series is a particularly unforgiving teacher of that lesson, because it doesn't fail politely. It takes your data, says nothing, and gets slower and slower until you finally ask why — and discover the answer was structural all along. Match the store to the shape of the data, and the whole problem quietly stops being hard.