4. Data modeling and schema design for heterogeneous simulation and test data.

Simulation and test campaigns rarely produce a single, tidy dataset. A typical program generates time-series telemetry from sensors, discrete event logs from avionics or control buses, structured parameter sweeps from Monte Carlo runs, image or point-cloud artifacts from optical and radar instruments, and free-text operator annotations. Each of these has a different natural shape: regular sampling versus irregular events, dense numeric arrays versus sparse key-value metadata, binary blobs versus tabular records. Data modeling is the discipline of deciding how these heterogeneous sources are represented, related, and queried so that downstream analytics, replay, and certification evidence remain trustworthy.

The core problem is that a single physical "test" is described by many partial records that must be joined back together. A vibration test, for example, has a test article, a fixture, a sensor layout, a shaker profile, an acquisition configuration, and the resulting waveform. If each of these lives in a separate silo with its own identifier scheme, reconstructing the test becomes an exercise in manual matching. Schema design exists to make that reconstruction deterministic.

Separating Identity, Structure, and Payload

A workable model separates three concerns. Identity answers "what is this thing and how do I reference it" — a stable, globally unique key for every test run, sensor, channel, and artifact. Structure describes the relationships and the shape of the data: which channels belong to which run, which run belongs to which campaign, what the sampling rate and units are. Payload is the actual measured or simulated values, which are usually large and should be stored in a format optimized for sequential or columnar access rather than in the metadata store.

Keeping payload out of the metadata layer is the single most important decision. Metadata queries (find all runs where the article was WING-A and the excitation exceeded 5 g) should be fast and cheap; they should not require scanning terabytes of waveform samples. This is why production systems pair a relational or catalog store for identity and structure with object storage or a columnar format for payload.

Choosing a Modeling Approach

Three broad approaches dominate, and the right choice depends on how much the schema is expected to change and how varied the sources are.

For heterogeneous simulation and test data, the hybrid approach is usually the pragmatic default. A central catalog holds run-level and channel-level metadata in a relational schema, while payloads are written to Parquet, HDF5, or a time-series store depending on access pattern. The catalog is the contract; the payload format is an implementation detail behind it.

A Concrete Catalog Schema

The following example shows a minimal but realistic catalog for a test campaign. It is illustrative, not a prescription — field names and types should follow your organization's conventions.

-- Campaign groups related test runs
CREATE TABLE campaign (
    campaign_id     TEXT PRIMARY KEY,
    name            TEXT NOT NULL,
    article_id      TEXT NOT NULL,
    start_date      DATE,
    objective       TEXT
);

-- A single execution of a test or simulation
CREATE TABLE test_run (
    run_id          TEXT PRIMARY KEY,
    campaign_id     TEXT REFERENCES campaign(campaign_id),
    run_type        TEXT NOT NULL,      -- 'simulation' | 'ground_test' | 'flight_test'
    started_at      TIMESTAMPTZ NOT NULL,
    ended_at        TIMESTAMPTZ,
    software_version TEXT,
    config_hash     TEXT                -- links to the exact configuration used
);

-- One measurement channel within a run
CREATE TABLE channel (
    channel_id      TEXT PRIMARY KEY,
    run_id          TEXT REFERENCES test_run(run_id),
    name            TEXT NOT NULL,      -- e.g. 'ACCEL_01_X'
    unit            TEXT NOT NULL,      -- e.g. 'g', 'Pa', 'V'
    sample_rate_hz  DOUBLE PRECISION,
    payload_uri     TEXT NOT NULL,      -- location of the actual samples
    payload_format  TEXT NOT NULL       -- 'parquet' | 'hdf5' | 'csv'
);

Two design choices deserve attention. First, config_hash links a run to the exact configuration that produced it, which is essential for reproducibility: two runs with the same hash should be comparable, and a changed hash explains why results differ. Second, payload_uri plus payload_format decouples the catalog from storage technology, so migrating from CSV to Parquet does not require changing the query layer.

Handling Units, Time, and Coordinate Conventions

Heterogeneous data fails most often at the seams: a channel recorded in g compared against one recorded in m/s², or timestamps from two acquisition systems with different clock origins. The schema must make these explicit rather than implicit.

• Units: store the unit string with every channel and normalize during ingestion where possible. Never assume a default.
• Time: record all timestamps in UTC with an explicit timezone type. If a source uses relative time (seconds since trigger), store the trigger time separately so absolute time can be reconstructed.
• Coordinates: define the reference frame once per run and store it as metadata. If a sensor's axis orientation differs from the article frame, record the transformation rather than silently rotating the data.

Schema Evolution and Validation

Test programs evolve: new sensors are added, old ones retired, sampling rates change. A schema that cannot absorb these changes without breaking existing queries will be bypassed, and once bypassed it stops being a source of truth. Two practices keep it viable. First, use additive evolution — add nullable columns or new tables rather than renaming or retyping existing ones. Second, validate at ingestion: reject or quarantine records that violate the contract (missing unit, unknown channel, payload URI that does not resolve) rather than letting them silently corrupt downstream analysis.

The diagram below summarizes how a heterogeneous source flows through validation into the catalog and payload stores, and how a query reconstructs a complete test.

Practical Guidance

Start from the questions you need to answer, not from the data you happen to have. If the recurring question is "which runs exceeded a threshold on a given channel," the catalog must support filtering by channel name, unit, and run metadata without touching payloads. If the recurring question is "replay this run exactly," the catalog must capture configuration and software version precisely enough to reproduce it.

Resist the temptation to model everything as a generic key-value store. Flexibility without structure pushes complexity into every query and makes validation impossible. A modest, well-documented schema with explicit units, time conventions, and payload references will outperform a schema-less approach for any campaign that needs to be audited, compared, or reproduced — which is to say, almost every real simulation and test program.