Power Consumption Model¶

-- int_esg_node_client_distribution.sql
-- Computes weighted client efficiency per node category

WITH client_counts AS (
    SELECT
        nc.node_category,
        cl.consensus_client,
        cl.execution_client,
        count(*) AS node_count
    FROM {{ ref('int_esg_node_classification') }} nc
    LEFT JOIN {{ ref('stg_node_clients') }} cl
        ON nc.peer_id = cl.peer_id
    GROUP BY nc.node_category, cl.consensus_client, cl.execution_client
),

efficiency_mapped AS (
    SELECT
        cc.*,
        -- Consensus client efficiency
        CASE cc.consensus_client
            WHEN 'lighthouse' THEN 0.95
            WHEN 'nimbus'     THEN 0.85
            WHEN 'teku'       THEN 1.15
            WHEN 'prysm'      THEN 1.05
            WHEN 'lodestar'   THEN 1.10
            ELSE 1.00
        END AS cl_efficiency,
        -- Execution client efficiency
        CASE cc.execution_client
            WHEN 'erigon'     THEN 0.95
            WHEN 'nethermind' THEN 1.00
            WHEN 'besu'       THEN 1.02
            WHEN 'geth'       THEN 0.98
            ELSE 1.00
        END AS el_efficiency
    FROM client_counts cc
)

SELECT
    node_category,
    sum(node_count) AS total_nodes,
    -- Weighted average of combined CL+EL efficiency
    sum(node_count * (cl_efficiency + el_efficiency) / 2.0)
        / sum(node_count) AS weighted_client_efficiency,
    -- Client diversity: count distinct clients
    count(DISTINCT consensus_client)
        + count(DISTINCT execution_client) AS distinct_client_count,
    -- Diversity bonus
    0.95 + 0.05 * least(4,
        count(DISTINCT consensus_client) + count(DISTINCT execution_client)
    ) / 4.0 AS diversity_bonus
FROM efficiency_mapped
GROUP BY node_category

-- int_esg_dynamic_power_consumption.sql
-- Daily power consumption with Monte Carlo confidence intervals

WITH base_params AS (
    SELECT
        cd.node_category,
        cd.total_nodes,
        cd.weighted_client_efficiency,
        cd.diversity_bonus,
        -- Hardware power profiles (CCRI 2022)
        CASE cd.node_category
            WHEN 'Home Staker'  THEN 22.0
            WHEN 'Professional' THEN 48.0
            WHEN 'Cloud Hosted' THEN 155.0
        END AS mean_power_w,
        CASE cd.node_category
            WHEN 'Home Staker'  THEN 3.3
            WHEN 'Professional' THEN 7.2
            WHEN 'Cloud Hosted' THEN 23.0
        END AS stddev_power_w,
        -- PUE factors
        CASE cd.node_category
            WHEN 'Home Staker'  THEN 1.00
            WHEN 'Professional' THEN 1.58
            WHEN 'Cloud Hosted' THEN 1.15
        END AS pue
    FROM {{ ref('int_esg_node_client_distribution') }} cd
),

-- Monte Carlo: 1,000 scenarios
scenarios AS (
    {{ generate_monte_carlo_samples(1000) }}
),

simulated AS (
    SELECT
        s.scenario_id,
        bp.node_category,
        bp.total_nodes
            * {{ bounded_normal_sample('bp.mean_power_w', 'bp.stddev_power_w', '5.0', '300.0') }}
            * 24
            * bp.weighted_client_efficiency
            * bp.diversity_bonus
            * bp.pue
            / 1000.0 AS daily_kwh
    FROM scenarios s
    CROSS JOIN base_params bp
)

SELECT
    node_category,
    -- Point estimate
    avg(daily_kwh) AS mean_daily_kwh,
    -- Uncertainty bounds
    quantile(0.025)(daily_kwh) AS ci_lower_2_5,
    quantile(0.975)(daily_kwh) AS ci_upper_97_5,
    stddevPop(daily_kwh) AS stddev_daily_kwh
FROM simulated
GROUP BY node_category

-- fct_node_energy.sql
-- Final daily energy consumption fact table

SELECT
    toDate(now()) AS report_date,
    dpc.node_category,
    nc.total_nodes,
    dpc.mean_daily_kwh,
    dpc.ci_lower_2_5,
    dpc.ci_upper_97_5,
    dpc.stddev_daily_kwh,
    -- Annualized estimates
    dpc.mean_daily_kwh * 365.25 AS annual_kwh,
    -- CO2 equivalent (using EU grid average: 0.233 kg CO2/kWh)
    dpc.mean_daily_kwh * 0.233 AS daily_co2_kg,
    dpc.mean_daily_kwh * 365.25 * 0.233 AS annual_co2_kg
FROM {{ ref('int_esg_dynamic_power_consumption') }} dpc
LEFT JOIN {{ ref('int_esg_node_client_distribution') }} nc
    ON dpc.node_category = nc.node_category