dwd-medallion-data-layers

Documentation status: MIXED / IN PROGRESS Started: 2026-10-15. Brainstorm and implementation outline for telemetry and client-supplied operational data (SCADA, meters, similar feeds). Not yet locked with product. Related (current product): dwd-per-project-scada-connection-design.md, dwd-fastapi-scada-api-design.md, DeviceModels README, NetworkManager devices scripts.

DWD: Medallion data layers (bronze / silver / gold)

Purpose

Define a medallion architecture for external / client operational data that DWD ingests (SCADA, water meters, and future sources). The goal is:

1. Preserve what the client sent (bronze).
2. Parse and normalize into a stable internal shape (silver) — where the product lives today.
3. Quality-assure into a production dataset the API, UI, and workers use exclusively (gold) — where we want to end up.

This document is intentionally separate from simulation results storage (dwd-output/.../timeseries-parquet/, timestep JSON, etc.). Results already follow a blob-heavy, Parquet-oriented pattern documented under dwd-results-storage-v2-design.md. Telemetry medallion may reuse the same Azure account and naming discipline, but it is a different domain (client feeds vs EPANET run outputs).

Naming note: EPANET input files are sometimes called “Gold Standard …” in filenames. That is model QA naming, not this medallion gold layer. In this doc, gold always means quality-assured operational telemetry.

---

Summary (proposed)

Layer	Meaning	Typical contents	Proposed storage (target)	Today (approx.)
Bronze	Immutable as-received artifacts	Excel, CSV, Access/SQLite exports, shapefiles, vendor zips, email attachments	Blob (+ ingest metadata in Azure Table)	Analyst laptops / ad hoc folders only
Silver	Parsed, typed, joinable to DWD entities	SCADA tags & samples; device metadata & readings; asset cross-refs	PostgreSQL per project (ScadaDatabaseName); optional Parquet shards in blob for large series; Azure Table for batch/lineage catalog	Postgres only — parsed data loaded by NetworkManager / one-off scripts
Gold	Trusted data for product surfaces	Same entities as silver after QC rules; documented units; deduped keys; outlier policy applied	Same technologies as silver, separate schemas/prefixes/tables (or materialized views) until cutover	Same as silver — no separate gold yet

End state: FastAPI, workers, and the SPA read gold only. Silver remains for reprocessing; bronze remains for audit and re-parse.

---

Scope: data sources

Initial sources called out for medallion treatment:

Source	Bronze examples	Silver entities (existing)	Gold concerns (examples)
SCADA	Historian exports, CSV dumps, SQL extracts	ScadaTag, ScadaTagData, asset/tag xref, zones (SD2Models / SCADA Postgres)	Spike removal, stale tag detection, unit consistency, gap handling for calibration
Water meters / devices	Billing CSV, AMI exports, shapefile + consumption join	Device, DeviceTag, DeviceTagDatum, DeviceGroup (DeviceModels)	Billing period alignment, duplicate serials, consumption outliers, missing join keys to AssetId
Weather / demand drivers (future)	CSV, API pull snapshots	TBD tables or parquet partitions	Coordinate system, timezone, station ↔ model node mapping
GIS / asset overlays (optional bronze)	Shapefile, FGDB, client geodatabase	Hosted layers / DB geometry (parallel to SCADA ArcGIS backlog)	CRS normalization, version pinning

Sources out of scope for this doc’s first pass (but may mirror similar layers later):

• EPANET .inp and scenario JSON — already dwd-input / dwd-scenarios (azure worker addendum).
• Simulation timeseries — dwd-output/.../timeseries-parquet/ (results medallion = “run output”, not client feed).

---

Current-state pipeline (implicit silver)

Today the stack behaves as if bronze does not exist in cloud storage:

Implications:

• Reproducibility depends on whoever still has the original file.
• Re-parse requires re-obtaining files from the client or from personal archives.
• Lineage (which file produced which row) is weak or absent unless scripts log it manually.
• Product reads hit Postgres directly — effectively silver = production.

Ingest tooling references:

• Devices: epanetconsolecore/NetworkManager/python/devices (parse_devices_csv, parse_timeseries_csv_v2, etc.).
• SCADA: legacy ASP.NET SD2 paths and worker Dwd.Scada (SCADA API design) — same ScadaDatabaseName database as devices.

