Medallion layers
The current Bronze, Silver, Gold, and enriched-view model in the centrum schema.
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
| Layer | Current tables | Responsibility |
|---|---|---|
| Bronze | raw_data, raw_imported_data, raw_uploaded_data, raw_large_data | Preserve source events and source-specific metadata with minimal transformation. |
| Silver | clean_data | Parse and normalize records, decompress samples, validate timestamps, unify MQTT/import/upload sources, and retain provenance fields. |
| Gold | experiment_raw_data, experiment_devices, experiment_macro_data, experiment_uploaded_data, experiment_contributors, experiment_table_metadata, and the append-only lists beneath them: bridge_experiment_device, bridge_experiment_contributor, experiment_device_keys, experiment_raw_data_schemas, experiment_uploaded_data_schemas | Produce experiment-oriented facts, device and contributor summaries, macro output, upload data and query metadata. |
| Enriched views | enriched_experiment_raw_data, enriched_experiment_macro_data, enriched_experiment_uploaded_data | Join display and user context for application-facing reads without duplicating it into every raw record. |
| Status views | experiment_device_data, experiment_status, device_last_activity | Compute each device's and experiment's current state when someone reads. |
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 are built in two steps. bridge_experiment_device and bridge_experiment_contributor hold the distinct pairs that have produced data, and experiment_device_keys each experiment's distinct devices and firmware. A streaming dropDuplicates appends each of them and holds only the keys in its state. experiment_devices and experiment_contributors sit above the bridges as materialized views and add the registry and profile lookups. 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.
Nothing in Centrum merges. Every MERGE is followed by an auto compaction on the pipeline's driver that cannot be turned off, and while the latest-per-key tables were kept by AUTO CDC those compactions came with the driver's heap filling within about 32 hours. Outside a serverless pipeline no table can keep a count, or a newest value per key, without merging, so row counts and each device's and experiment's status are taken when someone reads, by the views described below. tests/lib/test_centrum_append_only.py fails if a Centrum flow merges again.
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.
The other views add what cannot be stored without merging. 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, counting a device table's rows in experiment_device_data. The status views compute state from the rows themselves. device_last_activity holds each device's newest measurement and newest connectivity event. experiment_status holds each experiment's newest row and whether it arrived within the hour. experiment_device_data holds each device's newest attributes and measurement count per firmware, read from clean_data because gold carries no firmware; experiment_device_data_view is the same view under the name the backend reads.
The enriched and status views carry the names of the tables they replaced, so readers outside this repository still find them. experiment_table_metadata_view ends in _view because the pipeline table beneath it keeps the plain name.
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.