Thingsboard

name: Tin Can Thingsboard data description: Thingsboard device telemetry history data is stored in the RAW_DB.THINGSBOARD.THINGBSBOARD_REPORTS table.
This table contains 1 row per device (name) per historical import snapshot. For current state reporting, only the most recent historical import snapshot should be used, to avoid overstating device data. The full historical import snapshot can be used to trend data over time, but the default should always be to use the most recent snapshot data.

What this table is

A historical record of device state pulled from ThingsBoard. Every hour, an export of all devices is written to S3 (one CSV per snapshot) and loaded by Airbyte into this table. Each row captures the full state of one device at the moment of that hourly export.

This means:

The same device (ID) appears in roughly 24 rows per day — one per hourly snapshot.
"Current state" of a device = the most recent row for that ID.
"History" of a device = all rows for that ID, ordered by EXTRACTED_AT.
The table is append-only; old snapshots are never updated or deleted by the pipeline.

Two timestamps, two different meanings

This is the single most important thing to understand before querying.

EXTRACTED_AT is when we pulled the snapshot from ThingsBoard, and it is the same for every device in a given hourly batch. Use this when you want to know "what did the fleet look like at time X."

It is TEXT, not a TIMESTAMP — a uniform 24-character ISO 8601 string like 2026-09-10T22:03:37.914Z. That is not a defect to work around. ISO 8601 sorts lexicographically in the same order as chronologically, so string comparison does everything a timestamp would, and casting the column is actively expensive. See "Filtering this table cheaply" below.

<column>_LAST_ACTIVITY_TIMESTAMP is when ThingsBoard last received that specific telemetry value from the device. Stored as Unix epoch milliseconds (e.g., 1761706509464), not a timestamp type — you'll need to convert. Use these when you want to know "when did this device last report its WiFi RSSI / firmware version / etc."

A device that's offline will keep appearing in new hourly snapshots with stale *_LAST_ACTIVITY_TIMESTAMP values. The snapshot time keeps advancing; the activity timestamps don't. The gap between them is how you measure staleness per-metric.

Converting epoch milliseconds in Snowflake:

TO_TIMESTAMP_NTZ(WIFI_RSSI_LAST_ACTIVITY_TIMESTAMP / 1000)

Filtering this table cheaply

This is the largest table in the warehouse, and its micro-partitions are ordered by EXTRACTED_AT because Airbyte appends in snapshot order. A literal string bound on that column prunes nearly all of it away. The obvious alternatives do not, and the difference is three orders of magnitude.

Always bound the window first, then reduce inside it.

with recent as (

    select *
    from raw_db.thingsboard.thingsboard_reports
    where extracted_at >= to_char(dateadd('hour', -30, current_timestamp()),
                                  'YYYY-MM-DD"T"HH24:MI:SS')

), latest_snapshot as (

    select *
    from recent
    where extracted_at = (select max(extracted_at) from recent)

)
select *
from latest_snapshot;

Thirty hours is the recommended window — wide enough to survive this pipeline's known gaps, narrow enough to prune hard. The bound works even though it is computed, because current_timestamp() resolves at compile time.

Note what the second CTE shows: max(extracted_at) is perfectly fine inside an already-pruned window. It is only ruinous when it runs unbounded against the whole table.

Measured with EXPLAIN against 2,581 micro-partitions holding 363M rows:

filter as written	partitions scanned	bytes
`= (select max(extracted_at) ...)` unbounded	4,298	63.9 GB
unbounded `qualify row_number() = 1`	2,581	33.6 GB
`to_timestamp(extracted_at) >= ...`	1,717	30.3 GB
`>= '2026-09-09' and < '2026-09-10'`	192	499 MB
`>= '2026-09-10T20:00:00.000Z'`	24	62 MB

The top row is the one to internalize. Asking for "the latest snapshot" the obvious way reads more than the whole table, because Snowflake evaluates the subquery across every partition and then scans again. Bounding first turns that into 62 MB.

Clustering would not help and is not worth proposing: the table is already perfectly ordered on EXTRACTED_AT (clustering depth 1.0), so a clustering key would bill Automatic Clustering for no gain. The cost here is compute, not storage — 31 GB is well under a dollar a month.

Columns by category

Identity

Column	Notes
`ID`	ThingsBoard device UUID. Stable across snapshots — this is the join key for tracking a single device over time.
`NAME`	Device name as set in ThingsBoard. Often a MAC address. Can change if renamed in ThingsBoard, so prefer `ID` for joins.
`DEVICE_PROFILE`	The ThingsBoard device profile assigned to the device (e.g., `Tin Can Devices (Certificate)`, `Tin Can Devices (Customer Activated)`). Useful for segmenting fleet by provisioning state.
`BETA_COHORT`	Cohort flag for beta program participation. Mostly `0`.

Firmware

Column	Notes
`FIRMWARE_VERSION`	Version string the device reports. Empty / `0` if never reported.
`FW_STATE`	Firmware update state. Mixed values: `0` (idle/unknown), `DOWNLOADING`, and other states the device emits during OTA. Treat as a string.
`FIRMWARE_VERSION_LAST_UPDATED_TIME`	Epoch ms — last time the device reported its firmware version.
`FW_STATE_LAST_ACTIVITY_TIMESTAMP`	Epoch ms — last time `FW_STATE` was updated.

Network & connectivity