---

Target-state pipeline

Principles:

1. Bronze is append-only. Corrections arrive as new batches, not overwrites of the original blob (new version id / new path).
2. Silver is reproducible from bronze given documented parser version + batch id.
3. Gold is the contract for HTTP APIs and calibration — schema stability, QC flags, and retention policy documented per source.
4. Idempotent loads: (ingestBatchId, naturalKey, timestamp) drives upserts into silver; gold recomputes from silver snapshots or incremental QC jobs.

---

Layer definitions (detailed)

Bronze — raw landing

What belongs here

• Every file (or exported snapshot) exactly as received, plus optional sidecar metadata supplied by the client (README, column dictionary, sample queries).
• Checksum, received timestamp, uploader, project id, declared source type (scada, device, weather, …).

Proposed blob layout (draft — align with DwdBlobPaths / dwd-core when implementing):

dwd-ingest/{projectId}/bronze/{sourceType}/{ingestBatchId}/{originalFileName}

Proposed table entity (draft):

Field	Role
PartitionKey	{projectId}
RowKey	{ingestBatchId}
SourceType	scada | device | …
ReceivedAt	UTC
ContentHash	SHA-256 of primary payload
BlobPrefix	path under dwd-ingest/...
ParserVersion	set when silver job starts
Status	received | parsing | silver_complete | failed

Open decision: dedicated container dwd-ingest vs prefix under an existing container. Prefer one ingest container so lifecycle policies (cool/archive) differ from dwd-input INP files.

Silver — parsed operational data

What belongs here

• Normalized relational data matching (or extending) current Postgres models:
  • SCADA: tags, time-series samples, asset linkage, data types.
  • Devices: devices, tags, datum rows, groups/types.
• Parse artifacts: rejected rows, type coercion errors, unmapped columns (blob JSON or table rows keyed by ingestBatchId).
• Units and types as in DB today (SCADA/Device APIs return native tag units — SCADA design).

Storage split (draft guidance)

Data shape	Prefer	Rationale
Metadata, xref, low-volume tags	PostgreSQL	Matches FastAPI + worker access today; joins to assets
High-volume time series (AMI, 1-min SCADA)	Parquet in blob plus slim index in Postgres or Table	Columnar cost; selective read paths; optional sync of aggregates to Postgres
Cross-batch lineage	Azure Table	Cheap catalog of batches, row counts, parser version

Proposed Postgres approach (minimal disruption):

• Phase A: keep single database per ScadaDatabaseName, add schema silver (or table suffix _silver) populated by new jobs; APIs still read legacy public tables until cutover.
• Phase B: rename / view so gold schema is default for ORM queries.

Silver is not “lightly cleaned”. Parsing, type coercion, key assignment, and deterministic transforms (e.g. period → ReadingDate) belong here. Anything that requires judgment (outlier removal) belongs in gold unless the rule is fixed and documented as part of parse (then document it as silver rule).

Gold — quality-assured production data

What belongs here

• Datasets that APIs, maps, heatmaps, cluster views, and SCADA calibration may treat as authoritative.
• Documented QC rules per source, e.g.:
  • Physical bounds (pressure, flow).
  • Rate-of-change limits.
  • Duplicate timestamp resolution.
  • Missing data policies (drop vs interpolate — product decision).
  • Cross-source consistency (meter sum vs zone demand) — likely later phase.

Is gold “just cleaning outliers”? Mostly, plus governance:

• Outlier and anomaly handling.
• Dedup and canonical keys (one tag id per logical sensor).
• Certification of a batch (goldBatchId, supersedes, effectiveFrom).
• Optional derived series (LPS from consumption, transformed SCADA) if those are product-facing — today some transforms live in legacy paths (TransformedData); gold is the place to consolidate “what the UI shows”.

Storage: Same as silver (Postgres + optional Parquet). Gold Parquet paths might mirror silver with .../gold/... partition keys for analytics exports.

Cutover criterion: When gold exists for a project source, FastAPI routes documented in dwd-fastapi-scada-api-design.md switch connection string / search_path / view from silver to gold; workers follow the same rule.

---

Azure Table vs Parquet vs PostgreSQL

