| Layer | Data | Partition | Key Property |
|---|---|---|---|
| Bronze | Raw GRIB2 → Parquet, SCADA telemetry, asset registry | init_time | Immutable, append-only |
| Silver | Weather TS per asset, power TS per turbine, dim_assets | asset_id + ZORDER(timestamp) | Dual access (pipeline + analyst) |
| Gold | Forecast per turbine + park, confidence intervals, accuracy | asset_id | Downstream contract |
asset_id STRING PK
asset_type STRING (wind_park|turbine)
parent_asset_id STRING FK (NULL for parks)
latitude DOUBLE
longitude DOUBLE
hub_height_m DOUBLE
capacity_kw DOUBLE
metadata MAP<STRING,STRING>asset_id STRING PK (partition)
timestamp TIMESTAMP PK
forecast_ref_time TIMESTAMP
wind_speed_ms DOUBLE
wind_speed_hub_ms DOUBLE
wind_direction_deg DOUBLE
temperature_c DOUBLE
irradiance_wm2 DOUBLE
pressure_hpa DOUBLE
source_model STRINGasset_id STRING PK (partition)
timestamp TIMESTAMP PK
power_kw DOUBLE
rotor_speed_rpm DOUBLE
nacelle_temp_c DOUBLE
availability DOUBLE
status_code STRINGasset_id STRING (partition)
asset_type STRING
timestamp TIMESTAMP
forecast_power_kw DOUBLE
confidence_low_kw DOUBLE
confidence_high_kw DOUBLE
capacity_factor DOUBLE
forecast_ref_time TIMESTAMP
model_version STRING
produced_at TIMESTAMP
source_model STRINGWeather API (ECMWF/GFS/ICON)
→ [Download GRIB2] → bronze.weather_raw (PARTITION BY init_time)
→ [Geo-join + derive] → silver.weather_ts (PARTITION BY asset_id, ZORDER timestamp)
→ [Compute features] → features.wind_power_features (PK: asset_id, timestamp)
→ [ML inference @champion] → gold.forecast_ts
→ [Serve] → Trading (JDBC) / Grid (Delta Sharing) / Dashboards (SQL Warehouse)| Variable | Formula |
|---|---|
| Wind speed | $\sqrt{u^2 + v^2}$ |
| Wind direction | $\text{atan2}(-u, -v) \times \frac{180}{\pi} \mod 360$ |
| Hub-height wind | $v_{10} \times (h_{hub} / 10)^{\alpha}$ (α ≈ 0.14 open, 0.10 offshore) |
| Temperature | $T_C = T_K - 273.15$ |
| Irradiance (inst.) | $(ssrd[t] - ssrd[t{-}1]) / \Delta t_{sec}$ |
Metastore
├── prod (machine-written only)
│ ├── bronze → weather_raw
│ ├── silver → weather_ts, power_ts, dim_assets
│ ├── gold → forecast_ts, forecast_accuracy
│ └── features → wind_power_features
├── dev (engineers develop here)
│ ├── bronze / silver / gold (mirrors prod)
└── sandbox (analysts + scientists explore)
└── <user_schema>| Principal | prod | dev | sandbox |
|---|---|---|---|
| Pipeline SP | ALL (bronze/silver/gold) | — | — |
| Data engineers | SELECT (all) | ALL | ALL |
| Data scientists | SELECT (silver/gold) | ALL | ALL |
| Analysts | SELECT (silver/gold) | — | ALL |
| Downstream systems | SELECT (gold only) | — | — |
| Task | Type | Workers | VM |
|---|---|---|---|
| GRIB ingestion | Job cluster | 0 (single-node) | Memory-opt (E4s) |
| Silver transforms | Job cluster | 2–4 | General (D4s) |
| ML inference | Job cluster | 1 | Memory-opt (E8s), ML runtime |
| Analyst queries | SQL Warehouse | Serverless | Auto (scales to zero) |
| Task | Frequency | Command |
|---|---|---|
| OPTIMIZE + ZORDER | Weekly | OPTIMIZE table ZORDER BY (timestamp) |
| VACUUM | Weekly | VACUUM table RETAIN N DAYS |
| Bronze retention | Monthly | Drop partitions > 90 days |
| Model retraining | Monthly (or on drift) | Retrain → validate → promote |
| Decision | Recommendation | Lesson |
|---|---|---|
| DLT vs custom Spark for Bronze | Custom (GRIB2 needs cfgrib) | 1 |
| Asset hierarchy model | Flat table + parent pointer | 2 |
| Partition strategy for TS | BY asset_id, ZORDER timestamp | 2 |
| Geo-join approach | Nearest-neighbour (snap to grid) | 4 |
| Forecast versioning | Store all, serve latest (view) | 4 |
| Catalog structure | Environment-based (prod/dev/sandbox) | 5 |
| Global vs per-asset model | Global + asset features (start) | 7 |
| Confidence intervals | Quantile regression (LightGBM) | 8 |
| IaC tooling | Databricks Asset Bundles | 9 |
Rechtliches: Impressum