Renewable Energy Generation & PPA Analytics data model
Kimball warehouse for renewables generation, PPA settlement, and curtailment. Three facts: a 15-minute SCADA telemetry fact aligned to the ISO settlement interval, a PPA settlement fact at delivery-period × node grain with REC tracking, and a curtailment-event fact (with two date roles for start/end) carrying compensable loss measures. Conformed SCD2 site dimension preserves as-was installed capacity through repowering; weather-station dimension supports performance-ratio analysis vs irradiance and wind.
Renewable energy generation analytics for solar PV, onshore/offshore wind, battery storage, and hybrid sites. SCADA delivers 15-minute generation telemetry (energy, AC/DC power, capacity factor, availability, performance ratio) correlated to irradiance and wind weather; PPA settlement and REC inventory roll up to contract / counterparty / pricing-node grain; curtailment events are captured with compensable/non-compensable classification across ISOs (CAISO / ERCOT / PJM / MISO / NYISO) for NERC-CIP and GHG-Protocol reporting. It is a Kimball dimensional model: fact tables at a declared grain surrounded by conformed dimensions, ready for BI tools and a semantic layer.
- 14
- tables
- 132
- columns
- 16
- relationships
- 0
- PII columns
Opening the template creates a project with the ERD, platform-native DDL, a dbt project and documentation already generated (recommended platform: Snowflake). Edit it in plain English or on the canvas, then ship it to any supported warehouse.
Subject areas
Design decisions
15-minute telemetry-grain fact aligned to ISO settlement interval
ISO markets settle on 15-minute (or sub-hourly) intervals; landing SCADA at that grain makes the generation fact directly joinable to PPA settlement and curtailment without re-bucketing, and preserves intra-hour capacity factor and ramp behaviour.
PPA settlement fact at delivery-period grain with REC tracking
Power Purchase Agreements settle per delivery period × node and frequently bundle Renewable Energy Certificates; modelling the settlement at that grain with separate REC quantity / value columns supports contract performance, shortfall damages, and REC-inventory reporting from one fact.
Curtailment-event fact for compensable losses
Curtailment is event-shaped (start, end, reason, ISO) rather than periodic; a dedicated event-grain fact with compensable / non-compensable classification by reason category enables lost-revenue and ISO-recovery reporting that a periodic fact would obscure.
SCD2 site to preserve as-was capacity through repowering
Wind and solar sites are repowered (turbines replaced, modules augmented, BESS added) over their 20-30 year life; type-2 history on installed capacity, commissioning date, and technology type keeps historical capacity factors comparable to the configuration that produced them.
Weather-station dimension to correlate generation to irradiance / wind
Performance ratio and P50/P90 require pairing generation with the resource (irradiance for PV, hub-height wind for turbines); modelling weather stations as a conformed dimension on the telemetry fact supports onsite-vs-satellite comparisons without fact-table bloat.
Tables in the Renewable Energy Generation & PPA Analytics model
Every table with its grain, columns, types and keys. PK marks a primary key, BK a business key and PII a column classified as personal data, which gets a masking strategy by default.
Fact tables (3)
fct_generation_interval
Grain: one row per generation asset per 15-minute interval (SCADA telemetry grain)
SCADA 15-minute telemetry fact aligned to ISO settlement period.
| Column | Type | Keys |
|---|---|---|
| generation_interval_key | bigint | PK |
| interval_start_key | bigint | |
| date_key | bigint | |
| site_key | bigint | |
| asset_key | bigint | |
| weather_station_key | bigint | |
| inverter_or_turbine_key | bigint | |
| energy_generated_kwh | decimal | |
| ac_power_kw | decimal | |
| dc_power_kw | decimal | |
| capacity_factor_pct | decimal | |
| availability_pct | decimal | |
| performance_ratio_pct | decimal | |
| irradiance_w_per_m2 | decimal | |
| wind_speed_ms | decimal | |
| ambient_temperature_c | decimal | |
| panel_or_blade_temperature_c | decimal | |
| downtime_minutes | decimal |
fct_ppa_settlement
Grain: one row per PPA × delivery period × pricing node (energy + REC settlement)
Delivery-period PPA settlement fact with REC tracking.
| Column | Type | Keys |
|---|---|---|
| ppa_settlement_key | bigint | PK |
| delivery_date_key | bigint | |
| ppa_contract_key | bigint | |
| counterparty_key | bigint | |
| site_key | bigint | |
| pricing_node_key | bigint | |
| contracted_volume_mwh | decimal | |
| delivered_volume_mwh | decimal | |
| settled_price_per_mwh | decimal | |
| settled_revenue | decimal | |
| shortfall_volume_mwh | decimal | |
| liquidated_damages | decimal | |
| rec_quantity | decimal | |
| rec_value | decimal | |
| curtailment_loss_mwh | decimal | |
| currency | varchar |
fct_curtailment_event
Grain: one row per curtailment event at a site
Event-grain curtailment fact for compensable and non-compensable losses.
| Column | Type | Keys |
|---|---|---|
| curtailment_event_key | bigint | PK |
| event_start_date_key | bigint | |
| event_end_date_key | bigint | |
| site_key | bigint | |
| curtailment_reason_key | bigint | |
| iso_market_key | bigint | |
| event_id | varchar | |
| duration_minutes | decimal | |
| energy_lost_mwh | decimal | |
| revenue_lost | decimal | |
| is_compensated | smallint | |
| currency | varchar | |
| curtailment_type | varchar |
Dimension tables (11)
dim_date
Grain: one row per calendar day
| Column | Type | Keys |
|---|---|---|
| date_key | bigint | PK |
| full_date | date | BK |
| day_of_month | smallint | |
| month_number | smallint | |
| month_name | varchar | |
| quarter | smallint | |
| year | smallint | |
| season | varchar | |
| is_holiday | boolean |
dim_interval_15min
Grain: one row per 15-minute settlement interval (aligned to ISO settlement period)
| Column | Type | Keys |
|---|---|---|
| interval_key | bigint | PK |
| interval_datetime | timestamp | BK |
| hour_of_day | smallint | |
| day_part | varchar | |
| settlement_period | smallint |
dim_site
Grain: one row per generation site per SCD2 version (solar farm / wind farm / BESS / hybrid)
SCD type 2
| Column | Type | Keys |
|---|---|---|
| site_key | bigint | PK |
| site_id | varchar | BK |
| site_name | varchar | |
| technology_type | varchar | |
| installed_capacity_mw | decimal | |
| commissioning_date | date | |
| country | varchar | |
| region | varchar | |
| iso_balancing_authority | varchar | |
| latitude | decimal | |
| longitude | decimal | |
| is_active | boolean |
dim_asset
Grain: one row per individual asset (module/inverter/turbine/transformer/battery-module)
| Column | Type | Keys |
|---|---|---|
| asset_key | bigint | PK |
| asset_id | varchar | BK |
| asset_name | varchar | |
| asset_type | varchar | |
| manufacturer | varchar | |
| model | varchar | |
| nameplate_capacity_kw | decimal | |
| installation_date | date | |
| warranty_end_date | date | |
| is_active | boolean |
dim_inverter_or_turbine
Grain: one row per power-conversion unit (string/central inverter, wind turbine, BESS block)
| Column | Type | Keys |
|---|---|---|
| inverter_or_turbine_key | bigint | PK |
| unit_id | varchar | BK |
| unit_label | varchar | |
| unit_type | varchar | |
| manufacturer | varchar | |
| model | varchar | |
| capacity_kw | decimal | |
| site_id | varchar | |
| is_active | boolean |
dim_weather_station
Grain: one row per weather station providing irradiance / wind / temperature telemetry
| Column | Type | Keys |
|---|---|---|
| weather_station_key | bigint | PK |
| station_id | varchar | BK |
| station_name | varchar | |
| latitude | decimal | |
| longitude | decimal | |
| sensor_set | varchar | |
| is_onsite | boolean |
dim_ppa_contract
Grain: one row per Power Purchase Agreement contract
| Column | Type | Keys |
|---|---|---|
| ppa_contract_key | bigint | PK |
| ppa_id | varchar | BK |
| contract_name | varchar | |
| contract_type | varchar | |
| term_start | date | |
| term_end | date | |
| contracted_capacity_mw | decimal | |
| contracted_volume_mwh | decimal | |
| price_basis | varchar | |
| includes_recs | boolean |
dim_counterparty
Grain: one row per PPA counterparty (offtaker)
| Column | Type | Keys |
|---|---|---|
| counterparty_key | bigint | PK |
| counterparty_id | varchar | BK |
| counterparty_name | varchar | |
| country | varchar | |
| counterparty_type | varchar | |
| credit_rating | varchar |
dim_pricing_node
Grain: one row per ISO pricing node (LMP node or zone)
| Column | Type | Keys |
|---|---|---|
| pricing_node_key | bigint | PK |
| node_id | varchar | BK |
| node_name | varchar | |
| iso | varchar | |
| node_type | varchar | |
| latitude | decimal | |
| longitude | decimal |
dim_iso_market
Grain: one row per ISO market (CAISO / ERCOT / PJM / MISO / NYISO across day-ahead/real-time/capacity)
| Column | Type | Keys |
|---|---|---|
| iso_market_key | bigint | PK |
| iso_market | varchar | BK |
| country | varchar | |
| market_type | varchar | |
| time_zone | varchar |
dim_curtailment_reason
Grain: one row per curtailment reason code
| Column | Type | Keys |
|---|---|---|
| curtailment_reason_key | bigint | PK |
| curtailment_reason | varchar | BK |
| description | varchar | |
| reason_category | varchar | |
| is_compensable | boolean |
Relationships
Each relationship is a foreign key on the first table referencing the second.
- fct_generation_interval.interval_start_key → dim_interval_15min (many-to-one)
- fct_generation_interval.date_key → dim_date (many-to-one)
- fct_generation_interval.site_key → dim_site (many-to-one)
- fct_generation_interval.asset_key → dim_asset (many-to-one)
- fct_generation_interval.weather_station_key → dim_weather_station (many-to-one)
- fct_generation_interval.inverter_or_turbine_key → dim_inverter_or_turbine (many-to-one)
- fct_ppa_settlement.delivery_date_key → dim_date (many-to-one)
- fct_ppa_settlement.ppa_contract_key → dim_ppa_contract (many-to-one)
- fct_ppa_settlement.counterparty_key → dim_counterparty (many-to-one)
- fct_ppa_settlement.site_key → dim_site (many-to-one)
- fct_ppa_settlement.pricing_node_key → dim_pricing_node (many-to-one)
- fct_curtailment_event.event_start_date_key → dim_date (many-to-one)
- fct_curtailment_event.event_end_date_key → dim_date (many-to-one)
- fct_curtailment_event.site_key → dim_site (many-to-one)
- fct_curtailment_event.curtailment_reason_key → dim_curtailment_reason (many-to-one)
- fct_curtailment_event.iso_market_key → dim_iso_market (many-to-one)
Related templates
All templates →From requirements to AI-ready context, in one platform.
Describe your business in a conversation, start from a template or connect your own database. Your first model is ready in minutes.