4. Data modeling for industrial assets

Industrial data platforms rarely deal with isolated sensors. A single production line contains motors, gearboxes, pumps, valves, and controllers, each carrying its own tags, and each of those tags belongs to a physical asset that sits somewhere in a plant hierarchy. Before any historian query or anomaly detection model can be trusted, the platform needs a data model that answers three questions: what the asset is, where it sits in the equipment tree, and how its signals are named and stored. This chapter covers equipment hierarchies, tag naming conventions, and the relational schema that ties them together.

Equipment Hierarchy: ISA-95 and Beyond

The most widely adopted reference for industrial asset hierarchy is ISA-95 (IEC 62264), which defines a functional hierarchy from Enterprise down to Work Unit. In practice, most analytics platforms implement a simplified five-level tree:

• Enterprise — the company or business unit (e.g., ACME_CORP)
• Site — a physical plant or facility (e.g., PLANT_NORTH)
• Area — a production area or process unit (e.g., UTILITIES, LINE_2)
• Equipment — a major asset such as a compressor or conveyor (e.g., PUMP_101)
• Component — a sub-assembly or measurement point (e.g., MOTOR, BEARING_DE)

This hierarchy is not merely organizational. It determines how you aggregate data, how you propagate asset metadata, and how you scope queries. A vibration analyst asking "show me all bearing temperatures on Line 2" relies on the tree to resolve LINE_2 into a set of equipment and component nodes. If the hierarchy is flat or inconsistent, every query becomes a manual enumeration of tag names.

A practical modeling decision is whether to store the hierarchy as a self-referencing table (adjacency list) or as a materialized path. The adjacency list is simple and normalized:

CREATE TABLE asset (
    asset_id        INTEGER PRIMARY KEY,
    asset_name      TEXT NOT NULL,
    asset_type      TEXT NOT NULL,   -- 'site','area','equipment','component'
    parent_id       INTEGER REFERENCES asset(asset_id),
    site_code       TEXT,
    criticality     TEXT,            -- 'A','B','C'
    commissioned_at DATE
);

For deep trees, recursive CTEs in PostgreSQL or SQL Server handle traversal efficiently. If your platform is read-heavy and the tree changes infrequently, a materialized path column (e.g., /PLANT_NORTH/UTILITIES/PUMP_101/MOTOR) allows fast prefix queries with a simple LIKE or starts_with predicate. Many industrial platforms use both: adjacency for integrity, path for performance.

Tag Naming Conventions

Tag names are the interface between the control system and the analytics platform. A well-designed convention encodes the asset path, the measurement type, and the signal role in a single string. The most common pattern in process industries follows the ISA-5.1 instrument identification style, extended with hierarchy prefixes:

<SITE>-<AREA>-<EQUIPMENT>-<COMPONENT>-<FUNCTION>-<MODIFIER>

Example:
PLANT_NORTH-UTILITIES-PUMP_101-MOTOR-VIB-DE
PLANT_NORTH-UTILITIES-PUMP_101-MOTOR-TEMP-WINDING
PLANT_NORTH-LINE_2-CONVEYOR_03-DRIVE-SPEED-ACT

The FUNCTION field uses ISA-5.1 letters: VIB for vibration, TEMP for temperature, SPEED for rotational speed, FLOW for flow rate. The MODIFIER distinguishes measurement location or role: DE (drive end), NDE (non-drive end), ACT (actual value), SP (setpoint).

Two rules matter more than the exact syntax. First, delimiters must be consistent — mixing hyphens, underscores, and dots across sites breaks parsing. Second, the convention must be enforced at ingestion. A tag that fails the pattern should be rejected or quarantined, not silently accepted. In practice, you implement this with a validation function or a regex check in the ETL pipeline:

import re

TAG_PATTERN = re.compile(
    r"^(?P<site>[A-Z0-9_]+)-(?P<area>[A-Z0-9_]+)-"
    r"(?P<equipment>[A-Z0-9_]+)-(?P<component>[A-Z0-9_]+)-"
    r"(?P<function>[A-Z]+)-(?P<modifier>[A-Z]+)$"
)

def parse_tag(tag: str) -> dict | None:
    m = TAG_PATTERN.match(tag)
    return m.groupdict() if m else None

When a tag fails validation, log the raw string, the source system, and the timestamp. This quarantine table becomes the first place to look when a dashboard shows missing data.

Relational Schema Design

The schema must connect three domains: the asset hierarchy, the tag catalog, and the time-series measurements. A normalized design uses separate tables for assets, tags, and readings, with foreign keys enforcing referential integrity.

The tag table is the bridge. Each tag row carries the parsed components from the naming convention, so queries can filter by asset_id, function, or modifier without string manipulation at query time. The reading table is typically partitioned by time (e.g., monthly partitions) and indexed on (tag_id, ts).

A common pitfall is storing the full tag name as the primary key in the reading table. This inflates storage and slows joins. Instead, resolve the tag to an integer tag_id during ingestion. The following query illustrates the benefit — it retrieves all drive-end vibration readings for a specific pump over the last 24 hours:

SELECT r.ts, r.value, r.quality
FROM reading r
JOIN tag t ON t.tag_id = r.tag_id
JOIN asset a ON a.asset_id = t.asset_id
WHERE a.asset_name = 'PUMP_101'
  AND t.function = 'VIB'
  AND t.modifier = 'DE'
  AND r.ts >= NOW() - INTERVAL '24 hours'
ORDER BY r.ts;

Without the parsed columns in tag, this query would require LIKE '%VIB-DE', which cannot use an index efficiently and is fragile to naming variations.

Putting It Together: A Worked Example

Consider a plant with two areas, UTILITIES and LINE_2. The utilities area contains PUMP_101 with a motor component. The motor has three tags: vibration drive-end, vibration non-drive-end, and winding temperature. The hierarchy and tag relationships can be visualized as follows.

With this model in place, a query for all vibration tags on PUMP_101 returns two rows, and a query for all tags under UTILITIES returns three. The hierarchy and the tag catalog work together to make those queries declarative rather than procedural.

Validation and Common Failure Modes

After loading the schema, verify it with a few checks. First, confirm that every tag has a valid asset_id — orphan tags indicate a parsing or mapping error. Second, check for duplicate tag names within the same asset; duplicates usually mean two source systems are publishing the same signal under different names. Third, validate that the function and modifier columns are populated for all tags that follow the convention.

-- Orphan tags
SELECT t.tag_id, t.tag_name
FROM tag t
LEFT JOIN asset a ON a.asset_id = t.asset_id
WHERE a.asset_id IS NULL;

-- Duplicate tag names per asset
SELECT asset_id, tag_name, COUNT(*)
FROM tag
GROUP BY asset_id, tag_name
HAVING COUNT(*) > 1;

Typical failure symptoms include dashboards showing NULL for a specific asset, which usually means the tag-to-asset join failed; and aggregation queries returning fewer rows than expected, which often points to a missing parent node in the hierarchy. When these occur, inspect the quarantine table first, then verify the parent_id chain for the affected asset.

A well-designed asset model is not a one-time deliverable. It evolves as equipment is added, renamed, or decommissioned. Treat the hierarchy and tag catalog as versioned artifacts: record when an asset was commissioned or retired, and keep historical readings linked to the asset version that was active at the time. This preserves the integrity of long-term trend analysis and makes root-cause investigations reproducible.