Medallion layers

The current data platform keeps all research data in one central Unity Catalog schema, centrum. This replaces the per-experiment schema model described in the project's 2025 historical ADRs. Two other pipelines write alongside the main one. Macro execution runs in its own pipeline and publishes experiment_macro_data and experiment_macro_data_schemas into centrum, because a macro is a synchronous HTTP call from a Spark task and sharing the ingest pipeline's compute let those calls hold the slots the stream reader needed. A small sibling schema, metrics, holds platform activity rollups produced by a separate serverless DLT pipeline reading the centrum tables. Its public-facing tables are aggregate-only; two backend-only tables (daily_activity_by_experiment, experiment_contributors_window) carry experiment- and contributor-grain rows that the backend aggregates per organization or user and never exposes unaggregated. Experiment isolation is now expressed through experiment identifiers, experiment-facing tables, access checks, and filtered queries rather than a separate Databricks schema for every experiment.

Layers

The exact deployed names are configured through apps/data/src/lib/openjii/openjii/centrum/runtime.py; the pipeline definitions live under apps/data/src/pipelines/centrum/, and the macro pipeline's under apps/data/src/pipelines/macros/.

Bronze: preserve provenance

raw_data reads the Kinesis stream and retains broker/stream metadata such as the authenticated MQTT client ID, sequence number, shard, arrival time, and ingestion time. The parsed event remains close to what arrived. Imported transfer files and web-uploaded datasets use separate Bronze tables because their provenance and schemas differ.

Silver: normalize once

clean_data turns source records into a common representation. It decompresses sample when _sample_encoding is gzip+base64, validates timestamps, normalizes device and user fields, parses macros/questions/annotations, and unions supported non-MQTT sources. Invalid timestamps are dropped by a DLT expectation; other expectations retain observability without silently inventing values.

Gold: serve experiment tasks

Gold tables answer application questions without repeatedly unpacking raw JSON. They separate raw measurement rows, device summaries, macro results, uploaded data, contributors, and status. experiment_table_metadata holds the VARIANT schemas of each experiment table, which the backend needs to build its queries.

The device and contributor summaries and the status tables are built in two steps. The tables beneath are streaming tables maintained by AUTO CDC, so each processes only what a trigger brought in. bridge_experiment_device and bridge_experiment_contributor hold the distinct pairs that have produced data. The latest_* tables hold the newest row per key: per experiment in latest_experiment_activity, per device in latest_device_data and latest_device_event, and per experiment, device and firmware in latest_experiment_device. experiment_devices, experiment_contributors, experiment_device_data, experiment_status and device_last_activity sit above them as materialized views over one row per key, and add the registry and profile lookups, the fresh or stale status, and the join of data and connectivity. The split exists because a materialized view outside a serverless pipeline is always fully recomputed, so a view over silver scans every measurement on every trigger.

AUTO CDC keeps the latest value per key but cannot add up, and outside a serverless pipeline no table can keep a count incrementally. Row counts are therefore taken when someone reads, by the views described below.

The schemas in experiment_table_metadata are merged from the *_schemas tables, which keep one sample value per distinct schema of each VARIANT column, a few per experiment table. A streaming dropDuplicates keeps the first row for each key and holds only the keys. The merge reports decimals as DOUBLE: schema_of_variant gives every decimal its own precision, which would make nearly every macro result a schema of its own. Merging schemas ignores order and repeats, so merging the samples gives the same schema as merging every row.

Flexible result shapes use Databricks VARIANT values where the protocol or macro output cannot be represented by one rigid relational schema. Stable identifiers remain separate from display labels so renaming a macro does not change the table identity.

The payload tables shred their VARIANT columns (delta.enableVariantShredding): fields that recur across rows are stored as columns of their own, so a read that needs one field skips the rest of the payload. Only files written after shredding was enabled are shredded. REORG TABLE ... APPLY (SHRED VARIANT), which would rewrite the older ones, is refused on pipeline streaming tables, so those files keep their original layout until something else rewrites them. Every reader needs Databricks Runtime 17.3 or later.

Enriched views

The enriched data joins each measurement or macro result to its contributor, device, annotations and custom metadata. Annotations and custom metadata change after a measurement arrives, and every historical row has to show them as they are now. The joins are plain SQL views, defined in apps/data/src/views/ and created by terraform: a view stores no data, so the join runs when someone reads, for the experiment they read, and an edit shows on the next read. The gold tables are clustered by experiment_id and then by the order reads take: timestamp for raw data, macro_id and then timestamp for macro results, and upload table and upload time for uploads. A read then touches only that experiment's files, and a time window skips the older ones. Changing the keys reclusters new files only; OPTIMIZE ... FULL rewrites the existing ones.

Two more views add what cannot be stored incrementally. experiment_table_metadata_view adds each table's row count and an upload table's newest name to the schemas stored in experiment_table_metadata. experiment_device_data_view adds each device's measurement count per firmware to experiment_device_data, read from clean_data because gold carries no firmware.

The enriched views carry the names of the materialized views they replaced, so readers outside this repository still find them. The other two end in _view because the pipeline tables beneath them keep the plain names.

Macro execution pipeline

experiment_macro_data and experiment_macro_data_schemas belong to the macro pipeline, which streams experiment_raw_data from Centrum. Centrum's experiment_table_metadata reads experiment_macro_data_schemas by its qualified name, so that table has to exist before a Centrum update starts.

Why the layers matter

• Bronze retains evidence needed for replay and investigation.
• Silver centralizes compatibility and data-quality logic.
• Gold makes common reads predictable and economical.
• Enriched views keep user-facing joins consistent.

The layers are data-quality stages, not public authorization boundaries. Product access is still enforced by the backend and its experiment membership/visibility rules. See Data ingestion for how events enter Bronze.