Trackunit IrisX

View saved version →

---
name: irisx-analytics-sql
description: Orientation and query procedure for the IrisX Analytics data lake (Databricks Unity Catalog, catalog `irisx`). Use this when the user writes, reviews, debugs or explains SQL against irisx.* data; asks to query the data lake, Databricks, or the irisx catalog; or wants historical, aggregated, metric, trend, or cross-fleet analysis such as fuel usage, idle hours, engine hours, fault codes, or utilisation — even if no table is named. Do not use this for current lookups of an asset, site, depot, customer, group, or other entity details — GraphQL is the primary interface for live metadata, associations, maps, and Manager links. Trigger this BEFORE running any analytics SQL, because picking the wrong catalog or the wrong time-grain view is the most common failure and cannot be recovered after the fact.
---

# IrisX Data Lake

This skill gets you oriented and tells you how to look things up. It deliberately does **not** describe what individual tables and columns mean — that lives in the Unity Catalog comments, which are authoritative. Read them.

## Current entities belong on GraphQL

A question about an asset, site, depot, customer, or group as it exists now is a GraphQL lookup. GraphQL returns live metadata, associations, maps, and Manager links. This skill is for the data lake — SQL, history, aggregation, metrics, trends, and cross-fleet analysis. Do not start here for a current-entity question; the orientation below will not help, and it only adds latency.

## Start in the right catalog

Trackunit-provided fleet and telematics data lives in **`irisx`**. Always start there.

Other catalogs may be named similarly to the customer account slug. Those hold tables the *customer* uploaded themselves — not Trackunit data. Never infer a catalog name from the account slug. Only leave `irisx` if the user explicitly asks about their own uploaded tables.

## The catalog describes itself — read it before guessing

Every catalog object carries a table comment, and core-schema columns carry column comments. These are the source of truth for business meaning. Blind-guessing table names is the single most common failure mode here, and it is entirely avoidable:

```sql
DESCRIBE TABLE EXTENDED irisx.<schema>.<table>;
```

Do this before explaining, joining, or filtering a table you have not already looked up in this session. If a comment is missing or contradicts what the name implies, **trust the comment and say the name is misleading** — several are.

If a table or column has no comment and you cannot determine its meaning from the catalog, say so plainly and ask. Do not infer meaning from the name. A wrong confident answer about fleet data is worse than an admitted gap, because the user cannot tell the difference without re-doing your work.

## Orientation

`asset.asset` is the hub. Nearly every other schema hangs off `asset_id`. When unsure how two tables relate, check whether both carry `asset_id` before looking for anything cleverer.

| Domain | Schema(s) |
|---|---|
| Accounts & customers | `account` |
| Users & groups | `user`, `group` |
| Asset (hub) | `asset` |
| Devices & device state | `telematics_device` |
| Raw device telemetry | `telematics_device_telemetry` |
| Computed metrics | `insight` |
| OEM / custom sensors | `advanced_sensor` |
| Faults & CAN errors | `event` |
| Sites & access keys | `site`, `access_management` |
| Maintenance | `service_plan` |
| Platform / billing / calendar | `irisx`, `usage`, `dimension` |

## Finding the right metric — do not enumerate

The `insight` schema ships many metric families, each with five grains: base, `_2min`, `_hour`, `_day`, `_latest`. Never list them all, and never guess a metric name. Search:

```sql
SELECT table_name, comment
FROM irisx.information_schema.tables
WHERE table_schema = 'insight'
  AND table_name LIKE '%fuel%'          -- keyword from the user's question
ORDER BY table_name;
```

Try a couple of keyword spellings before concluding 

Preview of the first 4,000 characters. Read full instructions →