Asset Hierarchy & Time Series in Delta

This lesson teaches you how to model your domain — parks, turbines, weather time series, power time series — as Delta tables. By the end, you'll be able to sketch table schemas, choose partitioning strategies, and explain how the same tables serve both your production pipeline and your analysts.

The Modelling Problem

You have three interrelated concerns that must coexist in Delta tables:

1. Asset hierarchy — a wind park contains turbines. You query at both levels.
2. Time series — every measurement (weather, telemetry, forecasts) is timestamped and repeating.
3. Dual access — production pipelines read "latest data for asset X" while analysts scan "all data for asset X over the last 90 days."

Getting the table layout right is your single highest-leverage architectural decision. A bad schema means rewriting pipelines later. A good one means your team barely notices the platform.

Pattern 1: The Asset Dimension Table

Separate what things are from what happened to them. The asset registry is a slowly-changing dimension — it describes parks and turbines but doesn't grow with every timestamp.^[1]

-- Silver layer: dim_assets
CREATE TABLE silver.dim_assets (
  asset_id        STRING NOT NULL,     -- e.g. "wp-nordsee-1"
  asset_type      STRING NOT NULL,     -- "wind_park" | "turbine"
  parent_asset_id STRING,              -- NULL for parks, park ID for turbines
  name            STRING,
  latitude        DOUBLE NOT NULL,
  longitude       DOUBLE NOT NULL,
  hub_height_m    DOUBLE,              -- turbine-specific
  capacity_kw     DOUBLE,
  commissioned    DATE,
  metadata        MAP<STRING, STRING>  -- flexible attrs
)
USING DELTA
TBLPROPERTIES ('delta.columnMapping.mode' = 'name');

Why not nested/struct types?

You could model turbines as an ARRAY<STRUCT> inside a park row. Don't. It makes time series joins painful, breaks per-turbine queries, and complicates the geo-spatial weather mapping. Keep it flat, join when you need hierarchy.

Pattern 2: Time Series Fact Tables

Time series are your fact tables — they grow with every measurement cycle. The design goal: fast writes from the pipeline, fast reads for both "give me the latest" and "give me a range."

Weather time series (mapped to assets)

-- Silver layer: weather time series mapped to asset locations
CREATE TABLE silver.weather_ts (
  asset_id          STRING NOT NULL,
  timestamp         TIMESTAMP NOT NULL,
  forecast_ref_time TIMESTAMP,          -- when the forecast was issued
  wind_speed_ms     DOUBLE,
  wind_direction_deg DOUBLE,
  temperature_c     DOUBLE,
  irradiance_wm2    DOUBLE,
  pressure_hpa      DOUBLE,
  humidity_pct      DOUBLE,
  source_model      STRING              -- "ecmwf_ifs", "gfs", "icon"
)
USING DELTA
PARTITIONED BY (asset_id)
TBLPROPERTIES (
  'delta.autoOptimize.optimizeWrite' = 'true',
  'delta.autoOptimize.autoCompact'   = 'true'
);

Power / telemetry time series

-- Silver layer: power output and telemetry per turbine
CREATE TABLE silver.power_ts (
  asset_id        STRING NOT NULL,      -- turbine-level
  timestamp       TIMESTAMP NOT NULL,
  power_kw        DOUBLE,
  rotor_speed_rpm DOUBLE,
  nacelle_temp_c  DOUBLE,
  availability    DOUBLE,               -- 0.0–1.0
  status_code     STRING
)
USING DELTA
PARTITIONED BY (asset_id)
TBLPROPERTIES (
  'delta.autoOptimize.optimizeWrite' = 'true',
  'delta.autoOptimize.autoCompact'   = 'true'
);

Partitioning Strategy — The Critical Trade-off

Partitioning determines which files Spark can skip when reading. Get it wrong and every query scans everything. The question for time series is: partition by asset, by date, or both?

What is Z-ORDER?

Z-ordering is Delta Lake's data-skipping optimisation. It physically reorders data within files by the specified column(s), so queries that filter on those columns can skip entire files.^[2]

-- Run periodically (or via auto-optimize) to keep data co-located
OPTIMIZE silver.weather_ts ZORDER BY (timestamp);
OPTIMIZE silver.power_ts   ZORDER BY (timestamp);

After Z-ordering, a query like WHERE asset_id = 'turbine-47' AND timestamp > '2026-07-10' skips the partition for other assets (partition pruning) and skips files with older timestamps within that partition (data skipping). Both access paths — pipeline and analyst — benefit.

Enabling Dual Access

The same Silver tables serve two audiences. Here's how each accesses them:

Practical implications for your team

The Geo-Spatial Join: Weather Grid → Asset

Weather models produce data on a grid (e.g., ECMWF at 0.25° ≈ 28 km resolution). Your assets are point locations. The Silver-layer transformation must map grid cells to nearby assets.

-- Simplified: nearest grid point to each asset
-- In practice, use bilinear interpolation for higher accuracy
SELECT
  a.asset_id,
  w.timestamp,
  w.wind_speed_ms,
  w.temperature_c,
  w.irradiance_wm2
FROM bronze.weather_raw w
JOIN silver.dim_assets a
  ON ABS(w.grid_lat - a.latitude)  < 0.125   -- half grid cell
  AND ABS(w.grid_lon - a.longitude) < 0.125
WHERE a.asset_type IN ('wind_park', 'turbine')

Turbine-level vs. park-level weather

For a compact wind park (turbines within ~2 km), all turbines may map to the same grid cell — so they share weather data. For large offshore clusters spanning multiple grid cells, different turbines get different weather. Your schema handles both: weather is mapped per asset_id, and the join determines granularity.

Putting It Together: Schema Map

Anti-Patterns to Avoid

Check Your Understanding

Primary Source

What's Next

You now know how to model assets and time series in Delta. The next lesson will cover ingestion pipelines — how weather data actually arrives (GRIB2 files, API pulls), gets parsed, and lands in Bronze. We'll look at scheduling, incremental loads, and the tooling (cfgrib, xarray) that makes weather data accessible to Spark.

Questions? Want to dive deeper into partitioning, Z-ordering, or the geo-spatial join? Ask your teacher — I can show you more detailed examples or discuss trade-offs specific to your fleet size.

Rechtliches: Impressum