Concern	Azure Table	Blob Parquet	PostgreSQL (ScadaDatabaseName)
Ingest batch catalog, run status	Primary	—	Optional mirror for joins
SCADA / device metadata	Secondary index only	—	Primary
Time-series serving (API charts)	Not for bulk series	Candidate at scale	Primary today
Time-series calibration (worker)	—	Possible if worker taught to read Parquet	Primary today (Dwd.Scada)
Reprocessing	Pointer to bronze blob	Rebuild from bronze	Truncate/reload per batch

Hypothesis to validate: Postgres remains gold serving store for interactive latency; Parquet becomes silver/gold archive and analytics for large AMI/SCADA history, with APIs reading recent windows from Postgres and deep history from Parquet — only if measured need appears.

---

Ingestion jobs (conceptual components)

Component	Responsibility
Landing API or CLI	Upload to bronze blob, create IngestBatch row, virus scan hook (TBD)
Parser registry	Map (sourceType, formatHint) → script/module version (devices v2, SCADA CSV profile X)
Silver loader	Stream bronze → validate → Postgres (+ optional Parquet writer)
Gold promoter	Run QC pipeline; write gold tables; mark batch superseded
Observability	Per-batch metrics: rows in/out, error rate, duration

Existing Python parsers in NetworkManager should become library modules invoked by a hosted job (worker poller, Azure Function, or manual CLI with same code path) rather than one-off laptop runs — without changing parse semantics on day one.

---

API and product impact

Surface	Today	Target
/projects/{id}/scada/...	Postgres (silver de facto)	Postgres gold (views)
/projects/{id}/devices/...	Same DB	Same
SCADA calibration worker	Direct Postgres	Direct Postgres gold
Admin / ops	Manual scripts	Ingest batch status in Table + optional admin UI

Non-goal (initial): Exposing bronze downloads to end users — restrict to admin / support roles if exposed at all.

---

Phased implementation (draft)

Phase	Outcome	Risk
0 — Document & inventory	Per-project list of sources, formats, current scripts, row counts	Low
1 — Bronze landing	Container + IngestBatch table; manual upload of new deliveries	Low
2 — Silver lineage	Every new load records ingestBatchId; parsers unchanged	Medium
3 — Gold QC v1	One source (e.g. devices) with explicit rules + API reads gold	Medium
4 — SCADA gold + calibration	Worker and FastAPI aligned on gold	High — calibration parity
5 — Parquet scale-out	Optional for largest series	Medium
6 — Deprecate silver reads	API/worker gold-only	Requires migration per project

---

Open decisions (brainstorm backlog)

1. Container name: dwd-ingest vs nested under dwd-scenarios / new dwd-telemetry.
2. Postgres modeling: separate schemas (bronze never in PG — only blob) vs silver / gold schemas vs database-per-layer (likely overkill).
3. Gold QC ownership: config per project in Azure Table vs Postgres config tables vs repo YAML checked into dwd-python-common.
4. Transformed SCADA (TransformedData, heatmap series): silver derived vs gold derived.
5. Retention: bronze forever vs N-year; Parquet compaction strategy.
6. PII / billing fields on device CSV — encryption at rest, column masking in gold.
7. Multi-file batches: one ingestBatchId for shapefile + CSV join sets (meters use case).
8. Alignment with SWMM: SWMM uses Azure Tables/Blobs heavily for scenario data; DWD telemetry stays Postgres-first — document intentional divergence unless convergence wins on ops cost.

---

Related documentation

Doc	Relevance
dwd-data-lifecycles.md	External feeds section — link here for medallion detail
dwd-per-project-scada-connection-design.md	Where silver/gold Postgres lives
dwd-fastapi-scada-api-design.md	Read path that must move to gold
dwd-csharp-retirement-and-scada-db-move.md	Postgres host migration — independent of layer logic
_azure_storage_upgrade_docs/dwd/dwd-azure-storage-worker-addendum.md	Simulation blob layout (do not conflate with telemetry bronze)

---

Glossary

Term	Meaning in this doc
Ingest batch	One client delivery attempt, one bronze folder, one lineage id
Source type	scada, device, weather, … — drives parser selection
Silver	Parsed, structurally valid data
Gold	QC-passed data approved for product consumption
Medallion	Bronze → silver → gold progression with immutable raw storage

---

Next doc pass: fill “Phase 0 inventory” table for each active DWD project (sources, last ingest, script version), and lock container/table names in dwd-core contracts.