Are temporal tables suggested in IoT scenarios?

Question

I read about temporal tables here https://learn.microsoft.com/it-it/azure/azure-sql/temporal-tables and see that they are suggested in IoT scenarios here https://techcommunity.microsoft.com/t5/azure-sql/build-your-full-paas-iot-solution-with-azure-sql-database/ba-p/1759194.
From what I understand, temporal tables seems to be suggested in order to keep track of substantial changes. In an IoT scenario where the devices are sending metrics (e.g temperature, humidity), would you suggest storing this kind of data inside a database with active temporal tables? What would be the advantage of doing this?

Answer 1

Hello @paolo.dallapiazza , Thanks for your query, sorry for the delay in responding.
We have received response from our team and below is the quote.

Temporal Tables are definitely an option for storing data in a IoT solution. They can help in many scenarios, one is certainly the so called "shock absorber" where you use memory-optimized temporal tables to ingest lots of data in memory and let the engine to offload data to a long-term columnstore based table for historical analysis.
The scenario is explained in this article and related code sample:
https://azure.microsoft.com/en-us/blog/in-memory-oltp-in-azure-sql-database/
https://learn.microsoft.com/en-us/sql/relational-databases/tables/system-versioned-temporal-tables-with-memory-optimized-tables?redirectedfrom=MSDN&view=sql-server-ver15

Apart from using temporal tables, you may be interested to know about Azure Time Series Insights(TSI). Quote from Azure TSI Product Team.

• TSI is not only a time series database but it’s a fully managed, end-to-end analytics platform, tailored/optimized to be used as IoT data historian packed with rich analytical functions
• Custom built for IoT scale. Millions of events (telemetries) from millions of assets from the filed
• TSI’s ingestion service natively understand various IoT specific event sources such as IoTHub, EventHub, etc.
• Asset modeling (meaningfully connecting data with field assets ) and IoT specific analytical functions (such as time weighted average, etc) are inbuilt
• Customers own the data in their own storage, in an open standard format. Data retention is infinite as opposed to temporal table where retention is time bound. Temporal table also recommends not to have longer retention period
• Finally, while customers can build custom applications, TSI comes with a rich user experience that is custom-built for IoT and historical time series data analytics !

Fleet queries, Calc engine to support derived analytics and AI-ML based analytics, integration with Azure Digital Twins and PnP are few in-pipeline items worth mentioning in the given context.

Please comment in the below section if you have any further questions regarding this matter.