Column	Notes
`ONLINE_STATUS`	Boolean (`true` / `false`) as ThingsBoard saw it at snapshot time. Devices can flap; use `LASTACTIVITYTIME` for a more reliable "is this device alive" check.
`WIFI_RSSI`	WiFi signal strength (dBm, negative number — closer to 0 is stronger). `0` typically means never reported.
`LASTACTIVITYTIME`	Epoch ms — last time ThingsBoard received any message from the device. The most useful "is this device alive" field.
`ONLINE_STATUS_LAST_ACTIVITY_TIMESTAMP`	Epoch ms — last time online status flipped.
`WIFI_RSSI_LAST_ACTIVITY_TIMESTAMP`	Epoch ms — last RSSI report.
`LASTACTIVITYTIME_LAST_ACTIVITY_TIMESTAMP`	Epoch ms — meta-timestamp on when `LASTACTIVITYTIME` itself was updated. Rarely needed; `LASTACTIVITYTIME` is usually what you want.

SIP (call functionality)

Column	Notes
`SIP_STATUS`	SIP registration status.
`SIP_RETRY_COUNT`	Number of SIP registration retries. High values suggest a device struggling to register.
`SIPSERVERCERT`	SIP server certificate state/value.
`SIP_STATUS_LAST_ACTIVITY_TIMESTAMP`	Epoch ms.
`SIP_RETRY_COUNT_LAST_ACTIVITY_TIMESTAMP`	Epoch ms.
`SIPSERVERCERT_LAST_ACTIVITY_TIMESTAMP`	Epoch ms.

Device health (heap & uptime)

These come from the device's runtime. All numeric; 0 typically means never reported.

Column	Notes
`UPTIME_S`	Seconds since device boot. Resets to a low number after a reboot — useful for detecting reboots in history.
`HEAP_FREE`	Bytes of free heap memory.
`HEAP_MIN`	Minimum free heap observed (low-water mark). Lower = closer to OOM.
`HEAP_LARGEST_BLOCK`	Largest contiguous free heap block. Low values relative to `HEAP_FREE` indicate fragmentation.
`WORKER_WDT_REBOOT_COUNT`	Count of watchdog-triggered reboots. Non-zero values are worth investigating.
`UPTIME_S_LAST_ACTIVITY_TIMESTAMP`	Epoch ms.
`HEAP_FREE_LAST_ACTIVITY_TIMESTAMP`	Epoch ms.
`HEAP_MIN_LAST_ACTIVITY_TIMESTAMP`	Epoch ms.
`HEAP_LARGEST_BLOCK_LAST_ACTIVITY_TIMESTAMP`	Epoch ms.
`WORKER_WDT_REBOOT_COUNT_LAST_ACTIVITY_TIMESTAMP`	Epoch ms.
`BETA_COHORT_LAST_ACTIVITY_TIMESTAMP`	Epoch ms.

Ingestion metadata (generally ignore)

These are added by Airbyte and the source-file convention. Useful for debugging the pipeline, not for analysis.

Column	Notes
`EXTRACTED_AT`	Exception to "ignore" rule — this is the snapshot timestamp and the most useful time field in the table. Same value for every device in a given hourly batch.
`_AIRBYTE_RAW_ID`	Airbyte's per-row unique ID.
`_AIRBYTE_EXTRACTED_AT`	When Airbyte loaded the row into Snowflake. Slightly later than `EXTRACTED_AT`. Use `EXTRACTED_AT` for analysis.
`_AIRBYTE_META`	JSON of Airbyte sync metadata.
`_AIRBYTE_GENERATION_ID`	Airbyte generation counter.
`_AB_SOURCE_FILE_URL`	S3 path of the source CSV for this row. Useful when reconciling against the raw export.
`_AB_SOURCE_FILE_LAST_MODIFIED`	When the source CSV was last modified in S3.

Gotchas to keep in mind

Watch for duplicate-row inflation. Any aggregate (COUNT, SUM, AVG) across the whole table multiplies by the number of snapshots. For "how many devices do we have," you want one row per device — but reduce inside a bounded window, never across the whole table. An unbounded QUALIFY ROW_NUMBER() OVER (PARTITION BY ID ORDER BY EXTRACTED_AT DESC) = 1 gets the right answer by reading 33 GB to do it. Bound on EXTRACTED_AT first, as shown in "Filtering this table cheaply".

Empty strings are not NULLs. The CSV ingestion produces empty strings ('') for missing values in some columns rather than proper NULLs. WHERE col IS NOT NULL won't filter these out — use NULLIF(col, '') or WHERE col <> ''.

Numeric-looking columns are sometimes strings. FW_STATE is the obvious one (mixes 0 with strings like DOWNLOADING), but inspect column types in Snowflake before assuming you can do math. Cast explicitly with TRY_CAST(col AS NUMBER) to fail safely.

0 often means "never reported," not literal zero. Especially for HEAP_*, UPTIME_S, WIFI_RSSI, and WORKER_WDT_REBOOT_COUNT. When analyzing these, consider filtering to rows where the corresponding *_LAST_ACTIVITY_TIMESTAMP is non-zero / non-empty.

ONLINE_STATUS is point-in-time and can flap. For reliable liveness, compute it from LASTACTIVITYTIME against a threshold (e.g., "active in the last 15 minutes") rather than trusting the boolean directly.

NAME is not a stable identifier. It can be edited in ThingsBoard. Use ID for joining across snapshots or to other tables.

Snapshot cadence isn't always exactly hourly. Pipeline delays, retries, and ThingsBoard-side hiccups can produce gaps or doubled snapshots. Don't assume COUNT(*) / COUNT(DISTINCT ID) = 24 per day exactly.