---
title: "Continuous Aggregate Refresh Policies for Solar and Wind SCADA Data: Choosing the Right Policy Shape"
published: 2026-10-07T08:14:21.000-04:00
updated: 2026-10-07T08:14:21.000-04:00
excerpt: "Five refresh policy shapes for solar and wind SCADA: frequent, daily, hierarchical, retention-aware, and manual. Pick the one that fits each job."
tags: IoT, TimescaleDB
authors: Damaso Sanoja
---

> **TimescaleDB is now Tiger Data.**

A few dozen solar plants and wind farms push supervisory control and data acquisition (SCADA) readings into a [TimescaleDB](https://www.tigerdata.com/docs) hypertable on Postgres. An hourly continuous aggregate serves the dashboards and the settlement exports, and the continuous aggregate refresh policy behind it was configured on the day the system went live. Nobody has touched it since. It has three numbers in it: `start_offset`, `end_offset`, and `schedule_interval`.

By the end of this guide, you'll be able to read those numbers off your own system, check them against the one rule the engine enforces, and pick the policy shape that fits each job your aggregates do. There are five shapes, each an answer to something a generation fleet actually does: a control room that needs the last hour, sites that flush days of buffered readings, rollups stacked on rollups, a retention policy dropping raw data, and a historian export that has to be loaded. The shapes combine, and most fleets run two or three at once. The guide assumes you know how refresh works and already have a continuous aggregate in production. The examples assume TimescaleDB 2.29.0 or later, and Shape 3 also uses the TimescaleDB Toolkit extension.

Everything here is do-it-yourself. If you'd rather tune against your own workload with someone who does this daily, that's what TigerData's [support plans](https://www.tigerdata.com/support) are for.

## Read your three policy numbers

Together, the three settings define a schedule and a moving window. The policy wakes up every `schedule_interval` and refreshes the window that runs from `now() - start_offset` to `now() - end_offset`. `start_offset` sets how far back the policy looks for changed data, and `end_offset` keeps the window clear of the bucket that is still filling. The mechanism underneath (the invalidation log, the materialization watermark, and what happens when late-arriving data lands) belongs to [Continuous Aggregate Refresh, Demystified](https://www.tigerdata.com/blog/continuous-aggregate-refresh-demystified).

Read your own numbers from the [jobs view](https://www.tigerdata.com/docs/reference/timescaledb/informational-views/jobs):

```SQL
SELECT ca.view_name,
       j.config ->> 'start_offset' AS start_offset,
       j.config ->> 'end_offset'   AS end_offset,
       j.schedule_interval
FROM timescaledb_information.jobs j
JOIN timescaledb_information.continuous_aggregates ca
  ON ca.materialization_hypertable_schema = j.hypertable_schema
 AND ca.materialization_hypertable_name   = j.hypertable_name
WHERE j.proc_name = 'policy_refresh_continuous_aggregate'
ORDER BY ca.view_name;
```

A typical go-live policy on an hourly aggregate over `site_readings` looks like this:

```SQL
SELECT add_continuous_aggregate_policy('generation_hourly',
    start_offset      => INTERVAL '3 days',
    end_offset        => INTERVAL '2 hours',
    schedule_interval => INTERVAL '1 hour');
```

Check every row the query returns against one rule: the window has to span at least two buckets, so `start_offset` must be at least `end_offset` plus two bucket widths, and `end_offset` must never be `NULL`. TimescaleDB enforces the first half, but not the second. Ask for a narrower window, and `add_continuous_aggregate_policy` refuses with policy refresh window too small, because both edges of the window snap to whole buckets and a job [rarely fires exactly on a bucket boundary](https://github.com/timescale/timescaledb/blob/main/tsl/src/bgw_policy/continuous_aggregate_api.c). A `NULL end_offset` is accepted, and the [argument reference](https://www.tigerdata.com/docs/reference/timescaledb/continuous-aggregates/add_continuous_aggregate_policy) calls it possible but not recommended, so that half of the check is yours.

Past that floor, be generous with`start_offset`. Refresh work tracks the buckets your writes invalidated, so in steady state a wide window is close to free: one late row dirties its own bucket, and the policy recomputes that bucket and nothing else. Width costs something in two cases. A retention drop inside the window is a change like any other, so the policy recomputes that range (Shape 4 deals with it), and the first run after you create or widen a policy materializes the whole window once.

The go-live policy is a sound default. Each shape below is what you reach for when one job the aggregate serves pulls against it.

## Shape 1: Frequent refresh for recent data

The control room tracks generation against forecast, and when the grid operator issues a curtailment instruction, the effect has to show up in the hourly numbers right away. [Real-time aggregation](https://www.tigerdata.com/docs/learn/continuous-aggregates/real-time-aggregates) covers that: a query against `generation_hourly` combines the materialized buckets with the raw readings newer than them, so the latest hour is always current. It is off by default for continuous aggregates created on TimescaleDB 2.13 or later, so turn it on where you want it:

```SQL
ALTER MATERIALIZED VIEW generation_hourly SET (timescaledb.materialized_only = false);
```

Real-time aggregation answers from raw data at query time; the policy decides how soon those rows become precomputed buckets. That still matters. Anything that reads materialized results only, like the settlement export, waits for the publication delay, roughly `schedule_interval + end_offset`, the figure _Demystified_ recommends handing to downstream consumers. And every hour the policy hasn't materialized yet is aggregated again on each dashboard query. The go-live policy publishes a bucket about three hours after it closes.

A tight window on a frequent schedule brings that down. A continuous aggregate only takes a second policy when the windows don't overlap (Shape 2 covers that), so replace the go-live policy:

```SQL
SELECT remove_continuous_aggregate_policy('generation_hourly');

-- Tight and frequent.
SELECT add_continuous_aggregate_policy('generation_hourly',
    start_offset      => INTERVAL '12 hours',
    end_offset        => INTERVAL '1 hour',
    schedule_interval => INTERVAL '15 minutes');
```

![Continuous Aggregate Refresh Policies for Solar and Wind SCADA Data - Shape 1](https://assets.tigerdata.com/blog/2026/10/one-run-shape-1.svg)

The 06:15 run publishes everything up to 05:00. The bucket that closed at 06:00 sits inside the `end_offset` until the 07:00 run picks it up, so the publication delay is now an hour, an hour and a quarter at most. Twelve hours of lookback still covers a site that drops off for a shift. A site that comes back after three days is beyond it, and that's Shape 2.

## Shape 2: Daily refresh for late data

The usual reason generation data falls far behind its own timestamp is the site link. A remote met mast or a rural inverter cluster loses connectivity; the site controller or remote terminal unit (RTU) goes into store-and-forward, and on reconnect it flushes hours or days of readings in one burst, each carrying its original timestamp. Those rows land in buckets that were materialized days ago.

These rows matter as long as they can still change an invoice, so the lookback is set by your market's resettlement window. ERCOT issues its [true-up statement](https://www.ercot.com/files/docs/2020/05/12/2019_09_Set301_M7_-_St_Inv.pdf) 180 days after the operating day, and Great Britain's [final reconciliation run](https://www.elexon.co.uk/bsc/glossary/1st-reconciliation/) comes at 14 months. Past that horizon a correction is an accounting matter, and the aggregate no longer needs to move. None of this needs to be fresh. It needs to be complete, and a sweep that size belongs off-peak, once a day:

```SQL
-- Wide and daily. Its end_offset is Shape 1's start_offset.
SELECT add_continuous_aggregate_policy('generation_hourly',
    start_offset      => INTERVAL '90 days',
    end_offset        => INTERVAL '12 hours',
    schedule_interval => INTERVAL '1 day');
```

The ninety days is a placeholder for the number your contracts already give you. On its own, this shape suits an aggregate that only feeds daily availability reports and the settlement export: a bucket appears within a day and a half of closing, and everything inside the resettlement window stays correct. If it replaces the go-live policy instead of joining Shape 1's, remove the old policy first, as in Shape 1.

![Continuous Aggregate Refresh Policies for Solar and Wind SCADA Data - Shape 2](https://assets.tigerdata.com/blog/2026/10/generation_hourly.svg)

### Combining shapes 1 and 2

Shape 2's `end_offset` is Shape 1's `start_offset` on purpose. A [refresh policy](https://www.tigerdata.com/docs/build/continuous-aggregates/refresh-policies) supports concurrent policies on one continuous aggregate as long as their windows do not overlap, and the documentation gives the pattern as one policy for recent data plus another for backfilled data in older chunks. Here the second policy runs on a schedule for a recurring late tail, the same mechanism applied to a different cause. The two windows meet at twelve hours and never cross.

One policy with a ninety-day `start_offset` would catch the same backlog. Splitting it in two buys each job its own schedule. The recent end refreshes every fifteen minutes, the deep sweep runs once a day, and when a site flushes five days of readings, that rewrite happens in the daily job instead of inside the one keeping the last hour fresh. In the diagram, the flushed readings carry timestamps beyond the tight band, and the next daily run picks them up.

## Shape 3: One policy per level for hierarchical continuous aggregates

A generation fleet keeps four granularities. Raw telemetry arrives every one to sixty seconds. Above it sits the ten-minute statistical record. In wind, it comes from power performance testing and the contracts built on it: [IEC 61400-12-1](https://webstore.iec.ch/en/publication/68499) builds the power curve from ten-minute averages, availability guarantees are counted in ten-minute periods, and turbine SCADA systems [store each signal's ten-minute mean, minimum, maximum, and standard deviation](https://wes.copernicus.org/articles/8/893/2023/) as a matter of course. Solar sites vary in polling interval and report revenue metering on the system operator's settlement interval, so a solar stack often puts its base rollup somewhere other than ten minutes. Above that come hourly for dashboards and daily for availability reporting.

[Hierarchical continuous aggregates](https://www.tigerdata.com/docs/learn/continuous-aggregates/hierarchical-continuous-aggregates) build each level on the one below, and each level is itself a hypertable with its own columnstore and retention policies. The aggregate function is where a stack goes wrong. You cannot sum a mean, and an average of averages across buckets with unequal populations is arithmetically wrong. [`stats_agg and rollup`](https://www.tigerdata.com/docs/build/data-management/hyperfunctions/stats-aggs) from the Toolkit build a composable summary at the base and combine children into a parent correctly. Here the hourly aggregate is rebuilt on top of the ten-minute record:

```SQL
CREATE MATERIALIZED VIEW generation_10min
WITH (timescaledb.continuous) AS
SELECT site_id,
       time_bucket(INTERVAL '10 minutes', ts) AS bucket,
       stats_agg(power_kw)                    AS power_stats,
       sum(energy_kwh)                        AS energy_kwh
FROM site_readings
GROUP BY site_id, time_bucket(INTERVAL '10 minutes', ts)
WITH NO DATA;

CREATE MATERIALIZED VIEW generation_hourly
WITH (timescaledb.continuous) AS
SELECT site_id,
       time_bucket(INTERVAL '1 hour', bucket) AS bucket,
       rollup(power_stats)                    AS power_stats,
       sum(energy_kwh)                        AS energy_kwh
FROM generation_10min
GROUP BY site_id, time_bucket(INTERVAL '1 hour', bucket)
WITH NO DATA;

CREATE MATERIALIZED VIEW generation_daily
WITH (timescaledb.continuous) AS
SELECT site_id,
       time_bucket(INTERVAL '1 day', bucket) AS bucket,
       rollup(power_stats)                   AS power_stats,
       sum(energy_kwh)                       AS energy_kwh
FROM generation_hourly
GROUP BY site_id, time_bucket(INTERVAL '1 day', bucket)
WITH NO DATA;
```

Reads go through the accessors, so `average(power_stats)` and `stddev(power_stats)` are correct at every level. Energy stays a plain sum because `energy_kwh` here is energy per reading interval, which composes by addition; a cumulative meter register would need a different treatment.

Each level gets its own refresh policy, and the offsets step upward. Give every parent an `end_offset` that clears the publication delay of the level below it, so a parent only materializes buckets its child has finished:

```SQL
SELECT add_continuous_aggregate_policy('generation_10min',
    start_offset      => INTERVAL '3 days',
    end_offset        => INTERVAL '20 minutes',
    schedule_interval => INTERVAL '10 minutes');

SELECT add_continuous_aggregate_policy('generation_hourly',
    start_offset      => INTERVAL '3 days',
    end_offset        => INTERVAL '2 hours',
    schedule_interval => INTERVAL '1 hour');

SELECT add_continuous_aggregate_policy('generation_daily',
    start_offset      => INTERVAL '7 days',
    end_offset        => INTERVAL '1 day',
    schedule_interval => INTERVAL '1 day');
```

![Continuous Aggregate Refresh Policies for Solar and Wind SCADA Data - Shape 3](https://assets.tigerdata.com/blog/2026/10/stacked-policies-1.svg)

Read bottom-up, each window ends behind the one below it: the ten-minute level publishes to twenty minutes ago, the hourly level to two hours ago, and the daily level to the last complete day. No parent ever materializes a bucket its child hasn't finished.

## Shape 4: Keep the aggregate, drop the raw data

Contracts set how long renewable generation data has to exist, and the aggregates are what they need. Settlement and resettlement set one horizon, warranty availability disputes with the turbine or inverter manufacturer set a second ([the claim is calculated from SCADA records](https://www.ourenergypolicy.org/wp-content/uploads/2017/08/Definitions-of-availability-terms-for-the-wind-industry-white-paper-09-08-2017.pdf)), and power curve verification sets a third, often [limited to a year or so after commissioning](https://www.windguard.com/publications-wind-energy-statistics.html?file=files/cto_layout/img/unternehmen/veroeffentlichungen/2013/Critical+Limitations+of+Wind+Turbine+Power+Curve+Warranties.pdf). Aggregated history is usually held for the contract term, and a power purchase agreement [typically runs around 20 years](https://www.stoel.com/insights/reports/the-law-of-wind/power-purchase-agreements-and-environmental-attrib). Nothing asks that of the raw stream: [standard practice](https://strathprints.strath.ac.uk/64817/1/Gonzalez_etal_RE_2018_Using_high_frequency_SCADA_data_for_wind_turbine_performance.pdf) is to store the ten-minute averages, so how many months of raw telemetry you keep is your call. On Tiger Cloud you can [tier it to low-cost object storage](https://www.tigerdata.com/docs/learn/data-lifecycle/storage/about-storage-tiers) before dropping it; a policy window only reads tiered chunks when its `include_tiered_data` argument says so.

The refresh window and the retention interval are one decision with two knobs. The refresh policy documentation warns that when the window covers data the retention policy has removed, the next refresh of those buckets takes the data out of the aggregate too, and the drop-data guide notes that the aggregate [can end up holding NULLs](https://www.tigerdata.com/docs/build/continuous-aggregates/drop-data) in its place. The control is a single inequality: keep the retention interval longer than the deepest `start_offset` on any aggregate built on the hypertable. With Shape 2 in place, that is ninety days:

```SQL
SELECT add_retention_policy('site_readings', drop_after => INTERVAL '6 months');
```

![Continuous Aggregate Refresh Policies for Solar and Wind SCADA Data - Shape 4](https://assets.tigerdata.com/blog/2026/10/refresh-windows.png)

The aggregate now keeps its history after the raw rows are gone. The deliberate opposite is an open-ended `start_offset`, which the docs present as a choice:

```SQL
-- The aggregate follows removals from the raw hypertable.
SELECT add_continuous_aggregate_policy('generation_hourly',
    start_offset      => NULL,
    end_offset        => INTERVAL '2 hours',
    schedule_interval => INTERVAL '1 hour');
```

What picks between them is whether the aggregate is a durable record or a derived view. Settlement exports, warranty evidence, and power curve submissions are records, so they take the bounded form. A curtailment analysis that should only ever show periods the raw data still covers is a view, so it takes the open-ended one. Paired this way, the economics work: twenty years of settlement-grade hourly and daily rollups on top of a few months of raw telemetry, on a hypertable small enough to stay fast.

## Shape 5: Manual refresh for data older than any window

Bringing a site onto the platform means loading its history. Its live feed cut over in June, and the eighteen months before that sit in the plant historian (PI, FactoryTalk, or Ignition's built-in one), exported and loaded over a week with timestamps older than any policy window. No schedule will ever reach that range, so _Demystified_ hands this case to a manual [`refresh_continuous_aggregate`](https://www.tigerdata.com/docs/reference/timescaledb/continuous-aggregates/refresh_continuous_aggregate) call. After an ordinary load, which records its invalidations as it goes, the plain form is enough:

```SQL
CALL refresh_continuous_aggregate('generation_10min',
     TIMESTAMPTZ '2024-12-01 00:00:00+00',
     TIMESTAMPTZ '2026-06-01 00:00:00+00');
```

Buckets that do not fit entirely inside the window are excluded, so bucket-align your bounds to keep the partial ones at each end. The window parameters have to match the type of the aggregate's time bucket expression, and `force => true` re-does buckets the engine already considers up to date.

Manual refresh [runs incrementally in batches](https://github.com/timescale/timescaledb/pull/9903), forced refreshes included, each batch in its own transaction with locks released between them. The batch size defaults to ten buckets, `buckets_per_batch => 0` gives a single atomic pass, and `refresh_newest_first` defaults to true. That suits an onboarding backfill, where the recent end is the part someone is waiting on.

The load itself has a setting worth knowing. Bulk-loading eighteen months of readings writes eighteen months of invalidation records, and [`timescaledb.skip_cagg_invalidation`](https://www.tigerdata.com/docs/build/data-management/write-data/insert) turns that tracking off for the transaction. Because nothing was recorded, an ordinary refresh would treat the loaded buckets as current, so the manual refresh for this backfill has to be a forced one:

```SQL
BEGIN;
SET LOCAL timescaledb.skip_cagg_invalidation = ON;

COPY site_readings (site_id, ts, power_kw, energy_kwh)
  FROM '/data/onboarding/site_47.csv' WITH (FORMAT csv, HEADER true);

COMMIT;

CALL refresh_continuous_aggregate('generation_10min',
     TIMESTAMPTZ '2024-12-01 00:00:00+00',
     TIMESTAMPTZ '2026-06-01 00:00:00+00',
     force   => true,
     options => '{"buckets_per_batch": 5, "refresh_newest_first": true}'::jsonb);
```

![Continuous Aggregate Refresh Policies for Solar and Wind SCADA Data - Shape 5](https://assets.tigerdata.com/blog/2026/10/onboarding-site.svg)

On Tiger Cloud, where server-side files are unavailable, use psql's \\copy inside the same transaction. The forced refresh rewrites the ten-minute level and logs the invalidations its parents need, so the hourly and daily levels follow with plain calls over the same range, in that order. Run all three, and the new site's history is in every rollup before its first settlement cycle.

## Picking and combining your shapes

Your data's arrival behavior picks the shape, and what else runs against the hypertable modifies it.

.cagg-table { width: 100%; border-collapse: collapse; font-family: Arial, sans-serif; margin: 20px 0; } .cagg-table th { background-color: #f5f5f5; border: 1px solid #000; padding: 10px; font-weight: bold; text-align: left; font-size: 14px; } .cagg-table td { border: 1px solid #000; padding: 10px; font-size: 13px; line-height: 1.5; } .cagg-table tr:nth-child(even) { background-color: #fafafa; } .cagg-table code { background-color: #f0f0f0; padding: 2px 5px; font-family: 'Courier New', monospace; font-size: 12px; }

| Shape | When to use | How to use |
| --- | --- | --- |
| 1. Frequent, recent | Operators need closed buckets within the hour, and healthy sites deliver on time | Tight window (start_offset of hours, end_offset of one bucket) on a 15-minute schedule |
| 2. Daily, long-term | Sites flush days of buffered readings that still fall inside a resettlement window | Wide window sized to the resettlement window, run once a day off-peak |
| 3. Hierarchical | You stack ten-minute, hourly, and daily rollups | One policy per level with end_offset stepping upward, Toolkit stats_agg and rollup |
| 4. Keep aggregates, drop raw | Raw telemetry is kept for months and rollups for the contract term | Retention interval longer than the deepest start_offset; open-ended start_offset only for a derived view |
| 5. Manual | One-off history: onboarding a site, a corrected historian extract | skip_cagg_invalidation on the load, then a forced refresh_continuous_aggregate, level by level |

The shapes combine, and a working fleet usually runs several. Shapes 1 and 2 share one aggregate as long as their windows meet without crossing. Shape 3 gives each level of a stack its own policy. Shape 4 puts a floor under how far Shape 2 can reach, and Shape 5 runs beside all of them whenever a site comes onboard. Manual refresh never substitutes for a policy, though: a recurring late tail belongs in Shape 2, not in a runbook.

After a change, run the jobs query from the first section again to confirm it took. With Shapes 1 and 2 in place, `generation_hourly` returns two rows whose offsets meet, and each row still passes the two-bucket rule. If the shape you need involves moving in years of history from another system, the migration team on Tiger Data's [Enterprise plan](https://www.tigerdata.com/enterprise) can help plan it. Tuning the numbers inside the wrong shape will not get you to the right one.