Gold Layer & Serving Downstream

This lesson covers the final stage: structuring forecast output in the Gold layer, computing confidence intervals, aggregating from turbine to park level, and delivering predictions to downstream systems (trading desks, scheduling platforms, grid operators). By the end, you'll know how to design the Gold table and the integration patterns that serve it.

What Gold Must Deliver

Your downstream consumers don't care about Bronze, Silver, or features. They need one thing: a reliable forecast with a clear contract.

Gold Table Schema

CREATE TABLE prod.gold.forecast_ts (
  asset_id            STRING NOT NULL,
  asset_type          STRING NOT NULL,        -- "turbine" or "wind_park"
  timestamp           TIMESTAMP NOT NULL,     -- forecast valid time
  forecast_power_kw   DOUBLE NOT NULL,
  confidence_low_kw   DOUBLE,                 -- P10 (10th percentile)
  confidence_high_kw  DOUBLE,                 -- P90 (90th percentile)
  capacity_kw         DOUBLE,                 -- for capacity factor calc
  capacity_factor     DOUBLE,                 -- forecast_power / capacity
  forecast_ref_time   TIMESTAMP NOT NULL,     -- which weather model run
  model_version       STRING NOT NULL,        -- MLflow model URI
  produced_at         TIMESTAMP NOT NULL,     -- when this row was computed
  source_model        STRING                  -- weather model used
)
USING DELTA
PARTITIONED BY (asset_id)
TBLPROPERTIES (
  'delta.autoOptimize.optimizeWrite' = 'true',
  'delta.autoOptimize.autoCompact'   = 'true'
);

Turbine → Park Aggregation

The ML model predicts per turbine. Downstream systems often need park-level aggregates. Compute both and store in the same table:

from pyspark.sql import functions as F

def aggregate_to_park(turbine_forecasts, dim_assets):
    """
    Aggregate turbine-level forecasts to park level.
    Returns park-level rows to append alongside turbine rows in Gold.
    """
    # Join to get parent park
    with_park = turbine_forecasts.join(
        dim_assets.select("asset_id", "parent_asset_id", "capacity_kw"),
        on="asset_id",
        how="inner"
    ).filter(F.col("parent_asset_id").isNotNull())  # only turbines

    # Aggregate per park × timestamp
    park_forecast = with_park.groupBy(
        F.col("parent_asset_id").alias("asset_id"),
        "timestamp",
        "forecast_ref_time",
        "model_version",
        "produced_at",
        "source_model"
    ).agg(
        F.sum("forecast_power_kw").alias("forecast_power_kw"),
        F.sum("confidence_low_kw").alias("confidence_low_kw"),
        F.sum("confidence_high_kw").alias("confidence_high_kw"),
        F.sum("capacity_kw").alias("capacity_kw"),
    ).withColumn(
        "asset_type", F.lit("wind_park")
    ).withColumn(
        "capacity_factor", 
        F.col("forecast_power_kw") / F.col("capacity_kw")
    )

    return park_forecast

Why sum confidence intervals?

Summing P10/P90 across turbines is a simplification. Strictly, portfolio confidence intervals are narrower than the sum of individual intervals (diversification effect). For a first iteration, summing is conservative (overestimates uncertainty). For a refinement, model park-level uncertainty directly.

Confidence Intervals

Point forecasts aren't enough. Trading and scheduling need uncertainty quantification. Two approaches:

Option A: Quantile regression

# Train separate models for P10, P50, P90
# LightGBM supports this natively:
model_p10 = lgb.LGBMRegressor(objective="quantile", alpha=0.10, **params)
model_p50 = lgb.LGBMRegressor(objective="quantile", alpha=0.50, **params)
model_p90 = lgb.LGBMRegressor(objective="quantile", alpha=0.90, **params)

Option B: Prediction intervals from residuals

# Use historical prediction errors to estimate intervals
residuals = actuals - predictions  # from validation set
p10_offset = np.percentile(residuals, 10)
p90_offset = np.percentile(residuals, 90)
# At inference: confidence_low = prediction + p10_offset
#               confidence_high = prediction + p90_offset

Serving Patterns

How do downstream systems consume Gold data?

Forecast Quality Table

Gold should also include a quality/monitoring table that compares forecasts to actuals:

-- Materialise forecast accuracy as actuals arrive
CREATE OR REPLACE TABLE prod.gold.forecast_accuracy AS
SELECT
  f.asset_id,
  f.timestamp,
  f.forecast_power_kw,
  p.power_kw AS actual_power_kw,
  f.forecast_power_kw - p.power_kw AS error_kw,
  ABS(f.forecast_power_kw - p.power_kw) AS abs_error_kw,
  f.model_version,
  f.forecast_ref_time,
  f.source_model,
  -- Was actual within confidence band?
  CASE WHEN p.power_kw BETWEEN f.confidence_low_kw AND f.confidence_high_kw
       THEN TRUE ELSE FALSE END AS within_confidence
FROM prod.gold.forecast_ts f
JOIN prod.silver.power_ts p
  ON f.asset_id = p.asset_id AND f.timestamp = p.timestamp
WHERE f.asset_type = 'turbine'
  AND p.power_kw IS NOT NULL;

Check Your Understanding

Primary Source

What's Next

The pipeline is complete end-to-end. The next lesson covers Workflows & Orchestration — wiring all stages together into a production-grade workflow with dependencies, retries, alerting, and scheduling.

Questions about Delta Sharing setup, forecast accuracy KPIs, or how to handle negative forecasts (physically impossible)? Ask your teacher.

Rechtliches: Impressum