Timesream multi-measure vs single measure record - best practices

Question

I have 3 types of devices in the same site (I need them in the same table, cause join between tables is not supported)
device A - have 3 measures:   temperature, power, voltage
device B - have 3 measures:   temperature, power, soc
device C -have 2 measures:  temperature, power

I have 2 option - which on is the best?
1) single measure table
2) multi measure table -  however soc and/or voltage column will be null in more than half of the records.

Will the answer be any different if there was 20 common measures (that exists in all record) and 2 specific measures (that exist in only some of the records)? What about 10 common and 1 specific?
Is there any rule of thumb or best practice when to choose multi over single?
Is there any cost to an empty column in a multi record table?

Thank You!!

Answer

One of the reasons to choose multi measure table is if the query pattern involves comparison between different measures in the same query For e.g. return all rows where avg(temperature) > avg(power). If the measures are stored in a single measure table, then the query is complicated and involves multiple CTEs. With multi measure table the query is simple and intuitive.

Timestream supports joins between tables but joins come with their own cost depending upon the number of rows to be joined.

Timestream does not support NULL values on ingestion and hence if the ingested record does not include a measure, then it will be not charged for storage.

Timesream multi-measure vs single measure record - best practices

Relevant content