4. Profiling semi-structured and unstructured engineering data

Structured tables have schemas you can query directly. JSON documents, XML payloads, log lines, and sensor streams do not. Before you can compute a single quality metric, you must first discover what structure actually exists — and how consistently it holds. This chapter covers the profiling techniques that turn opaque payloads into measurable, comparable datasets.

Why These Formats Break Conventional Profiling

Column-oriented profilers assume fixed field names, stable types, and one value per cell. Semi-structured and unstructured data violate all three assumptions:

• JSON/XML — fields may be optional, nested arbitrarily deep, or appear as arrays of mixed types. The same logical entity can be represented in several shapes across records.
• Log files — structure is implied by a format string, not declared. A single malformed line can shift every downstream field.
• Sensor streams — values arrive continuously with timestamps, gaps, duplicates, and out-of-order delivery. "Rows" are really time-ordered events.

The profiling goal shifts from "describe columns" to "infer and validate a schema, then measure conformance to it."

Profiling JSON and XML: Schema Inference First

For document formats, the first deliverable is a field inventory: every path that appears, its observed types, its presence rate, and its cardinality. Treat each document as a tree and walk it recursively.

# Example: flatten and inventory JSON paths (Python, illustrative)
import json, collections

paths = collections.defaultdict(lambda: {"count": 0, "types": set(), "nulls": 0})

def walk(node, prefix=""):
    if isinstance(node, dict):
        for k, v in node.items():
            walk(v, f"{prefix}.{k}" if prefix else k)
    elif isinstance(node, list):
        for item in node:
            walk(item, prefix + "[]")
    else:
        p = paths[prefix]
        p["count"] += 1
        p["types"].add(type(node).__name__)
        if node is None:
            p["nulls"] += 1

for line in open("events.jsonl"):
    walk(json.loads(line))

Run this over a representative sample and you get a table like the following. The presence column is the fraction of documents containing the path; type_conflict flags paths where more than one type was observed.

Two findings jump out. event.ts mixes string and integer representations — a classic ingestion bug where some producers emit epoch seconds and others emit ISO-8601. payload.raw is sometimes a string and sometimes an object, meaning downstream parsers must branch. Both are quality defects that a column profiler would never surface.

XML profiling follows the same logic but must also track attributes versus elements, namespaces, and repeated sibling elements. Record the XPath of each node, its occurrence count per document, and whether it is required by any schema (XSD) if one exists.

Log Files: Pattern Extraction and Field Stability

Logs are line-oriented but not self-describing. The practical approach is to cluster lines by a template — the constant text with variable slots — then profile the slots.

2024-03-11T08:14:02Z INFO  svc=auth user=u4471 status=200 latency_ms=38
2024-03-11T08:14:02Z WARN  svc=auth user=u4471 status=401 latency_ms=12
2024-03-11T08:14:03Z INFO  svc=orders user=u9920 status=201 latency_ms=104

Template mining collapses these into <ts> <level> svc=<svc> user=<user> status=<status> latency_ms=<latency>. Once you have templates, profile each slot: status should be a small integer set, latency_ms a non-negative number, user a high-cardinality identifier. A slot that suddenly contains free text or a null indicates a format drift or an unhandled exception path.

Sensor Streams: Time-Aware Profiling

Streaming sensor data adds a temporal dimension. Profiling must measure not just value distributions but arrival behavior:

• Sampling interval — compute the distribution of deltas between consecutive timestamps. A nominal 1 Hz sensor should show deltas clustered near 1000 ms; a bimodal distribution reveals dropped or duplicated batches.
• Gaps — count intervals exceeding a threshold (e.g., 3× nominal). Record gap start, duration, and affected sensor IDs.
• Out-of-order events — compare event time to ingestion time. A positive skew means late arrival; a negative skew means clock drift on the device.
• Value range and rate of change — flag readings outside physical bounds and step changes exceeding a per-sensor limit.

For a temperature sensor reporting in °C with a nominal 1 Hz cadence, a reasonable profile might show: median delta 998 ms, p99 delta 1,240 ms, 14 gaps over 5 s in a 24-hour window, and 3 readings above 150 °C — the last being physically implausible and almost certainly a sensor fault or a unit-conversion error.

Choosing the Right Profiling Pass

The diagram below maps each data family to the profiling artifacts it produces and the quality signals those artifacts expose.

Practical Workflow and Baseline Discipline

Profiling semi-structured data is iterative. A workable sequence:

1. Sample — draw 10,000–50,000 records or a full day of stream data, whichever is smaller. Ensure the sample spans known operational phases (startup, steady state, peak).
2. Infer — run path/template extraction and build the field inventory or template set.
3. Quantify — compute presence rates, type sets, null rates, cardinalities, and for streams the temporal metrics above.
4. Baseline — persist the profile as a versioned artifact. This is the reference for future comparisons.
5. Monitor — re-profile on a schedule or on trigger (new deployment, schema change). Diff against the baseline.

Common Failure Patterns and How to Catch Them

The unifying discipline is simple: never assume structure you have not measured, and never trust a measurement you have not baselined. JSON, XML, logs, and streams each demand a slightly different extraction technique, but they all reduce to the same two questions — what is the structure, and how consistently does the data conform to it?